Why doesn't this work?

[Manat]

Hi everyone,

I have an online catalog on my extranet, running on php and mysql. I want to be able to search by price, but price range.

If I have links like: 10$-15$
15$-23$
etc....

See the problem is that I don't have any suggested retail prices, just price ranges, so I stored min and max prices in two separate INT fields. So i could use simple comparison operators like: WHERE min_price >= 5 AND max_price <= 15

Here is my code:

I pass a min and max variable through the url and do a $_GET.
then

$strSQLPrice = mysql_query("Select Price_Range_ID FROM Price_Range WHERE min_price >= $min AND max_price <= $max");

$whereClause = constructWhereClause($ageId,$priceId,$pieceId,$awardWinner,$newProduct,$subCat, $min, $max);
$strSQL =
"SELECT DISTINCT
prod.Produit_ID AS prodId,
prod.Is_New AS newProduct,
prod.Is_Award_Winner AS awardProd,
prod.Nom AS prodName,
prod.img_thumb_1 AS prodThmImg,
prod.img_pop_1 AS prodPopUpImg,
prod.vr AS vr,
prod.SKU AS prodSKU,
prod.Number_Piece_Range_ID AS piecesId,
prod.Price_Range_id AS priceId,
prod.List_Group_Age_ID AS ageId,
prod.subCat AS subCat,
prod.prio AS priority
FROM
Produit AS prod,
Groupe_Age AS age
WHERE
".$whereClause."
ORDER BY prod.priority ASC
". $limit;
$q = new Query();
$q->setSQL($strSQL);
return $q;

function constructWhereClause($ageId,$priceId,$pieceId,$awardWinner,$newProduct,$subCat, $min, $max) {
$i = 0; //Count the number of clauses
$whereStmts = array();//an array to hold each clause

$rows = mysql_num_rows($strSQLPrice);
for ($i=0; $i < $rows; $i++){
list($id,$numOrder) = pg_fetch_row($strSQLPrice,$i);

$whereStmts[$i++] = " prod.Price_Range_id = $id ";

}

$numClauses = $i;
$whereClause = "";
for( $j = 0; $j <$numClauses - 1; $j ++ ) { // loop through each clause, construct proper SQL
$whereClause .= $whereStmts[$j]. " AND ";
}
$whereClause .= $whereStmts[$numClauses-1];
return $whereClause;
}

Would anyone know what the problem with my code is?
It doesn't seem to be working properly, and the Where clause doesn't seem to take effect.

What is being outputted by the browser is everything... It displays everything as if there were no WHERE clause, like a basic Select *.

I would apreciate any help.
MK

thx

[drawmack]

try echoing out your sql statement and taking a look at it.

[Manat]

Hi,

I echoed the SQL statement and the where clause isn't even there. There is no WHERE PRice ANything.

That means that:

$rows = mysql_num_rows($strSQLPrice); 
for ($i=0; $i < $rows; $i++){ 
list($id,$numOrder) = pg_fetch_row($strSQLPrice,$i); 

$whereStmts[$i] = " prod.Price_Range_id = $id ";

Doesn't work at all.
Please can anyone plz tell me what is the proper way of doing this, because it seems I am very much on the wrong track.

[Manat]

pg_fetch_row? Im sorry, I'm on MySQL, I was on the wrong docs section in php.net.

Anyhow, i'm trying this now:

$a=0;
while ($rowPrice = mysql_fetch_array($strSQLprice, MYSQL_NUM)) {
$a++
  $whereStmts[$i++] = " prod.Price_Range_id = $rowPrice[$a] ";
}PHP] 

But I get a parse error,  would anyone know why i'm getting a parse error, or what is the best way to do this?

Thx
MK

[Manat]

Someone PLZ help!!!!!

Im sure if a good PHP guy takes a look at this they can figure it out.

There's something I'm missing

plz help anyone

thx
MK

[Manat]

someone plzzz help me out!!!

[tsinka]

Hi,

it's a little bit difficult to read your code but try this:

$strSQLPrice = mysql_query("Select Price_Range_ID FROM Price_Range WHERE min_price >= $min AND max_price <= $max") or die("SQL error: ".mysql_error()); 

$arrIds = array();

while ($row = mysql_fetch_array($strSQLPrice)) {
  $arrIds[] = $row["Price_Range_ID"];
}

$ids = implode(",",$arrIds);

$strSQL = "SELECT DISTINCT prod.Produit_ID AS prodId, "
         ." prod.Is_New AS newProduct, "
         ." prod.Is_Award_Winner AS awardProd, "
         ." prod.Nom AS prodName, "
         ." prod.img_thumb_1 AS prodThmImg, "
         ." prod.img_pop_1 AS prodPopUpImg, "
         ." prod.vr AS vr, "
         ." prod.SKU AS prodSKU, "
         ." prod.Number_Piece_Range_ID AS piecesId, "
         ." prod.Price_Range_id AS priceId, "
         ." prod.List_Group_Age_ID AS ageId, "
         ." prod.subCat AS subCat, "
         ." prod.prio AS priority, "
         ." FROM Produit AS prod, "
         ." Groupe_Age AS age "
         ." WHERE prod.Produit_ID IN (".$ids.") "
         ." ORDER BY prod.prio ASC "
         .$limit;
$q = new Query(); 
$q->setSQL($strSQL); 
return $q; 

Where does $limit come from ? Query seems to be a class. Does that class support to show query errors ? If not, adjust it so that it uses mysql_error() along with mysql_query().

