You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[hfheuhf]

Keep getting these two errors when trying to UPDATE a field called "type" to 'banana' based on the fuitid. Why is this happening?

 

Undefined index: fruitid in update.php

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

<form method="POST" action="update.php">
<select>
<option value="">Select</option>
<?php
$sql = "SELECT fruitid, datefrom, dateto FROM fruits";
$sqlresult = $link->query($sql);
$sqllist = array();
while($row = mysqli_fetch_array($sqlresult)) {
echo "<option>".$row['datefrom']." - ".$row['dateto']."</option>";
}
?>
</select>
<input type="hidden" value="<?php echo $fruitid;?>" name="fruitid"/>
<input type="submit" value="Submit" name="submit"/>
</form>

 

<?php
if(isset($_POST['submit'])) {

$fruitid= mysqli_real_escape_string($link,$_POST['fruitid']);

$sql = "UPDATE `fruits` SET `type`='banana' WHERE fruitid = $fruitid";

if (mysqli_query($link, $sql)) {
echo "updated";
} else {
echo "problem: " . mysqli_error($link);
}
}
?>

 

[Psycho]

Echo out the query to the page so you can verify what the variable content and the resulting complete query contain. My guess is that $fruitid does not contain what you think it does (i.e. it is empty, thus the " . . . the right syntax to use near ''". Plus, if it should be a numeric value, mysqli_real_escape_string() is not the function you want to use. You want to ensure the value is numeric.

Edited February 6, 2015 by Psycho

[hfheuhf]

I have done, it says... UPDATE `fruits` SET `type`='banana' WHERE `fruitid` = Error: You have an error in your SQL syntax; How do I solve this please. I have read over 15 different posts today but nothing seems to be working.

[hfheuhf]

Echo out the query to the page so you can verify what the variable content and the resulting complete query contain. My guess is that $fruitid does not contain what you think it does (i.e. it is empty, thus the " . . . the right syntax to use near ''". Plus, if it should be a numeric value, mysqli_real_escape_string() is not the function you want to use. You want to ensure the value is numeric.

Basically, the table for fruits has 4 fields - fruitd which is a primarykey, user, dateto, datefrom and type. I need to update the type to "banana" when the Submit button is pressed. So, the fruitid field contains the id numbers. I have removed mysqli_real_escape_string but it is still displaying the errors. I have used "print_r($_POST);" which printed and array of the select data and in displays the fruitid in the array which is 9.  Array ( [select] => 9 [submit] => Submit )

[Zane]

If you're going to treat the id as a string, then use it like a string.. place quotes around the id

$sql = "UPDATE `fruits` SET `type`='banana' WHERE fruitid = '$fruitid'";

[hfheuhf]

 

If you're going to treat the id as a string, then use it like a string.. place quotes around the id

$sql = "UPDATE `fruits` SET `type`='banana' WHERE fruitid = '$fruitid'";

If I treat it as a string would I use the msqli_escape string function then? But, I have tried this solution originally, it would show the message that the type has been updated but not actually updating in the database. Also, it's still showing the Undefined index:fruitid error.

Edited February 6, 2015 by hfheuhf

[maxxd]

I have used "print_r($_POST);" which printed and array of the select data and in displays the fruitid in the array which is 9.  Array ( [select] => 9 [submit] => Submit )

 

If this is the exact output from print_r($_POST), then there's an issue. The index is not 'fruitid', it's 'select'. When you view the HMTL source of your page, does the hidden field show the correct fruit id value? Try giving your select element a name attribute, and run 'print_r($_POST,true);' again and see what it has to say.

[hfheuhf]

If this is the exact output from print_r($_POST), then there's an issue. The index is not 'fruitid', it's 'select'. When you view the HMTL source of your page, does the hidden field show the correct fruit id value? Try giving your select element a name attribute, and run 'print_r($_POST,true);' again and see what it has to say.

I've given it a name attribute and an id of "fruitid" and now the array is Array ( [fruitid] => 9 [submit] => Submit ) how can i fix this please help

[CroNiX]

If you gave your <select> a name of "fruitid", did you get rid of the hidden form field with the name "fruitid"?

<input type="hidden" value="<?php echo $fruitid;?>" name="fruitid"/>

[CroNiX]

Also, in your loop, you aren't assigning any values to the <option>'s, so nothing will get sent with that field.

[maxxd]

I've given it a name attribute and an id of "fruitid" and now the array is Array ( [fruitid] => 9 [submit] => Submit ) how can i fix this please help

 

Also note that PHP will index both the $_GET and $_POST arrays by the HTML 'name' attribute. So, if you have

<input type='text' name='a_text_box' id='my_name_is_earl' value='yuuuuuup' />

You would access that field using $_POST['a_text_box'], and it would print 'yuuuuuup'. However, attempting to access $_POST['my_name_is_earl'] will return an error, as that index does not exist in $_POST after submitting your form.

[hfheuhf]

Also, in your loop, you aren't assigning any values to the <option>'s, so nothing will get sent with that field.

I've deleted the hidden input and added echo "<option value=".$row['fruitid'].">".$row['datefrom']." - ".$row['dateto']."</option>"; and

it's fixed now! Thanks for your help everyone!

Edited February 6, 2015 by hfheuhf