problem with SQL statment

amarafzal

Hi ALL

Im setting up a search page and what i want the user to do is choose a keyword and then select 2 options in the drop down fields.

I can get the following query to work fine:
$query = "select * from jobs where job_sector = '$POST[job_sector]' AND region = '$POST[region]'";

But becasue i want the user to be able to choose a keyword so that they can narrow there search im getting an eror with following query, im assuming i need to use the LIKE function?

$query = "select * from jobs where job_title OR job_summary LIKE '%". '$POST[keyword]' ."%' job_sector = '$POST[job_sector]' AND region = '$_POST[region]'";

Can you tell me what im doing wrong and what the correct format would be for Select statment?

Thanks in advance

sivasankari_kv

$_POST is an associative array , the values won't be returned if u enclose them with in single quote

Try this :

$query = "select * from jobs where job_sector =".
$POST[job_sector] .
" AND region =".
$POST[region]

amarafzal

Like i have said in my post, i can get that to work fine, if user puts in a keyword how do i pull those results out and how do i need to modify my Select statment

any more ideas?

sivasankari_kv

This shud work

$select_clause = "select * from from jobs";

$where_and = " WHERE ";
$where_clause = "";

if ($POST[keyword] )
{
$where_clause = $where_clause . $where_and ."job_title OR job_summary LIKE '%". '$POST[keyword]' ."%";
$where_and = " AND ";
}

if ($POST[job_sector] )
{
$where_clause = $where_clause . $where_and ."sector=".$POST[job_sector];
$where_and = " AND ";
}

