Существует таблица с заявлениями пользователей. Эти заявления поданы в разные детские сады. И что-то не пойму как правильно сформировать номер очереди. Место в очереди: - Дет. сад - Возраст - Категория И в ней относительно времени подачи заявления, кто первее подал тот и выше место в очереди. Например: Иванов А.И. подал заявление 01.09.13 В дет. сад №1, в 3-летнюю группу, очередник У него 1 место. Петров А.И. подал заявление 02.09.13 В дет. сад №1, в 3-летнюю группу, очередник У него 2 место. Сидоров А.И. подал заявление 03.09.13 В дет. сад №2, в 3-летнюю группу, очередник У него 1 место. $req = mysql_query("SELECT * FROM `statement_doy` WHERE `id_user`=".$user['id']." ORDER BY `id` ASC"); while ($row = mysql_fetch_assoc($req)) { $nomer++; echo '<tr> <td style="text-align: center">'.$row['f_baby'].' '.$row['i_baby'].' '.$row['o_baby'].'</td> <td style="text-align: center">'.$row['data_r'].'</td> <td style="text-align: center">'.$row['z_f'].' '.$row['z_i'].' '.$row['z_o'].'</td>'; echo '<td style="text-align: center">'.$nomer.'</td>'; if($row['moderation']==1)echo '<td style="text-align: center"><font color="green">допущено</font></td>'; if($row['moderation']==2)echo '<td style="text-align: center"><font color="red">на проверке</font></td>'; if($row['moderation']==3)echo '<td style="text-align: center"><font color="blue">отклонена</font></td>'; echo '</tr>'; } CREATE TABLE IF NOT EXISTS `statement_doy` ( `id` int(11) NOT NULL auto_increment, `id_user` int(11) NOT NULL, `moderation` int(1) NOT NULL default '2', `status` int(11) NOT NULL default '2', `nomer` varchar(11) NOT NULL, `f_baby` varchar(32) NOT NULL, `i_baby` varchar(32) NOT NULL, `o_baby` varchar(32) NOT NULL, `data_r` varchar(10) NOT NULL, `vozrast_groups_doy` varchar(10) NOT NULL, `cat_baby` varchar(100) NOT NULL, `pol` int(1) NOT NULL, `document_tip` varchar(100) NOT NULL, `serial_doc` varchar(100) NOT NULL, `nomer_doc` varchar(100) NOT NULL, `data_out_doc` varchar(100) NOT NULL, `ulica` varchar(100) NOT NULL, `dom` varchar(100) NOT NULL, `kvartira` varchar(100) NOT NULL, `data_post` varchar(20) NOT NULL, `lgoty` varchar(500) NOT NULL, `potrebnost_zd` varchar(100) NOT NULL, `spec_potrebnost` varchar(100) NOT NULL, `predstavitelstvo` varchar(20) NOT NULL, `z_f` varchar(32) NOT NULL, `z_i` varchar(32) NOT NULL, `z_o` varchar(32) NOT NULL, `z_email` varchar(32) NOT NULL, `z_telefond` varchar(12) NOT NULL, `z_telefonm` varchar(32) NOT NULL, `z_telefonr` varchar(32) NOT NULL, `doy` varchar(200) NOT NULL, `prim` varchar(500) NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  • Sort first by Garden, then by Date (reverse). Once the garden has changed, reset the queue counter. And that's all. - SilverIce
  • @vitagame, frame your question humanly. Remove excess, leave the most important thing. What for here, for example, style? - Denis Khvorostin

1 answer 1

Spread the data on the tables. There should be at least three fields in the statement table:

 id int NOT NULL PRIMARY KEY, id_user int NOT NULL, actual bool DEFAULT true

Here you can add links to kindergartens and groups - it is up to you.

The id field will be used for sorting. By default, the new entry in the queue is relevant. When the application is processed, the value changes to false.

To get the number in the queue, we find the id of the queue entry for a specific user (subquery), then we count those entries that are less than this id and add one to the result (COUNT (*) + 1):

  SELECT COUNT(*)+1 AS turn FROM turn WHERE actual = true AND id < (SELECT id FROM turn WHERE id_user = 987 /* id пользователя */ );

If you want to get a queue in a specific garden (there is an id_org field in the table), adjust the query like this:

  SELECT count(*)+1 AS turn FROM turn WHERE actual = true AND id < (SELECT id FROM turn WHERE id_user = 987 /* id пользователя */ ) AND id_org = 123; /* id садика */

Similarly solved the issue with the group.

  • Forgotten to take into account that for each kindergarten - a separate queue. - SilverIce
  • @SilverIce, do not forget. The second query displays the queue for the garden (notice AND AND__org ...) - Denis Khvorostin
  • Well, in general, yes, probably. Sorry. I looked only at the description of the table at the very beginning. - SilverIce
  • Added table structure to first post. - vitagame
  • @vitagame, pay attention to the first paragraph of my answer. I have a feeling that you have only one table in the database. And this, to put it mildly, the decision is not very good. - Denis Khvorostin