List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:February 12 2009 5:33pm
Subject:RE: How can avoid 2 selects and 2 while? SOLVED
View as plain text  
Jose-

preface the product_id column with table ID e.g.
table_1.product_id 
table_2.product_id
or use the alias assigned to tableName

Saludos Cordiales desde EEUU
Martin 
______________________________________________ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business of Sender.
This transmission is of a confidential nature and Sender does not endorse distribution to
any party other than intended recipient. Sender does not necessarily endorse content
contained within this transmission. 




> From: jbuda@stripped
> To: mysql@stripped
> Subject: Re: How can avoid 2 selects and 2 while? SOLVED
> Date: Thu, 12 Feb 2009 13:45:13 -0300
> 
> 
> ----- 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 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

_________________________________________________________________
Windows Live™: Keep your life in sync. 
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
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