I have a problem with DATE_FORMAT()

[erezvol]

Hello,
I coded a page which displays trading data
Here is a screenshot of a typical page

When a user clicks on hide, the text “hide” changes into "show and this trade is not included in the charts or in the summary table. a message shows up letting the user that a trade is hidden, and there is a button which will show all hidden trades

"
So far, all works well.
Here is the code for the variables creating the data

//Get the data for balance graph
$data_b = profit_loss_single_couple($couple, $user_id);
//Get the data for trades counter graph
$data_c = p_l_ֹcounter_single_couple($couple, $user_id);
//Get the data for line graph
$data_l = get_Single_Couple_profit($couple, $user_id);
//var_dump($data_l);
//die();
//Get the data for summary table
$table_data = closed_pos_single_couple($couple, $user_id);
//Get the data for closed positions table
$positions_couple = closed_pos_table_single_couple($couple, $user_id);
// Count Couple hidden trades
$hidden_trades_couple = count_hidden_pos_couple($couple, $user_id);

here is the function which exracts the table data from the DB

function closed_pos_table_single_couple($item, $user_id)
{
	global $db;

	try
	{
	
	$sql = "SELECT id, user_id, ticket, type, size, item, o_time, o_price, c_time, c_price, profit, hideshow
			FROM `data`
			WHERE  `user_id` = :user_id  
			AND `item` = :item 
			ORDER BY `c_time` ASC";

			$stmt = $db->prepare($sql);
			$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $stmt->bindParam(':item', $item, PDO::PARAM_STR);
			$stmt->execute();

			if($stmt->rowCount() == 0)
		       return 0;
		    else
			   return $stmt->fetchAll(PDO::FETCH_ASSOC);
	}
	    catch(Exception $e) 
	{
	   echo $e;
	   die();
	   return false;        
	}

}

And here is the code for summary table function

