Jump to content


Photo

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


  • Please log in to reply
7 replies to this topic

#1 adrianTNT

adrianTNT
  • Members
  • PipPipPip
  • Advanced Member
  • 146 posts

Posted 08 September 2006 - 04:19 PM

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:
<?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;
?>
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.
<?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);
?>

The second code returns these errors:
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

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.

#2 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 08 September 2006 - 04:22 PM

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

#3 predator

predator
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts
  • LocationEngland

Posted 08 September 2006 - 04:27 PM

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
<a href="http://www.mandppcs.co.uk">M & P PC's Website and Software Design & Development Service's</a>

#4 adrianTNT

adrianTNT
  • Members
  • PipPipPip
  • Advanced Member
  • 146 posts

Posted 08 September 2006 - 04:40 PM

Jenk, 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?

Predator, 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
<?php 
$the_user_id="1";
echo get_balance($the_user_id);
?>
But I get same errors.

#5 adrianTNT

adrianTNT
  • Members
  • PipPipPip
  • Advanced Member
  • 146 posts

Posted 08 September 2006 - 04:42 PM

Since it says Warning: mysql_select_db(): supplied argument is not a valid
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.

#6 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 08 September 2006 - 04:43 PM

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

change to:
<?php

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

?>


#7 adrianTNT

adrianTNT
  • Members
  • PipPipPip
  • Advanced Member
  • 146 posts

Posted 08 September 2006 - 04:51 PM

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

change to:

<?php

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

?>

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. :)

#8 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 08 September 2006 - 04:53 PM

It's variable scope; have a read :)

http://php.net/manua...ables.scope.php




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users