Categorize my blog topic...

Post here is you are having problems with any of the tutorials.
Post Reply
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Categorize my blog topic...

Post by louiegiezer »

hello jacek in your blog tutorial i want to categorize to arranged the topic...i want a simple idea to start my coding...
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Categorize my blog topic...

Post by 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.
Image
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by louiegiezer »

I finished the database structure...now i make the form of add a category...
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>

everything's ok....so whats the next
Josh
Posts: 38
Joined: Tue Oct 11, 2011 9:31 pm

Re: Categorize my blog topic...

Post by Josh »

louiegiezer wrote:I finished the database structure...now i make the form of add a category...
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>

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.
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by 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 :lol:
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Categorize my blog topic...

Post by jacek »

louiegiezer wrote: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 :lol:
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.
Image
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by louiegiezer »

code for fetch the category list on my database ;)
<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>
and for the function i add $cid on the function of posts to insert it on the database...
it works... :P

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...
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by louiegiezer »

<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-->
			
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 :roll:
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Categorize my blog topic...

Post by 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.
Image
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by 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
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by louiegiezer »

teach me how to facilitate my code haha...
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Categorize my blog topic...

Post by 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 :D
Image
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by 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
$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>";
			} 
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Categorize my blog topic...

Post by jacek »

You would probably have to format the data with php.

Best way to explain would be with code, so here is an example.
$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;
Image
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by louiegiezer »

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

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

posts.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;
}
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Categorize my blog topic...

Post by jacek »

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

print out the array
echo '<pre>', print_r($posts), '</pre>';
and you should see where you have gone wrong.
Image
User avatar
louiegiezer
Posts: 57
Joined: Fri Oct 21, 2011 11:31 am
Contact:

Re: Categorize my blog topic...

Post by louiegiezer »

thanks i still confused in array...
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Categorize my blog topic...

Post by jacek »

louiegiezer wrote: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.
Image
Post Reply