Mysql question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Mysql question

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

up
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Thanks alot :D :D :D
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

In your database, what do the dates look like?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

20040125201144 All of them
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
Last edited by microthick on Sun Jan 25, 2004 9:23 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
Post Reply