Page 1 of 1

sql queries

Posted: Mon Feb 28, 2005 8:09 pm
by elle_girl

Code: Select all

$query1 = "SELECT * FROM cart WHERE cart.cookieId = '7e411b5ee9926542bea68dd079250c6e'";
			 $result1 = @mysql_query ($query);
			 $query2 = "UPDATE view SET itemId = їcolor=red]їb]' 'ї/b], ї/color]price їcolor=red]=їb] ' 'ї/b], ї/color]qty = їcolor=red]їb]' ' ї/b]ї/color]	WHERE  cart.cookieId = '7e411b5ee9926542bea68dd079250c6e' AND view.token_id = '$t' ";
			 $result2= @mysql_query;
I don't know how to define the variable that I bold.

This is the table that involve.

Code: Select all

create table cart 
( 
cartId int auto_increment not null, 
cookieId varchar(50), 
itemId int, 
qty int, 
price decimal(4,2),
primary key(cartId), 
unique id(cartId) 

); 


CREATE TABLE view (
view_id INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
token_id VARCHAR(10) NOT NULL,
account_no VARCHAR(50) NOT NULL,
name VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
postcode MEDIUMINT(5) NOT NULL,
state VARCHAR(10) NOT NULL,
country VARCHAR(10) NOT NULL,
itemId int, 
qty int, 
price decimal(4,2),
registration_date DATETIME NOT NULL,
PRIMARY KEY (token_id));
I actually want the data that I stored in the table cart to be insert into the table view. Please help me to solve this problem.

This is my complete coding that related to the sql queries.

Code: Select all

<?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. 
	$submit = true;

   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' AND account_no = '$aa' "; 
      $result = @mysql_query ($query); 

       
       
       
      if ($result) { 
             // Add the user. 
              $query = "INSERT INTO view (account_no, token_id, 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. 
			 $query1 = "SELECT * FROM cart WHERE cart.cookieId = '7e411b5ee9926542bea68dd079250c6e'";
			 $result1 = @mysql_query ($query);
			 $query2 = "UPDATE view SET itemId = ' ', price = ' ', qty = ' ' 	WHERE  cart.cookieId = '7e411b5ee9926542bea68dd079250c6e' AND view.token_id = '$t'";
			 $result2= @mysql_query;
			
          
          

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

          
      } 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_id'])) echo $_POSTї'token_id']; ?>" /></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'); 
?>
PHENOM | Please use code tags on all code while

Code: Select all

tags are down[/color]

Posted: Mon Feb 28, 2005 8:40 pm
by Jim_Bo
Hi,

I would be doing something like ..

When someone adds all the products they want into the cart and then proceed to the checkout ... Once they hit the submit button to make the final purchas ( so the products are purchased products .. not wannabe products ) .. Put in a sql query that will write the order into the "view" table ..

If you rely on cart.cookieId it wont give you true purchases as the cart.cookieId holds the order for like upto a week .. and my not hold data for product that they actually purchased .. And also makes what you want to acheive more work ..

If im on the right track of what you are trying to acheive ..

Posted: Mon Feb 28, 2005 9:14 pm
by elle_girl
Yes. You in the right track. I want when the customer click button confirm add every product that the user want to order to the view table. Can u help me the complete sql queries to do it?

Posted: Mon Feb 28, 2005 9:26 pm
by smpdawg
I don't mean to be rude, but you have been reminded before to use code tags when you post. It is also recommended that you use proper English when you post as well. It is just basic etiquette and is appreciated when you ask for help.

I don't mind helping anyone at all that is respectful and has a desire to learn and grow but don't ask us to do your work for you all the time. There are great resources on the net, this being one of them. Study up, ask questions and please use the proper etiquette in this forum.

Posted: Mon Feb 28, 2005 9:49 pm
by Jim_Bo
Hi smpdawg

Who are you referring to in your post above?

I see code tags used .. I also see the posts are in proper english ..

Maybe the fact that elle_girl could be asian or such with the way I read the posts from her ..

So I dont understand why you post what you did ...

Posted: Mon Feb 28, 2005 9:51 pm
by elle_girl

Code: Select all

$query = "INSERT INTO view (account_no, token_id, name, address, postcode, state, country, itemId, qty, price) VALUES ('$aa','$t','$n','$ad','$p','$s','$c' ) SELECT itemId, qty, price FROM cart WHERE cookieId = '7e411b5ee9926542bea68dd079250c6e'"; 
              $result = @mysql_query ($query); // Run the query.
I try to do the sql queries like this. Why it cannot appear to the database for itemId, qty and price. Please help me?

Posted: Mon Feb 28, 2005 9:54 pm
by feyd
you have an error in your sql syntax. Swapping in the following will help you understand what is wrong.

