I have a database with a table which includes among others, one column of dates (the column is titled 'date') in the format of Y-m-d.
I want to extract all the rows of data which have been logged during the current month, so I am using the following query:
$year_month = date("Y-m");
$query = "SELECT date FROM tracker WHERE date LIKE '$year_month%'";
When I execute the query and attempt to output it visually, I get the error "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 64".
Line 64 is the beginning of the following loop:
while($row = mysql_fetch_array($result, MYSQL_ASSOC)
{
echo "Name :{$row['date']} <br>";
}
I've scoured google and had a look here too, but I can't seem to figure out this seemingly simple problem. As far as I can tell, the actual query is executing just fine.
Any ideas?
** Additional - Call to MySQL Query Below **
$result = mysqli_query($conn, $query);
$conn refers to the following line:
$conn = @mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) or die ('Error connecting to MySQL');
Two things jump out at me:
Are you sure that's the query that goes with that result? Queries like insert and delete do not have results. They return a boolean indicating success.
Are you checking to make sure the query succeeded? After the line where you call mysql_query, make sure you check the result. If it's false, you can call mysql_get_error to find out what went wrong.
EDIT: Okay, your real problem is that you're mixing mysql_* and mysqli_* commands. You need to use one or the other.
Something like this should work nicely...
if(!$result)
throw new Exception("MySQL Error: ".mysql_error()." (#".mysql_errorno().")");
if(mysql_num_rows($result) <= 0)
echo "No results found.";
else
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
echo "Name :{$row['date']} <br>";
If you don't understand the error that you're getting from mysql_error() and mysql_errorno(), then post the output and we should be able to help with it.
What is the data type for the date field, if it is date type, then you should try following :
$query = "SELECT date FROM tracker WHERE year(date)='".date("Y")." and month(date) = '".date("m")."'";
$year_month = date("Y-m");
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)
{
echo "Name :{$row['date']} <br>";
}
$query = "SELECT date FROM tracker WHERE date LIKE '$year_month%'";
should be
$query = "SELECT `date` FROM tracker WHERE `date` LIKE '$year_month%'";
date is a reserved word in sql.
look for more examples here