ANOTHER option would be to query the DB for table names and then its
fields.
You could then do a dirt-simple str_replace, because you would know
exactly what table names to expect.
//totally wrong code to give you the idea:
$tables = pg_query_something_about_table_names($connection);
foreach($tables as $table){
$ms_table = str_replace('_', ' ', $table);
$query = str_replace("[$ms_table]", $table, $query);
}
This would not be as "slick" as a single PCRE, but it might be more
"maintainable" for a newbie who would have a tough time with PCRE
look-ahead assertions...
ymmv
On Mon, January 21, 2008 9:55 am, Keith Roberts wrote:
> Hi Peter. Is this what you want to do. Copy this into a
> *.php page, and then look at it with your browser.
>
> I just refactored one of my heredoc queries to handle your
> problem.
>
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
> Transitional//EN"
> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
>
> <html xmlns="http://www.w3.org/1999/xhtml"; xml:lang="en">
>
> <head>
> <title>SQL Regex Tester</title>
> </head>
>
> <body>
>
> <?php
>
> echo "Version 1 <br />";
>
> $this_table = 'MSS TABLE NAME';
> $this_id = 5;
>
> $sql = <<<OUT
> select * from $this_table
> where ID = $this_id
> OUT;
>
> echo "Contents of \$sql: <br />$sql";
>
>
>
> echo "<br /><br /> Version 2 <br />";
>
> $sql = "SELECT DISTINCT [Table Name].[Column.Name], [Table
> Name1].[Column Name 2]";
>
> echo "Contents of \$sql: <br />$sql";
>
>
>
> echo "<br /><br /> Version 3 <br />";
>
>
> $this_table = 'MSS TABLE NAME';
> $this_id = 5;
>
> $sql = <<<OUT
> SELECT DISTINCT
> [Table Name].[Column.Name],
> [Table Name1].[Column Name 2]
> OUT;
>
> echo "Contents of \$sql: <br />$sql";
>
>
>
> echo "<br /><br /> Version 4 <br />";
>
> $tbl_name = "[Table Name]";
> $col_name = "[Column Name]";
>
> $tbl_name1 = "[Table Name1]";
> $col_name2 = "[Column Name 2]";
>
>
> $sql = <<<OUT
> SELECT DISTINCT
> $tbl_name.$col_name,
> $tbl_name1.$col_name2
> OUT;
>
> echo "Contents of \$sql: <br />$sql";
>
>
> echo "<br /><br /> Version 5 <br />";
>
> $sql_query = "SELECT DISTINCT";
>
> $tbl_name = "[Table Name]";
> $col_name = "[Column Name]";
>
> $tbl_name1 = "[Table Name1]";
> $col_name2 = "[Column Name 2]";
>
>
> $tbl_name = strtolower($tbl_name);
> $col_name = strtolower($col_name);
>
> $tbl_name1 = strtolower($tbl_name1);
> $col_name2 = strtolower($col_name2);
>
>
> $sql = <<<OUT
> $sql_query
> $tbl_name.$col_name,
> $tbl_name1.$col_name2
> OUT;
>
> echo "Contents of \$sql: <br />$sql";
>
>
>
> echo "<br /><br /> Version 6 <br />";
>
> $sql_query = "SELECT DISTINCT";
>
> $tbl_name = "[Table Name]";
> $col_name = "[Column Name]";
>
> $tbl_name1 = "[Table Name1]";
> $col_name2 = "[Column Name 2]";
>
>
> // convert to lower case
> $tbl_name = strtolower($tbl_name);
> $col_name = strtolower($col_name);
>
> $tbl_name1 = strtolower($tbl_name1);
> $col_name2 = strtolower($col_name2);
>
>
> // remove '[]' characters
> $tbl_name = trim($tbl_name, "[]");
> $col_name = trim($col_name, "[]");
>
> $tbl_name1 = trim($tbl_name1, "[]");
> $col_name2 = trim($col_name2, "[]");
>
>
> // replace space with '_' character
> $tbl_name = preg_replace('/\s/', '_', $tbl_name);
> $col_name = preg_replace('/\s/', '_', $col_name);
>
> $tbl_name1 = preg_replace('/\s/', '_', $tbl_name1);
> $col_name2 = preg_replace('/\s/', '_', $col_name2);
>
>
> $sql = <<<OUT
> $sql_query
> $tbl_name.$col_name,
> $tbl_name1.$col_name2
> OUT;
>
> echo "Contents of \$sql: <br />$sql";
>
> ?>
>
> </body>
> </html>
>
> The above code is not optimal, but it works OK.
>
> I use the heredoc construct to build my sql query strings.
> Saves using all those single and double quote characters.
>
> http://uk2.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc
>
> HTH
>
> Keith Roberts
>
> -----------------------------------------------------------------
> Websites:
> http://www.karsites.net
> http://www.php-debuggers.net
> http://www.raised-from-the-dead.org.uk
>
> All email addresses are challenge-response protected with
> TMDA [http://tmda.net]
> -----------------------------------------------------------------
>
> On Mon, 21 Jan 2008, Peter wrote:
>
>> To: php-general@xxxxxxxxxxxxx
>> From: Peter <tasmaniac@xxxxxxxxxxxxxx>
>> Subject: Re: regex
>>
>> Well actually not a real lot so far. I'm just trial and error(lots
>> of that)
>> at the moment. I've only been 'playing with php for about a month or
>> so.
>> $file = "phptest1.txt";
>> $rep = array ("tbl_" , "_%", "bool default 0", "bool default 1",
>> '?');
>> $wih = array ("", "_pc", "bool DEFAULT FALSE", "bool DEFAULT TRUE",
>> "" );
>>
>> if (is_file($file)) :
>>
>> $fh = fopen($file, "r+") or die("File does not exist");
>> while (! feof($fh)) :
>> $line = fgets($fh,4096);
>> $str = strtolower($line);
>> $str_fixed = str_replace($rep, $wih, $str);
>> print $str_fixed . "<br />";
>> endwhile;
>>
>> Then as far as the regexp part for replacing the space inbetween
>> [blah blah
>> blah] with _ goes it was a case of try delete try delete etc. I may
>> need to
>> break the string into an array but that in its self adds its own
>> problems.
>>
>> Think one of my problems is Im try to run before I can crawl with
>> php,
>> postgre regex etc. Also its fun trying to workout things when all
>> the books
>> you come across are php/mysql.
>>
>> Came across an old message in my trawl of the news group that 'may'
>> help
>> going to give that a try as soon as I get the time. (it was more to
>> do with
>> replacing | with space between " " but it maybe convertable)
>> "Keith Roberts" <keith@xxxxxxxxxxxx> wrote in message
>> news:alpine.LFD.1.00.0801211031370.31657@xxxxxxxxxxxxxxxxxxx
>>> Can yo upost the code you have got to do the conversion so far
>>> please?
>>>
>>> Regards
>>>
>>> Keith
>>>
>>> -----------------------------------------------------------------
>>> Websites:
>>> http://www.karsites.net
>>> http://www.php-debuggers.net
>>> http://www.raised-from-the-dead.org.uk
>>>
>>> All email addresses are challenge-response protected with
>>> TMDA [http://tmda.net]
>>> -----------------------------------------------------------------
>>>
>>> On Mon, 21 Jan 2008, Peter wrote:
>>>
>>>> To: php-general@xxxxxxxxxxxxx
>>>> From: Peter <tasmaniac@xxxxxxxxxxxxxx>
>>>> Subject: regex
>>>>
>>>> I am trying to convert ms access sql to postgresql using php.
>>>>
>>>> I have a sql statement in the form ;-
>>>> $sql = SELECT DISTINCT [Table Name].[Column.Name], [Table Name
>>>> 1].[Column
>>>> Name 2] etc.
>>>>
>>>> what I want to end up with is $sql = SELECT DISTINCT
>>>> table_name.column_name,
>>>> table_name_1.column_name_2, ........
>>>>
>>>> I have managed to get the caps to lower but I cant work out how to
>>>> put
>>>> the _
>>>> in place of spaces if the spaces are between [ ]. I either end
>>>> up with
>>>> S_E_L_E_C ..... or SELECT_DISTINCT_ etc... .
>>>>
>>>> Naturally I have only used part of sql statement and table, column
>>>> names
>>>> have been changed. (Think the one I'm trying on is 2000+
>>>> characters. So
>>>> its
>>>> not a case of set number of words/numbers between [] it could be 2
>>>> or it
>>>> could be 4 etc)
>>>>
>>>> Anyone workout what I am talking about and can help would be
>>>> appreciated.
>>>>
>>>> Peter
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
