SQL statement will not work in PHP but works in MySQL

select query in php mysql with example
mysqli_query not working
select query in php with where clause
mysql_query(insert)
php net mysql
php mysql fetch
mysql query
mysqli insert query

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

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.

SQL statement will not work in PHP but works in MySQL, In the case where you pass a statement to mysqli_query() that is longer than max_allowed_packet libmysqlclient on all platforms returns an error code 2006​. This may or may not be obvious to people but perhaps it will help someone. When running joins in SQL you may encounter a problem if you are trying to pull two  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

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

mysqli::query - Manual, When using UPDATE, MySQL will not update columns where the new value is the same as the old value. It works also for REPLACE query,returning: the number of affected rows will be 0 normally but 1 with CLIENT_FOUND_ROWS. there was a problem with the sql statement) the number of lines affected by that  MySQL NOT IN () makes sure that the expression proceeded does not have any of the values present in the arguments. expr NOT IN (value,) If you want to fetch the rows from the table book_mast which contain such books, not written in English and the price of the books are not 100 or 200, the following statement can be used.

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!

mysql_affected_rows - Manual, mysql_query() sends a unique query (multiple queries are not supported) to the a MySQL query can be misleading if you use echo($sql) in PHP because the null Dunno if is it a bug but when you are working with replications servers and​  Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

mysql_query - Manual, If you don't do this, you will get ugly problems in case other software is reading This is not efficient but is sufficient for my purposes for now. to run exactly the same SQL statement 2+ times and you have the "MySQL has gone away" error a lot. MYSQL_CLIENT_SSL is not working, use MySQLi and mysqli->ssl_set(). The link you posted to the manual is mysql i _multi_query, so it will not work by directly changing mysql_query to mysqli_multi_query as the whole block of code would need to be rewritten in

mysql_connect - Manual, PHP MySQL Prepared Statements Prepared statements basically work like this​: If the original statement template is not derived from external input, SQL  Update the question so it's on-topic for Database Administrators Stack Exchange. SQl comand works in Workbench but not from Java Program MySQL Triggers not

PHP MySQL Prepared Statements, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java  The SQL SELECT statement is used to select the records from database tables. To select all columns from the table, the character is used. Let us consider the following table ‘ Data ‘ with three columns ‘ FirstName ‘, ‘ LastName ‘ and ‘ Age ‘. To select all the data stored in the ‘ Data ‘ table, we will use the code mentioned

Comments
  • Provide us your PHP code
  • pls show your php code for the mysql query..
  • Edited to show code. Thanks.
  • are you connected to the correct dbspace? what is error output?
  • There is no error. The connection is the same - all SQL statements in the code share the same DB connection and database connection - and they all work. This one is running - with no errors being returned.
  • Actually.. it returns zero results. I.e., the JSON object shows 0 records. I tried echoing mysql_error() and it did not contain anything. Yeah.. msqli is the next move. But for now, this is still a mystery.
  • What do you mean by this the JSON object shows 0 records? There isn't a single mention of JSON in your code. Please update your question with all the relevant parts of your code.
  • At the end of the code - I convert the results arrays to JSON. I will edit the question again.
  • Add echo mysql_error(); after the mysql_query() call and then update your question with any output.
  • There is no output. I tried that. Nothing is returned in the echo of mysql_error();