Creating tables with PHP

Question

Expensive;

I have a problem and need a little help. I have an html page that contains some radio Buttons. When clicking on a radio button and then on the send button, it redirects to a PHP page that captures the radio value and with this value, enters a for, and inside the for has the mktime function, and with the radio button value adds the months. Below put 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();




}

?>

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

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

---

With the help of rray, asking to print the return of stm, I arrived at 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. So it does not accept table name with date or numeral format.

Putting the variable with "crase" escaping it, as the orientation of rray , I solved my problem.

Grateful for the personal help!

(living and learning)

Answer 1

According to the response of @rray explains the problem of syntax error, however sometimes it is not possible to create tables in the database dynamically, only manually (I said the times), it would make its application susceptible to failures.

Another situation is that I noticed that you already want to create a table for each month of the year, do you think this really necessary? I mean you don’t need to create anything beforehand, the best would be to really pull the only things that exist, 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 table 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 you’re going to filter by date I would just use this:

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

exchange YYYY-MM-DD by the numbers as 1999-11-22

And if it’s today, wear it CURDATE:

SELECT * FROM MINHA_TABELA WHERE data_inclusao = CURDATE();

So 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 of an exaggeration, if you are trying to achieve something like performance I recommend at most creating a table per year (maybe, there is a lot of variation).

Consultation per month:

# 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';