Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with Update statement / regex?

Posted on 2013-05-25
5
Medium Priority
?
433 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 111

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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 111

Expert Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
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. . .
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month8 days, 9 hours left to enroll

877 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