problem in sql statement

[elle_girl]

// Retrieve all of the information for the prints in the cart.
$query = 'SELECT * FROM category, product WHERE category.category_id = product.category_id AND product.product_id IN (';
foreach ($_SESSION['cart'] as $key => $value) {
$query .= $key . ',';
}

In this sql statement it want to display data. How If I want the display data to be insert into another table view.Please help me about the sql statement.

[laserlight]

hmm... basically you want to transfer data from one table to another, while displaying this data to the user?

[elle_girl]

I does not want to display the data to the user. I just want to insert the data into the other table. That sql statement actually for my view cart. This is because I add new product to the cart by using session. If the user already confirm what to purchase that product I want it (the product attribute)to be insert into the table view. So how to do the sql statement. I have no idea to do it?

[RossC0]

So you need to insert the data (stored in the session) to a table ?

[elle_girl]

Yes. Then how to do it? Here I attach the script that deal with the session

1. add_cart.php
   <?php
   // This page adds prints to the shopping cart.
   session_start();

if (is_numeric ($_GET['pid'])) {
// Check for a print ID.

// Set the page title and include the HTML header.
$page_title = 'Add to Cart';
include_once ('includes/header_customer_two.html');

// Check if the cart already contains one of these prints.
if (isset ($_SESSION['cart'][$pid])) {
	$qty = $_SESSION['cart'][$pid] + 1;
} else {
	$qty = 1;
}

// Add to the cart session variable.
$_SESSION['cart'][$pid] = $qty;

// Display a message.
echo '<p>The print has been added to your shopping cart.</p>';

} else { // Redirect
header ("Location: [url]http://[/url]" . $SERVER['HTTP_HOST'] . dirname($SERVER['PHP_SELF']) . "/index.php");
exit();

}

?>
<PRE><?php
print_r($_SESSION);
?></PRE>

2. view_cart.php

<?php
// This page displays the contents of the shopping cart.
session_start();
// Set the page title and include the HTML header.
$page_title = 'View Your Shopping Cart';
include_once ('includes/header_customer_two.html');

// Check if the form has been submitted (to update the cart)
if (isset ($_POST['submit'])) {

foreach ($_POST['qty'] as $key => $value) {
	if (($value == 0) AND (is_numeric ($value))) {
		unset ($_SESSION['cart'][$key]);
	} elseif ( is_numeric ($value) AND ($value > 0) ) {
		$_SESSION['cart'][$key] = $value;
	}
}

}

// Check if the shopping cart is empty.
$empty = TRUE;
if (isset ($SESSION['cart'])) {
foreach ($SESSION['cart'] as $key => $value) {
if (isset($value)) {
$empty = FALSE;
}
}
}

// Display the cart if it's not empty.
if (!$empty) {

require_once ('./mysql_connect.php'); // Connect to the database.

// Retrieve all of the information for the prints in the cart.
$query = 'SELECT * FROM category, product WHERE category.category_id = product.category_id AND product.product_id IN (';
foreach ($_SESSION['cart'] as $key => $value) {
	$query .= $key . ',';
}
$query = substr ($query, 0, -1) . ') ORDER BY category.last_category ASC';
$result = mysql_query ($query);

// Create a table and a form.
echo '<table border="0" width="90%" cellspacing="3" cellpadding="3" align="center">
<tr>
	<td align="left" width="30%"><b>Author</b></td>
	<td align="left" width="30%"><b>Book Title</b></td>
	<td align="right" width="10%"><b>Price</b></td>
	<td align="center" width="10%"><b>Qty</b></td>
	<td align="right" width="10%"><b>Total Price</b></td>
</tr>

<form action="view_cart.php" method="post">
';

// Print each item.
$total = 0; // Total cost of the order.
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

	// Calculate the total and subtotals.
	$subtotal = $_SESSION['cart'][$row['product_id']] * $row['price'];
	$total += $subtotal;

	// Print the row.
	echo " <tr>
	<td align=\"left\">{$row['first_category']} {$row['middle_category']} {$row['last_category']}</td>
	<td align=\"left\">{$row['product_name']}</td>
	<td align=\"right\">\${$row['price']}</td>
	<td align=\"center\"><input type=\"text\" size=\"3\" name=\"qty[{$row['product_id']}]\" value=\"{$_SESSION['cart'][$row['product_id']]}\" /></td>
	<td align=\"right\">$" . number_format ($subtotal, 2). "</td>
</tr>\n";
} // End of the WHILE loop.

