Avatar of morako
morako
Flag for United States of America asked on

I need to do a one time run query to grab values in a field and place the value into a new field in the same record.

This is a one time update to place the connection_id values from the log_body field into its own field connection_id.

Screenshot of table
I created the field connection_id in a table to better manipulate the data.  I need to grab the value after connection_id from a string in a field called Log_body and place the value into the connection_id field  currently NULL.  (View image)

select a value from data in a database field and update the same record with that value into a field.

<a href="index.php?t=network&amp;connection_id=4">ANOTHONY</a> added a <a href="index.php?wp=blog&amp;blog_id=2#comment_id2">blog comment</a>.

The data in the string above is in a database field called "log_body "  and I need to grab the connection_id value (in this field it would be 4) and update the same record with the connection_id in the field called connection_id.

Any thoughts on if this is possible.  How do I grab the value 4 .  Grab the value after connection_id=VALUE and before the " or &

<a href="index.php?t=network&amp;connection_id=4">
<a href="index.php?t=network&amp;connection_id=4&record=23">

Select ((Value after connection_id= "value" in the field)) `log_body` AND log_id

Then

$logidvar = $row['log_id'];         //place the log_id in a var to update same record

place value collected after connection_id= in the log_body string into a $variable

and

update `logs_table` set (connection_id  = ($variable) WHERE log_id = $logidvar

This is a one time update to place the connection_id values from the log_body field into its own field connection_id.

And I need to run the query once to update the current data (all records) so it needs to run recursively..
PHPMySQL ServerSQL

Avatar of undefined
Last Comment
morako

8/22/2022 - Mon
morako

ASKER
Maybe a long SQL statement that can achieve the above one time SQL RUN get value update field.
Bradley Fox

Try this for ones with conneciton id 0-9 (it looks like the left portion of the string is the same on all records, if not this won't work)
UPDATE logs_table SET
connection_id = RIGHT(LEFT(log_body,48),1)
WHERE log_body LIKE '%connection_id=[0123456789][!0123456789]%'

Open in new window


And this for 10-99 (you get the idea)
UPDATE logs_table SET
connection_id = RIGHT(LEFT(log_body,49),2)
WHERE log_body LIKE '%connection_id=[0123456789][0123456789][!0123456789]%'

Open in new window

morako

ASKER
Total records in the table amount to 806.  the highest connection_id value is 69

Would your second post be the answer to this?

UPDATE logs_table SET
connection_id = RIGHT(LEFT(log_body,49),2)
WHERE log_body LIKE '%connection_id=[0123456789][0123456789][!0123456789]%'
AFiero1.JPG
Your help has saved me hundreds of hours of internet surfing.
fblack61
morako

ASKER
What would it be if the first records have 1 digit and others have 2 digits?
morako

ASKER
I run the above SQL but nothing happens...  Any thoughts?
ASKER CERTIFIED SOLUTION
Scott Madeira

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
morako

ASKER
Very nice...  ;-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
morako

ASKER
Very cool...  ;-)