We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to search my whole database not just table?

FairyBusiness
on
Medium Priority
387 Views
Last Modified: 2012-06-27
Hi, I am creating a search bar and I want it to search my whole database so I wrote my query as follows:

$query = "SELECT * FROM auroriella LIKE '%$find%'";

but its not working.  Anyone know how to do this?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you cannot search, with sql, in a full database. you can only search for every table individually.
also, you cannot search for all columns at one, but you have to repeat the condition (LIKE) for every column.

normally, you would implement this by doing
1) a UNION on all relevant tables to be searched for
2) putting a full text index on all relevant columns (for all tables involved) to make sure your query returns in a reasonable time
3) change the LIKE into a MATCH .. .AGAINST syntax to use the full-text index

hope this helps

Author

Commented:
ok, I just wrote this:

	$query = "SELECT * FROM bracelets"; 
	$query .= "UNION SELECT * FROM necklaces"; 
	$query .= "UNION SELECT * FROM earrings"; 
	$query .= "WHERE MATCH * AGAINST '%$find%' "; 
	$result = mysql_query($query, $conn);

Open in new window


Am I on the right track here?  (bracelets, necklaces, and earrings are the tables I want to be searched)
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you need to repeat the WHERE condition for every table:
$query = "SELECT * FROM bracelets"; 
        $query .= "WHERE MATCH * AGAINST '%$find%' "; 
        $query .= "UNION SELECT * FROM necklaces"; 
        $query .= "WHERE MATCH * AGAINST '%$find%' "; 
        $query .= "UNION SELECT * FROM earrings"; 
        $query .= "WHERE MATCH * AGAINST '%$find%' "; 
        $result = mysql_query($query, $conn);

Open in new window

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
It doesn't like the necklaces apparently lol

Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* AGAINST '%BLUE%' UNION SELECT * FROM necklaces WHERE MATCH * AGAINST '%BLUE%' ' at line 1
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I do have a table called products which has the names of all those tables.

I changed my code to this:

        $query = "SELECT * FROM bracelets "; 
        $query .= "WHERE MATCH * AGAINST '$find' "; 
        $query .= "UNION ALL SELECT * FROM necklaces "; 
        $query .= "WHERE MATCH * AGAINST '$find' "; 
        $query .= "UNION ALL SELECT * FROM earrings "; 
        $query .= "WHERE MATCH * AGAINST '$find' "; 

Open in new window


But I got this error message: (I searched for blue)

Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* AGAINST 'BLUE' UNION ALL SELECT * FROM necklaces WHERE MATCH * AGAINST 'BLUE' ' at line 1

Author

Commented:
Ok, I changed it:

		$query = "SELECT * FROM bracelets "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST '$find' "; 
        $query .= "UNION ALL SELECT * FROM necklaces "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST '$find' "; 
        $query .= "UNION ALL SELECT * FROM earrings "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST '$find' "; 

Open in new window


but its still screaming at me:

Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''BLUE' UNION ALL SELECT * FROM necklaces WHERE MATCH (name, content, color) AGAI' at line 1

Author

Commented:
Ok, I finally got a different error message:

Query failed: Can't find FULLTEXT index matching the column list

I searched just one table for simplicity:

$query = "SELECT * FROM bracelets "; 
 $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') "; 

Open in new window


How do make my fields FULL TEXT indexes??
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>How do make my fields FULL TEXT indexes??
by creating a full text index on the table(s)
http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Author

Commented:
I am still getting the same error message even though I altered my table columns

ALTER TABLE bracelets ADD FULLTEXT (name)

Author

Commented:
Even in my phpmyadmin it lists them as FULL TEXT columns:

content       FULLTEXT       
color       FULLTEXT       
name       FULLTEXT

What else can I do?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
checklist:
* full text index added on all 3 tables, each containing the 3 columns
* the query syntax is validated to run on each table individually
* you post the most recent SQL you tried, along with the error message you get
 ...

Author

Commented:
Well, I am just searching the bracelet table for now.  I commented the rest outl

	$query = "SELECT * FROM bracelets "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') "; 
        /*$query .= "UNION ALL SELECT * FROM necklaces "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST '$find' "; 
        $query .= "UNION ALL SELECT * FROM earrings "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST '$find' "; */
        $result = mysql_query($query, $conn);	$result = mysql_query($query, $conn);
	confirm_query($result);

Open in new window


Error message:

Query failed: Can't find FULLTEXT index matching the column list

http://www.auroriella.com/results.php
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Oh, well maybe I don't have that.

How do I create one full text index with 3 columns??

something like this:

ALTER TABLE bracelets ADD FULLTEXT (name, content, color)

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you might want to find the syntax in the above link I posted ...

Author

Commented:
Ok, I fixed it and it works when I search one or two tables, but once I search all three it sends an error message.  Can I use UNION ALL more than once?

      $query = "SELECT * FROM bracelets ";
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT * FROM necklaces ";
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT * FROM earrings ";
        $query .= "WHERE MATCH (name, content, color) AGAINST '$find' ";

Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''PURPLE'' at line 1

Author

Commented:
Nope, it was syntax error not the UNION ALL.  Thanks for all of your help!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.