... problem with "while" loops...

[techiefreak05]

im developing aan "item trade system" for a client of mine, and everything up to this point has worked, but the page where you view the offers of the trade, is very strage, but I know i thas something to do with my "while" loops. it only shows the last offer, when it should show them al and organize them by user. right now the database is setup , that when people make an offer, each seperate item is placed in the "trade_offers" table, with the "item_id" and "user_id" ... the following code should work, and it does... but it only shows the LATEST offer ...

<?php
//GET USER ID#'s OF ALL OFFERERS
$query1 = mysql_query("SELECT * FROM trade_offers WHERE trade_id = $_GET[tradeid]") or die(mysql_error());
while ($array1 = mysql_fetch_array($query1)){
$u__id = $array1[user_id];
}

//RETREIEVE USERNAMES FOR DISPLAY PURPOSES ONLY
$query3 = mysql_query("SELECT * FROM members2 WHERE id = $u__id") or die(mysql_error());
while ($array3 = mysql_fetch_array($query3)){
$offerer_Name =  $array3[username];
echo "<h4>Offer made By: $offerer_Name</h4>";
}

///GET THE ITEMS OF EACH PERSON WHO MADE AN OFFER
$query2 = mysql_query("SELECT * FROM trade_offers WHERE trade_id = $_GET[tradeid] AND user_id = $u__id") or die(mysql_error());
while ($array2 = mysql_fetch_array($query2)){
$item__id =  $array2[item_id];
?>
<img src=<?php echo $base_url; ?>/images/user_images/opg_1/items/item_<?php echo $item__id;?>.gif>
<?php
}
?>

 

Thanks a lot in advance!!

[akitchin]

a while() loop will go through each record in the resultset and perform the statement inside the braces.  in this case, you're assigning $u__id to the id in the current row.  you are doing this for every row in the set; each new assignment to $u__id will overwrite the old one, thus leaving you with the last value in the end.

 

in order to process all the records, you need to either work it all into one query, or assign your intermediates (user id and name) to an array and process through each one of those to make your new query.  to bring this down to one query, you can do a simple inner join to obtain the offerer's name:

 

$query = "SELECT trade_offers.*, members2.username AS offerer_name FROM trade_offers, members2 WHERE trade_offers.trade_id='{$_GET['tradeid']}' AND trade_offers.user_id=members2.username ORDER BY something";

 

this will fetch all offers made for the given trade_id, as well as the username from members2 of the offerer for each of these offers.  this will simplify your process to one while() loop and, in general, increase performance.  you should also explicitly SELECT the fields from the table that you actually want; no need to SELECT all fields in the row if you won't use them.

[techiefreak05]

Thanks, how would I put that query into the code?

[Psycho]

Well, your first while loop will exit with the last value assigned to $u__id. I think that's the problem. But, instead of three different queries, why not use just one?

 

<?php

$query = "SELECT trade_offers.item_id, members2.username
         FROM trade_offers
           LEFT JOIN members2 ON trade_offers.user_id = members2.id
         WHERE trade_offers.trade_id = $_GET[tradeid]
          ORDER BY members2.username";

$result = mysql_query($query) or DIE (mysql_error());

while ($record = mysql_fetch_array($result)) {

   if ($username != $record['username']) {

       $username = $record['username'];
       echo "<h4>Offer made By: " . $record['username'] . "</h4>\n";
   }

   echo "<img src=\"".$base_url."/images/user_images/opg_1/items/item_" . $record['item_id'] . ".gif\">\n";
}

?>

 

[techiefreak05]

@akitchin:

your query did not work at alll..

 

@mjdamato:

yours worked perfectly! ONLY ... if each person ofers only ONE item, but multiple are allowed...so if i offered you 2 items.. it shows one at the top of the page.. and the other one, under "Offer made By: techiefreak05"... but like I said its fine if there is only one item offered per person... so, how can we fix it?

[akitchin]

sorry, my condition that caused the inner join was written incorrectly:

 

