How can I date the list please? [Archive]

Click to See Complete Forum and Search --> : How can I date the list please?

charter

12-15-2004, 02:04 PM

Can anyone please help?
I have a flat MySql database which has 100 rows that have the same date and week no.
It is a list for music charts.
These are the databse table fields.
tw lw wks title artist date week no.

It goes through until it has found all 100 of the same date from the search.

The thing is I want it to show the date but just once in a certain place.

Also I want the date to show as dd/mm/yyyy Not as yyyy/mm/dd.

This is the php code of my results page. I have put password where my password is and put it in bold where I want the date to be.

At the moment I am having to enter the date manually.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>usold90</title>
<?php
// create short variable names

if(ini_get('register_globals') != "1")
{
$post = (floatval(substr(phpversion(), 0, 3)) >= 4.1) ? $_POST : $HTTP_POST_VARS;

foreach($post as $key => $value)
{
$$key = $value;
}
}
$searchtype = addslashes(trim($searchtype));
$searchterm = addslashes(trim($searchterm));

if(!$searchtype || !$searchterm)
{
exit('You have not entered search details. Please go back and try again.');
}
$link_id = @mysql_connect("localhost", "root", "password");

if($link_id === false)
{
exit("Error, Could not connect to the system database. Sorry...");
}
mysql_select_db('charts90db', $link_id);
$result = mysql_query("SELECT * FROM `usa` WHERE `{$searchtype}` LIKE '%{$searchterm}%'");
?>
</head>

<body bgcolor="#0000FF"><div align="center">
<table class="table" width="760" height="266" border="0" cellpadding="5" cellspacing="0">
<tr align="center">

<td colspan="4" valign="top"> <div align="center"><img src="../oldies/images/singleUSA.gif" width="389" height="56"></div></td>
<td width="219" rowspan="3" valign="top" bgcolor="#5e90cb"> <div align="center"><img src="../oldies/images/eu-flag1.gif" width="152" height="108" align="middle"></div></td>
</tr>

<tr>
<td width="102" valign="top"><div align="center"></div></td>
<td width="194" valign="top" bgcolor="#00FFFF"><font color="#FFFFFF" face="Arial Black">week
ending </font></td>
<td width="194" valign="top" bgcolor="#00FFFF"><div align="right"><font color="#FFFF00" face="Arial Black">04.01.90</font></div></td>
<td width="93" valign="top"> </td>
</tr>
<tr>
<td height="29" colspan="4" class="years-ago">15 years ago </td>
</tr>
<tr align="left">
<td height="29" colspan="5" class="years-ago">
<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr class="headings">
<tr>
<th class="headings" width="28">TW</th>
<th class="headings" width="53">LW</th>
<th class="headings" width="28">Wks</th>
<th class="headings" width="340">Title</th>
<th class="headings" width="307">Artist</th>

</tr>
<?php
$i = 1;
while ($row = mysql_fetch_assoc($result)) {
$item = array();
$item['tw'] = stripslashes($row['tw']);
$item['lw'] = stripslashes($row['lw']);

// now we can override that
$item['lw'] = ($item['lw']=='new')
? "<img src=\"../oldies/images/new2.gif\" alt=\"new!\" />"
: $item['lw'];

$item['wks'] = stripslashes($row['wks']);
$item['title'] = strtoupper(stripslashes($row['title']));
$item['artist'] = stripslashes($row['artist']);
?>

<tr>
<td width="28" class="tw"><?php echo $item['tw']; ?></td>
<td width="53" class="lw"><?php echo $item['lw']; ?></td>
<td width="28" class="wks"><?php echo $item['wks']; ?></td>
<td width="340" class="title"><?php echo $item['title']; ?></td>
<td width="307" class="artist"><?php echo $item['artist']; ?></td>
</tr>
<?php $i++; } ?>
</table>
</body>
</html>

Everything else works fine.

All Help much appreciated.

CrazyDrummer69

12-15-2004, 02:31 PM

Right now I'm at school working on a project so please excuses the vagueness. You can try something like

if ($row1['date'] == $item['date'])
{
$date = '';
}
else
{
$date = $row1['date'];
}

Not quite sure if that's what you're looking for, hopefully somebody else will come along and clean up, gotta work!

charter

12-16-2004, 04:42 AM

Sorry about any confusion.
I don't want the date when I create the search. I want that date to be that shown on the database as below. I want this to be shown only once placed where it is shown on the original coding.

The date in the database is also configuered as yyyy/mm/dd. It has been configuered this way by using PHP MyAdmin to create the database.

How do I configure it as dd/mm/yyyy on the results page and place it once where it should be as on my above coding.

Here is a screenshot of the first three rows of the database table.
http://www.hart87.freeserve.co.uk/images/screenshot.gif

There are as many as 100 rows with the same date & week no.

The loop part showing tw - lw - Title - Artist work fine. It is just the date & am having trouble with.
I only use the week number for my search purposes (not to be shown).

Everything else works fine.

All Help much appreciated.

CrazyDrummer69

12-16-2004, 09:09 AM

You can do this to re-arrange your date:

$exploded = explode("-", $row1['date']);
$newdate = $exploded[2] . "-" . $exploded[1] . "-" . $exploded[0];

$newdate will return dd-mm-yyyy. Anything else needed? :)

charter

12-16-2004, 11:02 AM

Sorry Crazy Drummer but it's not showing any date on the web page.

I have put the code in like this:

