The trouble with Joins....

[swatisonee]

Or should i say my inability to understand them properly.

 

I have the foll. code (CODE1) that runs correctly when not using Joins. Obviously, its not good coding and I would do a more efficient job if I did use Joins. But when i do (CODE2), the result loop picks up only the 1st match it finds and not all of them . The error has to lie in the if-while loop that I have but I cannot understand what i am missing.

 

Guidance appreciated. Thanks. Swati

 

CODE1

=====

 

<?php //code works for the entire data

$sql11 = "SELECT * FROM `TableA` WHERE `Eid` = $group  AND `Month` = $month AND `Year` = $year " ;

$result11 = mysql_query($sql11);
if ($myrow11 = mysql_fetch_array($result11))
{
do
{
$sid = $myrow11["Sid"];


$sql11a = "SELECT * FROM `TableB` WHERE `Sid` ='$sid' ORDER BY `Binname` asc ";

$result11a = mysql_query($sql11a);
$myrow11a = mysql_fetch_array($result11a) ;

$fn = $myrow11a["FirstName"];
$mn = $myrow11a["MiddleName"];
$ln = $myrow11a["LastName"];
$a  = $myrow11a["Binname"];
$b  = $myrow11a["Binadd"];
$c  = $myrow11a["Bincity"];

$d   = $myrow11["Total"];

$sqlx= "SELECT Sum(Total) FROM TableA WHERE `Eid` ='$group' AND (Month =$month AND Year=$year) ";
   $resultx = mysql_query ($sqlx)    or die (mysql_error());
if ($myresult = mysql_result($resultx,0))

  printf("<tr><td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
             </tr>",
        $fn." ".$mn." ".$ln,
        $a,
        $b,
        $c,
        $d );


}
while  ($myrow11 = mysql_fetch_array($result11));
}

?>

 

 

CODE 2

=====

 

<?php  //code works only for 1st record that matches the query

$sql11 = "SELECT * FROM `TableA` INNER JOIN `TableB`  ON  TableA.Sid = TableB.Sid
           WHERE TableA.Eid = $group  AND TableA.Month = $month AND TableA.Year = $year ORDER BY TableB.Binname asc ";
           
// I also tried the foll. sql 

$sql11 = "SELECT * FROM `TableA` INNER JOIN `TableB`  USING (Sid)
           WHERE TableA.Eid = $group  AND TableA.Month = $month AND TableA.Year = $year ORDER BY TableB.Binname asc ";
           


$result11 = mysql_query($sql11);

if($myrow11 = mysql_fetch_array($result11))

{
$fn = $myrow11["FirstName"];
$mn = $myrow11["MiddleName"];
$ln = $myrow11["LastName"];
$a  = $myrow11["Binname"];
$b  = $myrow11["Binadd"];
$c  = $myrow11["Bincity"];

$d   = $myrow11["Total"];

$sqlx= "SELECT Sum(Total) FROM TableA WHERE `Eid` ='$group' AND (Month =$month AND Year=$year) ";
   $resultx = mysql_query ($sqlx)    or die (mysql_error());
if ($myresult = mysql_result($resultx,0))

  printf("<tr><td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
             </tr>",
        $fn." ".$mn." ".$ln,
        $a,
        $b,
        $c, 
       $d );


}

while  ($myrow11 = mysql_fetch_array($result11));


?>

[Barand]

$result11 = mysql_query($sql11);

if($myrow11 = mysql_fetch_array($result11))

do {                                                                  ### you missed the "do"
.....
} while ($myrow11 = mysql_fetch_array($result11));

[swatisonee]

Thanks Barand but when i put in a query under the Joins Tutorial, Daniel Egeberg suggested I use

 

if (mysql_num_rows($result)) {   
    while($row = mysql_fetch_array()) {   
        // do something   
    }   
}   
else {   
    echo 'The query returned no results';   
}  

 

I assumed that meant I didnt need to put in a "do"  but just run the rest of the code in that "do something" bit.

 

Did I get it wrong ?

Thanks again

[br0ken]

