?
Solved

How to search my whole database not just table?

Posted on 2011-04-27
19
Medium Priority
?
372 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?
0
Comment
Question by:FairyBusiness
  • 11
  • 8
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480692
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
0
 

Author Comment

by:FairyBusiness
ID: 35480716
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)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480724
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

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35480727
note that SELECT * will only work with UNION (and you should also use UNION ALL to avoid the implicit distinct) is the column list is the same for (all) tables involved.

also, remove the % from the AGAINST condition ...

anyhow: reading the query, it seems you shall actually have 1 table with a column indicating if the object is a necklace, earring, bracelet etc .. instead of having 3 tables ...
0
 

Author Comment

by:FairyBusiness
ID: 35480729
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
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35480740
it's actually MATCH(col1, col2 ...) AGAINST ('BLUE') ... ad not MATCH * AGAINST '%BLUE%'

http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html
0
 

Author Comment

by:FairyBusiness
ID: 35480741
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
0
 

Author Comment

by:FairyBusiness
ID: 35480756
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
0
 

Author Comment

by:FairyBusiness
ID: 35480776
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??
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480788
>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
0
 

Author Comment

by:FairyBusiness
ID: 35480799
I am still getting the same error message even though I altered my table columns

ALTER TABLE bracelets ADD FULLTEXT (name)
0
 

Author Comment

by:FairyBusiness
ID: 35480840
Even in my phpmyadmin it lists them as FULL TEXT columns:

content       FULLTEXT       
color       FULLTEXT       
name       FULLTEXT

What else can I do?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480925
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
 ...
0
 

Author Comment

by:FairyBusiness
ID: 35480933
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
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35480952
you need 1 fulltext index with the 3 columns ... not 3 indexes with each 1 column  ...
0
 

Author Comment

by:FairyBusiness
ID: 35480969
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)

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480975
you might want to find the syntax in the above link I posted ...
0
 

Author Comment

by:FairyBusiness
ID: 35481066
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
0
 

Author Closing Comment

by:FairyBusiness
ID: 35481096
Nope, it was syntax error not the UNION ALL.  Thanks for all of your help!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question