$query = "SELECT trade_offers.*, members2.username AS offerer_name FROM trade_offers, members2 WHERE trade_offers.trade_id='{$_GET['tradeid']}' AND trade_offers.user_id=members2.id ORDER BY something";

 

if you want to get multiple offers by one user from mjdamato's code, remove the if() within the while() loop.

[Psycho]

@akitchin:

your query did not work at alll..

 

@mjdamato:

yours worked perfectly! ONLY ... if each person ofers only ONE item, but multiple are allowed...so if i offered you 2 items.. it shows one at the top of the page.. and the other one, under "Offer made By: techiefreak05"... but like I said its fine if there is only one item offered per person... so, how can we fix it?

 

Hmm... Would have been nice to see the actual output. The suggestion akitchen made should show the label "Offer made by" for every item that a person offers. I was thinking you wanted to have that label and then all the offers by that person, then a label for the next person and all the items they offered, etc.

 

So, if akitchen's suggestion is what you are after, great. If not, please an example of the current out put and how you want it changed.

[techiefreak05]

Here is my current code:

 

<?php

$query = "SELECT trade_offers.*, members2.username
        FROM trade_offers
        LEFT JOIN members2 ON trade_offers.user_id = members2.id
    WHERE trade_id = $_GET[tradeid]";

$result = mysql_query($query) or DIE (mysql_error());

while ($record = mysql_fetch_array($result)) {

        $username = $record['username'];
        echo "<h4>Offer made By: " . $record['username'] . "</h4>\n";

    echo "<img src=\"".$base_url."/images/user_images/opg_1/items/item_" . $record['item_id'] . ".gif\">\n";
}
?>

 

which shows "Offer made By:XXX" for every item offered, when it should only show "Offered made By:XXX" for the group of the items that the said person offered.

 

EXAMPLE OUTPUT:

when running the code from above, i actually get this exactly, except <item> is the image of the item.

*****************************

Offer made By: brenden

<item>

 

Offer made By: _test_

<item>

 

Offer made By: _test_

<item>

 

Offer made By: _test_

<item>

 

DESIRED OUTPUT:

 

Offer made By: brenden

<item>

 

Offer made By: _test_

<item> <item> <item>

 

 

See how all te items are grouped by the person who offered them?

[Psycho]

Well, what you state that you want is exactly what I think the first code I provided should do.

 

the if statement in my code was there to only show the $username if it was different from the last record. I've changed it slightly, try it now - still should have worked before:

 

<?php

$query = "SELECT trade_offers.item_id, members2.username
          FROM trade_offers
            LEFT JOIN members2 ON trade_offers.user_id = members2.id
          WHERE trade_offers.trade_id = $_GET[tradeid]
          ORDER BY members2.username";

$result = mysql_query($query) or DIE (mysql_error());
$username = "";

while ($record = mysql_fetch_array($result)) {

    if ($username != $record['username']) {
        $username = $record['username'];
        echo "<h4>Offer made By: $username</h4>\n";
    }

    echo "<img src=\"$base_url/images/user_images/opg_1/items/item_" . $record['item_id'] . ".gif\">\n";
}

?>

[techiefreak05]

mjdamato ::your code DID work perfect, but ONLY if a person offerered ONLY 1 item.

[techiefreak05]

i dont know what you did for that second one, but it works!! thanks a lot everybody!!

 

I dont know what I would do without this forum... it's priceless!

[techiefreak05]

um... Could I possibly get one more thing ... I need to get the user id number to process the trade... but when i try $record['id']... it doesnt work... So how can I get the user id number of the offerer? Since I'm not famliar on what the query does... I'm lost. thanks

[Psycho]

Just add the field to the query. Assuming the table you want the record id from is the trade_offers table and the field is record_id, then this is what you want:

 

$query = "SELECT trade_offers.item_id, trade_offers.record_id, members2.username
          FROM trade_offers
            LEFT JOIN members2 ON trade_offers.user_id = members2.id
          WHERE trade_offers.trade_id = $_GET[tradeid]
          ORDER BY members2.username";

 

Change trade_offers.record_id in the first line to the correct table name, field name if that is not correct. Also, please mark this thread as solved if your issue is complete.