Javascript从数据库中获取值

Question

I have a html page and a php page. The html page has a table and the php has a query to a mysql table. I don't know how to show the query results in my html page.

The html page has the following partial code:

<table class="table-striped" id="tabela_editavel_1">
    <thead>
        <tr>
            <th>Contacto</th>
            <th>Dt Receção</th>
            <th>Dt Validade</th>
            <th>Refª Cliente</th>
            <th>Num Proposta</th>
            <th>Estado</th>
        </tr>
    </thead>
    <tbody id="dados_tabela">

        <tr class="">
            <td id="xz"><input id="contacto"/></td>
            <td id="xz"><input id="dtrececao"/></td>
            <td id="xz"><input id="dtvalidade"/></td>
            <td id="xz"><input id="ref_cli"/></td>
            <td id="xz"><input id="numproposta"/></td>
            <td id="xz"><input id="estado"/></td>
        </tr>

    </tbody>
</table>

<script>
$.getJSON('php/c_listaconsultas.php', function(data) {
  $('#contacto').val(data.nome);
  $('#dtrececao').val(data.datarecepcao);
  $('#dtvalidade').val(data.validadeconsulta);
  $('#ref_cli').val(data.referenciaconsulta);
  $('#numproposta').val(data.propostanumero);
  $('#estado').val(data.estado);            
});
</script>

My php file has the code:

(...)

mysql_select_db($database_connLOOPGEST, $connLOOPGEST);
$query_rs_listaconsultas = sprintf("SELECT clientes_consultas.id_cliente, clientes_consultas.id_consulta, clientes_consultas.datarecepcao, clientes_consultas.referenciaconsulta, clientes_consultas.validadeconsulta, clientes_consultas.tituloconsulta, loop_propostas.propostanumero, clientes_contactos.nome, loop_propostas.id_proposta, status.descricao estado, clientes.nome_curto nome_cliente FROM (clientes_consultas left join clientes_contactos on clientes_consultas.id_contacto = clientes_contactos.id_contacto) left join loop_propostas on clientes_consultas.id_consulta = loop_propostas.id_consultacliente inner join status on status.id_status = clientes_consultas.estado inner join clientes on clientes.id_cliente = clientes_consultas.id_cliente WHERE clientes_consultas.id_cliente=%s ORDER BY clientes_consultas.datarecepcao DESC", GetSQLValueString($numcliente_rs_listaconsultas, "int"));
$rs_listaconsultas = mysql_query($query_rs_listaconsultas, $connLOOPGEST) or die(mysql_error());
$row_rs_listaconsultas = mysql_fetch_assoc($rs_listaconsultas);
$totalRows_rs_listaconsultas = mysql_num_rows($rs_listaconsultas);

echo json_encode($row_rs_listaconsultas);

First of all, my "echo json_encode" doesn't give me all the query rows, but only one, how can a pass all the query rows to my html page? What is missing here? :((( The second help I need is how can I show those values in my table? I'm using the correct function (.$getJSON)?

Thanks in advance. Mário

Answer 1

You must iterate over the mysql_fetch_assoc with something like:

while ($row = mysql_fetch_assoc($result)) {
    $row_rs[] = $row;
}

And the returned data will be an array containing objects. You should iterate over it with:

$.each(data, function(element) { /* do something */})