// Print the footer and close the table and the form.
echo ' <tr>
	<td colspan="4" align="right"><b>Total : <b></td>
	<td align="right">$' . number_format ($total, 2) . '</td>
</tr>
</table><div align="center"><input type="submit" name="submit" value="Update My Cart" /></form><br /><br /><a href="checkout.php"><font size="+3">Continue To Purchase</font></a></div>';

mysql_close(); // Close the database connection.

} else {
echo '<p>Your cart is currently empty.</p>';
}

?>
<PRE><?php
print_r($_SESSION);
?></PRE>

Can u tell me the sql statement to do the inserting the product(from the session)?

[elle_girl]

This is my script for checkout.php

<?php
// This is the registration page for the site.

// Set the page title and include the HTML header.
$page_title = 'Confirmation';

if (isset($_POST['submit'])) { // Handle the form.

// Register the user in the database.
require_once ('./mysql_connect.php'); // Connect to the db.

// Create a function for escaping the data.
function escape_data ($data) {
	global $dbc; // Need the connection.
	if (ini_get('magic_quotes_gpc')) {
		$data = stripslashes($data);
	}
	return mysql_real_escape_string($data, $dbc);
} // End of function.

$message = NULL; // Create an empty new variable.



// Check for the account number.
if (empty($_POST['account_no'])) {
	$aa = FALSE;
	$message .= '<p>You forgot to enter your account number!</p>';
} else {
	$aa = escape_data($_POST['account_no']);
}

if (empty($_POST['token'])) {
	$t = FALSE;
	$message .= '<p>You forgot to enter your reference ID!</p>';
} else {
	$t = escape_data($_POST['token']);
}

if (empty($_POST['name'])) {
	$n = FALSE;
} else {
	$n = escape_data($_POST['name']);
}

if (empty($_POST['address'])) {
	$ad = FALSE;

} else {
	$ad = escape_data($_POST['address']);
}

if (empty($_POST['postcode'])) {
	$p = FALSE;

} else {
	$p = escape_data($_POST['postcode']);
}

if (empty($_POST['state'])) {
	$s = FALSE;

} else {
	$s = escape_data($_POST['state']);
}

if (empty($_POST['country'])) {
	$c = FALSE;

} else {
	$c = escape_data($_POST['country']);
}




if ( $aa && $t ) { // If everything's OK.


	// Make sure the token available.
	$query = "SELECT * FROM customer_bank WHERE token_id = '$t' ";
	$result = @mysql_query ($query);

	if (mysql_num_rows($result) == 0) { // Available.

		// Add the user.
		$query = "INSERT INTO view (account_no, token, name, address, postcode, state, country) VALUES ( '$aa','$t','$n','$ad','$p','$s','$c' )";
		$result = @mysql_query ($query); // Run the query.
		// Retrieve all of the information for the prints in the cart.
		$variable = $_SESSION['cart'];
		$query1 = "INSERT INTO view (product_id) VALUES ('$variable')";
		$result1 = mysql_query ($query1); 
		if ($result && $result1) { // If it ran OK.

			// Successful add the new customer.
			echo '<h3>Successful the transaction</h3>';
			include ('includes/header_bank.html');
			exit();

		} else { // If it did not run OK.
			// Send a message to the error log, if desired.
			echo '<p><font color="red" size="+1">Cannot complete the transaction due to a system error. We apologize for any inconvenience.</font></p>';
		}

	} else { // The account number already exist.
		echo '<p><font color="red" size="+1">The account number already in the database</font></p>';
	}




	mysql_close(); // Close the database connection.

} else { // If it did not run OK.
	$message = '<p>Please try again.</p>';
}

} // End of the main Submit conditional.

// Print the error message if there is one.
if (isset($message)) {
echo '<font color="red">', $message, '</font>';
}
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>"method="post">
<fieldset><legend>Enter the reference id that you get request from the server with the account number that register with this website:</legend>

