Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Concat in Select for Search Return

Posted on 2011-03-13
6
Medium Priority
?
288 Views
Last Modified: 2012-06-22
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
Comment
Question by:theideabulb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35121422
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
 

Author Comment

by:theideabulb
ID: 35121426
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35121443
you could. ... but it would be as inefficient as doing the "or" as I showed (because of the LIKE '% ... %' part) ...
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:theideabulb
ID: 35121444
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
 

Author Closing Comment

by:theideabulb
ID: 35121457
Thank you for your help.
0
 
LVL 143

Expert Comment

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

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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

722 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