Help with Query code

[dotbands]

I am currently re-writing a site from coldfusion into php. I have one section within a loop that pulls a query out of the database and then runs a query on the result to build the array that will be used later on in the script.

I have the following code that was used in coldfusion:

<cfquery name="Score#MyNum#" DBTYPE="query">
SELECT Score, Answer FROM ScoreA WHERE Answer = '#Questlist.Answer#'
</cfquery>

Where #MyNum# is the number of the loop (there are 20 iterations), ScoreA is a query run earlier in the script as is Questlist.

This gives out the following (example) result:

Score1.Score = 1, Score2.Score = 4, Score3.Score = 10, etc.

Is it possible to do this in php? or will i have to do it a long winded way.

Thanks for any help.

[bbaassiri]

you might have to do it differently but from the looks of it you can create a variable array that contains all of your results

Score[0]['Score'] = 1
Score[1]['Score'] = 4
Score[2]['Score'] = 10

IMHO, I prefer this approach since I have one variable that is an array that contains an associative array with all of my query results.

/* foreach of the results from the query save it  as an array */
//you will have set it you query but the result is like such
$Score[]['Score'] = $result_from_query;

Hope this helps

[dotbands]

i think i actually figured a way around it. Is this the best way to do it:

include_once("config.php");

$Tot1=0;
$Tot2=0;
$GrTot=0;
$QuestionnaireID=1;
$MyNum=1;
do {

mysql_select_db($cfgDbDatabase, $asset);
$query_ScoreA = sprintf("SELECT ScoreRef.Question, ScoreRef.ScorePattern, PatLink.Answer, PatLink.Score 
  FROM ScoreRef INNER JOIN PatLink ON ScoreRef.ScorePattern = PatLink.Pattern 
  WHERE ScoreRef.Question='%s'", $MyNum);
	$ScoreA = mysql_query($query_ScoreA, $asset) or die(mysql_error());
	$row_ScoreA = mysql_fetch_assoc($ScoreA);
	$totalRows_ScoreA = mysql_num_rows($ScoreA);


mysql_select_db($cfgDbDatabase, $asset);
$query_Questlist = sprintf("SELECT (Question$MyNum) as Answer, QuestionnaireID FROM Questlist WHERE QuestionnaireID 
  = '%s'", $QuestionnaireID);
	$Questlist = mysql_query($query_Questlist, $asset) or die(mysql_error());
	$row_Questlist = mysql_fetch_assoc($Questlist);
	$totalRows_Questlist = mysql_num_rows($Questlist);

$i=1;
do {
if( $row_ScoreA['Answer'] = $row_Questlist['Answer']) {

	$ScoreMyNum['Score'] = $row_ScoreA['Score'];
	$ScoreMyNum['Answer'] = $row_ScoreA['Answer'];

	echo $ScoreMyNum['Score'] ."<br>";
	echo $ScoreMyNum['Answer'] ."<br>";

	if( $MyNum < "11") {
		$Tot1 += $ScoreMyNum['Score'];
		} else {
		$Tot2 += $ScoreMyNum['Score'];
		}

	$i=6;
	} else {
	++$i;
	}
} while ($i < 6);


  ++$MyNum;
} while ($MyNum < "21");

$GrTot = $Tot1 + $Tot2;

echo $Tot1;
echo $Tot2;
echo $GrTot;

It seems to work, although i've only run it with test data.

[bbaassiri]

looks ok, just like any problem in computing there are many solutions,

the only things i might add is the mysql_select_db can be done outside the do while

happing coding