<p><b>Account number:</b> <input type="text" name="account_no" size="50" maxlength="50" value="<?php if (isset($POST['account_no'])) echo $POST['account_no']; ?>" /></p>

<p><b>Reference ID:</b> <input type="text" name="token" size="20" maxlength="20" value="<?php if (isset($POST['token'])) echo $POST['token']; ?>" /></p>

</fieldset>

<fieldset><legend>Enter the billing information correctly so that the product can delivered</legend>

<p><b>Recipient Name:</b> <input type="text" name="name" size="20" maxlength="20" value="<?php if (isset($POST['name'])) echo $POST['name']; ?>" /></p>

<p><b>Address:</b> <input type="text" name="address" size="50" maxlength="50" value="<?php if (isset($POST['address'])) echo $POST['address']; ?>" /></p>

<p><b>Postcode:</b> <input type="text" name="postcode" size="5" maxlength="5" value="<?php if (isset($POST['postcode'])) echo $POST['postcode']; ?>" /></p>

<p><b>State:</b> <input type="text" name="state" size="10" maxlength="10" value="<?php if (isset($POST['state'])) echo $POST['state']; ?>" /></p>

<p><b>Country:</b> <input type="text" name="country" size="10" maxlength="10" value="<?php if (isset($POST['country'])) echo $POST['country']; ?>" /></p>

</fielset>
<div align="center"><input type="submit" name="submit" value="Confirmation" /></div>

</form><!-- End of Form -->

<?php
include ('includes/footer_home.html');
?>

Why when I want to execute it appear this error

Notice: Undefined variable: _SESSION in C:\Program Files\Apache Group\Apache2\htdocs\checkout.php on line 91

I want the data that I stored based on the view_cart.php to be insert into table view?

[RossC0]

OK - first of all the error is caused because you haven't started the session in the checkout.php (Try putting: "session_start();" at the top of the script.)

To find out what is in your session do this:

print_r($_SESSION);

To do a sql insert the syntax is:

INSERT INTO table (a,b,c) VALUES (1,2,3);

But you have a insert from the session:

// Retrieve all of the information for the prints in the cart.
$variable = $_SESSION['cart'];
$query1 = "INSERT INTO view (product_id) VALUES ('$variable')";
$result1 = mysql_query ($query1);

So the problem is most likely the session hasn't been reinitialised for the script.

Try putting: "session_start();" at the top of the script. First and work from there.

[elle_girl]

It does not appear the failure. But it show the system failure. Because I want the system to insert data into the database and then go to the next page. But it appear this statement

Cannot complete the transaction due to a system error. We apologize for any inconvenience.

When I print the session, it appear like this

Array
(
[cart] => Array
(
[pid] => 1
[1] => 1
)

)

Please help me how to solve this problem!

[RossC0]

[Edited]

Cannot complete the transaction due to a system error. We apologize for any inconvenience.

Is caused by the insert queries failing - this is probably due to either:

1) the sql statement being incorrect
2) violation of table keys.

[elle_girl]

I make a little modify to this code

<?php
// This is the registration page for the site.
session_start();
// Set the page title and include the HTML header.
$page_title = 'Confirmation';


