Select * problem

[edawg]

Hi, I have 2 databases that I want to select from, the first database is the categories one which holds 29 results, and contains an id, title, url, text field ect ect....... the other database is pictures, which contains its own id, an associated id for the categories database, and it holds is own title, picture filename, and description field, my problem is, when I SELECT * FROM categories, pictures      , it gives me all the results in order from 1 - 29 of the categories database, but the pictures database seems to only list the first row of that database repeated until the category database gets to 29, then the pictures database list result number 2, 29 times then 3, 29 times and so on?. what can I do to get them to list each result separately without repeating??.

  I'm trying to build my own php site search, so the problem is when I get a enter a search term, I get 29 results of the same term!!! . Ive tried to use SELECT database.table FROM database to limit the results too, but the same problem arises?.

 

please help:).

 

thanks!

[artacus]

JOIN man, JOIN!

 

What you have now is a Cartesian product. What you want is to JOIN pictures on categories

[fenway]

Yeah, something like:

 

SELECT p.*, c.title 
FROM pictures AS p 
LEFT JOIN categories AS c ON ( c.uid = p.category_uid )

 

Obviously, I've guess all of the column names.

[edawg]

Still the same kind of problem, even when I use the joins??

[artacus]

Then you're not doing it correctly.  Look at fenway's query closely. If you join your foreign key in the pictures table with the primary key of the category table, you will only get category joined once per picture.

[fenway]

It's impossible for this one-to-one relationship to ever give you more rows than there are picture records.