help in search

**sukanya.paul** · 04-26-2007, 06:47 AM

hi...
i hv a php form called adminretrieve.php which has 6 search criterias. they are skill,percentage,experience,current salary,expected salary and a drop down box for the date whn profile was posted.
the admin can enter 1 or all the fields according to his requirement and according to the filelds entered the database "candidate" has to be searched and the result listed as a table.
my problem is in the query to select the conditions. if i give AND then all the fields hv to be entered and if igive OR then all the conditions r being executed separately.
EXAMPLE: if i enter php in skill and last 7 days in post date then all the profiles which hv php skill are displayed plus all the profiles submitted in last 7 days even if de dont hv php skill are displayed..
i want only those profiles which hv been posted in last 7 days and hv skill php.
same applies for all the fields.
can someone pls help
am entering my code here:

<html>
<body bgcolAND="Silver" text="mediumvioletred" link="CANDnflowerblue" vlink="Darkmagenta">
<fANDm name="action" id="action" method="post" action="questeditfANDm.php">
<table border="0" bgcolAND="gainsbANDo" width="100%" height="800px" cellpadding="5" cellspacing="0">
<tr>
<td width="80%" >
<table border="0" width="100%" height="800px" cellspacing="0" cellpadding="5" bgcolAND="white">
<tr height="50px" >
<td width="100%" bgcolAND="white"><!right TOP>
<br>
<br>
<br><center><FONT face="COMIC SANS" TEXT="mediumvioletred"size="4"><b><u>EDIT QUESTIONS</u></b></font></font></center>
</td>
</tr>
<tr>
<td>

<?
include 'db.php';
$valid = false;
$option1 = $_POST['post'];

switch($option1)

{

case "7" :

$resumedate = array();

$lastweek = mktime(0,0,0,date("m"),date("d")-7,date("Y"));
$resumedate[0]=date("d/m/Y", $lastweek);

break;

case "15" :

$resumedate = array();

$last15 = mktime(0,0,0,date("m"),date("d")-15,date("Y"));
$resumedate[0]=date("d/m/Y", $last15);

break;
case "1" :

$resumedate = array();

$lastmonth = mktime(0,0,0,date("m")-1,date("d")-30,date("Y"));
$resumedate[0]=date("d/m/Y", $lastmonth);
break;
case "3" :

$resumedate = array();

$last3month = mktime(0,0,0,date("m")-3,date("d")-90,date("Y"));
$resumedate[0]=date("d/m/Y", $last3month);
break;
default :

$resumedate = array();

$resumedate[0] = "-";

echo $queryPage;

break;

} // END SWITCH
$skill=$_POST['skill'];
$experience=$_POST['experience'];
$percentage=$_POST['percentage'];
$currsal=$_POST['currsal'];
$expectedsal=$_POST['expectedsal'];
$sql="SELECT * FROM candidate WHERE skills LIKE '%$skill%' OR 10th >= '$percentage' OR 12th >= '$percentage' OR graduation >= '$percentage' OR postgrad >= '$percentage' OR currentsalary = '$currsal' OR expectedsalary = '$expectedsal' OR posteddate='$resumedate[0]'";
$query = mysql_query($sql) or die (mysql_error());
$count=mysql_num_rows($query);
if ($count>0)
$valid=true;
if ($valid)
{
echo "<table width='95%' border='2'>
<tr>
<th>ID</th>
<th>FIRST NAME</th>
<th>MIDLE NAME 1</th>
<th>LAST NAME 2</th>
<th>AGE</th>
<th>DATE OF BIRTH</th>
<th>CONTACT NUMBER</th>
<th>E-MAIL ID</th>
<th>STREET ADDRESS 1</th>
<th>STREET ADDRESS 2</th>
<th>CITY</th>
<th>STATE</th>
<th>PINCODE</th>
<th>10TH %</th>
<th>12TH %</th>
<th>GRADUATION %</th>
<th>POST GRADUATION %</th>
<th>HIGHEST QUALIFICATION</th>
<th>SPECIALISATION</th>
<th>SKILLS</th>
<th>EXPERIENCE</th>
<th>CURRENT SALARY</th>
<th>EXPECTED SALARY</th>
<th>DATE POSTED%</th>
<th>JOB CODE</th>
<th>RESUME</th>
</tr>";
while($row = mysql_fetch_array($query))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" .$row['firstname'] . "</td>";
echo "<td>" . $row['middlename'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "<td>" . $row['dob'] . "</td>";
echo "<td>" . $row['contactnumber'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" .$row['streetaddress1'] . "</td>";
echo "<td>" . $row['streetaddress2'] . "</td>";
echo "<td>" . $row['city'] . "</td>";
echo "<td>" . $row['state'] . "</td>";
echo "<td>" . $row['pincode'] . "</td>";
echo "<td>" . $row['10th'] . "</td>";
echo "<td>" . $row['12th'] . "</td>";
echo "<td>" .$row['graduation'] . "</td>";
echo "<td>" . $row['postgrad'] . "</td>";
echo "<td>" . $row['highestqualification'] . "</td>";
echo "<td>" . $row['specialisation'] . "</td>";
echo "<td>" . $row['skills'] . "</td>";
echo "<td>" . $row['experience'] . "</td>";
echo "<td>" . $row['currentsalary'] . "</td>";
echo "<td>" . $row['expectedsalary'] . "</td>";
echo "<td>" . $row['posteddate'] . "</td>";
echo "<td>" . $row['jobcode'] . "</td>";
echo("<td>");
echo("<a href='" . $row['resume'] . " ' target = '_blank' >" );
echo("<br/>" . $row['resume'] . "</a>");
echo("</td>");
echo "</tr>";
}
echo "</table>";
}
else
echo "<BR><center> NO PROFILES MATCH THE CRITERIA GIVEN.</CENTER> ";

?>

</td>

</table>
</td>
</tr>

</table>
</form>
</body>
</html>
thanks in advance..suk

**codeexploiter** · 04-26-2007, 07:52 AM

You can solve this by constructing the SQL statement that retrieves the candidate data from the table based on some conditions. Have a look at the following method (just algorithm):

(a) Get all the conditional values like skill, expectedsal, currentsal and postdate into variables

(b) Construction of SQL statement

Code:

$sql = "SELECT * FROM candidates WHERE";

if($skill != "")
	$sql = $sql . "skills LIKE '%$skill%';

if($currsal != "")
{	if($sql != "")
		$sql = $sql . " AND currentsalary = ".$currsal;
	else
		$sql = $sql. " currentsalary = ".$currsal;
}

Like the above mentioned checking you can perform the checking and construction of SQL statement for the other value also. After the complete checking the SQL statement will be constructed and you can use that statement to retrieve the records from the table.

I hope you've got the essence

**sukanya.paul** · 04-26-2007, 08:03 AM

thanks wil try tht

Thread: help in search

Thread Tools

Search Thread

help in search

Bookmarks

Bookmarks

Posting Permissions