CODEWITHSUNDEEP
phpmysqldatabase
dirigibleplum
dirigibleplum

Reputation: 137

Displaying correct data

I'm building a blog and to view each post I use:

post.php?id=<?php echo $row_post['id']; ?>

Post is a table in my db.

$colname_post = "-1";
if (isset($_GET['postid'])) {
  $colname_post = $_GET['postid'];
}

mysql_select_db($database_connect, $connect);
$query_post = sprintf("SELECT postid, title, DATE_FORMAT(date,'%%d.%%m.%%Y') AS date, idcategory, text FROM post WHERE postid = %s", GetSQLValueString($colname_post, "int"));;
$post = mysql_query($query_post, $connect) or die(mysql_error());
$row_post = mysql_fetch_assoc($post);
$totalRows_post = mysql_num_rows($post);

I've got a problem displaying data that is not in the post table. For example under the title, I want to display the category. Category is a separate table. The relation between them is that one category can be connected to many posts, but one post can only have one category. They are connected with categoryID (foreign key). Comment if you need to see show create table.

So, while all the data from the post table is correctly displayed, the data from other tables (that is the tables which do not include postid as an attrubute) are not. For example

<h1><?php echo $row_post['title']; ?></h1> <-- correct value displayed
<p><?php echo $row_category['name']; ?></p> <-- not correct

Any suggestions for how to fix this?

Upvotes: 0

Views: 52

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to join 

select p.title, c.name from post p
inner join category c on c.categoryID = p.categoryID

You can add more columns in the select statement as you want.

UPDATE

This is how your query should look like

SELECT 
p.postid, 
c.name,
p.title, 
DATE_FORMAT(p.date,'%%d.%%m.%%Y') AS date, 
p.idcategory, 
p.text 
FROM post p 
inner join category c on c.categoryID = p.idcategory 
WHERE p.postid = %s

I have created alias for table names p and c for post and category, so check this query if the selection is done from proper tables.

Upvotes: 2

Brovoker
Brovoker

Reputation: 896

You can address multiple tables in SQL with a JOIN clause.

More information about this here : http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 0

Related Questions