MySQL query inside a function.... (easy answer I think)

[adrianTNT]

Hello,

I have this php code... it reads a table "transactions", calculates the sum of the transactions where transactions belong to a certain user ID and should return the SUM of the transactions.

Code I have now is:
[code]
<?php
mysql_select_db($database_affiliates, $affiliates);
$query_Recordset_total_to_pay = "SELECT SUM(tr_amount) FROM transactions WHERE tr_user_id = '1'";

$Recordset_total_to_pay = mysql_query($query_Recordset_total_to_pay, $affiliates) or die(mysql_error());
$row_Recordset_total_to_pay = mysql_fetch_assoc($Recordset_total_to_pay);
$totalRows_Recordset_total_to_pay = mysql_num_rows($Recordset_total_to_pay);

$arr = $row_Recordset_total_to_pay;
$sum = 0;
foreach($arr as $element){
$sum += $element;
}
$total_to_pay=$sum;
?>
[/code]
The above code works ok and shows the SUM of transactions for USER ID "1".
I want to place that code block in a function so that I can find the transactions sum for a given user ID.
[code]
<?php
function get_balance($the_user_id){
mysql_select_db($database_affiliates, $affiliates);
$query_Recordset_total_to_pay = "SELECT SUM(tr_amount) FROM transactions WHERE tr_user_id = '".$the_user_id."'";

$Recordset_total_to_pay = mysql_query($query_Recordset_total_to_pay, $affiliates) or die(mysql_error());
$row_Recordset_total_to_pay = mysql_fetch_assoc($Recordset_total_to_pay);
$totalRows_Recordset_total_to_pay = mysql_num_rows($Recordset_total_to_pay);

$arr = $row_Recordset_total_to_pay;
$sum = 0;
foreach($arr as $element){
$sum += $element;
}
$total_to_pay=$sum;
return $total_to_pay;
}
?>

<?php
// now trying to get the transactions sum (balance) for user id 1
echo get_balance(1);
?>
[/code]

The second code returns these errors:
[b]Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /var/www/vhosts/adriantnt.com/httpdocs/affiliates/admin_users.php on line 59

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /var/www/vhosts/adriantnt.com/httpdocs/affiliates/admin_users.php on line 62[/b]
Maybe I didnt place the code block in the function correctly or I incorrectly used the "return"?!
I am still new to php, any help is appreciated.

- Adrian.

[Jenk]

The error is exactly what it says on the tin.. you are not passing mysql resource handlers to the functions specified.

[predator]

in other words make sure your database is connecting and secondly make sure there is a value in the variable you are passing the statement
Regards
Mark

[adrianTNT]

[b]Jenk[/b], the other variables $database_affiliates, $affiliates are defined above in the rest of the code, if these variables are inside the function do I have to send them to the function each time I call it?

[b]Predator[/b], I don't know... I think I am not passing the user data to the function correctly. I tried to define the user_id before I call the function
[code]<?php
$the_user_id="1";
echo get_balance($the_user_id);
?>[/code]
But I get same errors.

[adrianTNT]

Since it says [b]Warning: mysql_select_db(): supplied argument is not a valid[/b]
Then it means that it doesnt know what is $database_affiliates, $affiliates in mysql_select_db code.
Do I have to pass these values to the function once with the user_id?
When the same query is outside of a function then it works fine.

[Jenk]

you don't have to, you can 'call upon' them within the function with the global key word..

change to:[code]<?php

function get_balance($the_user_id){
            global $database_affiliates, $affiliates;
mysql_select_db($database_affiliates, $affiliates);

?>[/code]

[adrianTNT]

[quote author=Jenk link=topic=107362.msg430706#msg430706 date=1157733786]
you don't have to, you can 'call upon' them within the function with the global key word..

change to:[code]<?php

function get_balance($the_user_id){
            global $database_affiliates, $affiliates;
mysql_select_db($database_affiliates, $affiliates);

?>[/code]
[/quote]
Yes, that worked fine, I didn't knew that I have to access them from an upper level when working in a function.
I also tried to send them in the function parameters when defining the function and when calling the function, that also worked; but your metod is better.

Thank you very much. :)

[Jenk]

It's variable scope; have a read :)

http://php.net/manual/en/language.variables.scope.php