Greetings. What I am trying to do is the following. I need to count and show how many students have the current day as their payment date, to show it in an alert that says There are 4 students who must pay today. And I also want to show in a list the names of the students with today's payment date. I have made the query but apparently it is not telling me the records. I have the tables related as follows:
The tables are related as you can see. The query I have made is the following:
<?php
include '../lib/conexion.php';
date_default_timezone_set('America/Caracas');
setlocale(LC_ALL, "es_ES");
$hoy = date('d-m-Y');
$query = current($mysqli->query("SELECT COUNT(pagos_estudiantes.id),
students.names FROM students INNER JOIN inscritos ON
inscritos.id_student = students.id_students INNER JOIN pagos_estudiantes
ON pagos_estudiantes.id_inscripcion = inscritos.id WHERE
pagos_estudiantes.fecha_a_pagar = $hoy")->fetch_assoc());
if($query){
$alert = $query;
$result = "Hay " . $query . "que deben cancelar hoy";
}
echo $alert;
echo $result;
?>
That is my query, but as I said, what it shows me is a 0 in the alert, that is, it is not counting the records. What I need is to display the number of records with date_to_pay = today and the names of those students
the query
You can create a query that returns a single row with two columns, in one you will have the count and in another all the names of the students with a separator. Then you would do
explode
the second column to present a list of names.The query would be more or less like this:
The PHP code
As I said in comments, your PHP code is strange , and it's not the usual way taught in the Manual (or common practice) to read our results.
Also, your query must be sanitized, as it is vulnerable to SQL Injection. To resolve this vulnerability, we will significantly modify your code, applying the existing methods to do so. That is, change the value
$hoy
that you previously passed directly in the SQL statement, by a placeholder?
, and the value itself pass it separately, through thebind_param
.This is the proposal:
Since you need to display the names and other data, don't use
count
make the query and then get the number of rows withnum_rows
The query can be something like this, you adjust it to your needs.
Later
As you have been told, it is a bad idea to use
text
for date fields.