LOUDMOUTH Posted June 24, 2009 Share Posted June 24, 2009 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 Quote Link to comment Share on other sites More sharing options...
ldougherty Posted June 24, 2009 Share Posted June 24, 2009 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 Quote Link to comment Share on other sites More sharing options...
flyhoney Posted June 24, 2009 Share Posted June 24, 2009 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']."'" Quote Link to comment Share on other sites More sharing options...
gizmola Posted June 24, 2009 Share Posted June 24, 2009 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. Quote Link to comment Share on other sites More sharing options...
garethhall Posted June 24, 2009 Share Posted June 24, 2009 Maybe some like <?php mysql_query("SELECT tableName.fieldName, tableName2.fieldName FROM tableName, tableName2",$connection) ?> Quote Link to comment Share on other sites More sharing options...
LOUDMOUTH Posted June 24, 2009 Author Share Posted June 24, 2009 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); Quote Link to comment Share on other sites More sharing options...
gizmola Posted June 24, 2009 Share Posted June 24, 2009 Again -- you have 40 tables? Quote Link to comment Share on other sites More sharing options...
LOUDMOUTH Posted June 24, 2009 Author Share Posted June 24, 2009 Yes there are 40 different items tables. 1 for each store, I am trying to show all items tables in one Inventory page. Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 24, 2009 Share Posted June 24, 2009 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. Quote Link to comment Share on other sites More sharing options...
LOUDMOUTH Posted June 24, 2009 Author Share Posted June 24, 2009 I agree that one tables section would be more logical, but was just wondering if there was a way to join them all. Quote Link to comment Share on other sites More sharing options...
gizmola Posted June 24, 2009 Share Posted June 24, 2009 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. Quote Link to comment Share on other sites More sharing options...
Dathremar Posted June 24, 2009 Share Posted June 24, 2009 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. Quote Link to comment Share on other sites More sharing options...
LOUDMOUTH Posted June 24, 2009 Author Share Posted June 24, 2009 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 Quote Link to comment Share on other sites More sharing options...
gizmola Posted June 24, 2009 Share Posted June 24, 2009 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. Quote Link to comment Share on other sites More sharing options...
LOUDMOUTH Posted June 24, 2009 Author Share Posted June 24, 2009 Thanks for all the help sorry I didnt list more info from the start Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.