homepage Welcome to WebmasterWorld Guest from 184.73.52.98
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
3 condition query
petermourra




msg:4372728
 1:52 pm on Oct 10, 2011 (gmt 0)

Hello, I am having a similar issue. I am building a shopping cart.

On my product details page I have this, which works fine by the way :


<?php
// Check to see the URL variable is set and that it exists in the database
if (isset($_GET['id'])) {
// Connect to the MySQL database
include "storescripts/connect_to_mysql.php";
$id = preg_replace('#[^0-9]#i', '', $_GET['id']);
// Use this var to check to see if this ID exists, if yes then get the product
// details, if no then exit this script and give message why
$sql = mysql_query("SELECT * FROM products WHERE id='$id' LIMIT 1");
$productCount = mysql_num_rows($sql); // count the output amount
if ($productCount > 0) {
// get all the product details
while($row = mysql_fetch_array($sql)){
$product_name = $row["product_name"];
$price = $row["price"];
$details = $row["details"];
$category = $row["category"];
$subcategory = $row["subcategory"];
$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
$link = $row["link"];
$color1 = $row["color1"];
$color2 = $row["color2"];
$advertiser = $row["advertiser"];
}

} else {
echo "That item does not exist.";
exit();
}

} else {
echo "Data to render this page is missing.";
exit();
}
mysql_close();

?>
<?php
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Section 1 (if user attempts to add something to the cart from the product page)
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if (isset($_POST['pid'])) {
$pid = $_POST['pid'];
$wasFound = false;
$i = 0;
// If the cart session variable is not set or cart array is empty
if (!isset($_SESSION["cart_array"]) || count($_SESSION["cart_array"]) < 1) {
// RUN IF THE CART IS EMPTY OR NOT SET
$_SESSION["cart_array"] = array(0 => array("item_id" => $pid, "quantity" => 1));
} else {
// RUN IF THE CART HAS AT LEAST ONE ITEM IN IT
foreach ($_SESSION["cart_array"] as $each_item) {
$i++;
while (list($key, $value) = each($each_item)) {
if ($key == "item_id" && $value == $pid) {
// That item is in cart already so let's adjust its quantity using array_splice()
array_splice($_SESSION["cart_array"], $i-1, 1, array(array("item_id" => $pid, "quantity" => $each_item['quantity'] + 1)));
$wasFound = true;
} // close if condition
} // close while loop
} // close foreach loop
if ($wasFound == false) {
array_push($_SESSION["cart_array"], array("item_id" => $pid, "quantity" => 1));
}
}
header("location: cart.php");
exit();
}
?>








Now on the bottom of the product, I need a 3 condition query that states:

Select all products in the database that:

Category = Same Category of product selected to view AND
Color1 = Same Color1 of product selected to view OR
Color2 = Same Color2 of product selected to view


This is what I have so far:


<?php
// Include database connection
include "storescripts/connect_to_mysql.php";
// SQL query to interact with info from our database
$sql = mysql_query("SELECT * FROM products WHERE category=' ' AND color1=' ' OR color2=' ' ORDER BY date_added DESC");
$i = 0;
// Establish the output variable
$dyn_table = '<table border="0" cellpadding="10">';
while($row = mysql_fetch_array($sql)){

$id = $row["id"];
$product_name = $row["product_name"];
$category = $row["category"];
$subcategory = $row["subcategory"];
$details = $row["details"];
$price = $row["price"];
$date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));

if ($i % 3 == 0) { // if $i is divisible by our target number (in this case "3")
$dyn_table .= '<tr><td width="17%" valign="top"><a href="product.php?id=' . $id . '"><img style="border:#cccccc 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="120" height="120" border="1" /></a></td>';
} else {
$dyn_table .= '<td width="17%" valign="top"><a href="product.php?id=' . $id . '"><img style="border:#cccccc 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="120" height="120" border="1" /></a></td>';
}
$i++;
}
$dyn_table .= '</tr></table>';
?>



Please assist

 

mark_roach




msg:4372735
 2:20 pm on Oct 10, 2011 (gmt 0)

I don't really know PHP but if your code is not working it is probably because you need brackets in your SQL.

SELECT * FROM products WHERE category=' ' AND (color1=' ' OR color2=' ') ORDER BY date_added DESC

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved