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
