MySQL help

[Seanie]

Hiya,

I am trying to create a database for my users to submit to rather than having to manually update the HTML file myself.

Currently I have got so far as being able to add to and edit/delete entries from the database, however now I now need some help with the code that would display the release date list.

As it stands at the moment the static HTML looks something like this.

Monday 29th Sept
Release 1
Release 2
Release 3

Monday 6th Oct
Release 1
Release 2
Release 3

Monday 13th Oct
Release 1
Release 2
Release 3

I was wondering if there was any way of sorting the releases by their release date.

Thanks in advance.

[Floris]

If you insert the time as a timestamp, you can also get it as one. And sort on that table.
Just use PHP to display the timestamp in a human readable format.

[Seanie]

I can get them to list them in order of the date, but I'd like to split them up like we do it at the moment.

[Floris]

Well, you list the dates on ordering on the date, and the releases on the uniqueid so the oldest is listed first or the other way around.

[Sn2]

$lastDate = '';
while ($row = mysql_fetch_assoc($result))
{
    $curDate = date('l d F', $row['timestamp']);

    if ($curDate != $lastDate)
    {
        echo '<br /><strong>' .  $curDate . "</strong><br />\n";
    }

    // echo items here
    echo $row['releasename'] . "<br />\n";

    // Set last date
    $lastDate = $curDate;
} 

That's with it the timestamp in unix format, if you've stored it as a mysql timestamp then you'll need to adjust your query to use the UNIX_TIMESTAMP function on the field.

[Seanie]

okay, i've done that, and put three different release dates into the database and it just shows like this:


01 Jan 1970
IzzyTest
JakeDon't hate..appreciate!!!
Jack Stevensla la la

[Floris]

That is correct, i see a date, then the three results, going from a-z.
If you want to have an order on the release dates that's not from unique id a-z or z-a, you have to order on a different field. Or add a field that lets you change the order.

[Sn2]

What's the structure of your table?

[Seanie]

That is correct, i see a date, then the three results, going from a-z.
If you want to have an order on the release dates that's not from unique id a-z or z-a, you have to order on a different field. Or add a field that lets you change the order.

yes but each one has a different 'release_date' i want to separate them into different lists.

What's the structure of your table?

http://www.vbulletin.com/forum/attachment.php?attachmentid=27624&stc=1&d=1222704869

[Sn2]

Change the release_date column to timestamp type and set the default to CURRENT_TIMESTAMP.

Then in your query when you're choosing the fields do SELECT col1, col2, UNIX_TIMESTAMP(release_date) AS rdate, col3 FROM ... and the code I gave should work fine.

[Seanie]

but the release dates are pre-set to be every monday, wont a timestamp be when the user submits their release?

[Sn2]

It's whatever you tell it to be, but yes by default it would be the current time.

I didn't actually realise that each release would be a Monday and thought it would be whatever time it was entered into the system.

If you're letting the user choose the dates then you could use the code I gave earlier and remove the $curDate variable from it and use the normal field from your result instead. That would work.

[Seanie]

so how would i edit this to show it like

DATE
releases

DATE
releases

DATE
releases

<?php
    include('includes/config.php');
    include('includes/functions.php');
        
    //Open a connection to the database
    $db = mysql_connect($hostname, $user, $pass) or die ('Error connecting to mysql');
    mysql_select_db($name) or die ('Error connecting to mysql');
    
    //generate and execute the sql query
    $query = "SELECT * FROM $release_sched_db";
        
    $result = mysql_query($query)
    or die ("Error in query: $query. " . mysql_error());
    
    $lastDate = '';
    while ($row = mysql_fetch_assoc($result))
    {
        $curDate = formatDate($row->date);
        
        if ($curDate != $lastDate)
        {
            echo '<br /><strong>' .  $curDate . "</strong><br />\n";
        }

        // echo items here
        echo $row['act'] . $row['title'] . "<br />\n";

        // Set last date
        $lastDate = $curDate;
    }      
?>

[Sn2]

 <?php 
    include('includes/config.php'); 
    include('includes/functions.php'); 
         
    //Open a connection to the database 
    $db = mysql_connect($hostname, $user, $pass) or die ('Error connecting to mysql'); 
    mysql_select_db($name) or die ('Error connecting to mysql'); 
     
    //generate and execute the sql query 
    $query = "SELECT * FROM $release_sched_db ORDER BY release_date, title"; 
         
    $result = mysql_query($query) 
    or die ("Error in query: $query. " . mysql_error()); 
     
    $lastDate = ''; 
    while ($row = mysql_fetch_assoc($result)) 
    { 
        $curDate = formatDate($row['release_date']); 
         
        if ($curDate != $lastDate) 
        { 
            echo '<br /><strong>' .  $curDate . "</strong><br />\n"; 
        } 

        // echo items here 
        echo $row['act'] . $row['title'] . "<br />\n"; 

        // Set last date 
        $lastDate = $curDate; 
    }       
?>

You'll probably want to run $row['act'] and $row['title'] through htmlspecialchars() or similar unless you completely trust whoever is entering the data.

[Seanie]

okay THANK YOU So much!

its now split them into the two dates ive got, exept it was formatting the dates to 1970 and 1969, but i sorted that by changing the sql field to datetime!

Thanks!