Example table
First let’s base the following table created in mysql:
+-------------------------+
|datetime_inicio datetime |
+-------------------------+
|datetime_fim datetime |
+-------------------------+
Fixing the current code
And now make your code work (before suggesting any improvement!). Your code presented only a small error when executing. Apparently the variable $alterar_hora was undefined (I imagine you wanted to use $inserir_data). Making this change, the code was like this:
<?php
//formato ano/mes/dia ou YYYY/mm/dd
$_POST['inserir_data'] = '2018/1/1';
$inserir_data = nl2br( addslashes($_POST['inserir_data']));
//formato ano/mes/dia ou YYYY/mm/dd
$_POST['inserir_turno'] = '10:30-11:40';
$inserir_turno = nl2br( addslashes($_POST['inserir_turno']));
$turno_separado = explode('-', $inserir_turno);
$hora_inicio = "$inserir_data $turno_separado[0]";
$hora_final = "$inserir_data $turno_separado[1]";
var_dump($hora_inicio);
var_dump($hora_final);
$conexao = mysqli_connect('host', 'usuario', 'senha', 'nome_banco');
mysqli_query($conexao, 'insert into teste (datatime_inicio, datetime_fim) values (\''
. $hora_inicio . '\',\'' . $hora_final .'\')');
//exibir erros na execução da instrução anterior (remover em produção).
echo mysqli_error($conexao);
What is different from your code are the escapes " '" to allow a single quotation mark to be inside another single quotation mark, since strings need to be in quotation marks. After some executions of the above code, the table in the database will be similar to:
+----------------------+--------------------+
|datetime_inicio |datetime_fim |
+----------------------+--------------------+
|2018-01-01 10:30:00 |2018-01-01 11:40:00 |
|2018-01-01 10:30:00 |2018-01-01 11:40:00 |
|2018-01-01 10:30:00 |2018-01-01 11:40:00 |
+----------------------+--------------------+
Improvements
Of course you could use something similar to that tag <input type="datetime-local" name="bdaytime"> to get the complete datetime, and only validate on the server. Although this does not work in all browsers (but gives to use plugins that allow this).
Perhaps it would be better to always work with DATETIME in mysql instead of VARCHAR, and with the Datetime class in PHP to extract with the desired format. Aside from that the shifts would look better in different columns, so probably part of the logic would be solved in the query.
– Guilherme Nascimento
But you developed the database? I think it’s going the way of the stones, and mysql and php have features for date and time that solve a lot of things without having to kill themselves like this, aside from the problems that you will avoid with errors of calculations. But do as you wish, I’m just making a suggestion ;)
– Guilherme Nascimento
Shider, don’t take this the wrong way, but it’s easier the way you did it because it’s already done and because it’s the only way you know it, if you know in depth the functioning of functions that work with real time and not with CHAR or STRING, will realize that in fact you are just trying to recreate the wheel needlessly. It’s not because you only know one way that it makes you the best, working with das thus can be a big problem. I wish you luck with that and if you want to take a look at the PHP "Date" classes and see the examples maybe in the future you can refactor your code. Happy 2018
– Guilherme Nascimento
php: http://php.net/manual/en/book.datetime.php - mysql: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html - Unix Time: https://en.wikipedia.org/wiki/Unix_time and lastly questions for time comparison: https://answall.com/search?q=comparar+datas+%5Bphp%5D e https://answall.com/search?tab=votes&q=comparar%20datas%20%5bmysql%5d
– Guilherme Nascimento
It is not input, the DATE type will only exist in mysql, the PHP Datetime name class vc will use to compare strings (your input) with the mysql format of the DATE type fields.
– Guilherme Nascimento
I wanted to provide an example, but I’m not sure what you’ve done so far, it’s kind of hard to imagine where to start, because initially your problem is something else, even if it’s caused by working strings as dates. There is no magic solution, some things will have to remake or simply risk what you have done so far and in the future when you better understand the concept of TIME remake/ remake your codes.
– Guilherme Nascimento