nigel5
asked on
PHP Tokenizer and SQL
Hi there,
I am looking at writing a simple SQL interface.
I want to tokenize an SQL string so I can prepend a table identifier to table names.
The reason is I only have one database, and I want to separate out the tables so they have structure...
for instance
userlist for the blog section should be different to the userlist for the distribution list section, but theyh could have simple sql...
select * from userlist
with a tokenizer, I can prepend the DB section to the table name..
select * from dist_userlist
select * from blog_userlist
making reading a lot simpler... I think.
Any ideas how I can do this simply?
Thanks
Nigel.
I am looking at writing a simple SQL interface.
I want to tokenize an SQL string so I can prepend a table identifier to table names.
The reason is I only have one database, and I want to separate out the tables so they have structure...
for instance
userlist for the blog section should be different to the userlist for the distribution list section, but theyh could have simple sql...
select * from userlist
with a tokenizer, I can prepend the DB section to the table name..
select * from dist_userlist
select * from blog_userlist
making reading a lot simpler... I think.
Any ideas how I can do this simply?
Thanks
Nigel.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
roonaan. I had a few issues with strtolower() since the WHERE Clauses are generally case sensitive. I did think about finding the strpos of 'FROM' and 'WHERE' then using split.... but that got messy.
gnudiff, thanks that inspired me, and I have a class that builds the SQL in the first place. This is not the optimal solution however, since it is easier to type SQL.... it does mean that I cannot type SQL into a form input and have it run against the database...
But then would I really want that much power... best have some form with some control and dynamic inputs...
Thanks guys.
gnudiff, thanks that inspired me, and I have a class that builds the SQL in the first place. This is not the optimal solution however, since it is easier to type SQL.... it does mean that I cannot type SQL into a form input and have it run against the database...
But then would I really want that much power... best have some form with some control and dynamic inputs...
Thanks guys.
It certainly depends on your users.
I have just written some PHP that allows user to build query using webform and adding/removing conditions one by one.
Like, he can select dropdown for logical operator (AND/OR/AND NOT/OR NOT), field, condition (is/isn't/>/</contains/... ) and value, and that gets shown in the list on the page, eg:
AND title contains 'One'
AND creation date > 2002-01-01
AND NOT header is 9338
AND NOT header starts with 7
There are certain benefits to storing the query in this form at all times, and constructing the real SQL only at the point of executing it.
I have just written some PHP that allows user to build query using webform and adding/removing conditions one by one.
Like, he can select dropdown for logical operator (AND/OR/AND NOT/OR NOT), field, condition (is/isn't/>/</contains/...
AND title contains 'One'
AND creation date > 2002-01-01
AND NOT header is 9338
AND NOT header starts with 7
There are certain benefits to storing the query in this form at all times, and constructing the real SQL only at the point of executing it.
Why I used strtolower was because you would like to have a case insensitive search to where the positions of the keywords 'FROM' and 'WHERE' are. This is because stripos is still not functional on all systems.
But better indeed to have a class which performs some validation before querying. Saves a lot of work in the future :-)
Regards
-r-
But better indeed to have a class which performs some validation before querying. Saves a lot of work in the future :-)
Regards
-r-
substr(strtolower($query),
You'd then good retrieve the 'from table table table where'-piece and replacement should be doable.
Or even better. Have variable queries:
$query = 'SELECT * FROM `'.$table.'` WHERE etc';
-r-