Solved

Help with Update statement / regex?

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

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.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…

920 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

12 Experts available now in Live!

Get 1:1 Help Now