Creating tables with PHP

Navigation
3

Expensive;

I have a problem and I need a little help. I have a page in html that contains some radio buttons. By clicking on a radio button and then the submit button, it redirects to a page in PHP that captures the value of the radio and with this value, it enters a for, and within the for has the function mktime, and with the value of the radius button is adding months. Below is the code for better understanding: 

<?php

  // Dados da conexão com o banco de dados

define('SERVER', 'localhost');
define('DBNAME', 'contas');
define('USER', 'root');
define('PASSWORD', '');


$valor = $_POST['radios'];
$i = 1;

for ($i; $i <= $valor; $i++) {
$result = date('Y-m-d',mktime(0,0,0,date('m')+$i));

  $opcoes = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8');
  $conexao = new PDO("mysql:host=".SERVER."; dbname=".DBNAME, USER, PASSWORD,                '$opcoes);'

  $sql = "CREATE TABLE ".$result." (id int not null   auto_increment,nome_boleto          VARCHAR(70) not null,data_inclusao date not null,vencimento date not    null,valor_boleto VARCHAR(20) not null,mensal VARCHAR(4) not null,descricao    VARCHAR(50) not null,pago VARCHAR(4) not null,data_pagamento date not    null,forma_pagamento VARCHAR(13) not null)";
  $stm = $conexao->prepare($sql);
  $stm->execute();




}

?>

But it does not execute the SQL code, it does not create the tables in the bank accounts. If I put an "echo" at the end returning the variable ($ result), the return occurs right.

Ps: If I put some incorrect command in the $ sql variable to force an error, it does not return anything, I believe that this block is not running, being ignored.

With the help of rray, asking to start the stm return, I got a syntax error: 

Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax;    check the manual that corresponds to your MariaDB server version for the right syntax to use near '2016-10-14 (id int not null auto_increment,nome_boleto VARCHAR(70) not null,data' at line 1 ) Dados inseridos2016-10-14

MariaDB is not accepting a table with this format 2016-10-14. I tried not to accept table name with date format or numeral.

By putting the variable with "crase" escaping it, according to the orientation of the rray, I solved my problem.

Thanks for the personal help!

(living and learning)

    
asked by anonymous 14.09.2016 / 21:36

1 answer

4

As the answer from @rray explains the syntax error, however, it is sometimes not possible to create tables in the database dynamically, just manually (I said sometimes), this would make your application susceptible to crashes.

Another situation is that I noticed that you already want to create a table for each month of the year, do you really think this is necessary? I mean you do not have to create anything in advance, it would be best to actually pull the only things out there, I'll explain further below.

If the idea is to create tables to organize something by date then I recommend using the column data_inclusao of the table by creating a single table something like: 

CREATE TABLE MINHA_TABELA
(
    id              INT NOT NULL auto_increment,
    nome_boleto     VARCHAR(70) NOT NULL,
    data_inclusao   DATE NOT NULL,
    vencimento      DATE NOT NULL,
    valor_boleto    VARCHAR(20) NOT NULL,
    mensal          VARCHAR(4) NOT NULL,
    descricao       VARCHAR(50) NOT NULL,
    pago            VARCHAR(4) NOT NULL,
    data_pagamento  DATE NOT NULL,
    forma_pagamento VARCHAR(13) NOT NULL
)

So if I'm filtering by date I'd just use this: 

SELECT * FROM MINHA_TABELA WHERE data_inclusao = 'YYYY-MM-DD';
  

Switch YYYY-MM-DD with numbers like 1999-11-22

And if it is today use CURDATE : 

SELECT * FROM MINHA_TABELA WHERE data_inclusao = CURDATE();

Well, you can create simpler query queries, for example: 

SELECT * FROM table WHERE data_inclusao BETWEEN '2015-MM-DD' AND CURDATE()

Creating a table for each day seems a bit overkill, if you are trying to achieve something like performance I recommend at most to create one table per year (maybe there is a lot of variation).

Monthly Query: 

# Consulta mês 11 de 2016
SELECT * FROM MINHA_TABELA WHERE data_inclusao BETWEEN '2016-11-01' AND '2016-11-30';

# Consulta mês 12 de 2016
SELECT * FROM MINHA_TABELA WHERE data_inclusao BETWEEN '2016-12-01' AND '2016-12-31';
    
14.09.2016 / 22:36
Change column names of tables created by Asp.Net Identity How to generate an .o of the implementation of a .cpp class without the main function in g ++?