9 replies to this topic

[piskie]

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.

[portentint]

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.

[piskie]

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

[Randy]

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...

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.

[1dmf]

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.

[rolf]

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.

[1dmf]

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 . "%'";

[piskie]

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.

[Randy]

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.

[piskie]

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.