Selecting all values in a field question

[loomer]

Here's my situation:

I have a total of 6 combo boxes. The user has a choice of selecting an item or he can leave it. If the user does not select an item then I would like to run a query to select everything in that field.

For example. There are 6 spec boxes. The user can select all or none of those specs. If the user selects all 6 then I simply run a query using all 6 specs. But if the user does not select an item in, let's say Spec#3 and Spec#5 then I would like to run a query with the selected items, but for Spec#3 & #5 I would like mySQL to select everything from that given column.

Basically if there is an item chosen then use that value, if no item is chosen then mySQL should select anything in that field where other selected items match.

Blah. .sorry to ramble. Thanks.

for example:

SELECT description FROM table WHERE Spec#1='bar' AND Spec#2='Hex' AND Spec#3=<UNSURE WHAT TO PUT?!?>

And any tips on speeding up a search? It has to sort through about 3300 records and takes about 15secs.. Is this normal?

[raf]

So something like
spec1 = 'one'
--> if checked, then condition needs to inlude where column1='one'
--> if not checked, then there shouldn't be anything in the condition for column1.

and this for all six. So if i check spec 2,5,6, i'll have
SELECT description FROM table WHERE columnspac2='bar' AND columnspac5='Hex' AND columnspac6='bla'

No?

15 seconds for a table of 3300 records? That sure isn't normal. 0.15 seconds would be more what i'd expect...

[loomer]

Originally posted by raf
So something like
spec1 = 'one'
--> if checked, then condition needs to inlude where column1='one'
--> if not checked, then there shouldn't be anything in the condition for column1.

and this for all six. So if i check spec 2,5,6, i'll have
SELECT description FROM table WHERE columnspac2='bar' AND columnspac5='Hex' AND columnspac6='bla'

No?

15 seconds for a table of 3300 records? That sure isn't normal. 0.15 seconds would be more what i'd expect...

I guess after hour 13 of coding sometimes you lose track of what's going on...lol I guess what's confusing me is how to structure the query depending on what specs are chosen.. Bascially if a spec is chosen then use it in the query ELSE don't include it in the query.

. Still having a problem with the hanging query though.. I must be doing something wrong here:



But I'm still doing something wrong with my query because it's hanging forever. I've used two queries. The first simply retrieves a product code that will be used in the second query:


// Select the product code from the XREF file using the session_fullproduct variable
$ProductCodeSearch = mysql("database", "SELECT productmasterxref.productxref_Productcode FROM productmasterxref WHERE productmasterxref.productxref_Prolinecode = '$session_Productfull'");


then there's the loop and second query which then goes through a table of about 3300 records and tries to match up the product code selected in the first query (and any specs that were chosen by the user).
This second query works fine when I just tell it to select WHERE the query 1 code = the code in the large table. But as soon as I add = code AND Spec 1 it hangs and hangs:

--> $Specfinder1 is the value of the Spec chosen by the user in the combo box


$i=0;

$count=mysql_numrows($ProductCodeSearch);

while($i<$count){

list($ProductCodeDisplay)=mysql_fetch_row($ProductCodeSearch);

// Select all the products from the Product Master File where the product code selected above matches the product code in the Product Master File

$ProductDescSearch = mysql("database", "SELECT productmasterfile.productmfile_Productdesc, productmasterfile.productmfile_Spec1, productmasterfile.productmfile_Spec2, productmasterfile.productmfile_Spec3,

productmasterfile.productmfile_Dimen1, productmasterfile.productmfile_Dimen2, productmasterfile.productmfile_Dimen3 FROM productmasterfile WHERE productmasterfile.productmfile_Productcode = '$ProductCodeDisplay'
AND productmasterfile.productmfile_Spec1= '$Spec1finder' "); <-- PROBLEM (also tried $Spec1finder without ' ')

$countdesc=mysql_numrows($ProductDescSearch);

list($ProductDescDisplay, $ProductSpec1Display,$ProductSpec2Display,$ProductSpec3Display,$ProductDimen1Display,$ProductDimen2D isplay,
$ProductDimen3Display)=mysql_fetch_row($ProductDescSearch);

[raf]

I don't think you've taken the right approach.

Can't you solve it in one sql statement, by joining the two tables? instead of grabbing a recordset with the products and then loop through that in PHP to get a query and recordset for each of them. I frankly don't get that loop (isn't it infinite?) or the script-logic.
Using an inner joins will surely speed up things conciderably. So use this

"SELECT productmasterfile.productmfile_Productdesc,productmasterfile.productmfile_Spec1,productmasterfile.pr oductmfile_Spec2,productmasterfile.productmfile_Spec3,productmasterfile.productmfile_Dimen1,productm asterfile.productmfile_Dimen2,productmasterfile.produotmfile_Dimen3
FROM productmasterfile INNER JOIN productmasterxref ON productmasterfile.productmfile_Productcode=productmasterxref.productxref_Productcode
WHERE productmasterxref.productxref_Prolinecode= '$session_Productfull' AND productmasterfile.productmfile_Spec1= '$Spec1finder'")

To get a recordset with all the records you need.
Maybe write the value of $Spec1finder to see if there's a value passed.

[loomer]

I've never used an INNER JOIN before but I knew there must be a way to combine the queries.

So the problem is weird. When I run the query:


$ProductCodeSearch = mysql("loomer", "SELECT productmasterfile.productmfile_Productdesc
FROM productmasterfile INNER JOIN productmasterxref ON productmasterfile.productmfile_Productcode=productmasterxref.productxref_Productcode
WHERE productmasterxref.productxref_Prolinecode= '$session_Productfull');


It works fine. If I add a manual value where Spec 1 should be it also works fine:


$ProductCodeSearch = mysql("loomer", "SELECT productmasterfile.productmfile_Productdesc
FROM productmasterfile INNER JOIN productmasterxref ON productmasterfile.productmfile_Productcode=productmasterxref.productxref_Productcode
WHERE productmasterxref.productxref_Prolinecode= '$session_Productfull' AND productmasterfile.productmfile_Spec1 = '304' ");


Now I do output the value of $Specfinder1 and the value does output correctly. But when I substitute the manual value for $Spec1finder it's not returning the correct results:


$ProductCodeSearch = mysql("loomer", "SELECT productmasterfile.productmfile_Productdesc
FROM productmasterfile INNER JOIN productmasterxref ON productmasterfile.productmfile_Productcode=productmasterxref.productxref_Productcode
WHERE productmasterxref.productxref_Prolinecode= '$session_Productfull' AND productmasterfile.productmfile_Spec1 = '$Spec1finder' ");


Can you see or think of anything off hand why this isn't working for me?

[raf]

Probably a typo but $Specfinder1 isn't the same as $Spec1finder. Maybe it's something stupid like that.

Instead of running the query, print it to the browser so you see what happens. With something like
echo ("the sql-statement");
exit
Then you see if the values from the variable or inserted.