Jump to content

  • Log in with Facebook Log in with Twitter Log In with Google      Sign In   
  • Create Account

Subscribe to HRA Now!

 



Are you a Google Analytics enthusiast?

Share and download Custom Google Analytics Reports, dashboards and advanced segments--for FREE! 

 



 

 www.CustomReportSharing.com 

From the folks who brought you High Rankings!



Photo
- - - - -

Mysql Select Wild Cards


  • Please log in to reply
9 replies to this topic

#1 piskie

piskie

    HR 7

  • Active Members
  • PipPipPipPipPipPipPip
  • 1,098 posts
  • Location:Cornwall

Posted 16 June 2009 - 10:28 AM

Has anyone please got any clues as to why I can't get a Wild Card to work in my SELECT functions.

This works:
"SELECT * FROM destinations WHERE company1 = '$company' OR company2 = '$company' ORDER BY name";

This fails:
"SELECT * FROM destinations WHERE company_ = '$company' ORDER BY name";

and this fails:
"SELECT * FROM destinations WHERE company% = '$company' ORDER BY name";

I am running Cpanel.
MySQL client version: 4.1.22
phpMyAdmin - 2.11.9.4

Is there some functionality that I need to enable or am I just not coding it correctly.
Thanks in advance.

#2 portentint

portentint

    HR 2

  • Active Members
  • PipPip
  • 30 posts

Posted 16 June 2009 - 12:50 PM

What are you trying to match? Usually a % is the accepted wildcard for matching [word] and any characters that come before or after.

So '%company' would do it, I think.


#3 piskie

piskie

    HR 7

  • Active Members
  • PipPipPipPipPipPipPip
  • 1,098 posts
  • Location:Cornwall

Posted 17 June 2009 - 01:37 AM

I am trying to match: company1 or company2 or company3 etc. to $company.

#4 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 17 June 2009 - 03:27 AM

So you're trying to wildcard the field name of a database?

I'm not sure I'd do that without placing some extremely tight security on the script itself. Or to quote the robot from Lost In Space: Danger Will Robinson! Danger!

For an internal app where nothing much is going to change over the years and the users are going to be limited, it's okay. But when you start opening such things up to publicly available scripts, this kind of wildcarding make me queasy. Too many things could go horribly wrong down the road if someone else were to get their hands on the code, even if they weren't trying to do something nefarious but simply didn't understand what the code did.

Okay, with all of those disclaimers out of the way... nerd.gif

What you're trying to do won't work the way you're trying to do it. That's a natural protection from potential harm built into MySQL. Of course the fact that there are protections in there doesn't mean it cannot be done. How depends upon which version of MySQL you're running.

If you're on MySQL 5 you'll probably want to look into Information Schema Tables. That's a link to the official docs. There are lots more examples out there if you search Google for the Information Schema bit.

Realize though that if you don't place some significant limits on what the select statement does it can have some rather serious effects on server loads. It can end up producing a runaway Select statement.

If you're on an earlier version of MySQL you can accomplish the same basic thing by via "show tables" and then using php to analyze the results, pull out the relevant entries via ereg or whatever, then using that to build your SELECT statement dynamically. Again be a bit careful with it since these automated types of things can tend to run wild.

If you only have a few of these potential company* 's to process, the safer way to do it is to hard code them in your select statement with a bit of concatenation withsome OR's. Similar to your first working example, just with more OR's in there.

#5 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 17 June 2009 - 07:17 AM

Apart from Randy's heads up , you have the SQL syntax wrong, to use wild cards you need to place them inside the search string and use the operator LIKE....

CODE
SELECT * FROM destinations WHERE company LIKE '%$search%'


that allows anything BEFORE the search string and anything AFTER

hope this helps.


#6 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 17 June 2009 - 09:32 AM

Am I overlooking something by suggesting that (aside of any syntax problem) magic quotes are needed to compare the value of the variable rather than the name of the variable here? e.g.

CODE
$query = "SELECT * FROM destinations WHERE company1 = \"$company\" OR company2 = \"$company\" ORDER BY name";


Maybe the outside magic quotes take care of that and maybe I've not thought this through properly and maybe I'm prepared to admit that I should have.

Edited by rolf, 17 June 2009 - 09:37 AM.


#7 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 17 June 2009 - 10:19 AM

possibly some form of variable interpolation considerations need to be taken, but i beleive you should still use single quotes for SQL queries so in PERL i'd do...

CODE
my $query = "SELECT * FROM destinations WHERE company1 = '" . $company . "' OR company2 = '" . $company . "' ORDER BY name";


Or

CODE
my $query = "SELECT * FROM destinations WHERE company LIKE '%" . $search . "%'";


#8 piskie

piskie

    HR 7

  • Active Members
  • PipPipPipPipPipPipPip
  • 1,098 posts
  • Location:Cornwall

Posted 18 June 2009 - 04:33 PM

Very helpful replies, thanks a lot everyone. I now understand why I couldn't get it to work.
In view of the security concerns raised by Randy, I am minded to hard code the select statement even though this means it is a bit more work when new companies join the scheme. I do however severely limit the permissions for the "Connection" from the pages where this is featured by not allowing writing, deleting etc.

#9 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 18 June 2009 - 08:56 PM

What you could do in that case where things might be changing from time to time is to either set the changing part as a php variable, or even make it part of a file unto itself and require/include it into your real page. Then you simply call this variable in the SELECT statement.

I've done the extra file thing several times before and it makes editing/updating those a snap. As long as you remember what you call the additional file. lol.gif

#10 piskie

piskie

    HR 7

  • Active Members
  • PipPipPipPipPipPipPip
  • 1,098 posts
  • Location:Cornwall

Posted 20 June 2009 - 12:35 PM

Yes Randy, that's exactly what I've done, used an include file and the selects then become a variable. It is simple enough and it's site wide. I already do name my includes something relevant and intuitive, it can be a nightmare downstream otherwise.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

SPAM FREE FORUM!
 
If you are just registering to spam,
don't bother. You will be wasting your
time as your spam will never see the
light of day!