Solved

Pass through a wildcard value to Mysql

Posted on 2008-10-16
7
717 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 27

Expert Comment

by:yodercm
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

778 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