2

How do I get a SQL result that will contain multiple rows into an array:

Example SQL Table:

ID      Cat        LineID      Qty    DealID    Cost
1       Phone      1           2      8941      25.00
2       Phone      2           43     8941      85.00
3       Net        1           2      8941       1.00
4       App        1           1      8941      87.00
5       Phone      1           20     8942      98.00

Would like to be able to return the result like:

$product[Phone][LineID][Qty]
$product[Phone][1][Qty] -- this would return 2

BTW: Within a Cat the LineID would never be duplicated, but it may not alway be the same LineID's - for Example under DealID 8941 there might be LineID for Cat>Phones 1,2,3,4 but under a different DealID there might only be Cat>Phones 4,5

Not sure if I'm barking up the complete wrong tree, basicly I need to loop through all the results under a DealID then cross reference the LineID to get all the information about that LineID from a another table which holds the name, image etc...Then put into a html table along the lines of:

Product Name     Qty     Cost     Line Total
Phone 1           1      85.00    85.00

I hope I have made this clear, if not don't be too harsh!!

Thanks, B.

Improve this question

4 Answers 4

Reset to default
3 
$array = array();
while ($row = mysql_fetch_assoc($query_result)) {
    $array[$row['Cat']][$row['LineID']] = array(
        'Qty' => $row['Qty'],
        'DealID' => $row['DealID'],
        'Cost' => $row['Cost']
    );
}

This will look through your array and create an array of the structure you described. Note that I assume you'll want all the remaining fields available under the two indices you mentioned, not just Qty. Another solution, quicker, but larger in memory, would have been:

    $array[$row['Cat']][$row['LineID']] = $row;

This would have put every value there, including the Cat and LineID values that were just used.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@Dereleased - thanks for you reply - could you give and example of how to reurn a value - say the cost of LineID 1 - I tried print_r etc but it not worky!!!! Ta
I just realized that according to your table certain value combinations used as keys are non-unique and this will not contain all the values, but instead the last values for each unique combination. For example, in the table you provided, ID 5 will overwrite ID 1. Not knowing much about your data, I'm not really able to propose a solution, except perhaps $array[$row['Cat']][$row['LineID']][$row['ID']] = array(
@Dereleases - thanks again - is there complety different, but better way to do this - can only think that shopping cart style solution would work - any thoughts??
What is your goal? What are you trying to do?
@Dereleases -So the overall project is a quoting tool. Quoting part is done with w/ all details added to DB. This is part is reload the quote after it is saved, so the user can recall any of the quotes (i.e. www.url.php?QuoteID=xxx). Withing the DB are number of tables. Orderlines table - this is where all the lines of the quote are saved(as above). The LineID of that table needs to be cross-referenced with another table, depending on what the Cat is, so if the Cat is Phones then the table Phones is the one that holds all the detail of that LineID (name, image etc.). A loop is needed to get..
|
1

Have a look at mysql_fetch_assoc.

http://php.net/manual/en/function.mysql-fetch-assoc.php

Improve this answer

Comments

0

you need to use the php function mysql_fetch_array()

Improve this answer

Comments

0

This is the answer:

$sql="SELECT * FROM Blar";
$result = mysql_query($sql);
$combinedResults = array();
while ($row = mysql_fetch_array($result)) {
    $combinedResults[$row['cat']][] = array( 
    'LineID' => $row['lineID'],
    'Qty' => $row['qty'],
    'SetCost' => $row['Setcost'],
    'MonCost' => $row['Moncost'],
    'Postcode' => $row['postcode']
    );
};
//print_r($combinedResults);
print_r($combinedResults['Apps'][0]['Qty']);

Thanks for your help @Dereleases

Improve this answer

1 Comment

It would have been better if you'd accepted @Dereleases (and upvoted) answer instead if it helped you. That's kinda the point of the Accepting answers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.