Pass through a wildcard value to Mysql

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
Jonesey007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jonesey007Author Commented:
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
wesgarrisonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jonesey007Author Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Cornelia YoderArtistCommented:
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
wesgarrisonCommented:
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
Jonesey007Author Commented:
I got it working lastnite, not really sure what i was going wrong but thanks for the help

Pete
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Ruby

From novice to tech pro — start learning today.

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.