SQL query returns empty result

Question

I have this SQL query:

SET @date_shift = DATE_SUB(NOW(), Interval 60 MINUTE);
SELECT hinfo.idx,
   hinfo.host_idx,
   hinfo.processor_load,
   hinfo.memory_total,
   hinfo.memory_free,
   hnames.idx,
   hnames.name,
   disks.hostinfo_idx,
   disks.id,
   disks.name,
   disks.size,
   disks.freespace,
   hinfo.probetime
FROM   systeminfo.hostinfo AS hinfo
   INNER JOIN systeminfo.hosts AS hnames
           ON hnames.idx = hinfo.host_idx
   INNER JOIN systeminfo.disksinfo AS disks
           ON disks.hostinfo_idx = hinfo.idx
WHERE   hinfo.probetime > @date_shift AND
      hinfo.probetime = (SELECT MAX(probetime) FROM systeminfo.hostinfo AS hi
                                    WHERE hi.probetime > @date_shift AND hi.host_idx = hinfo.host_idx)
GROUP  BY disks.id,
      hnames.name
ORDER  BY hnames.name,
      disks.id;

and i try to execute it in php code. I tried mysql, mysqli and pdo. Ma last code is following:

$db = new PDO("mysql:host=".$this->ip.";dbname=".$this->dbname.";charset=utf8", $this->username, $this->password);
$result = $db->query($query);
$fetch_data = $result->fetchAll(PDO::FETCH_ASSOC);

or

mysql_connect($this->ip, $this->username, $this->password);
mysql_query('SET NAMES utf8;');
mysql_select_db($this->dbname);
$result = mysql_query($query);

PHP connects correcly to the database. Simple queries, like select * from tablename works. But this query returns NO data but error:

1064 : 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 hinfo.idx, hinfo.host_idx, hinfo.processor_load, hinfo.memory_total, ' at line 2

The same query, executed in the database client works perfectly (it's not a slow query, it takes less than 10 ms to perform it). Moreover, when i try to print the query directly from this function on a web page and paste it into mysql client - it works perfectly as well! I can't find any special character in hex viewer i tried to force utf-encoding, nothing.

Any ideas? Test i could perform? Thanks in advance!

Its the chained queries you effectively have 2 queries there. Change your @date_shift to the actual date sub functions and it should work. — Dave, Apr 19 '13 at 15:29
There is **no more support** for `mysql_*` functions, they are [**officially deprecated**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and will be [**removed**](http://php.net/manual/en/function.mysql-connect.php#warning) in the future. You should update your code with [PDO](http://php.net/pdo) or [MySQLi](http://php.net/msqli) to ensure the functionality of your project in the future. — CSᵠ, Apr 19 '13 at 19:06
@ka i know. I just did it for test purposes. If you read my post, i wrote that i use PDO in my code. Thta's not the problem! — Tomasz Szkudlarek, Apr 20 '13 at 11:59

score 2 · Accepted Answer · answered Apr 19 '13 at 16:41

2

Try to execute this as two seperate statements. Running queries from GUI tools or command line tools differ from embedding queries in code. So it works in database tool, but not in php code.

answered Apr 19 '13 at 16:41

Ertunç

819
1
9
21

score 1 · Answer 2 · answered Apr 19 '13 at 16:50

1

The mysql adapter for PHP can only handle one query at a time. You should indeed run the queries separate from each other.

"@date_shift" will be available in the second query, assuming your connection to mysql does not get destroyed between the two queries.

answered Apr 19 '13 at 16:50

mkdevoogd

136
1
4

That's the best idea. And it really works. @date_shift is availble indeed in the second query. I also read that it is possible to switch between results of multiple queries in PDO with use of "nextRowset" method. Didn't try it, but it should work as well. Thanks! – Tomasz Szkudlarek Apr 20 '13 at 12:00

SQL query returns empty result

2 Answers2