INSERT INTO statement not working

Question

I am fairly new to PHP/SQL coding and I am completely stumped as to why this coding isn't working. The database itself was made by my University, and so I am just trying to connect to it (I'm not sure if I'm able to give away the details so I took the connect coding out. I am at a basic level so heavy technical language will go over my head but any advice will be greatly appreciated!

I am trying to insert the results from a form which is linked to the PHP file into the database table. I am unsure if I need to put anything in the PHP file to state this? But this is my code:

<?php
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
// Connect to database
$sql = "SELECT runnerid, position, eventid, date, finishtime, categoryid,  agegrade, pb FROM Results";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
    { 
        echo "<table>";
        echo '<table border=1><tr><th>Runner ID</th><th>Position</th><th>Event ID</th><th>Date</th><th>Finish Time</th><th>Category ID</th><th>Age Grade</th><th>Personal Best</th></tr>';
        echo "<tr><td>";
        echo $row['runnerid'];
        echo "</td><td>";
        echo $row['position'];
        echo "</td><td>";
        echo $row['eventid'];
        echo "</td><td>";
        echo $row['date'];
        echo "</td><td>";
        echo $row['finishtime'];
        echo "</td><td>";
        echo $row['categoryid'];
        echo "</td><td>";
        echo $row['agegrade'];
        echo "</td><td>";
        echo $row['pb'];
        echo "</td>
        </tr>";

    }
    echo "</table>";
}
} else {
 echo "0 results";
}

$sql = "INSERT INTO Results VALUES ('$_POST[runnerid]', '$_POST[position]' '$_POST[eventid]' '$_POST[date]' '$_POST[finishtime]' '$_POST[categoryid]' '$_POST[agegrade]' '$_POST[pb]')";

$result = $conn->query($sql);
if (!$result) {
    die('Could not insert data' . mysql_error()); 
}
$conn->close();
?>

I have even tried the code without the $_POST coding in to just add new data but that's not working either.

You're missing commas between your values in your query. You have one after the `runnderid` but none of the others have them. — DiddleDot, Dec 15 '15 at 17:41
Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code. You're also mixing MySQL functions with `mysql_error()` which should read as `mysqli_error($conn)`. Not posting your HTML form that goes with this, leaves us all guessing. **Check for errors**. — Funk Forty Niner, Dec 15 '15 at 18:00
Worth mentioning, you should be using PDO instead of mysqli; it's more portable and much much easier to use. — miken32, Dec 15 '15 at 18:16

score 7 · Answer 1 · edited May 23 '17 at 12:34

What nobody has mentioned yet is that, given your code, I could easily wipe out your entire database with one request. Here's what you need to be doing to protect against SQL injection attacks:

$sql = "INSERT INTO Results VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('iiississ', $_POST["runnerid"], $_POST["position"], $_POST["eventid"], $_POST["date"], $_POST["finishtime"], $_POST["categoryid"], $_POST["agegrade"], $_POST["pb"]);
$result = $stmt->execute();
if (!$result) {
    die('Could not insert data: ' . $conn->error); 
}

Read up on prepared statements:

Bound variables are sent to the server separately from the query and thus cannot interfere with it. The server uses these values directly at the point of execution, after the statement template is parsed. Bound parameters do not need to be escaped as they are never substituted into the query string directly. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion.

Good point, After all the other bits I totally missed that UV+1 — RiggsFolly, Dec 15 '15 at 18:08

RiggsFolly · Accepted Answer · 2015-12-15T18:26:41.190

If you indent your code nicely you will see errors more easily.

You have unmatches curly brackets
Your query syntax is wrong, i.e. its missing commas between parameters
Variables will substitute better in a "" string if you use {} round the variable name.
You need to move that <table> outside the while loop and remove the second <table.... tag
The table headings also would be better outside the loop
You are also using mysql_error() but you instantiated a mysqli_ object, its not allowed to mix extensions. Use $conn->error instead
Also see @miken32 answer suggesting the use of bound variables.

