Select whole database

[adi123]

I am creating a cart from my website. The items on my site are from different tables in a items database. So I am trying to select from the database instead of a table.

 

The code is below.

 

$sql = 'SELECT * FROM items WHERE id = '.$id;

 

how do i tell php to select data from the whole database rather than selecting a single table in the database.

[fenway]

You need to specify a table.... I don't follow.

[PFMaBiSmAd]

A) Why on earth did you use multiple tables instead of putting all the items into one table? Using multiple tables to hold data that only differs in the type of item/category creates a data management nightmare (see item B) below.)

 

B) The only way you can do this in a single query is to dynamically form (php code) a UNION query using all the different tables (you will need to use a SHOW TABLES query first to get a list of all of the tables) OR you will need to execute a query for each table. Both of these things result in poor performance and take more code to accomplish (you do want to write and debug more php code?)

 

I recommend that you combing all the data into a single table, using a 'category' column to distinguish what type of data it is.

[adi123]

I have multiple tables because, I have a few shops on my site. Each shop having its own table, which is why i was trying to select the database.

[Veteah]

It's sort of an awkward way of doing things. Try something like:

 

$table_array = array('table_one', 'table_two', 'table_three');

foreach($table_array as $key => $value)
{
   $sql = "SELECT * FROM ". $value ." WHERE id = '". $id ."'";
   $run = mysql_query($sql);

   while($row = mysql_fetch_assoc($run)
   {
      $everything_ever[] = $row;
   }
}

 

That will give you a multidimensional array with all the results from all the tables you specify. From there you can get to them by going:

 

foreach($everything_ever as $key => $value)
{
   echo $everything_ever[$key]['field_one'];
   echo $everything_ever[$key]['field_two'];
   echo $everything_ever[$key]['field_three'];
}

[kickstart]

Hi

 

If shops are at all related then reallly store should just be a column on various tables in your database.

 

However if you really do need to do this (and I would recommend redesigning the database) then build up something like this.

 

SELECT *, "Store1" FROM Store1DbName.items WHERE id = $id
UNION ALL
SELECT *, "Store2" FROM Store2DbName.items WHERE id = $id
UNION ALL
SELECT *, "Store3" FROM Store3DbName.items WHERE id = $id
UNION ALL
SELECT *, "Store4" FROM Store4DbName.items WHERE id = $id

 

You will need a list of the databases.

 

All the best

 

Keith

[adi123]

what would be a good idea for the database. If i put all items into the same table between different shops, won't it slow down the performance on the site due to a lot of items in the database??

[Muddy_Funster]

... If i put all items into the same table between different shops, won't it slow down the performance on the site due to a lot of items in the database??

Not nearly as much as it will slow things down trying to query against every table.  MySQL is (more or less) designed to be big.  There is little actual performance drop running a query against a table with 100000 records compared to running one against a table with 100 (ASSUMING the table is built and indexed properly).  Merge your stores into a single table with a store_id number field.  then create a refference table to link store_id to store_name.  this will let you query all your goods, and at the same time let you query single stores with a user frieldy selection of name rather than number.

[kickstart]

Hi

 

Maybe a tiny amount (and I am merrily using mysql databases with ~1m records on a laptop for testing), but nothing like as much as trying to connect to multiple databases.

 

If the data is very separate and will never be compared then yes use a separate database. However most of the time it is just a case of keying things properly.

 

All the best

 

Keith