List:General Discussion« Previous MessageNext Message »
From:Jose Julian Buda Date:February 12 2009 4:45pm
Subject:Re: How can avoid 2 selects and 2 while? SOLVED
View as plain text  
----- Original Message ----- 
From: "Brent Baisley" <brenttech@stripped>
To: "Jose Julian Buda" <jbuda@stripped>
Sent: Thursday, February 12, 2009 1:21 PM
Subject: Re: How can avoid 2 selects and 2 while?


> On Thu, Feb 12, 2009 at 10:43 AM, Jose Julian Buda
> <jbuda@stripped> wrote:
>> table_1:
>> product_id      features  .....
>> 1                        aaa
>> 2                        bbb
>> 3                        ccc
>>
>> table_2 :
>> product_id    name   size    price
>> 1                     11        1          111
>> 1                     12       2         221
>> 1                     13        3         331
>> 2                      21       1         112
>> 2                      22       2        222
>> 3                      31      1        113
>> 3                      32       2        223
>> 3                      33      3        333
>>
>>
>> What i want is the next report for example the size =2
>> .............
>> Report of products with size =2
>>
>> Product features : aaa
>> Name   12    Price 221
>>
>> Product features : bbb
>> Name 22     Price  222
>>
>> Product features : ccc
>> Name 32     Price  223
>> ...........
>>
>>
>> basically what i do now with php is :
>> ...
>> $cliente=mysql_query("select * from table_1");
>> while($row = mysql_fetch_array($cliente))
>> {
>>    printf("\nProduct features  : %s\n",$row[features]);
>>    $cliente2=mysql_query("select name,price from table_2 where 
>> product_id='$row[product_id]' and size=2");
>>    while($row2 = mysql_fetch_array($cliente2))
>>    {
>>  .     print("Name %s --- Price %s\n",$row2[name],$row2[price],);
>>    }
>> }
>> .........
>>
>> How can i do this without 2 select and whitout 2 while and the "features" 
>> be printed just one time?
>>
>>
>> Thank you in advance
>>
>> Jose Julian Buda
>
> Whenever you have a query in a loop, you know you are doing something
> wrong. Your query will pull repeated values, but there is no way
> around that. You'll then check if the repeated value changes in PHP.
>
> $cliente = mysql_query("SELECT table_1.*, table_2.name, table_2.price
> FROM table_2 JOIN table_1 ON table_2.product_id=table_1.product_id
> WHERE table_2.size=2 ORDER BY table_1.product_id");
> $prev_prod_id = -1;
> while($row = mysql_fetch_array($cliente))
> {
>  if ( $row['product_id']!=$prev_prod_id ) {
>    printf("\nProduct features  : %s\n",$row[features]);
>    $prev_prod_id = $row['product_id'];
>  }
>  print("Name %s --- Price %s\n",$row[name],$row[price],);
> }
>
> Note I have not tested it, but you get the idea.
>
> Brent Baisley



Brent , it work fine with just 1 select query
Thank you very much

Jose Julian Buda 

Thread
How can avoid 2 selects and 2 while?Jose Julian Buda12 Feb
  • Re: How can avoid 2 selects and 2 while? SOLVEDJose Julian Buda12 Feb
    • RE: How can avoid 2 selects and 2 while? SOLVEDMartin Gainty12 Feb
  • Re: How can avoid 2 selects and 2 while?Dane Harmon12 Feb