MYSQL table will not update

Question

I have been trying to trouble shoot this issue for a couple days now and I am hitting a brick wall. My form submits without issue and my php code generates no errors yet it does not update the table in mysql. Any help is greatly appreciated I apologize in advance for posting so much code.

Here is my form code:

<form style="width:1000px" action="new_recipe.php" method="POST" name="frm_add_recipe">

<div id="home_wrapper">

<div id="add_recipe_box">
<h1>Add a New Recipe</h1>

<label for="recipe_name">Recipe Name:</label><br/>
<input type="text" name="recipe_name" class="add_recipe_field"/>

<label for="ingredient1">Ingredients:</label><br/>
<input type="text" name="ingredient1" class="add_recipe_field"/>


<input type="text" name="ingredient2" class="add_recipe_field"/>


<input type="text" name="ingredient3" class="add_recipe_field"/>


<input type="text" name="ingredient4" class="add_recipe_field"/>


<input type="text" name="ingredient5" class="add_recipe_field"/>


<input type="text" name="ingredient6" class="add_recipe_field"/>

<label for="ingredient7"></label>
<input type="text" name="ingredient7" class="add_recipe_field"/>


<input type="text" name="ingredient8" class="add_recipe_field"/>


<input type="text" name="ingredient9" class="add_recipe_field"/>


<input type="text" name="ingredient10" class="add_recipe_field"/><br/>

<label for="lst_meal">Select meal type:</label>
<select name="lst_meal" >
<option value="breakfast">Breakfast</option>
<option value="lunch">Lunch</option>
<option value="dinner">Dinner</option>
</select><br/>

<label>Recipe Ethnecity:</label>
<select name="lst_ethnicity">
<option value="blank">N/A</option>
<option value="American">American</option>
<option value="Asian">Asian</option>
<option value="Chineese">Chineese</option>
<option value="German">German</option>
<option value="Italian">Italian</option>
<option value="Indian">Indian</option>
<option value="Mexican">Mexican</option>
<option value="Thia">Thia</option>
</select><br/>


<label for="instructions">Cooking instructions:</label><br/>

<input name="instructions" type="text" maxlength="250" id="txt_instructions"/>

<input type="submit" value="Save" />

</div>
</div>
</form>

And here is my php:

 <?php
$recipe_name = $_POST['recipe_name'];
$ingredient1 = $_POST['ingredient1'];
$ingredient2 = $_POST['ingredient2'];
$ingredient3 = $_POST['ingredient3'];
$ingredient4 = $_POST['ingredient4'];
$ingredient5 = $_POST['ingredient5'];
$ingredient6 = $_POST['ingredient6'];
$ingredient7 = $_POST['ingredient7'];
$ingredient8 = $_POST['ingredient8'];
$ingredient9 = $_POST['ingredient9'];
$ingredient10 = $_POST['ingredient10'];
$lst_meal = $_POST['lst_meal'];
$lst_ethnicity = $_POST['lst_ethnicity'];
$instructions = $_POST['instructions'];

$dbhost = 'localhost' or die("cannot connect"); //Change to webserver info
$dbname = '*' or die("cannot connect"); //Change to webserver info
$dbuser = '*' or die("cannot connect"); //Change to webserver info
$dbpass = '*' or die("cannot connect"); //Change to webserver info
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $conn);

$recipe_name = mysql_real_escape_string($recipe_name);
$ingredient1 = mysql_real_escape_string($ingredient1);
$ingredient2 = mysql_real_escape_string($ingredient2);
$ingredient3 = mysql_real_escape_string($ingredient3);
$ingredient4 = mysql_real_escape_string($ingredient4);
$ingredient5 = mysql_real_escape_string($ingredient5);
$ingredient6 = mysql_real_escape_string($ingredient6);
$ingredient7 = mysql_real_escape_string($ingredient7);
$ingredient8 = mysql_real_escape_string($ingredient8);
$ingredient9 = mysql_real_escape_string($ingredient9);
$ingredient10 = mysql_real_escape_string($ingredient10);
$instructions = mysql_real_escape_string($instructions);

