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:
<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>
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.
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
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?
<?php
$query = mysql_query("SELECT * FROM follow JOIN users WHERE user_id = " . ([COLOR="#FF0000"]$row['id'][/COLOR]) . " ORDER BY follow_user_id ASC LIMIT 10"); [COLOR="#FF0000"]// use initial value of $row[/COLOR]
while([COLOR="#FF0000"]$row[/COLOR] = mysql_fetch_array($query)) { [COLOR="#FF0000"]// new value for $row[/COLOR]
<?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 = " . ([COLOR="#FF0000"]$row['id'][/COLOR]) . " ORDER BY follow_user_id ASC LIMIT 10"); [COLOR="#FF0000"]// value for $row is not the same as it was on line 1[/COLOR]
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.
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
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.