Code: Select all

$result = mysql_query($query) or die(mysql_error());

Posted: Mon Feb 28, 2005 11:32 pm
by elle_girl
Why my sql queries cannot stored the data the way I want(itemId, qty, price)?It appear in the database as NULL value , 0, 0.00.

Code: Select all

$itemId = "SELECT itemId FROM cart";
			 $price = "SELECT price FROM cart";
			 $qty = "SELECT qty FROM cart";
              $query = "INSERT INTO view (account_no, token_id, name, address, postcode, state, country, itemId, qty, price) VALUES ('$aa','$t','$n','$ad','$p','$s','$c', '$itemId', '$qty', '$price' )"; 
             $result = mysql_query($query) or die(mysql_error());// Run the query.

Posted: Mon Feb 28, 2005 11:39 pm
by smpdawg
Please take the time to read the help first then ask questions.

http://dev.mysql.com/doc/mysql/en/select.html

and this

http://us3.php.net/manual/en/ref.mysql.php

Failing that, there is no way on earth that you can assign a query to a variable and just use that variable inside another query. You need to execute the first query and feed the result into the second query.

Posted: Mon Feb 28, 2005 11:40 pm
by feyd
you must perform each query, then fetch the resultant data.. Your itemid, price, and qty "queries" would return all itemids, prices, qty's in the table cart.

In reality, you aren't performing those queries.. just trying to insert the syntax strings into your table.

Posted: Tue Mar 01, 2005 7:51 am
by elle_girl
I try to do the fetch like you say. But still appear the same problem.

Code: Select all

