Problem with inner join

Question

I have used inner join in my php page. Problem is when I fire query in mysql query browser it fetches nearly 6 rows. and all are correct but when I use same in my php page it fetch only one row. That is only one value..

while($row=mysql_fetch_array($result1))
{   
while ($resultpoet = mysql_fetch_array($poet)) {
    ?>
    <tr>
        <td><?= $iCounter?>
            . </td>
        <td><? if(($row['roman']=="")&&($row['hindi']=="")) { ?>
            <?=$row['name'] ?>
            <? } else { ?>
            <a href="sview.php?title=<?=$row['sid'] ?>">
            <?=$row['name'] ?>
            </a>
            <? } ?></td>
        <td width="216"><? 

 if($resultpoet['pstatus']!='u')
 print '<a href="pview.php?title='.$resultpoet['pname'].'">'.$resultpoet['pname'].'</a>';
else 
 print $resultpoet['pname'];

            ?>



            </td> 
        <td width="135"><?=$row['category'] ?></td>
        <td width="67"><a href="songtitle_action.php?title=<?=$row['sid'] ?>"> Edit </a>
            <input name="check[]" type="checkbox" value="<?=$row[s_id]?>" id="checkbox_<?=$row[sid]?>"></td>
        <td width="75"><?  if(($row['roman']=="")&&($row['hindi']=="")) { ?>
            <a href="song_add_lyrics.php?title=<?=$row['sid'] ?>"> Add Lyrics</a>
            <? } ?></td>
    </tr>
    <? }
            $iCounter++;
         }?>

Chris Baker · Accepted Answer · 2011-07-20 17:24:08Z

2

You are only calling mysql_fetch_array once, so you only get one row.

You need to instead iterate using while and do your output for each of the rows returned by the query. This is a pretty simplistic example:

$poet= mysql_query("SELECT  p.name as pname, p.status as pstatus from poet p INNER JOIN song s ON p.pid=s.pid");
while ($resultpoet= mysql_fetch_array($poet)) {
   if($resultpoet['pstatus']=='u')
     print '<a href="pview.php?title='.$resultpoet['pname'].'">'.$resultpoet['pname'].'</a>';
   else 
     print $resultpoet['pname'];
}

Check out the documentation for mysql_fetch_array, the example code elaborates on this principle.

answered Jul 20, 2011 at 17:24

Chris Baker

50.8k13 gold badges99 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aditii Over a year ago

Yes I forgot to do that... Correct that.. Have been puzzled since an hour.

Aditii Over a year ago

As I am using this in a other while loop so each time outer while is fetch value it fetches all group of calue for that particular column or tuple. How to manage two while loops?

Chris Baker Over a year ago

You can nest while loops all you'd like, just make sure you are using different variable names. :)

Chris Baker Over a year ago

Edit your answer and include more code... don't get what you're talking about.

Chris Baker Over a year ago

Still missing some code... put in the part where you are doing the queries.

|

Kerrek SB · Accepted Answer · 2011-07-20 17:21:16Z

2

You have to keep calling fetch_array:

while ($result = mysql_fetch_array($poet))
{
  /* do stuff with $result */
}

answered Jul 20, 2011 at 17:21

Kerrek SB

480k96 gold badges904 silver badges1.1k bronze badges

Collectives™ on Stack Overflow

Problem with inner join

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related