Page 1 of 2

Mysql question

Posted: Sun Jan 25, 2004 5:11 pm
by John Cartwright
Okay I'm making a bunch of forms that are stored in a mysql page and then are later called up with viewing a page. The page is a releases table which looks like this

September
04 - blah
29 - gjkd

November
03 - Kfjsdkl
30 - dkfjslk

October
03 - kdjfsdf

I need to set this up so each month has the date going from lowest to highest. I'm just wondering setting the type to "date" can I use something like this

Code: Select all

<?php $result = @mysql_query("SELECT * FROM releases ORDER BY `date` DESC LIMIT 10"); ?>
Also I'm a bit confused, how I can only make the months with dates set appear?

Also for the form, how would I go about doing this

Field for the description, field for the date, and a list to choose from for the month. Depending on the month, it goes into the appropriate table.

Posted: Sun Jan 25, 2004 6:17 pm
by John Cartwright
up

Posted: Sun Jan 25, 2004 6:35 pm
by microthick
I think you're making this more complicated than it really is.

You obviously have a News table of some sort. You only need to store the date and not any month specifically (since it's inherently stored in the date).

Your query looks good as well, but it's your looping through the queries that'll have to be tweaked.

Code: Select all

<?php $result = @mysql_query("SELECT * FROM releases ORDER BY `date` DESC LIMIT 10"); ?>
Normally, when you output it'll look like this:

Code: Select all

<?php
while ($row = mysql_fetch_array($result)) {
   // store the two different date "things" you want to display.
   $month = date("F", $row["date"]); // eg. February
   $day = date("d", $row["date"]); // eg. 04
   echo $day." - ".$row["newsreleasetitle"]."<br>";
}
?>
This on its own should output:

04 - twrg
06 - ey4y
03 - 46436
17 - 4634634

but it doesn't yet output month.

Taking the same code, we can do this:

Code: Select all

<?php
while ($row = mysql_fetch_array($result)) {
   // store the two different date "things" you want to display.
   $month = date("F", $row["date"]); // eg. February
   $day = date("d", $row["date"]); // eg. 04
   echo $month."<br>";
   echo $day." - ".$row["newsreleasetitle"]."<br>";
}
?>
But it will then look like:

February
04 - twrg
February
06 - ey4y
June
03 - 46436
July
17 - 4634634

Which is still not really what you want.

So what you need to do is store the $pastmonth so that you only output a specific month name once.

Code: Select all

<?php
$pastmonth = "" // so that the first month always gets outputted.
while ($row = mysql_fetch_array($result)) {
   // store the two different date "things" you want to display.
   $month = date("F", $row["date"]); // eg. February
   $day = date("d", $row["date"]); // eg. 04
   if ($pastmonth != $month) {
        echo $month."<br>";
   }
   echo $day." - ".$row["newsreleasetitle"]."<br>";
   $pastmonth = $month; // set the current month to the past month.
}
?>
This should then output:

February
04 - twrg
06 - ey4y
June
03 - 46436
July
17 - 4634634

No code has been tested. But theoretically, that's how it might work.

Posted: Sun Jan 25, 2004 6:48 pm
by John Cartwright
Thanks alot :D :D :D

Posted: Sun Jan 25, 2004 6:59 pm
by John Cartwright
Sorry now that I actually am picking apart the code understand it I'm wondering how am I supposed to store this information and how I am going to seperate the dates for each month.

Code: Select all

$month = date("F", $row&#1111;"date"]); // eg. February 
   $day = date("d", $row&#1111;"date"]); // eg. 04
What does "F" represend and "d" represent. They both are related back to "date" also. But don't I want to store these as seperate fields???? Maybe you can sort dates like Febuary 20th and December 20th, it can tell that Febuary is first..... that's what I'm assuming by you keeping the month and day in the same field... :S

All a bit confusing to me

TY

Posted: Sun Jan 25, 2004 7:26 pm
by microthick
I'm assuming you're storing everything as a unix timestamp.

The PHP function date() takes in two arguments, a string that tells PHP how to output the date and a unix timestamp.

If you read up on date() you'll see there's a huge list of letters that represent different ways you can output a date.

For example, F just grabs the full month from the date and d just grabs the numeric day of the month. You could even go date("F d", $row["date"]) to display February 04.

So, if you store the date in one field, a datetime field, you can order by date. Otherwise, it's very hard to order by date.

Assuming you have created your table with 4 fields:
newsid int auto_increment primary key
newstitle varchar
body text
date datetime

You can insert from a form using this SQL:

$sql = "insert into sometable (newstitle, body, date) values ('".$newstitle."', '".$body."', now())";

now() sticks in the current date and time into that datetime field we called date. If you were to look at how it looks in the database it might look like: 04-11-2004 12:54:03, to represent the date and time of the insert.

Again, date() just allows us to manipulate what the date looks like when we output it.

Posted: Sun Jan 25, 2004 7:57 pm
by John Cartwright
I'm using the code I was given but every time I input something it ends up december 31......... my mind is hurting im so confused :S :S :S

Posted: Sun Jan 25, 2004 8:10 pm
by microthick
In your database, what do the dates look like?

Posted: Sun Jan 25, 2004 8:14 pm
by John Cartwright
20040125201144 All of them

Posted: Sun Jan 25, 2004 8:27 pm
by microthick
I guess MySQL datetime fields are different from unix timestamps.

To see a tutorial explaining how you can convert from one to the other see this:
http://www.cre8asiteforums.com/viewtopic.php?t=1069

Posted: Sun Jan 25, 2004 8:39 pm
by John Cartwright

Code: Select all

<?php 
$pastmonth = "" // so that the first month always gets outputted. 
while ($row = mysql_fetch_array($result)) { 
   // store the two different date "things" you want to display. 
   $month = date("F", $row["date"]); // eg. February 
   $day = date("d", $row["date"]); // eg. 04 
   if ($pastmonth != $month) { 
        echo $month."<br>"; 
   } 
   echo $day." - ".$row["newsreleasetitle"]."<br>"; 
   $pastmonth = $month; // set the current month to the past month. 
} 
?>
Let's take a look back at this. If I wanted to use this what format would I have to enter the dates in?

Posted: Sun Jan 25, 2004 9:01 pm
by microthick
Good idea.

To use that code, it is assumed that $row["date"] is a unix timestamp.

To make "date" in the database a unix time stamp when it comes out, we'd have to modify the select statement.

Code: Select all

// The query we actually want to make 
$result = mysql_query("SELECT something, somethingelse, date, UNIX_TIMESTAMP(date) as unixdate FROM some_table");
By using the MySQL function unix_timestamp(), we convert date from a MySQL date to a unix timestamp. It comes out of the database as "unixdate".

So, then, $row["date"] would be a MySQL date and $row["unixdate"] would be a unix time stamp.

Posted: Sun Jan 25, 2004 9:08 pm
by John Cartwright
Thanks alot for your help man.. this isnt over I want to learn more :D :D

I'll be back tommorow, I really appreciate your help.

Posted: Mon Jan 26, 2004 7:55 am
by John Cartwright
This whole topic just confuses me sooooooooooooooooo much. GRRRRRRRR, so in the mysql table am I going to have to store 'date' in unix timestamp, but doesn't that mean I have to enter the date in that fashion? How am I to do it if I enter January 5 2004?

TY

Posted: Mon Jan 26, 2004 8:30 am
by microthick
You'd probably want to store it in the database in a MySQL datetime field... so as a MySQL date.

You can either construct the date so that it's in a format that MySQL likes OR you can insert the current date/time using the MySQL function now() if that's sufficient.

Constructing the date means concatenating the day, month, and year into one string. Using the PHP function mktime() you'd create a unix datetime stamp. Then using date(), you'd format that unix date time stamp in the way the MySQL database wants the string to look.

For example, using date to create the current time (instead of using now()) might look like this:
$current_date = date('Y-m-d %H:%M:%S');

Then, you'd insert the current date into the date field.

It's important that the date field in the database is of type datetime though.