We help IT Professionals succeed at work.

How to Display MySQL Data by date field using PHP

jsvb1977
jsvb1977 asked
on
375 Views
Last Modified: 2013-12-13
I have a table which contains rows of data. One column is Date. I would like to learn how to display this data on a web page using PHP. That seems easy enough -- what I am struggling with is how to display the data filtered by date. For example, I only want to show the rows of data for events which are within two weeks from the current date. I also do not want to show past events.

I imagine the way to do this would be to use a select statement in SQL to display only those rows of data WHERE [DATE] is greater than [todays date] but less than [two eeks from now]. I probably also would need to build the rows on the fly using PHP each time the page loads, right?

Thank you,
Jason
Comment
Watch Question

Top Expert 2008

Commented:
Use something like this:
select * from TableName where DateCol between curdate() and curdate() + interval 2 week

Open in new window

Top Expert 2008

Commented:
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>';

Open in new window

Author

Commented:
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>';
?>

Open in new window

example.png
Top Expert 2008

Commented:
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>';
?>

Open in new window

Author

Commented:
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
NerdsOfTechTechnology Scientist
CERTIFIED EXPERT

Commented:
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>

Open in new window

Top Expert 2008

Commented:
>> 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

Open in new window

Author

Commented:
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>';
?>

Open in new window

example2.png
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Jason
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.