Query not working

[n1concepts]

Can someone review this piece of code and advise what's missing that's preventing the SQL query from working?

Issue: no results being echoed out from $results.

 

Note: I confirmed the db connection  established (only the query not working - no results pulled from db and displayed via echo statements).

 

$db = mysql_connect($host,$user,$pw)
        or die("Cannot connect to MySQL.");

mysql_select_db($database,$db)
        or die("Cannot connect to database.");

echo "Success!  Connected to database ".$database."<br /><br />";

// $jsearch is variable sent in from form "title" name
$jsearch = $_POST['jobsearch'];

// I confirmed that all the field names are correct

  //-query  the database table
  $sql="SELECT id,title,details,status FROM jobs WHERE title = '$jsearch';";
  //-run  the query against the mysql query function
  $result=mysql_query($sql) or die("<br>Query string: $result<br>Returned error: " . mysql_error() );
  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($result)){
          $title  =$row['title'];
	  echo title."<br />";
          $details=$row['details'];
	  echo $details."<br />";
	  $status=$row['status'];
	  echo $status."<br />";
          $ID=$row['id'];

[Maq]

Please tell us what make you think it doesn't work.

[n1concepts]

I have acouple of entries in the "jobs" table and I'm performing the search - i.e. "Customer Service Agents" - and no results being displayed from the script.

 

I posted the script here as I've racked my brain looking fo syntax errors but didn't find any...

So are you saying the code looks good to you?

 

I'm just trying to isolate the problem - thx

[n1concepts]

Never mind - I found the problem.

It wasn't the script but the form.

[QuickOldCar]

I added a closing statement to the while loop, not sure if this was your complete code or not.

 

$db = mysql_connect($host,$user,$pw)
        or die("Cannot connect to MySQL.");

mysql_select_db($database,$db)
        or die("Cannot connect to database.");

echo "Success!  Connected to database ".$database."<br /><br />";

// $jsearch is variable sent in from form "title" name
$jsearch = $_POST['jobsearch'];

// I confirmed that all the field names are correct

  //-query  the database table
  $sql="SELECT id,title,details,status FROM jobs WHERE title = '$jsearch';";
  //-run  the query against the mysql query function
  $result=mysql_query($sql) or die("<br>Query string: $result<br>Returned error: " . mysql_error() );
  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($result)){
          $title  =$row['title'];





  echo title."<br />";
          $details=$row['details'];





  echo $details."<br />";





  $status=$row['status'];





  echo $status."<br />";
          $ID=$row['id'];
}

[PFMaBiSmAd]

From my signature -

Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

[n1concepts]

Hi,

 

My original problem is resolved - the query works now.

However, I need to figure out the best way to perforrm the query based on the captured $jsearch field.

 

For example, If the string "Customer Service Rep" is defined for $jsearch, then the query should search for any of the three words to match the title db field. I thought about STRTOK() function but wanted to know if anyone had a better suggestion.

 

Any input much appreciated.

[n1concepts]

Ok, I went a different direction and used the EXPLODE functiont to break apart the string to search on the individual words (1st one for now...) See code below:

 

// Break up string to search individual words
$search_array = explode(" ", $jsearch);
$word = $search_array[0];

  //-query  the database table
  $sql="SELECT id,title,details,cat FROM jobs WHERE title LIKE '$word%';";

 

My issue now is that I'm not matching on certain words where Case (upper or lower) between the $jsearch variable and what's defined in the table.

 

I read about BINARY option but need some guidance on implementing this feature in the search query.

 

Again, I just want to be able to have any of the words - found in $jsearch  which is passed to the script from a form - parsed and if ANY of the words match ANY word within that "jobs" table, that record is displayed.

[Maq]

Check out full-text searching - http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html