php - help getting results into an array

[danmurf]

I was wondering if someone could help - I'm having trouble getting my sql results into an array which I can then print with a loop.

//Add the words to each scale array if any were selected
foreach ($scales as $key => $scale) {

	$query = "SELECT word FROM words WHERE resultid = ".$resultsid." AND scale = '".$scale."'";
	$result = mysql_query($query);
	$row = mysql_fetch_row($result);

	if (mysql_num_rows($result) != 0 ) {
	
		${$scale . "WordsList"} = "<ul>"; 
		
		//Add each word to the scaleWordsList array
		foreach ($row as $words => $value) {
			${$scale . "WordsList"} .= "<li>" . $value."</li>";
		}
		
		${$scale . "WordsList"} .= "</ul>";
		${$scale . "WordsCount"} = count($row);
		
	} else {
	
		${scale . "WordsCount"} = 0;
		
	}
	
}

$scales is an array, and each value in the array gets broken down and put in $scale for the first loop - this works fine. The SQL runs and should return as many rows are there are with that scale in the table. But for some reason, with this setup, I'm only getting one value in $row per loop. Any ideas why it's not returning more?

Any advice appreciated

Many thanks

 

[Jaffa_Cake]

Try

while($row = mysql_fetch_row($result)) {

then add the corresponding } at the end of the loop. You are not looping it, which is why its not displaying in a loop The current code you are using will just get 1 row.

 

[danmurf]

That seemed to do the trick

Here's the full working code:

//Add the words to each scale array if any were selected
foreach ($scales as $key => $scale) {

	$query = "SELECT word FROM words WHERE resultid = ".$resultsid." AND scale = '".$scale."'";
	$result = mysql_query($query);


	if (mysql_num_rows($result) != 0 ) {
	
		${$scale . "WordsList"} = "<ul>"; 
		
		//Add each word to the scaleWordsList array
		while($row = mysql_fetch_row($result)) {
			${$scale . "WordsList"} .= "<li>" . $row[0]."</li>";
		}
		
		${$scale . "WordsList"} .= "</ul>";
		${$scale . "WordsCount"} = count($row);
		
	} else {
	
		${scale . "WordsCount"} = 0;
		
	}
	
}

Many thanks for your help!

 

[Jaffa_Cake]

If you want to make things easier to read you can use

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

this will allow you to use $row['rowname'], $row['id'] etc

 

[psyr33n]

Or just use mysql_fetch_assoc() to save some typing.