How to Display MySQL Data by date field using PHP

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
jsvb1977Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window

0
Roger BaklundCommented:
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

0
jsvb1977Author 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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Roger BaklundCommented:
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

0
jsvb1977Author 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
0
NerdsOfTechTechnology ScientistCommented:
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

0
Roger BaklundCommented:
>> 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

0
jsvb1977Author 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
0
Roger BaklundCommented:
1. yes, you can wrap each line in a span, like this:

echo '<tr><td class="img">'.$row['IMG'].'</td><td class="eventscontent">'.
    '<span class="TheTitle">'.$row['TITLE'].'</span><br />'.
    '<span class="TheDate">'.$row['DATE'].'</span><br />'.
    '<span class="TheTime">'.$row['TIME'].'</span><br />'.
    '<span class="TheLocation">'.$row['LOCATION'].'</span><br />'.
    '<span class="TheDescription">'.$row['first150'].'...</span> '.
    '<span class="TheLink">'.$row['LINK'].'</span>'.
    '</td></tr>';

2. There are many ways to get the first 150 words without splitting a word. A elegant ways is to use wordwrap() and strtok() in combination:

strtok(wordwrap($row['DESCRIPTION'],150),"\n")

Don't use LEFT(DESCRIPTION,150) first, because then it will think the last word is complete, even if it is cut. If the descriptions are very long, you could use something like LEFT(DESCRIPTION,200) to make sure the last word (after 150 chars) is complete.

http://php.net/wordwrap
http://php.net/strtok

>> Notice: Undefined variable: row in ...

The variable $row is only defined within (or after) the while loop. To execute multiple statements in the loop you must wrap it in {} like this:

while($row = ...) {
  # multiple statements here
}

The {} was omitted in my suggestion because the loop consists of a single echo statement only.
echo '<tr><td class="img">'.$row['IMG'].'</td><td class="eventscontent">'.
     '<span class="TheTitle">'.$row['TITLE'].'</span><br />'.
     '<span class="TheDate">'.$row['DATE'].'</span><br />'.
     '<span class="TheTime">'.$row['TIME'].'</span><br />'.
     '<span class="TheLocation">'.$row['LOCATION'].'</span><br />'.
     '<span class="TheDescription">'.strtok(wordwrap($row['DESCRIPTION'],150),"\n").'...</span> '.
     '<span class="TheLink">'.$row['LINK'].'</span>'.
     '</td></tr>';

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jsvb1977Author Commented:
Well done. It is beautiful code. Thank you, and thanks to the other expert for their input as well.

Jason
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.