function closed_pos_single_couple($item, $user_id)
{	
	global $db;
	
	try
	{
		$sql = "SELECT  `profit`, `hideshow`, `item`, `user_id`, `o_time`
				,SUM(CASE WHEN `profit` >= 0 THEN `profit` ELSE 0.0 END) AS TotalProfit
				,SUM(CASE WHEN `profit` < 0 THEN `profit` ELSE 0.0 END) AS TotalLoss
				,SUM(CASE WHEN `profit` >= 0 THEN 1 ELSE 0.0 END) AS CountProfit
				,SUM(CASE WHEN `profit` < 0 THEN 1 ELSE 0.0 END) AS CountLoss
				,AVG(CASE WHEN `profit` >= 0 THEN `profit` ELSE 0.0 END) AS AveProfit
				,AVG(CASE WHEN `profit` < 0 THEN `profit` ELSE 0.0 END) AS AveLoss
				, MAX(profit), MIN(profit), MIN(`o_time`) AS StartDate, MAX(`o_time`) AS EndDate
				,SUM(CASE WHEN `profit` >= 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS BuyProfit
				,SUM(CASE WHEN `profit` < 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS BuyLoss
				,SUM(CASE WHEN `profit` >= 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS SellProfit
				,SUM(CASE WHEN `profit` < 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS SellLoss
				,SUM(CASE WHEN `profit` >= 0 AND type LIKE 'buy%' THEN 1 ELSE 0.0 END) AS CountBuyProfit
				,SUM(CASE WHEN `profit` < 0 AND type LIKE 'buy%' THEN 1 ELSE 0.0 END) AS CountBuyLoss
				,SUM(CASE WHEN `profit` >= 0 AND type LIKE 'sell%' THEN 1 ELSE 0.0 END) AS CountSellProfit
				,SUM(CASE WHEN `profit` < 0 AND type LIKE 'sell%' THEN 1 ELSE 0.0 END) AS CountSellLoss
				,AVG(CASE WHEN `profit` >= 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS AvgLongProfit
				,AVG(CASE WHEN `profit` < 0 AND type LIKE 'buy%'THEN `profit` ELSE 0.0 END) AS AvgLongLoss
				,AVG(CASE WHEN `profit` >= 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS AvgShortProfit
				,AVG(CASE WHEN `profit` < 0 AND type LIKE 'sell%'THEN `profit` ELSE 0.0 END) AS AvgShortLoss
				,MAX(CASE WHEN `profit` >= 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS MaxBuyProfit
				,MIN(CASE WHEN `profit` < 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS MaxBuyLoss
				,MAX(CASE WHEN `profit` >= 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS MaxSellProfit
				,MIN(CASE WHEN `profit` < 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS MaxSellLoss
				FROM `data` 
				WHERE  `hideshow` = 'hide'
				AND `user_id` = :user_id
				AND `item` = :item";
		$stmt = $db->prepare($sql);
		$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
        $stmt->bindParam(':item', $item, PDO::PARAM_STR);
		$stmt->execute();
				
		if($stmt->rowCount() == 0)
		return 0;
		else
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
		
	}
	catch(Exception $e) 
	{
	   echo $e;
	   die();
	   return false;        
	}
}

And here is the jquery which emphasizes the hidden trade

 $("td:contains('show')").css({"background-color":"#16e6f4", "font-weight":"bold"});
});

the problem is that I want to let users filter their results by dates, weekdays, hours.
Here is a screenshot of the table storing the filtering criteria

Here is the code which works with the data (with 2 lines which will display the data for this post

// Here a variable wil get user filtering data and will insert all filtering criteria into variables which will
// go to all functions

$filter_criteria = get_user_filter_criteria($user_id);

foreach($filter_criteria as $xrow =>$xval)
{
  $start_date = $xval['start_date'];
  $end_date = $xval['end_date'];
  $weekdays = $xval['weekdays'];
  $hour_start = $xval['hour_start'];
  $hour_end = $xval['hour_end'];
} // End foreach
var_dump($filter_criteria);
die();

and here is the relevant function

function get_user_filter_criteria($user_id)
{	
	global $db;
	
	try
	{
		$sql = "SELECT * FROM `filter` WHERE `user_id` = :user_id";
		$stmt = $db->prepare($sql);
		$stmt->bindParam(':user_id', $user_id, PDO::PARAM_STR);
		$stmt->execute();
		
		if($stmt->rowCount() == 0)
		return 0;
		else
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
		
	}
	catch(Exception $e) 
	{
	   return false;        
	}
}

Here is the var_dump results
C:\wamp64\www\AHopefully\pages\page_header.php:37: array (size=1) 0 => array (size=7) 'f_id' => string '28' (length=2) 'user_id' => string '30' (length=2) 'start_date' => string '2017-08-14' (length=10) 'end_date' => string '2022-06-09' (length=10) 'weekdays' => string '0,1,2,3,4,5,6' (length=13) 'hour_start' => string '00:00:00' (length=8) 'hour_end' => string '23:59:59' (length=8)

Now I add the variables to the functions and function calls:
these are for the table

function closed_pos_table_single_couple($item, $user_id, $start_date, $end_date, $hour_start, $hour_end, $days)
{
	global $db;

	try
	{
	
	$sql = "SELECT id, user_id, ticket, type, size, item, o_time, o_price, c_time, c_price, profit, hideshow
			FROM `data`
			WHERE DATE_FORMAT(`o_time`, '%Y-%m-%d') BETWEEN :start_date AND :end_date 
			AND TIME(`o_time`) BETWEEN :hour_start AND :hour_end
			AND WEEKDAY(`o_time`) IN ($days)
			AND `user_id` = :user_id
			AND `item` = :item 
			ORDER BY `c_time` ASC";

			$stmt = $db->prepare($sql);
			$stmt->bindParam(':item', $item, PDO::PARAM_STR);
			$stmt->bindParam(':user_id', $user_id, PDO::PARAM_STR);
			$stmt->bindParam(':start_date', $start_date, PDO::PARAM_STR);
			$stmt->bindParam(':end_date', $end_date, PDO::PARAM_STR);
			$stmt->bindParam(':hour_start', $hour_start, PDO::PARAM_STR);
			$stmt->bindParam(':hour_end', $hour_end, PDO::PARAM_STR);
			$stmt->execute();

			if($stmt->rowCount() == 0)
		       return 0;
		    else
			   return $stmt->fetchAll(PDO::FETCH_ASSOC);
	}
	    catch(Exception $e) 
	{
	   echo $e;
	   die();
	   return false;        
	}

}

$positions_couple = closed_pos_table_single_couple($couple, $user_id, $start_date, $end_date, $hour_start, $hour_end, $days);

And these are for the summary table

$table_data = closed_pos_single_couple($couple, $user_id, $start_date, $end_date, $hour_start, $hour_end, $days);

And

function closed_pos_single_couple($item, $user_id, $start_date, $end_date, $hour_start, $hour_end, $days)
{	
	global $db;
	
	try
	{
		$sql = "SELECT  `profit`, `hideshow`, `item`, `user_id`, `o_time`
				,SUM(CASE WHEN `profit` >= 0 THEN `profit` ELSE 0.0 END) AS TotalProfit
				,SUM(CASE WHEN `profit` < 0 THEN `profit` ELSE 0.0 END) AS TotalLoss
				,SUM(CASE WHEN `profit` >= 0 THEN 1 ELSE 0.0 END) AS CountProfit
				,SUM(CASE WHEN `profit` < 0 THEN 1 ELSE 0.0 END) AS CountLoss
				,AVG(CASE WHEN `profit` >= 0 THEN `profit` ELSE 0.0 END) AS AveProfit
				,AVG(CASE WHEN `profit` < 0 THEN `profit` ELSE 0.0 END) AS AveLoss
				, MAX(profit), MIN(profit), MIN(`o_time`) AS StartDate, MAX(`o_time`) AS EndDate
				,SUM(CASE WHEN `profit` >= 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS BuyProfit
				,SUM(CASE WHEN `profit` < 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS BuyLoss
				,SUM(CASE WHEN `profit` >= 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS SellProfit
				,SUM(CASE WHEN `profit` < 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS SellLoss
				,SUM(CASE WHEN `profit` >= 0 AND type LIKE 'buy%' THEN 1 ELSE 0.0 END) AS CountBuyProfit
				,SUM(CASE WHEN `profit` < 0 AND type LIKE 'buy%' THEN 1 ELSE 0.0 END) AS CountBuyLoss
				,SUM(CASE WHEN `profit` >= 0 AND type LIKE 'sell%' THEN 1 ELSE 0.0 END) AS CountSellProfit
				,SUM(CASE WHEN `profit` < 0 AND type LIKE 'sell%' THEN 1 ELSE 0.0 END) AS CountSellLoss
				,AVG(CASE WHEN `profit` >= 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS AvgLongProfit
				,AVG(CASE WHEN `profit` < 0 AND type LIKE 'buy%'THEN `profit` ELSE 0.0 END) AS AvgLongLoss
				,AVG(CASE WHEN `profit` >= 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS AvgShortProfit
				,AVG(CASE WHEN `profit` < 0 AND type LIKE 'sell%'THEN `profit` ELSE 0.0 END) AS AvgShortLoss
				,MAX(CASE WHEN `profit` >= 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS MaxBuyProfit
				,MIN(CASE WHEN `profit` < 0 AND type LIKE 'buy%' THEN `profit` ELSE 0.0 END) AS MaxBuyLoss
				,MAX(CASE WHEN `profit` >= 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS MaxSellProfit
				,MIN(CASE WHEN `profit` < 0 AND type LIKE 'sell%' THEN `profit` ELSE 0.0 END) AS MaxSellLoss
				FROM `data` 
				WHERE DATE_FORMAT(`o_time`, '%Y-%m-%d') BETWEEN :start_date AND :end_date 
			    AND TIME(`o_time`) BETWEEN :hour_start AND :hour_end
				AND WEEKDAY(`o_time`) IN ($days)
				AND `hideshow` = 'hide'
				AND `user_id` = :user_id
				AND `item` = :item";
		$stmt = $db->prepare($sql);
		$stmt->bindParam(':item', $item, PDO::PARAM_STR);
		$stmt->bindParam(':user_id', $user_id, PDO::PARAM_STR);
		$stmt->bindParam(':start_date', $start_date, PDO::PARAM_STR);
		$stmt->bindParam(':end_date', $end_date, PDO::PARAM_STR);
		$stmt->bindParam(':hour_start', $hour_start, PDO::PARAM_STR);
		$stmt->bindParam(':hour_end', $hour_end, PDO::PARAM_STR);
		$stmt->execute();
				
		if($stmt->rowCount() == 0)
		return 0;
		else
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
		
	}
	catch(Exception $e) 
	{
	   echo $e;
	   die();
	   return false;        
	}
}

now, the hidden trade wont show in the table

And when the show hidden trades button is clicked, it disappears but the trade wont show in the table and won’t be taken in the calculations for the page functions
Here is aresult of a query on the sql page in the DB

What is the problem here ?
What did I do wrong ?

[mabismad]

Just to summarize, when you are retrieving all the date range data, i.e. not filtering by date/time, you get all the data that you expect, but when filtering by the date/time, you are missing one row of the test data?

If so, the reason is simple, the c_time (year) value is less than the o_time (year) value, for that row of data, so the between comparison fails. The first value in a between operation must be less than or equal to the second value.

Edit: let me redo the above (it was based on the values in the last posted image, which are bad anyway.) The filter date values are from ‘2017-08-14’ to ‘2022-06-09’. There’s only one row of data with an o_time date matching that range.

Some comments about the code -

1. If a SELECT query is failing, its due to a programming mistake. There’s no point in your code having exception try/catch logic in this case, just let php catch and handle any database exception, allowing you to remove the try/catch, simplifying the code.
2. When you added the filter to this code, you should have simply added ONE array call-time parameter to the functions, rather than listing out each value as a separate parameter. This will simplify both the defining and calling syntax. And if you make the parameter optional, and add a conditional test inside the function definition(s), you can call the function(s) both without a filter parameter and with one and keep the initial operation and the filter operation, using a single fucntion.
3. Can there be more than one filter per user id? If not, then why are you using fetchAll() and then needing to use a loop to get the values from a single row of data?
4. If you set the PDO default fetch mode to assoc when you make the database connection, you won’t have to specify it in each fetch statement.
5. The fetchAll() method returns an empty array if the query doean’t match any data. There’s no need for the conditional test and the two different return values. Just return the result of the fetchAll() call.
6. If you use implicit binding, by simply supplying an array of the inputs to the execute([…]) call you can simplify all the code.
7. Using simple positional ? place-holders will also eliminate a lot of typing.
8. The reason you couldn’t get the column IN() comparison to work with a prepared query is because each value would require a separate place-holder. If you use FIND_IN_SET(column, put_one_place_holder_here) you can get this to work.

[erezvol]

One more thig to add,
When the link “show” is clicked, you are directed to a page which changes the value of the variable hideshow from hide to show.
Here is the code of th file

<?php

require "includes/db_connect.inc.php";
//echo "hello<br>";
if(isset($_GET['h']) &&isset($_GET['i'])&& is_numeric($_GET['i'])&& isset($_GET['u'])){
	
$url = $_GET['u'];
$id = $_GET['i'];
$hideshow = $_GET['h'];
//   Users who click that hide/ show link wish to show a hidden trade or hide a present trade


}
if($hideshow == 'show')
	{
	  $hideshow = 'hide';
    }else
	{
	  $hideshow = 'show';

	}

function update_hide_show($id, $hideshow)
{
	global $db;
	
	try
	{
		$sql = "
				UPDATE data SET
				`hideshow` = :hideshow
				WHERE `id` = :id";		

		$stmt = $db->prepare($sql);
		$stmt->bindParam(':id', $id, PDO::PARAM_INT);
		$stmt->bindParam(':hideshow', $hideshow, PDO::PARAM_STR);
		$stmt->execute();
		
		return true;	
	}
	catch(Exception $e) 
	{
	   echo $e;
	   die();
	   return false;        
	}
}//end function



$result = update_hide_show($id, $hideshow);

if($result == false)
{
	die('Problem updating visuality');
}
else
{
	header('location: '.$url.'');
	die();
}

//}

?>

the problem is that the line is supposed to show in the table with the text “show”.
Yet after clicking on “hide” you are directed back to the page, but the line is missing.
It happens whenever the link is clicked.
It worked well when the time variables were not included in the functions