-2
select COUNT(Id) AS Total 
from `euro` 
where (N1 = $i or N2 = $i or N3 = $i or N4 = $i or N5 = $i) 
LIMIT 50

How to count only 50 results??

I've tryied this with no sucess

$sql= "SELECT * FROM euro where (N1 = $i or N2 = $i or N3 = $i or N4 = $i or N5 = $i) LIMIT 50";
$query = "select COUNT(Id) As Total FROM ($sql) as a"

or this:

$sql= "SELECT * FROM euro  LIMIT 50";
$query = "select COUNT(Id) As Total FROM ($sql) as a where (N1 = $i or N2 = $i or N3 = $i or N4 = $i or N5 = $i)"

I'm not quite sure if I understand last part on results give all 50

I have this table:

Id date dayofweek N1 N2 N3 N4 N5 E1 E2

1 2004-02-13 Friday 1 4 12 41 50 1 4

2 2004-02-28 Friday 2 5 32 33 39 4 6

3 2004-03-10 Tuesday 23 26 43 44 49 9 10

I had this: $query = "select COUNT(Id) AS Total from euro where (N1 = $i or N2 = $i or N3 = $i or N4 = $i or N5 = $i)"; And it worked counting in an efficient way for all the records in table so it means it must respect that part.

Now I have :

$query = "select COUNT(N1) As Total FROM (SELECT * FROM euro where $i in (N1, N2, N3, N4, N5) Order By Id Desc LIMIT 50) a";

And it gives 50 to all counting... I can't understand...

Joao Marques
  • 7
  • 1

2 Answers2

0

You would typically select the records in a subquery first, then aggregate:

select count(id) as total 
from (
    select id
    from euro 
    where ? in (n1, n2, n3, n4, n5)
    order by ??
    limit 50
) t

Notes:

  • limit without order by does not give you a stable result; I added an order by clause, in which you would need to put the relevant sorting column

  • in is handy to avoid repeating or conditions

  • use parameters; don't concatenate variables in the query string, this is inefficient - and, more important, unsafe if the value comes from outside your code

  • count(<column>) takes in account everything but null values; for the query to make sense at all, id should be a nullable column; if it does not contain any null value, the query is guaranteed to return 50 (or the total number of records in the table, if that's less than 50)

GMB
  • 188,822
  • 23
  • 52
  • 100
  • It gives me 50 in all values – Joao Marques Aug 27 '20 at 21:59
  • @JoaoMarques: this is not necessarily suprising - see the last bullet point in my answer. – GMB Aug 27 '20 at 22:00
  • I had this: $query = "select COUNT(Id) AS Total from euro where (N1 = $i or N2 = $i or N3 = $i or N4 = $i or N5 = $i)"; And it worked counting in an efficient way for all the records in table so it means it must respect that part , only when I try to LIMIT 50 it worked out wrong – Joao Marques Aug 28 '20 at 01:35
0

I solved this by doing this:

$query= "SELECT Id FROM euro Order By Id DESC"; $result = mysqli_query($db,$query); $resultados = mysqli_num_rows($result);

$query= "SELECT COUNT(Id) As Total FROM euro where $i in (N1, N2, N3, N4, N5) AND (Id > ($resultados -50)) Order By Id DESC"; $result = mysqli_query($db,$query);

Joao Marques
  • 7
  • 1