-1

i have a database with a table that has 10 items and all of these items have amounts(item_one, item_one_amount). So that's 20 rows in the table.

What i want to do is bind these 2 things in php, the item id with the amount.

Not sure how to go around my problem. Is a double foreach a good idea?

My db structure could also be a problem? My idea is: i have a 10 different bundles. Each of them has different items with different amounts of said items in there. The item id_s i get from another table that has all the information about the item

My db:
    FirstItem, SecondItem, ThirdItem, FourthItem, FifthItem,                  
    SixthItem, SeventhItem, EighthItem, NinthItem, TenthItem, 
    FirstItemID, SecondItemID, ThirdItemID, FourthItemID, FifthItemID, 
    SixthItemID, SeventhItemID, EighthItemID, NinthItemID, TenthItemID, 
    first_item_amount, second_item_amount, third_item_amount, fourth_item_amount, 
    fifth_item_amount, sixth_item_amount, seventh_item_amount, eighth_item_amount, ninth_item_amount, tenth_item_amount
FROM Bundles

My php attempt: 

foreach ($all_ids as &$item_id) {
    if ($item_id == 0){
        break;
    }

foreach ($all_item_amounts as &$item_amount) {
    //do something
    if ($item_amount == 0) {
        break;
    } 
}

 }

For some reason i'm stuck in the loop.

user3614214
  • 1
  • 4
  • Why is it 20 rows? It should be 10 rows with 2 columns, right? Can you show the table contents? – Barmar Feb 20 '17 at 16:23
  • What do you mean by "bind these 2 things"? – Barmar Feb 20 '17 at 16:23
  • I suspect you just need to pivot the table, see http://stackoverflow.com/questions/7674786/mysql-pivot-table – Barmar Feb 20 '17 at 16:24
  • thats the problem, i plan to populate the table with data this way: a certain package contains 10 items with varying quantities, and a different package can have different items with different values, thus the 10 rows for item names and 10 rows for their amounts. – user3614214 Feb 20 '17 at 16:27
  • and by binding these 2 together i mean that each item should have his value binded to him – user3614214 Feb 20 '17 at 16:28
  • not sure how the linked answer has anything to do with my problem. I dont need joints, its a php problem, not an sql one – user3614214 Feb 20 '17 at 16:37
  • Why do it in PHP when you can do it in SQL? – Barmar Feb 20 '17 at 16:48
  • Maybe I'm misunderstanding the question. Show the data, and show the result you're trying to get. If it doesn't match, I'll reopen. – Barmar Feb 20 '17 at 16:49
  • SELECT TOP (200) FirstItem, SecondItem, ThirdItem, FourthItem, FifthItem, SixthItem, SeventhItem, EighthItem, NinthItem, TenthItem, FirstItemID, SecondItemID, ThirdItemID, FourthItemID, FifthItemID, SixthItemID, SeventhItemID, EighthItemID, NinthItemID, TenthItemID, first_item_amount, second_item_amount, third_item_amount, fourth_item_amount, fifth_item_amount, sixth_item_amount, seventh_item_amount, eighth_item_amount, ninth_item_amount, tenth_item_amounta FROM Bundles This is how my database looks. – user3614214 Feb 20 '17 at 16:53
  • I put all the data i get from the tables into a session and try to loop through them:' foreach ($all_ids as &$item_id) { if ($item_id == 0){ break; } foreach ($all_item_amounts as &$item_amount) { //do something if ($item_amount == 0) { break; } } } ' For some reason i get stuck in the loop. – user3614214 Feb 20 '17 at 16:54
  • MySQL doesn't have `TOP`, that's SQL-Server. – Barmar Feb 20 '17 at 16:56
  • Oh, I just noticed that you didn't tag this `mysql`. – Barmar Feb 20 '17 at 16:57
  • Please add a tag for the RDBMS you're using, so the proper people will see the question. Add the sample data to the question, and your attempted code -- there's no code formatting in comments. – Barmar Feb 20 '17 at 16:58
  • Yes, this is sql-server, did not state it because i thought that it did not matter because i thought this was a php issue – user3614214 Feb 20 '17 at 16:59
  • Usually if you can solve it in the database, it's preferable to minimize the communcation between PHP and the DB. – Barmar Feb 20 '17 at 17:04
  • This seems like a very poorly normalized schema. What are all those columns? I thought you said there were only 20 rows in the table, so how can you do `TOP 200` in the query? – Barmar Feb 20 '17 at 17:08
  • Thats just the sql server select command, it will have 20 rows – user3614214 Feb 20 '17 at 17:11

1 Answers1

0

Use an array with all the column prefixes:

$nths = array('First', 'Second', 'Third', 'Fourth', 'Fifth', 'Sixth', 'Seventh', 'Eighth', 'Ninth', 'Tenth');

Then loop over all the rows of the query results, and use a nested loop over these prefixes to get all the columns in the proper groupings.

$results = array();
foreach ($all_rows as $row) {
    foreach ($nths as $prefix) {
        $results[] = array('item' => $row[$prefix . 'Item'],
                           'itemid' => $row[$prefix . 'ItemID'],
                           'amount' => $row[strtolower($prefix) . '_item_amount']);
    }
}

However, I suggest you normalize your design. Instead of 30 columns in each row, put each group in a separate row, with a bundleId that's common to all the rows for the same bundle.

CREATE TABLE Bundles (
    BundleID INT,
    ItemID INT, -- Foreign key to Items table
    Amount INT,
    PRIMARY KEY (BundleID, ItemID)
);

This creation syntax might not be exactly right for SQL-Server, my experience is with MySQL.

Barmar
  • 741,623
  • 53
  • 500
  • 612