I'm going to the point, I'm doing a search engine and when I put certain names or data, the results are shown, the problem is with some others where nothing is shown and it shows me the following warning
Warning: sqlsrv_fetch_array () expects parameter 1 to be resource, boolean given in
and I still do not know how the error is solved, I pass the part where the error marks me.
Here my code:
<?php
//USAR dicc.php para cambiar usuario pass y DB
error_reporting(-1);
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
ini_set('max_execution_time', 300);
function dd($attr){
print_r($attr);
echo "-----<br>";
var_dump($attr);
echo "-----<br>";
echo serialize($attr);
die();
}
$logFile = "querys.log";
function creaWhere($attr, $cols){
$tmp = ""; // variable temporal para sacar los wheres de la query
if (count($attr) != 0) {
foreach ($attr as $val) {
$tmp .= " (";
foreach ($cols as $col) {
$tmp .= ($col === end($cols))? $col." LIKE "."'%".$val."%'" : $col." LIKE "."'%".$val."%' OR ";
}
$tmp .= ($val === end($attr))? ")" : ") AND";
}
return $tmp;
}else{ return "1";} //regresa 1 para mostrar todos los datos de la tabla
}
include "dicc.php";
//variables POST
$serch = utf8_encode($_POST['search']);
$table = $_POST['table'];
$idx = isset($_GET['idx']) ? $_GET['idx'] : 1;
//--------------
$indexOffset = $idx != 0 ? ($idx - 1) * 10 : 0;
$attr = explode(' ', $serch); //agregamos cada palabra del search a una entrada de attr
$serverName = SVR;
$connectionInfo = array( "Database"=>DBO, "UID"=>USR, "PWD"=>PSS, "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}
//-------------------------------
$where = creaWhere($attr, $COLUMNS[$DBOS[$table]]); //le metemos los atributos de busqueda y las columnas en las que debe de buscar a la funcion
$queryCount = "SELECT COUNT(*) FROM ".$DBOS[$table]." where $where" ;
$start = microtime(true);
$count = sqlsrv_fetch_array(sqlsrv_query($conn, $queryCount))[0];
file_put_contents($logFile, $queryCount."\n TOTAL de resultados: ".$count);
if ($count == 0) { //checa si hay resultados
$hay = 0;
}else {
//crea la query que devolvera las columnas indicadas en el diccionario en el array SELECTS, se crea despues de confirmar que hay resultados con la primera query
$fetch = ($count - $indexOffset) > 10 ? "FETCH NEXT 10 ROWS ONLY" : "";
$query = "SELECT ".
implode(", ", $SELECTS[$DBOS[$table]]) .
" FROM ".$DBOS[$table]." WHERE ". $where .
" ORDER BY ". $COLUMNS[$DBOS[$table]][0] ." ASC".
($count <= 10 ? "" : " OFFSET $indexOffset ROWS $fetch");
file_put_contents($logFile, "\n".$query, FILE_APPEND);
$results = sqlsrv_query($conn, $query); //ejecuta la query
$hay = 1;
// si hay resultados extrae los headers de las columnas para despues ponerlo en la tabla html
$end = microtime(true) - $start;
file_put_contents($logFile, "\n tiempo de ejecución TOTAL: ".$end, FILE_APPEND);
file_put_contents("time-data.txt", count($attr)." ".$end."\n", FILE_APPEND);
}
And this is where it marks the error exactly on the line of the while
//imprime resultados codificados a utf8 en caso de no haber resultados no hace nada
if($hay){
$i = 0;
while($result = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) {
if ($i == 0) {
$headers = array_keys($result);
echo "<thead><tr>";
foreach ($headers as $head) {
echo "<th> $head </th>";
}
echo "</tr></thead><tbody>";
}
$i+=1;
echo "<tr>";
foreach ($result as $col) {
if($col instanceof DateTime){
$txt = $col->format('Y-m-d');
}
else{
$txt=(string)$col;
}
echo "<td>". utf8_encode($txt) ."</td>";
//echo $col;
//echo $col->format("Y-m-d");
}
echo "</tr>";
}
}
That's it, if more code is needed I can put it