Php explode then get the name row from a MySql Table

Question

I know this has been talked about here before. However, my situation is a bit different and I'm so close.

I would like to explode an array of category id's from a news data table then get the category name from the category table.

Right now I am able to get the id's out and explode them just fine inside the while loop for the news data using:

    $post_cats_data = $news_data['cat_id']; // 1,6,7,11
    $post_cats_id = explode(",", $post_cats_data);

Now where I'm getting stuck is getting the news categories and echoing out the name.

 $cat_count = count($post_cats_id);
 $i = 0;
     foreach($post_cats_id as $cat_id){
       $cat_qry = mysql_query("SELECT * FROM news_categories WHERE `cat_id` = '$cat_id'") or die(mysql_error());
 $cat_title_row = mysql_fetch_row($cat_qry) or die(mysql_error());
   $i++;
   $write_cat .= $cat_title_row['cat_name'] ;
  if($i<$cat_count){
     $write_cat .= ', ';
  }

 }

The idea is that this will get the category names from the category tables that were exploded and will add a comma back to the end of everyone but the last one. I am unable to get the category name and when I return the ID it loops though the id for all the news.

I know this is a simple problem, I'm just new to using loops.

Thanks.

Answer 1

mysql_fetch_row returns an array indexed at 0, so $cat_title_row['cat_name'] will not give you the desired results. Use mysql_fetch_assoc instead and it should work fine.

From PHP manual:

mysql_fetch_row() returns an numerical array of strings that corresponds to the fetched row, or FALSE if there are no more rows.

mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

Answer 2

you could just use mysql_result like this:

$cat_title = mysql_result(mysql_query("SELECT cat_name FROM news_categories WHERE `cat_id` = '$cat_id'"),0);

if you want them all in a comma delimited list you could use implode like this:

$cat_titles = array();
foreach($post_cats_id as $cat_id){
  $cat_title = mysql_result(mysql_query("SELECT cat_name FROM news_categories WHERE `cat_id` = '$cat_id'"),0);
  $cat_titles[] = $cat_title;
}
$comma_cat_titles = implode(',',$cat_titles);

To get it all done in a single query you could do something like:

$cat_titles = mysql_result(mysql_query("SELECT GROUP_CONCAT(`cat_name` SEPARATOR ',') FROM `news_categories` WHERE `cat_id` IN (".$post_cats_data.")"),0);