if ($POST[job_region )
{
$where_clause = $where_clause . $where_and ."region=".$POST[job_region;
$where_and = " AND ";
}

$stmt = $select_clause.$where_clause;

amarafzal

thanks

At moment i have this code for my search:

$numresults = mysql_query("select * from $table_name where job_sector = '$POST[job_sector]' AND region = '$POST[region]'"); // the query.

$numresults variable is used for some pagination that i have set up. How can i manipulate the code that you have sent me in to this?

i also have the following select statment as well
$query = "select * from jobs where job_sector = '$POST[job_sector]' AND region = '$POST[region]'";

where and how do i put your code in to the above select statments?

sivasankari_kv

This is how i do ... A very brief description , Hope it helps

function($startrows = 0 , $end_rows = 0)
{
$limit_clause = limit . $startrows.",".$endrows;

$stmt = select * from $table_name where job_sector = '$POST[job_sector]' AND region = '$POST[region]' . $limit_clause;

$resultset = mysql_query($stmt);

}

i hava a php function which returns me the startrows and endrows based on my pagesize(usually 10 , i store it in a constant) , i pass them to the above function and get the result and print them

amarafzal

sorry i am completely confused now,

my pagination is working and really want interfere with that.

the code you gave me to solve my problem how do i put it in to the 2 querys i gave you in my last post

i do appreciate your help, im a newbie!

sivasankari_kv

You are using mysql_query("...") right , before executing the query you are handling the form data ,

$numresults = mysql_query("select * from $table_name where job_sector = '$POST[job_sector]' AND region = '$POST[region]'"); //this is ur query

instead of hard coding the query i assigned it to $stmt in my example and call it this way

$select_clause = "select * from from jobs";

$where_and = " WHERE ";
$where_clause = "";

//these are constraint checking

if ($POST[keyword] )
{
$where_clause = $where_clause . $where_and ."job_title OR job_summary LIKE '%". '$POST[keyword]' ."%";
$where_and = " AND ";
}

if ($POST[job_sector] )
{
$where_clause = $where_clause . $where_and ."sector=".$POST[job_sector];
$where_and = " AND ";
}

if ($POST[job_region )
{
$where_clause = $where_clause . $where_and ."region=".$POST[job_region;
$where_and = " AND ";
}

$stmt = $select_clause.$where_clause;

$numresults = mysql_query($stmt);

I'm sorry if i am not clear

amarafzal

i really appreiciate your help, its nice to know that there are people like you out there

Im getting errors, actually a blank page the whole code i have for the page is:

$db_name = "";
$table_name = "jobs";
$connection =
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

if (!($_GET['limit'])){
$limit = 500;} // Default results per-page.

if (!($_GET['page'])){
$page = 0;} // Default page value.

// select query
$select_clause = "select * from $table_name";

$where_and = " WHERE ";
$where_clause = "";

//these are constraint checking

if ($POST[keyword] )
{
$where_clause = $where_clause . $where_and ."job_title OR job_summary LIKE '%". '$POST[keyword]' ."%";
$where_and = " AND ";
}

if ($POST[job_sector] )
{
$where_clause = $where_clause . $where_and ."job_sector=".$POST[job_sector];
$where_and = " AND ";
}

if ($POST[job_region )
{
$where_clause = $where_clause . $where_and ."job_region=".$POST[job_region];
$where_and = " AND ";
}

$stmt = $select_clause.$where_clause;

$numresults = mysql_query($stmt); // the query.

$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows % $limit) {
$pages++;} // has remainder so add one page

$current = ($page/$limit) + 1; // Current page number.

if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

$job_id = $_GET['job_id'];
while ($row = mysql_fetch_array($result))
{
$job_id = $row['job_id'];
$contact_name = stripslashes($row['contact_name']);
$contact_phone = $row['contact_phone'];
$date_posted = $row['date_posted'];
$job_title = stripslashes($row['job_title']);
$job_summary = stripslashes($row['job_summary']);
$short_summary = substr($job_summary,0,150);
$location = stripslashes($row['location']);
$job_status = stripslashes($row['job_status']);
$salary = $row['salary'];
$career_level = stripslashes($row['career_level']);

$display_block .= "
<table width=75% height=35 cellpadding=0 cellspacing=1>
<tr bgcolor=#FFEBD7>
<td width=54% align=left valign=top><a href=latest_jobs_detail.php?job_id=$job_id>$job_title</a><br>$short_summary.....<a href=latest_jobs_detail.php?job_id=$job_id>[More]</a><br>
<font size=2><strong>Job type:</strong> $job_status<br>
<strong> Salary:</strong>$salary</font>
<td width=15% align=left valign=top>$location
</tr>
</table>";

}
?>

Can you see where im going wrong?

amarafzal

ive it to work with the following code but im getting this error:

Unknown column 'Office' in 'where clause'

code so far:

// select query
$select_clause = "select * from $table_name";

$where_and = " WHERE ";
$where_clause = "";

//these are constraint checking

if ($POST[keyword] )
{
$where_clause = $where_clause . $where_and ."job_title OR job_summary LIKE '%". '$POST[keyword]' ."%";
$where_and = " AND ";
}

if ($POST[job_sector] )
{
$where_clause = $where_clause . $where_and ."job_sector=".$POST[job_sector];
$where_and = " AND ";
}

if ($POST[region] )
{
$where_clause = $where_clause . $where_and ."region=".$POST[region];
$where_and = " AND ";
}

$stmt = $select_clause.$where_clause;

$numresults = mysql_query($stmt); // the query.

$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows % $limit) {
$pages++;} // has remainder so add one page

$current = ($page/$limit) + 1; // Current page number.

if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

$job_id = $_GET['job_id'];
$result = mysql_query($stmt) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
$job_id = $row['job_id'];
$contact_name = stripslashes($row['contact_name']);
$contact_phone = $row['contact_phone'];
$date_posted = $row['date_posted'];
$job_title = stripslashes($row['job_title']);
$job_summary = stripslashes($row['job_summary']);
$short_summary = substr($job_summary,0,150);
$location = stripslashes($row['location']);
$job_status = stripslashes($row['job_status']);
$salary = $row['salary'];
$career_level = stripslashes($row['career_level']);

}
?>

sivasankari_kv

Is this the complete code ?

while ($row = mysql_fetch_array($result))

//$result -> where does it come from , u haven't used $result
anywhere in program

sivasankari_kv

ignore the last message it wasn't for u

there is two mysql_query($stmt) i dunno why for u are executing the same query twice , did u try printing $stmt

amarafzal

this is the complete working code that i was using before, ive just tried to amend the 2nd bit that is red with your code:

if (!($_GET['limit'])){
$limit = 500;} // Default results per-page.

if (!($_GET['page'])){
$page = 0;} // Default page value.

[COLOR=red]$numresults = mysql_query("select * from $table_name where job_sector = '$_POST[job_sector]' AND region = '$_POST[region]'"); // the query. [/COLOR]

$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows % $limit) {
$pages++;} // has remainder so add one page

$current = ($page/$limit) + 1; // Current page number.

if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

$job_id = $GET['job_id'];
$query = "select * from jobs where job_sector = '$POST[job_sector]' AND region = '$_POST[region]'";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
$job_id = $row['job_id'];
$contact_name = stripslashes($row['contact_name']);
$contact_phone = $row['contact_phone'];
$date_posted = $row['date_posted'];
$job_title = stripslashes($row['job_title']);
$job_summary = stripslashes($row['job_summary']);
$short_summary = substr($job_summary,0,150);
$location = stripslashes($row['location']);
$job_status = stripslashes($row['job_status']);
$salary = $row['salary'];
$career_level = stripslashes($row['career_level']);

}
?>

