Jump to content

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
Go to solution Solved by CroNiX,

Recommended Posts

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);
}
}
?>

 

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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
Share on other sites

 

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 by hfheuhf
Link to comment
Share on other sites

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
Share on other sites

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
Share on other sites

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
Share on other sites

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! :happy-04:

Edited by hfheuhf
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.