[RESOLVED] Getting Caught in a Loop

swatisonee

hi,

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

b. I have to select only those records from Table A that do not 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 common field appears with the same data 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!";
 }

Plasma

One way to resolve problems like this is to chuck some echo statements in the loop, so you can logically see from the page output whats going on.

Also, you could try adjusting your sql query to simply not return rows you dont want.

If your mysql supports subquerues (4.1+ I think):

SELECT * FROM order WHERE Enqid = $id AND some_field NOT IN (SELECT some_field FROM badorders WHERE ...) etc

Roger_Ramjet

swatisonee wrote:
hi,

b. I have to select only those records from Table A that do not appear in Table B. 1 field in both is common .

You do that with a LEFT JOIN, it is covered in the manual - hint: look for the bit about IS NULL

swatisonee

Thanksfor those quick replies.

a. I did echo out everything - all of which works - in the sense i dont get any sql errors and the 2 sql statements list out all that i want.

b. i always struggle with joins which is why i never use them. also correct me if i am wrong but isnt this the definition of left join ?

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

c. Now my rows or records in both tables are not identical. Table A lists all my enquries, Table B my orders - which also has data that has come in from other tables

d. I need to list out all those enquiries pertaining to specific categories which DID NOT result in an order and thats where i am stuck.

Any ideas pl ?
Thnaks.

Roger_Ramjet

And that is why I told you to look for the part about IS NULL. It is definitely there in the manual, and every manual on sql and basic joins, it is the standard way of finding out exactly what you want: find records in table A with no matching record in table B.

LEFT JOIN returns every row from left table and all rows that match from right table. If no record matches in the right table then columns in the right table are set to NULL.

So, if your where condition specifies IS NULL for the table B key column you will get all the table A records WITH NO MATCH IN table B.

I really cannot understand why anyone has trouble understanding joins. Trial and error should make you understand them if nothing else. Just fill some tables with test data and experiment with joins. See what data you get back and then see how it changes when you change the join.

swatisonee

Hi,

I do test but i cannot isolate the point where the error comes. In this case, this is the query i ran but it just shows no records found.

$sqlb = "SELECT * FROM `Enq` LEFT JOIN `order` ON  Enqid.Enq = Enqid.order WHERE Enqid.order IS NULL AND   Category.Enq!= 'D' AND Category.Enq!= 'A' AND fid.Enq='$fid'" ;
echo $sqlb;

I also tried it the other way round but the result is the same - no records found.

$sqlb = "SELECT * FROM `Enq` LEFT JOIN `order` ON  Enq.Enqid = order.Enqid WHERE order.Enqid IS NULL AND   Enq.Category!= 'D' AND Enq.Category!= 'A' AND Enq.fid='$fid'" ;
echo $sqlb;

This also did not work

$sqlb = "SELECT * FROM `Enq` LEFT JOIN `order` ON  (Enq.Enqid = order.Enqid AND   Enq.Category!= 'D' AND Enq.Category!= 'A' AND Enq.fid='$fid' )WHERE order.Enqid IS NULL " ;
echo $sqlb;

Roger_Ramjet

Because you are putting the table names and column names in the wrong order: it is TABLE.COLUMN not column.table. Sorry did not see the second version.

Whenever you have problems with a query you MUST echo out the query string to see what the priblem is, not look at the code that generates it. So echo the query and post that here.

swatisonee

Sorry about that -i hit the post button in error before copying the third option . i have put an echo of the sql and also echo $resultb but only the sql echoes correctly and not the result.
Clearly it means the query is not being run...so how / what do i check for ?

thanks

Roger_Ramjet

Post the query here, the query not the code that generates it. Also, prove to yourself that the join is working by removing all the other conditions

 $sqlb = "SELECT * FROM `Enq` LEFT JOIN `order` ON  Enq.Enqid = order.Enqid WHERE order.Enqid IS NULL";

