Solved

Help with Update statement / regex?

Posted on 2013-05-25
5
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 110

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 110

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 110

Expert Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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…

752 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