Upgrade SQL selection with checker numbers (php included)

PHP
1

This is possible to add a numbers checker, to a query ?

here is example in php (for all number)

for ($i = 1; $i <= 70; $i++) {
    echo $i;
}

or

foreach (range(1, 70) as $i) {
    echo $i;
}

Here is query

SELECT ticketnums.num, COUNT( ticketnums.num ) AS times, keno_tickets.gameid
FROM keno_tickets
INNER JOIN ticketnums ON keno_tickets.ticketid = ticketnums.ticketid
WHERE keno_tickets.gameid = (select MAX(gameid) from keno_tickets)
GROUP BY ticketnums.num
ORDER BY times ASC LIMIT 10

If some of numbers from 1 to 70 not exists in the results, so needed gives result 0 times (if not found).

Here is DEMO http://sqlfiddle.com/#!2/a635c4/4

Need it look like

24 => 0 times 
35 => 0 times 
44 => 0 times 
46 => 1 times 
47 => 1 times 
62 => 1 times 
40 => 1 times 
42 => 1 times 
45 => 2 times 
50 => 3 times

here is php with mysqli

$arr = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT ticketnums.num, COUNT( ticketnums.num ) AS times, keno_tickets.gameid
FROM keno_tickets
INNER JOIN ticketnums ON keno_tickets.ticketid = ticketnums.ticketid
WHERE keno_tickets.gameid = (select MAX(gameid) from keno_tickets)
GROUP BY ticketnums.num
ORDER BY times ASC LIMIT 10") or sqlerr(__FILE__, __LINE__);
    if (mysqli_num_rows($arr) > 0){
    while($res = mysqli_fetch_array($arr)){	 
         echo $res['num']." => ".$res['times']." times <br />";
    }
    }

Thanks for all

2

Last I checked there is nothing built-in in MySQL for that. You could make a table with values 1 to 70 and LEFT JOIN on that.
Otherwise you’d have to do this in your code; query, loop the results and fill the gaps.

1 Like
3

Can you little help me with the php, if i do like this

$arr = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT ticketnums.num, COUNT( ticketnums.num ) AS times, keno_tickets.gameid
FROM keno_tickets
LEFT JOIN ticketnums ON keno_tickets.ticketid = ticketnums.ticketid
WHERE keno_tickets.gameid = ((select MAX(gameid) from keno_history) + 1)
GROUP BY ticketnums.num
ORDER BY times ASC LIMIT 10") or sqlerr(__FILE__, __LINE__);
    if (mysqli_num_rows($arr) > 0){
      while ($res = mysqli_fetch_assoc($arr)) {	 
	  for ($i = 1; $i <= 70; $i++) {
         if ($i == $res['num']) {
         echo $i." => ".$res['times']." times <br />"; 
      }else{
	    echo $i." => 0 times<br />"; 
      }
	 }
	 }
   }

This is dublicate results many time… thanks

4

Your SQLFiddle is blank. I’m not sure if I follow what you’re trying to do.

So you want to see how many times a ticket number occurs in the database?

5

Yes (a ticket number) , but i need all numbers from 1 to 70 (like list), add to them (how many times) if number/s exsits, and sort total results (by count(times) ASC).

P.S: SQLFiddle now give me error - Unknown Error Occurred: XML document structures must start and end within the same entity.:

6

Maybe you’re looking for

array_count_values

array_count_values() returns an array using the values of array as keys and their frequency in array as values.

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.