SELECT all records but only for particular month

[hotmert]

This code works just fine. But it will be a nightmare once we enter August. Because in August I only want people to see Augusts data the Sept, the Oct you get me!

 

I have looked at loads of examples on the net but I can't see how I can do it. I'm quite happy to change a bit of code each month. I suppose the ideal solution would be for the viewer to pick a month to view from a list and let that selection output that month.

 

Any ideas on getting one months data to display??

<?php
#connect to MySQL
$link = mysql_connect('localhost', 'root', 'root'); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
}

#select the database
$link = @mysql_select_db("gig_list1") or die("Could not select database");


#create the SQL query
$sql = "SELECT * FROM listings ORDER BY 'date' DESC";

#execute the query
$link = @mysql_query($sql) or die("Could not execute SQL query");
?>
<div class="table-holder">
    <table width="100%" border="0" id="details">
<?
#loop through all records
$counter = 0;
while ($row = mysql_fetch_array($link)) 
{
   $rowclass = ($counter % 2 == 0) ? 'style="background-color: #333333"' : '';
   $tableclass = ($counter % 2 == 0) ? 'even' : 'odd';
   ?>
   <div class="table-holder">
   <table width="100%" padding="2" table height="100"id="<?=$tableclass;?>">

   <tr <?=$rowclass;?>>
       <td rowspan="3" width="100" id="event-colour" align="center" style="border-right:2px solid black"><? echo date("D j", strtotime( $row["date"])); ?></td>
      
       <td width="7%" id="event-colour" align="left"><? echo $row["time"]; ?></td>
      
       <td align="left" align="left" id="event-colour"><? echo $row["event"]; ?></td>
       
      <td width="10%" margin="10px"style="border-left:2px solid black" rowspan="3" align="center" id="price-colour"><? echo $row["price"]; ?></td>
      
   </tr>
   <tr <?=$rowclass;?>>
       <td colspan="2" id="bands-colour"> <? echo $row["bands"]; ?></td>
   </tr>
   
   <tr <?=$rowclass;?>>
       <td colspan="2" id="info-colour"><? echo $row["info"]; ?></td>
   </tr>
   </table>
   </div>
   <?
   $counter++;
}
?>
    </table>
</div>

 

MOD EDIT: code tags added

[Pikachu2000]

When posting code, enclose it within the forum's

 . . . 

BBCode tags.

 

If you want it to always return only records from the current month of the current year, this oughta do it. Following the example, you should be able to deduce the format you'd need to use to compare the value to form fields if you want to make it user selectable. This query assumes you have the date stored in a field with DATE or DATETIME data type.

SELECT field1, field2, etc FROM table WHERE DATE_FORMAT( date, '%Y%m' ) = DATE_FORMAT( CURDATE(), '%Y%m' )

[hotmert]

Do i need to assign a variable to that or just type it in? Yes my field is 'date'

[redixx]

Do i need to assign a variable to that or just type it in? Yes my field is 'date'

 

You can either assign a variable to it and then put the variable in the mysql_query() function, or just put the query in the mysql_query() function.

[xyph]

A theoretically quicker MySQL query would be

 

SELECT `col1`, `date` FROM `table` WHERE YEAR(`date`) = YEAR(NOW()) AND MONTH(`date`) = MONTH(NOW())

[hotmert]

Thanks for everyones help on this. 

 

I have not been able to 'make it work', with the code I already have, as I don't know where to put it and I'm never quite sure which bits to replace.  Obvious ones like 'my table name' are easy.  I tried replacing the SELECT statement I already have with the one suggested. xyph: your statement sort of worked I guess but gave zero results.

 

I have to admit I thought the advice might require me to have more knowledge than I do, as some assumptions are being made, and rightly so.

 

Sadly, I think I'm shooting above my weight here and should bug out.

[hotmert]

Ahaaa. A little tweeking, got it working. I guess if I want to name a specific month to vies i just change the = MONTH(NOW()) to = MONTH(july)

[xyph]

You can loop through MySQL results like this

 

I'm going to do things in OOP form. It's a good way to get used to doing things.

 

<?php

$sql = new MySQLi( 'localhost', 'user', 'pass', 'database' );

$query = '
   SELECT DATE_FORMAT(`date`,\'%a %e\') AS `date`, `time`, `event`, `price`, `bands`, `info`
   FROM `table`
   WHERE YEAR(`date`) = YEAR(NOW()) AND
      MONTH(`date`) = MONTH(NOW())
';

$result = $sql->query( $query );
if( $result->num_rows() < 1 ) {
   echo 'No results for this month';
} else {
   while( $row = $result->fetch_assoc() ) {
      echo '<pre>'.print_r($row)."\n".'</pre>';
   }
}
$result->free();

?>

 

 

You want

 

 

SELECT... ... WHERE YEAR(`date`) = 2011 AND MONTH(`date`) = 7[code]


For July, 2011

[hotmert]

Yeah I actually sorted that one myself, must be learning!  Not seen the oop form before although I have seen similar "not working" type stuff (!isset).

 

Again, I see that wonderful display of script and I think to myself where does that fit in to what I have already.  My 'customer' wants people to see 'whats on' for each month he has data for.  So I gotta think of a clever way to do that.

 

Display current month, I now see that thats no good if you want to see whats on next month. And fixing the month ie MONTH('date') = 7 would mean a new web page for every month.  That's what I do now!

 

I think I need to offer the viewer a choice and use that in the query??

[Pikachu2000]

You could use <select> fields for year and month, and build the query string dynamically based on the user input.