MySQL Query vs. PHP MySQL Query

Question

I'm trying to do a MySQL query in PHP with some special characters. It seems to work if I run the query on my database with straight SQL:

SELECT SUM(quantity_ordered)  FROM `shopping_cart` 
   WHERE `cart_number` = 10316027
   AND `size` IN ('5&#188" x 8&#188"','5&#8540;" x 7&#190;"','4&#189;" x 9&#189;"')

The above query returns the expected result and SUM but when I put it in my prepared PHP query it returns no records or SUM.

I suspect that it has to do with the single quotes around each size but if I remove them I get a MySQL error. A similar query in my PHP with straight numbers and no surrounding quotes works fine.

I've tried different ways of escaping the special characters but I'm wondering if this query will work at all with these types of characters?

What does your prepared statement in php look like and what are the exact values of the variables? — jeroen
– jeroen, Commented Apr 4, 2014 at 16:23
@jeroen that's the point. I think he doesn't use prepared statement. but just mysql_query — Su4p
– Su4p, Commented Apr 4, 2014 at 16:26
Here is the prepared statement prepare("SELECT SUM(quantity_ordered),item_number FROM shopping_cart WHERE cart_number = :cart_number AND size IN ($items)"); with $items carrying the same variables as my sql statement — drdalziel
– drdalziel, Commented Apr 4, 2014 at 16:27
Use before query mysql_real_escape_string for `cart_number AND item_number. — kxc
– kxc, Commented Apr 4, 2014 at 16:30
Show us the actual PHP code that you're trying to execute. Please edit the original question and post the actual code, not a summary or paraphrase. — Andy Lester
– Andy Lester, Commented Apr 4, 2014 at 16:45

iMakeWebsites · Accepted Answer · 2014-04-04 16:29:31Z

1

You problem is you are probably not escaping the double quotes in your PHP string.

try this

$qry = "SELECT SUM(quantity_ordered)  FROM `shopping_cart` 
WHERE `cart_number` = 10316027
AND `size` IN ('5&#188\" x 8&#188\"','5&#8540;\" x 7&#190;\"','4&#189;\" x 9&#189;\"')"

answered Apr 4, 2014 at 16:29

iMakeWebsites

5873 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

drdalziel Over a year ago

That did it! I didn't think I needed to because they were inside the double quotes, Thanks!

iMakeWebsites Over a year ago

That is the problem. They were inside the double quotes, and the next double quote it saw, php thought that ended the string.

jeroen · Accepted Answer · 2014-04-04 16:30:08Z

1

Based on your comment about the prepared statement:

SELECT SUM(quantity_ordered) FROM wholesale_shopping_cart WHERE cart_number = :cart_number AND item_number IN ($items)

You should build your $items array with individual bound variables so that it would look like:

SELECT SUM(quantity_ordered) FROM wholesale_shopping_cart
WHERE
       cart_number = :cart_number
   AND item_number IN (:val1, :val2, :val3)

Then you can bind your variables and execute the query.

If you put your variable directly in your sql statement, you will (probably...) have an sql injection problem and you would need to escape your quotes correctly.

answered Apr 4, 2014 at 16:30

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

Collectives™ on Stack Overflow

MySQL Query vs. PHP MySQL Query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related