Completing a transaction - adding data to one table and deleting from another

[murtz]

Hi all!

 

Im currently creating an online bookshop. At the moment I've got some of the main functions working. Im having some problem with the part where the user 'buys' the book.

 

Below is a screenshot of how a user buys a book..

 

 

I've got a table called BOOK and a table called SALES. The details in the screeny above come from the BOOK table.

 

At the moment.. when a user clicks BUY BOOK.. the details of he book along with the logged in user id are inserted into the SALES table. But I also need the book that is being bought to be deleted from the BOOK table after it has been inserted into SALES.

 

Im using the code below at the moment to insert into the sales table (im using hidden fields on the screen to display id's etc)

 

$buyer=$_SESSION['session_name'];
$book_id=mysql_real_escape_string(trim($_POST['book_id']));
$title=mysql_real_escape_string(trim($_POST['title']));
$author=mysql_real_escape_string(trim($_POST['author']));
$ISBN=mysql_real_escape_string(trim($_POST['ISBN']));
$seller=mysql_real_escape_string(trim($_POST['seller_id']));
$price=mysql_real_escape_string(trim($_POST['price']));


$query = "INSERT INTO sales(book_title, book_author, ISBN, price, buyer_id, seller_id, sale_date)
VALUES ('$title', '$author', '$ISBN', '$price', '$buyer', '$seller', NOW())";

mysql_query($query)
or die ('Purchase was Unsuccessful, Please go back and try again');

// Check if the form has been submitted.
if (mysql_affected_rows() ==1) 
header("location:purchasecomplete.php")
?>

 

Please help!!

[Wolphie]

error_reporting(E_ALL);

// $uid = $_SESSION['uid']; 
// $bookId = $_POST['book_id'];

<?php
if(isset($uid)) {
  $ins = "INSERT INTO `db_name` . `table_name` ( `col_1`, `col_2` ) VALUES ( 'val_1', 'val_2' )";
  $res = mysql_query($ins) or die('<strong>Error: </strong>' . mysql_error());
  if($res) {
    $del = "DELETE FROM `db_name` . `table_name` WHERE `book_id` = '/* $bookId */' LIMIT 1";
    $res = mysql_query($del) or die('<strong>Error: </strong>' . mysql_error());

    if($res) print 'Record Deleted!';
  }
}
?>

[murtz]

Hi wolfie thanks for the quick reply!.. could you please talk me through your code because im a bit confused 

[Wolphie]

Sure, no problem.

 

The first line simply turns error reporting on to report all errors, so that debugging is easier (it makes life simpler).

I'm not too sure about your variables, and what you use to retrieve the user id, and book id so that's why they're commented out.

 

The isset() function checks to see if a variable has been set, in this case if the user id exists, if TRUE we then proceed to construct the MySQL query. Which is your basic MySQL structure, field names, and values etc..

 

We then execute the query, and if it fails we print an error (also makes life easier) regarding what has failed in the query. Now, if no error was present, we then move onto deleting the record from the database. The DELETE statement is much like the SELECT statement, except you're deleting the record.

 

$bookId was quoted out (/* $bookId */) because once again, I do not know your variables in which you use to retrieve the data. The reason I have used "LIMIT 1" at the end of the query string is so that the first and only occurrence is deleted. And again, we execute the query, and print an error if it fails.

 

If it doesn't fail, we simply alert the user that the record has been deleted. You don't have to alert the user, and the statement can be conditional if you wished.

 

e.g.

 

if($res) {
  // do something
}
else {
  // do something else
}

 

I hope everything goes well

 

 

EDIT:

 

<?php
error_reporting(E_ALL);

// $uid = $_SESSION['uid']; 
// $bookId = $_POST['book_id'];

if(isset($uid)) {
  $ins = "INSERT INTO `db_name` . `table_name` ( `col_1`, `col_2` ) VALUES ( 'val_1', 'val_2' )";
  $res = mysql_query($ins) or die('<strong>Error: </strong>' . mysql_error());
  if($res) {
    $del = "DELETE FROM `db_name` . `table_name` WHERE `book_id` = '/* $bookId */' LIMIT 1";
    $res = mysql_query($del) or die('<strong>Error: </strong>' . mysql_error());

    if($res) print 'Record Deleted!';
  }
}
?>

[murtz]

Wolfie! You are a star mate. Thanks alot!

 

A slightly off topic question.. how would I build a query to get results of transactions that are completed in a 3 day space?.. Basically what I need to do is.. on the home page.. give 'alerts' to users who have sold their books.

 

If someone bought a book from them today.. then on the home page.. the user would see something like 'NEW PURCHASE'. I want the query to check in the sales table for transactions for the logged in buyer that happened during the last 3 days.

 

Hope all that makes sense! Would the syntax WHERE sale_date = NOW()-3 would work?

 

Im taking the easy way out here.. the most ideal way for me to do an alert system would be that as soon as the user logs on.. he gets a message or pop up message box saying someone bought his book!

 

Thanks once again for the delete stuff!

[murtz]

Anyone??