sivasankari_kv

I tried it with my db this works fine

<?
function LoadDiscountInfo( )
7 {
8 $select_count_clause = "SELECT * ";
9
10 //initialize where clause
11 $where_and = " WHERE ";
12 $where_clause = "";
13 $from_clause = " FROM TBL_CONTACT_MASTER ";
12 $where_clause = "";
13 $from_clause = " FROM TBL_CONTACT_MASTER ";
14 $stmt = $select_count_clause . $from_clause . $where_clause;
15
16 echo $stmt;
17 // ShowDebug("",$stmt,"");
18 // execute
9
10 //initialize where clause
11 $where_and = " WHERE ";
12 $where_clause = "";
13 $from_clause = " FROM TBL_CONTACT_MASTER ";
14 $stmt = $select_count_clause . $from_clause . $where_clause;
15
16 echo $stmt;
17 ShowDebug("",$stmt,"");
18 // execute
19 $rs = ExecQuery($stmt, _RESPONSE_NORMAL,$not_required,$err_msg) or die ("aiyoo");
20 // $rs = mysql_query($stmt) or die ("aiyoo");
21 $limit = 500;
22
23 $page = 0;
24
25 $numrows = mysql_num_rows($rs); // Number of rows returned from above query.
26
27 $pages = intval($numrows/$limit); // Number of results pages.
28
29 // $pages now contains int of pages, unless there is a remainder from division.
30
31 if ($numrows % $limit) {
32 $pages++;} // has remainder so add one page
33
34 $current = ($page/$limit) + 1; // Current page number.
35
36 if (($pages < 1) || ($pages == 0)) {
37 $total = 1;} // If $pages is less than one or equal to 0, total pages is 1.
38
39 else {
40 $total = $pages;} // Else total pages is $pages value.
41
42 $first = $page + 1; // The first result.
43
44 if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
45 $last = $page + $limit;} //If not last results page, last result equals $page plus $limit.
46
47 else{
48 $last = $numrows;} // If last results page, last result equals total number of results.
49
50 while ($row = mysql_fetch_array($rs))
51 {
52 $job_id = $row['FLD_CONTACT_ID'];
53
54 $display_block .= "
55 <table width=75% height=35 cellpadding=0 cellspacing=1>
56 <tr bgcolor=#FFEBD7>
57 <td>$job_id</td>
58 </tr>
59 </table>";
60 }
61 return $display_block;
62 }
63
64 echo LoadDiscountInfo();
65 ?>

amarafzal

that code is great but it dont make no sense

