php mysql: UPDATE will only work WHERE data = integer

Question

I am new to php and mysql so it may be that I am missing something obvious but here goes:

I am setting up a database to store items with stock. Each item is assigned an amount of stock (e.g. 5). Each item is displayed on my webpage with buttons next to it to manually add or remove stock from the database:

 $query = "SELECT * FROM classes"; // This uses a mysql function that act upon our $query variable above $result = mysql_query($query); // This is a loop that says "while there is data to display...keep looping round and displaying that data". while($class_data = mysql_fetch_array($result)) { // Now we are going to echo that data echo "<div class=\"classmanagertablecontent\">" . $class_data['name'] . "</div> <div class=\"classmanagertablecontent\">" . $date['date'] . "</div> <div class=\"classmanagertablecontent\">" . $class_data['stock'] . "</div> <div class=\"classmanagertablecontent\">" . $class_data['button_paypal'] . "</div>"; <form action="../url.php" method="post"> <input type="hidden" name="button_paypal_fromform" value="<?php echo $class_data['button_paypal'] ?>"> <input type="submit" value="remove stock" name="remove stock"> </form> <form action="../url.php" method="post"> <input type="hidden" name="button_paypal_fromform" value="<?php echo $class_data['button_paypal'] ?>"> <input type="submit" value="add to stock" name="add to stock" > </form> </div> }

The code in the url that the "add to stock" button posts to is as follows:

 <?php $button_paypal = $_POST['button_paypal_fromform']; mysql_query("UPDATE classes SET stock = stock+1 WHERE button_paypal = ". $button_paypal .""); ?>

For whatever reason WHERE button_paypal = "a number" it works... ...but if button_paypal = "a string" it does nothing.

I cannot get my head around this. The paypal hosted buttons I will be using are made up of letter and numbers so it need to work with a combination of the two.

I have tried...

echo "$button_paypal";

...on my url.php page and the button reads fine (e.g. SD76S9SFGV)

in my database "button_paypal" is a TEXT field but i have also tried VARCHAR. STOCK is an INT.

If anyone can help then I will be very grateful. Thanks. #hopethismakessense

The short answer is that you need to wrap strings that you're using as values in SQL statements in quotes. The longer answer is that you should be using PDO or mysqli, and using prepared statements and bound statements to add variables into queries, as it's more secure and avoids this sort of issue completely. — andrewsi
– andrewsi, Commented May 22, 2013 at 19:38
Thanks for your answer. Can you please give me an example of wrapping the values in statements in quotes? — Michael Walkling
– Michael Walkling, Commented May 22, 2013 at 19:50
Ok got it you mean single quotes like this - '". $button_paypal ."' Thank you so much. I've been sitting here for over an hour trying to work that one out! Cheers. — Michael Walkling
– Michael Walkling, Commented May 22, 2013 at 19:53
Something like UPDATE table SET field=1 WHERE value='something'. Any string value should be wrapped in single quotes, and you should also wrap table and column names in backticks (which I've not done, as backticks turn text here into code. — andrewsi
– andrewsi, Commented May 22, 2013 at 19:54
And as I said - look at PDO or mysqli, because otherwise your website is wide open to exploitation. — andrewsi
– andrewsi, Commented May 22, 2013 at 19:55

Patrick Moore · Accepted Answer · 2013-05-22 20:00:13Z

All you need to do is wrap your string value in single quotes:

<?php mysql_query( "UPDATE classes SET stock = stock+1 WHERE button_paypal = '{$_POST['button_paypal_fromform']}' ");

I wrapped the array variable in {} brackets, so you can include directly within your double-quoted query (no need to concatenate like " . $var . ", and also no need to create a second variable when you can reference this directly)

Collectives™ on Stack Overflow

php mysql: UPDATE will only work WHERE data = integer

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related