PHP script seems to be inserting data incorrectly at random o_O

[jaelle]

I'm sure that's not true, and it's entirely user error, but I have been looking at it for 2 weeks and haven't been able to figure it out.

I'm trying to create a script that uploads an image and then inserts an entry into the SQL database. The most recently uploaded file should be at the top of the database. This seems to work well for awhile, and then it will suddenly insert the newest record in between two older ones. Not good.

My function that takes care of uploading and inserting the info into the database:

function upload($filename, $title, $description) {
global $prefix;
global $dir;
$location = $dir.'/'.$filename;
$now=gmdate('Y-m-d H:i');

$imgDir = opendir ($dir);
while ($file=readdir($imgDir)) {
if ($file!='.' && $file!='..') {
if ($file==$filename) {
print "File already exists.<br>";
$file_exists=true;
break;
}
}
}

if (!$file_exists) {
if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
copy($_FILES['userfile']['tmp_name'], $location);
$result=mysql_query("INSERT INTO ".$prefix."comics (comic_id, comic_image, comic_title, comic_description, comic_date) VALUES ('','$location','$title','$description','$now')");
if (!$result) {
print "Error inserting record.<br>";
} else {
print "Error uploading " . $_FILES['userfile']['name']."<br>";
}
}
}
}

A demo of the script can be found at http://www.jlcreationsonline.com/comicproject/index.php. If you look in the dropdown menu, you can see that the dates are out of order for one comic. :scream:

If anyone has any ideas, please let me know. Any help would be appreciated.

[Hans Kilian]

Data in relational tables do not have an inherent 'order' to them.

If you want them in a specific order, you need to specify that by adding 'ORDER BY xxxx' to your select statement when you retrieve the data.

ORDER BY comic_date DESC

seems like it would do the trick (if you want the newest first)

[jaelle]

Thanks. That makes sense. I'm pretty new to SQL and kind've making it up as I go along.

I tried running the query:

SELECT comic_id, comic_image, comic_title, comic_description, comic_date FROM wo_comics ORDER BY comic_date DESC LIMIT $i,1

where $i is the comic number. This now works. (Yay!) But, it seems to me that there should be a better way to do it, but I don't really know what. Any suggestions, would again, be appreciated. ;)

[Hans Kilian]

The 'better' way is to say SELECT comic_id, comic_image, comic_title, comic_description, comic_date FROM wo_comics ORDER BY comic_date DESC and then loop around fetching the rows one by one. They'll then come in the order you want.

You should be able to use your original code to generate your web page without any other changes than the added order by clause.