advanced search script

[spikeZ]

Hi,
I am trying to develop an advanced option search script and found this thread http://www.sitepoint.com/forums/show...ghlight=search but I have come up against a brick wall and now my head hurts.....

If I put the query as

SELECT * FROM table WHERE 1=1 

(as suggested in the other thread)
it makes no difference what I put after it, it still gives all the database records (?)

What is the easiest way of designing a search that uses option menus from an html form?

I hope this makes sense to you all.....

SpikeZ

[aldob]

The posted way is actually the best approach.
You're probably falling in some other part of the process. Please post your code ;-)

Aloha,

[reminder]

which row is called 1 into your db?
and what happens if the row 1 is equal to 1?

[spikeZ]

Below is the code I am using, I have rewritten it for the purpose of posting but it still gives the same results....

<?php require_once('../Connections/vse.php'); ?>
<?php
mysql_select_db($database_vse, $vse);
$advSql = "SELECT * FROM vse_villa_details WHERE 1=1";
$advResult = mysql_query($advSql, $vse); 
$row_advResult = mysql_fetch_assoc($advResult);

if(isset($_GET['location'])) 
$advSql .=" AND state_region = {$location}";


do {
 echo $row_advResult['villa_id'];
 echo '<br>';
 echo $row_advResult['villa_name'];
 echo '<br>';
 echo $row_advResult['state_region'];
 echo '<br>';
 } while($row_advResult = mysql_fetch_assoc($advResult));

 ?><br>
location =  <? echo $location; ?><br>
sql =  <? echo $advSql; ?>

I have echoed the location search as well as the sql query the results are as follows:

15
villa_dos
LA
14
villa america
Florida

location = florida
sql = SELECT * FROM vse_villa_details WHERE 1=1 AND state_region = florida 

Any help would be appreciated

SpikeZ

[reminder]

do u read my post?
which row is called 1
and why where 1=1
ie

$id = $_SESSION["id"];
$query = $db->query("SELECT *
                     FROM table 
                     WHERE id =". $id); 

[spikeZ]

reminder, yes I did read your post so bear with me.

The reason for 1=1 is that it was suggested in the other thread as a query that will always return true. Currently I have 3 rows in the database 13, 14, 15 but I can change one of those to 1 but it makes no difference....


Am I missing something blindingly obvious?

[reminder]

if noone row is called 1 then this query wont work!!!
ie.
....WHERE row_name = something
u have
....WHERE 1 = 1
it means that he cant find row called 1 (if it dont exsist) which is equal to something!
cheers

[kleineme]

Hi,

you are doing the mysql_query before you append any further restrictions to your WHERE clause. The line "$advSql .=" AND state_region = {$location}";" has no effect onto your resultset because it is already fetched at this moment. Move the mysql_query line right after the line where you append the restriction.

[kleineme]

Hi,

if noone row is called 1 then this query wont work!!!
ie.
....WHERE row_name = something
u have
....WHERE 1 = 1
it means that he cant find row called 1 (if it dont exsist) which is equal to something!
cheers

have you tried to use it? WHERE 1=1 is always true so when there are no further restrictions you will get all rows. When there are further restrictions it doesn't hurt because you will get the same result as if it weren't there.

Unfortunately MySQL really lets you create a column called '1'. I would assume that this is absolutely non-standard, at least it is highly unrecommended. But if you use a WHERE 1=1 with a column '1' present MySQL still evaluates it as a boolean expression. If you want to evaluate the data stored in column '1' you have to put the column name in backticks, i.e. WHERE `1` = 1.