Solved

Pass through a wildcard value to Mysql

Posted on 2008-10-16
7
715 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 27

Expert Comment

by:yodercm
Comment Utility
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
Comment Utility
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
Comment Utility
I got it working lastnite, not really sure what i was going wrong but thanks for the help

Pete
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now