Searching a MySql database using 5 variables

[hornsby100]

Hi All,

I've been working on a particular piece of code allowing users of my website to be able to search for Jobs using the following variables:
- Search (keyword)
- Candidate_Type (graduate,etc)
- Occupation (field of work)
- Salary
- Location

I have created a database and a table called Jobs and am able to read and write to the MySql DB but am having trouble searching the DB as when i search it just returns a blank page.

Below code, can anyone help me out?


<?php
$Search= $_POST["Search"];
$Candidate_Type= $_POST["Candidate_Type"];
$Occupation= $_POST["Occupation"];
$Salary= $_POST["Salary"];
$Location= $_POST["Location"];

if ($Search == "") {
echo "<p>You forgot to enter what job you are searching for</p>";
exit;
}
if ($Candidate_Type == "") {
echo "<p>You forgot to enter what type of candidate you are</p>";
exit;
}
if ($Occupation == "") {
echo "<p>You forgot to enter which occupation you are looking for</p>";
exit;
}
if ($Salary == "") {
echo "<p>You forgot to enter a salary you desire</p>";
exit;
}
if ($Location == "") {
echo "<p>You forgot to enter where you would like to work</p>";
exit;
}

// Otherwise we connect to our Database
mysql_connect("*********", "*******", "*********") or die(mysql_error());
mysql_select_db("********") or die(mysql_error());


//Now we search for our search term, in the field the user specified
$data = mysql_query("SELECT * FROM Job WHERE upper($field) LIKE'%$find%'");

// check for a search parameter
if (!isset($var))
{
exit;
}

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: ',' . $trimmed . ',' returned zero results</p>";
}

// next determine if s has been passed to script, if not use 0
if (empty($s))
{
$s=0;
}


$result = mysql_query($query) or die("Couldn't execute query");

if($numrows > 1){ $return = "results";}
else{ $return = "result"; }

//And we display the results
while ($r= mysql_fetch_array($result))
{
$Search = $r["Search"];
$Candidate_Type = $r["Candidate_Type"];
$Occupation = $r["Occupation"];
$Salary = $r["Salary"];
$Location = $r["Location"];

$count++ ;
}
?>


Thanks in advance.

[PappaJohn]

First things first - I'd suggest editing your post to remove the database details (db name, user, password). You probably don't want that info publicly viewable.

[hornsby100]

Thanks for your reminder, just changed over.

[PappaJohn]

I don't see where $field is defined in your code:

$data = mysql_query("SELECT * FROM Job WHERE upper($field) LIKE'%$find%'");


It can be helpful (for debugging) to write the query as:

//Now we search for our search term, in the field the user specified
$sql = "SELECT * FROM Job WHERE upper($field) LIKE'%$find%'";
$data = mysql_query($sql) or die(mysql_error());

Now when there's a problem, you can add:

//Now we search for our search term, in the field the user specified
$sql = "SELECT * FROM Job WHERE upper($field) LIKE'%$find%'";
echo $sql;
$data = mysql_query($sql) or die(mysql_error());

This will echo the sql to the page. You can then copy/paste that into phpMyAdmin (or similar tool) and execute the query. Also, the "or die()" can provide some helpful clues when the query fails.

[Fou-Lu]

It can be helpful (for debugging) to write the query as:


I'm going to have to disagree a bit with you on this one. ALWAYS use it :D
If not a die, always handle the possibility for an exception. You don't want processing to happen when nothing is there to process. This can cause semantic errors later in you're processing. I should mention this as well, although or die works, I can't actually find official documentation supporting its usage. Its *should* really just return true, but it doesn't, it returns a resultset if it exists.

Anyway, as PJ pointed out its you're $field that appears to be a part of the problem. You also don't have a $find value (which shouldn't kill it, but it won't find any records either).
This whole section is a mess though:

// check for a search parameter
if (!isset($var))
{
exit;
}

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: ',' . $trimmed . ',' returned zero results</p>";
}

// next determine if s has been passed to script, if not use 0
if (empty($s))
{
$s=0;
}


$result = mysql_query($query) or die("Couldn't execute query");

if($numrows > 1){ $return = "results";}
else{ $return = "result"; }

