I have some store scripts that I wrote and unfortunately they use subqueries. However mysql doesn't support subqueries yet. I really really desperately need a solution to this problem. I can use multiple connections to the database or even multiple databases if need be. However I can't use (at all) LEFT JOIN or JOIN queries because 1) the tables that are being subqueried do not have common elements with the table from the main query and 2) The subqueries are directly reliant on the main query. If anyone could help me out with this I would really appreciate it. Also, if necessary I could switch to postgresql, but I would need help altering my code since I haven't used it before.
Thanks in advance! Here is my code:
<?php
include("functions.php");
//$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
$s1 = mysql_connect($dbServer, $dbUser, $dbPass) or die(mysql_error());
$d1 = mysql_select_db($dbName, $s1) or die(mysql_error());
switch($_GET["action"])
{
case "add_item":
{
AddItem($_GET["id"], $_GET["qty"]);
ShowCart();
break;
}
case "update_item":
{
UpdateItem($_GET["id"], $_GET["qty"]);
ShowCart();
break;
}
case "remove_item":
{
RemoveItem($_GET["id"]);
ShowCart();
break;
}
default:
{
ShowCart();
}
}
function ShowCart() // Display the users cart
{
// Session var shiptype equals the variable passed in the url
$_SESSION['shiptype'] = $_GET['shiptype'];
switch($_SESSION['shiptype']) // Depending on the shiptype, keep that shiptype selected
{
case "Express":
{
$eselected = "SELECTED";
$pselected = "";
$cselected = "";
break;
}
case "Priority":
{
$eselected = "";
$pselected = "SELECTED";
$cselected = "";
break;
}
case "EMS":
{
$eselected = "SELECTED";
$pselected = "";
$cselected = "";
break;
}
case "AirMail":
{
$eselected = "";
$pselected = "SELECTED";
$cselected = "";
break;
}
default:
{
$eselected = "";
$pselected = "SELECTED";
$cselected = "";
}
}
// Our cart query
$cartpsql = "SELECT * FROM cart INNER JOIN " . $_SESSION['table'] . " on cart.itemId = " . $_SESSION['table'] . ".itemId WHERE cart.cookieId = '" . GetCartId() . "' ORDER BY " . $_SESSION['table'] . ".itemId ASC";
$result = mysql_query($cartpsql) or die(mysql_error());
?>
<html>
<script language="JavaScript">
function getSelect(s) {
return s.options[s.selectedIndex].value
}
</script>
<body>
<form name="cartform" method="post" action="cart.php">
<select name="shipsel" OnChange="location=getSelect(this)">
<option <?php echo $cselected; ?> value="#"> Choose a shipping type</option>
<option <?php echo $eselected; ?> value="cart.php?shiptype=<?php echo $_SESSION['expressorems']; ?>"><?php echo $_SESSION['expressorems']; ?></option>
<option <?php echo $pselected; ?> value="cart.php?shiptype=<?php echo $_SESSION['prorair']; ?>"><?php echo $_SESSION['prorair']; ?></option>
</select>
</form>
<table>
<tr>
<th><font face="verdana" size="2" color="black">Quantity</font></th>
<th><font face="verdana" size="2" color="black">Name</font></th>
<th><font face="verdana" size="2" color="black">Price</font></th>
</tr>
</table>
</body>
</html>
<?php
while($row = mysql_fetch_array($result) or die(mysql_error()))
{
// Increment the total cost of all items
$totalCost += ($row["qty"] * $row["itemPrice"]);
$totalWeight += ($row["qty"] * $row["itemWeight"]);
$_SESSION['totalCost'] = $totalCost;
$_SESSION['totalWeight'] = $totalWeight;
if ($totalWeight < 16)
{
$emslookup=(ceil($totalWeight/8))* 8;
} else {
$emslookup=(ceil($totalWeight/16))* 16;
}
$_SESSION['emslookup'] = $emslookup;
if ($totalWeight < 16)
{
$explookup=(ceil($totalWeight/8))* 8;
} else {
$explookup=(ceil($totalWeight/16))* 16;
}
$_SESSION['explookup'] = $explookup;
if ($totalWeight < 8)
{
$airlookup=(ceil($totalWeight/1))* 1;
} else {
$airlookup=(ceil($totalWeight/4))* 4;
}
$_SESSION['airlookup'] = $airlookup;
$lookupinsurance=(ceil($totalCost/50))* 50;
$_SESSION['lookupinsurance'] = $lookupinsurance;
$priorlookup=(ceil($totalWeight/16))* 16;
$_SESSION['$priorlookup'] = $priorlookup;
if ($_GET['id'] == "831" AND $_GET['action'] == "add_item")
{
?><html><script language="JavaScript">window.location="cart.php?action=add_item&id=157&qty=1";</script></html><?php
} else if ($_GET['id'] == "831" AND $_GET['action'] == "remove_item") {
?><html><script language="JavaScript">window.location="cart.php?action=remove_item&id=157&qty=1";</script></html><?php
} else if ($_GET['id'] == "157" AND $_GET['action'] == "remove_item") {
?><html><script language="JavaScript">window.location="cart.php?action=remove_item&id=831&qty=1";</script></html><?php
}
$iresult = mysql_query("SELECT * FROM us_priority_insurance WHERE coverage = $lookupinsurance") or die(mysql_error());
$emsresult = mysql_query("SELECT cost FROM foreign_ems WHERE weight = '$emslookup' AND emszone = '" . $_SESSION['emszone'] ."'") or die(mysql_error());
$airresult = mysql_query("SELECT cost FROM foreign_letterpost WHERE weight = '$airlookup' AND priority = '" . $_SESSION['airprior'] . "'") or die(mysql_error());
$expresult = mysql_query("SELECT cost FROM us_express WHERE weight = '$explookup'") or die(mysql_error());
$priorresult = mysql_query("SELECT cost from us_prioritymail WHERE weight = '$priorlookup'") or die(mysql_error());
if ($_SESSION['shiptype'] == "EMS")
{
$shipresult = $emsresult;
} elseif ($_SESSION['shiptype'] == "AirMail") {
$shipresult = $airresult;
} elseif ($_SESSION['shiptype'] == "Express") {
$shipresult = $expresult;
} elseif ($_SESSION['shiptype'] == "Priority") {
$shipresult = $priorresult;
}
?>
<html>
<script>
function UpdateQty(item)
{
stype = "<?php echo $_SESSION['shiptype']; ?>";
itemId = item.name;
newQty = item.options[item.selectedIndex].text;
document.location.href = 'cart.php?action=update_item&id='+itemId+'&qty='+newQty+'&shiptype='+stype;
}
</script>
<body>
<table>
<tr>
<td width="15%" height="25">
<font face="verdana" size="1" color="black">
<select name="<?php echo $row["itemId"]; ?>" onChange="UpdateQty(this)">
<?php
for($i = 1; $i <= 20; $i++)
{
echo "<option ";
if($row["qty"] == $i)
{
echo " SELECTED ";
}
echo ">" . $i . "</option>";
}
?>
</select>
</font>
</td>
</tr>
<td width="55%" height="25">
<font face="verdana" size="1" color="black">
<?php
echo $row["itemName"];
?>
</font>
</td>
<td width="20%" height="25">
<font face="verdana" size="1" color="black">
$<?php echo number_format($row["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["itemId"]; ?>&shiptype=<?php echo $_SESSION['shiptype']; ?>">Remove</a>
</font>
</td>
<tr>
<td width="100%" colspan="4">
<hr size="1" color="blue" NOSHADE>
</td>
</tr>
<tr>
<td width="40%" colspan="2">
<font face="verdana" size="1" color="black">
<a href="product.php?country=<?php echo $_SESSION['country']; ?>&cartid=<?php echo GetCartId(); ?>"><< Keep Shopping</a>
</font>
</td>
</tr>
<tr>
<td width="40%" colspan="2">
<font face="verdana" size="2" color="black'">
</font>
</tr>
</td>
</table>
</body>
</html>
<?php
echo "Sub Total: $" . number_format($_SESSION['totalCost'], 2, ".", ",") . "<br>";
echo "Shipping: $";
while ($srow = mysql_fetch_array($shipresult) or die(mysql_error()))
{
echo $srow[cost] . "<br>";
}
echo "Total: $". number_format($srow[cost] + $_SESSION['totalCost'], 2, ".", ",");
while ($irow = mysql_fetch_array($iresult) or die(mysql_error()))
{
echo $st . "<br> Insurance: $";
echo $irow[rate];
}
}
}
?>