if (isset($_POST['submit'])) { // Handle the form.

// Register the user in the database.
require_once ('./mysql_connect.php'); // Connect to the db.

// Create a function for escaping the data.
function escape_data ($data) {
	global $dbc; // Need the connection.
	if (ini_get('magic_quotes_gpc')) {
		$data = stripslashes($data);
	}
	return mysql_real_escape_string($data, $dbc);
} // End of function.

$message = NULL; // Create an empty new variable.



// Check for the account number.
if (empty($_POST['account_no'])) {
	$aa = FALSE;
	$message .= '<p>You forgot to enter your account number!</p>';
} else {
	$aa = escape_data($_POST['account_no']);
}

if (empty($_POST['token'])) {
	$t = FALSE;
	$message .= '<p>You forgot to enter your reference ID!</p>';
} else {
	$t = escape_data($_POST['token']);
}

if (empty($_POST['name'])) {
	$n = FALSE;
} else {
	$n = escape_data($_POST['name']);
}

if (empty($_POST['address'])) {
	$ad = FALSE;

} else {
	$ad = escape_data($_POST['address']);
}

if (empty($_POST['postcode'])) {
	$p = FALSE;

} else {
	$p = escape_data($_POST['postcode']);
}

if (empty($_POST['state'])) {
	$s = FALSE;

} else {
	$s = escape_data($_POST['state']);
}

if (empty($_POST['country'])) {
	$c = FALSE;

} else {
	$c = escape_data($_POST['country']);
}




if ( $aa && $t ) { // If everything's OK.


	// Make sure the token available.
	$query = "SELECT * FROM customer_bank WHERE token_id = '$t' ";
	$result = @mysql_query ($query);

	if (!$result = mysql_query ($query)) { 
			// Add the user. 
			 $query = "INSERT INTO view (account_no, token, name, address, postcode, state, country) VALUES ( '$aa','$t','$n','$ad','$p','$s','$c' )"; 
 			$result = @mysql_query ($query); // Run the query. 
		 // Retrieve all of the information for the prints in the cart. 


		$variable = $_SESSION['cart']; 
		$query1 = "INSERT INTO view (product_id) VALUES ('$variable')"; 
		$result1 = mysql_query ($query1); 
		if ($result1) { // If it ran OK.

			// Successful add the new customer.
			echo '<h3>Successful the transaction</h3>';
			include ('includes/header_bank.html');
			exit();

		} else { // If it did not run OK.
			// Send a message to the error log, if desired.
			echo '<p><font color="red" size="+1">Cannot complete the transaction due to a system error. We apologize for any inconvenience.</font></p>';
		}

	} else { // The account number does not match with token ID.
		echo '<p><font color="red" size="+1">The account number does not match with the reference ID in the database</font></p>';
	}




	mysql_close(); // Close the database connection.

} else { // If it did not run OK.
	$message = '<p>Please try again.</p>';
}

} // End of the main Submit conditional.

// Print the error message if there is one.
if (isset($message)) {
	echo '<font color="red">', $message, '</font>';
}
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>"method="post">
<fieldset><legend>Enter the reference id that you get request from the server with the account number that register with this website:</legend>

<p><b>Account number:</b> <input type="text" name="account_no" size="50" maxlength="50" value="<?php if (isset($_POST['account_no'])) echo $_POST['account_no']; ?>" /></p>

<p><b>Reference ID:</b> <input type="text" name="token" size="20" maxlength="20" value="<?php if (isset($_POST['token'])) echo $_POST['token']; ?>" /></p>

</fieldset>

<fieldset><legend>Enter the billing information correctly so that the product can delivered</legend>

<p><b>Recipient Name:</b> <input type="text" name="name" size="20" maxlength="20" value="<?php if (isset($_POST['name'])) echo $_POST['name']; ?>" /></p>

<p><b>Address:</b> <input type="text" name="address" size="50" maxlength="50" value="<?php if (isset($_POST['address'])) echo $_POST['address']; ?>" /></p>

<p><b>Postcode:</b> <input type="text" name="postcode" size="5" maxlength="5" value="<?php if (isset($_POST['postcode'])) echo $_POST['postcode']; ?>" /></p>

<p><b>State:</b> <input type="text" name="state" size="10" maxlength="10" value="<?php if (isset($_POST['state'])) echo $_POST['state']; ?>" /></p>	

<p><b>Country:</b> <input type="text" name="country" size="10" maxlength="10" value="<?php if (isset($_POST['country'])) echo $_POST['country']; ?>" /></p>

</fielset>
<div align="center"><input type="submit" name="submit" value="Confirmation" /></div>

</form><!-- End of Form -->

<?php
include ('includes/footer_home.html');
?>
<PRE><?php 
print_r($_SESSION); 
?></PRE>	

This time it appear

The account number does not match with the reference ID in the database

can u please help me to solve this problem?

[RossC0]

Well looking at your code its because this statement fails:

// Make sure the token available.
        $query = "SELECT * FROM customer_bank WHERE token_id = '$t' ";
        $result = @mysql_query ($query); 

Take a look at the query (output it to page) to see what is going wrong - try running it through your sql database direct.