PHP MySQL - AND OR Query? Help :)

[JuiceUK]

Hi,

I want to do a search on my site by ticking boxes. It's to find jobs - so you will tick a box in the 'roles' list then tick a box in the 'location' list. When you select one or many from the options you press search and it pulls back all the jobs with the locations or xyz and/or with the role of xyz. This is my form:

<form method="POST" action="results.php">
<table>
<tr>
<td>
<h2>Step 1</h2>
<h3>Location</h3>
<input type="checkbox" name="location[]" value="1" /><label for="location">North West</label><br />
<input type="checkbox" name="location[]" value="2" /><label for="location">Yorkshire</label><br />
<input type="checkbox" name="location[]" value="3" /><label for="location">East Midlands</label><br />
<input type="checkbox" name="location[]" value="4" /><label for="location">West Midlands</label><br />
<input type="checkbox" name="location[]" value="5" /><label for="location">South East Midlands</label>
</td>
<td>
<h2>Step 2</h2>
<h3>Role</h3>
<input type="checkbox" name="role[]" value="1" /><label for="role">Technical</label><br />
<input type="checkbox" name="role[]" value="2" /><label for="role">Commercial</label><br />
<input type="checkbox" name="role[]" value="3" /><label for="role">Senior Appointments</label><br />
<input type="checkbox" name="role[]" value="4" /><label for="role">Land</label><br />
<input type="checkbox" name="role[]" value="5" /><label for="role">Construction</label>
</td>
<td><h2>Step 3</h2>
<input type="submit" name="submit" value="Search" id="submit">
</td>
</tr>
</table>
</form>

This is the results page code:

<?php

include('common.php');

$role = $_POST['role'];
$roleid = implode (' OR role = ', $role);

$location = $_POST['location'];
$locationid = implode (' OR location = ', $location);

$connection = mysql_connect($dbhost,$dbuser,$dbpasswd);
$db = mysql_select_db($dbname,$connection);

$query = 'SELECT * FROM jobs WHERE role = '.$roleid.' AND location = '.$locationid.' AND active = 1 ORDER BY title';
$result = mysql_query($query,$connection) or die(mysql_error());

// close the database
mysql_close();

$total=mysql_num_rows($result);
?>
<h1>Job Results</h1>
<?php
if (empty($total)) {
?>
<h2>No jobs found</h2>
<p>We're sorry, but we don't seem to have any jobs that match your search.</p>
<h3>Search tips:</h3>
<ul>
<li>If you're not sure of the spelling, type in part of the word</li>
<li>If you've used the advance search, try widening your search criteria</li>
</ul>
<?php
} else {
?>
<table cellspacing="0" class="sortable autostripe">
<thead>
<tr><th>Title</th><th>Description</th><th>Location</th><th>Role</th><th>Salary</th></tr>
</thead>
<tbody>
<?php
while ($row = mysql_fetch_array($result)) {
?>
<tr><td><a href="/search/details?id=<?php echo $row['id'];?>"><?php echo $row['title'];?></a></td><td><?php echo substr(strip_tags($row['description']), 0, 100);?>...</td><td><?php if ($row['location']==1) {?>North West<?php }?><?php if ($row['location']==2) {?>Yorkshire<?php }?><?php if ($row['location']==3) {?>East Midlands<?php }?><?php if ($row['location']==4) {?>West Midlands<?php }?></td><td><?php if ($row['role']==1) {?>Technical<?php }?><?php if ($row['role']==2) {?>Commercial<?php }?><?php if ($row['role']==3) {?>Senior Appointments<?php }?><?php if ($row['role']==4) {?>Land<?php }?><?php if ($row['role']==5) {?>Construction<?php }?></td><td><?php echo $row['salary'];?></td></tr>

<?php
} // end of while loop
?>
</tbody>
</table>
<?php
} // end of if($total<=0) else
?>

In the role and location column of my database I either put 1 2 3 etc so I'm trying to say look at tick box if it matches 1 2 3 etc then find all from column role or location.

I'm currently getting a bad expression error in my mysql - does anyone have the code to point me in the right direction.

Thanks very much for your time.

M

[dreamcatcher]

if (!empty($_POST['location']) &&!empty($_POST['role']))
{

$query = "SELECT * FROM jobs WHERE role IN (".implode(",",$_POST['role']).") AND location IN (".implode(",",$_POST['location']).") AND active = 1 ORDER BY title";
$result = mysql_query($query,$connection) or die(mysql_error());

}

dc

[JuiceUK]

Dreamcatcher you have done me proud as wever - I really appreciate your help. I have got it to work but it only works on if both are selected. I would like it to find an/or so if I just put a tick in the location it shows all roles under that location and if i just put a tick in the role it shows that role in all locations? or - if you select a role and a location it just shows that role in that location?

Hope that makes sense - can you help further?

Many thanks, this is the code as it stands now

M

<?php
include('common.php');

$connection = mysql_connect($dbhost,$dbuser,$dbpasswd);
$db = mysql_select_db($dbname,$connection);

if (!empty($_POST['location']) &&!empty($_POST['role']))
{

$query = "SELECT * FROM jobs WHERE role IN (".implode(",",$_POST['role']).") AND location IN (".implode(",",$_POST['location']).") AND active = 1 ORDER BY title";
$result = mysql_query($query,$connection) or die(mysql_error());

// close the database
mysql_close();

$total=mysql_num_rows($result);

}
?>

[dreamcatcher]

No problem, just need to do some juggling around:

$connection = mysql_connect($dbhost,$dbuser,$dbpasswd);
$db = mysql_select_db($dbname,$connection);

if (!empty($_POST['location']) &&!empty($_POST['role']))
{

$roles = (!empty($_POST['role'])? implode(",",$_POST['role']) : '');
$location = (!empty($_POST['location'])? implode(",",$_POST['location']) : '');

if ($roles && $location)
{
$query = "SELECT * FROM jobs WHERE role IN (".$roles.") AND location IN (".$location.") AND active = 1 ORDER BY title";
}
else
{
$query = "SELECT * FROM jobs WHERE ".($roles? 'role IN (".$roles.")' : 'location IN (".$location.")')." AND active = 1 ORDER BY title";
}

$result = mysql_query($query,$connection) or die(mysql_error());

// close the database
mysql_close();

$total=mysql_num_rows($result);

}

Not the prettiest, but should work. I haven`t tested it, so let me know.

dc