chris227 chris227 - 3 months ago 9
MySQL Question

Mysql order by 2 columns that represent price and new price

I have 2 columns for a product

price
that represents the normal price and
new_price
that represents the offer of the product. When I order them by price I can't get a correct order if both columns are filled. Now it orders first by new price and then by price. I want somehow to concatenate prices.

if($order==1)
{
$order_by='Produse.New_price ASC,Produse.Price ASC';
}

if($order==2)
{
$order_by='Produse.New_Price DESC,Produse.Price DESC';
}

if($order==3)
{
$order_by='Produse.Nume_Prod ASC';
}

if($order==4)
{
$order_by='Produse.Nume_Prod DESC';
}

if($order==0)
{
$order_by='Produse.Nume_Prod DESC';
}
$stmt=$dbh->prepare("Select * FROM Produse
INNER JOIN Categorii on Produse.ID_Categorie=Categorii.ID_Categorie
where Categorii.Nume_Categ=:id
ORDER BY $order_by");
$stmt->bindParam(':id',$id,PDO::PARAM_INT);
$stmt->execute();

Answer

Use Coalesce to sort by new price if present (not null) then by price if new price is null...

if($order==1)
{
    $order_by='coalesce(Produse.New_price,Produse.Price) ASC';
}
if($order==2)
{
    $order_by='Coalesce(Produse.New_Price,Produse.Price) DESC';
}    
if($order==3)
{
    $order_by='Produse.Nume_Prod ASC';
}   
if($order==4)
{
    $order_by='Produse.Nume_Prod DESC';
}   
if($order==0)
{
    $order_by='Produse.Nume_Prod DESC';
}
        $stmt=$dbh->prepare("Select * FROM Produse 
                               INNER JOIN Categorii on Produse.ID_Categorie=Categorii.ID_Categorie 
                             where Categorii.Nume_Categ=:id 
                             ORDER BY $order_by");
        $stmt->bindParam(':id',$id,PDO::PARAM_INT);
        $stmt->execute();
Comments