php from to insert a row to mysql database table

Question

I'm trying to get a user input to add a row into my table, but seems $_post doesn't work anymore on second page, how do i do this? I want user be able to add, delete, update values by themselves. thank you much for you all's help

here is my first page:

<?php
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$sql = "SELECT * FROM entree";
$result = $conn->query($sql);
echo "<table border='1'>
<tr>
<th>entree id</th>
<th>entree name</th>
<th>price</th>
<th>spicy</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['e_id'] . "</td>";
echo "<td>" . $row['ename'] . "</td>";
echo "<td>" . $row['price'] . "</td>";
echo "<td>" . $row['spicy'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
<form action="addrow.php" method="post">
    to make any change of this table, please fill in parts you needs.<br><br>
    To add a new row: please enter:<br>
    Entree ID: <input type="input" name="e_id"> 
    Entree Name: <input type="input" name="ename">
    Price: <input type="input" name="price">
    Spicy: <input type="input" name="spicy">
    <br>
    <input type="submit" value="add">
</form>

here is my second page:

<?php
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO entree (e_id, ename, price, spicy)
VALUES ('$_POST["e_id"]', '$_POST["ename"]', '$_POST["price"]', '$_POST["spicy"]')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>

Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Feb 24 '15 at 22:45
Try using this format: `{$_POST["e_id"]}` inside quoted string for PHP variables (good for arrays). — skobaljic, Feb 24 '15 at 22:48
**WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). **NEVER** put `$_POST` data directly into a query. — tadman, Feb 24 '15 at 22:56
SQL's error output should have given you `Parse error: syntax error, unexpected '"', expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)` something you failed to share with us. — Funk Forty Niner, Feb 24 '15 at 23:00

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

You are nesting """ in

$sql = "INSERT INTO entree (e_id, ename, price, spicy) VALUES ('$_POST["e_id"]', '$_POST["ename"]', '$_POST["price"]', '$_POST["spicy"]')";

Also this introduces a big security hole - sql injection. Use named parameters instead. This will solve two problems.

how to bind multiple parameters to MySQLi query

edited May 23 '17 at 11:43

Community

1
1

answered Feb 24 '15 at 22:48

Kevin Seifert

3,494
1
18
14

score 0 · Answer 2 · answered Feb 24 '15 at 22:57

You should be using prepared statements but in your current state your quotes are mixed up. One way to fix that is with concatenation:

 $sql = "INSERT INTO entree (e_id, ename, price, spicy) VALUES ('" . $_POST["e_id"] . "', '" . $_POST["ename"] ."', '". $_POST["price"] ."', '" .$_POST["spicy"] ."')";

score 0 · Answer 3 · answered Feb 24 '15 at 22:58

You can actually see the error from the highlighting in your post. There is a mismatch between the quotes used in your SQL query.

I would rewrite you script to extract the required information before constructing the SQL query.

$id    = $_POST['e_id'];
$name  = $_POST['ename'];
$price = $_POST['price'];

// Or even better. Filter incoming data to increase security just a little more.
$spicy = filter_input(INPUT_POST, 'spicy', FILTER_SANITIZE_STRING);

$sql = "
    INSERT INTO `entree` (`e_id`, `ename`, `price`, `spicy`)
    VALUES 
    ('{$id}', '{$name}', '{$price}', '{$spicy}')
";

When using double quotes you can write variable directly inside a string. I have furthermore added curly brackets around the variables to help the PHP interpreter, yourself visually and some IDE's recognize them.

I have also added quotes round the table and column names inside the SQL to improve visualization of which words are keywords and which words are table/column names.

Important!

Please consider using prepared statements. PHP provides PDO and MySQLi extensions. They will help mitigate vulnerabilities from SQL injection, which your current script is subjective to. There are plenty of tutorials on how to use prepared statements and the two extensions mentioned.

Happy Coding!

php from to insert a row to mysql database table

3 Answers3