Page 1 of 1

[SOLVED]News Query problem!

Posted: Fri Jun 04, 2004 8:05 am
by Joe
During May I made a brand new news panel where each post is ordered by the date but for some strange reason when it hits June my articles start going all over the place for what reason I do not know. My query go's like:

$sql = "SELECT * FROM news ORDER BY date";

and the php date function go's like:

$date = date("F j, Y");

Can anyone please give me any suggestions to how I can order my posts in an organised manner without all this fuss.

Regards


Joe 8)

Posted: Fri Jun 04, 2004 8:09 am
by lostboy
post code

Posted: Fri Jun 04, 2004 8:11 am
by Joe
$query = "SELECT * FROM news";
$result = mysql_query($query) or die("Mysql Error!");
$username = $_SESSION['username'];
$headline = $_POST['headline'];
$mainnews = $_POST['article'];
$date = date("F j, Y");

$query = "INSERT INTO news VALUES('$username','$headline','$mainnews','$date')";
$result = mysql_query($query) or die("mySQL Error!");
mysql_close();

Ok pal there you go!

*edit* That code is to post news!!! I will do another post in a sec with the actual news page.

Posted: Fri Jun 04, 2004 8:17 am
by Joe

Code: Select all

$link = mysql_connect("???", "???", "???");
 mysql_select_db("???") or die("couldnt connect" . mysql_error());
 $sql = "SELECT * FROM news ORDER BY date"; 
 $result = mysql_query($sql) or die(mysql_error());
 $i = 1;

 while  ($row = mysql_fetch_assoc($result))
 {
 $headline = $row['headline'];
 $username1 = $row['username'];
 $mainnews = $row['mainnews'];
 $comments = $row['comments'];
 $date = $row['date'];

  if ($i % 2 == 0)
  $bg = "#eeeeee";
  else
  $bg = "#e1e1e1";
  $i ++;
  echo "<tr><td bgcolor=".$bg.">";
  echo "<font face='verdana' size=1>";
  echo "<b>.:$headline:.</b> Posted By: $username1 - <i>$date</i><br><hr color='black'>";
  echo "$mainnews<p>";
  echo "<a href='changes/editnews.php?edit=$headline'>Edit Post</a> | <a href='addcomment.php?article=$headline'>View Comments</a>";
 }
OK theres the code to view the news!

edit patrikG: added

Code: Select all

for readability.

Posted: Fri Jun 04, 2004 9:07 am
by Joe
LOL thanks man i never even thought! ;) Any help though as I seem to be struggling still! Thanks...

Posted: Fri Jun 04, 2004 9:19 am
by patrikG
what's the date-format in the database? A normal timestamp?

Posted: Fri Jun 04, 2004 9:21 am
by Joe
The database column is actually set as text and I use the function $date = date("F j, Y"); to stamp the date into the column so it outputs like June 4, 2004. Thanks

Joe 8)

Posted: Fri Jun 04, 2004 9:25 am
by evilmonkey
Why not just give each post an ID in order (i.e. when the lastest news is added, it will automatically have the last ID.) Order the news by ID in descending order. To methat's what makes the most sense. So your query will look like this:

Code: Select all

$sql = "SELECT * FROM news ORDER BY id DESC";
Be sure to add an 'id' column to your database, and set it to auto_increment in phpmyadmin.

The alternative is to put a timestamp in the database, or just put the return of the time() function (the seconds since the UNIX epoch) for comparison. You will still need it in descending order. Personally, I think the id system is the easiest way.

Good luck!

Posted: Fri Jun 04, 2004 9:28 am
by Joe
Yes evilmonkey i could do that but then theres the confusion of deleting rows from the table due to me ordering by the ID. I cannot explain this to well but i tried it and failed completly. Any suggestions.
The alternative is to put a timestamp in the database, or just put the return of the time() function (the seconds since the UNIX epoch) for comparison. You will still need it in descending order. Personally, I think the id system is the easiest way.
Now I like the sound of the timestamp method. I will give it a try.
All the best

The lil man Joe 8)

Posted: Fri Jun 04, 2004 9:43 am
by patrikG
the way you've formatted your date e.g. "May 2, 2004" and "June 4, 2004" results in ORDER BY sorting your results correctly. "J" for "June" comes before "M" for "May" - either sort by ID or change the date-format to either timestamp or something like: yyyymmddhhiiss, .e.g 20040604130434 (year-month-day-hours-minute-second).

Posted: Fri Jun 04, 2004 10:04 am
by Joe
Thanks alot for the help everyone and yes i have managed to succeed. What I done was used the microtime() function and placed it in a seperate column so everything which is ordered is ordered by the time. Great :)

All the best


The lil man Joe 8)