When you need to debug a query you first prove that there is data to find by running it without any Where conditions. You then add one condition at a time untill you find which one is causing all the problems. If the above query does not return any results it means that all records in Enq are matched by records in order.

The simple form that I have written above is the standard way that we find 'orphans' when doing regular maint on a db. Orphans are related records that should have a parent record in a main table. I write these housekeeping queries as soon as I finish creating my tables, and run them on a regular schedule. The hope is that they will all find nothing, but it happens.

swatisonee

I'm sorry but i'm terribly confused now. Isn't a query supposed to be in a code itself ?

As you suggested I ran the code without the where clause and it found no data. Then I physically verified the 2 tables and found that 36 records should have come up which were in Enq but not in Order.

Its a bit late to ask but could this have anything to do with using mysql ver 4.1.12

Here's what i ran :

<?php

mysql_connect("localhost", $dbname, $dbpasswd )
    	or die ("Unable to connect to server.");

mysql_select_db($database)
    	or die ("Unable to select database.");

$sqlb = "SELECT * FROM `Enq` LEFT JOIN `order` ON  Enq.Enqid = order.Enqid WHERE order.Enqid IS NULL";

//$sqlb = "SELECT * FROM `Enq` LEFT JOIN `order` ON  Enq.Enqid = order.Enqid ";
gave no result either.

echo $sqlb;

$resultb=mysql_query($sqlb);
echo $resultb;

 if ($myrowb = mysql_fetch_array($resultb))

 {
 do
 {
$enqid=$myrowb["Enqid"];
echo $enqid;
// running the code without the do-while loop is also not working 
printf("<tr><td><input type=\"radio\" name=\"choice\" value=%d><td>
     <font size=2 face=Tahoma color=blue>%d<td>
</tr>",
  $myrowb["Enquiryid"],
 $myrowb["Enquiryid"]);

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

Roger_Ramjet

OK, from the very top: how to code and test a query.

// build a query string
$sql = "SELECT * FROM Enq LEFT JOIN order ON Enq.Enqid = order.Enqid";

// run a query in development mode
$result = mysql_query($sql) or die ($sql . '<br />' . mysql_error());

// check the query returned a valid result, which could be empty but still valid
if (!$result) {
   echo ' there was a problem running this query<br />' . $sql . '<br />it did not return a valid result';
   break;
}

// now find out how many rows it returned
$count = mysql_num_rows($result);
echo $sql . '<br />this query returned ' . $count . ' rows';

That is how to build and test your query. Use it as a seperate test script for any select query. When it works then use the query in your production script.

Never assume that user input is there, or that it is correct even if it does exist. So when you first start to develop and debug queries and the like then hard code any input variables with test value. Then when you pop it into your script and it fails you know the query is good so you can look to the input vars for the fault.

Break everything down into small parts and test them one thing at a time or you will spend half your life chasing after problems by looking in the wrong place.

If that query does not work it means one of these:

1.you have not connected to the db server, check user name and password
2.you have not selected the right db, look for typos in the name
3.you have not used the correct names for your tables or columns
4.the tables are empty

swatisonee

Thanks so much for your time. I followed your advise and at the very 1st step i get this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Enq.Enqid=order.Enqid WHERE order.Enqid IS NULL' at line 1

The field names are correct as are the table names. What else should i be checking for pl ?
thanks.

swatisonee

I cannot believe this but its been a great learning experience.

I had to put backticks on thetable names in the ON Clause, but some of the columns required back ticks , others didnt.

I cannot thank you (Roger Ramjet) enough for this but its the first time i have been successful using a join !

Swati

Roger_Ramjet

The simple solution to that is NEVER to use a Reserved Word for a table or column name. In fact, it is best to get into the habit of only using names that are legal in strict SQL: no names beginning with a digit, no names that are all digits, no function or operator names. There are quite enough words and letter combinations available without ever having to break the rules - you just saw the trouble it can lead to. ORDER is a reserved word because it is an sql operators as in ORDER BY.