this is a question about code optimization, because my code works good but I think there is an easier way to do what I did, I explain, I have an admin interface where admin can search courses, but the search can be specific, I mean you can search by Author or/and by Category or/and by School, and if you do NOT choose any of these options it returns all the courses so here is my code and it works I used the logic because I send an array called $param that contains 3 values. Look at the $query value and it just change on Where clause
Author School Category
0 0 0
0 0 1
0 1 0
0 1 1
1 0 0
1 0 1
1 1 0
1 1 1
and my code works with this same way
if(($param[0]==0)&&($param[1]==0)&&($param[2]==0))
$query = "SELECT idCurso, tema, indice, descripcion, fecha, idioma, imagenes.ubicacion as imgLocation, enlaces, nivel, keywords, autorId, escuelasId, categoriaId, subcategoriaId FROM plataforma.cursos INNER JOIN imagenes ON imagenes.idImagen = cursos.imagenId ";
else if( ($param[0]==0) && ($param[1]==0) && (intval($param[2])>=1)){
$query = "SELECT idCurso, tema, indice, descripcion, fecha, idioma, imagenes.ubicacion as imgLocation, enlaces, nivel, keywords, autorId, escuelasId, categoriaId, subcategoriaId FROM plataforma.cursos INNER JOIN imagenes ON imagenes.idImagen = cursos.imagenId WHERE categoriaId = ?;";
}
else if( ($param[0]==0) && ( intval($param[1])>=1) && ($param[2]==0)){
$query = "SELECT idCurso, tema, indice, descripcion, fecha, idioma, imagenes.ubicacion as imgLocation, enlaces, nivel, keywords, autorId, escuelasId, categoriaId, subcategoriaId FROM plataforma.cursos INNER JOIN imagenes ON imagenes.idImagen = cursos.imagenId WHERE escuelasId = ?; ";
}
else if( ($param[0]==0) && ( intval($param[1])>=1) && (intval($param[2])>=1)){
$query = "SELECT idCurso, tema, indice, descripcion, fecha, idioma, imagenes.ubicacion as imgLocation, enlaces, nivel, keywords, autorId, escuelasId, categoriaId, subcategoriaId FROM plataforma.cursos INNER JOIN imagenes ON imagenes.idImagen = cursos.imagenId WHERE escuelasId = ? AND categoriaId = ?; ";
}
else if( ( intval($param[0])>=1) && ($param[1]==0) && ($param[2]==0)){
$query = "SELECT idCurso, tema, indice, descripcion, fecha, idioma, imagenes.ubicacion as imgLocation, enlaces, nivel, keywords, autorId, escuelasId, categoriaId, subcategoriaId FROM plataforma.cursos INNER JOIN imagenes ON imagenes.idImagen = cursos.imagenId WHERE autorId = ?; ";
}
else if( ( intval($param[0])>=1) && ($param[1]==0) && (intval($param[2])>=1)){
$query = "SELECT idCurso, tema, indice, descripcion, fecha, idioma, imagenes.ubicacion as imgLocation, enlaces, nivel, keywords, autorId, escuelasId, categoriaId, subcategoriaId FROM plataforma.cursos INNER JOIN imagenes ON imagenes.idImagen = cursos.imagenId WHERE autorId = ? AND categoriaId = ?;";
}
else if( ( intval($param[0])>=1) && ( intval($param[1])>=1) && ($param[2]==0)){
$query = "SELECT idCurso, tema, indice, descripcion, fecha, idioma, imagenes.ubicacion as imgLocation, enlaces, nivel, keywords, autorId, escuelasId, categoriaId, subcategoriaId FROM plataforma.cursos INNER JOIN imagenes ON imagenes.idImagen = cursos.imagenId WHERE autorId = ? AND escuelasId = ?; ";
}
else if( ( intval($param[0])>=1) && ( intval($param[1])>=1) && (intval($param[0])>=1)){
$query = "SELECT idCurso, tema, indice, descripcion, fecha, idioma, imagenes.ubicacion as imgLocation, enlaces, nivel, keywords, autorId, escuelasId, categoriaId, subcategoriaId FROM plataforma.cursos INNER JOIN imagenes ON imagenes.idImagen = cursos.imagenId WHERE autorId = ? escuelasId = ? AND categoriaId = ?;";
}
//"asi quedo ".$query;
if ($stmt = $mysqli->prepare($query)) {
/* bind result variables */
if(($param[0]==0)&&($param[1]==0)&&($param[2]==0)) {
}
if( ($param[0]==0) && ($param[1]==0) && (intval($param[2])>=1)){
$stmt->bind_param("s",$param[2]);
//echo $param[2];
}
if( ($param[0]==0) && ( intval($param[1])>=1) && ($param[2]==0)){
$stmt->bind_param("i",$param[1]);
}
if( ($param[0]==0) && ( intval($param[1])>=1) && (intval($param[2])>=1)){
$stmt->bind_param("ii",$param[1],$param[2]);
}
if( ( intval($param[0])>=1) && ($param[1]==0) && ($param[2]==0)){
$stmt->bind_param("i",$param[0]);
}
if( ( intval($param[0])>=1) && ($param[1]==0) && (intval($param[2])>=1)){
$stmt->bind_param("ii",$param[0],$param[2]);
}
if( ( intval($param[0])>=1) && ( intval($param[1])>=1) && ($param[2]==0)){
$stmt->bind_param("ii",$param[0],$param[1]);
}
if( ( intval($param[0])>=1) && ( intval($param[1])>=1) && (intval($param[0])>=1)){
$stmt->bind_param("iii",$param[0],$param[1],$param[2]);
}
/* execute statement */
$stmt->execute();
I set $param[x]>=1 because if the value is 0 means that the field was not selected but if it is selected it must have a value greater or equal than 1 And like I said before, This code works, but I know It can be less, but I don't Know the way