Retrieving multiple columns from MySQL to Jquery and outputting as table

Question

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';
}

mysqli deprecation protestors in 3,2,1...

Eisa Adil
– Eisa Adil

2013-12-19 19:27:55 +00:00
Commented Dec 19, 2013 at 19:27 — Eisa Adil
– Eisa Adil, Commented Dec 19, 2013 at 19:27

Dave · Accepted Answer · 2013-12-19 19:53:09Z

2

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;
}

answered Dec 19, 2013 at 19:53

Dave

3,6581 gold badge18 silver badges9 bronze badges

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

1 Comment

Byate Over a year ago

This is great - didn't think about how JSON could work with this, so it's proved very useful.

Collectives™ on Stack Overflow

Retrieving multiple columns from MySQL to Jquery and outputting as table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related