How can I

TheCase

Hi,

Im trying to do the following and am really stuck on how to achieve this.

This is my SQL structure

CREATE TABLE IF NOT EXISTS shows (
id int(11) NOT NULL AUTO_INCREMENT,
datetime varchar(100) NOT NULL,
presenterid smallint(5) NOT NULL,
presenter enum('0','1','2') NOT NULL DEFAULT '0',
presentercomment varchar(60) NOT NULL DEFAULT 'No Comment',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

Now I want to have the date displayed at the top of the table which I will EXPLODE the datetime field. The format is 01 May 19:00

Now the hardest bit to to create a HTML table to output the data in but I want a new table to start for a new date. How can I go about this?

Many Thanks

bradgrafelman

Why aren't you storing the dates & times as a DATETIME column? You wouldn't store a string in an INT column, so why are dates any different?
Use a temporary variable in your PHP loop that loops through the result set. Check to see if the current row's date matches the date in that temp variable and, if so, simply output the row. If not, the date has changed and you know to a) output the new header/whatever, and b) update the temp variable with the new date.

TheCase

I needed a certain format and as far as I am aware you cannot change the format? Firslty it is backwards for me as I want UK date and I dont want to the year.
Is a good idea, I will try this.

bradgrafelman

TheCase;10981437 wrote:
1. I needed a certain format and as far as I am aware you cannot change the format?

Seems like you missed a very large chunk of Chapter 11 in the MySQL manual: 11.7. Date and Time Functions

Ashley_Sheridan

The dates should be stored in datetime format in your database. If you don't, then you can't sort by date, or have a limit in your query to only retrieve values by a certain date, etc.

Also, PHP has a whole plethora of options for the date() function which allows you to output pretty much any kind of date format you want:

$date_var = strtotime($mysql_date_var);
$us_date = date("m-d-Y", $date_var);
$uk_date = date("d-m-Y", $date_var);

There are other options as well, such as outputting days of the week, week of the year, month names, times, locale offset, etc. Look up the date function in the PHP manual.

TheCase

Yeah I guess this is also a bad habbit. So its bascailly converting from SQL to output correct format then converting back to SQL format to insert or update.

As for the HTML table bit im stuck I got this

 $starting = 0;
  while ($row = mysql_fetch_assoc($query))
      {


$starting++;


echo "	  <table width='80%' cellspacing='0' cellpadding='0' border='1' align='center'>

<tr>
<td align='center' width='18%'><b><p>Date / Time</p></b></td>
<td align='center' width='25%'><b><p>Presenter</p></b></td> 

etc .....

How can I tell it after every 4 rows to break into new table but to keep outputting from where it left off.

bradgrafelman

TheCase wrote:
So its bascailly converting from SQL to output correct format then converting back to SQL format to insert or update.

Nothing wrong with that, really. Display format doesn't have to match internal/storage format (and vice versa).

TheCase wrote:
How can I tell it after every 4 rows to break into new table but to keep outputting from where it left off.

Modulo math (the '%' operator) should be all you need for that.

EDIT: Quick explanation in case you never encountered the modulus operation in an algebra class:

if($i % $num_seperator == 0)
{
    // $i is an even multiple of $num_separator

// in other words, this if() condition is true for every "$num_seperator"-th
// value/iteration
}