Link to home
Get AccessLog in
Avatar of morako
morakoFlag 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.

User generated image
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..
Avatar of morako
morako
Flag of United States of America image

ASKER

Maybe a long SQL statement that can achieve the above one time SQL RUN get value update field.
Avatar of 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

Avatar of 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
Avatar of morako

ASKER

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

ASKER

I run the above SQL but nothing happens...  Any thoughts?
ASKER CERTIFIED SOLUTION
Avatar of Scott Madeira
Scott Madeira
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of morako

ASKER

Very nice...  ;-)
Avatar of morako

ASKER

Very cool...  ;-)