CODEWITHSUNDEEP
phpmysqldatabase
TheGiantBaboon
TheGiantBaboon

Reputation: 89

How do I get a table value based on the value of another string?

I'm not to sure if I have used the correct jargon above but what I am trying to do is the follow... How do I get $author to output a users name (from the mySQL users tabel) on the basis that $author_id of the post, matches the users ID?

<?php 
include "inc/mysql-connect.php"; 
$DynamicFeed = "";
$sql = mysql_query("SELECT * FROM posts ORDER BY id DESC LIMIT 20");
$postCount = mysql_num_rows($sql); // count the output amount
if ($postCount > 0) {
  while($row = mysql_fetch_array($sql)){ 
    $id = $row["id"];
    $title = $row["title"];
    $post = $row["post"];
    $author_id = $row["author_id"];
    $author = $row["author_id"];
    $type = $row["type"];
    $date_posted = $row["date_posted"];
    $DynamicFeed .= "<div class=\"item\"><img class=\"img-type\" align=\"left\"       src=\"img/$type.jpg\"> <p><a href=\"droplet.php?id=$id\"><b>$title</b></a><br />by <a    href=\"profile.php?id=$author_id\">$author</a> on $date_posted<br /><br /> $post</p>    </div>";
}
} else {
$DynamicFeed = "It would appear that there ar'nt any Droplets here...";
}
?>

Upvotes: 0

Views: 54

Answers (3)

Nils
Nils

Reputation: 446

mysql_query("SELECT posts.*, users.username FROM posts JOIN users ON author_id = users.id ORDER BY posts.id DESC LIMIT 20");

Upvotes: 1

postolka
postolka

Reputation: 1

If I understand correctly, you should do this directly in your query (not in while cycle), by joining user table:

SELECT * FROM posts LEFT JOIN <user-table> ON <user-table>.id=author_id ORDER BY id DESC LIMIT 20"

Then you get your data directly in $row array. You may need to add aliases, if you have same column names in both tables.

Upvotes: 0

danielb
danielb

Reputation: 878

You can use a JOIN statement as follows:

$sql = mysql_query("SELECT posts.*,users.name FROM posts LEFT JOIN users on posts.author_id=users.id ORDER BY posts.id DESC LIMIT 20");

Upvotes: 2

Related Questions