The difference between a DO and a WHILE is that a WHILE loop checks the statement passed to it before entering the loop where as a DO will process the loop once and then check the statement.

 

Both of your codes work but Barands code checks whether results have been returned before processing the loop.

[Barand]

Did I get it wrong ?

 

In your posted code you had

 

if($myrow11 = mysql_fetch_array($result11))

{
$fn = $myrow11["FirstName"];
$mn = $myrow11["MiddleName"];
$ln = $myrow11["LastName"];
$a  = $myrow11["Binname"];
$b  = $myrow11["Binadd"];
$c  = $myrow11["Bincity"];

$d   = $myrow11["Total"];

$sqlx= "SELECT Sum(Total) FROM TableA WHERE `Eid` ='$group' AND (Month =$month AND Year=$year) ";
   $resultx = mysql_query ($sqlx)    or die (mysql_error());
if ($myresult = mysql_result($resultx,0))

  printf("<tr><td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
       <font size=2 face=Tahoma color=black>%s<td>
             </tr>",
        $fn." ".$mn." ".$ln,
        $a,
        $b,
        $c, 
       $d );


}

while  ($myrow11 = mysql_fetch_array($result11));

 

which processes the first row then loops through the rest (in that single while() line) but does absolutely nothing with them. However, if you put in the "do" as I indicated then it will work as expected.

 

if ()
{
   // this code belongs to the "if"
}

while () ;       // this code loops but does nothing

 

if ()
do {
    // this code belongs to the while
}
while ();

 

[swatisonee]

Hi,

 

I'm in a mess trying to get joins and unions right. I wished to combine Tables B&C,both having identical structures and tried the foll. 3 options all of which returned errors.  The Unions work correctly when used without the Join and the Join works correctly for a single table but put them all in the same sql and errors flow even if the ORDER BY clause is removed.

So where am I going wrong ?

 

CODE 1

=====

 

$sql11a = "SELECT * FROM `TableB`
  
UNION
  
SELECT * FROM `TableC`

INNER JOIN `TableA` 

ON/ USING  // (tried both options)

(`TableB`.Sid = `TableA`.Sid) || (`TableC`.Sid = `TableA`.Sid) ) // line 9

WHERE `TableA`.`Eid` = $group  AND `TableA`.`Month` = $month AND `TableA`.`Year` = $year 

ORDER BY `Binname` asc  ";
  
  $result11a = mysql_query($sql11a) or die (mysql_error());
if ($myrow11a = mysql_fetch_array($result11a))
{
do
{

 

ERROR:

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 '.Sid = `TableA`.Sid) || (`TableC`.Sid = `TableA`.Sid)  line 9

 

 

CODE 2

======

 

$sql11a = "SELECT * FROM `TableB`
  
UNION
  
SELECT * FROM `TableC`

INNER JOIN `TableA` 

USING (Sid)  
WHERE `TableA`.`Eid` = $group  AND `TableA`.`Month` = $month AND `TableA`.`Year` = $year 

ORDER BY `Binname` asc  ";
  
$result11a = mysql_query($sql11a) or die (mysql_error());
if ($myrow11a = mysql_fetch_array($result11a))
{
do
{

 

ERROR :

The used SELECT statements have a different number of columns

 

 

CODE3

====

 

$sql11a = "SELECT * FROM `TableB`
  
UNION
  
SELECT * FROM `TableC`

INNER JOIN `TableA` 

ON Sid

WHERE `TableA`.`Eid` = $group  AND `TableA`.`Month` = $month AND `TableA`.`Year` = $year 

ORDER BY `Binname` asc  ";
  
$result11a = mysql_query($sql11a) or die (mysql_error());
if ($myrow11a = mysql_fetch_array($result11a))
{
do
{

 

 

ERROR:

 

Column 'Sid' in on clause is ambiguous

 

 

 

[Stefan]

Try this, it will give you a idea of how many row are called.

 

print mysql_num_rows($queryname);

 

I had the same problem as you did until I realized that it wasn't my code but my queries

giving me the trouble. The way I was calling my queries I had only one result and that is why

my loops where not working!

 

Let me know how you faring and please don't post doubles.