$query = "INSERT INTO recipes ( recipe_name, ingredient1, ingredient2, ingredient3, ingredient4, ingredient5, ingredient6, ingredient7, ingredient8, ingredient9, ingredient10, meal, ethnicity, instructions )
        VALUES ( '' , '$recipe_name' , '$ingredient1' , '$ingredient2' , '$ingredient3' , '$ingredient4' , '$ingredient5' , '$ingredient6' , '$ingredient7' , '$ingredient8' , '$ingredient9' , '$ingredient10' , 'lst_meal' , '$lst_ethnicity' , '$instructions' );";
mysql_query($query);
mysql_close();
header('Location: home.php');
?>

Holy hell. Use PDO, you would save a LOT of coding time and escaping... (and easier troubleshooting) What does `mysql_error();` after the query give you? — Jon, Apr 13 '13 at 06:31
and remove `''` at the front of `VALUES ( '', '$recipe_name'...` — Jon, Apr 13 '13 at 06:34
echo mysql_error() after mysql_query to check whether your insert statement generates any error or not — Amit, Apr 13 '13 at 06:37
Lol,so your `or die` are all arbitrary since they're just assigning a string.. You want to test mysql_query.. Try `die($sql);` right Before it and let us know what the sql statement is, since that's where we'll likely find the problem.. — asifrc, Apr 13 '13 at 06:37
additionally, mysql is deprecated in PHP 5.5, use mysqli instead (you can more or less just add an i after mysql in your code http://php.net/manual/en/book.mysqli.php — asifrc, Apr 13 '13 at 06:41
If you echo out $query - variable. How does the actual query look like? — bestprogrammerintheworld, Apr 13 '13 at 07:09
Just add echo mysql_error(); below mysql_query($query); and you can see what is wrong. — Kuzgun, Apr 13 '13 at 07:57

score 3 · Accepted Answer · answered Apr 13 '13 at 06:38

Column counts are not matching in your query.

Try below one

$query = "INSERT INTO recipes ( recipe_name, ingredient1, ingredient2, ingredient3, ingredient4, ingredient5, ingredient6, ingredient7, ingredient8, ingredient9, ingredient10, meal, ethnicity, instructions )
        VALUES ('$recipe_name' , '$ingredient1' , '$ingredient2' , '$ingredient3' , '$ingredient4' , '$ingredient5' , '$ingredient6' , '$ingredient7' , '$ingredient8' , '$ingredient9' , '$ingredient10' , 'lst_meal' , '$lst_ethnicity' , '$instructions' );";

score 0 · Answer 2 · answered Apr 13 '13 at 08:33

Please don't downvote/flag for offtopic, I'm just trying to help (and it deals directly with the code presented in this question).

I know that this is unsolicited, but I thought I'd just throw out some advice that I think would help you when approaching iterative situations like the one in your question in the future:

Instead of using ingredient1, 2, 3.. you can just use an array of inputs in your html, and iterate through them in your PHP. You can do this by replacing the number in your html name attribute for the inputs with []. You can either replace all ten of your ingredient inputs with
<input type="text" name="ingredient[]" class="add_recipe_field"/> or instead, you could use a simple php loop:

<?php
for ($i=0; $i < 10; $i++)
{
    echo "<input type='text' name='ingredient[]' class='add_recipe_field'/>";
}
?>

This will make $_POST['ingredient'] available as an array to your PHP. I rewrote your PHP code (yep, all of it) to illustrate how you can loop through it (additionally, I used myqsqli), found below. This way, you could change the number of ingredients simply by changing the counter above and your mysql table (no need to modify the code below!). Additionally, (something I would do myself) you could write some javascript (or jquery,etc.) to have only one input to start with, and automatically add an input every time the user types in an ingredient.. just a thought. But yea, let me know if this is helpful and if it all makes sense. And of course, I'm by no means a paragon of perfect and efficient code, so by all means if anyone who bothered reading this answer wants to comment on how to improve on what I wrote, go for it.

<?php
$dbhost = 'localhost'; //Change to webserver info
$dbname = '*'; //Change to webserver info
$dbuser = '*'; //Change to webserver info
$dbpass = '*'; //Change to webserver info

//Connect to database
$mysqli = mysqli($dbhost, $dbuser, $dbpass, $dbname);
//Check for successful connection
if ($mysqli->connect_errno) {
    die("Failed to connect to MySQL: ".$mysqli->connect_error);
}

//Mysql table fields (besides ingredients)
$sqla = "`recipe_name`, `meal`, `ethnicity`, `instructions`";

//Manually add corresponding values (could also be done in a loop)
$sqlb = $_POST['recipe_name']; //You Probably want some sort of validation here
$sqlb .= ", ".$_POST['lst_meal'];
$sqlb .= ", ".$_POST['lst_ethnicity'];
$sqlb .= ", ".$_POST['instructions'];

//Loop through ingredients and add to query
$comma = ", "; //Leave empty if all mysql fields within loop
$ingredients = $_POST['ingredient'];
foreach(array_keys($ingredients) as $k)
{
    if ($ingredients[$k]!="") //If not blank
    {
        //Add mysql field name (+1 since index starts at 0)
        $sqla .= $comma."`ingredient".($k+1)."`";
        //Add value to other half of string
        $sqlb .= $comma."\"".$mysqli->real_escape_string($ingredients[$k])."\""; 
        $comma = ", "; //Insert comma in subsequent iterations
    }
}
//Put the query together
$query = "INSERT INTO `recipes` (".$sqla.") VALUES (".$sqlb.");";

//Try the query and die the error + sql on error
if (!$mysqli->query($query))
{
    die("MySQL Error: ".$mysqli->error."<br>Query: ".$query);
}
header('Location: home.php');
?>

Use PDO. MySQLi is incomplete and while `real_escape_string` _may be_ effective enough to use, the library itself is lacking, and using prepared queries is a better way to prevent injection attacks. — Jon, Apr 13 '13 at 08:40
@Jon I've never used PDO, and only know *about* it (and most certainly never have use real_esc.. for myself).. Do you have an example of how MySQLi is incomplete / PDO is better? — asifrc, Apr 13 '13 at 08:45
If you have ever tried to use prepared statements, you'd understand why it is incomplete, though, for an example, you have to bind the results, or use [`mysqli_stmt::get_result()`](http://us2.php.net/manual/en/mysqli-stmt.get-result.php) in order to work with the result of a query in a fashion that is.. expected. Only, the `get_result()` function is unreliable and doesn't work with all compiled versions, so it can't be relied upon - meaning to have portable code, you have to bind each expected result which is tedious to say the least. — Jon, Apr 13 '13 at 09:00
Ran out of room. See: http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons or do a google search for 'php mysqli vs pdo' and you'll get a much better understanding of why I say it is incomplete. ^^ — Jon, Apr 13 '13 at 09:04
Wow, thank you for taking the time to do all of that. I will take your suggestions and look at rewriting my code. It makes much more sense to use the techniques you described. I am sure that is is quite obvious that I am a novice. That being said I am very thankful that you spent so much time and effort to help me out. Cheers! — user2276813, Apr 14 '13 at 01:44

score -1 · Answer 3 · answered Apr 13 '13 at 07:05

-1

I think u have to give the name to input type submit. Like below:

<?php
$save=$_POST['save'];
?>

<input type="submit" value="Save"  name="save"/>

answered Apr 13 '13 at 07:05

Rashad

1,265
2
16
31

after that it should be work. Please let me know, if will not work I will try to fix this issue – Rashad Apr 13 '13 at 07:08
I'm about 90% sure that you're wrong.. Where did you learn that from? – asifrc Apr 13 '13 at 07:10
Your assumption is incorrect. This is totally irrelevant. Therefore downvote. – bestprogrammerintheworld Apr 13 '13 at 07:10
@bestprogrammerintheworld whose assumption? – asifrc Apr 13 '13 at 07:11
@asifrc - ah forgive me. Rashads assumption. – bestprogrammerintheworld Apr 13 '13 at 07:12
There are no checks for whether it was submitted or not, and naming the submit is really only useful if there is more than one submit button and they perform different actions. – Jon Apr 13 '13 at 08:32

MYSQL table will not update

3 Answers3