Solved

Help with Update statement / regex?

Posted on 2013-05-25
5
426 Views
Last Modified: 2013-05-25
I am converting from one forum to another, and the one thing that needs formatting help are the quote from other posters.  

Here is the current format

[quote author="Mr Poster" date="1325040681"]Check them out...[/quote]

Open in new window


Here is the new format
<blockquote class="ipsBlockquote" data-author="Mr Poster" data-cid="9" data-time="1367980239">
<div>
<p>Check them out...</p>
</div>
</blockquote>

Open in new window



Anyone have any solutions how I can somewhat run an update statement to convert the old to the new?
0
Comment
Question by:theideabulb
  • 3
  • 2
5 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39196985
Are you using PHP?  If so, it might be easier to do all of this in a PHP script that loops through the rows.  Also, what is the meaning and origin of the data-cid="9"?  Is this an important variable or can it just be "plugged in" as-is?
0
 

Author Comment

by:theideabulb
ID: 39197062
php is available, but i wouldn't know how to do it.  I don't think the data-cid is important.  Looks the same if i take it out, but it was just there in the column.  I am unsure of its use.
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39197151
OK, here is the example with the essential moving parts to transform the data.  Have a look at the view-source of http://www.laprbass.com/RAY_temp_theideabulb.php to see how it works.

Your PHP script would run this process once on each row of the table.  If you can post the CREATE TABLE statement for the table in question, I can show you something more of a complete PHP example.  I would just need to know the column names to get the script correct.  If nothing goes wrong, you can do this in three steps.

Step One: Back Up The Table!
Step Two: Run The Updates
Step Three: Check the work

<?php // RAY_temp_theideabulb.php
error_reporting(E_ALL);


// SEE http://www.experts-exchange.com/Database/MySQL/Q_28138913.html


/* OBJECTIVE
 *
 * <blockquote class="ipsBlockquote" data-author="Mr Poster" data-cid="9" data-time="1367980239">
 * <div>
 * <p>Check them out...</p>
 * </div>
 * </blockquote>
 *
 */

// INPUT, AS IF FROM A ROW OF THE QUERY RESULTS SET
$input = <<<EOD
[quote author="Mr Poster" date="1325040681"]Check them out...[/quote]
EOD;

// ISOLATE AUTHOR
$array  = explode('author="', $input);
$array  = explode('"', $array[1]);
$author = $array[0];

// ISOLATE DATE (TIMESTAMP)
$array  = explode('date="', $input);
$array  = explode('"', $array[1]);
$date   = $array[0];

// ISOLATE QUOTE TEXT
$array  = explode(']', $input);
$array  = explode('[/', $array[1]);
$quote  = $array[0];

// CONSTRUCT THE NEW QUOTE FORMAT
$outgo = <<<EOD
<blockquote class="ipsBlockquote" data-author="$author" data-cid="9" data-time="$date">
<div>
<p>$quote</p>
</div>
</blockquote>
EOD;

// RUN THE UPDATE QUERY HERE TO UPDATE THE ROW IN THE DATA BASE TABLE
echo $outgo;

Open in new window

0
 

Author Closing Comment

by:theideabulb
ID: 39197160
Wow, fantastic.  I can see how this will work.  Great job!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39197168
Thanks!  And best of luck with it, ~Ray
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
As most anyone who uses or has come across them can attest to, regular expressions (regex) are a complicated bit of magic. Packed so succinctly within their cryptic syntax lies a great deal of power. It's not the "take over the world" kind of power,…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

770 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