Solved

Pass through a wildcard value to Mysql

Posted on 2008-10-16
7
721 Views
Last Modified: 2013-11-13
Hi, i have a form with about 7 text input boxes and would like to query the database, 9 times out of 10 users will only want to input values for 2 of the boxes but theoretically they could put inputs into each box and i would have to send a long query string to the database.
Ive been thinking of ways around this and thought of having a default find method in rails that would search by all the fields but when the user only sends 2 fields accross from the client i was going to send over 5 wildcard caracters with them.
Please can someone let me know if this is the wrong way to go about this, and if its not could someone let me know what the wildcard is because i cant seem to find it on google

thanks
0
Comment
Question by:Jonesey007
[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
  • 2
7 Comments
 

Author Comment

by:Jonesey007
ID: 22738974
I've got alittle further on this, in mysql its the % sign so if i use the following:
select * from assets where manufacturer like '%' and modelNumber like '690';
This will return all the results where model number is 690 and doesnt care about the manufacturer type.
I cant get this to work in rails though, im using;
 @assets = Asset.find(:all, :conditions => ["manufacturer = ?", '%'])
This should return all the assets back but the result is coming back empty
thanks
0
 
LVL 14

Accepted Solution

by:
wesgarrison earned 500 total points
ID: 22743596
I normally build the sql string before doing the find (see code).

By starting with 'true', you can append any number of statements as long as they begin with AND.  That way you don't have to figure out whether or not to put the AND at the beginning.  mysql will optimize that out, too, so it doesn't hurt the query speed.

Yes, % is the wildcard, not *.  
sql = 'true'
sql += "AND manufacturer = '%#{params[:man]}%'" unless params[:man].nil?
sql += "AND model = '#{params[:model]}'" unless params[:model].nil?
# .... for each search field
 
@assets = Asset.find(:all, :conditions => sql}

Open in new window

0
 

Author Comment

by:Jonesey007
ID: 22744577
Hi, thanks for the reply, i've tried it but im not getting much luck, here's my method
 def findbytype
  @man=params[:man]

  sql = 'true'
  sql += " AND manufacturer = '%#{params[:man]}%'" unless params[:man].nil?
  @assets = Asset.find(:all, :conditions => sql)
  render :xml => @assets.to_xml
end

Im getting an empty set back, i've tried putting the query through mysql and used the following:
mysql> select * from assets where 'true AND manufacturer like dell';
Empty set, 1 warning (0.00 sec)

Ive got no idea how to check to see what the warning is, any idea's?

thanks
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 22779052
Why don't you send a null field for the inputs that aren't specified by the user, and then check which inputs are provided and do the query just with them.

It involves a few if-statements, each with it's own query, depending on how many inputs are specified, but it wouldn't be tricky programming and it would be much more efficient than searching the database with unnecessary wildcard characters all the time.

$inputtext1=$_POST["inputtext1"];
$inputtext2=...etc...

if ($inputtext1!="" && $inputtext2=="") ($query = "SELECT...etc...}
elseif ... and so on.
0
 
LVL 14

Expert Comment

by:wesgarrison
ID: 22781036
mysql> select * from assets where 'true AND manufacturer like dell';

You need quotes around 'dell'.

Look in your log and look at the query that's being generated by the find() and paste it here and we'll take a look at it.
0
 

Author Closing Comment

by:Jonesey007
ID: 31509161
I got it working lastnite, not really sure what i was going wrong but thanks for the help

Pete
0

Featured Post

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.

Question has a verified solution.

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

In Ruby, Call or invoke a API DLL library is easily via Win32API class, win32-api gem or other gems. For general DLL API call, there are quite a few references, some good tips list below: http://www.rubytips.org/2008/05/13/accessing-windows-api-fro…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

632 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