HELP with MySQL inner joins

2012-04-09  Source: Original Site  Category:PHP  Views:0 

Advertisement

Working on my final project for a PHP college course. I am working on a shopping cart at this point. I am hacking the script which came with my text book to make it work with my db.

In my db I have:

Table name = 'cart' in cart I have the following rows;
cartId
cookieId
isbn
qty

Table name = 'books' in books I have the following relevant rows;
isbn
title
price

I am able to click and add items to the cart. This function is supposed to display the cart (and I get a blank page). Pardon me, but I am posting the whole function just in case:

function ShowCart()
{
// Gets each item from the cart table and display them in
// a tabulated format, as well as a final total for the cart

global $hst, $usnm, $pwd, $dbs;

// Get a connection to the database
@ $db = new mysqli($hst, $usnm, $pwd, $dbs);
if (mysqli_connect_errno()) {
echo 'Connection to database failed:'.mysqli_connect_error();
exit();
}

$totalCost = 0;
$query ="select * from cart inner join items on cart.isbn = books.isbn where cart.cookieId = '" . GetCookieId() . "' order by books.name asc"; $result = $db->query($query);

?>
<html>
<head>
<title> Your Shopping Cart </title>

</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 = mysqli_fetch_array($result, mysqli_ASSOC)) {
// Increment the total cost of all items
$totalCost += ($row["qty"] * $row["price"]);
?>
<tr>
<td width="15%" height="25">
<font face="verdana" size="1" color="black">
<select name="<?php echo $row["isbn"]; ?>" onChange="UpdateQty(this)">
<?php

for($i = 1; $i <= 20; $i++)
{
echo "<option ";
if($row["qty"] == $i)
{
echo " SELECTED ";
}
echo ">" . $i . "</option>";
}
?>
</select>
</font>
</td>
<td width="55%" height="25">
<font face="verdana" size="1" color="black">
<?php echo $row["title"]; ?>
</font>
</td>
<td width="20%" height="25">
<font face="verdana" size="1" color="black">
$<?php echo number_format($row["price"], 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["isbn"]; ?>">Remove</a>
</font>
</td>
</tr>
<?php
}

// 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">&lt;&lt; Keep 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
}

?>
</body>
</html>

