PHP Category list

[luweegee]

Hi Guys!

I have a category name and a post_title in different table. i fetch all category and the post_title in my index let say my category name is "sport" then my post_title is all about sport now proceed to the code i used the query of inner join for the two table.
my problem is every time i make a topic with category the result is like 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?
This is 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>";
			}

[KamranArshad]

There is much simple way to get results you want i.e

First get category name
$getCategoryName
while{
when you get category name now is the time to get post name related to that category
$getPostName where Category name = "$getCategoryName"
<ol><li>$getPostName</li></ol>

}

that is inner loop effective one
gives you result some thong like this

SPORT
1.nba 2011
2.nba 2010

You can use order or unordered list or simple display results

If you find this post helpful please give positive remarks and marked this as solved

thanks

[luweegee]

thanks but that's what exactly i used before...

[KamranArshad]

I think there is problem with inner loop

see when it print "Sport" it should go to inner loop and print all the post related to "Sport"

use inner while

like

category name
while{
post name related to that category
while{
post name
}
}
now when he check "Sport" and go to inner loop then inner loop work until it get all the records related to "Sport"

Now what you are working is it get category and post in one loop that's why you are getting this problem

Hopefully you get this helpful

[luweegee]

ok this code is working so far...

$sql = "SELECT * FROM `blog_category`";
      $row = mysql_query($sql);
while($data = mysql_fetch_assoc($row)){
       echo  "$data[categoryname]";   
 
$sql2 = "SELECT * FROM `posts` WHERE `blog_cat_id` = `post_cat_id`";
    $row2 = mysql_query($sql2);
while($data2 = mysql_fetch_assoc($sql2)){
       echo "$data2[post_title]";     
}
}

[KamranArshad]

Can you tell name fields of these two tables you are using, because i think blog_cat_id should be in blog_category table.

i think you have to get blog_cat_id of outer loop like

$sql2 = "SELECT * FROM `posts` WHERE `blog_cat_id` = `$row[$id]`";

[pritaeas]

Why all those queries if just one does the job:

$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);
$cat_name = '';
while ($data = mysql_fetch_assoc($rows)) {
  if ($cat_name <> $data['cat_name']) {
    echo "{$data['cat_name']}<br/>";
    $cat_name = $data['cat_name'];
  }
  echo "{$data['post_title']}<br/>";
}

[KamranArshad]

There are lots of way to solve one problem, I always prefer simplest way to solve the problem, Which can b understand by any reader.

[luweegee]

@priteas thanks your code was exactly same too..however your code was a little difficult to understand now im dead how can i make an array of that query...another problem huhu

[pritaeas]

What should your array contain ?

[luweegee]

The category and post_title i call this parts to the other page that's why i use to make array...i don't know if the position was still the same

[luweegee]

is that possible? sorry for double post

[KamranArshad]

Can you post the result of this code

$count = 0;
$sql = "SELECT * FROM `blog_category`";
      $row = mysql_query($sql);
while($data = mysql_fetch_assoc($row)){
       echo  "$data[categoryname]";   
 		$sql2 = "SELECT * FROM `posts` WHERE `blog_cat_id` = `post_cat_id`";
		$row2 = mysql_query($sql2);
		while($data2 = mysql_fetch_assoc($sql2)){
       		echo $count = $count + 1;     
		}
}

according to me your query

$sql2 = "SELECT * FROM `posts` WHERE `blog_cat_id` = `post_cat_id`";

not working properly.

[luweegee]

haha i forgot this is the correct `post_cat_id`='".$data."'

[KamranArshad]

That's what i was saying, your inner query is not working properly, it must related to outer query. Now please mark this as solved
regards

[luweegee]

i have to put an array of the category name and the post_title to call it on the other page but the position was still the same is that possible?