How can I date the list please?

charter · #1 (**permalink**) Dec 15th, 2004, 18:37

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 database 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 my 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]../oldies/images/singleUSA.gif[/img]</div></td>
<td width="219" rowspan="3" valign="top" bgcolor="#5e90cb"> <div align="center">[img]../oldies/images/eu-flag1.gif[/img]</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>

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.

twlwwks title artist date week no
1 1 3 Do The Know It's Christmas Band Aid II 1990-01-06 9001
2 3 5 When You Come Back To Me Jason Donovan 1990-01-06 9001

There is 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.

Webforumz Staff · #2 (**permalink**) Dec 16th, 2004, 15:54

I've got about 2 mins to answer your question, so let me just point you in the right direction! PHP has some handy date functions for manipulating the date field in mysql.

http://www.php.net/manual/en/ref.datetime.php

The one that probably interests you is date() which allows you to format a date to display in any way you want.
However, there are several ways to store a date in MySQL, so ensure that you're using the method that suits your application best. If in doubt, head over to http://dev.mysql.com/doc/mysql/en/Da..._overview.html

If you're still having trouble when I get back then let me know and I'll try to help some more.

charter · #3 (**permalink**) Dec 17th, 2004, 18:24

Hi Sirkent

I've racked my brains out trying to get it to work with no joy.
So far I have changed my code like this.

After the line with the SELECT I have added this code:

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

Then where the date should be added changed the code from above to:

<td width="194" valign="top" bgcolor="#00FFFF"><div align="right"><font color="#FFFF00" face="Arial Black"><?=$newdate;?></font></div></td>

Your help is much appreciated.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using a list for navigation, can you center a list?	tonyb	Web Page Design	4	May 9th, 2008 16:26
2 Drop down list - auto select depending on choice from first list	ciaranleeper	JavaScript Forum	0	Mar 26th, 2008 10:38
Spacing between list items incorrect when span tag is used in a list item...	MikeTheVike	Web Page Design	5	Apr 18th, 2007 01:52
Changing date format in date picker	AdRock	JavaScript Forum	1	Aug 1st, 2006 17:16
Date in Asp	Gup	Classic ASP	1	Jun 5th, 2006 09:05

		Webforumz.com > Main Forums > Program Your Website > PHP Forum
How can I date the list please?