//And we display the results
while ($r= mysql_fetch_array($result))
{
$Search = $r["Search"];
$Candidate_Type = $r["Candidate_Type"];
$Occupation = $r["Occupation"];
$Salary = $r["Salary"];
$Location = $r["Location"];

$count++ ;
}

The isset($var) check is terminating you're script. You don't have a $var variable.

None of this part succeeds correctly:

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: ',' . $trimmed . ',' returned zero results</p>";
}

No $query variable causes $numresults to fail, causing $numrows to fail. The $numrows == 0 will actually succeed though since 0 is considered false and you're not performing an identical comparison.

Same goes for the entire section below it, no defined variables and query failures.

Gotta fix them up too!

[Fumigator]

The usage of "or die" is explained here, sort of:

http://us.php.net/manual/en/language.operators.logical.php

If you look at Example #1, you see the syntax $f = false or true;. So it's really just that syntax, minus the assignment of the result to any variable. And it just so happens that the expression on the right kills the script dead.

Personally I don't like the syntax and I never use it.

(Sorry to hijack the thread, please continue)

[hornsby100]

Thanks guys and apologies for my belated response.

Comments noted.

Fou-Lu commented "This whole section is a mess though". How should i go about structuring the whole code or would you say its near enough right? I'm relatively new to PHP and haven't come across needing the search a MySql function before so am just finding my feet

Any snippets of code would be of great help.

[Fou-Lu]

Thanks guys and apologies for my belated response.

Comments noted.

Fou-Lu commented "This whole section is a mess though". How should i go about structuring the whole code or would you say its near enough right? I'm relatively new to PHP and haven't come across needing the search a MySql function before so am just finding my feet

Any snippets of code would be of great help.

The problem is that $var, $query and $s never exist so you can't perform any operations using them.

if (!isset($var)) // Or empty($var) but it will trigger an error
{
exit();
}

is true, so the script always terminates at this point. You'll need to initialize these variables:

$var = 'somevalue';
$query = "SELECT id FROM table";
$s = 'somevalue';

From the looks of it, $query is not necessary and you're probably working with the resultset from $data instead. I don't know what $var and $s are representing though, so I can't comment on what their predefined values are supposed to be.

[hornsby100]

Ok, i've had a play about with the code and now have the following:

<?php
$Search= $_POST["Search"];
$Candidate_Type= $_POST["Candidate_Type"];
$Occupation= $_POST["Occupation"];
$Salary= $_POST["Salary"];
$Location= $_POST["Location"];

$username = "********";
$password = "********";
$hostname = "********";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
print "Connected to MySQL<br>";
$selected = mysql_select_db("******",$dbh)
or die("Could not select Database");

$result = mysql_query("SELECT Search,Candidate_Type,Occupation,Salary,Locations FROM Jobs",$dbh);

while ($row = mysql_fetch_array($result)) {

print "Job".$row{'Search'}."
".$row{'Candidate_Type'}."
".$row{'Occupation'}."
".$row{'Salary'}."
".$row{'Locations'}."
<br>";
}
mysql_close($dbh);
?>

The following warning message is:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ************* on line 27 which is the while ($row = mysql_fetch_array($result)) { line.

[Fou-Lu]

Anytime you receive an error like this through PHP and a mysql connection, it simply means that the query returned no recordset (as in, the query has failed). This is why you always need to die or account for you're errors before continuing.

$result = mysql_query("SELECT Search,Candidate_Type,Occupation,Salary,Locations FROM Jobs",$dbh) or
die ('Cannot execute query: ' . mysql_error());
// Or, after a standard non-dying query:
if (false === $result)
{
die('Cannot execute query: ' . mysql_error());
}


These are usually caused by incorrect SQL Syntax or invalid fields. Remember that SQL is case sensitive (which is what causes a lot of the problems), and has a number of reserved words that if used need to be backreferenced with ` marks.
Since you don't appear to have any keywords in use (link here: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html ), I'd say the problem is with you're required field names.

[oesxyl]

Remember that SQL is case sensitive (which is what causes a lot of the problems)
sorry, I disagree, :)
php mysql extension is responsable for identifier case sensitivity not sql.
case sensitivity in mysql depend on other factors:

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

reserved words are case insensitive. I allways use lowercase but I guess that is
true for both php mysql extension and mysql.

best regards