Duplicate Queries Crash When Not Seperated

Hi,

I have a query which I am trying to repeat. However when I place them next together the second one creates an error. However when I seperate them with another query they both work independently. Can anyone please advise why they wont work next to each other?

This works:

Code:

---

          <div class="followbuttonimagearea">
                                                                                                <?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY follow_user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {
               
          ?>       
         
<div class="followimage">
<a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
                                </div>

<?php
}

          ?>
</div>

<?php
if (isset($_GET['ID']))
$ID = mysql_real_escape_string($_GET['ID']);
$sql = "SELECT * FROM users WHERE ID = '$ID'";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res); // no need to loop since you are retrieving only one row
$num_rows = mysql_num_rows($res); // check to see if any results were found, just in case someone puts an ID in the url without clicking on your link
?>

          <div class="followbuttonimagearea">
                                                                                                <?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY follow_user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {   
          ?>       
         
<div class="followimage">
<a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
                                </div>

<?php
}

          ?>
</div>

---

This crashes with an error:

Code:

---

<div class="followbuttonimagearea">
                                                                                                <?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY follow_user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {
               
          ?>       
         
<div class="followimage">
<a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
                                </div>

<?php
}

          ?>
</div>


          <div class="followbuttonimagearea">
                                                                                                <?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY follow_user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {   
          ?>       
         
<div class="followimage">
<a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
                                </div>

<?php
}

          ?>
</div>

---

I don't understand why you're trying to repeat the same query twice? Why are you actually doing that?

Apologies, my mistake.

When I changed the second query it worked independently. But I still thought they would work independently even if they were copies.

Hi again,

I thought I had it fixed but when I remove the query in the middle it does create the error.

This is what Im trying to do.

This is the error it creates. Is there a rule that they cant be allowed to run next to each other?

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in

PHP Code:

---

 <div class="followbuttonimagelinks">
  <div class="followbuttonimagelinksleft">
 Followers
  </div>
   <div class="followbuttonimagelinksright">
 <a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>">View All >></a>
 </div>
  </div>
      <div class="followbuttonimagearea">
    <?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {     
      ?>    
      
<div class="followimage">
<a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
                 </div>

<?php
}

      ?>
</div>




 <div class="followbuttonimagelinks">
  <div class="followbuttonimagelinksleft">
 Following
  </div>
   <div class="followbuttonimagelinksright">
 <a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>">View All >></a>
 </div>
  </div>
      <div class="followbuttonimagearea">
                                                <?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE follow_user_id = " . ($row['id']) . " ORDER BY user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {     
      ?>    
      
<div class="followimage">
<a href="/test/profileinserttest.php?ID=<?php echo $row['user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
                 </div>

<?php
}

      ?>
</div>

---

You're getting the error because the query failed meaning that mysql_query has returned false to indicate the failure of the query.

The query has failed because MySQL doesn't know what field in each table it is to join the two tables by.

Quote:

---

SELECT
*
FROM
employee
INNER JOIN
department
ON employee.DepartmentID = department.DepartmentID

---

In that example of an INNER JOIN from a wikipedia page the line that begins with "ON" tells MySQL which fields in each table have to have the same value to match.

Have a read of this page which probably explains it better.

Also you should be considering migrating from the mysql_* extenstion to either the mysqli_* extenstion or PDO as the mysql_* extension has been depreceated as of php version 5.5

EDIT: Also do you need every field from both tables? If you don't then list each one in the SELECT clause in the format da_table.da_field

Quote:

---

Originally Posted by SpacePhoenix

You're getting the error because the query failed meaning that mysql_query has returned false to indicate the failure of the query.

The query has failed because MySQL doesn't know what field in each table it is to join the two tables by.



In that example of an INNER JOIN from a wikipedia page the line that begins with "ON" tells MySQL which fields in each table have to have the same value to match.

Have a read of this page which probably explains it better.

Also you should be considering migrating from the mysql_* extenstion to either the mysqli_* extenstion or PDO as the mysql_* extension has been depreceated as of php version 5.5

EDIT: Also do you need every field from both tables? If you don't then list each one in the SELECT clause in the format da_table.da_field

---

Many thanks,

I shall go through this later today.

The code works when there is one being used however when two are placed next to each other the second creates and error. Is this related to the error of them working next to each other?

