Some Mysql ordering and form trouble

[Albino]

ok, i have my table, upgigs, with fields in. One field is date, but and upcoming date.

I dont want to use the date field type in mysql, as then it has to be 0000-00-00. I know i can use "l j F Y" to set it, but it never works when i put it in defualt. At the moment i am using a text firld, but then i cant order the gigs by date.

Any ideas?

Also, i have a drop down menu, so you can select two items from it, when i want to edit the gigs, i would like this to show the value already. At the moment, i have :

<?php if ($type = "Skabar") {
	  	echo "<option value='Skabar'>Skabar";
	  } else {
	  	echo "<option value='Other'>Other";
	  }
?>

$type is taken from my mysql database.

Cheers if anyone can help, an amount of NP$ is available

 

[stscac]

Well, we're dealing with getting the mysql query and ordering by the date. So lets take a look, shall we:

Assuming the database locale, username, and password has already been set

<?
$fetch = mysql_query("SELECT * FROM mytable WHERE $type = 'Skabar' ORDER BY DESC");
while ($row = mysql_fetch_assoc($fetch)) {
echo"<option value='Skabar'> $row[data_here];";

}
mysql_free_result($result);

It isn't going to work perfectly, beacuse you need to replace with your information. This is just an example of the MySQL query from PHP you should use.

Hope it is useful.

-Steve

 

[moondog]

Ahhhhhhhh


The classic mistake even made by seasoned veterans.

This:

<?php if ($type = "Skabar") {
          echo "<option value='Skabar'>Skabar";
      } else {
          echo "<option value='Other'>Other";
      }
?>

Should be this:

<?php if ($type == "Skabar") {
          echo "<option value='Skabar'>Skabar";
      } else {
          echo "<option value='Other'>Other";
      }
?>

Note the double '=='

Cheers!

-Bob

 

[Albino]

go on Moondog!!!

cheers

now just for the date problem :D

 

[moondog]

go on Moondog!!!

cheers

now just for the date problem :D

You should use the date type field in the table.

You can easily set the date in the format that mySQL wants by doing it this way (and this is how I always do it):

$today = date('Y-m-d');

That will give you today's date in a YYYY-mm-dd format - which of course, mySQL likes.

The problem with storing dates as a TEXT field is that sorting is then done on a string basis which can lead to the erroneous results that you are seeing.

In the world of strings, a 0 comes after 9, so the date:

2005-09-01

comes after

2005-10-01

because in ASCII land, 0 is after 1, and the sort is done from left to right on the strings.

When I first started coding dates and databases, I went through the same thing as you are right now. Once I got used to using the date function as I described above, everything fell into place.

When you update (or insert) into the database, treat the date as you would any other string (i.e. you must enclose it in quotes).

Example:

$today = date('Y-m-d');

$sql = "INSERT INTO users (name, email, signupDate) VALUES ('$_POST[name]', '$_POST[email]', '$today')";

mysql_query($sql);

I know you said you do not want to use the "date" field in mySQL, but it makes life a lot easier.

Happy coding!

-Bob

 

[Albino]

cheers for that, but the problem is, it isnt the date now.

The dates are for upcoming gigs, so they could be a week in advance, and I would like them to be displayed as:

Thursday 3rd September 2005. (just an example date)

I know how to do this as if it was the date now, just use "l j F Y", but im not sure if the date field type supports this, as it says the default for it is 0000-00-00.

I hope that makes sense.

Todd

 

[moondog]

Todd,

The problem here is two-fold. You want to display the date as "Thursday 3rd September 2005" while you want to be able to store the date in the database and be able to SORT them. The two types are incompatible.

My solution would be to store the date in the database in a 'YYYY-MM-DD' format. That way you can easily sort and do any kind of functions on the dates.

You could convert the 'YYYY-MM-DD' format to the 'l j F Y' easily by using the date() function. Date() supports a second parameter - which is the unix timestamp. To get the unix tiemstamp, you can easily use the mktime() function.

The mktime() function takes several parameters:

mktime($hours, $minutes, $seconds, $month, $day, $year);

Since you are not concerned with hours, minutes, or seconds, you can simply assign those values to 0, thus making the "clock time" midnight. You can pick apart the date that is stored in the database easily enough (substr() and explode() come to mind - among other ways too) to pass the appropriate values to the mktime() function. Thus your code would look something like this:

$sql = "select date from gigs. . . . "; 
$result = mysql_query($sql);
$row=mysql_fetch_assoc($result);
$parts = explode('-', $row[date]); 
# $parts[0] has year, $parts[1] has month, $parts[2] has day

$displayDate = date('l j F Y', mktime(0,0,0,$parts[1], $parts[2], $parts[0]);
echo "The date is: $displayDate";

Display date SHOULD come out to be what you wanted.

-Bob

 

[Albino]

ok, cheers moondog, i can see where you are coming from.

however, i get an error:



Warning: date(): Windows does not support dates prior to midnight (00:00:00), January 1, 1970 in C:\Program Files\Apache Group\Apache2\test\bi_temp\TMPlwq0om7bfp.php on line 42

anyway around this?

EDIT: ok, obviously it is because I havnt set my date properly.

ive put in "2005-09-10"

with 9 being the date and 10 being the month, is that correct?

 

[Albino]

nevermind, thanks for your help, it is all fixed now :D