Javascript get values from database

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

radalin · Accepted Answer · 2013-04-06 23:00:38Z

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

answered Apr 6, 2013 at 23:00

radalin

6005 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user788472 Over a year ago

Why iterate when he is just going to encode it? Much better to just fetch all the results. Also, why iterate over the data when you can simply access the properties?

Mario Cordeiro Over a year ago

Radalin, thank you for your answer. I've answered to Nathan, and it is the same for your. Thank you for your help.

user788472 · Accepted Answer · 2013-04-06 23:01:17Z

1

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.

answered Apr 6, 2013 at 23:01

user788472

1 Comment

Mario Cordeiro Over a year ago

Nathan, Thank you for your help. I'm trying to learn something "by my own" and I didn't know about mysqli. Now I read something about it and I will use it. I appreciate your tip, thanks once more. At this moment I'm able to see my data in a table, but now the "search" and the "pagination" of the table are not working it says that my table have "0 rows". I'm using the DataTables and I'm reading about it and passing the corrects (apparently) values (I've checked with console.log(data)). I don't know what is the problem at the moment.

Collectives™ on Stack Overflow

Javascript get values from database

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related