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.

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&connection_id=4">ANOTHONY</a> added a <a href="index.php?wp=blog&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&connection_id=4">
<a href="index.php?t=network&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..

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

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