It's not working because you are re-using $row

Code:

---

<?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY follow_user_id ASC LIMIT 10"); // use initial value of $row
while($row = mysql_fetch_array($query)) { // new value for $row
   
    <?php
   
    <div class="followimage">
    <a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
    </div>
   
    <?php
}
?>
</div>


<div class="followbuttonimagearea">
<?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY follow_user_id ASC LIMIT 10"); // value for $row is not the same as it was on line 1
while($row = mysql_fetch_array($query)) {
    ?>
   
    <div class="followimage">
    <a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
    </div>
   
    <?php
}
?>
</div>

---

So in the second loop $row doesn't have the same value it had in the first loop so the query fails and you get an error. Easiest way to fix are to either to store the ID you need in a separate variable, or to use a different variable name in the inner loop.

PHP Code:

---

<?php
$id = $row['id'];
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . $id . " ORDER BY follow_user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {
    
    <?php
    
    <div class="followimage">
    <a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
    </div>
    
    <?php
}
?>
</div>


<div class="followbuttonimagearea">
<?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . $id . " ORDER BY follow_user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {
    ?>
    
    <div class="followimage">
    <a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
    </div>
    
    <?php
}
?>
</div>

---

even better would be to use mysql_data_seek to rewind the query result and start over so you don't have to do the same query twice:

PHP Code:

---

<?php
$id = $row['id'];
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . $id . " ORDER BY follow_user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {
    
    <?php
    
    <div class="followimage">
    <a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
    </div>
    
    <?php
}
?>
</div>


<div class="followbuttonimagearea">
<?php
mysql_data_seek($query, 0);
while($row = mysql_fetch_array($query)) {
    ?>
    
    <div class="followimage">
    <a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
    </div>
    
    <?php
}
?>
</div>

---

Thanks,

I added a different string on the second query. However it is doing something quite strange. It is echoing the whatever is in the database 3 times.

I haven't seen this being done before. From the code it should it should only display once. Does anyone have any suggestions why this is please?

Code:

---

  <div class="followbuttonimagelinksleft">
 Followers
  </div>
  <div class="followbuttonimagelinksright">
 <a href="/test/profileinserttest.php?ID=<?php echo $profileid; ?>">View All >></a>
 </div>
  </div>
          <div class="followbuttonimagearea">
        <?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {   
          ?>       
         

         
         
<div class="followimage">
<a href="/test/profileinserttest.php?ID=<?php echo $row['follow_user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
                                </div>

<?php
}
          ?>
</div>


         

 <div class="followbuttonimagelinks">
  <div class="followbuttonimagelinksleft">
 Following
  </div>
  <div class="followbuttonimagelinksright">
 <a href="/test/profileinserttest.php?ID=<?php echo $profileid; ?>">View All >></a>
 </div>
  </div>
          <div class="followbuttonimagearea">
                                                                                                <?php
                                                                                               
                                                                                               
$query = mysql_query("SELECT * FROM follow JOIN users WHERE follow_user_id = " . ($profileid) . " ORDER BY user_id ASC LIMIT 10");
while($row = mysql_fetch_array($query)) {   
          ?>       
         
<div class="followimage">
<a href="/test/profileinserttest.php?ID=<?php echo $row['user_id']; ?>"><img src="/test/images/<?php echo $row['logo']; ?>" alt="<?php echo $row['company']; ?>" /></a>
                                </div>

<?php
}

          ?>
</div>

---

Echo both the queries with the values in there WHERE clauses and try each one separately against the database to check how many rows you've got in the result set for each one

Quote:

---

Originally Posted by SpacePhoenix

Echo both the queries with the values in there WHERE clauses and try each one separately against the database to check how many rows you've got in the result set for each one

---

Thanks,

How do I echo the queries, should I be using one of these methods?

Code:

---

echo $followerid;
echo $profileid;

echo ("SELECT * FROM follow JOIN users WHERE user_id = " . ($row['id']) . " ORDER BY user_id ASC LIMIT 10");

echo $_SERVER["QUERY_STRING"];

---

If you're just copying and pasting the same code over and over, it's definitely a sign of bad design. You shouldn't really have any duplicated code anywhere in your application.

I have a table with only two columns but I need to display both with different queries.