<?php
$exploded = explode("-", $row1['date']);
$newdate = $exploded[2] . "-" . $exploded[1] . "-" . $exploded[0];
?>
<tr>
<td width="102" valign="top"><div align="center"></div></td>
<td width="194" valign="top" bgcolor="#00FFFF"><font color="#FFFFFF" face="Arial Black">week
ending </font></td>
<?php
if ($row1['date'] == $item['date'])
{
$date = '';
}
else
{
$date = $row1['date'];
}
?>
<td width="194" valign="top" bgcolor="#00FFFF"><div align="right"><font color="#FFFF00" face="Arial Black"><?php echo $item['date']; ?></font></div></td>

If this is wrong could you please correct it for me?
I have attached a screenshot as to where I want the date to be (The black Xs are where it should be). At the moment it is not showing.

CrazyDrummer69

12-17-2004, 12:36 PM

try this

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>usold90</title>
<?php
// create short variable names

if(ini_get('register_globals') != "1")
{
$post = (floatval(substr(phpversion(), 0, 3)) >= 4.1) ? $_POST : $HTTP_POST_VARS;

foreach($post as $key => $value)
{
$$key = $value;
}
}
$searchtype = addslashes(trim($searchtype));
$searchterm = addslashes(trim($searchterm));

if(!$searchtype || !$searchterm)
{
exit('You have not entered search details. Please go back and try again.');
}
$link_id = @mysql_connect("localhost", "root", "password");

if($link_id === false)
{
exit("Error, Could not connect to the system database. Sorry...");
}
mysql_select_db('charts90db', $link_id);
$sql = "SELECT date FROM `usa` WHERE `{$searchtype}` LIKE '%{$searchterm}%' LIMIT '1'";
$result = mysql_query($sql);
$date = mysql_fetch_array($result);
$exploded = explode("-", $row1['date']);
$newdate = $exploded[2] . "-" . $exploded[1] . "-" . $exploded[0];

$result = mysql_query("SELECT * FROM `usa` WHERE `{$searchtype}` LIKE '%{$searchterm}%'");
?>
</head>

<body bgcolor="#0000FF"><div align="center">
<table class="table" width="760" height="266" border="0" cellpadding="5" cellspacing="0">
<tr align="center">

<td colspan="4" valign="top"> <div align="center"><img src="../oldies/images/singleUSA.gif" width="389" height="56"></div></td>
<td width="219" rowspan="3" valign="top" bgcolor="#5e90cb"> <div align="center"><img src="../oldies/images/eu-flag1.gif" width="152" height="108" align="middle"></div></td>
</tr>

<tr>
<td width="102" valign="top"><div align="center"></div></td>
<td width="194" valign="top" bgcolor="#00FFFF"><font color="#FFFFFF" face="Arial Black">week
ending </font></td>
<td width="194" valign="top" bgcolor="#00FFFF"><div align="right"><font color="#FFFF00" face="Arial Black"><?=$newdate;?></font></div></td>
<td width="93" valign="top"> </td>
</tr>
<tr>
<td height="29" colspan="4" class="years-ago">15 years ago </td>
</tr>
<tr align="left">
<td height="29" colspan="5" class="years-ago">
<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr class="headings">
<tr>
<th class="headings" width="28">TW</th>
<th class="headings" width="53">LW</th>
<th class="headings" width="28">Wks</th>
<th class="headings" width="340">Title</th>
<th class="headings" width="307">Artist</th>

</tr>
<?php
$i = 1;
while ($row = mysql_fetch_assoc($result)) {
$item = array();
$item['tw'] = stripslashes($row['tw']);
$item['lw'] = stripslashes($row['lw']);

// now we can override that
$item['lw'] = ($item['lw']=='new')
? "<img src=\"../oldies/images/new2.gif\" alt=\"new!\" />"
: $item['lw'];

$item['wks'] = stripslashes($row['wks']);
$item['title'] = strtoupper(stripslashes($row['title']));
$item['artist'] = stripslashes($row['artist']);
?>

<tr>
<td width="28" class="tw"><?php echo $item['tw']; ?></td>
<td width="53" class="lw"><?php echo $item['lw']; ?></td>
<td width="28" class="wks"><?php echo $item['wks']; ?></td>
<td width="340" class="title"><?php echo $item['title']; ?></td>
<td width="307" class="artist"><?php echo $item['artist']; ?></td>
</tr>
<?php $i++; } ?>
</table>
</body>
</html>

charter

12-17-2004, 01:05 PM

Hi Crazy Drummer

Still No Joy.

I have attached an image of what this latest code comes up with (Please ignore the places for images).

If you look at the Top image you will see what I get on the search.
All I get where the date should be (following week ending) are two yellow dashes.
The bottom image shows the MySQL database records.

As you can see the date is in the format YYYY/MM/DD. I want them to be shown as DD/MM/YYYY.

I think i'll soon be bald the rate i'm pulling my hair out.

NogDog

12-17-2004, 01:49 PM

You could let MySQL do the work with its date functions (http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html) to get the desired date format:

$query = "SELECT `tw`,`lw`,`wks`,`title`,`artist`, " .
"DATE_FORMAT(`date`, '%d%/%m/%Y'),`week`,`no` " .
"FROM `usa` WHERE `{$searchtype}` LIKE '%{$searchterm}%'";
$result = mysql_query($query);

charter

12-17-2004, 03:40 PM

Sorry Nog Dog. This doesn't work.
Using this code I don't even get the rest of the results.