Natural Join question??

toddg

Hello:
I am trying to accomplish this:

pass gallery.html?galleryID=#
and then have that variable be the key to pull all the data from two tables together.

I thought it was like this:
$sql_text=("SELECT * FROM gallery, photos WHERE gallery.galleryID AND photos.galleryID =" . $galleryID . "ORDER BY photoID DESC");

but I keep getting a parse error unexpected T_STRING...so I must be doing something wrong.

Any advice on how to accomplish this?

Thanks in advance

Todd

Weedpacket

Originally posted by toddg
Hello:
I thought it was like this:
$sql_text_=_("SELECT * FROM gallery, photos WHERE gallery.galleryID AND photos.galleryID =" . $galleryID . "ORDER BY photoID DESC");
Why are you going "="?

You might as well drop the () while you're at it as well - they don't hurt, but they are unnecessary clutter.

And also, I was just wondering what gallery.galleryID was supposed to be equal or not equal to. Do you not mean
gallery.galleryID=$galleryID AND photos.galleryID=$galleryID
?

toddg

Hi again:
actually what I am trying to do is limit the results from both tables to ONLY pull the rows where galleryID is equal to the variable galleryID that was passed through by the URL.

I tried your changes and I am still getting that same parse error. is there another way to do this? or achieve the same results?

Thanks again!

Todd

Weedpacket

Well, one other change to the SQL is that you don't leave a space between $galleryID and "ORDER BY" (it should be " ORDER BY".

That's a SQL error though, and the one you're desribing is coming from PHP, so the wording of your SQL statement at this stage is irrelevant - you'd get exactly the same error if you replaced it with "foo bar=".$galleryID." wibble".

Since the line you give (apart from those two "_" charaters) is valid PHP, the error must be on some line other than the one you gave (maybe in the previous one or two). After all, PHP only gives the number of the line in which it realises there is an error - which isn't necessarily the line that contains the error.

toddg

Hello:

I made some changes and am getting cartesian results...so I am thinking it has to do with trying to pass the variable from the url into the sqlselect statement.

Here is what produces the weird result:

$sqlstring = "SELECT * FROM gallery, photos WHERE gallery.galleryID AND photos.galleryID=".$galleryID." ORDER BY photoID ASC";

Here is what works but doesn't include the variable from the URL:

SELECT * FROM gallery, photos WHERE gallery.galleryID = photos.galleryID ORDER BY photoID ASC;

So, any ideas on what I am doing wrong or another way of using that variable as the limiter across the two tables?

Thanks again.

Todd

Weedpacket

Originally posted by toddg
Hello:

I made some changes and am getting cartesian results...so I am thinking it has to do with trying to pass the variable from the url into the sqlselect statement.

Well I saw that that was what you were going to get (every field from both tables, in every possible combination because there's nothing to indicate otherwise), but far as I could tell that's what you were wanting.

gallery.galleryID AND photos.galleryID=".$galleryID."

I take it gallery.galleryID is supposed to be equal to something? photos.galleryID or $galleryID, perhaps?

toddg

Weedpacket:
I really appreciate your help.

Yes I am trying to do something like this:

http://www.foo.com/gallery.html?ID=1
or
http://www.foo.com/gallery.html?ID=2
or whatever.

Then on gallery.html
I am trying to use the ID=# from the URL string to limit the result set from my select statement on my two tables "gallery" and "photos"

I thought I had it set up to limit, but apparently not.

Basically in "gallery" I am assigning unique id numbers to each gallery as I put it in the database, then in "photos" I am assigning the galleryID that is appropriate for each image.

When I pull data I want to be able to pull galleryName, folderName from "gallery" and photoName, photoLocation, photoDescription and fileID from "photos" to be used in various places on a page I am building.

If you have any thoughts or advice or articles I would appreciate it.

Thanks again.

Todd

toddg

I think I got it...

I changed the code to this:
$sqlstring = "SELECT * FROM gallery, photos WHERE gallery.galleryID=".$galleryID." AND photos.galleryID=".$galleryID." ORDER BY photoID ASC";

so now when I pull the data I am seeing the right result set for at least one of the links.

I'll find out for sure tomorrow when I populate the rest of the data into the table.

Thanks again!!

Todd