0

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

Improve this question
10
  • In this case old is not good. You really should not be learning to use mysql_* functions as they are deprecated. Instead, find tutorial that uses mysqli or PDO. Commented Jan 13, 2014 at 23:23
  • 1
    So what have you done to narrow down the problem? Have you looked in web developer tools to see if the AJAX call is even made, or whether it gets a 200 response code (or something else) from the server? Do you see any errors in your console log or PHP server logs? Commented Jan 13, 2014 at 23:26
  • Error doesn't trigger on a failed db connection, it triggers on network related problems. Read the documentation instead of just assuming. Commented Jan 13, 2014 at 23:30
  • Thanks for the response, I really have done very little with web based anything so this is a bit of a grind for me. I thought I checked the appropriate console but apparently I missed something. On double-checking my web developer console there was an error with my error: function call in my client.php. After removing that, everything loads without error in the web-console, ajax is being called, but hitting with success fails. Any way you could point me in the right direction as to figuring out why it isn't successful? Commented Jan 13, 2014 at 23:36
  • Daedalus - I did read the documentation I found @ api.jquery.com/jquery.ajax and thought that it fit with what I am trying to do. My comments are poorly worded, the assumption that I made is that if I connected to the network I would be able to connect to the database, apparently this isn't the case. Commented Jan 13, 2014 at 23:39

2 Answers 2

Reset to default
3

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);
}
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Much thanks for all of the help, I know it can be frustrating helping someone that doesn't even really know what they are asking. I will check this out and get back to you with a response, but I am going to presume it has me in the right direction.
Here's some other bit of good reading as well.
Three sentences in and already a helpful link. Big props to you for taking extra time to help
I have updated my question, I am making progress but still seem to be missing something. From what I can tell, everything is setup through WAMP appropriately (from what I can tell). Whatever I am missing, it has something to do with my AJAX call. I do have some more reading to do, just figured I would update. Thanks very much again.
@zgc7009 .. Sorry again; didn't see it before, you never do select a db, see here for how to do that.
|
-1

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);
Improve this answer

9 Comments

Re-writing the query using PDO doesn't answer the OP's question. On top of that, this doesn't use paramiterized queries, and thus is not safe from injection attacks.
Thank you for the input, but I still don't understand why I am having trouble connecting to my database. Though it may be an old way of doing it, it should still work and doesn't for me.
@zgc7009 Please account for mysql connection errors, and give the requisite error you get should you get one. You won't be able to tell if there is something wrong with your query without it.
@Daedalus As ignorant as this may make me sound, how do I check my connection log? I thought I figured out how to run error logs but I guess I am still confused. Is it done using mysqli_error()? This project is turning out to be more of a headache than I thought, didn't realize there was so much to server-client interactions.
@zgc7009 This page should describe what you need to do to troubleshoot your db interaction.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.