Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP Tokenizer and SQL

Posted on 2004-10-11
5
Medium Priority
?
483 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:nigel5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 49

Expert Comment

by:Roonaan
ID: 12278440
substr(strtolower($query), ' from ');
substr(strtolower($query), ' where');

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-
0
 
LVL 3

Accepted Solution

by:
gnudiff earned 500 total points
ID: 12284484
I wrote a small class for that recently.

The basic idea is to store different parts of the query in separate arrays, and only join them into a query upon executing it.

ie. something like:

$sb = new SQLBuilder;

$sb->add_column("mycol");
$sb->add_clause('FROM', 'table1 t1');
$sb->add_clause('FROM', 'table2 t2');
$sb->add_clause('WHERE', 't2.id = t1.id');
etc.

and then $query = $sb->parse();

where parse takes all the currently defined parts together and forms a single:
SELECT mysql FROM table1 t1, table2 t2 WHERE t2.id=t1.id

0
 

Author Comment

by:nigel5
ID: 12286818
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.
0
 
LVL 3

Expert Comment

by:gnudiff
ID: 12287036
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.
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12287040
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-
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question