CODEWITHSUNDEEP
phpmysqlphpmyadmin
Lee Fuller
Lee Fuller

Reputation: 2176

SQL statement will not work in PHP but works in MySQL

Found a few similar questions, but nothing exactly like this... So here goes...

So this SQL statement works perfectly when I paste it into a PHPMyAdmin SQL window, but it returns zero results (no error) when run as a PHP mysql_query statement:

SELECT  distinct action_detail2
FROM    stats_tracking
WHERE   date_entered >= '2013-05-08 00:00:00' AND
        date_entered <= '2013-05-08 23:59:59'  AND
        action_detail1 != '10.0.0.1'
ORDER BY    action_detail2

You would think it would error if there was something wrong with the syntax. As you can see, I'm trying to return all entries from a specific day. And that works perfectly on PHPMyAdmin. Just returns 0 results in a "mysql_query" call.

Here's the code:

// Fix date entries
$fixFrom = explode("-", $_GET['from']);
$FROM = $fixFrom[2] . "-" . $fixFrom[0] . "-" . $fixFrom[1];
$fixTo = explode("-", $_GET['to']);
$TO = $fixTo[2] . "-" . $fixTo[0] . "-" . $fixTo[1];

// Build SQL statement
$SQL_Centers = "
    SELECT      distinct(action_detail2)
    FROM        stats_tracking
    WHERE       date_entered >= '" . $FROM . " 00:00:00' AND
                date_entered <= '" . $TO . " 23:59:59'  AND
                action_detail1 != '10.0.0.1'
    ORDER BY    action_detail2
";

$resultCenters = mysql_query($SQL_Centers); // Run the actual query.

$dataCount_Centers=mysql_num_rows($resultCenters); // Get the count of rows, just in case we need it.

echo $dataCount_Centers;

This is returning a count of 0. NO ERROR. Later in the code I convert all data output to JSON - and all other queries work. This shows no JSON records as well. Again.. NO ERRORS. They're all running through the same DB connection.. etc. Clearly this statement is resulting in no records being returned.

I ECHO'd the SQL statement, then cut and pasted it from Firebug to this question. Then I pasted it into PHPMyAdmin SQL - and ran it. Worked perfectly.

Any help is appreciated.

Thanks

Upvotes: 0

Views: 1753

Answers (3)

i3Codes
i3Codes

Reputation: 11

I know this is several years old (mysqli is now the new normal), but I have had a similar problem. A query worked if I pasted it into phpmyadmin, but not from a PHP script. I had no errors and got absolutely no indication something was wrong other than the lack of updating in my database. So, after deleting and rebuilding my database table by hand (ugh...), it turns out I was using a multi-line query, something the standard mysqli_query doesn't support. Example:

mysqli_query($connection, "SET @x = 0; UPDATE table SET someValue = (@x=@x*2) WHERE someID = 4;");

If you are doing something similar, try using mysqli_multi_query instead:

mysqli_multi_query($connection, "SET @x = 0; UPDATE table SET someValue = (@x=@x*2) WHERE someID = 4;");

... something which now seems embarrassingly obvious. :)

Hopefully this helps someone else in the future!

Upvotes: 0

Burhan Khalid
Burhan Khalid

Reputation: 174624

Just returns 0 results in a "mysql_query" call.

That means you have an error in your query. 0 is a False value.

So, the next step would be to find out what the error is. To do that, print the result of mysql_error().

You should also migrate your code from mysql_* to mysqli or PDO as the old mysql_* methods will be removed from PHP. See this link in the PHP manual for more information.

Upvotes: 2

Ravi
Ravi

Reputation: 31397

First..I would say, please show your php code, meanwhile, you can try this 

SELECT  distinct action_detail2 as "ACTION_DETAIL2" 
    FROM    stats_tracking
    WHERE   date_entered >= '2013-05-08 00:00:00' AND
            date_entered <= '2013-05-08 23:59:59'  AND
            action_detail1 != '10.0.0.1'
    ORDER BY    action_detail2

Upvotes: 0

Related Questions