Jump to content


Photo

Getting Caught in a Loop


  • Please log in to reply
3 replies to this topic

#1 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 11 March 2006 - 04:37 PM

hi,

a. I have 2 Tables: Table A and Table B

b. I have to select all records from Table A that donot appear in Table B . 1 field in both is common .

c. I dont have a problem selecting data that appears in both tables where the same field is in common but
the reverse is getting caught in a loop and i'm not able to figure out what the error is.

d. The error is that the else statement gets executed.

Would appreciate some guidance pleas.e . Thanks.Swati

$sqlb= "SELECT * FROM `Enq` WHERE  Category`!= 'D' && `Category`!= 'A' && fid='$fid'";
$resultb=mysql_query($sqlb);
 if ($myrowb=mysql_fetch_array($resultb))
{
do
{
$enqid=$myrowb["Enqid"];

$sqlc="SELECT * FROM `order` WHERE `Enqid`= $enqid ";
if ( !@mysql_query($resultc) )
{

do {

     printf("<tr><td><input type=\"radio\" name=\"choice\" value=%d><td>
     <font size=2 face=Tahoma color=blue>%d<td>
     <font size=2 face=Tahoma color=blue>%s<td>
     <font size=2 face=Tahoma color=blue>%s<td>
 </tr>",
  $myrowb["Enqid"],
   $myrowb["fid"],
  $myrowb["Company"],
  calculatedate($myrowb["Date"]) );

 }
while ($myrowc = mysql_fetch_array($resultc));
 }

}
while ($myrowb= mysql_fetch_array($resultb));
 }

else {
     echo "Sorry, no records were found!";
 }

Shishya

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 12 March 2006 - 12:41 AM

To find unmatched records use LEFT JOIN and search for results where the data in the right table is NULL (ie no data)


SELECT a.* FROM tableA a LEFT JOIN tableB b
ON a.common = b.common
WHERE b.common IS NULL

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 12 March 2006 - 06:41 PM

[!--quoteo(post=353947:date=Mar 11 2006, 05:37 PM:name=swatisonee)--][div class=\'quotetop\']QUOTE(swatisonee @ Mar 11 2006, 05:37 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
hi,

a. I have 2 Tables: Table A and Table B

b. I have to select all records from Table A that donot appear in Table B . 1 field in both is common .

c. I dont have a problem selecting data that appears in both tables where the same field is in common but
the reverse is getting caught in a loop and i'm not able to figure out what the error is.

d. The error is that the else statement gets executed.

Would appreciate some guidance pleas.e . Thanks.Swati

$sqlb= "SELECT * FROM `Enq` WHERE  Category`!= 'D' && `Category`!= 'A' && fid='$fid'";
$resultb=mysql_query($sqlb);
 if ($myrowb=mysql_fetch_array($resultb))
{
do
{
$enqid=$myrowb["Enqid"];

$sqlc="SELECT * FROM `order` WHERE `Enqid`= $enqid ";
if ( !@mysql_query($resultc) ) // <-- look this line
{

do {

     printf("<tr><td><input type=\"radio\" name=\"choice\" value=%d><td>
     <font size=2 face=Tahoma color=blue>%d<td>
     <font size=2 face=Tahoma color=blue>%s<td>
     <font size=2 face=Tahoma color=blue>%s<td>
 </tr>",
  $myrowb["Enqid"],
   $myrowb["fid"],
  $myrowb["Company"],
  calculatedate($myrowb["Date"]) );

 }
while ($myrowc = mysql_fetch_array($resultc));
 }

}
while ($myrowb= mysql_fetch_array($resultb));
 }

else {
     echo "Sorry, no records were found!";
 }
[/quote]
look lin 9 change '$resultc' to '$sqlc'

#4 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 13 March 2006 - 06:43 AM

Hi,

Thanks Barand, Sasa.

I struggled to get the Join going and after lots of trial and error got it to work - it did not work till i put backticks for the table names in the on clause and also on column name in the where clause.


Thanks again.
Swati
Shishya




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users