[RESOLVED] preg_replace (PCRE, arggh!)

[SpeedBird]

This should be simple. I have an array like this:

('array part 1')
('array part 2')
('array part 3')

I want to remove the opening and closing brackets () from each element of the array. Some elements in the array will contain brackets as part of their value, but not with a single-quote character, '. I thought this would do the trick:

preg_replace('/((?=\')|(?<=\'))/','',$array);

A breakdown of the PCRE:

/ #Delimiter
( #Escape opening bracket [(] character
(?=\') #Positive look ahead, single quote character ['] is escaped
| #Pipe, OR match
(?<=\') #Positive look behind, single quote character ['] is escaped
) #Escape closing bracket [)] character
/ #Delimiter

On it's own, the first part of the PCRE works, removing the opening bracket, it's the second part that never works but I can't see why. Tried a few variations but I'm stumped.

[bradgrafelman]

So do all of the values have single quotes around them? If so, just do this:

preg_replace('/\(\'(.*?)\'\)/','\'\1\'',$array);

EDIT: Actually, your code worked just fine for me:

$array = "('array part 1')
('array part 2')
('array part 3')";

echo preg_replace('/\((?=\')|(?<=\')\)/','',$array);
/* Output:
'array part 1'
'array part 2'
'array part 3'
*/

[SpeedBird]

bradgrafelman wrote:

So do all of the values have single quotes around them? If so, just do this:

preg_replace('/\(\'(.*?)\'\)/','\'\1\'',$array);

That worked, but it removed the single quotes as well (I want to keep them).

The code I posted doesn't work for me, nor does it work on REGex Tester.

I must be having one of those days.

[laserlight]

Well, just add them back, or shift the parentheses to include them in the subpattern.

[NogDog]

untested:

$search = array('/^\(/', '/\)$/');
$array = preg_replace($search, '', $array);

[bradgrafelman]

If you copy and paste my second code snippet above, you should get the same result.

If so, it must be a problem with your $array string.

[SpeedBird]

bradgrafelman wrote:

If you copy and paste my second code snippet above, you should get the same result.

If so, it must be a problem with your $array string.

I think that is more likely the case. I have an Access database with a customer table like this:

tblcustomer

customerid     salutation     firstname     lastname
1                    Mr                 Joe               Bloggs
2                    Mr                 Vincent         Vega
3                    Mrs               Jane             Doe

And a MySQL database with a blank customer table like this:

tblcustomer

customerid     title               firstname     lastname

I want to query all the records from tblcustomer in the Access database and insert them into tblcustomer in the MySQL database. Using 'ON DUPLICATE KEY UPDATE' should make sure that existing rows in the MySQL database are updated and new rows are added should they not exist (customerid being an indexed, primary key in both databases).

Here's the code I have presently:

<?php
/*
Connect to Access database
*/

echo "Connecting to Access database...\n";
$access = odbc_connect('access','','');
if (!$access) {
   exit("Connect failed: " . $access);
} else {
   echo "Success!\n\n";
}

/*
Query Access database
*/

echo "Retrieving customer data...\n";
$cust_data = '';  

$cust_query = "SELECT customerid, salutation, firstname, lastname FROM tblcustomer ORDER BY customerid";
$res = odbc_exec($access,$cust_query);
while($r = odbc_fetch_array($res)){
$cust_data .= "('".$r['customerid']."', '".$r['salutation']."', '".$r['firstname']."', '".$r['lastname']."',) ";
}
$cust_data = substr($cust_data,0,-1);
if (!$cust_data) {
   exit("Error retrieving customer data!");
} else {
   echo "Success!\n\n";
}

odbc_close($access);

/*
Connect to MySQL database
*/

echo "Connecting to MySQL database...\n";
mysql_connect('127.0.0.1:3306', 'user', 'pass') or
   die('Could not connect: ' . mysql_error());
mysql_select_db('mysqldb');
echo "Success!\n\n";

/*
Populate/update MySQL database

Increase max_allowed_packet for >1Mb of data
*/

echo "Updating customer data...\n";
$cust_insert = "INSERT INTO tblcustomer (customerid, title, firstname, lastname) Values $cust_data ON DUPLICATE KEY UPDATE title=VALUES(title), firstname=VALUES(firstname), lastname=VALUES(lastname)";
mysql_query(stripslashes($cust_insert)) or die(mysql_error());
echo "Success!\n\n";
?>

All I'm getting right now is

