Calling data from two tables and multiplying it?

[doomdude]

Hey guys,

 

Not sure how to do this. The basic principle of what I want to do is:

 

ounit1 number (members table) * ounit1 power (ounit table)

ounit2 number (members table) * ounit2 power (ounit table)

 

etc to unit12.

 

The members table has the fields:

 

username ounit1 ounit2 ounit3 ounit4 etc for the number of units.

 

The Ounit table has the fields:

 

Ounit (primary auto increase) opower and a few more that are not needed for this.

 

So basically, it it possible to  call data from two different tables and multiply it?

 

So far I've got:

 

<?php 

        $result = mysql_query("SELECT * FROM members WHERE Username='$_SESSION[username]'") 
                or die(mysql_error());  

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

			echo '<b>Power</b><br />';
                                echo 'Offence: ' . $row[''] . ' <br />';
			echo 'Defence: ' . $row[''] . ' <br />';
			echo 'Power: ' . $row[''] . ' <br />';

	}
?>

 

I'm not sure how to call two tables at once.

 

Any help would be great!

 

 

[hyster]

just run 2 queries then add what u want. just rename the query $vars diffrent IE: $result1 and $rows1 instead of $result and $rows.

 

something like below ( did of the top of my head so probs dont work ut give u the idea)

 

 <?php
        $result = mysql_query("SELECT * FROM members WHERE Username='$_SESSION[username]'")                 
        or die(mysql_error());  				    	
        while($row = mysql_fetch_array( $result )) {   						
            echo '<b>Power</b><br />';                                
            echo 'Offence: ' . $row[''] . ' <br />';				
            echo 'Defence: ' . $row[''] . ' <br />';				
            echo 'Power: ' . $row[''] . ' <br />';						
        }
        
       $result1 = mysql_query("SELECT * FROM data WHERE Username='$_SESSION[username]'")                 
        or die(mysql_error());  				    	
        while($row1 = mysql_fetch_array( $result1 )) {   						
            echo '<b>Power</b><br />';                                
            echo 'Offence: ' . $row1[''] . ' <br />';				
            echo 'Defence: ' . $row1[''] . ' <br />';				
            echo 'Power: ' . $row1[''] . ' <br />';						
        }
        
$vardata = $rows['data1'] + $rows1['data2'] ;

echo $var;    
        
?>

[doomdude]

Not sure I explained it correctly.

 

I tried: But I get an error.

 

 <?php
        $result = mysql_query("SELECT * FROM members WHERE Username='$_SESSION[username]'")                 
        or die(mysql_error());  				    	
        while($row = mysql_fetch_array( $result )) {   						
            ' . $row['ounit1'] . ';					
        }
        
       $result1 = mysql_query("SELECT * FROM ounit WHERE ounit='1'")                 
        or die(mysql_error());  				    	
        while($row1 = mysql_fetch_array( $result1 )) {   						
	    ' . $row['opower'] . ';			
        }
        
$vardata = $rows['ounit1'] * $rows1['opower'] ;

echo $var;    
        
?>

 

I've got the table "members" which has each persons unit numbers in it (how many they have).

 

I've then got a table called "ounit" which has all of the units cost and power (opower).

 

I'm trying to get the number of units from the members table and multiply it by the opower in the ounit table.

 

And to top it off, I need to do this for each of the 12 units, then add all of the total power of the units up to equal offence power.

 

 

[hyster]

ur echoing $var not $vardata

 

$vardata = $rows['ounit1'] * $rows1['opower'] ;
echo $var;

 

what i do to trouble shoot somethin like this would be

 

echo $rows['ounit1'];
echo "</br>";
echo $rows1['opower']

 

if the above worked then id do

 

$vardata = $rows['ounit1'] * $rows1['opower'] ;
echo $rows['ounit1'];
echo "</br>";
echo $rows1['opower']
echo "</br>";
echo $vardata;

 

then to finish it

 

$vardata = $rows['ounit1'] * $rows1['opower'] ;
echo $vardata;

[doomdude]

I've been playing with it and I've got to this point:

 

