CODEWITHSUNDEEP
phpmysqlsqljquery
Byate
Byate

Reputation: 123

Retrieving multiple columns from MySQL to Jquery and outputting as table

This is something of a beginner's question, I've successfully set up a local database that returns the LOCATION column based on a search.

What I'd like to understand is which part of the code in the PHP file I can manipulate to return multiple / all columns from that search into a multi column div table, rather than just the LOCATION column. I've played around with using * instead of LOCATION but get an 'unexpected * on line 11' error when searching.

Any pointers would be of great use and help me to understand the fundamentals better.

JS File

$('input#name-submit').on('click', function() {
var name = $('input#name').val();
if ($.trim(name) != '') {
$.post('ajax/name.php', {name: name}, function(data) {
  $('div#name-data').text(data);  
});
}

});

PHP file (name.php)

<?php
if (isset($_POST['name']) === true & empty($_POST['name']) === false) {
require '../db/connect.php';

$query = mysql_query("
SELECT `LOCATION`
FROM `table 3`
WHERE `table 3`.`ARTIST` = '" . mysql_real_escape_string(trim($_POST['name'])) . "'
");

echo (mysql_num_rows($query) !== 0) ? mysql_result($query, 0, `LOCATION`) : 'Name    not found';
}

Upvotes: 2

Views: 1054

Answers (1)

Dave
Dave

Reputation: 3658

You currently are returning a single item (one column from one row) from the MySQL results. You will need to modify your code so that you can return multiple items. In the example below data from two columns are sent back to the JQuery script in JSON format, because parsing it is easy.

<?php
if (isset($_POST['name']) === true & empty($_POST['name']) === false) {
    require '../db/connect.php';
    $sql = "SELECT 
                `LOCATION`, `SOME_OTHER_COLUMN`
            FROM 
                `table 3`
            WHERE 
                `table 3`.`ARTIST` = '" . mysql_real_escape_string(trim($_POST['name'])) . "'";
    $result = mysql_query($sql);
    if ( mysql_num_rows($query) !== 0 ) {
        $data = json_encode(mysql_fetch_assoc($query));
        echo $data;
    }
}
?>

if you want to format the data in PHP before sending it back to JQuery, you could do something like this:

if ( mysql_num_rows($query) !== 0 ) {
    while ( $row = mysql_fetch_assoc($query) ) {
        $data = 'Blah blah '.$row['LOCATION'].' blah, blah '.$row['SOME_OTHER_COLUMN'];
    }
    echo $data;
}

Upvotes: 2

Related Questions