[SOLVED] Query question

[LOUDMOUTH]

I am trying to show info from more than one Database Table

 

The  'items1' is working but I would like to know how to also display 'items2' 'items3' 'items4' tables in the same area. 

 

mysql_query("SELECT * FROM `items1` WHERE `id`='".$line['itemid']."'");

 

Thanks for any help

[ldougherty]

Depends on what you are trying to select from the other databases and if the data has any correlation to the data you are already selecting.

 

http://tinyurl.com/lecgu9

[flyhoney]

You will need to look into the JOIN functionality of SQL.

 

Not knowing anything about your schema, its hard to help, but maybe something like this is what you are looking for:

 

SELECT * 
FROM `items1`
JOIN `items2` ON `items2`.`id` = `items1`.`id`
JOIN `items3` ON `items3`.`id` = `items1`.`id`
JOIN `items4` ON `items4`.`id` = `items1`.`id`
WHERE `items1`.`id`='".$line['itemid']."'"

[gizmola]

Sounds like your database structure is probably wrong.  How about providing the table layout, and a brief description of what the application is suppossed to do.

[garethhall]

Maybe some like

<?php
mysql_query("SELECT tableName.fieldName, tableName2.fieldName FROM tableName, tableName2",$connection)
?>

 

[LOUDMOUTH]

I want to show 'items1' 'items2' 'items3' and all the way upto 'items40'

 

<?
$result = mysql_query("SELECT * FROM `inventory` WHERE `userid` = '".$user_class->id."' ORDER BY `itemid` ASC");
$howmanyitems = 0;
while($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$result2 = mysql_query("SELECT * FROM `items1` WHERE `id`='".$line['itemid']."'");
    $worked2 = mysql_fetch_array($result2);

[gizmola]

Again -- you have 40 tables? 

[LOUDMOUTH]

Yes there are 40 different items tables.  1 for each store, I am trying to show all items tables in one Inventory page.

[Dathremar]

Why do You have 40 tables for stores?

 

Make one table and name it store, another table name it items (items in stock) and make a table between to tell which store has which items. At least that sound's more logical to me.

 

Joining 40 tables for a report really sounds too much.

[LOUDMOUTH]

I agree that one tables section would be more logical, but was just wondering if there was a way to join them all.

[gizmola]

No you can not join 40 seperate tables that have no relations.  You can Union them, but it's just a really bad design and I can't recommend it. 

 

With that said, the answer is:  UNION.

 

SELECT * FROM item1
UNION
SELECT * FROM item2
UNION

 

etc.

 

The structure of all the item tables needs to be the same.

[Dathremar]

Well You can join them all if You have to join them by something, but as it sounds I think You need UNION of all tables.

Try something like:

 

( SELECT col1, col2, col3 FROM item1 WHERE conditions )

UNION

( SELECT col1, col2, col3 FROM item2 WHERE conditions )

UNION

( SELECT col1, col2, col3 FROM item3 WHERE conditions )

.

.

.

( SELECT col1, col2, col3 FROM item40 WHERE conditions )

 

Have in mind that the number of columns selected should be the same in all selects.

Try reading this for more info.

[LOUDMOUTH]

Best way would be to Make just one ITEMS table and then associate the items to the correct Store Locations? 

 

Thanks for the help guys

[gizmola]

Best way would be to Make just one ITEMS table and then associate the items to the correct Store Locations? 

 

Thanks for the help guys

 

We don't know about your application, but on a basic level, yes.  You have a Store table, with a key (store_id).  One row per store.

 

You might also need an item table from the sound of it. (Key: item_id).  One row per item.

 

Then you have storeItem table.  If for example this track inventory, you'd have something like:

 

 

storeItem

------------

storeItem_id  pk

item_id ---> Identifies the item

store_id ---> Identifies the store

qty  ---> quantity on hand.

 

 

Just a guess given the small amount of info we have from you.

[LOUDMOUTH]

Thanks for all the help   sorry I didnt list more info from the start