CODEWITHSUNDEEP
javascriptphpjquerysqlajax
zgc7009
zgc7009

Reputation: 3389

Connecting to a server-side SQL database via php with jquery

I have been trying to look over an example to figure out how to connect to a server's SQL database from a client using JQuery, AJAX, and PHP, and though it is old it seems well done and straight forward: Example Link.A single folder contains all of my php files as well as the product version of jQuery (javascript-1.10.2.min.js).

Problem 3 - Fixed

JS console shows [Object, "parsererror", SyntaxError] at

var id = data.data[0];              //get id, data['data'][0] works here as well

in client.php. Object responseText shows ..."No Database Selected"... I have updated my client.php based on Daedalus' response and am still getting the same error.

Error was in mislabeling a variable ($link instead of $con) in server-api.php

-- Code --

db-connect.php:

<?php 

//--------------------------------------------------------------------------
// Example php script for fetching data from mysql database
//--------------------------------------------------------------------------
$host = "localhost";
$user = "root";
$pass = "password";

$databaseName = "server-db";
$tableName = "inventory";

?>

server-api.php:

<?php 

//--------------------------------------------------------------------------
// 1) Connect to mysql database
//--------------------------------------------------------------------------
include 'db-connect-99k.php';
$con = mysql_connect($host,$user,$pass);
$db_selected = mysql_select_db('zgc7009_99k_db', $con);
$array = array('mysql' => array('errno' => mysql_errno($con), 'errtxt' =>mysql_error($con)));

//--------------------------------------------------------------------------
// 2) Query database for data
//--------------------------------------------------------------------------
$result = mysql_query("SELECT * FROM $tableName");          //query
$array['mysql'][] = array('errno' => mysql_errno($con), 'errtxt' =>mysql_error($con));
$array['data'] = mysql_fetch_row($result);                    //fetch result

//--------------------------------------------------------------------------
// 3) echo result as json 
//--------------------------------------------------------------------------
echo json_encode($array);

?>

client.php

<html>
<head>
  <script language="javascript" type="text/javascript" src="jquery-1.10.2.min.js"></script>
</head>
<body>

<!-------------------------------------------------------------------------
1) Create some html content that can be accessed by jquery
-------------------------------------------------------------------------->
<h2> Client example </h2>
<h3>Output: </h3>
<div id="output">this element will be accessed by jquery and this text replaced</div>

<script id="source" language="javascript" type="text/javascript">

$(function () 
{

  //-----------------------------------------------------------------------
  // 2) Send a http request with AJAX http://api.jquery.com/jQuery.ajax/
  //-----------------------------------------------------------------------
  $.ajax({                                      
    url: 'server-api.php',           //the script to call to get data          
    data: "",                        //you can insert url argumnets here to pass to api.php
                                   //for example "id=5&parent=6"
    //dataType: 'json',                //data format  (comment out or get parsererror) 

    // Successful network connection
    // Successful network connection
    success: function(data)          //on recieve of reply
    {
      var id = data.data[0];              //get id, data['data'][0] works here as well
      var vname = data.data[1];           //get name
      //--------------------------------------------------------------------
      // 3) Update html content
      //--------------------------------------------------------------------
      $('#output').html("<b>id: </b>"+id+"<b> name: </b>"+vname); //Set output element html
      $('#error_code').html("Success!");
    },
  error: function() {
    console.log(arguments);
  }
  });
}); 

</script>
</body>
</html>

Problem 1 - Fixed

Thanks to user help, I have managed to get rid of my original error of:

OPTIONS file:///C:/Users/zgc7009/Desktop/Code/Web/php/server-api.php No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'null' is therefore not allowed access. jquery.js:8706
XMLHttpRequest cannot load file:///C:/Users/zgc7009/Desktop/Code/Web/php/server-api.php. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'null' is therefore not allowed access.

Problem 2 - Fixed [now running on temporary web server (see link at bottom)]

Now I am running WAMP (including phpmyadmin and apache) as my webserver. I can run my php page with script (client.php) no problem, it runs, can't seem to find any errors in my logs. However, I still never seem to hit the success function of my script. I am assuming that I have inappropriately set something somewhere (eg localhost/"my site".php) but I am not sure where.

I also tried changing my AJAX function a bit, to include .done:

$.ajax({      
  url: 'localhost/server-api.php',           //the script to call to get data          
  data: "",                        //you can insert url argumnets here to pass to api.php
                                   //for example "id=5&parent=6"
  dataType: 'json',                //data format   

  // Successful network connection
  success: function(data)          //on recieve of reply
  {
    var id = data[0];              //get id
    var vname = data[1];           //get name
    //--------------------------------------------------------------------
    // 3) Update html content
    //--------------------------------------------------------------------
    $('#output').html("<b>id: </b>"+id+"<b> name: </b>"+vname); //Set output element html
  }

}).done(function() {
    $('#output').html("AJAX complete");
});

