mysql_fetch_assoc() expects parameter 1 to be resource json

Question

I'm doing a reporting system and I have this code, and i don't know why it keep throwing that error

header('Access-Control-Allow-Origin: *');
    $conn=mysql_connect($hostname,$username,$password);
    mysql_select_db($database);
    $where = "where 1=1 ";
    if($_GET["desarrollo"] != 0)
        $where .= " and satisfaccion_cliente.clave_desarrollo = ".mysql_real_escape_string($_GET["desarrollo"])." ";
    if($_GET["del"] != "" && $_GET["al"] != "")
        $where .= " and fecha_creacion between '".mysql_real_escape_string($_GET["del"])."' and '".mysql_real_escape_string($_GET["al"])."' ";
    $sql = "SELECT COUNT (id_encuesta) as total, primer_apellido, segundo_apellido, nombres";
    $sql .= "FROM satisfaccion_cliente";
    $sql .= "INNER JOIN usuarios ON satisfaccion_cliente.clave_usuario = usuarios.clave_usuario";
    $sql .= "INNER JOIN personas ON usuarios.id_persona = personas.id_persona";
    $sql .= "INNER JOIN desarrollos ON desarrollos.clave_desarrollo = satisfaccion_cliente.clave_desarrollo";
    $sql .= $where;
    $sql .= "GROUP BY satisfaccion_cliente.clave_usuario";
    $data = array();
    $data["total"] = 0;
    $data["data"] = array();
    $resultado = mysql_query($sql);
    while ($fila = mysql_fetch_assoc($resultado)) {
        $data["total"] += $fila["total"];
        $temp["nombre"] = utf8_encode($fila["nombres"]." ".$fila["primer_apellido"]." ".$fila["segundo_apellido"]);
        $temp["total"] = $fila["total"];
        $temp["color"] = $color[rand(0, 24)];
        array_push($data["data"],$temp);
    }
    mysql_free_result($resultado);
    mysql_close($conn);
    echo json_encode($data);

I hope you can help me, thank you

score 0 · Answer 1 · answered Nov 24 '13 at 19:15

You get this error because mysql_query returns false. There is an error in your SQL statement. Use mysql_error to find out what's wrong.

$resultado = mysql_query($sql);
if(!$resultado) echo mysql_error();
while ($fila = mysql_fetch_assoc($resultado)) {

You are missing spaces when concatenating. Add spaces, maybe your query will run without errors

$sql = "SELECT COUNT (id_encuesta) as total, primer_apellido, segundo_apellido, nombres ";
$sql .= "FROM satisfaccion_cliente ";
$sql .= "INNER JOIN usuarios ON satisfaccion_cliente.clave_usuario = usuarios.clave_usuario ";
$sql .= "INNER JOIN personas ON usuarios.id_persona = personas.id_persona ";
$sql .= "INNER JOIN desarrollos ON desarrollos.clave_desarrollo = satisfaccion_cliente.clave_desarrollo ";
$sql .= $where;
$sql .= "GROUP BY satisfaccion_cliente.clave_usuario";

Thank you, the problem was by the spaces – Axri Nov 24 '13 at 19:50 — Axri, Nov 24 '13 at 19:50

score 0 · Answer 2 · answered Nov 24 '13 at 20:07

I took the liberty of changing a bunch of things that just hurt the eyes (for my own clarity and sake). I rewrote your code to get rid of what seems the most probable culprit

missing spaces between sql keywords
general lack of clarity (my own bias and OCD at work)

Corrected and reformated code:

header('Access-Control-Allow-Origin: *');
$conn = mysql_connect($hostname,$username,$password);
mysql_select_db($database);
$where = array();
if($_GET['desarrollo'] !== '0') {
  $where[] = 's.clave_desarrollo="'.mysql_real_escape_string($_GET["desarrollo"]).'"';
}
if($_GET['del'] !== '' && $_GET['al'] !== '') {
  $where[] = sprintf('u.fecha_creacion BETWEEN "%s" AND "%s"',
             mysql_real_escape_string($_GET['del']),
             mysql_real_escape_string($_GET['al']));
}
$sql = 'SELECT COUNT (id_encuesta) as total, primer_apellido, segundo_apellido, nombres';
$sql .= ' FROM satisfaccion_cliente AS s';
$sql .= ' INNER JOIN usuarios    AS u ON s.clave_usuario    = u.clave_usuario';
$sql .= ' INNER JOIN personas    AS p ON u.id_persona       = p.id_persona';
$sql .= ' INNER JOIN desarrollos AS d ON d.clave_desarrollo = s.clave_desarrollo';
$sql .= $where ? ' WHERE '.implode(' AND ',$where) : '';
$sql .= ' GROUP BY s.clave_usuario';
$data = array();
$data['total'] = 0;
$data['data'] = array();
//if ($debug) echo __FILE__.' query: '.$sql.PHP_EOL;
$resultado = mysql_query($sql);
while ($fila = mysql_fetch_assoc($resultado)) {
  $data['total'] += $fila['total'];
  $temp['nombre'] = utf8_encode($fila['nombres'].' '.$fila['primer_apellido'].' '.$fila['segundo_apellido']);
  $temp['total'] = $fila['total'];
  //$color is currently undefined
  $temp["color"] = $color[rand(0, 24)];
  $data['data'][] = $temp;
}
mysql_free_result($resultado);
mysql_close($conn);
echo json_encode($data);

This is obviously in spanish, so just in case, I'll make it very clear just for you

En tu sentencia sql hace falta muchos espacios, deberías añadir una línea de depuración en tu entorno de desarrollo para confirmar que tus sentencias son correctas. También deberías informarte a traves de mysql_error() y mysql_errno() si hay algún error durante la ejecución de la sentencia sql. Perdona que me haya tomado la libertad de reescribir grandes porciones de tu código, lo hago para poder aclararme mejor

mysql_fetch_assoc() expects parameter 1 to be resource json

2 Answers2