I think the query has to be tweaked a little bit because there is no part in the where clause regarding the relationship between Groupe_Age and Produit (or I've overseen it).

Thomas

[Manat]

Hi,

this is $limit:

	$offset = ($pageIndex-1)*$numToDisplay;
	$limit = " LIMIT $offset,$numToDisplay "; 

So no it's not a class, just a variable.

So I implemented the code, and it still doesn't seem to work.

Right now tsinka, im pretty desperate with this, I would really appreciate any help you can provide.

Thanks alot
MK

[tsinka]

Hi,

with class I meant the "Query" in $q = new Query();

I noticed that
FROM Produit AS prod,Groupe_Age AS age

which is an inner join. So how do they tables relate to each other.

You want to get the records from Produit where a field in Produit is the same as in Groupe_Age ?

Just to test:

Comment out the following lines:
$q = new Query();

$q->setSQL($strSQL);

return $q;

and do this instead:

$res = mysql_query($strSQL) or die("SQL error: ".mysql_error());

to see what might be wrong with the query.

Thomas

[Manat]

Hi,

I got this error when doing that:

SQL error: You have an error in your SQL syntax near 'FROM Produit AS prod, Groupe_Age AS age WHERE prod.Produit_ID IN (5,33,34,35) ' at line 1

but there is no sql at line 1

and I get no parse error in my logs.

This is the class u spoke about:

class Query
  {

var $result;
var $row;
var $sql;



function setSQL($sql) {
$this->sql = $sql;
}

//Queries the given database with the constructed sql. Adam Cuipka
function doquery2(&$db) {
$this->doquery($db,$this->sql);
}

function doquery(&$db, $query = "")
{
  if($query != "")
  {
    $this->result = @mysql_query($query, $db->ConnectionID);
    return $this->result;
  }
}

   

[tsinka]

My fault,

replace
." prod.prio AS priority, "
by
." prod.prio AS priority "

The "line 1" means line 1 of the SQL query.

Thomas

[Manat]

Ok, somethign is working now, I get some of the results but not all of them.

I had to put back:

$q = new Query(); 
$q->setSQL($strSQL); 
return $q; 

Because I got a PHP Fatal Error: call to a member function on a non-object because a function called $q.

But I am not getting all of the results, only a few of them.

echo:
FROM Produit AS prod, Groupe_Age AS age WHERE prod.Produit_ID IN (4,30,31) ORDER BY prod.prio ASC LIMIT 0,6

Can you explain the 'IN' keyword? it's for arrays?
Could that be the cause?

[tsinka]

No,

for example

WHERE field in (3,5,6) is the short form of

WHERE field=3 AND field=5 AND field=6

About that inner join ... what do you want to do with Groupe_Age ?

Which rows do you expect to be returned ?

Thomas

[Manat]

hi,

Not all of the correct rows are being returned, some of the results are not the correct one.

I have an online catalog on my extranet, and in my online catalog we have products... obviously. But each product has a price range and not suggested retailer prices.

I want to be able to search by price range.
0-10$
10-20$

So I wanted to do a type of:
SELECT * FROM catalog WHERE min_price >= 5 AND max_price <= 15.

So in my price_range table, I added a min_price and max_price fields.

If you have a better solution, plz let me know!

And for groupe_age it's just for putting what age group this product is target for

//Returns the query which gets the age name from the ages table
function ageQuery($ageId) {
	$strSQL =
	"SELECT
		age.Nom AS prodAge
	FROM
		Groupe_Age age
	WHERE
		age.Groupe_Age_Id='$ageId'";
        $q = new Query();
        $q->setSQL($strSQL);
        return $q;
}

MK

[tsinka]

Hi,

in the "big" query you don't read any data from Group_Age so just to test replace

." FROM Produit AS prod, "
." Groupe_Age AS age "

by

." FROM Produit AS prod "

About that price range:
If I understand it right instead of having a min and max field for each product you created an extra table containing price ranges and have the primary key of that table stored in the product table ? I don't get the reason for that completely ... to make administration of price ranges easier ... or having several products with the same price range ?

Give me an example of a product that doesn't get fetched for a given price range (including the data stored in the price_range table for that product and the price_range id stored in the product table for that product).

Thomas

[Manat]

Hi,

The reason why the two tables were seperated was because it's just normalized this way.

the product table has a price_range_id field
which corresponds to a field in the price_range table.

Here it's the price range structure:

Field Type Attributes Null
Price_Range_ID int(11) No auto_increment

NomPrice varchar(50) Yes NULL

Is_Active tinyint(1) No 1

Ordre_Aff int(4) No 0

min_price int(3) No 0

max_price int(3) No 0

The reason for this was to make it normalized, and yes to have only a set of pre-defined price ranges. It would have been much easier if we had prices and not price_ranges.

Ok, here is an example:

If I click on 60-80
so min_price is 60 and max_price is 80

I do not get this SKU
SKU #3423 price_range_ID in product table is 58
58 in the price_range table has this info:

Price_Range_ID NomPrice Ordre_Aff min_price max_price
58 $70-$75 12 70 75

Thanks for all your help so far tsinka
I really apreciate it

MK

[tsinka]

I think I found the error:

Replace
." WHERE prod.Produit_ID IN (".$ids.") "
by
." WHERE prod.Price_Range_id IN (".$ids.") "

Thomas

[Manat]

Excellent
thanks alot dude

it works now

but I don't seem to be able to add this WHERE clause: prod.Is_Active = '1'

Like
." WHERE prod.Produit_ID IN (".$ids.") "
." AND WHERE prod.Is_Active = '1' "

how come it doesn't work?
is it the correct syntax?

[tsinka]

Use WHERE only once:

." WHERE prod.Produit_ID IN (".$ids.") "
." AND prod.Is_Active = '1' "

Thomas

[Manat]

Sorry yes of course.

But how come my navigation no longer works?
Number of products no longer shows and navigation either.

Thanks