Can a query be made as an array wherein multiple tables are checked for desired values?

Question

Can I write a query in MYSQL (eventually for PHP + MySQL) such that I can check across multiple tables in a sort of array?

For example Suppose I have tables client, car, rate Each table has an ID.

In client are rows ... ID, Name, address In car are rows ... ID, Make, Model, Year, License plate number In rate are rows ... ID, 1-day, 2-day, 3-day

would the following work?:

SELECT name, make, model, license plate number, 1-day FROM client, car, rate WHERE make = ford

or in PHP

$q = "SELECT name, make, model, license plate number, 1-day FROM client, car, rate WHERE make = ford";

would this be correct syntax?

I'm confused as to what you want to achieve here... There doesn't seem to be anyway of linking these tables together..? — Matt Humphrey, Oct 15 '12 at 13:55
You need to chagne your DB structre so those tables will have `relations`. — Ofir Baruch, Oct 15 '12 at 13:56

score 1 · Accepted Answer · edited May 23 '17 at 11:56

No, you can´t. Not the way you are presenting it here. What you can do is JOINS if the tables are related to each other. Otherwise you´d probably have to select from one table at the time.

You may also implement an iterative solution in PHP to return from several tables or consider doing it on the database side with a stored procedure.

Normally when using a relational database there are relations between the tables so that you may perform join operations on them as such (a very loose example since I do not know anything about the context in which you are working):

SELECT c.name, c.make, c.model, c.licensePlateNumber FROM car AS c JOIN client AS cl ON c.ClientId = cl.ClientId JOIN rate AS r ON cl.RateId = r.RateId WHERE c.make = 'Ford';

Related: link

Thank you. As the first respondent above mentioned a joins is the preferred method for best results. — shane, Oct 16 '12 at 14:41

score 1 · Answer 2 · answered Oct 15 '12 at 14:46

1

yes you can do that you send your table names as array and id to you query function then do as shown below

function multiple_table_query($tbl_ary,$id)
{
      foreach($tbl_ary as $tbl)
{
    $sql="SELECT * FROM $tbl where id=$id"
    $result[]=mysql_fetch_array($sql)
}
}
 return $result;

but this is not preffered method better to go for sql joins to get accurate and good looking results.

answered Oct 15 '12 at 14:46

Sivagopal Manpragada

1,554
13
33

Thank you. Both this and the method below seem to work. You answer, however, has the PHP code needed and is more concise. (And is perhaps more elegant - not that it really matters; I just like it). – shane Oct 16 '12 at 14:39

Can a query be made as an array wherein multiple tables are checked for desired values?

2 Answers2