More efficient query in mysql

Question

I want to know how do they do a query with only one textbox in the page, but the current text in the text box can be used as a parameter for the query. I'm currently using the query below but I don't think I'm getting the desired results, is there anything wrong with my query?

$result1=query_database("SELECT * FROM prod_table WHERE CATEGORY LIKE '$cuts%' OR PRODUCT LIKE '$cuts%' OR P_DESC LIKE '$cuts%' ", "onstor", $link);

?>


<?php

if(mysql_num_rows($result1)==0){

}else{

How to do this kind of query better.

What results are you getting vs what are you expecting? Also consider using binds since doing straight variable substation is very dangerous and buggy — Andrew White
– Andrew White, Commented Dec 10, 2010 at 13:45
What result do you get and what result do you expect to get? — Matt Asbury
– Matt Asbury, Commented Dec 10, 2010 at 13:45
I should get all the results beginning with 'a' when I type in 'a'. But I think I'm only getting one or two of them. — Wern Ancheta
– Wern Ancheta, Commented Dec 12, 2010 at 1:22

Shakti Singh · Accepted Answer · 2010-12-10 13:38:32Z

1

if query_database is not defined by you

mysql_query("SELECT * FROM prod_table WHERE CATEGORY LIKE '$cuts%' OR PRODUCT LIKE '$cuts%' OR P_DESC LIKE '$cuts%' ", "onstor", $link);

answered Dec 10, 2010 at 13:38

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wern Ancheta Over a year ago

I defined query_database on the connection file.

Pradeep Singh · Accepted Answer · 2010-12-10 14:09:32Z

0

use % before $cut and escape this query is fine

 $result1=query_database("SELECT * FROM prod_table WHERE CATEGORY LIKE '%$cuts%' OR PRODUCT LIKE '%$cuts%' OR P_DESC LIKE '%$cuts%' ", "onstor", $link);

answered Dec 10, 2010 at 14:09

Pradeep Singh

3,6323 gold badges33 silver badges42 bronze badges

Comments

Jeroen · Accepted Answer · 2019-10-31 11:17:27Z

0

Add indexes for each of the 3 fields, CATEGORY,PRODUCT,P_DESC and it should run much faster.

Are you sure that LIKE '$cuts%' is correct? I believe that it should be LIKE '%$cuts%'. In the way that you have written it, it will only find the products which start with the value of the $cuts

edited Oct 31, 2019 at 11:17

Jeroen

1,1641 gold badge12 silver badges24 bronze badges

answered Dec 10, 2010 at 14:01

mazgalici

6066 silver badges10 bronze badges

Collectives™ on Stack Overflow

More efficient query in mysql

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related