Sql Problems, Same Field Names In Multiple Mysql Tables?

[Guest]

I have a keyword search that searches multiple DB tables for thumbnail images using UNION ALL. I have two pages, results.php, and view.php.  My goal is to able to click a thumbnail image on results.php and be directed to a larger version of that same image on view.php. The problem is each image in all my tables uses the field name "id" so when I click a thumbnail on results.php I get two different images with the same id from different tables.  I tried changing the id's to different names, but when it was time to pass url parameters I can only choose 1 value. (if you can choose more than 1 I don't know how).  So my question is why are my id's from different tables being grouped together, and how can I change this?

Image Results Page (which works perfect):

SELECT *

FROM table1

WHERE keyword LIKE %colname% OR id  LIKE %colname% 

UNION ALL

SELECT *

FROM table2

WHERE keyword LIKE %colname% OR id  LIKE %colname% 

View Image Page (having problems here):

SELECT *

FROM table1

WHERE id = colname

UNION ALL

FROM table2

WHERE id = colname

[bregent]

Yes, that is going to be a problem - and it's just the beginning of your problems when you do not normalize your data. Your data model is not correct. You should not be storing similar data in 15 tables - it's a really big mistake.

To solve your current problem you would need to include a table identifier in the query results in the Image results page, and pass that to the view page and then use PHP to dynamically create the SQL with the correct table....ugh!

[Rob Hecker2]

Just two things Bregent didn't address:

1. To pass multiple url parameters, use an ampersand, like this:

page.php?first_id=22&second_id=55

2. You can have the same field name in multiple tables. In the query, refer to them with their table name, like this

. . .WHERE table1.id = '$first_id' AND table2.id='$sencond_id'

[bregent]

Rob, you are correct, but that's not really going to help with this particular problem. He is trying to create a master/detail in which a result list is presented and can then be drilled down to the detail. His first master UNION query returns a result set that contains rows from different tables, but with the same ID. Passing the ID to the detail query, which is also a UNION query will not uniquely identify the row/table combination that the user clicks on.

[Rob Hecker2]

Yes, but they were specific questions, and worth knowing. that's all.

[bregent]

Yes, definitely.