MySQL query returns empty when run from PHP

Question

The query I am running returns 2 results when run from PhpMyAdmin but returns nothing when run from a PHP script:

SELECT event_contacts.*, tasks.all_forms_in, tasks.drive_folder_empty FROM event_contacts, tasks WHERE event_contacts.eid = tasks.eid AND tasks.all_forms_in = 'y' AND tasks.drive_folder_empty = 'completed' AND event_contacts.email_sent_date IS NULL

My PHP code is as follows:

$query = mysql_query("SELECT event_contacts.*, tasks.all_forms_in, tasks.drive_folder_empty FROM event_contacts, tasks WHERE event_contacts.eid = tasks.eid AND tasks.all_forms_in = 'y' AND tasks.drive_folder_empty = 'completed' AND event_contacts.email_sent_date IS NULL");
    $result = mysql_query($query) or die($query."<br/><br/>".mysql_error());

    while ($row = mysql_fetch_array($query)){ 
         // do stuff
    }
}

Why does it return 2 rows when run from PhpMyAdmin, but dies with "Query was empty" when run from the PHP script?

You are using PHP or PHPmyadmin? `Grid edit, checkbox, Edit, Copy and Delete features are not available.` sounds like PHPmyadmin.. — chris85, May 30 '17 at 20:57
Thanks @chris85. I don't understand the contradiction. What is different? — osakagreg, May 30 '17 at 21:02
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 30 '17 at 21:02
PHPmyadmin is an interface for interacting with mysql. https://en.wikipedia.org/wiki/PhpMyAdmin This `Grid edit, checkbox, Edit, Copy and Delete features are not available` isn't available in native PHP unless you wrote it, and if you did you should know what it isn't displaying because it would fall into a conditional of yours. — chris85, May 30 '17 at 21:03
I still don't understand how the php code contradicts the select statement. I did not write a conditional statement that would change the results. — osakagreg, May 30 '17 at 21:05
If you are using PHP you aren't using `PHPmyadmin`, or perhaps you are using that with the first interaction. I suspected you were using mysql shell and phpmyadmin. I guess it is PHP and phpmyadmin? I don't know, question is unclear to me. — chris85, May 30 '17 at 21:08
When I run it from PhpMyAdmin, I receive 2 rows. But when I run it from the php script, I get 0 rows. I updated the question to try and be more clear. — osakagreg, May 30 '17 at 21:18
@chris85 you were right on to the problem. I made one of those really stupid errors. In my php script, I was connecting to a different database and did not realize it. Thank you! If you can submit that as an answer, I can approve that as the accepted answer. — osakagreg, May 30 '17 at 22:06
That's not a programming issue, I'm going to leave the question as should be closed because it was a typo. — chris85, May 30 '17 at 22:47

score 1 · Answer 1 · answered May 30 '17 at 21:20

1

You need to use $result instead of $query in your while statement;

$query = mysql_query("SELECT event_contacts.*, tasks.all_forms_in, tasks.drive_folder_empty FROM event_contacts, tasks WHERE event_contacts.eid = tasks.eid AND tasks.all_forms_in = 'y' AND tasks.drive_folder_empty = 'completed' AND event_contacts.email_sent_date IS NULL");
    $result = mysql_query($query) or die($query."<br/><br/>".mysql_error());

    while ($row = mysql_fetch_array($result)){ 
         // do stuff
    }

answered May 30 '17 at 21:20

Ümit Aparı

540
2
6
23

THANK YOU! I appreciate it. – osakagreg May 30 '17 at 21:21
Actually, that still creates the same problem. 0 results when run from php script. But 2 results when run from phpmyadmin. – osakagreg May 30 '17 at 21:23
Can you replace your mysql functions with mysqli functions? – Ümit Aparı May 30 '17 at 21:25

score 0 · Answer 2 · answered May 30 '17 at 22:18

In your first

$query = mysql_query("SELECT event_contacts.*, tasks.all_forms_in, tasks.drive_folder_empty FROM event_contacts, tasks WHERE event_contacts.eid = tasks.eid AND tasks.all_forms_in = 'y' AND tasks.drive_folder_empty = 'completed' AND event_contacts.email_sent_date IS NULL");

Must be just a string. Like this:

$query = "SELECT event_contacts.*, tasks.all_forms_in, tasks.drive_folder_empty FROM event_contacts, tasks WHERE event_contacts.eid = tasks.eid AND tasks.all_forms_in = 'y' AND tasks.drive_folder_empty = 'completed' AND event_contacts.email_sent_date IS NULL";

(without the mysql_query() function). You are executing the query in the next mysql_query() call.

In addition, as @Umit Apari said, mysql_fetch_array() must be called with the mysql_query result.

But, today, you shouldn't use mysql_* functions, use PDO functions instead.

MySQL query returns empty when run from PHP

2 Answers2