sql syntax help

[samoht]

Hello again,

 

I want to have a search query that checks multiple values against the same field.

 

example:

the field = featureName

the featureName's I want are 'Chocolate' and 'Sugar Free'

 

my sql looks like

SELECT... FROM...

WHERE featureName = Chocolate

AND featureName = Sugar Free

 

This is turning up zero  - I know I am doing this wrong, can someone help me with the right syntax?

 

Thanks

[lemmin]

Yeah, that will always return nothing because you are asking for something to be two values at the same time. You just need to change it to an OR instead of AND.

"WHERE featureName = 'Chocolate' OR featureName = 'Sugar Free'"

[dennismonsewicz]

try putting Chocolate and Sugar Free in single quotes

[jcanker]

Both answers are potentially correct, depending on what you're really looking for in the db....

If you need to return the rows that are Chocolate AND Sugar Free, then use and (you can also use &&)

 

if you need to return the rows that are Chocolate OR Sugar Free, then use or (you can also use ||  --a double pipe sign)

 

either way, when using non-integers as the value for a select, insert, or update, you need to put the value in single or double quotes, whichever is appropriate for the code you use to build the query

[lemmin]

If you use AND, in his situation, it would find a row where the field has the first value, then check the same field for a different value, never returning anything.

[jcanker]

Ah, True...I didn't look closely enough...I just assumed the column names were different, and we all know what happens when you assume.... :-X

[samoht]

So I changed my syntax to

WHERE featureName = 'Sugar Free'

OR featureName = 'Chocolate'

AND itf.ItemId = i.ItemId

AND...

 

This locked up the sever pretty good =-)

Am I supposed to do the or at the end of the condition?

 

Also I know non int values go in quotes or single quotes - I just forgot to include them in the post.

[lemmin]

You should probably post the entire statement that isn't working.

[samoht]

this works:

SELECT f.Name as featureName, i.Name, pd.ItemId AS Id, pp.ProductId, pd.Name, pp.PriceSell

FROM product pd, item i, productprice pp, itemfeatures itf, features f

WHERE itf.FeatureId = f.FeatureId

AND itf.ItemId = i.ItemId

AND pd.ItemId = i.ItemId

AND pd.ProductId = pp.ProductId

AND ClientPriceCode = 'R1'

AND featureName= 'Sugar Free'

 

and this fails ??

SELECT f.Name as featureName, i.Name, pd.ItemId AS Id, pp.ProductId, pd.Name, pp.PriceSell

FROM product pd, item i, productprice pp, itemfeatures itf, features f

WHERE itf.FeatureId = f.FeatureId

AND itf.ItemId = i.ItemId

AND pd.ItemId = i.ItemId

AND pd.ProductId = pp.ProductId

AND ClientPriceCode = 'R1'

AND featureName = 'Sugar Free'

OR featureName = 'Chocolate'

[lemmin]

Is there any error messages or just a crash?

[samoht]

no errors just spin spin spin

[lemmin]

Could you post the code that iterates through your query results? I think that is where the error is.

[samoht]

This is in the header of the page

<?php 
$QFrom 		= "FROM product pd, item i, productprice pp, itemfeatures itf, features f, packaging pack, size s, brands b";
$QWhere		= "WHERE pd.PackageId = pack.PackageId
			AND itf.FeatureId = f.FeatureId
			AND itf.ItemId = i.ItemId
			AND i.BrandId = b.BrandId
			AND pd.SizeId = s.SizeId
			AND pd.ItemId = i.ItemId
			AND pd.ProductId = pp.ProductId
			AND ClientPriceCode = 'R1'
                                AND featureName = '$feature1'";
                                //AND featureName = '$feature2'";
$QOrderBy	= "ORDER BY pd.Name";

# Add Brand to query.
if (!isset($_GET['brand']) || (isset($_GET['brand']) && $_GET['brand']=="ALL"))
	unset($_SESSION['brand']);
elseif (isset($_GET['brand']))
	$_SESSION['brand'] = $_GET['brand'];

if (isset($_SESSION['brand'])) {
	$QWhere .= $QWhere == '' ? "WHERE i.Code = '$_SESSION[brand]'" : " AND b.Code = '$_SESSION[brand]'";

	# Get name of brand being displayed.
	$qryBrand = "SELECT Name FROM brands WHERE Code = '$_SESSION[brand]'";
	$rsBrand = mysql_query($qryBrand, $connection1) or die(mysql_error());
	$row_rsBrand = mysql_fetch_assoc($rsBrand);
	$Brand = $row_rsBrand['Name'];
	mysql_free_result($rsBrand);
}

# Add Size to query.
if (!isset($_GET['size']) || (isset($_GET['size']) && $_GET['size']=="ALL"))
	unset($_SESSION['size']);
elseif (isset($_GET['size']))
	$_SESSION['size'] = $_GET['size'];