<?php
        $result = mysql_query("SELECT * FROM members WHERE Username='$_SESSION[username]'")                 
        or die(mysql_error());  				    	
        while($row = mysql_fetch_array( $result )) {   						
             $row['ounit1'] = $ounit1;					
        }
        
       $result1 = mysql_query("SELECT * FROM ounits WHERE ounit='1'")                 
        or die(mysql_error());  				    	
        while($row1 = mysql_fetch_array( $result1 )) {   						
	    $row['opower'] = $opower1;			
        }
        
echo $ounit1;   
echo $opower1; 
        
?>

 

Trying just to echo the data first before multiplying it, however I'm having issues and it returns blankness lol.

[hyster]

does ur $_SESSION[username] hold a value? echo it out and see.

 

go into phpmyadmin and put the queries directly into the sql option and see what results u get

SELECT * FROM ounits WHERE ounit='1'

 

replace $_SESSION[username] with a name u no exists

SELECT * FROM members WHERE Username='$_SESSION[username]' // can also try this directly in php with a known name.

[doomdude]

I've got echo username on my site on the same page:

 

You are logged in as <?php echo $_SESSION['Username']	; ?>

 

Which works fine:

 

You are logged in as Admin.

 

 

Doing:

 

SELECT * FROM ounits WHERE ounit='1'

 

Returns:

 

 

Doing:

SELECT * FROM members WHERE Username='devildog'

 

Returns:

 

All seem to work. Just not through php lol. I've got a feeling I've wrote the query wrong some how.

 

 

 

[PFMaBiSmAd]

If your ounits table contains 12 rows, you would want to use a query that gets all 12 of those rows into a result set.

 

I would then pre-preocess the ounits data to get the opower values into an array that has the same index names (ounit1, ounit2, ..., ounit12) as what the member $row['...'] query data will have. You can then simply iterate over the index names ounit1, ounit2, ..., ounit12 to access both the member's value and the corresponding opower value.

 

Edit: You are retrieving only one row from your members table. There's no need to use a while() loop. Your assignment statements  $row['ounit1'] = $ounit1; and $row['opower'] = $opower1; have the left-hand and right-hand side backward.

[doomdude]

If your ounits table contains 12 rows, you would want to use a query that gets all 12 of those rows into a result set.

 

I would then pre-preocess the ounits data to get the opower values into an array that has the same index names (ounit1, ounit2, ..., ounit12) as what the member $row['...'] query data will have. You can then simply iterate over the index names ounit1, ounit2, ..., ounit12 to access both the member's value and the corresponding opower value.

 

Edit: You are retrieving only one row from your members table. There's no need to use a while() loop. Your assignment statements  $row['ounit1'] = $ounit1; and $row['opower'] = $opower1; have the left-hand and right-hand side backward.

 

Wow, there's still a lot for me to learn in php lol. That pretty much went over my head.

 

I think your saying there is a way to call all of the units opower from the ounits table, and then multiply them by the number of ounits in the members table, which I'd then be able to add up all of the power totals to equal the offence power?

 

Could you possibly point me at an example of calling multiple rows into variables?

 

Thanks for your help.

[PFMaBiSmAd]

<?php
// get the ounit data
$result = mysql_query("SELECT * FROM ounits")
	or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
	$opower['ounit' . $row['ounit']] = $row['opower'];
}

echo '<pre>Power:',print_r($opower,true),'</pre>'; // dump the power data for demo purposes	

// get the member's row of data
$result = mysql_query("SELECT * FROM members WHERE Username='$_SESSION[username]'")
	or die(mysql_error());
$row = mysql_fetch_assoc($result); // $row['ounit1'] .. $row['ounit12'] contains the member's data

echo '<pre>Row:',print_r($row,true),'</pre>'; // dump the member's data for demo purposes

// multiply the corresponding data together
foreach($row as $key=>$value){
	if(strpos($key, 'ounit') !== false){ // use only the 'ounitx' elements
		$arr[$key] = $value * $opower[$key];
	}
}

echo '<pre>Mult:',print_r($arr,true),'</pre>'; // dump the multiplied data for demo purposes

$total = array_sum($arr); // get the total
echo "Total: $total";
?>

 

Edit: fixed left-over result1 in code.