Help a PHP/MySQL beginner please ??

[Mulligan]

Trying to get a Tutorial to work from "Web Designer" magazine...
Its a Flash image uploader/browser (MySQL, PHP back-end).
I'm OK with Flash and it KINDA works, but I think there's something wrong with the PHP...

Here's the SQL i was given to start with...

CREATE TABLE `images` (
  `id` int(6) NOT NULL auto_increment,
  `image` varchar(30) NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;

INSERT INTO `images` VALUES (1, 'light1.jpg', '2006-09-04');
INSERT INTO `images` VALUES (2, 'light2.jpg', '2006-09-05');
INSERT INTO `images` VALUES (3, 'light3.jpg', '2006-09-06');

Now here's the "browser" part of the PHP

<?php
$dbh=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("yourDB");
$qr = mysql_query("SELECT * FROM images ORDER BY `date` DESC");
$nrows = mysql_num_rows($qr);
$rString = "n=".$nrows;
for ($i=0; $i < $nrows; $i++) {
$row = mysql_fetch_array($qr);
$rString .= "&id".$i."=".$row['id']."&image".$i."=".$row['image'];
}
echo $rString;
?>

Now, the tutorial says when you run the PHP in your browser you SHOULD get:

n=3&id0=1&image0=light1.jpg&id1=2&image1=light2.jpg...etc etc

BUT when I run it, the IDs and image order seems to be messed up or reversed (id0=3, image0=light3.jpg etc). The result is the Flash runs OK(ish) but this order problem really messes things up once you upload a new image (i've not shown the PHP for that), I'm guessing cos the Flash code works on increments when you browse through the images.
Also, I don't know much about SQL, but is that Auto Increment right of 11 ?? Maybe that's wrong too??


Hope this isn't too lengthy, I think i've jumped in the deep end with PHP here!! :)
Many thanks,
Ross.

[joshi_v]

If you provide the output of the PHP what is displaying in browser it will be more helpful than how it should be .

By the way are you sure than u want to increment id by 11 each time? When you define an integer column as 'auto_increment' coulmn there is no need to pass that value in the insert query.

Cehck these two things .if still u face the same problem come ip with teh output next time.

Cheers!

[Mulligan]

Yeah, Sorry, here's what the PHP produces in the browser...

n=3&id0=3&image0=light3.jpg&id1=2&image1=light2.jpg&id2=1&image2=light1.jpg

So I THINK both the id values and image values are wrong way round..?

And yes, I immediately thought that value of 11 in the SQL was odd, even though I've never done any before (but thats from the magazines Tutorial CD!!!). Will try deleting that anyway, see what happens...

Ross

[joshi_v]

Sorry! Didn't observe the query as it seems to be good in syntax.
The output you are getting is because of your query only! See the  order by part.it is in desc order by date.
So the out showing was Fine.

Don't mind i want to give u a suggestion as you are new to PHP.
When you are displaying mysql records if you display in present way ,easily u will get confused.
Try to display all the rows one by one.don't append it to previous rows.so that u can understand easily what is going on.

Replace and with you for loop with this and..give it a try.

[code]
for ($i=0; $i < $nrows; $i++)
{
  $row = mysql_fetch_array($qr);
  echo 'Counter Value:  '.$i.' ID Value: '.$row['id'].' Image Name:  '.$row['image'].'<BR>';
}
[/code]

You will observe the output row by row!

Cheers,
Joshi.

[Mulligan]

Yeah thanks for that, that will make debugging a lot easier!
As for it not working, as you say, that order is fine..
I think it only messes up when you UPLOAD a new image to add to the database... it adds one ok, but also adds an empty one as well for some reason, and that 11 increment is something to do with that I think, I'll take another look at it anyway... almost there!!

Thanks for the help,
Ross.

[jsladek]

As Previous poster mentioned
Change this line:

[code]$qr = mysql_query("SELECT * FROM images ORDER BY `date` DESC");[/code]
to this:
[code]$qr = mysql_query("SELECT * FROM images ORDER BY `date` ASC");[/code]

And the output will match what the book has.  I doubt it will make much a difference except for the order of the pictures displayed.  As long as the row id and pictures line up all should be well.

Regards,
John Sladek

[Mulligan]

Yeah I think just that date order is all that's different, in other words, it works OK... BUT I still think the main problem is something to do with the SQL...
Is it that auto increment thing? If so, what is that doing? (in layman's terms please).
After you upload a new image, using the Flash front-end (lets assume the Flash actionscript is OK for now), the database table adds the new image, but also adds a blank record with no image (so there's 5 records in total). Then when you use the Flash front-end to browse through the images, It stops showing any of the images (so I guess the incrementing is messed up that the Flash AS is trying to process?)
Any ideas??

Ross.

[jsladek]

Are you saying when you browse the database the information that was added by the upload is not there?  Or do you see the new data and the image is not displayed? 

If the first case is true then there is an issue with how the program is inserting records into the database.

If the second is true, maybe the file is not being uploaded to the correct directory or there is a permission issue on that directory preventing the image from being stored there.  I'm assuming you are not storing the images as BLOBs in the database.

-John

[Mulligan]

No, as far as I can tell, it adds the chosen image fine, BUT also adds another record that is blank (no image).

Ross.

[joshi_v]

Show us the code of inserting values into table!