jeudi 27 avril 2017

mysql if statement and where

I'm trying to get this code to work. As of now I'm getting all sorts of syntax errors. When I don't get syntax errors, the code does not do what I want it to. I'm new to sql and I'm not sure entirely what is wrong with it.

I'm specifically trying to do 2 things.

  1. Make the Tables Join only if the variable $category exists. Because not all of my Styles are linked into the category table yet.

  2. I'm trying to get a result based on only the 3 or less variables I've used. For Example, if I use all 3 variables $divisionof, $mill, and $category, I want to pull all of the products that fit into those criteria. If I only use $mill, I want to find styles that fit into only that 1 criteria. So on and so forth.

      <?php $divisionof='Mohawk'; $mill='Aladdin'; $category='Commercial Cut  Pile';
    
    

    $order = mysqli_query($con, " SELECT DivisionOf, Manufacturer, Style, Price, ShowPrice, Include, Fiber, Width, Backing

    FROM CarpetInfo

    SELECT IF ('$divisionof'=DivisionOf, JOIN CarpetCategorySort USING (ProductID) JOIN CarpetCategories USING (CategoryID),WHERE (('$divisionof'=DivisionOf) AND ('$mill'=Manufacturer) AND ('$category'=Category)), '')

    WHERE (('$divisionof'=DivisionOf) AND ('$mill'=Manufacturer) AND ('$category'=Category)) OR (('$divisionof'=DivisionOf) AND ('$category'=Category)) OR (('$mill'=Manufacturer) AND ('$category'=Category)) OR ('$category'=Category),

    WHERE (('$divisionof'=DivisionOf) AND ('$mill'=Manufacturer)) OR ('$divisionof'=DivisionOf) OR ('$mill'=Manufacturer) )

    GROUP BY ProductID

    ORDER BY CASE WHEN ((Price <= '0.00') OR (Price >= '49.95') OR (ShowPrice!='Yes')) THEN Style ELSE 0 END asc, CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Price ELSE 0 END asc, CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Style ELSE Manufacturer END asc, CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Manufacturer END asc;

    "); $result = mysqli_fetch_row($order);

    // Gather data from database mysqli_data_seek($order, 0); while($info = mysqli_fetch_array( $order ))

    include($_SERVER['DOCUMENT_ROOT'].$info['Include']);?>

Aucun commentaire:

Enregistrer un commentaire