if (isset($_SESSION['size'])) {
	$QWhere .= $QWhere == '' ? "WHERE pd.SizeId = '$_SESSION[size]'" : " AND s.SizeId = '$_SESSION[size]'";

	# Get name of brand being displayed.
	$qrySize = "SELECT Name FROM size WHERE SizeId = '$_SESSION[size]'";
	$rsSize = mysql_query($qrySize, $connection1) or die('bad size'.mysql_error());
	$row_rsSize = mysql_fetch_assoc($rsSize);
	$totalRows_rsSize = mysql_num_rows($rsSize);
	$Size = $row_rsSize['Name'];
	mysql_free_result($rsSize);
}


$sql = "SELECT f.Name as featureName, i.Name AS ItemName, pd.ItemId as Id, pp.ProductId, pd.Name, pd.PriceRetail, pp.PriceSell, i.ImagetnURL, QtyInStock, pack.PackageType, s.Name as Size 
$QFrom $QWhere $QOrderBy";
$result     = dbQuery(getPagingQuery($sql, $productsPerPage));
$pagingLink = getPagingLink($sql, $productsPerPage, "c=$catId");
$numProduct = dbNumRows($result);

[lemmin]

is dbQuery a custom function or yours? It isn't a php function that I know of, so the error could be in that function, as well as in getPagingLink, or dbNumRows. If there is iteration after the code you posted, that is actually the code I was interested in.

 

My guess is that, if the query returns nothing, it is fine because it never loops, but if it returns more than one result, a loop is never exiting, somewhere.

[samoht]

my function:

<?php
function dbQuery($sql)
{
$result = mysql_query($sql) or die(mysql_error());

return $result;
}

 

I think I am getting stuck in an infinite loop somewhere but why?

 

The query is not returning zero because there are records with both featureName's

one at a time works fine - just not together??

[lemmin]

Do either of those other functions (getPagingLink() or dbNumRows()) have any loops in them? Is there a loop after the code you posted somewhere? I am almost certain that the error is in your code, not the SQL.

[samoht]

I am almost certain that the error is in the syntax because when I run the same query from phpMyAdmin It behaves the same way.

 

After this I only have one loop

if ($numProduct > 0 ) {

$i = 0;
while ($row = dbFetchAssoc($result)) {

	extract($row);...

[lemmin]

phpMyAdmin freezes when you run the query? What if you do the query in MySQL Query Browser?

[samoht]

On my machine I dont have access to MySql Query Browser

[samoht]

is it possible that I had a server crash - first?

 

Anyway, now I am having all kinds of hang ups

 

I simplified the query to:

SELECT pd.Name, f.Name
From product pd, item i, itemfeatures itf, features f
Where itf.FeatureId = f.FeatureId
AND itf.ItemId = i.ItemId
AND pd.ItemId = i.ItemId
AND f.Name = 'Sugar Free'
OR f.Name = 'Chocolate'

 

This still causes a crash or infinite loop.

 

???

[samoht]

OK so I simplified my query yet some more.

This

SELECT i.ItemId, i.Name, f.Name
From item i, features f, itemfeatures itf
Where i.ItemId = itf.ItemId
AND f.FeatureId = itf.FeatureId 
AND f.Name = 'Sugar Free'

 

comes up with 15 records on my local machine -

 

when I add the 'Chocolate' to the mix like so:

SELECT i.ItemId, i.Name, f.Name
From item i, features f, itemfeatures itf
Where i.ItemId = itf.ItemId
AND f.FeatureId = itf.FeatureId 
AND f.Name = 'Sugar Free'
OR f.Name = 'Chocolate'

 

I get 8,778,651 records  - obviously something wrong

 

Any Ideas???

 

Thanks

[lemmin]

Do you have that many records?

[samoht]

nope,

 

I only have 1,985 items and 4,157 itemfeatures

[lemmin]

I think I see what it is doing. It seems like it returning all results for each result, resulting in the result being exponentially raised to the number of criteria.

 

I think the reason it is doing this is because the WHERE clause is checking different tables. It finds ItemId's from i that match the critera, but then it creates a new row for every occurance of 'Chocolate' or 'Sugar Free' AND for every occurance of the FeatureId criteria,wich will make extra rows that keep the same data for the f table(which raises the number exponentially.) It happens because you are not making a DIRECT comparison between table f and table i. To fix this, try the following:

"SELECT i.ItemId, i.Name, (SELECT Name FROM features WHERE FeatureId = itf.FeatureId AND Name = 'Sugar Free' OR Name = 'Chocolate') as fName
FROM item i, itemfeatures itf
WHERE i.ItemId = itf.ItemId"

 

The way this (hopefully) works is that it gets all the ItemId's from item that match the criteria, then, for each of them, it will select the name for features that has matches the criteria. This way, there is still no direct comparison between item and features, but it only checks if the item is there (or something like that.)

 

I don't guarantee that code will work if you copy and paste it, but hopefully you get the idea of what I am trying to change it to.

[samoht]

Thanks for the insite!

 

unfortunately this returns a  "MySQL said: Documentation

#1242 - Subquery returns more than 1 row"

 

Any Ideas??

 

What about a left join?