使用LEFT OUTER JOIN构建查询结果

Question

There are three tables like this:

store_stock:

ID   ItemWeaveType   ItemModel   Cost   Price   
7    3               4           10.00  15.00

store_item_weaves:

ID   WeaveID
3    MC

store_item_models:

ID   ModelID
4    HV

I am trying to do a query to gather all of the data for item with the stock ID of 7. As a finished result, I would like an array like:

Array ( [ID] => 7 [ItemWeaveType] => MC [ItemModel] => HV [Cost] => 10.00 [Price] => 15.00)

So, I need to join the data from the tables store_item_weaves and store_item_models.

Here is what I have so far:

$query = $db->query("SELECT * FROM `store_stock` s 
left outer join `store_item_weaves` w on w.`ID`=s.`ItemWeaveType` 
left outer join `store_item_models` m on m.`ID`=s.`ItemModel`
where s.`ID`=7");

This returns an array like:

Array ( [ID] => 7 [ItemWeaveType] => 3 [ItemModel] => 4 [Cost] => 10.00 [Price] => 15.00 [WeaveID] => MC [ModelID] => HV )

So, I'm almost there. Instead of using the values of WeaveID and ModelID for ItemWeaveType and ItemModel, it is adding it onto the array.

Any ideas?

Answer 1

Use a columns list instead of just * to make sure you get the values you want:

$query = $db->query("SELECT s.ID, w.WeaveId, m.ModelId, s.Cost, s.Price FROM `store_stock` s 
left outer join `store_item_weaves` w on w.`ID`=s.`ItemWeaveType` 
left outer join `store_item_models` m on m.`ID`=s.`ItemModel`
where s.`ID`=7");