Jamilah Jamilah - 21 days ago 10
MySQL Question

How to check each rows for DATE and DATETIME in PHP MySQL?

I have three sets of

datetime
type with two columns
dtfrom
and
dtto
in mysql database

row 1 : dtfrom 2016-11-11 , dtto 2016-11-11
row 2 : dtfrom 2016-11-12 , dtto 2016-11-12
row 3 : dtfrom 2016-11-13 , dtto 2016-11-13


The problem is the output cannot show "Duplicate date" if I input the same date in database.

It always show "Data not found !"

Can somebody help here?

<?php
$connect = mysqli_connect("localhost", "root", "root", "database");
global $connect;

if(isset($_POST['Submit']))
{
$user_id = $_POST['user_id'];
$dtfrom = $_POST['dtfrom'];
$dtfrom_user = strtotime($dtfrom);
$dtto = $_POST['dtto'];
$dtto_user = strtotime($dtto);

$sql = "SELECT * FROM table WHERE user_id='{$user_id}' AND dtfrom >= '{$dtfrom_user}' AND dtto <= '{$dtto_user}'";
$run = mysqli_query($connect, $sql);
if($run && mysqli_num_rows($run) > 0 )
{
while($result = mysqli_fetch_assoc($run))
{
echo "Date duplicate";
}
mysqli_free_result($run);
}
else
{
echo "Date not found !";
}
}
?>
<form action="datetime.php" method="post">
<table>
<tr>
<td><i class="fa fa-unlock-alt"></i> </td>
<td>User ID : </td>
<td><input type ="text" name="user_id" size="30"></td>
</tr>
<tr>
<td><i class="fa fa-unlock-alt"></i> </td>
<td>Date from : </td>
<td><input type ="date" name="dtfrom" size="30"></td>
</tr>
<tr>
<td><i class="fa fa-unlock-alt"></i> </td>
<td>Date to : </td>
<td><input type ="date" name="dtto" size="30"></td>
</tr>
</table>

<p><input class="btnSuccess" type ="submit" name="Submit" value="Submit"> </p>
</form>

Answer

Try to check error . Use echo mysqli_error($con); instead echo "Date not found !";

And Change your Query to

 $user_id        = $_POST['user_id'];
 $dtfrom         = $_POST['dtfrom'];
 $dtfrom_user    = strtotime($dtfrom);
 $dtto           = $_POST['dtto'];
 $dtto_user      = strtotime($dtto);


 $dtfrom_user  = date("Y-m-d",$dtfrom_user );
 $dtto_user  = date("Y-m-d",$dtto_user);

 $sql = "SELECT * FROM table WHERE user_id='".$user_id."'  AND dtfrom >= '".$dtfrom_user."' AND dtto <= '".$dtto_user."'";
Comments