SELECT WHERE AND

[rvdb86]

Hi, really hope someone can help me out...

 

I have a form with two dropdown lists as follows:

 

<form name="quick_find" action="search_result.php" method="get">
      <select name="var1">
        <option value="op1">1</option>
        <option value="op2">2</option>
        <option value="op3">3</option>
      </select>
       <select name="var2">
        <option value="op4">1</option>
        <option value="op5">2</option>
        <option value="op6">3</option>
      </select>

 

I have a MySQL table with 3 columns.

 

Basically i want to select records from the table using the two variables so i tried the following:

 

$query = "SELECT * FROM table WHERE column1 = $var1 AND column2 = $var2";
$result = mysql_query($query)
or die ("Couldn't execute query");

 

This returns the result: "Couldn't execute query" 

I used the $_GET function to ensure that the variables exsist but with no luck.

 

I would really appreciate any suggestions!

[revraz]

Just use POST and then (or you could use GET if you want, but Post wont show the variables).

 

$var1 = $_POST['var1'];
$var2 = $_POST['var2'];

$query = "SELECT * FROM table WHERE column1 = '$var1' AND column2 = '$var2'";
$result = mysql_query($query)
or die ("Couldn't execute query");
[/code

[nuxy]

Try enclosing the variables with single quotes.

$query = "SELECT * FROM table WHERE column1 = '$var1' AND column2 = '$var2'";
$result = mysql_query($query)
or die ("Couldn't execute query");

 

Edit:

but Post wont show the variables

Yes it will, it's just as easy to see the post data as it is to see the get data.

 

[rvdb86]

Thanks alot for your suggestions but i am still having problems. I have posted below my code with an explanation in hope that someone could can help me out...

 

I have 2 tables:

1. products_to_categories

2. products_to_products_extra_fields

 

I want first of all to select all products that mach a category from the products_to_categories table so i used the first query:

<?php


$query = "SELECT * FROM products_to_categories WHERE categories_id = '$cut'";
$result = mysql_query($query)
or die ("Couldn't execute query");


$counter = 1;
while ($row = mysql_fetch_array($result))
{
extract($row);

 

Then for each product that maches the category i want to show a the results from the products_to_products_extra_fields table, so within the while loop (so i get each product_id from the products_to_categories table) i have the following query:

 

$query2 = "SELECT * FROM products_to_products_extra_fields WHERE products_id = '$products_id' AND $products_extra_fields_value = '$colour'";
$result2 = mysql_query($query2)
or die ("Couldn't find colour");


while ($row2 = mysql_fetch_array($result2))
{
extract($row2);

echo"

  <tr>
    <td>$products_id</td>
    <td>$categories_id</td>
    <td>$products_extra_fields_id</td>
    <td>$products_extra_fields_value</td>
  </tr>

";
}

}
?>

 

This produces: "Couldn't find colour"

 

I would really appreciate it if someone could tell me where im going wrong

[rajivgonsalves]

try this

 

$result2 = mysql_query($query2)
or die ("Couldn't find colour, ERROR:".mysql_error());

 

and tell me the error that you get

[rvdb86]

Here is what i got:

 

Couldn't find colour, ERROR:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'D'' at line 1

 

I should mention that "D" is the value of $colour.

 

Thanks alot for taking the time to try help.

[rajivgonsalves]

your $products_extra_fields_value is not coming in why are you specifying it as a variable can't you use the fieldname itself

[rvdb86]

The variable comes from a form on a different page. I need the user to select the category they want and the colour, which they can choose from a dropdown list. Eventually once i get this working there will be 2 more options for the user. I have included the code of my form if it helps.

 

<form action="find_results.php" method="post" name="find">            
      <select style="width:200px;" name="category">
        <option value="30">Round</option>
        <option value="31">Princess</option>
        <option value="32">Emerald</option>
        <option value="33">Alternative</option>
      </select>
<!-- ### EOF SELECT CUT ### -->
  <tr style="background-color:#eff8fd;">
    <td>Colour:</td>
    <td>
<!-- ### SELECT COLOUR ### --> 
  <select style="width:200px;" name="colour">
    <option value="D">D - Best</option>
    <option value="E">E - Exceptional White</option>
    <option value="F">F - Very Very White</option>
    <option value="G">G - Very White</option>
    <option value="H">H - White</option>
    <option value="I">I - Some Colour</option>
  </select>

[rvdb86]

I also tried using a value for the second variabled (replaced $colour with a value) to see if that was the problem but i still go the same error ???