SELECT a field based on a wildcard, but backwards?

[Strahan]

Hi.  I need help figuring out how to do a SQL SELECT.  I have a login script running, and it has a variable $pc = the name of the PC.  I have a MySQL database with a table "targets".  It has a field, pcname.  Mostly they are straight PC names, but I wanna include the ability to put in wildcards so one record may have pcname = 'GAMEBOX*'. 

 

I need to read the database and find any records where my $pc matches the pcname field.  It's easy enough if I'm wildcarding the other way, I'd just do "where pcname like '$pc%'" or something but how would you do that the opposite?  The only thing that came to mind was looping on the length of the $pc string and slowly building it out like if $pc = "gamebox01" it would query as g* ga* gam* game* etc. 

 

That seems horribly horribly inefficient, there must be a better way.

[corbin]

Uh....

 

 

Huh?

 

 

Edit:  I think I understand what you're trying to do, but why?

 

 

 

Also, in MySQL, there's no such thing as a * wildcard.  It's always % with LIKE (or regular expressions).

[Strahan]

Yea, I know * isn't valid for MySQL but it's not trying to be a MySQL wildcard.  Lemme try to restate..

 

I have an script that runs on many boxes.  It is supposed to take action based on what user is logged on and/or what the PC name is depending on how the admin user has it configured.  There is a MySQL database it queries, and here is an example of the "target" table:

 

[pcname]

firewall

filesrv

game*

printsrv

 

So the field pcname has those data.  The users are the ones who type in the PC names, and they are more comfortable/familiar with * as a wildcard which is why I use that.  Since I can't think of any way that the wildcard already embedded in the data can be programatically relevant to MySQL itself in the query, I figured I'll let them use * if it makes them happy.  If I run this script on a PC named "GAMEBOX01", I'll have a var $pc with that PC name string which I can easily see by looking at that example table that it matches the third record.  My question is how do I do that in SQL?

 

I can't just do select * from target where pcname like '$pc%' because that would stuff the full name in there.  I can't do a substr of $pc because the position of the wildcard in the data won't be constant.  So far, the solution I came up with that works is:

 

$sql = "select * from target where pcname = '$pc' or ";
for ($x=0; $x<=strlen($pc)-1; $x++) {
  $sql .= "pcname = '" . substr($pc, 0, $x) . "*'";
  if ($x < strlen($pc)-1) $sql .= " OR ";
}

 

This does work, but it feels rather bulky and inefficient.  I'm wondering if there is a better way.  I'm hoping I'm just overlooking some easy way, hehe.

[corbin]

Wow....  I must say, I have no idea.

 

 

[fenway]

Sounds like you need REGEXP and to replace * with %