Add MySQL column with increment not to repeat

Question

Add MySQL column with increment not to repeat

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

My intention is to get a variable via POST and change the value of it.

$vem = $_POST['nome'];

if (isset($vem)){

    $vem = 1;
    $vem++;
    $vem = 'campo'.$vem;
}

I will add as a table column within that change

$vem = $_POST['nome'];

if (isset($vem)){

$vem = 1;
    $vem++;
    $vem = 'campo'.$vem;

$sqli = mysql_query ("ALTER TABLE  grafica ADD $vem varchar(255)")  or die(mysql_error());
if($sqli){

     echo 'salvou '.$vem;
 }

}

So far so good ok ! What I need is for it to understand that it already has the column campo.2 and add the column campo.3

php mysql

asked by anonymous 08.06.2018 / 19:54

2 answers

2

update 08/06/2018 22:59

This part is totally expendable and is of no use

$vem = 1;
    $vem++;
    $vem = 'campo'.$vem;

For the reasons listed below

It is quite obvious that as it stands, the variable $vem will always be campo2

If you want string column names, there is no need to type any number in the field to be sent via post. Example, suppose that the number 8 has been typed and that we have the columns field1, field.2 and field.3 in the table, so the next column to be created is field.4 and the number sent via post was not used for nothing.

This variable $vem from $_POST['nome']; can be, for example, a prefix of the names of the columns to be created, allowing you to create columns with several prefixes and sequentially, such as field1, field.2 , etc ... or qqnome.1, qqnome.2, etc ... and such

It is not common to name columns with . and this can cause errors if not handled correctly in the ALTER TABLE statement. In this case it is imperative to wrap the column name with inverted quotes that are nothing more than the accent crase of your keyboard. More details on Schema Object Names

$sql = "ALTER TABLE $table ADD '$nomeProxColuna' varchar(255)";

Proposed code

with Mysqli because mysql_* has been deprecated and Column names without .

$vem = $_POST['nome'];

if (isset($vem)){

    $hostname="localhost";  
    $db_user="USUARIO";  
    $db_pw="SENHA";  
    $db_name = "nome_DB";  

    $con = mysqli_connect('localhost',$db_user,$db_pw,$db_name);

    //nome da tabela
    $table = 'artigos';

    /**
    * Obtem os nomes das colunas com prefixo = $vem (vindo do post)
    **/

      $sql = 'DESCRIBE '.$table;
      $result = mysqli_query($con, $sql);

      $rows = array();
      while($row = mysqli_fetch_assoc($result)) {
         //cria array com nomes das colunas que contem a palavra $vem
         $texto = $row['Field'];
         if (strstr($texto, $vem)){;
            $rows[] = $row['Field'];
         }

      }

      /********** caso haja nomes de colunas com prefixo vindo do post
       prepara o nome da proxima coluna *****************************/

      if($rows){
          /*****retorna maior valor do array dado pela parte numérica
          já que a parte anterior à numérica é/são igual(is) *******/

          $maior = max($rows);

         /********próximo nome da coluna********/

         //comprimento da variavel vindo do post
         $len=strlen($vem);

         //pega a parte numérica do nome dado pela variável $maior definida acima
         $proxNum = substr($maior,$len)+1;

         //cria o nome da próxima coluna
         $nomeProxColuna = $vem.$proxNum;

      }else{ 

         //se não existem colunas com prefixo vindo do post cria a primeira

         $nomeProxColuna=$vem."1";

      }

    //  altera a estrutura da tabela acrescentando campos

    $sql = "ALTER TABLE $table ADD '$nomeProxColuna' varchar(255)";

    $incluirColuna = $con->query($sql);
    if ($incluirColuna) {
      echo "coluna criada com sucesso\n";
    } else {
      echo "deu zebra: " . $con->error . "\n";
    }     

}

If the column name prefixes will always be the same, just put it as the value of the $vem variable. Example $vem="campo";

and the first few lines of code, for example, are

if (isset($_POST['criarColuna'])) {

   $vem="campo";

and in the form only one input / button, example <input type="submit" name="criarColuna" value="Criar coluna">

08.06.2018 / 21:07

Sending a POST request without form Difference between dates with condition (only count certain month)

score 1 · Accepted Answer

First of all, I have some points to put:

Column names with . are not common

If a X table has several fields in this format ( campo1 , campo2 , campo3 ), consider normalizing and creating a X_Campo table, where you would have a id , a key to table X and a column with the value itself.

Try not to use the mysql_* functions, instead use at least mysqli_ . They have been discontinued ...

My solution would be this here:

<!DOCTYPE html>
<html>
<pre>
<?php
// conecta no bd
$mysqli = new mysqli("localhost", "root", "", "test");
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;  
} else {
  echo 'conexão: ' . $mysqli->host_info . "\n";

  // primeiro, precisamos saber dos campos da tabela. A query não precisa retornar nada, só precisamos do metadata inicialmente.
  $tableName = 'tabela';
  $res = $mysqli->query("SELECT * FROM tabela WHERE 0");
  if ($res) {
    $numeroNovoCampo = 1;
    $prefixoNovoCampo = 'campo';
    $fields = $res->fetch_fields();
    echo "verificando nomes dos campos pelo prefixo '$prefixoNovoCampo'...\n";
    foreach ($fields as $field) {
      printf("%s\n", $field->name);
      $pos = strpos($field->name, $prefixoNovoCampo);
      if ($pos !== false) {
        $apos = substr($field->name, strlen($prefixoNovoCampo) + $pos);
        echo "encontrou o prefixo, verifica o que está depois ($apos)\n";
        $numero = intval($apos);
        $numeroNovoCampo = max($numeroNovoCampo, $numero+1);
      }
    }
    $sql = "ALTER TABLE $tableName ADD $prefixoNovoCampo$numeroNovoCampo varchar(255)";
    echo "criando campo com nome $prefixoNovoCampo$numeroNovoCampo: $sql\n";
    $altTab = $mysqli->query($sql);
    if ($altTab) {
      echo "campo criado com sucesso\n";
    } else {
      echo "deu um erro? " . $mysqli->error . "\n";
    }
    $res->close();
  } else {
    echo "erro na consulta\n";
  }
}

$mysqli->close();

?>
</pre>
</html>

I created a table to test here and I put some campoN just to see how it would look, and I had this output:

conexão: localhost via TCP/IP
verificando nomes dos campos pelo prefixo 'campo'...
id
id_usuario
usuario
momento
random
valor
campo1
encontrou o prefixo, verifica o que está depois (1)
campo2
encontrou o prefixo, verifica o que está depois (2)
campo3
encontrou o prefixo, verifica o que está depois (3)
criando campo com nome campo4
campo criado com sucesso