Using Multiple Variables in Select Statement

[Devil_Boy]

Hi there i have a couple questions about my code, ive learned alot here so far and im kinda stuck on using multiples variables in my select statement.

 

Basically i have about 80,000 products and there sorted by 3 columns each column narrows it down further. Such as

Computers

Computers - Laptop

Computers - Laptop - Dell

 

So instead of using long url strings to grab all the variables i made a new table and assigned each row a number so i use category numbers.

 

$sortdesc = 'Desc';
$sortrecent = 'A';
$sortf = $_GET['sortf'];
if( empty( $sortf )) {
$sortf = $sortrecent;
}
$sorto = $_GET['sorto'];
if( empty( $sorto )) {
$sorto = $sortdesc;
}
$catidnum = $_GET['catidnum'];
if( empty( $catidnum )) {
echo 'Empty';
}
else{
echo $catidnum;}


$getcat = mysql_query("SELECT Catid1, Catid2, Catid3 FROM Category WHERE Catid=$catidnum");


while ($rowcat=mysql_fetch_array($getcat))

$Catid1 = $rowcat['Catid1'];
$Catid2 = $rowcat['Catid2'];
$Catid3 = $rowcat['Catid3'];

if( empty( $Catid1 )) { 
echo 'Empty'; 
} else { 
if( empty( $Catid2 )){
$query = ("g = '" . $Catid1 . "'");
} else {
if( empty( $Catid3 )) { 
$query = ("g = '" . $Catid1 . "' AND h = '" . $Catid2 . "'");
} else { 
$query = ("g = '" . $Catid1 . "' AND h = '" . $Catid2 . "' AND i = '" . $Catid3 . "'");
}}}



//execute the SQL query and return records
$result = mysql_query("SELECT A, B, D, E, J, K, T FROM TableName WHERE $query Order By $sortf $sorto");

 

I think it screws up around when im trying to declare the $catid variables or possibly in the empty statements, the page works it but it only uses the first $query statement so its only using the most broad variable.

 

If theres an easier way to do it please let me know to.

 

Thanks

[Ken2k7]

How many results do you get back from $getcat's SQL? If it's more than one, then you're overwriting the variable $query too many times.

 

Care to explain what you're trying to do?

[Devil_Boy]

basically the url would look like www.yourdomain.com/category.php?catidnum=xx&sortf=xx&sorto=xx

then the first sql statment grabs that catidnum lets say its 2 and goes through the first table with all the different categories so lets say it lookes like

 

catid      | catid1      | catid2      | catid3

---

1            computers

2            computers    laptop

3            computers    laptop      dell

 

 

so it would grab computers and laptop and search the second table with all the products for that 

so the $query would be g = computers and h = laptop

(cause thats what column the terms are in in the second table)

 

 

the reason i did it like this is to cut down on all the typing and the long url strings passing all the variables theres about 1000 different categories

 

hope that helps

[Ken2k7]

Okay. Can you post the table structure for the second table?

 

Reason is because normally, you would just write one SQL to select the things you need.

[Devil_Boy]

the table structure well it goes from A - AL so theres quite a bit of columns they include

Product number

Product title

price

cost price

short description

Category1 (column g)

Category2 (column h)

Category3 (column i)

 

etc

half of them i dont use

 

i use A, B, D, E, J, K, T    and G, H, I    are used for sorting the products.

 

hope this isent to confusing.

[Ken2k7]

Something like:

SELECT t.*
FROM `TableName` t
INNER JOIN `Category` c
     ON c.Catid1 = t.g
          AND c.Catid2 = t.h
          AND c.Catid3 = t.i

?

 

I don't get the sortf and sorto. Can you explain what they do?

[trq]

You seriously need to look into database normalization (if your using columns A through to Z for instance). Theres a link in my sig to a free online book (hudzilla), it contains an entire chapter on the subject.