CODEWITHSUNDEEP
phpmysql
Jeremy23
Jeremy23

Reputation: 157

Double mysql_fetch_array (get information from the other table to get data) / nest

I tried searching for this question, but theirs is a different error. I'm using PHP 5.2 for some reason.

What I'm trying to do is, get the mysql_fetch_array from one table, then use a column of that table to get a row from another table.

Here is my code (Advanced apologies for the horrible format)

$sql = mysql_query("SELECT * FROM testimonies WHERE visibility != 'Hide' ORDER BY date_submitted DESC");
$testimonyCount = mysql_num_rows($sql); // count the output amount
if ($testimonyCount > 0) {

//get data 'testimonies' table
while($info = mysql_fetch_array($sql)){ 
$username = $info['username'];

//Get id (in admin table) where username = username (in testimonies table)
$userid = mysql_query("SELECT * FROM admin WHERE username = $username LIMIT 1");
while($user = mysql_fetch_array($userid)){ $id = $user['id'];}

         $testimonies .= "<div class='row'><div class='col-lg-2'><center>
<img src='Pictures/Profile_Pictures/".$userid['id'].".jpg' width='160' height='160'>
<br>".$info['username']."</center></div><div class='col-lg-2'><center>
<img src='back/inventory_images/34.jpg' width='160' height='160'><br>"
.$info['product_used']."</center></div><div class='col-lg-8'><center><u>(Date: "
.$info['date_submitted']." - Ordered from our branch in <strong>"
.$info['branch_ordered']."</strong>, City)</u></center>".$info['testimony']."
</div></div><br>";
}
} 
else {
$testimonies = "No one has submitted their testimonies yet.";
}

So you see, my table "testimonies" have a column of 'username' and so is the "admin" table. What' I'm trying to do is get the row of the username (in admin table) where the username is the same as the one in the "testimonies" table. I used the information from the 2nd table in line 14 for the image src.

Unfortunately it gives me this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/_____/public_html/Testimonies.php on line xx
Notice: Undefined variable: id in /home/_____/public_html/Testimonies.php on line xx

Other's from what I've searched get their data on the first loop, but I don't get anything at all. The 2nd loop is certainly a problem. Help.

Thanks.

Upvotes: 0

Views: 129

Answers (1)

Chad Burke
Chad Burke

Reputation: 91

First I'll mention that it's a really bad idea to build queries in a such a way that they can be looped. As is for each of your testimonies you're executing another query to get the admin info. So for 10 testimonies you're hitting MySQL 10 times [ +1 for the original query ]. You generally want to get in, get your data, and get out.

So the better way to handle this is to let MySQL do it for you, with a join :

SELECT * FROM testimonies inner join admin on testimonies.username = admin.username WHERE visibility != 'Hide' ORDER BY date_submitted DESC

The above will only return you testimonies with a matching admin. If you wanted to return testimonies even if they don't have an admin you'd want an outer join. You'd replace inner join admin with left outer join admin

So remove your second while loop and try that.

Also, the mysql_ functions are deprecated .. so you shouldn't be developing new code using them. Try and get my change to work as-is but you should consider looking at PDO [ Why shouldn't I use mysql_* functions in PHP? ].

Upvotes: 1

Related Questions