Categorize my blog topic...

[louiegiezer]

hello jacek in your blog tutorial i want to categorize to arranged the topic...i want a simple idea to start my coding...

[jacek]

Start with the database structure. You will need a table for categories that contains an id and the name, then in the posts table you need to add a category id column.

[louiegiezer]

I finished the database structure...now i make the form of add a category...
[syntax=php]
function submit_category($category){
$category = mysql_real_escape_string(htmlentities($category));

mysql_query("INSERT INTO `blog_cat` (`name`) VALUES ('{$category}')");
}

if(isset($_POST['category'])){
submit_category($_POST['category']);
header('Location: index.php');
die();
}


<table border=0>
<form action="" method="post">
<tr>
<td>Category Name</td><td><input type="text" name="category"></td></tr>
<td align='right' colspan=2><input type="submit" name="Submit Category"></td>
</table>
[/syntax]


everything's ok....so whats the next

[Josh]

I finished the database structure...now i make the form of add a category...
[syntax=php]
function submit_category($category){
$category = mysql_real_escape_string(htmlentities($category));

mysql_query("INSERT INTO `blog_cat` (`name`) VALUES ('{$category}')");
}

if(isset($_POST['category'])){
submit_category($_POST['category']);
header('Location: index.php');
die();
}


<table border=0>
<form action="" method="post">
<tr>
<td>Category Name</td><td><input type="text" name="category"></td></tr>
<td align='right' colspan=2><input type="submit" name="Submit Category"></td>
</table>
[/syntax]


everything's ok....so whats the next

Then in the posts table you'd have a "cat_id" or similar column which corresponds to the id of the category that the post is posted in.

[louiegiezer]

i already have a cat_id in the column of posts. so in my form to post what is the best idea, I have a plan to make an select option tag to call my categories in the table of blog_cat and insert into the database of posts. how can i start work in this part...haha

[jacek]

I have a plan to make an select option tag to call my categories in the table of blog_cat and insert into the database of posts. how can i start work in this part...haha

That sounds like a good place to start. Take it step by step, first just try to make a function to get the categories then worry about the html and submitting. It should be possible, as it should just be a simple SELECT query.

[louiegiezer]

code for fetch the category list on my database

[syntax=php]
<p class="topic-cat"><select name='cid'><option value='0'>Please Choose</option>
<?php
$query = "SELECT * FROM `blog_cat` ORDER BY id ASC";
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) == 0){
echo "</select><br>No categories exist";
}else {
while($row = mysql_fetch_assoc($result)){
echo "<option value=\"".$row['id']."\">".$row['name']."</option>\n";
}
}
echo "</select>";
?></p>
[/syntax]
and for the function i add $cid on the function of posts to insert it on the database...
it works...

and the last i want to fetch all of that in my index...by using select query again the categories and the selected category of the topic...

[louiegiezer]

[syntax=php]
<div id="categories">
<?php
$select = mysql_query("SELECT * FROM `blog_cat`");
while($data = mysql_fetch_assoc($select)){
echo"<label>$data[name]</label><br>";

$select2 = mysql_query("SELECT * FROM `posts` WHERE `post_cid`='".$data['id']."' "); while($data2 = mysql_fetch_assoc($select2)){
echo "$data2[post_title]<br>";
}
}
?>
</div><!--Categories-->

[/syntax]

looks perfect for the code going back to the html...if i click the category it will show the topic list ...i dont know if it is a hover or a drop down tag...haha

[jacek]

Why have you stopped using functions ? Instead of doing the mysql stuff directly in the file you should make a function to get the list of categories and return it as an array. That way you will keep all your SQL in one place.

Also, why have you gone to using * rather than specifying the column names that you need ? You never need all of the columns so it would be better to only have the server send the needed data.

Finally, you should never use a query inside of a loop, queries are one of the slower things in php and looping them is going to hurt your page loat time. You should really use a INNER JOIN here.

[louiegiezer]

I do not know how to use the inner join and I am not too uses a different method to facilitate. would you do now in my code? thanks for the advice

[louiegiezer]

teach me how to facilitate my code haha...

[jacek]

Everything you need to know is here http://dev.mysql.com/doc/refman/5.0/en/join.html or more simply here http://www.tizag.com/mysqlTutorial/mysqljoins.php

Have a go, if you get stuck post here

[louiegiezer]

I make inner join like you said but the result is this...

SPORT
nba 2011
SPORT
nba 2010
MOVIE
Iron Man 2
MOVIE
Iron Man 1

but I want to make it like this :

SPORT
nba 2011
nba 2010
MOVIE
Iron Man 2
Iron Man 1

Any ideas how to do this?

Here's my code
[syntax=php]
$sql = "SELECT
`blog_category`.`id`,
`blog_category`.`cat_name`,
`posts`.`post_title`
FROM `blog_category`
INNER JOIN (`posts`)
ON `blog_category`.`id`=`posts`.`post_category_id`";

$rows = mysql_query($sql);
while ($data = mysql_fetch_assoc($rows)){
echo "$data[cat_name]<br>";
echo "$data[post_title]<br>";
}
[/syntax]

[jacek]

You would probably have to format the data with php.

Best way to explain would be with code, so here is an example.

[syntax=php]$sql = "SELECT
`blog_category`.`id`,
`blog_category`.`cat_name`,
`posts`.`post_title`
FROM `blog_category`
INNER JOIN `posts`
ON `blog_category`.`id` = `posts`.`post_category_id`";

$rows = mysql_query($sql);

$posts = array();

while ($data = mysql_fetch_assoc($rows)){
$posts[$data['cat_name']][] = array(
'id' => $data['id'],
'title' => $data['post_title'],
);
}

return $posts;[/syntax]

[louiegiezer]

Undefined index in line 4 and 5 am i correct at this line?

index.php
[syntax=xhtml]
$posts = get_categories();
foreach ($posts as $get){
?>
<label><?php echo $get['name']; ?></label>
<label><?php echo $get['post_title']; ?></label>
<?php
}
?>

[/syntax]

posts.php
[syntax=php]
function get_categories(){
$sql = "SELECT
`blog_category`.`id`,
`blog_category`.`cat_name`,
`posts`.`post_title`
FROM `blog_category`
INNER JOIN `posts`
ON `blog_catagory`.`id` = `posts`.`post_category_id`";

$row = mysql_query($sql);

$posts = array();

while ($data = mysql_fetch_assoc($row)){
$posts[$data['cat_name']][] = array(
'id' => $data['id'],
'title' => $data['post_title'],
);
}
return $posts;
}
[/syntax]

[jacek]

You seem to have just assumed that the different array format will still work with your simple loop.

print out the array

[syntax=php]echo '<pre>', print_r($posts), '</pre>';[/syntax]

and you should see where you have gone wrong.

[louiegiezer]

thanks i still confused in array...

[jacek]

thanks i still confused in array...

what ?

The array should contain an element for each category, each one will have the key as the name of that category and the value as an array of posts.