Sql/php Problem

[starfighter]

I'm trying to select data from 2 tables based on an id. so that if the id doesnt exist on table 1 it queries table 2 , and if it doesnt exist on either table to spit out an error message, but i cant figure out how to get it to return the right results.... any ideas.


Dan

[thevillageinn]

I don't believe you will receive an error message, just an empty recordset, so when programing your interaction with mySQL part of that is to look for an empty recordset, and then proceed from there.

regarding how you want this to happen, if the ID exists in both tables what do you want to do?

are you running a query with php, then checking to see whether you received results, and if not, running a second query on the other table, and then checking to see whether you received results, and if no on both counts, returning an error message?

*or*

are you running a query which will check both tables for a particular ID and return any results, be it recordset or error message?

I am assuming you've read over the syntax for the SELECT statement (http://dev.mysql.com/doc/mysql/en/SELECT.html)?

[starfighter]

Originally posted by thevillageinn@Jun 20 2004, 08:38 PM
are you running a query with php, then checking to see whether you received results, and if not, running a second query on the other table, and then checking to see whether you received results, and if no on both counts, returning an error message?

I am using php to run this with mysql holding the tables. I want to do it this way but, i cant get seem to get the logic right for the whole thing to work. The id's are unique to one of the tables but because of the way the data is given to me there is no way to know whcih table the id might be in. So i have to query each table and find the one which has the id in it.

If i could check all of the tables and have it return the one record that matches the id number in question i would love to do it that way, but I don't know how to do it. If you have any advice or code that you would like to share I would be deeply appreciative of the help. Im trying to build this system for the family business and I'm a little bit out of my league.


Thanks again
Dan

[thevillageinn]

are you familiar with IF statements and checking for query results in php?

if so, you should be able to write an if which incorporates your logic...if the id exists in table a return the record, if not, check table b, if not, check table c and so on until all tables have been queried, or a record has been returned.

I don't spend my work hours at the computer any longer so I'm less and less familiar with the specifics and how to do it in php, otherwise I'd set up an example for you.

[dbmasters]

SELECT a.id,b.id FROM table1 a INNER JOIN table2 b WHERE a.id=xxx OR b.id=xxx

??

[starfighter]

Originally posted by dbmasters@Jun 21 2004, 05:37 AM
SELECT a.id,b.id FROM table1 a INNER JOIN table2 b WHERE a.id=xxx OR b.id=xxx

??
Dan,
Just so i get this correctly, the 2 tables are named residential and land so the command would be:
SELECT residential.id,land.id FROM residential INNER JOIN land WHERE residential.id=12345 OR land.id=12345

So this will get me the one record from the table that has the unique id that I'm looking for?

thevillageinn,
No I'm not all that familiar with how to tell if a query has returned results or has failed due to the id not being present in that table. I can seem to get my if statements in the right order but i can't seem to find any dosumentation on what result is returned from a select query that is either invalid or returns no rows.




Dan

[dbmasters]

no, that will get you any record that has the queried number in either table...if they both do, you will get two returns...

[starfighter]

Originally posted by dbmasters@Jun 21 2004, 11:13 AM
no, that will get you any record that has the queried number in either table...if they both do, you will get two returns...
Dan,
Great that will do exactly what i need, Thanks for the help.


Dan