[Bonzo]

Hello

I am building a search page and have hit a bit of a problem.

I built the original search using either an error code search or symptom search; if one or the other field was empty it used a different query :

PHP Code:

if ( $error > ''){
$result=MYSQL_QUERY(" SELECT * FROM hardware WHERE error='$error' ORDER BY referance ") 
or die (mysql_error());
}

elseif ( $symptom > ''){
$result=MYSQL_QUERY(" SELECT * FROM hardware WHERE symptom='$symptom' ORDER BY referance ") 
or die (mysql_error());
}

else { echo "No search terms entered. Press back on your browser";} 


Then I tried expanding this to 4 form inputs and the user could just fill in the information he knew. But the problem is that I have to have ALL the fields matching a result to get an answer and the user can not leave anything blank.

Can somebody please tell me where I am going wrong?

PHP Code:

$result=MYSQL_QUERY("SELECT * FROM hardware WHERE symptom='$symptom' AND manufacturer = '$manufacturer'AND
 component = '$component' ORDER BY referance") 
or die (mysql_error()); 


Anthony

[dennispopel]

Hello,

What is the problem anyways?
After a quick look at your code: you need a space here

'$manufacturer'AND

replace with

'$manufacturer' AND

[Bonzo]

My problem dennispopel is that I want the user to input the information he knows into the text box's on the form and leave the other text box's blank.

The trouble with my code is that it will only return a result if all the text box inputs equal a row in the table.
What I wanted was to return any row that has the information submitted on the form.

Anthony

[sszettella]

Seems to me you simply need to replace AND with OR.

[Bonzo]

Thanks for that sszettella getting close but it still will not work; looking at it I think I have to have the first part of the query ? returning something as if it is empty or not found the other AND, OR do not cut in.

PHP Code:

WHERE symptom='$symptom' 


I suppose I could have another column that will always has the same result and do the original query on that ?

More thought required today I think. Some other thoughts I had last night below.

Anthony

Below is the query I think I want written out in a simpler way ?

PHP Code:


 $result=MYSQL_QUERY
(" SELECT * FROM hardware WHERE ( if $manufacturer > '' manufacturer = '$manufacturer' ) 
(if $error > '' AND error = $error) 
(if $symptom > '' AND symptom = $symptom )
(if $component > '' AND component= $component ) 
ORDER BY  referance") or die (mysql_error()); 


Another thought is do the first query which will then give me an array with all the referance numbers of rows that contain the error. I would then need to do the next query on the sypmtom using the results returned from the first query and so on.

PHP Code:


if ( $error > ''){
$result=MYSQL_QUERY
(" SELECT referance FROM hardware WHERE error='$error' ORDER BY referance ") 
or die (mysql_error());
}

if ( $symptom > ''){
while ($row = mysql_fetch_array($result)) {
$result=MYSQL_QUERY
(" SELECT referance FROM hardware WHERE referance = $row['referance'] AND symptom='$symptom' ORDER BY referance ")
 or die (mysql_error());
}} 


[the_mole001]

Hello There,

How about something that searches for the closest match using all the information given? Something like...

PHP Code:

$result=MYSQL_QUERY("SELECT * FROM hardware WHERE symptom LIKE '%$symptom%' AND manufacturer LIKE '%$manufacturer%'AND 
component LIKE '%$component%' ORDER BY referance") 
or die (mysql_error()); 


The % character says that the word can appear after/before words and it looks for the closest match, so if the person enters 3 fields and leaves one black it is still searching for three items? Just an idea. Hope it helps

[Bonzo]

That looks like it should work the_mole001; I am getting some strange returns now and again but I have confused myself over the last 24 hours and think it would be best to redo my database etc. from scratch and try again then.

Thanks for the help guys.

Anthony

[bannme]

Let me think something.

before start of your sql make sure to define yourchoices as

PHP Code:

$yourchoice = " WHERE $symptom  LIKE '%$symptom%'";
    if(!empty($manufacturer)) {
    $yourchoice = " AND $manufacturer LIKE '%$manufacturer%'";
    }
    if(!empty($component)) {
    $yourchoice = " AND $component LIKE '%$component%'";
    }

//    here is draft sql


$result=MYSQL_QUERY(select $whatyouwant $fromyourtables $yourchoices $yourorder)
or die (mysql_error()); 


I think this can help

[Bonzo]

I think that was just the method I was looking for bannme, it should work but I keep getting an sql error and I do not know why ! If I echo the query then hard code it into the script it works great but if I use the variables it dose not.

I will let you know how I get on later as I am in the middle of formatting my PC etc. at the moment and have brought a broken hard drive

Anthony