Is it possible to change a word from one string to another in the database via php?

Asked

Viewed 124 times

3

I have been working on a method that will fetch all dates containing "2014" and replace that value with "2016" without changing the rest of the date. I’ve been able to return all columns containing "2014" but I don’t know how to change only this value in all strings. I appreciate any suggestion.

The code I executed was the following:

        <?php
                $query = "SELECT * ";
                $query .= "FROM voo ";

                $result = mysqli_query($connection, $query);
                if(!$result) {
                    die ("Database query failed.");
                }

            //while($row = mysqli_fetch_row($result)){
                //  if (strpos($result,'2014') !== false) {
                        $query = "UPDATE voo SET DataPartida = DATE_ADD(DataPartida, ";
                        $query .= "INTERVAL 2 YEAR) WHERE year(DataPartida) = '2014'";
                        var_dump($row);
                    //}
                    echo "<hr />";
                //}
        ?>

Example: "2014-01-02 18:00:00" Intended to stay "2016-01-02 18:00:00"

php mysql string

  • Could you do something with regex.

  • I looked for this method (PREG_REPLACE_EVAL), and it was just something I was looking for, but php.net says it will no longer be compatible with the browsers: http://php.net/manual/en/reference.pcre.pattern.modifiers.php

  • @angelfmf the function is not obsolete, what is obsolete is the modifier e. "The modifier /e is obsolete. Use preg_replace_callback() instead. See documentation PREG_REPLACE_EVAL for additional information on security risks." And there is no why the browser does not support, because the function runs on the server and not on the browser.

  • 1

    @angelfmf The field in the database is of what type? By far, regex is the worst option in this case.

  • 3

    if the data is in a format suitable for the datetime type, modify the column for the datetime type, then you can apply a single query to update the data. There is no need to use php... but if you want to query php there is also no problem. But it’s completely unnecessary to loop all the records and make the replacements with php.

  • The Column is already type datetime, I checked in the database.

Show 1 more comment

1 answer

2


The format of the field seems to be of type date(datetime, timestamp etc), in this case use the functions of date manipulation as date_add() adding a date period.

Example

SELECT DATE_ADD(now(), INTERVAL 2 YEAR)

Your code should look something like this:

update voo set data = date_add(data, interval 2 year) where year(data) = '2014'

Example - sqlfiddle

$query = "UPDATE voo SET
             DataPartida = DATE_ADD(DataPartida,INTERVAL 2 YEAR)
          WHERE year(DataPartida) = '2014'";

$result = mysqli_query($connection, $query);

if(!$result) {
   die(mysqli_error($connection);
}

  • Thank you very much for the reply and the excellent suggestion, I tried to follow your example but I was not successful. I’ve been around the code a long time and tried to make the necessary changes. I only started PHP a few weeks ago, so it’s still difficult to find a solution for everything.

  • @angelfmf, run Update straight on mysqli_query does not need while as explained by Daniel Omine

  • I’ve run without the loop but there’s still no change

  • @Angelfmf, in your update missed calling mysqli_query($Connection, $query)

  • I’m sorry, I can’t figure out how to call mysqli_query, I thought it was already called above, but it’s $result right, once it’s set to that value. Can you just show me where to call? Thanks again

  • Thank you, I already managed to execute the code successfully, it was very helpful. I was calling mysqli_query before queries, hence do not run

Show 1 more comment

Browser other questions tagged php mysql string

You are not signed in. Login or sign up in order to post.