Little change array sort code in php

First i show the code, for help me, and in the end i will explain what i need.
Database

CREATE TABLE IF NOT EXISTS `tickets` (
      `ticketid` int(10) NOT NULL AUTO_INCREMENT,
      `datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `game` enum('1','2','3') NOT NULL DEFAULT '1',
      `gameid` int(100) NOT NULL,
      `userid` int(11) NOT NULL DEFAULT '0',
      `sum` int(11) NOT NULL,
      `username` varchar(50) NOT NULL,
      `numbers` varchar(27) NOT NULL,
      `count` int(2) NOT NULL,
      `how_much_win` int(20) NOT NULL,
      `win` enum('yes','no') NOT NULL DEFAULT 'no',
      `checked` enum('yes','no') NOT NULL DEFAULT 'no',
      PRIMARY KEY (`ticketid`)
    ) ENGINE=MyISAM;

INSERT INTO `tickets` (`ticketid`, `datetime`, `game`, `gameid`, `userid`, `sum`, `username`, `numbers`, `count`, `how_much_win`, `win`, `checked`) VALUES
(1, '2014-10-22 16:33:18', '1', 6592, 40294, 20, 'sergey', '13|19|31|49|50|61|65', 0, 0, 'no', 'no'),
(2, '2014-10-22 16:33:20', '1', 6592, 40294, 20, 'sergey', '8|10|36|38|44|50|68', 0, 0, 'no', 'no'),
(3, '2014-10-22 16:33:22', '1', 6592, 40294, 20, 'sergey', '2|14|31|42|48|56|64', 0, 0, 'no', 'no'),
(4, '2014-10-22 16:33:23', '1', 6592, 40294, 20, 'sergey', '8|11|26|34|37|42|44', 0, 0, 'no', 'no'),
(5, '2014-10-22 16:33:24', '1', 6592, 40294, 20, 'sergey', '5|27|28|55|60|62|67', 0, 0, 'no', 'no'),
(6, '2014-10-22 16:33:27', '2', 6592, 40294, 160, 'sergey', '1|15|19|25|38|47|62|64', 0, 0, 'no', 'no'),
(7, '2014-10-22 16:33:28', '2', 6592, 40294, 160, 'sergey', '2|6|40|45|54|56|69|70', 0, 0, 'no', 'no'),
(8, '2014-10-22 16:33:30', '3', 6592, 40294, 720, 'sergey', '1|7|23|47|54|55|57|59|68', 0, 0, 'no', 'no'),
(9, '2014-10-22 16:33:36', '3', 6592, 40294, 1080, 'sergey', '3|12|15|26|33|41|43|46|60', 0, 0, 'no', 'no'),
(10, '2014-10-22 16:33:45', '1', 6592, 40294, 30, 'sergey', '17|26|31|55|57|59|61', 0, 0, 'no', 'no');

page

$arr = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT numbers FROM tickets WHERE checked = 'no'") or sqlerr(__FILE__, __LINE__);
    if (mysqli_num_rows($arr) > 0){
        while ($res = mysqli_fetch_assoc($arr)) {
               foreach (explode("|", $res['numbers']) as $value) {
                        $numbers[$value]++;
               }    
       }

$numbers_f = array();
foreach($numbers as $num => $value){
    $numbers_f[$num] += $value;
}

foreach(range(1, 70) as $num){
  $numbers_f[$num] = (isset($numbers_f[$num]) ? $numbers_f[$num] : 0);
}

rsort($numbers_f,SORT_NUMERIC); 
$top=array_slice($numbers_f,0,10); //top 10
$butt=array_slice($numbers_f,(53));  //bottom 17
print_r($top);  
//echo implode(",", $top);
echo"<br />";
//echo implode(",", $butt);
print_r($butt);
}

The database (numbers like 17|26|31|55|57|59|61), my code do check how much numbers was chacked like if the system see number 5 in two tickets, it will do like 5=2, so now i my array it will be print only count (2 = $value), but its good because the sort work, but how change or what add to see results by $num, i dont wanna know how much number in tickets, i just wanna know the top number/s ))) thanks you very much for help.

Why do you have values stored like this in your database? Since they are each separate values each number should have a separate row in a second table. Then you will be able to get rid of a lot of unnecessary PHP to process them and will be able to select them in the order you want straight out of the database.

3 Likes

Thanks for replay first! Just because I felt like it when I started . Second, do you mean why i don’t do all numbers in different rows ? Like this ? (num1,num2 and etc…)

CREATE TABLE IF NOT EXISTS `tickets` (
      `ticketid` int(10) NOT NULL AUTO_INCREMENT,
      `datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `game` enum('1','2','3') NOT NULL DEFAULT '1',
      `gameid` int(100) NOT NULL,
      `userid` int(11) NOT NULL DEFAULT '0',
      `sum` int(11) NOT NULL,
      `username` varchar(50) NOT NULL,
          `num1` int(2) NOT NULL,
	  `num2` int(2) NOT NULL,
	  `num3` int(2) NOT NULL,
	  `num4` int(2) NOT NULL,
	  `num5` int(2) NOT NULL,
	  `num6` int(2) NOT NULL,
	  `num7` int(2) NOT NULL,
	  `num8` int(2) NOT NULL,
	  `num9` int(2) NOT NULL,
      `count` int(2) NOT NULL,
      `how_much_win` int(20) NOT NULL,
      `win` enum('yes','no') NOT NULL DEFAULT 'no',
      `checked` enum('yes','no') NOT NULL DEFAULT 'no',
      PRIMARY KEY (`ticketid`)
    ) ENGINE=MyISAM;

Don’t do that - when you see something like that in a database (or worse what you originally showed) then you know that the database is extremely badly designed.

The first step in fiixiing the design is to split it into two tables:

CREATE TABLE IF NOT EXISTS `tickets` (
      `ticketid` int(10) NOT NULL AUTO_INCREMENT,
      `datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `game` enum('1','2','3') NOT NULL DEFAULT '1',
      `gameid` int(100) NOT NULL,
      `userid` int(11) NOT NULL DEFAULT '0',
      `sum` int(11) NOT NULL,
      `username` varchar(50) NOT NULL,
      `how_much_win` int(20) NOT NULL,
      `win` enum('yes','no') NOT NULL DEFAULT 'no',
      `checked` enum('yes','no') NOT NULL DEFAULT 'no',
      PRIMARY KEY (`ticketid`)
    ) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `ticketnums` (
      `ticketid` int(10) NOT NULL,
      `num` int(2) NOT NULL,
      PRIMARY KEY (`ticketid`,`num`)
    ) ENGINE=MyISAM;

The second step is to get rid of the enum calls as you should never use them in a properly designed database.

1 Like

Ok tnx, whats a size can be the table ticketnums ? Because i have something like 30000 rows in 24 hours like before (13|19|31|49|50|61|65) . My tickets can be with 7, 8 or 9 numbers. So if i take a center number 8, lets do 8*30000 = +/- 240,000 rows in one day. Or maybe u can tell me good way to optimize (cached) the old information in table after one/two weeks or something (to do archive) .

You should be okay for a few centuries then.

1 Like

Thanks, I change all my code like this now )))