0

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.

Greg Marzouka
  • 3,315
  • 1
  • 20
  • 17
Jeremy23
  • 157
  • 1
  • 2
  • 10
  • Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. – Jay Blanchard Oct 20 '14 at 14:24

1 Answers1

1

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? ].

Community
  • 1
  • 1
Chad Burke
  • 91
  • 1
  • BTW, I'm using php 5.2 because the free webhost I am currently using only provides that version. Thank you for the quick response. That seems a lot better idea than I had. So in that case, how do I tell mysql that I am getting the column from the admin table, having other 2 columns of the same name (specifically: id and 1 more column) -- something like $info[admin.'id']? – Jeremy23 Oct 20 '14 at 13:48
  • You need to alias the columns - `table1.id AS IDT1, table2.id AS IDT2` etc. Then you can get the columns by the alias headings. – Jay Blanchard Oct 20 '14 at 14:26