Related articles
  • HELP with MySQL inner joins 2012-04-09

    Working on my final project for a PHP college course. I am working on a shopping cart at this point. I am hacking the script which came with my text book to make it work with my db. In my db I have: Table name = 'cart' in cart I have the following ro

  • Help with SQL [INNER JOIN] 2014-01-14

    ello, For the same problem that I posted which dated 2000-08-15 "Help with SQL [PHP and MySQL]". Does anyone, has a high knowledge of INNER JOIN. As I stated in my post that my SQL is not to good. select m.material_id from materials m INNER JOIN (mat

  • Help with complex inner join 2014-01-08

    I'm working on simple forum and I can't make one query. I want to show on homepage list of topics where will be the author of the topic and author of last post. I can make any of them but not together, because there is double inner join to one table

  • Need help with mysql on joining multiple tables on common column 2013-08-31

    Okay here is what i have so far (i simplified it down to its essence in order not to complicate things): foreach(array("tablename1","tablename2","tablename3") as $table) { $result = mysql_query("SELECT orderid FROM $table ORDER BY orderid ") or die(m

  • MySQL Help doing UPDATE SELECT with CASE, INNER JOIN and OUTER JOIN 2013-03-22

    SYNOPSIS: I have written a successful SELECT query which includes a CASE statement, and INNER JOIN and a LEFT JOIN. However, I can't get this work in an UPDATE statement. SITUATION: * I have a Products table with 2 prices: FullPrice and OurPrice. * W

  • MySQL help with a temporary join 2014-11-28

    I have the following query that uses a filesort.. Its a bit condensed since I'm not putting in other fields I am grabbing. It is a bit slow.. for people with a lot of friends.. it could take around 2-3 seconds. I think it all happens in the temp tabl

  • MySQL inner join, help please 2014-12-15

    I'm trying to select all of the 'username' values from two tables. tables: user_info submit1 So i can see whether the username is already taken. Can anyone see what is wrong with this code? I keep getting "query 2 failed" Thanks for your help. $query

  • MySQL INNER JOIN on 2 tables with SUM() 2012-01-06

    I am having a problem trying to JOIN across a total of three tables: Table users: userid, cap (ADSL bandwidth) Table accounting: userid, sessiondate, used bandwidth Table adhoc: userid, date, amount purchased I want to have 1 query that returns a set

  • MySQL Need help with a table join and/or tables structure 2012-05-25

    Please forgive me for the length of this post. The issue is kind of complicated (in my mind at least!) In a database I have a "classes" table that holds (college) classes and the IDs of the documents (from another table) associated with that class. T

  • MySQL INNER JOIN with some empty results 2013-09-17

    i'm trying the next query SELECT tb.id_b, tb.bvalue, ta.avalue, tc.cvalue FROM tablea ta INNER JOIN tableb tb on ta.id_a = tb.id_a INNER JOIN tablec tc on tb.id_b = tc.id_b WHERE tb.id_b = 1; when I try with id_b = 1 there is one row as result, +----

  • MySQL Need Help With 3 Table Join 2014-08-19

    I need help with a 3 table join that is driving me crazy. First, I'll describe the tables: Table 1 = users: id uuid username password Table 2 = profiles: id uid firstName lastName Table 3 = friends: id uid friendid The "uid" in profiles and friends a

  • MySQL inner join help... 2014-12-17

    SELECT * FROM Schedule: +---------+--------+-------+------------+-----------+ | SchedNo | custNo | EmpNo | SchedDate | SchedTime | +---------+--------+-------+------------+-----------+ | 2 | 2 | 2 | 2006-05-15 | 11:00:00 | | 3 | 3 | 2 | 2006-05-15 |

  • MySQL INNER JOIN Optimization - Index making query slower 2012-12-31

    I am currently doing some evaluation work on different databases and encointering an optimization problem on an inner join query that I haven't yet been able to make any faster... The same query runs resonably fast on other Databases (HSQLDB: 1.7 sec

  • MySQL INNER JOIN {TABLE_NAME IS A DYNAMIC TABLE} ON (X.X1=Y.Y1) 2015-03-20

    Mr. Mikael, I´am a student brazilian of Eng. Computer Systems, I need your help about SQL Dynamic Statements with Mysql. Table: Alumns a.id a.name a.mytable_name Table: mytable_name (The "mytable_name" is a name table dynamic, the value is store in t

  • mySQL Inner Join output 2013-02-19

    Well, this is new to me. $getdata = mysql_query("SELECT userDownloads.download_name,userDownloads.download_timestamp,jiveUser.email FROM userDownloads INNER JOIN jiveUser ON userDownloads.download_name = jiveUser.name WHERE download_info = '$v4linux8

  • MySQL Inner Joins, Compostie Keys and Partition issues 2013-06-02

    I am having a very strange problem. I have partitioned a table by range that had around two million records. When I run a select query on that table joined with another, sometimes it gives results but sometimes it doesn't although records meeting tha

  • MYSQL & INNER JOIN 2013-07-08

    When i try to execute a crystal report , linking two table an error occurs : OBDC error : [MYSQL][ODBC 3.51 driver][mysqld-3.22.32] You have an error in your SQL syntax near INNER JOIN scap_it.sito_contatti ... Can someone help to resolve this proble

  • Help with mysql query + Count 2013-08-27

    thanks in advance to anyone who can help me with this query. What I am trying to do is to retreieve a list of all the users from the table 'users' and count how many posts the user has made. The code below will shows a list of users that have made a

  • MySQL Inner join in the three tables 2013-11-13

    there, I need ur appreciated help. I have problem with this queries, if I try this query: SELECT CA.amb , SUM(A.VAL) `cl amb` FROM cl_2011 A INNER JOIN tfc CA ON CA.tfc = A.COD_A GROUP BY CA.amb I have this output: amb cl amb 0B 52922 If I try this q

  • Help With mysqli->num_rows 2014-03-25

    if (!isset($_GET['search_term'])) { echo "<p class='warning'>Please enter something to search for.</p>"; } else { $search_term = $_GET['search_term']; // check for data if (($search_term == '' || $search_term == NULL) && !isset($_