hfheuhf Posted February 6, 2015 Share Posted February 6, 2015 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> <?phpif(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);}}?> Link to comment https://forums.phpfreaks.com/topic/294431-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/ Share on other sites More sharing options...
Psycho Posted February 6, 2015 Share Posted February 6, 2015 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. Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505085 Share on other sites More sharing options...
hfheuhf Posted February 6, 2015 Author Share Posted February 6, 2015 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. Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505086 Share on other sites More sharing options...
hfheuhf Posted February 6, 2015 Author Share Posted February 6, 2015 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 ) Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505095 Share on other sites More sharing options...
Zane Posted February 6, 2015 Share Posted February 6, 2015 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'"; Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505096 Share on other sites More sharing options...
hfheuhf Posted February 6, 2015 Author Share Posted February 6, 2015 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. Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505097 Share on other sites More sharing options...
maxxd Posted February 6, 2015 Share Posted February 6, 2015 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. Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505098 Share on other sites More sharing options...
hfheuhf Posted February 6, 2015 Author Share Posted February 6, 2015 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 Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505099 Share on other sites More sharing options...
CroNiX Posted February 6, 2015 Share Posted February 6, 2015 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"/> Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505100 Share on other sites More sharing options...
CroNiX Posted February 6, 2015 Share Posted February 6, 2015 Also, in your loop, you aren't assigning any values to the <option>'s, so nothing will get sent with that field. Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505103 Share on other sites More sharing options...
maxxd Posted February 6, 2015 Share Posted February 6, 2015 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. Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505106 Share on other sites More sharing options...
hfheuhf Posted February 6, 2015 Author Share Posted February 6, 2015 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! Link to comment https://forums.phpfreaks.com/topic/294431-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/#findComment-1505120 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.