[SOLVED]News Query problem!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

[SOLVED]News Query problem!

Post 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)
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

post code
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

LOL thanks man i never even thought! ;) Any help though as I seem to be struggling still! Thanks...
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

what's the date-format in the database? A normal timestamp?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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)
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post 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!
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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)
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post 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).
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

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