0

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>";
			}

Edited by luweegee: n/a

3
Contributors
15
Replies
17
Views
5 Years
Discussion Span
Last Post by luweegee
0

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

Edited by kamran_FrizCon: n/a

0

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

Edited by luweegee: n/a

0

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

Edited by kamran_FrizCon: n/a

0

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]";     
}
}
0

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]`";

0

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/>";
}
0

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.

0

@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

Edited by luweegee: n/a

0

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

Edited by luweegee: n/a

0

is that possible? sorry for double post

Edited by luweegee: n/a

0

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.

Edited by kamran_FrizCon: n/a

0

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

Edited by luweegee: n/a

0

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

Edited by kamran_FrizCon: n/a

0

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?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.