How to concatenate variable with a string in a query?

PHP
1

Hello everybody. I have a problem with this query:

function profit_loss_coin_family($coin)
{
	global $db;	
	
	try
	{
		
	$sql = "SELECT item profit
				,SUM(CASE WHEN profit >= 0 AND type LIKE 'buy%'  THEN profit ELSE 0.0 END) AS TotalBuyProfit
				,SUM(CASE WHEN profit < 0 AND type LIKE 'buy%' THEN profit ELSE 0.0 END) AS TotalBuyLoss
				,SUM(CASE WHEN profit >= 0 AND type LIKE 'sell%'  THEN profit ELSE 0.0 END) AS TotalSellProfit
				,SUM(CASE WHEN profit < 0 AND type LIKE 'sell%' THEN profit ELSE 0.0 END) AS TotalSellLoss
				FROM data  WHERE item LIKE ':coin%' OR item LIKE '%coin' GROUP BY item";

				 $stmt = $db->prepare($sql);
				 $stmt->bindParam(':coin', $coin, PDO::PARAM_STR);
				 $stmt->execute();
				 return $stmt->fetchAll(PDO::FETCH_ASSOC);}

item is one of currency pairs like audusd, gbpusd, eurgbp. ect…
$coin is currency like usd, gbp, nzd, cad.

I need this query to receive $cion and perform a query on all currency pairs which include this $coin.

For example, if $coin = gbp, the query should extract all currency pairs which includ gbp, and it can be gbpusd or eurgbp (once at the begining of the string , and sometime at its end.

What is the right way to write the query ?
Thanks

4

you forgot to mention what the problem is

5

It’s in the subject: How to concatenate variable with a string in a query?

I need the query to receive a variable $coin (e.g. gbp) and perform the query on currency pairs whic include gbp (e.g. eurgbp, gbp aud)

I tried WHERE item LIKE ‘:coin%’ OR item LIKE ‘%coin’

the code :slight_smile:$data = profit_loss_coin_family($coin);

print_r($data);

returns : Array ( )

6

When using placeholders in prepared statements, to get a wildcard % in there it must go in with the value that is being bound, not in the raw query.
It is explained here: https://phpdelusions.net/pdo#like

7

thanks… i suspected this wasn’t really a mysql question but i wasn’t sure

1 Like
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.