Solved

How to Display MySQL Data by date field using PHP

Posted on 2009-06-27
10
353 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
0
Comment
Question by:jsvb1977
  • 5
  • 4
10 Comments
 
LVL 39

Expert Comment

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

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24729095
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
 

Author Comment

by:jsvb1977
ID: 24729593
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24729809
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
 

Author Comment

by:jsvb1977
ID: 24730071
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
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24730416
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24730911
>> 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
 

Author Comment

by:jsvb1977
ID: 24732251
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 24732452
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
 

Author Closing Comment

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

Jason
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
hosting images 4 28
php string detection problem 7 34
maybe no no httpd.conf 6 47
Number of hours between date in DB and now 8 17
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question