You have an error in your SQL syntax; check the manual that came with your MySQL server version for the right syntax to use near ') ('2', 'Mr', 'Vincent', 'Vega' at line 1

Before insertion the value of $cust_data (when output to a file) is:

('1', 'Mr', 'Joe', 'Bloggs',) ('2', 'Mr', 'Vincent', 'Vega',) ('3', 'Mrs', 'Jane', 'Doe',)

If it's of any significance this is PHP 5.1.4 with MySQL 5.0.27 on Win 2K3 Server.

[Roger_Ramjet]

OK, the simplest way to do this is in the Access database, if you have access and coding rights to it. The reason being that Access can link to mysql database tables through ODBC DSN very easily. You can then write Access queries to update the MySQL tables. You can also write VBA functions to handle the data conversion issues and use them directly in the Access query. This is one case where MS Access wins over OpenSource - a rare thing in itself.

To do this you will need to downlaod the MySQL ODBC driver and install it on the Access machine then create a System DSN for the MySQL database. After that just go into Access File->Get external data->Link tables etc.

[Roger_Ramjet]

PS your current error is because you have trailing commas in the values
('1', 'Mr', 'Joe', 'Bloggs',)

[Installer]

This should get rid of the query error:

$cust_data .= "('".$r['customerid']."', '".$r['salutation']."', '".$r['firstname']."', '".$r['lastname']."'),";

And here's an alternate solution to your first problem:

str_replace(array("('", "')"), "'", $str)

[Roger_Ramjet]

Just a futher note:
Unfortunately you cannot use replication to synchronise Access and MySQL databases. There are some 3rd party tools out there to simulate this but I've no direct experience of how good they are.

Unforunately Access does not support Triggers or they could handle this for you - maybe 2007 will?? What I have done in the past is to code update functions in the Access Forms so that any changes to the underlying Access tables are also mirrored in the mysql tables.

[SpeedBird]

Roger Ramjet wrote:

PS your current error is because you have trailing commas in the values
('1', 'Mr', 'Joe', 'Bloggs',)

Installer wrote:

This should get rid of the query error:

$cust_data .= "('".$r['customerid']."', '".$r['salutation']."', '".$r['firstname']."', '".$r['lastname']."'),"; 

I can't believe I didn't spot that earlier. Thanks Roger Ramjet & Installer.

Roger Ramjet wrote:

OK, the simplest way to do this is in the Access database, if you have access and coding rights to it. The reason being that Access can link to mysql database tables through ODBC DSN very easily. You can then write Access queries to update the MySQL tables. You can also write VBA functions to handle the data conversion issues and use them directly in the Access query.

Unfortunately I only have read access to the database (and I had a tough time just getting that). It's an Access '97 database used by a commercial application. The database will only open natively in Access 2007, but I can't do anything with it unless I upgrade it to 2003 or 2007 format (which stops the application from working). I had to mess around with Jet drivers to get it working, and I still don't know exactly how I chanced across fixing it.

[Roger_Ramjet]

Well now, you can still do it my way. One of the most usefull things about Access is the ease with which one can build a set of applications around a common backend. I was meaning that you link to the tables from a separate Access app anyway - from habit I assumed that the original was front-end forms/back-end tables rather than some nasty tables and forms all-in-one.

[You should tell whomever to convert the whole thing to Access 2000/Jet 4 anyway. Seamless process with no re-coding whatsoever required but you get away from all the well-documented flaws in Jet 3.5. I have done it with numerous large and complex Access apps many times and the only problem that ever arises is if some fool has made the basic and basest mistake of using MACROS :eek: . Every time I see them I know it was some idiot creating an app with the wizards :queasy: not a programmer who built the thing.
Better yet, tell them to dump the thing and start over with Access2003/SQL2005 EXPRESS 😃 ]

Anyway, create a new Access db in 2000/2003 and link to the tables in whatever rubbish app it is. Then link to the mysql db. Now you are in business.
You will need to create a VBA function to convert access dates to ISO8601 format that mysql uses - easy with string chopping functions. You may also need to convert currency column values as they will come with currency symbols and get converted to strings by mysql.
Then use the query builder to generate the queries to append your data to the mysql tables.
Now, Access does not support the ON DUPLICATE KEY UPDATE syntax so you will need 2 queries: 1 to append new rows (using LEFT JOIN:IS NULL) the other to update existing rows. Just put them in the same event module.
Done and dusted, fast efficient and always available.