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.

Also, you can compress your SQL into a single statement using IN like this: $s= "SELECT * FROM news_categories WHERE cat_id IN ($post_cats_data)" — leepowers
– leepowers, Commented Nov 25, 2009 at 0:02

Marek Karbarz · Accepted Answer · 2009-11-24 23:45:02Z

5

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.

answered Nov 24, 2009 at 23:45

Marek Karbarz

29.4k6 gold badges55 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brooke. Over a year ago

That worked, now I feel silly. I also hate that I was sooo close!

robjmills · Accepted Answer · 2009-11-25 09:07:44Z

0

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);

edited Nov 25, 2009 at 9:07

answered Nov 24, 2009 at 23:51

robjmills

18.6k16 gold badges81 silver badges124 bronze badges

2 Comments

Brooke. Over a year ago

What is the "0" for at the end of the query is that for error reporting?

robjmills Over a year ago

the 0 is the row number from the result set that's being retrieved

Collectives™ on Stack Overflow

Php explode then get the name row from a MySql Table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related