how to make more than one variable out of multiple SQL info

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

how to make more than one variable out of multiple SQL info

Post by jramaro »

Hi ,
Ive set up a database to handle user cart/ basket.
It stores it to database, and will erase at the end. So it only holds the info temporarily. I think that part is cool
because i wont be confusing and tangling all my SESSIONS that i have goin on.

the problem Im having is , the records all are fetched from the database and display fine.
but at the end i want to add up all the totals. and also output all the product info to a mailed receipt/invoice mail form.

and so when i make a SESSION for something like total. its only going to catch the last variable and not all of the unique instances of total.

so how can i make them each unique to assign them to their own variable , so that i can add them all up for some grand total or somethin?

Also, Im trying to figure out best way to echo all the cart info out to a mail form . where each variable would again have to be unique instances.

Any idea? :D

Thank You !
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

In order to sum up a total for your basket simply sum your values using a 'while loop'.

In terms of "echoing" the cart info for a mail shot, i'd store the cart info/html in a single variable and then echo that variable as the $message.
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

ah thanks,
I've never used one of them before.

whats the logic of how to get the instances of $total , in order to sum it ?

Code: Select all

while($total > 0 ) {
 sum($total) ; 
 }


There's my first rough thought on it , but thats wrong because of many reasons . $total would keep executing as true , because even if there
were one $total variable ,it would still be greater than 0 .


wouldnt i have to put the while loop on the SQL query
like this:

Code: Select all

while($num=mysql_num_rows($result)  ) {
sum($row['total']) ; 
}

Thanks for your help
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

Put this inside your loop:

Code: Select all

//+= is the same as x+x=y

$totalSum+=number_format($var_to_be_counted, 2, '.', ''); 

//number_format() helps you to format the number
//the number format i used formats the number like this 1234.54
Hope this helps.
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

cool thanks ..BUT !

havin a problem though, the while loop is running in an infinite i think

Code: Select all

while($result=mysql_query ($query)) { 
$totalSum+=number_format($total_cart, 2, '.', ''); 
}


I think Im setting up the original while loop wrong?
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

dont loop the query, loop the fetch object

Code: Select all

$query = mysql_query($sql);
while($res = mysql_fetch_row($query)){
    echo $res;
}
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

Here's what i have already making the query and fetch to the database.

Code: Select all

$query= "SELECT *  FROM user_cart_records WHERE user_id = " . $user_row['user_id'] ; 
$result=mysql_query ($query); // Run the query. 
$num=mysql_num_rows($result);


if i put the while loop on the $num rows - it'll run forever

are you sure i should run that while loop on there?

Thanks!
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

mysql_num_rows just returns the number of rows affected by the query. you want to do the while loop on the function that retrieves the rows, such as mysql_fetch_row, which returns false when its out of rows and breaks the loop.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

I'd change you query from

Code: Select all

$query= "SELECT *  FROM user_cart_records WHERE user_id = " . $user_row['user_id'] ;
to:

Code: Select all

$userId=$user_row['user_id']
$query= "SELECT *  FROM user_cart_records WHERE user_id = '$userId'" ;
Your loop should run on the variable that executed the query - $result
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

if i run it on $result the page will seemingly load forever .

until Error: Fatal error: Maximum execution time of 30 seconds exceeded

I tried it your way with this :

Code: Select all

$userId=$user_row['user_id']; 
$query= "SELECT *  FROM user_cart_records WHERE user_id = '$userId'" ;
$result=mysql_query ($query); // Run the query. 
$num=mysql_num_rows($result); 

while($result=mysql_query ($query)){ 
$totalSum+=number_format($total_cart, 2, '.', ''); 

}



so what do you think?
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

No, this is how you would write your loop:

Code: Select all

$userId=$user_row['user_id'];
$query= "SELECT *  FROM user_cart_records WHERE user_id = '$userId'" ;
$result=mysql_query ($query); // Run the query.
$num=mysql_num_rows($result);

while($row=mysql_fetch_array ($result))
{
$totalSum+=number_format($total_cart, 2, '.', '');
}
You need to fetch data from the database in order to do something with it.
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

this should work based off what you just posted.

Code: Select all

$userId=$user_row['user_id']; 
$query= "SELECT *  FROM user_cart_records WHERE user_id = '$userId'" ;

$result=mysql_query ($query); // Run the query. 
$num=mysql_num_rows($result); 


for ($i = 1; $i <= $num; $i++) {
    $totalSum+=number_format($total_cart, 2, '.', ''); 
    //I'm assuming youre setting $total_cart somewhere else?
}
are you actually doing anything with the data returned from that query?
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

Here's the basics of what I have.
Im actually going to have to do two while loops, because the first one is for setting up the $variables.

The it displays all the records in that format below , basically in seperate tables.
The i wanted to sum the total of all the records $total_cart.

with the two while loops , the second isnt working, so there's nothing on the variable for $totalSum

Code: Select all

$userId=$user_row['user_id']; 
$query= "SELECT *  FROM user_cart_records WHERE user_id = '$userId'" ; 
$result=mysql_query ($query); // Run the query. 
$num=mysql_num_rows($result); 

// Fetch variables to be echoed for page dispay.
while ($row=mysql_fetch_array($result, MYSQL_ASSOC)) {

$product_type_cart       =  $row['product_type']; 
$quantity_cart           =  $row['quantity']; 
$shipping_type_cart      =  $row['shipping_type']; 
$total_cart              =  $row['total']; 
$total_cart              =  number_format($total_cart,2); 
$date_cart               =  $row['date'];

}


while($row=mysql_fetch_array ($result)) 
{ 
$totalSum+=number_format($total_cart, 2, '.', ''); 
}
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

the first while loop exausted your result set... you need to combine those into one while loop.

Code: Select all

$userId=$user_row['user_id'];
$query= "SELECT *  FROM user_cart_records WHERE user_id = '$userId'" ;
$result=mysql_query ($query); // Run the query.
$num=mysql_num_rows($result);

// Fetch variables to be echoed for page dispay.
while ($row=mysql_fetch_array($result, MYSQL_ASSOC)) {

$product_type_cart       =  $row['product_type'];
$quantity_cart           =  $row['quantity'];
$shipping_type_cart      =  $row['shipping_type'];
$total_cart              =  $row['total'];
$total_cart              =  number_format($total_cart,2);
$date_cart               =  $row['date'];

$totalSum+=number_format($total_cart, 2, '.', '');

}
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

Something with the return of records happened now , because it wont display all of them now.

its only showing the last record in the database now.


i have to backtrack the way it was, and see how it changed real fast.
Last edited by jramaro on Thu Sep 20, 2007 10:49 am, edited 1 time in total.
Post Reply