The followng doesnt produce any errors only thing is it doesnt return any results?

// select query
$select_clause = "select * from $table_name";

$where_and = " WHERE ";
$where_clause = "";

//these are constraint checking

if ($POST[keyword] )
{
$where_clause = $where_clause . $where_and ."job_title OR job_summary LIKE '%". '$POST[keyword]' ."%";
$where_and = " AND ";
}

if ($POST[job_sector] )
{
$where_clause = $where_clause . $where_and ."job_sector=".$POST[job_sector];
$where_and = " AND ";
}

if ($POST[region] )
{
$where_clause = $where_clause . $where_and ."region=".$POST[region];
$where_and = " AND ";
}

$stmt = $select_clause.$where_clause;

$numresults = mysql_query($stmt); // the query.

$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows % $limit) {
$pages++;} // has remainder so add one page

$current = ($page/$limit) + 1; // Current page number.

if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

$job_id = $_GET['job_id'];
while ($row = mysql_fetch_array($numrows))
{
$job_id = $row['job_id'];
$contact_name = stripslashes($row['contact_name']);
$contact_phone = $row['contact_phone'];
$date_posted = $row['date_posted'];
$job_title = stripslashes($row['job_title']);
$job_summary = stripslashes($row['job_summary']);
$short_summary = substr($job_summary,0,150);
$location = stripslashes($row['location']);
$job_status = stripslashes($row['job_status']);
$salary = $row['salary'];
$career_level = stripslashes($row['career_level']);

}
?>

sdjensen

After the line

$stmt = $select_clause.$where_clause;

Try

print $stmt;

to see what the select statement looks like.

and

Try changing the line

$where_clause = $where_clause . $where_and ."job_title OR job_summary LIKE '%". '$_POST[keyword]' ."%";

$where_clause = $where_clause . $where_and ."job_title OR job_summary LIKE '%". $_POST[keyword] ."%'";

amarafzal

the following is outputed according to what i select:

select * from jobs WHERE job_title OR job_summary LIKE '%web%' AND job_sector=IT AND region=North East

but no results are displayed

????????????

amarafzal

Still got that problem, below is the major part of the working of the page:

<?
\ connection info goes first

if (!($_GET['limit'])){
$limit = 500;} // Default results per-page.

if (!($_GET['page'])){
$page = 0;} // Default page value.

// select query
$select_clause = "select * from $table_name";

$where_and = " WHERE ";
$where_clause = "";

//these are constraint checking

if ($POST[keyword] )
{
$where_clause = $where_clause . $where_and ."job_title OR job_summary LIKE '%". $POST[keyword] ."%'";
$where_and = " AND ";
}

if ($POST[job_sector] )
{
$where_clause = $where_clause . $where_and ."job_sector=".$POST[job_sector];
$where_and = " AND ";
}

if ($POST[region] )
{
$where_clause = $where_clause . $where_and ."region=".$POST[region];
$where_and = " AND ";
}

$stmt = $select_clause.$where_clause;

$numresults = mysql_query($stmt); // the query.

$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.

$pages = intval($numrows/$limit); // Number of results pages.

// $pages now contains int of pages, unless there is a remainder from division.

if ($numrows % $limit) {
$pages++;} // has remainder so add one page

$current = ($page/$limit) + 1; // Current page number.

if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.

else {
$total = $pages;} // Else total pages is $pages value.

$first = $page + 1; // The first result.

if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.

else{
$last = $numrows;} // If last results page, last result equals total number of results.

}
?>

For you to get an idea what im seeing, visit the following link:
http://www.talentrecruit.com/latest_jobs_search_test.php

Can you tell me what im doing wrong? This is really getting on my tits!!!

sivasankari_kv

Where are u printing the $display_block

amarafzal

Later on, just an echo statment.

You know what, that pagination doesnt even work properly shall we just talke it out? because thats whats causing the problem.

Have you got any pagination code similar to one ive got displaying may be we can manipulate the code you gave me in to that?