Help with SQL Advanced Form Query

bob2oo7

Hi guys

I have an advanced form on my website and having trouble with the SQL query

Basically if I search for County Louth, it rertuns couty louth

If I search for a keyword and County Louth, it will return results that also include other counties

The issue is with the sql query

Thanks in advance for the help

$query = "select * from jobs where";

if ($trimmed != '') {
$query .= " jobspec like \"%$trimmed%\"
OR firstname like \"%$trimmed%\"
OR lastname like \"%$trimmed%\"
OR position like \"%$trimmed%\"
OR username like \"%$trimmed%\"
OR organisation like \"%$trimmed%\"";
}

if ($industry != '' and $trimmed == '') {
$query .= " industry = '".$industry."'";
}

if ($industry != '') {
$query .= " AND industry = '".$industry."'";
}

if ($experience != '' and $trimmed == '' and $industry == '') {
$query .= " experience = '".$experience."'";
}


if ($experience != '') {
$query .= " AND experience = '".$experience."'";
}

if ($location != '' and $trimmed == '' and $industry == '' and $experience == '') {
$query .= " location = '".$location."'";
}


if ($location != '') {
$query .= " AND location = '".$location."'";
}

if ($jobtype != '' and $trimmed == '' and $industry == '' and $experience == '' and $location == '') {
$query .= " jobtype = '".$jobtype."'";
}


if ($jobtype != '') {
$query .= " AND jobtype = '".$jobtype."'";
}

Eoin

Can you paste in the query that's actually being generated?

I'm not great at SQL, but I'm guessing it's because you haven't used any brackets in your query, so one of the "OR" parameters might be negating the "AND" parameters.

BostonB

I'd be trying to catch things like "couty louth" and correct them before they go into the database rather than jumping through hoops to find it. Indeed I wouldn't be storing "couty louth" as text at all. It would have a code and be an int etc.

Eoin

Counties aren't that numerous and aren't likely to change, so I don't think they'd really need to be in their own table. I'd use a drop down list though. Probably a matter of personal choice.

BostonB

Maybe your right, I'd not a dba.

But it seems to me it would be saved at least once for every job, every client, every advertiser, every CV. Its going to be important for searching for jobs, clients by location, region. It will probably be in 90% of queries.

Eoin

Yep, that's a fair point. I'm no DB expert either, so you could well be right. I certainly wouldn't have county as a free text field though, if I didn't normalise the data.

Either way, I think that the lack of brackets is most likely causing the OP's problem. In fact, the query looks certain to break in lots of places, as no "AND" or "OR" has been put in front a few of the parameters.

BostonB

The OP needs to reduce the query down to one keyword and the country, and see why that doesn't work, before adding all the other keywords and trying to work why the whole thing doesn't work.

mark renton

lookup is yer only man dude

<form action='get_county.php' method='POST'>
      County:
      <select name='county'>
        <?php $query = "SELECT county_name FROM county"; 
        $result = mysql_query($query);
       
       while ($row = mysql_fetch_array ($result)) {
          echo "<option value=$row[county_id]>$row[county_name]</option>";
        
       }
 
?>

cython

john47832 wrote: »

lookup is yer only man dude

<form action='get_county.php' method='POST'>
      County:
      <select name='county'>
        <?php $query = "SELECT [B]county_id, [/B]county_name FROM county"; 
        $result = mysql_query($query);
       
       while ($row = mysql_fetch_array ($result)) {
          echo "<option value=$row[county_id]>$row[county_name]</option>";
        
       }
 
?>

That may give the OP errors if you try to access $row[county_id] when you haven't SELECTed it in the query as per the edit above.

As to the OP, personally I would suggest (as was already mentioned) echoing the query to yourself so you can debug the actual SQL, but even once that's done, I don't think your layout of the if statements is doing yourself any favours either. It may be easier to make sense of if you were to nest conditions according to the order in which you want clauses to be appended to your query. For example, instead of

if ($trimmed != '') {
$query .= " jobspec like \"%$trimmed%\"
OR firstname like \"%$trimmed%\"
OR lastname like \"%$trimmed%\"
OR position like \"%$trimmed%\"
OR username like \"%$trimmed%\"
OR organisation like \"%$trimmed%\"";
}

if ($industry != '' and $trimmed == '') {
$query .= " industry = '".$industry."'";
}

if ($industry != '') {
$query .= " AND industry = '".$industry."'";
}

I would find

if ($trimmed != '') {
  $query .= " jobspec like \"%$trimmed%\"
  OR firstname like \"%$trimmed%\"
  OR lastname like \"%$trimmed%\"
  OR position like \"%$trimmed%\"
  OR username like \"%$trimmed%\"
  OR organisation like \"%$trimmed%\"";

  if($industry != '')
    $query .= " AND industry = '".$industry."'";
}
else {
  if ($industry != '')
    $query .= " industry = '".$industry."'";
}

easier to debug, as there is more distinction between paths of execution. Yes, there is more code, but there is less potential for overlapping conditions. Brackets, as noted earlier, would also help with clarifying precedence of conditions.

mark renton

BTW - that was a plant to encourage troubleshooting and understanding of code