Filter via Month

[l3rodey]

Hi Everyone,

 

I am having a little trouble, I have not actually done this before or ever tried it which is why I am struggling so much, I am wanting to order a query via month.

 

For example my database looks like this:

 

jobID (int)

name (varchar 255)
date (timestamp)

 

for example there is 200 results there is 50 each month for the past 4 months... I want to filter the results by just this month so I want:

 

$getEnquiries = mysql_query("SELECT * FROM jobs", $connection);

$TotalEnquiries = mysql_num_rows($getEnquiries);

 

So I am running a query now I can display the results I want fine which is all 200 at the moment, I also get the total number and echo that out later to how many enquiries there are.

 

I tried this which I found on google SELECT * FROM jobs WHERE MONTH(current_time) = 4

 

But yeah... nothing is displaying so I am not sure I don' tactually know what that is doing...

 

Yeah... I honestly just don't know what to do and how to do it... It needs to be via month from 1-29,30,31 and it needs to know the dates... sort of thing... Yep donno.

 

Once I get help on this I will try and work out the week one. If your going to post code please comment what it is this is really confusing. 

 

[requinix]

Well, the field is called "date" so I would expect more like

SELECT * FROM jobs WHERE MONTH(date) = 4

If you're still not getting results, what are some of the actual values from the table?

[l3rodey]

Still fails I get this error 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/therma/public_html/booking/invoice/month.php on line 4

and

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in/home/therma/public_html/booking/invoice/month.php on line 34

The lins are

 

3. $getEnquiries = mysql_query("SELECT * FROM jobs WHERE userID='$userID' AND MONTH(date) = 4", $thermadoor);
4. $TotalEnquiries = mysql_num_rows($getEnquiries);

 

and 

 

34. while($row = mysql_fetch_array($getEnquiries)) {
$clientName = $row['name'];
$clientPaid = $row['paid'];
$clientPrice = $row['price'];
$clientPrice = number_format($clientPrice);
$jobID = $row['jobID'];
$clientStatus = $row['status'];

 

Not sure what is going on

Edited December 4, 2013 by l3rodey

[jcbones]

Those errors are telling us that there is an error in your SQL syntax.  Try this one:

 

SELECT * FROM jobs WHERE userID='$userID' AND MONTH(`date`) = 4

 

By putting backticks around the word date, we are telling MySQL that we are looking for a column named date, and not the date function.

 

*NOTE* normally date is allowed to be used without backticks, but I have had problems with it in the past.  This is why I would try this solution.

If this solution fails, then use:

 

$getEnquiries = mysql_query("SELECT * FROM jobs WHERE userID='$userID' AND MONTH(date) = 4", $thermadoor) or die(mysql_error());

This solution will print the actual error from MySQL back to your screen, (remove before going public for security).