SQL statement will not work in PHP but works in MySQL
mysqli_query not working
select query in php with where clause
php net mysql
php mysql fetch
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 . "-" . $fixFrom . "-" . $fixFrom; $fixTo = explode("-", $_GET['to']); $TO = $fixTo . "-" . $fixTo . "-" . $fixTo; // 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.
Just returns 0 results in a "mysql_query" call.
That means you have an error in your query.
0 is a
So, the next step would be to find out what the error is. To do that, print the result of
You should also migrate your code from
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
- 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.
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();