Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Concat in Select for Search Return

I am working on an auto suggest and I would like people to be able to either type in a product number or product name.  So for example

If they start to type 1234 it will start to show the products

12345: The Product 1
12346: The Product 2

Or if they type in Product
It would show the same results


So how can i can I concatenate the product id and product name in a query and have it format like that, even with the colon in it?
0
theideabulb
Asked:
theideabulb
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the problem?
* building the query?
* shoing the data
* running the query as users type?
please clarify...

in mysql, to concat, it would be:
 select concat(product_id, ': ' , product_name from products where product_id like '%$user_input%' or product_name like '%$user_input%'

Open in new window

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

hope this helps
0
 
theideabulbAuthor Commented:
if i am doing an autosuggest, would i still need to do the two LIKE statements, if you are combining them and use an alias

 select concat(product_id, ': ' , product_name as theProduct

could you just search it as one string?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could. ... but it would be as inefficient as doing the "or" as I showed (because of the LIKE '% ... %' part) ...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
theideabulbAuthor Commented:
so this wouldn't be a good way to do it?

SELECT set_id, CONCAT(product_number,': ',product_name) as theProduct FROM products
having lower(theProduct) like  '%1234%'
0
 
theideabulbAuthor Commented:
Thank you for your help.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from what I know, you could not even do that in terms of syntax (you cannot use the alias except in the ORDER BY)
also, using HAVING only makes sense in GROUP BY queries ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now