[vodka00]

Hello, I was wondering if it's possible to write a sql code that would run through all my trails that are in the database and add additional settings in the attrs field.

So, currently all the attrs fields in my trails look like this...

{"material":"materials/sprites/store/trails/awesome.vmt"}

I want to run an sql code that would change that line to this...

{
    "material":"materials/sprites/store/trails/awesome.vmt",
    "lifetime": 0.5,
    "width": 15,
    "endwidth": 3,
    "fadelength": 5
}

So the addition would be..

{"material":"materials/sprites/store/trails/awesome.vmt",
"lifetime": 0.5,
"width": 15,
"endwidth": 3,
"fadelength": 5
}

Is it possible to write a sql code that would do something like that?

[winterghost]

You would be better doing it in PHP or another language as SQL can't loop though the data from what I know (which I will admit isn't a lot!). Try this, see if it works. Might not work, haven't tested it.

PHP Code:

<?php

error_reporting(E_ALL);

$database = "";
$username = "";
$password = "";
$table = "store_items";
    $item = ""; //what item type do you want to select?

    // establish connections
    $db = new mysqli($database, $username, $password, $table);
    $db2 = new mysqli($database, $username, $password, $table); //two mysqli created to avoid affecting the result

    // check connection
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    function change_attrs($old){
        $arraycheck = is_array($old);
        if($arraycheck = true){
            return false;
        }

        $attrs_to_add = ',"lifetime": 0.5,"width": 15,"endwidth": 3,"fadelength": 5,';
        $curlybrace_left = '{';
        $curlybrace_right = '}';
        $replace[] = ' ';
        $new = str_replace($curlybrace_right, $replace, $old);
        $new = str_replace($curlybrace_left, $replace, $new);
        $edited_attrs = $curlybrace_left.$new.$attrs_to_add.$curlybrace_right;
        return $edited_attrs;

    }


    // Select the data
    $query = "SELECT id, name, attrs FROM store_items WHERE type = ".$item."";

    if ($result = $db->query($query)) {

        /* fetch associative array */
        $results = $result->fetch_assoc();

        foreach($results as $row){
            $old_attrs = $row['attrs'];
            $new_attrs = changeattrs($old_attrs);
            if($new_attrs = false){exit("The changeattrs function failed");}
            $row_id = $row['id'];
            echo "<br> Updating ".$row['name'].", changing attrs to ".$new_attrs."<br>";

            $query1 = "UPDATE store_items SET attrs = ".$new_attrs." WHERE id = ".$row_id."";
            $query1 = $db2->real_escape_string($query1);

            if ($mysqli->query($query1)){
                printf("%d Row inserted.\n", $mysqli->affected_rows);
                echo "<br>";
            }else{
                echo "Update failed. <br>";
            }
        }
    }else{
            echo "<br> Sadly the Select query failed.";
    }
        $db->close();
        $db2->close();
        ?>

[vodka00]

Thank you sir. I will try it when I get home

[alongub]

UPDATE store_items SET store_items.attrs = LEFT(atrs, LENGTH(attrs) - 1) + ', "lifetime": 0.5, "width": 15, "endwidth": 3, "fadelength": 5 }' WHERE store_items.type = 'equipment'

[Arrow768]

Originally Posted by alongub UPDATE store_items SET store_items.attrs = LEFT(atrs, LENGTH(attrs) - 1) + ', "lifetime": 0.5, "width": 15, "endwidth": 3, "fadelength": 5 }' WHERE store_items.type = 'equipment'

Thats what I am calling efficient !
around 70 lines of code scaled down to a single line. (Thats 1,4% of the php code)

[vodka00]

Originally Posted by alongub UPDATE store_items SET store_items.attrs = LEFT(atrs, LENGTH(attrs) - 1) + ', "lifetime": 0.5, "width": 15, "endwidth": 3, "fadelength": 5 }' WHERE store_items.type = 'equipment'

This set all attrs fields that were in equipment to 0.

[winterghost]

Originally Posted by vodka00 This set all attrs fields that were in equipment to 0.

Did you back up your db prior to running the code?

If not, you're probably gonna have to re-install the JSON for the type or re-do them manually (which would have the bonus of negating the need for SQL code/whatever to alter the attrs).

[vodka00]

Originally Posted by winterghost Did you back up your db prior to running the code? If not, you're probably gonna have to re-install the JSON for the type or re-do them manually (which would have the bonus of negating the need for SQL code/whatever to alter the attrs).

Yeah, I backed it up and restored it already.

[winterghost]

Originally Posted by vodka00 This set all attrs fields that were in equipment to 0.

Yeah I checked it in SQL fiddle. Soon as I try to place alongub's SQL code in, this error appears:

Schema Creation Failed: Data truncation: Truncated incorrect DOUBLE value: ', "lifetime": 0.5, "width": 15, "endwidth": 3, "fadelength": 5, }':

From what I could see, the + operator is replaced by CONCAT in MySQL? So for a MySQL database the correct SQL statement would be:

UPDATE store_items SET attrs = CONCAT(LEFT(attrs, Length(attrs) - 1), ', "lifetime": 0.5, "width": 15, "endwidth": 3, "fadelength": 5, }') WHERE type = 'equipment';

That would appear to work - check the SQL Fiddle(http://sqlfiddle.com/#!2/666ae/1)

[alongub]

Originally Posted by winterghost Yeah I checked it in SQL fiddle. Soon as I try to place alongub's SQL code in, this error appears: Schema Creation Failed: Data truncation: Truncated incorrect DOUBLE value: ', "lifetime": 0.5, "width": 15, "endwidth": 3, "fadelength": 5, }': From what I could see, the + operator is replaced by CONCAT in MySQL? So for a MySQL database the correct SQL statement would be: UPDATE store_items SET attrs = CONCAT(LEFT(attrs, Length(attrs) - 1), ', "lifetime": 0.5, "width": 15, "endwidth": 3, "fadelength": 5, }') WHERE type = 'equipment'; That would appear to work - check the SQL Fiddle(http://sqlfiddle.com/#!2/666ae/1)

Yup, sorry, I'm used to SQL Server