Page 1 of 1

Trouble creating a single string out of a mysql result set

Posted: Fri Jul 22, 2011 5:04 am
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.

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

Posted: Fri Jul 22, 2011 6:43 am
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