Using CONCAT in mysql_query

[squigs]

I'm looking to extract a dollar value out of a column in mysql table (inventory obviously) the column is set up as a decimal(8,2)

so my numbers are appearing but I can't get them to show a dollar value beforehand.

 

I have been playing with the following code. If anymore info is needed I can provide it.

 

Cheers

 

$searchSQL = mysql_query("SELECT *, CONCAT('$', low_price) FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%'");

[Pikachu2000]

Try escaping the dollar sign with a backslash.

 

CONCAT('\$', . . .

[requinix]

CONCAT('$', low_price) AS low_price

 

Even better, call that something else. Like "low_pricemoney". Then

$row = mysql_fetch_array($searchSQL);
echo "Number is ", $row["low_price"], "; currency is ", $row["low_pricemoney"];

[squigs]

CONCAT('$', low_price) AS low_price

 

Even better, call that something else. Like "low_pricemoney". Then

$row = mysql_fetch_array($searchSQL);
echo "Number is ", $row["low_price"], "; currency is ", $row["low_pricemoney"];

 

Excellent I had been reading about using CONCAT but this filled in the missing blank for me... the AS is needed to rename the column.

 

Thanks for your responses!

[squigs]

To take this idea one step further, I want to display a price range. I have another decimal column called 'high_price' and if that field is not null I would like for it to output some formatting as well to demonstrate a range of price e.g.

 

$99.99 - $199.99

 

Using CONCAT I have only been able to add the extra formatting regardless of whether the field is null or not. I'm assuming that I need a conditional statement to fix this?

 

My other thought is to scrap the high_price column as a decimal and change it to varchar with formatting inputted directly(kind of sloppy though right?)

 

Thanks for any advice on this!

 

[squigs]

Alright, with a little patience I came up with one solution using a both of the answers you guys gave me. I used CONCAT in the following way to achieve my base price in the desired format

 

$searchSQL = mysql_query("SELECT  *, CONCAT('$', low_price) AS low_price FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%'");	

 

and I wrote a conditional statement and escaped the $ with \ for my optional high_price to create the range which looks like this.

<?php
while ($row = mysql_fetch_array($searchResult)){
		if ($row['high_price'] > ($row['low_price'])){
			$high_price =  " - \${$row['high_price']}";
		}else {$high_price = "";
		}
?>

 

Works for me... if there is any improvements anyone would like to suggest feel free, but for now its doing the job.

Thanks again.