fetch rows from mysql using Ajax

Question

I am trying to retrieve a row from mysql db using ajax, code bellow:

jQuery.ajax({
                        type: 'POST',
                        url: 'Connection.php',
                        dataType: 'text',
                        data: {'query_id' : query_id},
                        success: function(response){
                            data = response;
                            alert(data['username']); //print undefined!!!
                        },
                        error: function(xhr, ajaxOptions, thrownError){
                            alert("thrownError");
                        }
                    });

Here is my mysql php code:

<?php

    $con = mysql_connect('****','****','****');
    mysql_select_db("eBay",$con);

    $username = $_SESSION['username'];
    $query_id = $_POST['query_id'];

    $myquery = "SELECT * FROM  `Output` WHERE `username` =" '$username';
    $query = mysql_query($myquery);
if ( ! $query ) {
    echo mysql_error();
}

    $data = mysql_fetch_array($query);
    echo ($data);

mysql_close($server);
?>

In the response I get, I have undefined array cell. Any idea?

**Danger**: You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Apr 20 '14 at 13:06
Forget about Ajax. Look at the output of your PHP program in your browser. It probably isn't what you think it is. The data doesn't seem to match what you expect and it is an HTML document not a JSON document. — Quentin, Apr 20 '14 at 13:07
@Quentin when I change $data to be `echo ($data['username']);` it works fine. — Anonymous, Apr 20 '14 at 13:17

jkirchne · Answer 1 · 2014-04-20T13:12:29.787

0

to return just the username from php:

$data = mysql_fetch_assoc($query)['username'];

in your javascript you should be able to do alert(data) instead of searching for the username tuple in an array.

Ideally you should return your data back in json instead of text that way it can remain structured and easier to access in javascript. You will need to change your ajax option to dataType: 'json' and the PHP code to :

$data = json_encode(mysql_fetch_assoc($query));

edited Apr 20 '14 at 13:12

answered Apr 20 '14 at 13:07

jkirchne

121
3

I want to return the entire row. When I make the changes I get `alert("thrownError");` message because there is an error from the ajax call – Anonymous Apr 20 '14 at 13:39
Are you able to find out what is causing the error? – jkirchne Apr 20 '14 at 13:41
I noticed a missing " after $username' $myquery = "SELECT * FROM `Output` WHERE `username` =" '$username'; – jkirchne Apr 20 '14 at 14:26

score 0 · Answer 2 · answered Apr 20 '14 at 17:16

You are getting array in $data variable so you should use json_encode function to get all values from resulting row like this-

 $myquery = "SELECT * FROM  `Output` WHERE `username` ='".$username."'";

foreach ($myquery as $test) 
  {
     $field1_value =$test->name_of_field1_in_db;
     $field2_value =$test->name_of_field2_in_db;
     $field3_value =$test->name_of_field3_in_db;                
  }

$all_values_in_array=array('field1'=>$field1_value,'field2'=>$field2_value,'field3'=>$field3_value,);

echo json_encode(echo json_encode($arr_provider);

and get all value on ajax suucess function like this--

success: function(response){
         var pro = jQuery.parseJSON(response);
         var field1_val=pro.field1;  //var field1 contain value of field1 in db
         var field2_val=pro.field2;
         var field3_val=pro.field3;
                        },

hope it will help you. Best of Luck

fetch rows from mysql using Ajax

2 Answers2