CODEWITHSUNDEEP
phpjavascriptmysqljson
Mario Cordeiro
Mario Cordeiro

Reputation: 119

Javascript get values from database

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

Upvotes: 0

Views: 2640

Answers (2)

user788472
user788472

Reputation:

First off, please stop using the mysql extension. It is deprecated and you should instead be using mysqli or PDO. The reason you're getting only one row is that the function you're using, mysql_fetch_assoc only returns one row. You want mysqli_fetch_all or the PDO equivalent. $.getJSON is a correct function to use, and setting values like you are should probably work, I would console.log(data) to find out what's going wrong.

Upvotes: 1

radalin
radalin

Reputation: 600

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 */})

Upvotes: 1

Related Questions