First try at relational database...FAIL (where did I go wrong?!?!)

[Tylor_Famous]

I am kind of new to relational databasing (ok very new). And what I am doing is making a simple blog. I just want to have one table called "articles" where all the information about the blog post is stored an then another table called "authors" where all the information about the author is stored. Now the problem is I am not quite sure how to get out the information from both tables at the same time. Here is my code i came up with just from guessing and testing (lol) P.S. Don't worry about the HTML parts (like the divs and styles)...

 

<?php 
mysql_pconnect ("host", "user", "pass");
mysql_select_db ("database");
$qResult = mysql_query ("SELECT * FROM articles ORDER BY date DESC");
$nRows = mysql_num_rows($qResult);

for ($i=0; $i< $nRows; $i++){
$row = mysql_fetch_array($qResult);

$query = "SELECT * FROM authors WHERE `id` = '".$row['authorid']."' ";
$result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query);
$row = mysql_fetch_array($result);
extract($row); 
  
echo"
    <div class=\"carrielf\">
        <div class=\"entry\">
         <span class=\"title\">".$row['title']."<br /></span>
            <span class=\"pudate\">".$row['date']."<br /></span>
         <span class=\"author\">By: $name <br /></span>
         <p class=\"post\">".$row['prepost']."</p>
        </div> 
    </div>
";

}

?>

See, the problem is that I connect to the database two times (which i am sure is not good). And all this gives my anyway is the authors name. It just won't give my anything else because I reconnect to the database again before any information to taken using the first connection set up at the beginning of the file.

 

I am just a little confused and know that I am doing this totally wrong. Any help steering me in the right direction would be greatly appreciated. Thanks so much!

[Barand]

Better to use a join

 

http://www.w3schools.com/sql/sql_join.asp

[Tylor_Famous]

Thank you for such a quick reply. This is the first time I have seen this and I can see that I will be very helpful in the future! I am though, having a little trouble seeing how to incorporate it in my example. Would I do something like this?

 

$qResult = mysql_query ("SELECT * FROM articles,authors ORDER BY date DESC");

 

and then how would I let PHP know that I wanted to select the author name from the "author" table?

 

I was thinking something like: ".$row['author.name']." but that didn't work...

[Barand]

SELECT a.*, au.name

FROM articles a INNER JOIN authors au ON au.id = a.authorid

[br0ken]

The reason you only get the authors information in the code you posted is because you assign the results of both queries to the same variable, $row.

 

The articles information is stored in $row but then you proceed to assign the authors information to $row therefore overwriting the article information. Try assigning the article query result to $rArticle and the author query result to $rAuthor and you should get all the data.

 

With that said, I would use a SELECT statement to join the two tables into one.