Need help adjusting PHP/AJAX/JQuery script

[webguync]

I need some help adjust a search form that uses PHP/MySQL/AJAX/JQuery. I am not sure what area needs to be adjusted, but I think it's in the Query. The search from searches across the MySQL database of sports team info and works fine. the only problem is in the return of one key word when I needed the exact match of two or more words. For instance you type in 'Miami Heat' and the results returned are for 'Miami Heat' and 'Miami Dolphins', so anything with the key word Miami is returned.

 

Here is the PHP code

 

<?php

$dbhost = "localhost";
$dbuser = "username";
$dbpass = "pw";
$dbname = "db_name";

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);

if(isset($_GET['query'])) { $query = $_GET['query']; } else { $query = ""; }
if(isset($_GET['type'])) { $type = $_GET['type']; } else { $query = "count"; }

if($type == "count")
{
$sql = mysql_query("SELECT count(category_id) 
FROM players

							WHERE MATCH(sport,first_name,last_name,MVP,team)
							AGAINST('$query' IN BOOLEAN MODE)");
$total = mysql_fetch_array($sql);
$num = $total[0];

echo $num;

}



if($type == "results")
{
$sql = mysql_query("SELECT sport,first_name,last_name,MVP,team
							FROM players 
							WHERE MATCH(sport,first_name,last_name,MVP,team)
							AGAINST('$query' IN BOOLEAN MODE)");
echo "<table>";
echo "<tr>";
echo "<th>First Name</th><th>Last Name</th><th>Team</th><th>MVP</th>";

while($array = mysql_fetch_array($sql)) {

	$sport = $array['sport'];
	$first_name = $array['first_name'];
	$last_name = $array['last_name'];
	$team = $array['team'];
	$MVP = $array['MVP'];
			echo "<tr><td>" . $first_name . "</td>\n<td>". $last_name ."</td>\n<td>" .$team."</td>\n<td> . $MVP .</td>\n</tr>\n";
    
  

}
echo "</table>";=\
}


mysql_close($conn);

?>

 

 

[bspace]

try something like

 

WHERE MATCH(sport,first_name,last_name,MVP,team)

AGAINST('$query' IN BOOLEAN MODE

AND concat(sport,first_name,last_name,MVP,team) like %$query$

 

point being that although the LIKE is much slower than MATCH

it's only applied to the lines that MATCH finds, not the whole set

 

course you may need to do some converting to lower case, like 

 

AND lower(concat(sport,first_name,last_name,MVP,team)) like %lower($query)$

[webguync]

thanks for the reply I am getting an error with my Query now though.

 

<?php

$dbhost = "localhost";
$dbuser = "webguync";
$dbpass = "Phoenix90";
$dbname = "test";

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);

if(isset($_GET['query'])) { $query = $_GET['query']; } else { $query = ""; }
if(isset($_GET['type'])) { $type = $_GET['type']; } else { $query = "count"; }

if($type == "count")
{
$sql = mysql_query("SELECT count(category_id) 
FROM players

							WHERE MATCH(sport,first_name,last_name,MVP,team)
							AGAINST('$query' IN BOOLEAN MODE) AND lower(concat(sport,first_name,last_name,MVP,team)) like %lower($query)$");
$total = mysql_fetch_array($sql);
$num = $total[0];

echo $num;

}



if($type == "results")
{
$sql = mysql_query("SELECT sport,first_name,last_name,MVP,team
							FROM players 
							WHERE MATCH(sport,first_name,last_name,MVP,team)
							AGAINST('$query' IN BOOLEAN MODE)
							AND lower(concat(sport,first_name,last_name,MVP,team)) like %lower($query)$
							");
echo "<table>";
echo "<tr>";
echo "<th>First Name</th><th>Last Name</th><th>Team</th><th>MVP</th>";

while($row = mysql_fetch_assoc($sql))
{
    $class    = ($row['MVP']) ? 'class="MVP"' : '';
    $mvpText = ($row['MVP']) ? 'MVP' : '';
    echo "<tr>\n";
    echo "  <td {$class}>{$row['first_name']}</td>\n";
    echo "  <td {$class}>{$row['last_name']}</td>\n";
    echo "  <td {$class}>{$row['team']}</td>\n";
    echo "  <td {$class}>{$mvpText}</td>\n";
    echo "</tr>\n";
}
echo "</table>";
}
echo $sql;

mysql_close($conn);

?>

 

The error is "Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in search2.php on line 42

[Pikachu2000]

That means your query is failing. I know you know how to use mysql_error to find out why.

[webguync]

I am trying to get the error

if($type == "results")
{
$sql = mysql_query("SELECT sport,first_name,last_name,MVP,team
							FROM players 
							WHERE MATCH(sport,first_name,last_name,MVP,team)
							AGAINST('$query' IN BOOLEAN MODE)AND concat(sport,first_name,last_name,MVP,team) like '%$query'");
echo mysql_error($sql);				

 

the mysql_error(): supplied resource is not a valid MySQL-Link resource in C:\xampp\htdocs\Testing\DB_Search\search2.php on line 36