0

I have a large database and its getting to big to find relevant stuff quickly.

I would like the following: search by customer type of function search. (We are a printing company) eg. search Business Cards you get thousands of results from all customers and to hard to find the most recent from a customer.

I'm using LIKE and its pretty good, but for a particular customer they have several names very similar and its bringing up all variations of that customer.

I've tried a few things but nothing is working so far. I have tried SELECT * FROM Jobs WHERE Customer='Customer' but I still get other customers.

Best result would be a select customer from a dropdown the a search. but I can implement custom search for each customer if required.

current query is:

$raw_results = mysql_query("SELECT * FROM Jobs WHERE (`id` LIKE '%".$query."%') OR (`Customer` LIKE 'CUSTOMER NAME') OR (`OrderNumber` LIKE '%".$query."%') OR (`JobDescription` LIKE '%".$query."%') OR (`JobNotes` LIKE '%".$query."%') OR (`Item_1_Name` LIKE '%".$query."%')OR (`Item_2_Name` LIKE '%".$query."%')OR (`Item_3_Name` LIKE '%".$query."%') OR (`Item_4_Name` LIKE '%".$query."%') OR (`Item_5_Name` LIKE '%".$query."%') OR (`Item_6_Name` LIKE '%".$query."%') OR (`Item_7_Name` LIKE '%".$query."%') OR (`Item_8_Name` LIKE '%".$query."%') OR (`Item_9_Name` LIKE '%".$query."%') OR (`Item_10_Name` LIKE '%".$query."%')ORDER BY id DESC") or die(mysql_error());
Dharman
  • 30,962
  • 25
  • 85
  • 135
user1825768
  • 9
  • 1
  • why not list down all the customer name with `DISTINCT` ? this way, u only have unique customer name – zimorok Feb 16 '22 at 07:00
  • 1
    Please note that all [mysql_* functions](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) are deprecated and even are removed from future PHP releases. You are also wide open for [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) – DarkBee Feb 16 '22 at 07:12
  • @zimorok customers names are not unique in any way, two people can be called exactly the same – DarkBee Feb 16 '22 at 07:51
  • **Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0 (2013), and has been removed as of PHP 7.0.0 (2015). Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. – Dharman Feb 16 '22 at 10:20

1 Answers1

0

First, I think you need to read about full text search in MySQL. You can merge several field and using one query for finding needed information.

Or, you can just set index for each field. I recommend check your query with EXPLAIN SELECT ... before making index. In this case is rude practice, but easy.

But the best solution would be to try to change the structure of the tables, if possible in your case. Item_X_Name must be placed in foreign table and linked with foreign key.

P.S. mysql_* function is deprecated. Try to use PDO, or more flexible ORM-libraries

dwdraugr
  • 106
  • 6