Here are some suggested fixes :-

<?php
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
// Connect to database
$sql = "SELECT runnerid, position, eventid, date, 
               finishtime, categoryid,  agegrade, pb FROM Results";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row

    echo '<table border="1">
         <tr><th>Runner ID</th><th>Position</th><th>Event ID</th>
             <th>Date</th><th>Finish Time</th><th>Category ID</th>
             <th>Age Grade</th><th>Personal Best</th>
         </tr>';

    while($row = $result->fetch_assoc()) {
    { 
        echo "<tr><td>";
        echo $row['runnerid'];
        echo "</td><td>";
        echo $row['position'];
        echo "</td><td>";
        echo $row['eventid'];
        echo "</td><td>";
        echo $row['date'];
        echo "</td><td>";
        echo $row['finishtime'];
        echo "</td><td>";
        echo $row['categoryid'];
        echo "</td><td>";
        echo $row['agegrade'];
        echo "</td><td>";
        echo $row['pb'];
        echo "</td>
        </tr>";

    }
    echo "</table>";

// } remove extra barckey

} else {
 echo "0 results";
}

$sql = "INSERT INTO Results 
          VALUES ('{$_POST['runnerid']}', '{$_POST['position']}', 
                  '{$_POST['eventid']}', '{$_POST['date']}', 
                  '{$_POST['finishtime']}' '{$_POST['categoryid']}',
                  '{$_POST['agegrade']}', '{$_POST['pb']}')";

$result = $conn->query($sql);
if (!$result) {
    die('Could not insert data' . $conn->error); 
}
$conn->close();
?>

score -2 · Answer 3 · answered Dec 15 '15 at 17:40

-2

$sql = "INSERT INTO Results VALUES ('$_POST[runnerid]', '$_POST[position]', '$_POST[eventid]', '$_POST[date]', '$_POST[finishtime]' ,'$_POST[categoryid]', '$_POST[agegrade]', '$_POST[pb]')";   //missing comma after each value

answered Dec 15 '15 at 17:40

Shailesh Katarmal

2,757
1
12
15

The $_POST variables will not get substituted into that string without doing `'{$_POST[runnerid]}',` – RiggsFolly Dec 15 '15 at 17:52
1

@RiggsFolly double-quoted strings can parse single dimensional array indices just fine. – miken32 Dec 15 '15 at 18:13
1

@miken32 Not on any PHP I have used and especially not without a single quote around the array index i.e. `$_POST[runnerid]` should be `$_POST['runnerid']` – RiggsFolly Dec 15 '15 at 18:18
@RiggsFolly [See example 8.](http://php.net/manual/en/language.types.string.php#language.types.string.parsing) – miken32 Dec 15 '15 at 18:20
1

@miken32 Ooo, another good day, I learnt something I had previously completely missed. Thanks – RiggsFolly Dec 15 '15 at 18:22

score -3 · Answer 4 · answered Dec 15 '15 at 17:46

-3

Brother you forgot , after each value. and where ever you are taking any value directly from post in sql query then it would be in between '".$_post."' or "'.$_post.'" one condition will suit see the following query you'll understand.

all the best...

if any problem, comment plz. let me know I'll fix

it must be like following:

 $sql = "INSERT INTO Results VALUES ('".$_POST[runnerid]."', '".$_POST[position]."', '".$_POST[eventid]."', '".$_POST[date]."', '".$_POST[finishtime]."', '".$_POST[categoryid]."', '".$_POST[agegrade]."', '".$_POST[pb]."')";

answered Dec 15 '15 at 17:46

Hemant Maurya

136
1
1
14

1

You will get a dozen notices about undefined constants if you try to run that code. – miken32 Dec 15 '15 at 18:14
@miken32 as many constants you use, it makes page slow. and whichever function you want to use, you can use inside it. – Hemant Maurya Dec 15 '15 at 18:22

INSERT INTO statement not working

4 Answers4