I wrote a script, but it does not work as it should.

The bottom line is that when you add a comment, the client is entered in the database 2 dates:

client_dateA - the date of the last contact;

client_dateB - the date of the next contact;

The system has customer output by date, but it works somehow strange, I need to output the number of COUNT (client_id) records for which the date of the next contact will be less than the current date of the day (this is considered an overdue record, unless its manager commented on the whole day and the next day it becomes overdue) you also need to output the number of clients whose date of the next contact is scheduled for tomorrow and for the week.

Please help me how to do it correctly.

The code that makes the request now

<?php // Подключение скриптов ядра include 'engine/function_mysqlServer.php'; include 'engine/function_configServer.php'; // Подключение скриптов ядра // Проверка авторизации пользователя if (isset($_COOKIE['id']) and isset($_COOKIE['hash'])) { $userdata = mysql_fetch_assoc(mysql_query("SELECT * FROM users WHERE users_id = '".intval($_COOKIE['id'])."' LIMIT 1")); $username = $userdata['users_login']; } // Проверка авторизации пользователя // Вывод 40 записей о клиентах на сегодня $main = mysql_query("SELECT * FROM client WHERE client_manager = '".$username."' and client_dateB = '".$date."' LIMIT 40"); if ($date == $row['client_dateB']) { while ($row_arr = mysql_fetch_array($main)) { echo "<div class=\"content_min\">"; echo "<p style=\"font-size:14px;float:left;font-weight:500;color:#949494;\">".$row_arr['client_company']." (ID: ".$row_arr['client_number'].")</p>"; echo "<p style=\"font-size:14px;float:right;font-weight:500;color:#949494;\">".$row_arr['client_name']."</p><br>"; echo "<p style=\"font-size:14px;float:left;font-weight:500;color:#949494;\">".$row_arr['client_dateA']."</p>"; echo "<p style=\"font-size:14px;float:right;font-weight:500;color:#949494;\">".$row_arr['client_phone']."</p><br><br>"; echo "<p style=\"line-height:20px;color:#676666;\">".$row_arr['client_comment']."</p></div>"; } } else { echo "<div class=\"mess_coffe\">".$username.", на сегодня у Вас отсутствуют записи</div>"; } // Общее количество зарегистрированных клиентов менеджером $sql = mysql_query("SELECT COUNT(client_id) FROM client WHERE client_manager = '".$username."'"); $res_sql = mysql_fetch_row( $sql ); // Общее количество зарегистрированных клиентов менеджером // Просроченные записи на текущий день $overdue = mysql_query("SELECT COUNT(client_id) FROM client WHERE client_manager = '".$username."' and client_dateB < NOW()"); $overdue_result = mysql_fetch_row( $overdue ); // Просроченные записи на текущий день // Зарегистрированные клиенты на завтра $tomorrow = mysql_query("SELECT COUNT(client_id) FROM client WHERE client_manager = '".$username."' and client_dateB > '".$date."'"); $tomorrow_result = mysql_fetch_row( $tomorrow ); // Зарегистрированные клиенты на завтра

2 answers 2

to retrieve all the records from the table, the query will look like this:

 select * from table

in order to get the list of records for tomorrow, you need to transfer the date type time parameter to the original request and filter the source table by it, i.e. in your case, the sql query might look something like this:

 select * from table where client_dateB = @date

client_dateB = @date changing the comparison conditions in the client_dateB = @date you can get data for yesterday if you change the = sign to <

to get the same records for the period, you need to pass two parameters to the request: the start date and the end date, and you can indicate what interests the records between these two dates, for example:

 select * from table where client_dateB >= @dateStart and client_dateB <=@dateStop

or use the Between operator, then the query will look like this:

 select * from table where client_dateB between @dateStart and @dateStop
  • I have all the record dates in the database in the varchar data type (stupidity) I know, but I have no idea how to convert them to current_date () or what is needed for normal work with dates - CyberX
  • @CyberX string in iso format? Is there access to the database at the table change level? - Bald
  • How does the data type in phpmyadmin DATE convert to the kind I need? and then he beat in this format (2016-06-24), and I need (06/24/2016) - CyberX
  • How do I remove this problem with data types so that the dates are as they should be and that there were opportunities to work with them (following the example of the above questions) - CyberX
  • if there is a possibility to change the structure of the table, then I would add columns for storing time in DateTime , fill them based on text values. And the DateTime value can already be easily converted to the format you need - Bald

so try

 $today = mktime(0, 0, 0, date("m"),date("d"),date("Y")); $overdue = mysql_query("SELECT COUNT(client_id) FROM client WHERE client_manager = '".$username."' and UNIX_TIMESTAMP(`client_dateB`) < $today");