$itemId = "SELECT itemId FROM cart";
			 $result1 = @mysql_query($itemId) or die(mysql_error());
			 $price = "SELECT price FROM cart";
			 $result2 = @mysql_query ($price) or die(mysql_error());
			 $qty = "SELECT qty FROM cart";
			 $result3 = @mysql_query ($qty) or die(mysql_error());
			 
			 while ( ($id = mysql_fetch_array ($result1, MYSQL_NUM)) && ($pr = mysql_fetch_array ($result2, MYSQL_NUM)) && ($q = mysql_fetch_array ($result3, MYSQL_NUM))) {
             				$query1 = "INSERT INTO view (account_no, token_id, name, address, postcode, state, country, itemId, price, qty) VALUES ('$aa','$t','$n','$ad','$p','$s','$c','$id','$pr','$q' )"; 
            				$result4 = mysql_query($query1) or die(mysql_error());// Run the query.
Can u please help me to overcome this problem?

Posted: Thu Mar 03, 2005 2:36 am
by elle_girl
Is the problem encountered because the way I do initialization for itemId is wrong? Here is the code where the data I want to retrieve based on this page.

Code: Select all

<?php

	include("db.php");
		
	switch($_GET&#1111;"action"])
	&#123;
		case "add_item":
		&#123;
			AddItem($_GET&#1111;"id"], $_GET&#1111;"qty"]);
			ShowCart($_GET&#1111;"id"]);
			break;
		&#125;
		case "update_item":
		&#123;
			UpdateItem($_GET&#1111;"id"], $_GET&#1111;"qty"]);
			ShowCart($_GET&#1111;"id"]);
			break;
		&#125;
		case "remove_item":
		&#123;
			RemoveItem($_GET&#1111;"id"]);
			ShowCart($_GET&#1111;"id"]);
			break;
		&#125;
		default:
		&#123;
			ShowCart($_GET&#1111;"id"]);
		&#125;
	&#125;

	function AddItem($itemId, $qty)
	&#123;
		// Will check whether or not this item
		// already exists in the cart table.
		// If it does, the UpdateItem function
		// will be called instead
		
		global $dbServer, $dbUser, $dbPass, $dbName;

		// Get a connection to the database
		$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
		
		// Check if this item already exists in the users cart table
		$result = mysql_query("select count(*) from cart where cookieId = '" . GetCartId() . "' and itemId = $itemId ");
		$row = mysql_fetch_row($result);
		$numRows = $row&#1111;0];
		
		
		if($numRows == 0)
		&#123;
			// This item doesn't exist in the users cart,
			// we will add it with an insert query

			@mysql_query("insert into cart(cookieId, itemId, qty) values('" . GetCartId() . "', $itemId, $qty)");
		&#125;
		else
		&#123;
			// This item already exists in the users cart,
			// we will update it instead
			
			UpdateItem($itemId, $qty);
		&#125;
	&#125;
	
	function UpdateItem($itemId, $qty)
	&#123;
		// Updates the quantity of an item in the users cart.
		// If the qutnaity is zero, then RemoveItem will be
		// called instead

		global $dbServer, $dbUser, $dbPass, $dbName;

		// Get a connection to the database
		$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
		
		if($qty == 0)
		&#123;
			// Remove the item from the users cart
			RemoveItem($itemId);
		&#125;
		else
		&#123;
			mysql_query("update cart set qty = $qty where cookieId = '" . GetCartId() . "' and itemId = $itemId");
		&#125;
	&#125;
	
	function RemoveItem($itemId)
	&#123;
		// Uses an SQL delete statement to remove an item from
		// the users cart

		global $dbServer, $dbUser, $dbPass, $dbName;

		// Get a connection to the database
		$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
		
		mysql_query("delete from cart where cookieId = '" . GetCartId() . "' and itemId = $itemId");
	&#125;
	
	function ShowCart($itemId)
	&#123;
		// Gets each item from the cart table and display them in
		// a tabulated format, as well as a final total for the cart
		
		global $dbServer, $dbUser, $dbPass, $dbName;

		// Get a connection to the database
		$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
		
		$totalCost = 0;
		$result = mysql_query("select * from cart inner join items on cart.itemId = items.itemId where cart.cookieId = '" . GetCartId() . "' order by items.itemName asc");
		?>
		<html>
		<head>
		<title> Your Shopping Cart </title>
		<script language="JavaScript">
		
			function UpdateQty(item)
			&#123;
				itemId = item.name;
				newQty = item.options&#1111;item.selectedIndex].text;
				
				document.location.href = 'cart.php?action=update_item&id='+itemId+'&qty='+newQty;
			&#125;
		
		</script>
		</head>
		<body bgcolor="#ffffff">
		<h1>Your Shopping Cart</h1>
		<form name="frmCart" method="get">
		<table width="100%" cellspacing="0" cellpadding="0" border="0">
			<tr>
				<td width="15%" height="25" bgcolor="red">
					<font face="verdana" size="1" color="white">
						&nbsp;&nbsp;<b>Qty</b>
					</font>
				</td>
				<td width="55%" height="25" bgcolor="red">
					<font face="verdana" size="1" color="white">
						<b>Product</b>
					</font>
				</td>
				<td width="20%" height="25" bgcolor="red">
					<font face="verdana" size="1" color="white">
						<b>Price Each</b>
					</font>
				</td>
				<td width="10%" height="25" bgcolor="red">
					<font face="verdana" size="1" color="white">
						<b>Remove?</b>
					</font>
				</td>
			</tr>
			<?php
			
			while($row = mysql_fetch_array($result))
			&#123;
				// Increment the total cost of all items
				$totalCost += ($row&#1111;"qty"] * $row&#1111;"itemPrice"]);
				$totalPrice = ($row&#1111;"qty"] * $row&#1111;"itemPrice"]);
				mysql_query("update cart set price = $totalPrice where cookieId = '" . GetCartId() . "' and itemId = $itemId");
				

				
				?>
					<tr>
						<td width="15%" height="25">
							<font face="verdana" size="1" color="black">
								<select name="<?php echo $row&#1111;"itemId"]; ?>" onChange="UpdateQty(this)">
								<?php
								
									for($i = 1; $i <= 20; $i++)
									&#123;
										echo "<option ";
										if($row&#1111;"qty"] == $i)
										&#123;
											echo " SELECTED ";
										&#125;
										echo ">" . $i . "</option>";
									&#125;
								?>
								</select>
							</font>
						</td>
						<td width="55%" height="25">
							<font face="verdana" size="1" color="black">
								<?php echo $row&#1111;"itemName"]; ?>
							</font>
						</td>
						<td width="20%" height="25">
							<font face="verdana" size="1" color="black">
								$<?php echo number_format($row&#1111;"itemPrice"], 2, ".", ","); ?>
							</font>
						</td>
						<td width="10%" height="25">
							<font face="verdana" size="1" color="black">
								<a href="cart.php?action=remove_item&id=<?php echo $row&#1111;"itemId"]; ?>">Remove</a>
							</font>
						</td>
					</tr>
				<?php
			&#125;
			
			// Display the total
			?>
					<tr>
						<td width="100%" colspan="4">
							<hr size="1" color="red" NOSHADE>
						</td>
					</tr>
					<tr>
						<td width="70%" colspan="2">
							<font face="verdana" size="1" color="black">
								<a href="products.php"><< Keep Shopping</a>
							</font>
						</td>
						<td width="70%" colspan="2">
							<font face="verdana" size="1" color="black">
								<a href="checkout.php"><< Continue Shopping</a>
							</font>
						</td>
						<td width="30%" colspan="2">
							<font face="verdana" size="2" color="black">
								<b>Total: $<?php echo number_format($totalCost, 2, ".", ","); ?></b>
							</font>
						</td>
					</tr>
				</table>
				</form>
			</body>
			</html>
			<?php
	&#125;

?>