Back To Programming / Scripting / Coding Forum

REGEX trouble

  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post January 28th, 2009, 6:28 pm

I'm trying to create some regex to retrieve some variables out of an SQL query... this is what I have so far

Code: [ Select ]
#(SELECT)(.*?)FROM(.*?)WHERE(.*?)ORDER BY(.*?)(ASC|DESC)#


The problem with that is that it doesn't give any results if I don't have ORDER BY or WHERE or whatever... I understand why I just don't know how to fix that... I would want it to give me an array of anything it could find.

What I'm trying to do is to explode an SQL string into an associative array... example below...

Code: [ Select ]
$sql = "SELECT email FROM users WHERE username = 'Billy Bob Joes'";


Once passed through the function, that would transform into...

Code: [ Select ]
array('SELECT' => 'email',
'FROM' => 'users',
'WHERE' => 'username = \'Billy Bob Joes\''
);
  1. array('SELECT' => 'email',
  2. 'FROM' => 'users',
  3. 'WHERE' => 'username = \'Billy Bob Joes\''
  4. );


Or, if the SQL string has the ORDER BY ID ASC. The array would look like

Code: [ Select ]
array('SELECT' => 'email',
'FROM' => 'users',
'WHERE' => 'username = \'Billy Bob Joes\'',
'ORDER BY' => 'ID ASC'
);
  1. array('SELECT' => 'email',
  2. 'FROM' => 'users',
  3. 'WHERE' => 'username = \'Billy Bob Joes\'',
  4. 'ORDER BY' => 'ID ASC'
  5. );


And so on... the reason I posted what I want is maybe there is a better way to do this, or maybe there is a function already that does this.

And explode(" ", $sql); doesn't really work for obvious reasons :lol:
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post January 28th, 2009, 6:28 pm

  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post January 28th, 2009, 9:13 pm

Note: This would be for trivial SQL queries... nothing fancy with UNION or anything like it. Simple SELECT (something) FROM (somewhere) where (Something is that thing) ORDER BY (this thing) ASC -or- DESC
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Joined: Jul 18, 2008
  • Posts: 239
  • Loc: Western Australia
  • Status: Offline

Post January 29th, 2009, 1:34 am

I might do something like:

Code: [ Select ]
#get variables from drop down boxes etc?
$select = "email";
$from = "users";
$where = "username = \'billybob\'";
$order = "ID ASC";

#if not blank, add to query
if (!empty($select)){$query = "SELECT ".$select;}
if (!empty($from)){$query = " FROM ".$query.$from;}
if (!empty($where)){$query = " WHERE ".$query.$where;}
if (!empty($order)){$query = " ODER BY ".$query.$order;}

echo $query;
  1. #get variables from drop down boxes etc?
  2. $select = "email";
  3. $from = "users";
  4. $where = "username = \'billybob\'";
  5. $order = "ID ASC";
  7. #if not blank, add to query
  8. if (!empty($select)){$query = "SELECT ".$select;}
  9. if (!empty($from)){$query = " FROM ".$query.$from;}
  10. if (!empty($where)){$query = " WHERE ".$query.$where;}
  11. if (!empty($order)){$query = " ODER BY ".$query.$order;}
  13. echo $query;


That seems the simplest way to do it - arrays might be complicating things a little.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post January 29th, 2009, 4:15 pm

I don't need that... I want a basic SELECT query that is held in a string to be exploded into that associative array.
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Joined: Jul 18, 2008
  • Posts: 239
  • Loc: Western Australia
  • Status: Offline

Post January 29th, 2009, 7:51 pm

Oh sorry, I misunderstood the question. :) I'll have a better look later, got to go out now.

Page 1 of 1

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 206 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.