Hello, A few days ago I searched on the web for a tutorial to create a shopping cart and I found one that's great for what i needed. Now, that shopping cart was getting the information of the products from 1 table, but I have created many tables in order to have the different kind of products organized. The problem is that the cart is only getting the info of the products from 1 table only and as I have many tables when I try to add an item from a table that the cart is not reading It gives me some database errors ( obviously ). This is the file where the cart gets the info or the products I add: functions.inc.php <?php function writeShoppingCart() { $cart = $_SESSION['cart']; if (!$cart) { return '<p>You have no items in your shopping cart</p>'; } else { // Parse the cart session variable $items = explode(',',$cart); $s = (count($items) > 1) ? 's':''; return '<p>You have <a href="cart.php">'.count($items).' item'.$s.' in your shopping cart</a></p>'; } } function showCart() { global $db; $cart = $_SESSION['cart']; if ($cart) { $items = explode(',',$cart); $contents = array(); foreach ($items as $item) { $contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1; } $output[] = '<form action="cart.php?action=update" method="post" id="cart">'; $output[] = '<table cellpadding="0" cellspacing="0">'; foreach ($contents as $id=>$qty) { $sql = 'SELECT * FROM setsbk WHERE id = '.$id; $result = $db->query($sql); $row = $result->fetch(); extract($row); $output[] = '<tr>'; $output[] = '<td align="center">'.$name.'</td>'; $output[] = '<td align="center">$'.$price.'</td>'; $output[] = '<td align="center"><input type="text" name="qty'.$id.'" value="'.$qty.'" size="3" maxlength="3" /></td>'; $output[] = '<td align="center">$'.($price * $qty).'</td>'; $output[] = '<td align="center"><a href="cart.php?action=delete&id='.$id.'" class="r">X</a></td>'; $total += $price * $qty; $output[] = '</tr>'; } $output[] = '</table>'; $output[] = '<p>Grand total: <strong>$'.$total.'</strong></p>'; $output[] = '<div><button type="submit">Update cart</button></div>'; $output[] = '</form>'; } else { $output[] = '<p>You shopping cart is empty.</p>'; } return join('',$output); } ?> PHP: As you may see at the line 26 there's the sql function where it gets the information from the table called "setsbk" $sql = 'SELECT * FROM setsbk WHERE id = '.$id; PHP: I tried adding the other tables there, like this: $sql = 'SELECT * FROM setsbk, setssm, setsdl, setsmg, setself, setssum WHERE id = '.$id; PHP: But it gives me the following error: Notice: Query failed: Column 'id' in where clause is ambiguous SQL: SELECT * FROM setsbk, setssm WHERE id = 1 in C:\xampp\htdocs\cart-demo\inc\mysql.class.php on line 109 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\cart-demo\inc\mysql.class.php on line 151 Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\cart-demo\inc\mysql.class.php on line 167 Warning: extract() expects parameter 1 to be array, boolean given in C:\xampp\htdocs\cart-demo\inc\functions.inc.php on line 29 PHP: Here's the C:\xampp\htdocs\cart-demo\inc\mysql.class.php file: <?php /** * MySQL Database Connection Class * @access public * @package SPLIB */ class MySQL { /** * MySQL server hostname * @access private * @var string */ var $host; /** * MySQL username * @access private * @var string */ var $dbUser; /** * MySQL user's password * @access private * @var string */ var $dbPass; /** * Name of database to use * @access private * @var string */ var $dbName; /** * MySQL Resource link identifier stored here * @access private * @var string */ var $dbConn; /** * Stores error messages for connection errors * @access private * @var string */ var $connectError; /** * MySQL constructor * @param string host (MySQL server hostname) * @param string dbUser (MySQL User Name) * @param string dbPass (MySQL User Password) * @param string dbName (Database to select) * @access public */ function MySQL ($host,$dbUser,$dbPass,$dbName) { $this->host=$host; $this->dbUser=$dbUser; $this->dbPass=$dbPass; $this->dbName=$dbName; $this->connectToDb(); } /** * Establishes connection to MySQL and selects a database * @return void * @access private */ function connectToDb () { // Make connection to MySQL server if (!$this->dbConn = @mysql_connect($this->host, $this->dbUser, $this->dbPass)) { trigger_error('Could not connect to server'); $this->connectError=true; // Select database } else if ( !@mysql_select_db($this->dbName,$this->dbConn) ) { trigger_error('Could not select database'); $this->connectError=true; } } /** * Checks for MySQL errors * @return boolean * @access public */ function isError () { if ( $this->connectError ) return true; $error=mysql_error ($this->dbConn); if ( empty ($error) ) return false; else return true; } /** * Returns an instance of MySQLResult to fetch rows with * @param $sql string the database query to run * @return MySQLResult * @access public */ function query($sql) { if (!$queryResource=mysql_query($sql,$this->dbConn)) trigger_error ('Query failed: '.mysql_error($this->dbConn). ' SQL: '.$sql); return new MySQLResult($this,$queryResource); } } /** * MySQLResult Data Fetching Class * @access public * @package SPLIB */ class MySQLResult { /** * Instance of MySQL providing database connection * @access private * @var MySQL */ var $mysql; /** * Query resource * @access private * @var resource */ var $query; /** * MySQLResult constructor * @param object mysql (instance of MySQL class) * @param resource query (MySQL query resource) * @access public */ function MySQLResult(& $mysql,$query) { $this->mysql=& $mysql; $this->query=$query; } /** * Fetches a row from the result * @return array * @access public */ function fetch () { if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) ) { return $row; } else if ( $this->size() > 0 ) { mysql_data_seek($this->query,0); return false; } else { return false; } } /** * Returns the number of rows selected * @return int * @access public */ function size () { return mysql_num_rows($this->query); } /** * Returns the ID of the last row inserted * @return int * @access public */ function insertID () { return mysql_insert_id($this->mysql->dbConn); } /** * Checks for MySQL errors * @return boolean * @access public */ function isError () { return $this->mysql->isError(); } } ?> PHP: The Here's the C:\xampp\htdocs\cart-demo\index.php file: <?php // Include MySQL class require_once('inc/mysql.class.php'); // Include database connection require_once('inc/global.inc.php'); // Include functions require_once('inc/functions.inc.php'); // Start the session session_start(); ?> <?php include 'header.php'; ?> <BR><BR> <div align="center"> <img src="images/titles/sets.png"> </div> <BR> <table class="sub-menu" width="75%" cellpadding="" cellspacing="0" align="center"> <tr align="center"> <td><a class="submenu" href="#bk">Blade Knight</a></td> <td><a class="submenu" href="#sm">Soul Master</a></td> <td><a class="submenu" href="#ms">Muse Elf</a></td> <td><a class="submenu" href="#mg">Magic Gladiator</a></td> <td><a class="submenu" href="#dl">Dark Lord</a></td> <td><a class="submenu" href="#sum">Summoner</a></td> </tr> </table> <BR><BR><BR> <table width="500" align="center" cellpadding="5" cellspacing="3"> <tr align="center"><td> Your Shopping Cart </td></tr> <tr align="center"><td> <?php echo writeShoppingCart(); ?> </td></tr> </table> <?php $sql = 'SELECT * FROM setsbk ORDER BY id'; $result = $db->query($sql); $output[] = '<table width="100%" cellpadding="10" cellspacing="0" align="center"> <tr width="644" align="center">'; $column_ctr = 0; while ($row = $result->fetch()) { $column_ctr++; if($column_ctr % 4 == 1) { $output[] = '</tr>'; $output[] = '<tr width="100%" align="center">'; } $output[] = ' <td><table class="item"> <tr><td class="itemname">'.$row['name'].'</td></tr> <tr><td class="imgtable"> <img class="testimage" width="161" height="204" src="'.$row['image'].'"> </td></tr> <tr><td class="itemprice"> <table width="100%" align="center" cellpadding="0" cellspacing="0"> <tr align="center"> <td width="50%">$'.$row['price'].' USD</td> <td width="50%"><a href="cart.php?action=add&id='.$row['id'].'">ADD TO CART</a></td> </tr> </table> </td></tr> </table></td> '; } $output[] = '</tr> </table>'; echo join('',$output); ?> <BR><BR><BR> <table class="hrtable" cellpadding="0" cellspacing="0" align="center"> <tr><td> <BR><BR><BR> </td></tr> </table> <?php $sql2 = 'SELECT * FROM setssm ORDER BY id'; $result2 = $db->query($sql2); $output2[] = '<table width="100%" cellpadding="10" cellspacing="0" align="center"> <tr width="644" align="center">'; $column2_ctr = 0; while ($row = $result2->fetch()) { $column2_ctr++; if($column2_ctr % 4 == 1) { $output2[] = '</tr>'; $output2[] = '<tr width="100%" align="center">'; } $output2[] = ' <td><table class="item"> <tr><td class="itemname">'.$row['name'].'</td></tr> <tr><td class="imgtable"> <img class="testimage" width="161" height="204" src="'.$row['image'].'"> </td></tr> <tr><td class="itemprice"> <table width="100%" align="center" cellpadding="0" cellspacing="0"> <tr align="center"> <td width="50%">$'.$row['price'].' USD</td> <td width="50%"><a href="cart.php?action=add&id='.$row['id'].'">ADD TO CART</a></td> </tr> </table> </td></tr> </table></td> '; } $output2[] = '</tr> </table>'; echo join('',$output2); ?> <BR><BR><BR> <table class="hrtable" cellpadding="0" cellspacing="0" align="center"> <tr><td> <BR><BR><BR> </td></tr> </table> <?php $sql3 = 'SELECT * FROM setself ORDER BY id'; $result3 = $db->query($sql3); $output3[] = '<table width="100%" cellpadding="10" cellspacing="0" align="center"> <tr width="644" align="center">'; $column3_ctr = 0; while ($row = $result3->fetch()) { $column3_ctr++; if($column3_ctr % 4 == 1) { $output3[] = '</tr>'; $output3[] = '<tr width="100%" align="center">'; } $output3[] = ' <td><table class="item"> <tr><td class="itemname">'.$row['name'].'</td></tr> <tr><td class="imgtable"> <img class="testimage" width="161" height="204" src="'.$row['image'].'"> </td></tr> <tr><td class="itemprice"> <table width="100%" align="center" cellpadding="0" cellspacing="0"> <tr align="center"> <td width="50%">$'.$row['price'].' USD</td> <td width="50%"><a href="cart.php?action=add&id='.$row['id'].'">ADD TO CART</a></td> </tr> </table> </td></tr> </table></td> '; } $output3[] = '</tr> </table>'; echo join('',$output3); ?> <BR><BR><BR> <table class="hrtable" cellpadding="0" cellspacing="0" align="center"> <tr><td> <BR><BR><BR> </td></tr> </table> <?php $sql4 = 'SELECT * FROM setsmg ORDER BY id'; $result4 = $db->query($sql4); $output4[] = '<table width="100%" cellpadding="10" cellspacing="0" align="center"> <tr width="644" align="center">'; $column4_ctr = 0; while ($row = $result4->fetch()) { $column4_ctr++; if($column4_ctr % 4 == 1) { $output4[] = '</tr>'; $output4[] = '<tr width="100%" align="center">'; } $output4[] = ' <td><table class="item"> <tr><td class="itemname">'.$row['name'].'</td></tr> <tr><td class="imgtable"> <img class="testimage" width="161" height="204" src="'.$row['image'].'"> </td></tr> <tr><td class="itemprice"> <table width="100%" align="center" cellpadding="0" cellspacing="0"> <tr align="center"> <td width="50%">$'.$row['price'].' USD</td> <td width="50%"><a href="cart.php?action=add&id='.$row['id'].'">ADD TO CART</a></td> </tr> </table> </td></tr> </table></td> '; } $output4[] = '</tr> </table>'; echo join('',$output4); ?> <BR><BR><BR> <table class="hrtable" cellpadding="0" cellspacing="0" align="center"> <tr><td> <BR><BR><BR> </td></tr> </table> <?php $sql5 = 'SELECT * FROM setsdl ORDER BY id'; $result5 = $db->query($sql5); $output5[] = '<table width="100%" cellpadding="10" cellspacing="0" align="center"> <tr width="644" align="center">'; $column5_ctr = 0; while ($row = $result5->fetch()) { $column5_ctr++; if($column5_ctr % 4 == 1) { $output5[] = '</tr>'; $output5[] = '<tr width="100%" align="center">'; } $output5[] = ' <td><table class="item"> <tr><td class="itemname">'.$row['name'].'</td></tr> <tr><td class="imgtable"> <img class="testimage" width="161" height="204" src="'.$row['image'].'"> </td></tr> <tr><td class="itemprice"> <table width="100%" align="center" cellpadding="0" cellspacing="0"> <tr align="center"> <td width="50%">$'.$row['price'].' USD</td> <td width="50%"><a href="cart.php?action=add&id='.$row['id'].'">ADD TO CART</a></td> </tr> </table> </td></tr> </table></td> '; } $output5[] = '</tr> </table>'; echo join('',$output5); ?> <BR><BR><BR> <table class="hrtable" cellpadding="0" cellspacing="0" align="center"> <tr><td> <BR><BR><BR> </td></tr> </table> <?php $sql6 = 'SELECT * FROM setssum ORDER BY id'; $result6 = $db->query($sql6); $output6[] = '<table width="100%" cellpadding="10" cellspacing="0" align="center"> <tr width="644" align="center">'; $column6_ctr = 0; while ($row = $result6->fetch()) { $column6_ctr++; if($column6_ctr % 4 == 1) { $output6[] = '</tr>'; $output6[] = '<tr width="100%" align="center">'; } $output6[] = ' <td><table class="item"> <tr><td class="itemname">'.$row['name'].'</td></tr> <tr><td class="imgtable"> <img class="testimage" width="161" height="204" src="'.$row['image'].'"> </td></tr> <tr><td class="itemprice"> <table width="100%" align="center" cellpadding="0" cellspacing="0"> <tr align="center"> <td width="50%">$'.$row['price'].' USD</td> <td width="50%"><a href="cart.php?action=add&id='.$row['id'].'">ADD TO CART</a></td> </tr> </table> </td></tr> </table></td> '; } $output6[] = '</tr> </table>'; echo join('',$output6); ?> <?php include 'footer.php'; ?> PHP: Here's the C:\xampp\htdocs\cart-demo\cart.php file: <?php // Include MySQL class require_once('inc/mysql.class.php'); // Include database connection require_once('inc/global.inc.php'); // Include functions require_once('inc/functions.inc.php'); // Start the session session_start(); // Process actions $cart = $_SESSION['cart']; $action = $_GET['action']; switch ($action) { case 'add': if ($cart) { $cart .= ','.$_GET['id']; } else { $cart = $_GET['id']; } break; case 'delete': if ($cart) { $items = explode(',',$cart); $newcart = ''; foreach ($items as $item) { if ($_GET['id'] != $item) { if ($newcart != '') { $newcart .= ','.$item; } else { $newcart = $item; } } } $cart = $newcart; } break; case 'update': if ($cart) { $newcart = ''; foreach ($_POST as $key=>$value) { if (stristr($key,'qty')) { $id = str_replace('qty','',$key); $items = ($newcart != '') ? explode(',',$newcart) : explode(',',$cart); $newcart = ''; foreach ($items as $item) { if ($id != $item) { if ($newcart != '') { $newcart .= ','.$item; } else { $newcart = $item; } } } for ($i=1;$i<=$value;$i++) { if ($newcart != '') { $newcart .= ','.$id; } else { $newcart = $id; } } } } } $cart = $newcart; break; } $_SESSION['cart'] = $cart; ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title>PHP Shopping Cart Demo · Cart</title> <link rel="stylesheet" href="css/styles.css" /> </head> <body> <div id="shoppingcart"> <h1>Your Shopping Cart</h1> <?php echo writeShoppingCart(); ?> </div> <div id="contents"> <h1>Please check quantities...</h1> <?php echo showCart(); ?> <p><a href="index.php">Back to bookshop...</a></p> </div> </body> </html> PHP: And Here's the C:\xampp\htdocs\cart-demo\inc\global.inc.php file: <?php $host = 'localhost'; $user = 'root'; $pass = ''; $name = 'cart2'; $db = &new MySQL($host,$user,$pass,$name); ?> PHP: Here's the complete script if you would like to check it: http://www.mirrormyfile.com/files/1YMNFAEX/cart.zip Thank you !
When selecting data from several tables use JOIN in sql statements (http://dev.mysql.com/doc/refman/5.1/en/join.html So your query will look like: SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id; Code (markup): or SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; Code (markup):
Hello, Thanks for your reply. I've tried to do it but looks like i did it wrong, i have the next tables: setsbk setssm setself setsdl setsmg setssum how would the sql query look like? Thank you.
According to your previous message : This means that you have this column in several tables in order to you should write your statements without * and write all the colunm you want to select like: SELECT table1.column1 as `t1c1`, table1.column2 as `t1c2`, ... table2.column1 as `t2c1`, table2.column2 as `t2c2`, ... Code (markup): so you don't get columns with the same name in result