PHP - mysql_query returns 0 rows
I run a query in php using a function mysql_query()
but it returns null strings whereas when I run the same query in phpMyAdmin I get one string. Not sure what makes things wrong. I created the same table on sqlfiddle with a query
SELECT * FROM cart_discount
WHERE email_counter < 1 AND
cart_time BETWEEN '2013-01-31 00:00:00' AND '2013-02-01 23:59:59'
http://sqlfiddle.com/#!2/cceb7/4
Note where php is causing problems.
This is how the request is generated and executed.
$date1 = date('Y-m-d 00:00:00', strtotime(' -1 day'));
$date2 = date("Y-m-d 23:59:59");
$query = "SELECT *
FROM cart_discount
WHERE email_counter < 1
AND cart_time >= '$date1'
AND cart_time <= '$date2'";
$ssql = mysql_query($query, $con) or die('Problem running query: ' . mysql_error());
if (mysql_num_rows($ssql) > 0) {
//do something
} else {
echo 'No rows found';
}
source to share
Try to print this variable first $date1
and $date2
before executing it with mysql_query
. see what it prints if there is some format mismatch with the database, then change it. perhaps this is the only reason. Beyond this Suggestion:
There are many things you can do to debug your problem. print out the actual request what you are doing. copy it and run the differences in phpmyadmin.compare.
source to share
As a reference from http://php.net/manual/en/function.mysql-fetch-array.php mysql_fetch_array returns an array corresponding to the selected row and moves the internal data pointer forward. So please make sure you don't use this before mysql_num lines.
If you use mysql_unbuffered_query (), mysql_num_rows () will not return the correct value until all rows in the result set have been restored.
Warning :: mysql_num_rows ()
This extension has been deprecated since PHP 5.5.0 and will be removed in the future. Instead, you should use the MySQLi or PDO_MySQL extension. See Also MySQL: Selecting the API Guide and related FAQ for more information. Alternatives to this feature include:
mysqli_stmt_num_rows() PDOStatement::rowCount()
Using SQL_CALC_FOUND_ROWS and FOUND_ROWS () will NOT trigger a race condition for MySQL, as this would largely violate their integer purpose.
The results for using them are actually unique for each communication session as it is not possible for processes to pass anything to PHP. As far as PHP is concerned, each request represents a new connection to MySQL, since each request is isolated from its own process.
To simulate this, create the following script:
<?php
$Handle = mysql_connect( "localhost" , "root" , "" );
mysql_select_db( "lls" );
if( isset( $_GET[ 'Sleep' ] ) ) {
mysql_query( "SELECT SQL_CALC_FOUND_ROWS `bid` From `blocks` Limit 1" );
} else {
mysql_query( "SELECT SQL_CALC_FOUND_ROWS `aid` From `access` Limit 1" );
}
if( isset( $_GET[ 'Sleep' ] ) ) {
sleep( 10 ); // Simulate another HTTP request coming in.
$Result = mysql_query( "SELECT FOUND_ROWS( )" );
print_r( mysql_fetch_array( $Result ) );
}
mysql_close( );
?>
Set the connection and query information to match your environment.
Run the script once with the sleep query string and again without it. It is important that they run them at the same time. Use Apache ab or something similar or even simpler, just open two browser tabs. For example:
http://localhost/Script.php?Sleep=10
http://localhost/Script.php
If the race condition existed, the results of the first script instance would equal the results of the second script instance.
For example, a second instance of the script will execute the following SQL query:
<?php
mysql_query( "SELECT SQL_CALC_FOUND_ROWS `aid` From `access` Limit 1" );
?>
This happens when the first instance of the script slept. If a race condition existed when the first script instance wakes up, the result of executing FOUND_ROWS () should be the number of rows in the SQL query executed by the second script instance.
But when you run them, they are not. The first script instance returns the number of lines of its OWN query, which is:
<?php
mysql_query( "SELECT SQL_CALC_FOUND_ROWS `bid` From `blocks` Limit 1" );
?>
So it turns out that there are no race conditions and all of the solutions presented to combat this "problem" are pretty much unnecessary.
Luck,
source to share