Page 1 of 2
how to make more than one variable out of multiple SQL info
Posted: Wed Sep 19, 2007 1:10 pm
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?
Thank You !
Posted: Thu Sep 20, 2007 3:59 am
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.
Posted: Thu Sep 20, 2007 8:40 am
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
Posted: Thu Sep 20, 2007 8:51 am
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.
Posted: Thu Sep 20, 2007 9:04 am
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?
Posted: Thu Sep 20, 2007 9:20 am
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;
}
Posted: Thu Sep 20, 2007 9:34 am
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!
Posted: Thu Sep 20, 2007 9:41 am
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.
Posted: Thu Sep 20, 2007 9:43 am
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
Posted: Thu Sep 20, 2007 10:03 am
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?
Posted: Thu Sep 20, 2007 10:09 am
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.
Posted: Thu Sep 20, 2007 10:11 am
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?
Posted: Thu Sep 20, 2007 10:29 am
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, '.', '');
}
Posted: Thu Sep 20, 2007 10:32 am
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, '.', '');
}
Posted: Thu Sep 20, 2007 10:39 am
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.