How can I retrieve multiple data from MYSQL using PHP and Javascript?

Question

I have this code below that retrieves data from the database when the user inputs the PRD number and hits the "enter" key. It works fine for displaying single data but I need to display multiple data(PRDNO, PRDITEMCODE, PRDQTY).

Js code:

$("#prNum").keypress(function(e){
    if(e.keyCode==13){
        var getPR = $("#prNum").val(); //gets the value entered on supCode textbox

        if($.trim(getPR) != ""){
            $.post("ajax/prdetails.php", {getPR: getPR}, function(data){
                if(data == "error"){
                    alert("Purchase Request number not found.");
                    $("#prNum").text("");
                }       
                else
                {
                    $("#displayPRD").text(data);
                }

            });

        }

    }    

}); //end prNum keypress

Php code:

if(isset($_POST['getPR']) === true && empty($_POST['getPR']) === false)
 {
    mysql_connect('localhost', 'root', '') or die("Could not connect to server.");
    mysql_select_db('cch');

    $query = mysql_query("
        SELECT `PRDNO` , `PRDITEMCODE` , `PRDQTY`
        FROM `prdetailfile`
        WHERE `PRDNO` = '" . mysql_real_escape_string(trim($_POST['getPR'])) . "' 
    ");
    if (!$query) 
    {
        die(mysql_error());
    }

    echo (mysql_num_rows($query) !== 0) ? mysql_result($query, 0, 'PRDNO') : 'error';

 }

I can't seem to find a way to store each column value in an array. Btw, I need to display the data by rows in an existing table in HTML and should look like this:

PRDNO | PRDITEMCODE | PRDQTY

PR001 | IC001 | 50

PR001 | IC002 | 40

PR001 | IC003 | 30

The Purchase Reuquest(PRDNO) can have multiple item orders(PRDITEMCODE) with corresponding quantity. I would greatly appreciate if someone can help me with this. THANKS!

EDIT: Sorry for the poor representation of a table. I can't post an image yet but that's how the table should look like :)

Answer 1

There are couple of ways to solve this problem - you could return the results in json format or html format instead of getting just one value.

Echo the following in PHP

while($row = mysql_fetch_array($result))
{echo '<tr><td>'.$row['PRDNO'] . "</td><td>" . $row['PRDITEMCODE']."</td><td>".$row['PRDQTY'];
echo "</td></tr>";
}

You can then simply append the result to the table using jquery.

$('#myTable tbody').append(data);

Answer 2

Use

mysql_fetch_array()

function to fetch details from database Try JSON Encode function to return multiple values

Like

  echo json_encode($functionArray);

by using those combination you can get what you wanted to do

Answer 3

Though your query fetching records for all the required columns, you are sending only the PRDNO back to client. You should construct a JSON object of the query result and send that JSON string to client which can be used to render in HTML.

These links should help too:

http://www.thomasfrank.se/mysql_to_json.html

Return JSON Response from a MySQL Query

Answer 4

mysql_query is deprecated Use mysqli or PDO instead