CODEWITHSUNDEEP
phpmysql
Archie Zineg
Archie Zineg

Reputation: 145

JOIN inside while loop mysql

I have these tables:

Table transactions

enter image description here

Table addonlist

enter image description here

Table addons

enter image description here

I want to echo all items on addons and see if addon_id from table addons matched the laddon_id from table addonlist with the given transac_id to add a note to the item traced.

I have this code and I can make a note to the items that found in addonlist but it echoes the same item from addons.

    $getaddons = mysql_query("SELECT * FROM addons LIMIT 10");
    while($rows = mysql_fetch_assoc($getaddons)){
        $addonid = $rows['addon_id'];
        $addondesc = $rows['description'];
        $addonprice = $rows['price'];
        $addonstat = $rows['status'];
        $checkaddon = mysql_query("SELECT * FROM transactions t, addonlist al WHERE t.transac_id='44005' and t.transac_id=al.transac_id");
        while($rows = mysql_fetch_assoc($checkaddon)){
            $caddonid = $rows['laddon_id'];
            if(mysql_num_rows($checkaddon) and $addonid == $caddonid){
                echo "$addondesc already in your list"; // NOTE: item is already in your list
            }
        }
        echo "<strong>$addondesc </strong><button>Add to list</button>";
    }

This would be displaying(my expectation):

Coke 1 Litre - already in your list
Tuna Sandwich - already in your list
Hotdog Sanwich - add button
Chicken Sandwich - add button
Ham & Egg Sandwich - add button
Ham & Cheese Sandwich - add button
Grilled Cheese Burger - add button
Clubhouse Sandwich - add button
Goto - add button
Arrozcaldo - add button

But what it displays:

Coke 1 Litre - already in your list
Coke 1 Litre - add button `// This wouldn't be appearing`
Tuna Sandwich - already in your list
Tuna Sandwich - add button `// This wouldn't be appearing`
Hotdog Sanwich - add button
Chicken Sandwich - add button
Ham & Egg Sandwich - add button
Ham & Cheese Sandwich - add button
Grilled Cheese Burger - add button
Clubhouse Sandwich - add button
Goto - add button
Arrozcaldo - add button

EDIT:

Please let me know if my database structure is bad or if it is just my codes.

Upvotes: 2

Views: 262

Answers (4)

Paresh Thummar
Paresh Thummar

Reputation: 928

Try following code

$getaddons = mysql_query("SELECT * FROM addons a LEFT JOIN addonlist al ON a.addon_id = al.laddon_id WHERE a.status = 1");
        while($rows = mysql_fetch_assoc($getaddons)){
            if($rows['transac_id'] == "" || $rows['transac_id'] == NULL)
                $output = "<strong>{$rows['description']}</strong><button> - Add to list</button>";
            else
                $output = "<strong>{$rows['description']}</strong><button> - already in your list</button>";
            echo $output."<br/>";
        }

Upvotes: 0

Barmar
Barmar

Reputation: 781004

Use a LEFT JOIN to get everything in one query. For addons that aren't in addonlist you'll get NULL for the columns from addonlist, and you can test that in the loop.

$getaddons = mysql_query("SELECT a.addon_id, a.description, a.price, a.status, l.laddon_id
                          FROM addons AS a
                          LEFT JOIN addonlist AS l ON a.addon_id = l.laddon_id AND l.transac_id = '44005'
                          LIMIT 10");
while ($rows = mysql_fetch_assoc($getaddons)) {
    $addonid = $rows['addon_id'];
    $addondesc = $rows['description'];
    $addonprice = $rows['price'];
    $addonstat = $rows['status'];
    if ($rows['laddon_id']) {
        echo "$addondesc already in your list";
    } else {
        echo "<strong>$addondesc </strong><button>Add to list</button>";
    }
}

There doesn't seem to be a need to join with transactions, as you're not using anything from that table.

Upvotes: 3

Kunal Mulwani
Kunal Mulwani

Reputation: 1

There is a logical error in your code. Even though the addon item is in your list it is printed again after the inner while loop completes. 

$getaddons = mysql_query("SELECT * FROM addons LIMIT 10");
while($rows = mysql_fetch_assoc($getaddons)){
    $addonid = $rows['addon_id'];
    $addondesc = $rows['description'];
    $addonprice = $rows['price'];
    $addonstat = $rows['status'];
    $checkaddon = mysql_query("SELECT * FROM transactions t, addonlist al WHERE t.transac_id='44005' and t.transac_id=al.transac_id");
    while($rows = mysql_fetch_assoc($checkaddon)){
        $caddonid = $rows['laddon_id'];
        if(mysql_num_rows($checkaddon) and $addonid == $caddonid){
            echo "$addondesc already in your list"; // NOTE: item is already in your list
        }else{
            echo "<strong>$addondesc </strong><button>Add to list</button>";
        }
    }
}

Upvotes: 0

Lance
Lance

Reputation: 654

Try this:

$getaddons = mysql_query("SELECT * FROM addons LIMIT 10");
while($rows = mysql_fetch_assoc($getaddons)){
    $addonid = $rows['addon_id'];
    $addondesc = $rows['description'];
    $addonprice = $rows['price'];
    $addonstat = $rows['status'];
    $checkaddon = mysql_query("SELECT * FROM transactions t, addonlist al WHERE t.transac_id='44005' and t.transac_id=al.transac_id");
    while($rows = mysql_fetch_assoc($checkaddon)){
        $caddonid = $rows['laddon_id'];
        if(mysql_num_rows($checkaddon) and $addonid == $caddonid){
            echo "$addondesc already in your list"; // NOTE: item is already in your list
        }else{
            echo "<strong>$addondesc </strong><button>Add to list</button>";
        }
    }
}

Your issue is that your loop has the "Add to list" line added to every iteration, instead of only the iterations without an $addondesc already in the list.

Upvotes: 0

Related Questions