Substr_replace Issue and Question?

PHP
1

I’ve wrote a function which outputs the array data from the database and in turn finds out the values from a column in the table for the user which groups they belong to which is stored such as:

1,2,3

The above represents the id for the groups in the group table and I use explode to check these in the final two functions and return the group names seperated by commas as a string value. The issue I have is firstly I want to trim the last comma from the string which I can’t seem to do with ‘substr_replace’

Secondly I want the string to be returned so I can show it in the main output in HTML but it doesn’t seem to display either.

Any of the issues you can help with would be great.

Thanks

public function getTwitterIndexList() {
		$dbo = new Database();
			if ($result = $dbo->_query("SELECT * FROM `journalist` ORDER BY `journalist_cdr_rating` DESC")) {
			if ($rows = $dbo->getObjectList($result)) {
				$i = 1;
				foreach ($rows as $journo) {
					$output = '<tr>';
					$output.='<td class="rank">'.$i.'</td>';
					$output.='<td class="info">';
					$output.='<img src="./images/icon_sample.gif" alt="'.$journo->journalist_name.'" />';
					$output.='<h2>'.$journo->journalist_name.'</h2>';
					$output.='<p><a href="http://www.twitter.com/'.$journo->journalist_username.'" target="_blank" class="twitterlink">@'.$journo->journalist_username.'</a><br /><span class="type">Publication<br />Groups: ';
					$output.= $this->getGroups($journo->journalist_groups);
					$output.='</span><br /><a href="#" target="_blank" class="weblink">'.$journo->journalist_web_address.'</a></p>';
					$output.='</td>';
					$output.='<td class="rated">'.$journo->journalist_klout_score.'</td>';
					$output.='<td class="rated">'.$journo->journalist_peer_score.'</td>';
					$output.='<td class="rated cdr">'.$journo->journalist_cdr_rating.'</td>';
					$output.='<td class="rated r_button"><a href="/?screename='.$journo->journalist_username.'"><span class="hidden">View More</span></a></td>';
					$output.='</tr>';
					echo $output;
					$i++;
					//print_r($rows);
				}
			}
		}
		return false;
	}

	public function getGroups($groups) {
		$list = explode(",",substr($groups,0));
		foreach ($list as $item) {
			$output = $this->getGroupNameById($item);
			$grouplist = substr_replace($output,"",-1); 
			echo $grouplist;

		}		
					
	}

	public function getGroupNameById($id) {
		$dbo = new Database();
		$output = '';
			if ($result = $dbo->_query("SELECT `group_name` FROM `groups` WHERE `group_id`=".intval($id)." LIMIT 1")) {
			if ($row = $dbo->getObject($result)) {
				$output.= $row->group_name.', ';
				return $output;
			}

		}
		return false;
	}
2

If I’m reading it correctly it looks there’s a comma and a space at the end of the string so you need to take off two characters, but instead of substr_replace I think you’d be better off using substr:

$grouplist = substr($output, 0 ,-2);

Personally though, in the getGroupNameById function I’d put the values in an array then implode it with ", " as the separator.

3

this design is less than optimal

you use one query to retrieve the journalists, and then for each journalist, you perform an additional query several times, in a loop, for each group the journalist belongs to

let me know if you ever want to redesign this, because it can be made much more efficient

4

I’d advise against using substr for removing ", ".
The whitespace can sometimes be stripped in some cases, for no apparent reason in PHP, especially when inserting/retrieving from a database.
If the whitespace is removed, and you substr -2, you will remove the comma and a groupid.

Consider using

$grouplist = trim($output, ', ');

This will remove either a comma, or comma and whitespace. More reliable.

As for your query… i agree with the user above.
Too many unnecessary queries.

Look into FIND_IN_SET(), this will help minimise the amount of queries, as you don’t have to to perform them in a loop.
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

5

FIND_IN_SET works, but won’t scale – it requires a table scan

only a redesign of that column will lead to optimization, because it violates first normal form