CODEWITHSUNDEEP
phpmysql
shane
shane

Reputation: 134

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

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?

Upvotes: 0

Views: 131

Answers (2)

Sivagopal Manpragada
Sivagopal Manpragada

Reputation: 1634

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.

Upvotes: 1

Marcus
Marcus

Reputation: 8669

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

Upvotes: 1

Related Questions