do i need a new table?

[bhavin_85]

hey guys

 

im having some trouble with a points system ive created

 

I need to get a total number of points for a customer, each customer can have an infinate number of invocies, i also need to store each addition and detraction from the points

 

right now i have 4 tables, customer (all of their details), invoice(stores an invoice id, customer id and the date) items (stores the item types) invoice_items (this stores the details about each item purchased, the price, descripion and the points they are worth)

 

I need to create a new table, i think, that stores the points details for each customer and gives a running total  ???

 

any1 have any ideas on the best way to do it? i cant seem 2 get my head around it

 

 

[karthikeyan_coder]

just ALTER the customer table... Add a column named `points` type (TEXT) do casting while doing arithmetic operations....

 

[bhavin_85]

that wont do quite what i need it to do though, i need to be able to keep a log of all addition and redemption of points

 

<?
session_start();
if ( empty($_SESSION['username'])){
header("location:default.php");
exit;
}

$id=$_SESSION['cust_id'];

include('config.php');

$sql = "SELECT invoice_id FROM invoices WHERE cust_id='$id'";
$query = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($query)) {
    $invoice_id = $row['invoice_id'];
echo $row['invoice_id'];

$sql1 = "SELECT points FROM invoice_items WHERE invoices_invoice_id='$invoice_id'";
$query = mysql_query($sql1) or die(mysql_error());
if ($row = mysql_fetch_assoc($query)) { 
    echo $row[1];
}
}
?>

ive written that code so far, the first query works fine, it returns 2 results (only 2 invoice_id in the database with that cust_id) but once i put the second query into the while loop it doesnt work. I only prints the first invoice_id and not the points...i need it to loop though and print all the invoice_id's with their points

 

any ideas?