How do I convert a query result into variables?

[Skipjackrick]

Basically something like this...

 

Query the database to get the info.....

 

<?php
$query = "SELECT 
                angler, team_id
	FROM anglers
	WHERE team_id=1
	GROUP BY angler
                LIMIT 4; 

$angler = mysql_query($query) or die(mysql_error());
?>

 

Then make the result variables.

 

For example,

 

$anglerA = 1st result

$anglerB = 2nd result

$anglerC = 3rd result

$anglerD = 4th result

 

 

This way I could use the variables throughout the code in the remaining page.

 

I am not sure how to do this?

[Philip]

If you're wanting to set the variable name,

$tempVar = "myVar";
$$tempVar = "myValue";
echo $myVar;
// prints "myValue"

 

[Skipjackrick]

If you're wanting to set the variable name,

$tempVar = "myVar";
$$tempVar = "myValue";
echo $myVar;
// prints "myValue"

 

 

???  Confused ??? 

[Philip]

Okay, what exactly are trying to do, I think I misunderstood?

 

anglerA <- would be the value coming out of the DB, or just the variable holding the value?

 

[Skipjackrick]

Okay, what exactly are trying to do, I think I misunderstood?

 

anglerA <- would be the value coming out of the DB, or just the variable holding the value?

 

 

$anglerA <-would be the variable holding the value from the query result.

 

The result from the query would probably look like this

 

<?php
$query = "SELECT 
                angler, team_id
      FROM anglers
      WHERE team_id=1
      GROUP BY angler
                LIMIT 4; 
    
$angler = mysql_query($query) or die(mysql_error());
?>

 

Result from query

Oz

Skip

Scott

Curtis

 

Then I want to have php do this

 

$anglerA = Oz

$anglerB = Skip

$anglerC = Scott

$anglerD = Curtis

[Philip]

Okay,

 

<?php
$query = "SELECT 
                angler, team_id
	FROM anglers
	WHERE team_id=1
	GROUP BY angler
                LIMIT 4;" 

$angler = mysql_query($query) or die(mysql_error());

// run a loop, to get each row:
while($row = mysql_fetch_array($angler)) {
// I'd recommend using an array, I think it'd be easier:

// now, this will create a multi-dimensional array. if you just want one value you can call it via row
// and the column name in your DB (i.e. $row['id']) 
$results[] = $row;
}

echo '<pre>'; // just for formatting, open
// show your variables: 
print_r($results); 
echo '</pre>'; //just for formatting, close
?>

[Skipjackrick]

Cool,

 

So my result looks like this

 

Array
(
    [0] => Array
        (
            [0] => 1
            [angler] => 1
            [1] => 1
            [team_id] => 1
        )

    [1] => Array
        (
            [0] => 2
            [angler] => 2
            [1] => 1
            [team_id] => 1
        )

    [2] => Array
        (
            [0] => 3
            [angler] => 3
            [1] => 1
            [team_id] => 1
        )

)

 

How do I utilize this through the rest of my page?

 

Would I just put

 

$row0  or  $row2

 

In the spots I want that particular variable?

[Philip]

$results[0]['angler'];

Would show '1'

 

Also, I didn't think about it until now, but change this line:

while($row = mysql_fetch_array($angler)) {

to:

while($row = mysql_fetch_assoc($angler)) {

 

It'll change your array a bit:

Array (

    [0] => Array (

                  [angler] => 1

...

 

Basically, just the name key's instead of the numeric ones too

[Skipjackrick]

Ok, I have hacked away at this for an hour trying to figure out how to get this to work.  No luck.

 

How do I get the new variables in the query?  It just doesn't work for me.

 

I keep getting syntax errors. and I think its right here --->

 

$query_anglertotals = "SELECT

                        species_id,

                        SUM(IF(angler='$results[0]['angler']',1,0)) AS anglerA,

This is my code.

 

<?php
//Connect to MySQL
include '/home/extremf3/dbconnect.php';

$query = "SELECT angler, team_id
      FROM anglers
      WHERE team_id=1
      GROUP BY angler
                LIMIT 5"; 
    
$anglervar = mysql_query($query) or die(mysql_error());

// run a loop, to get each row:
while($row = mysql_fetch_assoc($anglervar)) {

   $results[] = $row;
}

$query_anglertotals = "SELECT
                         species_id,
                         SUM(IF(angler='$results[0]['angler']',1,0)) AS anglerA,
                         SUM(IF(angler='$results[1]['angler']',1,0)) AS anglerB,
                         SUM(IF(angler='$results[2]['angler']',1,0)) AS anglerC,
                         SUM(IF(angler='$results[3]['angler']',1,0)) AS anglerD,
                         SUM(IF(angler='$results[4]['angler']',1,0)) AS anglerE,
                         SUM(IF(team_id=1,1,0)) AS teamtotal
                         FROM submit
                         WHERE species_id<25 AND yyyy=2008 AND team_id=1
                         GROUP BY species_id
                         ORDER BY species_id";

$anglertotals = mysql_query($query_anglertotals) or die(mysql_error());
while($row = mysql_fetch_array($anglertotals))
{
$anglerA = $row['anglerA'];
$anglerB = $row['anglerB'];
$anglerC = $row['anglerC'];
$anglerD = $row['anglerD'];
$anglerE = $row['anglerE'];
$teamtotal = $row['teamtotal'];
$species_id = $row['species_id'];

get_species($species_id);

$kayakwars_totals2 .=<<<EOD
<tr>
	<td align='center'>$speciesname</td>
	<td align='center'>$anglerA</td>
	<td align='center'>$anglerB</td>
	<td align='center'>$anglerC</td>
	<td align='center'>$anglerD</td>
	<td align='center'>$anglerE</td>
	<td align='center'>$teamtotal</td>
</tr>
EOD;
}
?>