1

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');
Improve this question
6

4 Answers 4

Reset to default
2

Two things jump out at me:

  1. 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.

  2. 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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

There sould be results for a SELECT query. Also, I am checking for query errors and that all seems to be working fine.
I think you are correct! It is outputing correctly now :) I don't know much about 'mysql' and 'mysqli', must I apply only one version to all my commands?
That is correct. mysql and mysqli are totally different extensions. They both work with MySQL databases, but they are not the same software.
2

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.

Improve this answer

6 Comments

It comes back with 'No results found.'. I'm not sure why this is?
That means that you don't have any data that matches your query. So, either your query is bad or the data isn't loaded into your database/table. To confirm your query, before mysql_query() put var_dump($query);. That will show you EXACTLY what's being sent to MySQL for a query.
That gives: string(51) "SELECT date FROM tracker WHERE date LIKE '2010-11%'"
I also have plenty of data in the table. I can't see why it doesn't work properly. Could this be anything to do with the way I'm connecting to the database? I have updated the end of the orig question with conn details.
(1) Can you run the query directly in MySQL (ie., in the console or PHPMyAdmin)? (2) If you still get no results found, then try changing the 'date' column name to something else. It might be interfering with the SQL date function.
|
0 
$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

Improve this answer

Comments

0

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>";
}
Improve this answer

1 Comment

Throws the same error as above unfortunately. I think I'm not getting any data back from the query but don't know why.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.