How to put condition in MySQL query

Question

How to put condition in MySQL query

Navigation

#1 by (1 votes)

1

I have the following query that works the way it should.

$result2 = mysql_query("SELECT * FROM datos WHERE intervalo in ($in) and fecha_hora >= '$dateini' AND fecha_hora <= '$datefin' and archivo in ($arch) and hora between $horain and $horafin", $db2);

My question is, how can I put a condition where intervalo when I receive $in e $in have the value of 15sec this sets the hours of both start as ending in 00:00:00 and otherwise if it is another value different from 15sec that executes the query that was put at the beginning.

mysql sql php

asked by Houdini 29.08.2016 в 22:53

source

1 answer

Jquery DataTables with column image fails the search Modify and delete records from a PHP table

score 1 · Answer 1

I understand that the only thing you want to change from the query is the value of $horain and $horafin , depending on the value of $in .

I assume that the value of $in is expressed in seconds and is a single value (it is not an array with values or string with a list of values separated by commas).

In that case, doing intervalo in ($in) can be replaced by intervalo = $in .

I also assume that the date string is in DD / MM / YYYY format (ex: 12/31/2015).

One possible solution:

//Si $in es igual a 15, la hora inicio y fin se ponen en 00:00:00
if($in == 15){
    $horaInicial= '00:00:00';
    $horaFin= '00:00:00';
}else{
    $horaInicial= $horain;
    $horaFin= $horafin;
}


$query= "SELECT * FROM datos ";
$query.= " WHERE intervalo = $in and "; 
$query.= " fecha_hora >= STR_TO_DATE('$dateini','%d/%m%/%Y) AND ";
$query.= " fecha_hora <= STR_TO_DATE('$datefin','%d/%m%/%Y) and ":
$query.= " archivo in ($arch) and ";
$query.= " hora between STR_TO_DATE($horaInicial,'%h:%i:%s) and STR_TO_DATE($horaFin,'%h:%i:%s)";

$result2 = mysql_query($query, $db2);

The STR_TO_DATE function transforms a string into a datetime, date or time type depending on the past format. I use it because not always the string match to date, time or datetime works according to what one would expect. More info about the function here .

On the other hand, just for informative purposes, mysql_query is part of the original MySQL API, which since PHP 5.5 was declared obsolete. From the PHP manual it can be read:

Warning

This extension was declared obsolete in PHP 5.5.0 and deleted in PHP   7.0.0. The MySQLi or PDO_MySQL extensions should be used instead. See also the MySQL guide: choose an API and its P + F   related for more information. Alternatives to this function   include:
mysqli_query()
PDO::query()