Displaying correct data

Question

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?

Note that `or die(mysql_error())` should never appear in production code, as [`die`](http://www.phpfreaks.com/blog/or-die-must-die) breaks HTML output and database error messages should never be revealed to non-admin users as it [discloses too much information](http://msdn.microsoft.com/en-us/library/ms995351.aspx#securityerrormessages_topic2). A better approach would be to properly implement error handling (and use PDO instead of the outdated mysql extension, which is being deprecated). — outis, Apr 01 '14 at 13:36
The mysql extension is deprecated. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. — outis, Apr 01 '14 at 13:37
Don't use [`SELECT *`](http://stackoverflow.com/q/321299/) unless you're writing a DB administration program; select only the columns you need. — outis, Apr 01 '14 at 13:41
if you use wordpress then there is no need to use costume query...tell me so i suggest you — deemi-D-nadeem, Apr 01 '14 at 13:44

Abhik Chakraborty · Accepted Answer · 2014-04-01T13:45:54.930

2

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.

edited Apr 01 '14 at 13:45

answered Apr 01 '14 at 13:28

Abhik Chakraborty

44,654
6
52
63

i edited my question, since i did simplify the code a bit at first. as you can see i use `WHERE postid = %s", ...` should I place this at the end of the code you wrote? (after on `c.categoryID = p.categoryID`)? – dirigibleplum Apr 01 '14 at 13:41

score 0 · Answer 2 · answered Apr 01 '14 at 13:29

0

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

answered Apr 01 '14 at 13:29

Brovoker

896
5
16

Displaying correct data

2 Answers2