Solved

How to Display MySQL Data by date field using PHP

Posted on 2009-06-27
10
349 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now