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?
 
wesgarrisonConnect With a Mentor Commented:
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
 
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
 
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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
All Courses

From novice to tech pro — start learning today.