priory Posted March 8, 2009 Share Posted March 8, 2009 Hi, I'm new to php/mysql (two weeks in ). I have access to a bunch of tables that store temp data, I want to be able to make a permanent storage of some of the data based on http posts from a windows app. The tables are structured like: user_lookup[AvatarName, AvatarKey] basket_lookup[AvatarKey, BasketKey] item_lookup[itemName, ItemKey] temp_basket[basketKey, ItemKey] I would like a table that looks like: backup[AvatarName, BasketKey, ItemName] At the moment I have no trouble with simple queries such as deriving the BasketKey for a given AvatarName etc. But I am having trouble trying to express the following as one query: For this AvatarName (stored in $avatarname), find the associated BasketKey and all ItemName's associated with that BasketKey . Then add ALL the fields to table backup. I tried using a join (which I think it the correct method) but it seems to just mess up. This has been hurting my brain for a while now so any hints would be appreciated. Quote Link to comment https://forums.phpfreaks.com/topic/148504-solved-query-help-populate-1-table-from-multiple-others/ Share on other sites More sharing options...
Mchl Posted March 8, 2009 Share Posted March 8, 2009 Try: INSERT INTO `backup` (AvatarName,BasketKey,ItemName) VALUES ( SELECT ul.AvatarName,bl.BasketKey,il.ItemName FROM user_lookup AS ul CROSS JOIN basket_lookup AS bl USING (AvatarKey) CROSS JOIN temp_basket USING (BasketKey) CROSS JOIN item_lookup AS il USING (ItemKey) WHERE ul.AvatarName = $avatarname ) Quote Link to comment https://forums.phpfreaks.com/topic/148504-solved-query-help-populate-1-table-from-multiple-others/#findComment-779818 Share on other sites More sharing options...
priory Posted March 8, 2009 Author Share Posted March 8, 2009 thanks for your help, I wasn't aware of CROSS JOIN. I tried out your suggestion but for some reason it is throwing up syntax errors. When I use it from a php script mysql_query() I don't get any feedback but trying the SQL directly on the database through phpMyAd throws up syntax errors. Quote Link to comment https://forums.phpfreaks.com/topic/148504-solved-query-help-populate-1-table-from-multiple-others/#findComment-779854 Share on other sites More sharing options...
priory Posted March 8, 2009 Author Share Posted March 8, 2009 modify wouldn't work above for some reason :/ error handling in my php script now returns: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ul.AvatarName, il.ItemName, bl.BasketKey FROM user_lookup AS ul CROSS JOI' at line 1 I am on 5.0.67 btw. Quote Link to comment https://forums.phpfreaks.com/topic/148504-solved-query-help-populate-1-table-from-multiple-others/#findComment-779874 Share on other sites More sharing options...
Mchl Posted March 8, 2009 Share Posted March 8, 2009 Ok... seems I got it wrong See: http://dev.mysql.com/doc/refman/5.1/en/insert-select.html INSERT INTO `backup` (AvatarName,BasketKey,ItemName) SELECT ul.AvatarName,bl.BasketKey,il.ItemName FROM user_lookup AS ul CROSS JOIN basket_lookup AS bl USING (AvatarKey) CROSS JOIN temp_basket USING (BasketKey) CROSS JOIN item_lookup AS il USING (ItemKey) WHERE ul.AvatarName = $avatarname Quote Link to comment https://forums.phpfreaks.com/topic/148504-solved-query-help-populate-1-table-from-multiple-others/#findComment-779883 Share on other sites More sharing options...
priory Posted March 8, 2009 Author Share Posted March 8, 2009 thank you so much. you've saved me from trawling through the reference manual Quote Link to comment https://forums.phpfreaks.com/topic/148504-solved-query-help-populate-1-table-from-multiple-others/#findComment-779988 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.