How to sort items with floating numbers in WHERE

1

I would like to know how I can perform a query, having a float number. In the bank I have an item with that number (3.4), but when I apply the following code, the item with number (3.4) is not appearing.

$numerador = '3.4';
$lisagemMedias = mysql_query("SELECT * FROM 'medias' WHERE 'cat'='".$dadosGerais["cat"]."' AND 'subcat'='".$dadosGerais["id"]."' AND 'numero'='".$numerador."' ORDER BY numero");
$mediaDados = mysql_fetch_array($lisagemMedias); 
$numeradorPonto = str_replace('.', '-', $mediaDados["numero"]);


echo $dadosGerais["nome"].' '.$numeradorPonto;

I would like to know how I can arrange this so that I can query both integers and (1,2,3). As numbers with float as (1.2, 3.4).

    
asked by anonymous 30.06.2015 / 16:37

2 answers

1

As this answer in the SOen

Change to decimal and use CAST :

SELECT * FROM table WHERE CAST(numero AS DECIMAL) = CAST(101.31 AS DECIMAL);

However you might consider changing the "column" of your table to DECIMAL :

ALTER TABLE table ALTER COLUMN numero decimal(4,2)

I recommend you look at this article:

link

Note: DECIMAL is generally considered the best type to use when dealing with monetary values.

    
30.06.2015 / 19:31
0

Try without the quotation marks, as follows:

$lisagemMedias = mysql_query("SELECT * FROM 'medias' WHERE 'cat'='".$dadosGerais["cat"]."' AND 'subcat'='".$dadosGerais["id"]."' AND 'numero'=".$numerador." ORDER BY numero");
    
30.06.2015 / 19:19