Solved

Help with Update statement / regex?

Posted on 2013-05-25
5
423 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 108

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 108

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 108

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now