From: Martin Gainty Date: February 12 2009 5:33pm Subject: RE: How can avoid 2 selects and 2 while? SOLVED List-Archive: http://lists.mysql.com/mysql/216285 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_4cc854c1-18f3-4603-84ef-a5b5e7fab0de_" --_4cc854c1-18f3-4603-84ef-a5b5e7fab0de_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Jose- preface the product_id column with table ID e.g. table_1.product_id=20 table_2.product_id or use the alias assigned to tableName Saludos Cordiales desde EEUU Martin=20 ______________________________________________=20 Disclaimer and confidentiality note=20 Everything in this e-mail and any attachments relates to the official busin= ess of Sender. This transmission is of a confidential nature and Sender doe= s not endorse distribution to any party other than intended recipient. Send= er does not necessarily endorse content contained within this transmission.= =20 > From: jbuda@stripped > To: mysql@stripped > Subject: Re: How can avoid 2 selects and 2 while? SOLVED > Date: Thu=2C 12 Feb 2009 13:45:13 -0300 >=20 >=20 > ----- Original Message -----=20 > From: "Brent Baisley" > To: "Jose Julian Buda" > Sent: Thursday=2C February 12=2C 2009 1:21 PM > Subject: Re: How can avoid 2 selects and 2 while? >=20 >=20 > > On Thu=2C Feb 12=2C 2009 at 10:43 AM=2C Jose Julian Buda > > 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 =3D2 > >> ............. > >> Report of products with size =3D2 > >> > >> 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=3Dmysql_query("select * from table_1")=3B > >> while($row =3D mysql_fetch_array($cliente)) > >> { > >> printf("\nProduct features : %s\n"=2C$row[features])=3B > >> $cliente2=3Dmysql_query("select name=2Cprice from table_2 where=20 > >> product_id=3D'$row[product_id]' and size=3D2")=3B > >> while($row2 =3D mysql_fetch_array($cliente2)) > >> { > >> . print("Name %s --- Price %s\n"=2C$row2[name]=2C$row2[price]=2C)= =3B > >> } > >> } > >> ......... > >> > >> How can i do this without 2 select and whitout 2 while and the "featur= es"=20 > >> be printed just one time? > >> > >> > >> Thank you in advance > >> > >> Jose Julian Buda > > > > Whenever you have a query in a loop=2C you know you are doing something > > wrong. Your query will pull repeated values=2C but there is no way > > around that. You'll then check if the repeated value changes in PHP. > > > > $cliente =3D mysql_query("SELECT table_1.*=2C table_2.name=2C table_2.p= rice > > FROM table_2 JOIN table_1 ON table_2.product_id=3Dtable_1.product_id > > WHERE table_2.size=3D2 ORDER BY table_1.product_id")=3B > > $prev_prod_id =3D -1=3B > > while($row =3D mysql_fetch_array($cliente)) > > { > > if ( $row['product_id']!=3D$prev_prod_id ) { > > printf("\nProduct features : %s\n"=2C$row[features])=3B > > $prev_prod_id =3D $row['product_id']=3B > > } > > print("Name %s --- Price %s\n"=2C$row[name]=2C$row[price]=2C)=3B > > } > > > > Note I have not tested it=2C but you get the idea. > > > > Brent Baisley >=20 >=20 >=20 > Brent =2C it work fine with just 1 select query > Thank you very much >=20 > Jose Julian Buda=20 >=20 >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped= om >=20 _________________________________________________________________ Windows Live=99: Keep your life in sync.=20 http://windowslive.com/howitworks?ocid=3DTXT_TAGLM_WL_t1_allup_howitworks_0= 22009= --_4cc854c1-18f3-4603-84ef-a5b5e7fab0de_--