but my output value never gets changed within the ajax call. I could be implementing .done incorrectly, but I just can't seem to figure out why I am not hitting anything and can't seem to find a log that is a help in finding the next step.

On previous edit I removed localhost from php calls ('localhost/server-api.php' returned a 404) and now I am stuck again. I get a 304 Not Modified from my jQuery call, but I thought that, as of jQuery 1.5 ajax handled this as a success so I should still be hitting my html text update (correct?) and I don't.

WAMP access Log:

127.0.0.1 - - [14/Jan/2014:14:22:45 -0500] "GET /client.php HTTP/1.1" 200 2146
127.0.0.1 - - [14/Jan/2014:14:22:45 -0500] "GET /jquery.js HTTP/1.1" 304 -
127.0.0.1 - - [14/Jan/2014:14:22:45 -0500] "GET /server-api.php HTTP/1.1" 200 38

Note - this is the only log that updates when I refresh client.php in my browser. my js console stays blank. I have uploaded this to a temp site: zgc7009.99k.org/client-99k.php

Upvotes: 0

Views: 6851

Answers (2)

Daedalus
Daedalus

Reputation: 7722

Forgive me if the following is drawn out, but I wish to explain all that I can;

Firstly, as noted in comments, the error method of the jQuery .ajax() method only gets called if there is an error when the method attempts to load the requisite php page you(or it(if you don't specify a url, it uses the current page)) has specified. An error in this regard would be something like a 404(page not found), 500(server error), or what-have-you.

The current error you are experiencing is two-fold:

  1. You are not running a server on your computer(or you are and aren't accessing the page via the correct url in your browser(it should be localhost/path/to/file.extension)
  2. Same origin policy is preventing your page from even being loaded

In regards to problem #1, a php page needs to be processed by your php interpreter, which you need to have installed on your system. I would recommend something like xampp to suit this case, though there are plenty others available.

When accessing a server which is running on your machine, one uses the localhost url in the address bar, no protocol(http://,https://,ftp://,etc), and never a file:/// protocol. For example, if I were to visit the folder test's index.php file, it would be localhost/test/index.php.

In regards to problem #2, browsers have various restrictions in place in order to prevent malicious code from executing.. One of these restrictions is the Same Origin policy, a policy which restricts documents of a differing origin than the originating request from accepting that request. For example..

If we have a server at domain.website.com, and it makes a request to otherdomain.website.com, the request will fail as the endpoint of the request is on a different domain.

Likewise, the same exists for any requests made in regards to a file:/// protocol.. It is always1 treated as a different origin, and it will always1 fail. This behavior can be changed, but it is not recommended, as it is a security hole.

I also recommend you check out MDN's article on SOP.

Of course, to fix all this.. install a web server(like xampp or wamp) on your machine(depending on your OS) or use a hosted web server, never open your html file by double clicking it, but by visiting its url(according to your webserver's directory(it differs per server)), and always make sure your domains and ports match.

  • 1: Except in certain cases, such as here

Edit 1:

Don't know why I didn't see this before; we could have avoided headaches.. anyway, firstly, change the error catching you do here:

$dbs = mysql_select_db($databaseName, $con);
echo mysql_errno($con) . ": " . mysql_error($con). "\n";

To:

$array = array('mysql' => array('errno' => mysql_errno($con), 'errtxt' =>mysql_error($con)));

And then, change your array set after your db handling to this:

$result = mysql_query("SELECT * FROM $tableName");          //query
$array['mysql'][] = array('errno' => mysql_errno($con), 'errtxt' =>mysql_error($con));
$array['data'] = mysql_fetch_row($result);

To explain what I've changed, and why.. Your first echo was causing the json parser to fail when parsing your echoed json. If you didn't have your console open during your refresh, you wouldn't have seen that it did in fact execute the ajax request. You also do not define an error handler, so you would have never known. In order to parse the new json I just created above, modify your success handler's variable declarations above into this:

var id = data.data[0];              //get id, data['data'][0] works here as well
var vname = data.data[1];           //get name

Of course, if your mysql causes any errors, you can then access those errors with the following:

console.log(data.mysql);

Again, in your success function. To see if you have any errors with the actual .ajax() method or such, you can just do this for your error handler:

error: function() {
    console.log(arguments);
}

Upvotes: 3

Normal9ja
Normal9ja

Reputation: 91

please you should start learning to PDO or Mysqli real fast, mysql_* will soon be depreciated, that is soonest, let me rewrite your query for you using PDO and prepared statements, you can kick it off from there.

$connectionStr = 'mysql:host='.$host.';dbname='.$databaseName.'';   
$driverOptions = array();    

     $dbh = new PDO($connectionStr, $user, $pass, $driverOptions);              
     $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);     

     $query  =  $dbh->prepare("SELECT * FROM $tableName");
     $query->execute();

     $array = fetch(PDO::FETCH_OBJ); 

     echo json_encode($array);

Upvotes: -1

Related Questions