Solved

why does this query execute fine in MySQL but not using mysql_query()?

Posted on 2011-02-27
6
350 Views
Last Modified: 2013-12-13
the following query
set @num := 0, @shop_id := NULL;


select shop_id, date
from (
   select shop_id, date,
      @num := if(@shop_id = shop_id, @num + 1, 1) as row_number,
      @shop_id := shop_id as dummy
  from products
  order by shop_id, date DESC
) as x where x.row_number <= 10;

Open in new window

executes fine when I run it through phpMyAdmin, but when I run it against the same database using
$query = "SET @num :=0,
@shop_id := NULL ;
SELECT shop_id, date
FROM (
SELECT shop_id, date, @num := IF( @shop_id = shop_id, @num +1, 1 ) AS row_number, @shop_id := shop_id AS dummy
FROM products
ORDER BY shop_id, DATE DESC
) AS x
WHERE x.row_number <=10;";

$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

Open in new window

I get
Query: SET @num :=0, @shop_id := NULL ; SELECT shop_id, date FROM ( SELECT shop_id, date, @num := IF( @shop_id = shop_id, @num +1, 1 ) AS row_number, @shop_id := shop_id AS dummy FROM products ORDER BY shop_id, DATE DESC ) AS x WHERE x.row_number <=10; 
MySQL Error: 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 '; SELECT shop_id, date FROM ( SELECT shop_id, date, @num := IF( @shop_id = shop_' at line 2

Open in new window

what am I doing wrong? I noticed that if I remove the user variable initialization line ('SET' etc.) the query executes, but obviously not with the correct result.
0
Comment
Question by:bitt3n
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34993089
It appears that you cannot execute multiple ";" spearated queries in one mysql_query.
http://www.php.net/manual/en/function.mysql-query.php#16878
You have to do that one at a time.
0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 500 total points
ID: 34993094
Try like this:

$query = "SET @num :=0, @shop_id := NULL ;";
$result = mysql_query($query);
$query = "SELECT shop_id, date
FROM (
SELECT shop_id, date, @num := IF( @shop_id = shop_id, @num +1, 1 ) AS row_number, @shop_id := shop_id AS dummy
FROM products
ORDER BY shop_id, DATE DESC
) AS x
WHERE x.row_number <=10;";

$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

Open in new window

0
 

Author Comment

by:bitt3n
ID: 34993128
thanks that works. I foolishly didn't realize the variables would persist between the calls to mysql_query().

I'm curious, given the fact that mysql_query() only accepts one query at a time, how a sql injection is ever possible, since my understanding is that the injection occurs when someone adds a second query to the first when submitting data, as in

'; DROP DATABASE;

or whatever.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Closing Comment

by:bitt3n
ID: 34993129
thanks!
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 34993147
Good point. This is the prevention for executing multiple queries. You cannot drop database in mysql_query, but you can add string variables ' or 1= 1 '.
0
 

Author Comment

by:bitt3n
ID: 34993187
ah that makes sense thanks again
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP curl issue VERBOSE output 18 80
PHP Syntax Error 4 32
Number of hours between date in DB and now 8 21
Moving from Mcrypt to OpenSSL 18 45
This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question