Trouble creating a single string out of a mysql result set

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Trouble creating a single string out of a mysql result set

Post by drayarms »

Hello all, I'm trying to display the contents of a mysql select query in a particular format. So basically, the result of the query returns 3 rows with values, red, white, and blue. I want to display these result in a single string in the following format: 'red', 'white', 'blue'
So I use this code in an attempt to achieve that:

Code: Select all

$query = "SELECT * FROM table WHERE id = 'someNumber' ";
										
$result = mysql_query($query); 

while ($row = mysql_fetch_assoc($result)){
                                       
$colors = " ' ".$row['color']." ' " . "," ;

echo $colors;

$col = substr($colors, 0, -1); 

echo $col;

}
So the idea is to wrap each of the row values(red white and blue) within single quotes and separate them with commas. Then I try to trim the last comma after the last entry, in this case blue to get my expected result. As expected the $colors variable reads:

'red', 'white', 'blue',

But the $col variable reads:

'red' 'white' 'blue' instead of the expected 'red', 'white', 'blue'

So the substr() function instead of just removing the last comma from the $colors string, removes the the last comma from each of the components of that string. Now I thought that PHP would treat $colors as just one string but apparently, that's not the case. How I can make it so? Can anyone tell me what to do next? Every other string trimming function I tried yielded the same undesirable result. Thanks.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Trouble creating a single string out of a mysql result s

Post by social_experiment »

Code: Select all

<?php
$query = "SELECT * FROM table WHERE id = 'someNumber' ";                                                                                
$result = mysql_query($query); 
while ($row = mysql_fetch_assoc($result)){                                       
$colors = " ' ".$row['color']." ' " . " " ; 
echo $colors; 
// displays color color color 
// removes whitespace
trim($colors); 
// creates an array
$colorArray = explode(' ', $colors) 
// creates a string where each array element
// is seperated by a comma
$col = implode(',' $colorArray);
} 
echo $col;
?>
What happens if you try the code above
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply