How to Display MySQL Data by date field using PHP

Use something like this:

select * from TableName where DateCol between curdate() and curdate() + interval 2 week

Select allOpen in new window

The PHP code could be something like this:

mysql_connect('localhost','user','pass');
mysql_select_db('db');
$sql = "select * from TableName where DateCol between curdate() and curdate() + interval 2 week";
$res = mysql_query($sql);
if(!$res) die(mysql_error());
echo '<table>';
$firstrow = true;
while($row = mysql_fetch_assoc($res)) {
  if($firstrow) {
    echo '<tr><th>'.implode('</th><th>',array_keys($row)).'</th></tr>';
    $firstrow = false;
  }
  echo '<tr><td>'.implode('</td><td>',$row).'</td></tr>';
}
if($firstrow) 
  echo '<td>(No data)</td>';
echo '</table>';

Select allOpen in new window

That's good stuff. I may have bit off more than I can chew. Attached you will find a "desired effect" image and the code I used to get there. Basically, I altered the code you sent to me so that I would be able to grab each field from sql and create a new row with it [you will see it in the sample image]. I feel like I am just hacking away. perhaps you have a cleaner method to do what I am attempting to do on my own?

To try to be more clear: I want to place the image to the left -- and then list the other fields to the right. I fear that what I have done will not properly work if there are many rows of data in the sql table.

Again, great code you gave earlier it perfectly answered my question.

<?php
$sql = "select IMG from events2 where DATE between curdate() and curdate() + interval 2 week ORDER BY DATE ASC";
$res = mysql_query($sql);
if(!$res) die(mysql_error());
echo '<div style="width: 100px; height: 100px; padding: 0px; float:left;">';
$firstrow = false;
while($row = mysql_fetch_assoc($res)) {
  if($firstrow) {
    echo '<tr><th>'.implode('</th><th>',array_keys($row)).'</th></tr>';
    $firstrow = false;
  }
  echo '<table height="100"><tr><td>'.implode('</td></tr><tr><td>',$row).'</td></tr></table>';
}
if($firstrow) 
  echo '<td>(No data)</td>';
echo '</div>';
?>
 
 
 
<?php
//mysql_connect('localhost','root','ishmael');
//mysql_select_db('bigread');
$sql = "select DATE, TIME, LOCATION, DESCRIPTION from events2 where DATE between curdate() and curdate() + interval 2 week ORDER BY DATE ASC";
$res = mysql_query($sql);
if(!$res) die(mysql_error());
echo '<div style="width: 450px; height: 100px; float:right">';
$firstrow = false;
while($row = mysql_fetch_assoc($res)) {
  if($firstrow) {
    echo '<tr><th>'.implode('</th><th>',array_keys($row)).'</th></tr>';
    $firstrow = false;
  }
  echo '<table height="100"><tr><td>'.implode('</td></tr><tr><td>',$row).'</td></tr></table>';
}
if($firstrow) 
  echo '<td>(No data)</td>';
echo '</div>';
?>

Select allOpen in new window

example.png

I would do this in one loop and with a single table. There are of course many ways to create that layout, this is just a suggestion:

<?php
$sql = "select IMG, DATE, TIME, LOCATION, DESCRIPTION 
        from events2 where DATE between curdate() and curdate() + interval 2 week 
        ORDER BY DATE ASC";
$res = mysql_query($sql);
if(!$res) die(mysql_error());
echo '<table style="width:550px">';
while($row = mysql_fetch_assoc($res)) 
  echo '<tr><td style="width:100px">'.$row['IMG'].'</td><td>'.
    $row['DATE'].'<br />'.
    $row['TIME'].'<br />'.
    $row['LOCATION'].'<br />'.
    $row['DESCRIPTION'].
    '</td></tr>';
echo '</table>';
?>

Select allOpen in new window

again, good stuff. I am curious, could you explain the code to me? I get the sql side, but the building of the tables in html using php is a mystery to me. if not, thats cool too -- i mean, you answered my question[s] to the "T".

