I have a question about how to count the number of records in the TUTOR table based on the type of career to which a PROJECT belongs.
This is because I want to know how many tutors are in systems projects, agronomy, nursing, in short.
It has 3 tables:
How would you select the database to tell me how many tutors are in projects whose proy_carr_id is 1, 2 and 3?
The nested PROJECT and TUTOR table is a link between the two.
I know that a LEFT JOIN would be used but I do not remember in what form
so far something goes like this:
function bd_rep23()
{
$sql = "SELECT COUNT(*) FROM tutor where cedu_tuto
LEFT JOIN proyecto c ON a.tuto_id = c.carr_id
ORDER BY a.cedu_tuto ASC";
$id=sql2value($sql);
return $id;
}
I do not know if I should first select the data and then count
Structure of tables
TUTOR:
CREATE TABLE 'tutor' (
'cedu_tuto' int(9) UNSIGNED NOT NULL COMMENT 'Cédula',
'ape1_tuto' varchar(50) COLLATE utf8_unicode_ci NOT NULL,
'ape2_tuto' varchar(50) COLLATE utf8_unicode_ci NOT NULL,
'nom1_tuto' varchar(50) COLLATE utf8_unicode_ci NOT NULL,
'nom2_tuto' varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
'telf_resi_tuto' varchar(11) COLLATE utf8_unicode_ci NOT NULL,
'telf_celu_tuto' varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
'proy_id' int(4) NOT NULL,
'tipo_id' int(1) NOT NULL,
'emai_tuto' varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
PROJECT:
CREATE TABLE 'proyecto' (
'proy_id' int(4) UNSIGNED NOT NULL,
'proy_deno' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
'plan_proy' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
'objg_proy' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
'obje_proy' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
'fech_insc' date NOT NULL,
'nomb_comu' varchar(50) COLLATE utf8_unicode_ci NOT NULL,
'resp_comu' varchar(50) COLLATE utf8_unicode_ci NOT NULL,
'parr_id' int(4) NOT NULL,
'deta' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
'esta_proy_id' int(1) NOT NULL,
'telf_inst' varchar(11) COLLATE utf8_unicode_ci NOT NULL,
'telf_resp' varchar(11) COLLATE utf8_unicode_ci NOT NULL,
'obsv_proy' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
'proy_carr_id' int(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
PROJECT_TUTOR:
CREATE TABLE 'proyecto_tutor' (
'proy_tuto_id' int(4) UNSIGNED NOT NULL,
'proy_id' int(4) UNSIGNED NOT NULL,
'tuto_id' int(9) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
List form
<?php
include 'conexion.php';
$tutor=bd_rep3();
$tutora=bd_rep5();
$tutoraca=bd_rep21();
$tutorcomu=bd_rep22();
$tutormeto=bd_rep23();
$tutoring=bd_rep24();
include 'cab.php';
?>
<h2>Reporte de Cantidades</h2>
<table class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th><center>Tutores</th>
<th><center>Tutores Asignados</th>
<th><center>Tutores Academicos</th>
<th><center>Tutores Comunitario</th>
<th><center>Tutores Metodológico</th>
<th><center>Tutores en Ing.Sistemas</th>
<th><center>Tutores en Ing.Gas</th>
<th><center>Tutores en Educación</th>
<th><center>Tutores en Administración</th>
<th><center>Tutores en Ing.Agronoma</th>
<th><center>Tutores en Enfermería</th>
</tr>
</thead>
<tbody>
<tr>
<td><?=$tutor?></td>
<td><?=$tutora?></td>
<td><?=$tutoraca?></td>
<td><?=$tutorcomu?></td>
<td><?=$tutormeto?></td>
<td><?=$tutoring?></td>
</tr>
</tbody>
</table>
<?php
include 'pie.php';
Being bd_rep24 ().
function bd_rep24()
{
$sql = "SELECT proy_carr_id, COUNT(DISTINCT tuto_id)
FROM PROYECTO_TUTOR pt
LEFT JOIN PROYECTO p
ON pt.proy_id = p.proy_id
GROUP BY proy_carr_id";
$id=sql2value($sql);
return $id;
}
Part of the connection code.php, this contains some functions to transform the querys to integers, among others.
<?php
$m = new mysqli(
'localhost',
'root',
'',
'proyecto_base'
);
if ($m->connect_errno)
{
printf("(lrcrud) Falló conexión: %s\n", $mysqli->connect_error);
exit();
}
$m->query("SET NAMES 'utf8'");
function sql($sql)
{
global $m;
$resultado = $m->query($sql);
if ( $resultado === FALSE )
{
printf( "%s\n", $m->error );
exit;
}
return $resultado;
}
function sqlerror($sql,$error)
{
return "<html><head></head><body><ul>"
."<li>Instruccion SQL:<br /><pre>{$sql}</pre></li>"
."<li>Error SQL: <font color='red'>{$error}</font></li>"
."</ul></body></html>";
}
function sql2array($sql)
{
global $m;
if ( !$res=$m->query($sql) )
{
echo sqlerror( $sql,$m->error );
exit;
}
$r=array();
while( $temp=$res->fetch_array(MYSQLI_ASSOC) )
{
$r[]=$temp;
}
return $r;
}
function sql2row($sql)
{
global $m;
if ( !$res=$m->query($sql) )
{
echo sqlerror( $sql,$m->error );
exit;
}
return $res->fetch_array(MYSQLI_ASSOC);
}
function sql2value($sql)
{
global $m;
if ( !$res=$m->query($sql) )
{
echo sqlerror( $sql,$m->error );
exit;
}
$p=$res->fetch_array(MYSQLI_NUM);
return $p[0];
}
function sql2options($sql)
{
global $m;
if ( !$res=$m->query($sql) )
{
echo sqlerror( $sql,$m->error );
exit;
}
$r=array();
while( $l=$res->fetch_array(MYSQLI_NUM) )
{
$r[$l[0]]=$l[1];
}
return $r;
}
function sqlexist($id,$tabla)
{
global $m;
$sql="SELECT COUNT(*) FROM $tabla WHERE id = '$id'";
if (sql2value($sql)>0)
{
return TRUE;
}
else
{
return FALSE;
}
}
function sqlexistvct($valor,$campo,$tabla) {
global $m;
$sql="SELECT COUNT(*) FROM $tabla WHERE $campo = '$valor'";
if (sql2value($sql)>0)
{
return TRUE;
}
else
{
return FALSE;
}
}
function cifra_clave($clave,$sal)
{
return sha1($clave.$sal);
}
Then the functions to add edit and delete, students, tutors and projects would follow.
I want to show like this: System tutors: 4 administration turores: 4 agronomy tutors: 4 each in a column of the list that is beginning the question.