MySQL Simple Database Search

[phpnstuff]

If I will search by a code or the agency name, it finds records easy and
display the results, but if I search any words they have in their description, it won't display any results, am i doing something wrong?

This is how its in the database:

agency_code: 1234
name: xyz agency
description: blah blah we are xyz agency that specializes in child abuse.

If I search for 1234 it display their info in the results, if I search for xyz agency it works also, but if I put in "child abuse" it won't find that data in the description of the database and displays no records.



<?php

include '../includes/mysql/sql_connection.php';

//Select Database
$dbname = "cap";

$dbconn = mysql_connect ( $dbhost, $dbuser, $dbpass );
mysql_select_db( $dbname, $dbconn );

//Remove Case-Sensitive
$q = strtoupper($q);

//Remove Common Words
$q = str_replace('AND', '', $q);
$q = str_replace('&', '', $q);
$q = str_replace('OR', '', $q);
$q = str_replace('IF', '', $q);
$q = str_replace('OF', '', $q);

//Seperate Words - WORD|WORD|WORD
$q = strtoupper(preg_replace('/\s+/', '|', trim($q)));

//Keep Symbols
$regexp = "REGEXP '[[:<:]]($q)[[:>:]]'";

$result = mysql_query("SELECT COUNT(*) FROM `company_information` WHERE UPPER(`name`) $regexp" . " OR UPPER(`description`) $regexp" . " OR UPPER(`agency_code`) $regexp", $dbconn);

$record_count = mysql_result($result, 0);

if($record_count > "0") {

//Execute The Data
$query = "SELECT * FROM `company_information` WHERE UPPER(`description`) $regexp" . " OR UPPER(`name`) $regexp" . " OR UPPER(`agency_code`) $regexp";
$result = mysql_query( $query ) or die( mysql_error() );
while($record = mysql_fetch_assoc( $result ))
{

echo" <tr>\n";
echo" <td>\n";
echo " <a href=\"../community_impact/url.php?loc=certified_agency_partners#".$record['agency_code']."\" onMouseOver=\"(window.status='".str_replace("'", "\'", $record['name'])."'); return true\" class=\"content_body_link\">".$record['name']."</a></li>\n";
echo" </td>\n";
echo" </tr>\n";

}

} else {

echo "no records found";

}

?>

[chazzy]

You have a lot of errors in your code. for one, i'm not even sure how you're able to get any results as that doesn't look like valid SQL. you have this line: [php]$dbname= cap;[/code] that isn't quoted, not sure how it's working but i've seen something like that happen. your column names aren't consistent. you have "agency_name" and "agency_description" in your post, but your code is only "name" and "description" so which is it?

[Webnerd]

You also have your "OR nested agains a quote so your SQL would look like

UPPER(`description`) REGEXP '[[:<:]]($q)[[:>:]]'OR UPPER

[phpnstuff]

include '../includes/mysql/sql_connection.php';



<?php

$dbuser = "username";
$dbpass = "password";
$dbhost = "localhost";

?>



I use the master sql connection to connect to our database...then define the table within the php file.

I'm not following WebNerd...

[chazzy]

What does that have to do with anything? We didn't question your connection.

[phpnstuff]

Yes chazzy, I was inconsistent in my original description, but I updated it to make more sense, sorry.

[Webnerd]

When you create your SQL, you are appending each REGEXP instance into the SQL command. Since you have no space before the "OR, it will concatenate your SQL in a single line which can cause an error. so, when your query executes, it looks like this:

SELECT COUNT(*) FROM `company_information` WHERE UPPER(`name`) REGEXP '[[:<:]](searchdata)[[:>:]]'OR UPPER(`description`) REGEXP '[[:<:]](searchdata)[[:>:]]'OR UPPER(`agency_code`) REGEXP '[[:<:]](searchdata)[[:>:]]'

Add a space before the "OR --> " OR

And also:

$dbname = cap;

is incorrect. With strict error reporting on, "cap" will be assumed a "constant". You need to surround it in quotes

$dbname = "cap";

[phpnstuff]

So like this?



$result = mysql_query("SELECT COUNT(*) FROM `company_information` WHERE UPPER(`name`) $regexp" . " OR UPPER(`description`) $regexp" . " OR UPPER(`agency_code`) $regexp", $dbconn);

[Webnerd]

Your SQL should look like:

SELECT COUNT(*) FROM `company_information` WHERE UPPER(`name`) REGEXP '[[:<:]](searchdata)[[:>:]]' OR UPPER(`description`) REGEXP '[[:<:]](searchdata)[[:>:]]' OR UPPER(`agency_code`) REGEXP '[[:<:]](searchdata)[[:>:]]'

[phpnstuff]

When i added that code it display no results now.

[Webnerd]

You have 2 OR clauses don't you?



$result = mysql_query("SELECT COUNT(*) FROM `company_information` WHERE UPPER(`name`) $regexp OR UPPER(`description`) $regexp OR UPPER(`agency_code`) $regexp", $dbconn);

[phpnstuff]

Do more than 2 or clauses no work, i presumed they did?

[Webnerd]

You keep changing your first post so I can't refer to what you originally had. Running COUNT(*) will always only return a single row.

$record_count = mysql_result($result, 0);

should be

$record_count = mysql_result($result, 0, 0);

As you want the value of the first column of the first row, not just the first row:


mysql_result ( resource result, int row [, mixed field] )