is the magic in "mysql_fetch_assoc"? is that what builds the table in html? is that also the logic behind what i will paraphrase as this, "for every row of data in the mysql table create the html using this template"?

thanks again,
Jason

Before the while loop {} <table> is outputted.

mysql_fetch_assoc fetches individual rows of the query output.

The while "loop" <tr> ouputs for the row

Then $row['key'] outputs the fields in <td></td> where ['key'] is a referenced field in the array of fields represented by the current row pulled from query via mysql_fetch_assoc.

Closes each </tr>.

After loop </table> is outputed.

<!--// output //-->
<table style="width:550px">
<tr>
<td style="width:100px">
</td>
<td>
 dd/mm/yyyy <br />
 4:20PM <br />
 USA <br />
</td>
</tr>
</table>

Select allOpen in new window

>> could you explain the code to me?

NerdsOfTech explained this (thank you!), so I will explain the loop from my previous suggestion, which was slightly different:

echo '<tr><td>'.implode('</td><td>',$row).'</td></tr>';

The implode() function takes two parameters, a separator and an array, and returns a string. This call: implode('-',array(1,2,3)) would return '1-2-3'.

mysql_fetch_assoc() returns the row as an associative array: array('id'=>1,'name'=>'joe','email'=>'joe@company.com').
array_keys() returns the keys as a simple array: array('id','name','email'). This was used to output column headers (which you did not need in this case).

By using '</td><td>' as a separator, you can output the array within table cells:

echo '<tr><td>'.                 # start a row and the first cell
     implode('</td><td>',$row).  # each row column separated by '</td><td>'
     '</td></tr>';               # end of last cell and end of row

Select allOpen in new window

Thank you both! I understand. With that knowledge I have the following questions:

1. Attached is code I am using to do some additional things with this data including styling it [see attached screen shot]. I am curious, though, is there a way I can break up each line and tag it with a <span class="EachIsUnique"></span> so that I can style each line separately? I was able to style the TITLE by using a css first-line pseudo class, but desire to style the other lines as well. I was also able to style the <a> with css. An example would be my desire to change the color of the text in the DESCRIPTION field.

2. You will see that I was also attempting to extract the First 150 characters [successful], but also prevent the truncation of whole words [not successful]. That bit of code is commented out. When un-commented, and the page loads, i get the following error:

Notice: Undefined variable: row in T:\htdocs\BigReadOldSchool\index.php on line 70
...

It would be great to be able to do this. Basically, the intention is to grab the first 150 characters from DESCRIPTION, but end with a complete word without truncating it. I originally had that string of code down where the $row loops through the sql statement, but everything just crapped out on me [so I moved it up and commented it out]. Any ideas?

Thank you,
Jason

<?php
$sql = "select TITLE, IMG, DATE_FORMAT(DATE, '%W, %M %e %Y') AS DATE, TIME, LOCATION, LEFT(DESCRIPTION, 150) AS first150, LINK 
        from events2 where DATE between curdate() and curdate() + interval 2 week 
        ORDER BY DATE ASC";
	//$extract = $row['first100'];
	//$lastSpace = strrpos($extract, ' ');
	//echo substr($extract, 0, $lastSpace).'... ';
$res = mysql_query($sql);
if(!$res) die(mysql_error());
echo '<table class="eventstable" cellpadding="0" cellspacing="0">';
while($row = mysql_fetch_assoc($res)) 
  echo '<tr><td class="img">'.$row['IMG'].'</td><td class="eventscontent">'.
    $row['TITLE'].'<br />'.
	$row['DATE'].'<br />'.
    $row['TIME'].'<br />'.
    $row['LOCATION'].'<br />'.
	$row['first150'].'... '.
	$row['LINK'].
    '</td></tr>';
echo '</table>';
?>

Select allOpen in new window

example2.png

Well done. It is beautiful code. Thank you, and thanks to the other expert for their input as well.

Jason