How to sort items with floating numbers in WHERE

Asked

Viewed 205 times

1

I would like to know how I can conduct a consultation with a number float. In the bank I have an item with this number (3.4), but when I apply the following code, it is not showing the item with number (3.4).

$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 fix this so that I can perform both integer queries and (1,2,3). As numbers with values float as (1.2, 3.4).

php
  • 1

    You can try putting a simple quotation mark before this numerator, as you do with the id AND 'numero' = ' ".$numerador." '

  • already edited I forgot why here I typed this code by the tablet was bad in any case still not making appear the value with float 3.4

  • In your bank you are not saved with no comma?

  • Not this saved as 3.4 even I did it in case when I put a number like the 3-4 it converts to 3.4 to send this value to SQL and the type in SQL instead of the int and float in the number field I do not use varchar and char because it does not sort right in numerical order depending on what you want to do and in my case it was necessary the float.

2 answers

1


As stated in the Soen

Change to decimal and uses CAST:

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

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

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

I recommend you see this article:

http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-characteristics.html

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

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");

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.