Extract column names from a (my)SQL query

--00163623aa8d43513a047183d6cd
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Lets assume I have the following string:
"SELECT field1, field2, field3 FROM tablename WHERE field1 = 'something' "

Is there any way to get "field1, field2, field3"? assuming it might also
have join, left join - things like that.

I can easily do it with preg_match, but I have to make sure its "foolproof".
It would be a lot better if it will also be able to parse the whole query -
catch limit, where, joins, comments....


Is there any php class that could do it?

I've looked in the pear site and on Google but haven't had any luck.

--
Use ROT26 for best security

--00163623aa8d43513a047183d6cd--

Re: Extract column names from a (my)SQL query

this might be some help...
http://stackoverflow.com/questions/283087/php-mysql-sql-pars er-insert-and-update

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Extract column names from a (my)SQL query

--0016e6476268500e170471994946
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

I haven't finished reading it yet - but this looks great - Thank you so
much!
On Thu, Aug 20, 2009 at 4:36 PM, kranthi wrote:

> this might be some help...
>
> http://stackoverflow.com/questions/283087/php-mysql-sql-pars er-insert-and-update
>



--
Use ROT26 for best security

--0016e6476268500e170471994946--

Re: Extract column names from a (my)SQL query

On Wed, 19 Aug 2009 22:42:36 +0300, דניאל דנון wrote:

> Lets assume I have the following string:
> "SELECT field1, field2, field3 FROM tablename WHERE field1 = 'something' "
>
> Is there any way to get "field1, field2, field3"? assuming it might also
> have join, left join - things like that.

If you're using MySQL, you can try mysql_field_name()
and see if it gets you anywhere. I don't think it works
on empty results though.


/Nisse

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Re: Extract column names from a (my)SQL query

> -----Original Message-----
> From: Nisse Engström [mailto:news.NOSPAM.0ixbtqKe@luden.se]=20
>
> If you're using MySQL, you can try mysql_field_name()
> and see if it gets you anywhere. I don't think it works
> on empty results though.


FYI. It will.


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Extract column names from a (my)SQL query

--0016e647657875a90b0471ab2d55
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

You all misunderstood my question, please read my replies above...

I'm looking to extract it from a string - *I'm not executing the queries, I
only get them as a string*

And to the topic:

Since everything I found was very complicated to parse, I've crafted my own
preg pattern,

"/^(\*|[a-z_, \(\)0-9]+)[\s]+FROM[\s]+([a-z_\.
]+)\s+(WHERE[\s]+(.+)|)\s*(|ORDER BY ([a-z0-9,
])(|\s(DESC|ASC)))\s*(|LIMIT\s+([0-9]+)\s*,\s*([0-9]+))\s*$/ Ui"

(Assuming this is a SELECT string, and the subject is something similar to:
* FROM table WHERE field2=3D'field3' LIMIT 0,10

(Yes, no SELECT - I've got another function to determine whether its select=
,
insert, update, delete from - etc, and it returns to query without the name
of the command - "SELECT field FROM table" =3D> "field FROM table")

(Without using joins or things like that)

But I don't have much experience crafting patterns like that - or working
with them so I'd be glad if you think of a better way of doing it,

and.... problem is since I'm using sub-brackets, its hard to process it
since if there is WHERE and a LIMIT, then LIMIT (...) will be on $result[$n=
]
(for example),
But if there is no WHERE, then LIMIT (...) will be on $result[$n - 2];

How should I overcome this problem?


2009/8/21 Daevid Vincent

> > -----Original Message-----
> > From: Nisse Engström [mailto:news.NOSPAM.0ixbtqKe@luden.se]
> >
> > If you're using MySQL, you can try mysql_field_name()
> > and see if it gets you anywhere. I don't think it works
> > on empty results though.
>
>
> FYI. It will.
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--=20
Use ROT26 for best security

--0016e647657875a90b0471ab2d55--

Re: Re: Extract column names from a (my)SQL query

--001636ed7183589f410471ac308f
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Update: I've changed it into

"/^(\*|[a-z_,
\(\)0-9]+)[\s]+FROM[\s]+([a-z_\.]+)(\s+)?(WHERE[\s]+(.+))?\s *(LIMIT\s+([0-9=
]+)\s*,\s*([0-9]+))?\s*(ORDER
BY ([a-z0-9, ]+)?(\s*(DESC|ASC)))?$/Ui"


Only problem that on:
SELECT * FROM table WHERE field2=3D'field3' ORDER BY id DESC LIMIT 0,10
it outputs

Array
(
[0] =3D> * FROM table WHERE field2=3D'field3' ORDER BY id DESC LIMIT 0,=
10
[1] =3D> *
[2] =3D> table
[3] =3D>
[4] =3D> WHERE field2=3D'field3' ORDER BY id DESC
[5] =3D> field2=3D'field3' ORDER BY id DESC
[6] =3D> LIMIT 0,10
[7] =3D> 0
[8] =3D> 10
)



On Fri, Aug 21, 2009 at 9:38 PM, דניאל ד=D7=
 ×•×=9F wrote:

> You all misunderstood my question, please read my replies above...
>
> I'm looking to extract it from a string - *I'm not executing the queries,
> I only get them as a string*
>
> And to the topic:
>
> Since everything I found was very complicated to parse, I've crafted my o=
wn
> preg pattern,
>
> "/^(\*|[a-z_, \(\)0-9]+)[\s]+FROM[\s]+([a-z_\.
> ]+)\s+(WHERE[\s]+(.+)|)\s*(|ORDER BY ([a-z0-9,
> ])(|\s(DESC|ASC)))\s*(|LIMIT\s+([0-9]+)\s*,\s*([0-9]+))\s*$/ Ui"
>
> (Assuming this is a SELECT string, and the subject is something similar t=
o:
> * FROM table WHERE field2=3D'field3' LIMIT 0,10
>
> (Yes, no SELECT - I've got another function to determine whether its
> select, insert, update, delete from - etc, and it returns to query withou=
t
> the name of the command - "SELECT field FROM table" =3D> "field FROM tabl=
e")
>
> (Without using joins or things like that)
>
> But I don't have much experience crafting patterns like that - or working
> with them so I'd be glad if you think of a better way of doing it,
>
> and.... problem is since I'm using sub-brackets, its hard to process it
> since if there is WHERE and a LIMIT, then LIMIT (...) will be on $result[=
$n]
> (for example),
> But if there is no WHERE, then LIMIT (...) will be on $result[$n - 2];
>
> How should I overcome this problem?
>
>
> 2009/8/21 Daevid Vincent
>
> > -----Original Message-----
>> > From: Nisse Engström [mailto:news.NOSPAM.0ixbtqKe@luden.se]
>> >
>> > If you're using MySQL, you can try mysql_field_name()
>> > and see if it gets you anywhere. I don't think it works
>> > on empty results though.
>>
>>
>> FYI. It will.
>>
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>
>
> --
> Use ROT26 for best security
>



--=20
Use ROT26 for best security

--001636ed7183589f410471ac308f--