How to fetch correct data from database with ajax

Question

In my project, I use ajax to fetch data from DB. And I test the data content, I choose alert(valData) in success function. But unlucky, nothing return from ajax. I tested

select contact from IDC WHERE id=5;

It works fine in mysql cmd line.

Here is my js code:

var stNum = 5;
$.ajax({
        dataType:'json',
        type:"POST",
        url:"get_ajax_csc.php",
        data:{stNum:stNum},
        success:function(data) 
        {
         var valData = data;
         alert(valData);
        }
      });

Here is get_ajax_csc.php code:

<?php
if(isset($_POST['stNum']))
{   
 include("DB.php"); 
 $q=$_POST["stNum"];
 $sql="select contact from IDC WHERE id='".$q."';";
 $sel = $conn->query($sql); 

 $arr = $sel->fetch(PDO::FETCH_ASSOC);
 echo $arr['contact'];
 }

if(isset($_POST['htmlCnt']))
{   
include("DB.php");
$htcnt=stripslashes(".$_POST['htmlCnt'].");
........ 
}
 ?>

Here is DB.php code:

<?php
session_start();
$pwd=$_SESSION['password'];
$user=$_SESSION['user'];

try 
{
  $conn = new PDO('mysql:host=x.x.x.x;port=3306;dbname=hpc',$user,$pwd);
}
catch (PDOException $e)
{
  echo "account or pwd wrong <meta http-equiv='refresh' content='1;url=index.html'>";
    exit;
}  
$conn->setAttribute(PDO::ATTR_ORACLE_NULLS, true);
?>

It seems nothing wrong in my code, but I cann't fetch data from DB

I have no idea about the error, who can help me ?

Please, please, dont store your password and user in the session. It is terribly insecure. — Erik Baars, Sep 11 '17 at 08:37
Press F12 with Chrome -> Network -> find your request and check out if all data is correct. — RaV, Sep 11 '17 at 08:37
$sql="select contact from IDC WHERE id='".$q."';"; why you use ' ' id is an int. header('Content-type: application/json') on get_ajax_csc.php — daheda, Sep 11 '17 at 08:38
dataType:'json' => expect json in return, echo $arr['contact']; => is this json ? if not, you're in error callback on ajax — ekans, Sep 11 '17 at 08:38
for print use json_encode( $arr['contact']); not echo ..in php file(get_ajax_csc) — pedram shabani, Sep 11 '17 at 08:40
@stack First you have to check $arr['contact']; has some value in get_ajax_csc.php. For test you can echo with static value Ex. echo 'test '.$arr['contact']; — BSB, Sep 11 '17 at 08:41
@Erik, I know store password and user in cookie is dangerous. But session is in the server, besides i need user and password to log on database when another page is clicked. I don't know why you say it is insecure? but how? — stack, Sep 11 '17 at 09:04
@daheda, I have tested that it doesn't matter between id is an int and $q is string. where id=5 and where id='5' are both working fine. The key is dataType:'json'. Delete it , and works fine. — stack, Sep 11 '17 at 09:09
@BSB, i have tested $arr['contact'] has right value. stripslashes meth makes ajax return nothing, see my update in get_ajax_csc.php code.But I don't know why — stack, Sep 12 '17 at 03:55

score 0 · Answer 1 · answered Sep 11 '17 at 08:53

The data that you send back from the server to the client is not a valid json data as you have specify in the ajax that you are expecting a json data.

Also your queries are not the same :

SELECT contact from IDC WHERE id=5; // This is the correct query you run on the cmd line.



$sql="select contact from IDC WHERE id='".$q."';"; // This is the one from your php

if you take a close look at the queries the first one id is treated as an integer on the second one as a string. See When to use single quotes, double quotes, and backticks in MySQL

Also you are using PDO so take the advantage of using prepared statements. Try the below code:

<?php
if (isset($_POST['stNum'])) {
    include("DB.php");

    $q   = $_POST["stNum"];
    $sql = "select contact from IDC WHERE id= ? ";

    $sel = $conn->prepare($sql);
    $sel->bindParam(1, $q, PDO::PARAM_INT);
    $sel->execute();
    $arr = $sel->fetchColumn(PDO::FETCH_ASSOC);
    echo json_encode($arr['contact']); //send back json data to the client
}
?>

on my god, stripslashes makes ajax return nothing. It is not about integer as a string, see get_ajax_csc.php code update — stack, Sep 12 '17 at 03:51
what do you mean? I have tried many times that without stripslashes(), Div content can not be update to my database. — stack, Sep 12 '17 at 08:44

How to fetch correct data from database with ajax

1 Answers1