Get email from a varchar field (in the BD) and then update

4

I have the following data recorded in my column:

As you can see, it's a varchar and one I can record whatever I want, the idea is to create an application in php , that only take me inside the varchar the email , and then update the tuple ...

This is what I have so far:

        $listaDeCorreos  = mysql_query("SELECT EMAIL, iden FROM planilla", $db);



        $cadenaEntrada = $listaDeCorreos;
        $patron = '/[a-z0-9_\-\+\.][email protected][a-z0-9\-]+\.([a-z]{2,4})(?:\.[a-z]{2})?/i'; 
        preg_match_all($patron, $cadenaEntrada, $matches);
        echo $matches[0][0]; 

As far as I understand, these lines take only email within varchar , but I need advice to perform the part where the value taken in $ matches is rescued and update the tuple with the correct data ...

Table structure:

 CREATE TABLE planilla (
 EMAIL VARCHAR(40) DEFAULT NULL,
 iden MEDIUMINT(9) NOT NULL AUTO_INCREMENT
 PRIMARY KEY (iden)
 )
    
asked by Francisco Acevedo 11.09.2018 в 20:03
source

2 answers

6

From what I understand, you already have a database with emails which are not all persé mails, but instead you come with more straw and you want to debug that base, if so, you need a lot of meat to your code, I'll leave you a example of how your script would work. I leave you commented on the code to guide you

    <?php
//Primero te debes conectar a tu base de datos con tus credenciales
$db = mysqli_connect('localhost', 'root', '', 'COLLINS');

//Validamos que se haya creado la conexión
if( mysqli_connect_errno() ){
  echo 'Falló la conexión a la base de datos. ' . mysqli_connect_error();
}

else{
  //Preparamos tu query y le ponemos Alias a tus campos
  $query = "SELECT EMAIL AS 'MAIL', iden AS 'ID' FROM planilla";
  $result = mysqli_query( $db, $query );
  //Maneja los errores de consulta o retorno de registros
  if( !$result ){
    echo 'La consulta está mal construida' . '<br>';
  }

  else{
    if( mysqli_num_rows( $result ) <= 0){
      echo 'La consulta no retornó información' . '<br>';
    }

    else{
      //Creas la expesión regular
      $regexp = '/[a-z0-9_\-\+\.][email protected][a-z0-9\-]+\.([a-z]{2,4})(?:\.[a-z]{2})?/i';

      //Ciclamos los resultados y sacamos los datos
      while( $row = mysqli_fetch_array( $result ) ){
        //Suponiendo que el ID es un entero, si no quitale el (int)
        $id = (int) $row['ID'];
        $text = $row['MAIL'];

        preg_match_all( $regexp, $text, $matches );
        //Ahora validamos que existan resultados y coincidencias
        if( !empty( $matches[0][0] ) ){
          //Si no está vacío entonces actualizas con lo que te regresó el regex
          $subquery = "UPDATE planilla SET EMAIL = '".$matches[0][0]."'
          WHERE iden = " . $id;
          mysqli_query( $db, $subquery );

          //Ejecutas la consulta y validas que se haya actualizado
          if( mysqli_affected_rows( $db ) <= 0){
            echo 'No fue posible actualizar el campo con ID ' .  $id . ' el nuevo valor: ' . $matches[0][0] . '<br>';
          }

          else{
            echo 'Se actualizó correctamente el valor: ' . $matches[0][0] . '<br>';
          }
        }

        else{
          //Si está vacío entonces mandar error
          echo 'El mail ' . $text . ' no cumplió con la expresión regular' . '<br>';
        }
      }

      //Liberas memoria (Como buen programador) y cierras conexión
      mysqli_free_result($result);
      mysqli_close($db);
    }
  }
}
 ?>

I LEAVE YOU THE IMAGES OF HOW YOU ARE WORKING

    
answered by 13.09.2018 / 21:24
source
2

To start you can read a little about regular expressions , here a good tutorial to get started.

With regard to the question itself, if it is not in your power to restructure the database to be able to work with separate fields, the only option you have is to analyze the text in search of a pattern that matches an email, and this is why I recommend a reading about regular expressions.

Then, a little more oriented on the subject, you can research the preg_match_all used to make a global comparison of a regular expression in a text string. Finally, you can start using it to matte emails in the values of your column email , for example:

<?php 
    $cadenaEntrada = 'maria andrea [email protected]'; //el valor del campo email
    $patron = '/[a-z0-9_\-\+\.][email protected][a-z0-9\-]+\.([a-z]{2,4})(?:\.[a-z]{2})?/i'; //un patrón ejemplo para matchear correos electrónicos
    preg_match_all($patron, $cadenaEntrada, $matches);
    echo $matches[0][0]; // primera coincidencia del patrón en la cadena de entrada.
?>
  

Examples:

$cadenaEntrada = 'maria andrea [email protected]'
echo $matches[0][0]; //[email protected]
---
$cadenaEntrada = 'Josem_ gtgmail.com [email protected], [email protected]';
echo $matches[0][0]; //[email protected]
echo $matches[0][1]; //[email protected]
    
answered by 11.09.2018 в 21:07