Connect to PHP MySQL with Javascript from external server

Question

Okay here is the problem. I have the following PHP code.

    <?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM Persons");

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

Which I got off of W3Schools and works fine when on the server that the database is on. However what I want to do is display that on another site using the following Javascript.

    function displaydata()
{
var xmlhttp;
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("datalist").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","phpscripthere.com/myphp.php",true);
xmlhttp.send();
}

The above javascript works but only when it is on the server that the database is on. What I need is to get the Javascript to work on another location while the PHP can still be stored on the main server.

Javascript has a cross domain policy that blocks requests going to other domains. You'll need to enable CORS or use JSONP to get this working. — adeneo, Apr 03 '13 at 07:38
FYI http://usejquery.com/posts/the-jquery-cross-domain-ajax-guide — Li-chih Wu, Apr 03 '13 at 07:44
Alternatively you can call a page on your server by ajax which fetches the remote page by using CURL. — LoneWOLFs, Apr 03 '13 at 07:45
It should be mentoined here, that you lose security when using jsonp, ie. there is no post, so all data posted will be exposed to the user, all routers in between and the web servers, each of them logging the information. — scones, Apr 03 '13 at 07:45

Volkan · Answer 1 · 2013-04-03T09:14:31.210

4

Please do not mark this as answer, since it is not.

But this is rather recommendations.

This is w3Schools virus spreading....

$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno())
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

In this script processing continues even when the connection fails.

You should incorporate more failure checking and exit on them like this:

if (mysqli_connect_errno())
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
   exit(0);
}

Also you do not know if

$result = mysqli_query($con,"SELECT * FROM Persons");

query is successfull.

Apart from these pitfalls

You should better collect your data for the page and encode it into JSON and send to the client.. as Follows:

$r = array();
while(true){
    $row = mysqli_fetch_array($result);
    if(!$row)break;
    $r[] = json_encode((object)$row);
}
echo json_encode($r);

forming [  {"firstName":"John", "lastName": "Smith"}, 
           {"firstName":"Abraham", "lastName":"Lincoln"}, ...etc ]

then upon receiving decode the data in js and inject to your page.

And of course use ajax cross domain requests (less secure) or make your server talk to the others (more secure) to solve your problem.

edited Apr 03 '13 at 09:14

answered Apr 03 '13 at 07:47

Volkan

2,212
1
14
14

2

It's great to teach people the right stuff, but should this be an answer or a comment? – Zlatko Apr 03 '13 at 07:49
Wait the rest is coming. I am not after reps, I start to encounter the same thing agin and again. – Volkan Apr 03 '13 at 07:50
1

Thank you for the advice. I understand W3Schools is just their to teach and not necessarily their to give the best practices. – Ben P. Dorsi-Todaro Apr 03 '13 at 08:19
@Ihsan Wait a second. I'm a tad bit lost. So let me see if I can post an example of what I'm getting from you to see if I understand you or not.$r = array(); while(true){ $row = mysqli_fetch_array($result); if(!$row)break; $r[] = json_encode(object($row)); } echo json_encode($r); forming [ {"firstName": $row['FirstName'], "lastName": $row['LastName']}] – Ben P. Dorsi-Todaro Apr 03 '13 at 09:01
What I was posting was the same thing as you but I replaced John with a variable and Smith with a variable. I also would most likely add a loop to it since the DB will always be changing. – Ben P. Dorsi-Todaro Apr 03 '13 at 09:05
Uh ! Oh I got it wrong, It should be (object)$row, sooo sorry... Corrected now... – Volkan Apr 03 '13 at 09:14

score 3 · Answer 2 · edited May 23 '17 at 11:48

3

You need make cross domain request https://stackoverflow.com/search?q=javascript+cross+domain (some of them AJAX cross domain call )

edited May 23 '17 at 11:48

Community

1
1

answered Apr 03 '13 at 07:42

Victor

1,449
10
8

Thank you I went to the site you refered me to. I downloaded the files from GITHub I will look threw them. – Ben P. Dorsi-Todaro Apr 03 '13 at 08:21

score 2 · Answer 3 · answered Apr 03 '13 at 07:45

2

Try to make a request using $.ajax with cross domain true like this:

$.ajax({
type: "GET",
crossDomain:true,
url: "phpscripthere.com/myphp.php"
});

answered Apr 03 '13 at 07:45

Gurmeet

3,094
4
19
43

I understand the AJAX code you posted. It looks alot like Javascript. However I have a some other questions should AJAX be in the or should it be refrenced – Ben P. Dorsi-Todaro Apr 03 '13 at 08:18
you can call above ajax method in or some other part in the part .If you want to call it from another file then that should be a JavaScript file with extension .js and need to be included in the of page. – Gurmeet Apr 04 '13 at 04:52
Just make sure you have jQuery JS included. – Zlatko Apr 05 '13 at 18:24

score 1 · Answer 4 · answered Apr 03 '13 at 07:48

If you're learning this stuff off of W3Schools, it may sound just a little little bit complicated, but don't be discouraged :)

Anyway, like @LoneWOLFs said in a comment, you could probably also set up another PHP page on the same server where your JavaScript comes from, and make that page call on the other remote server.

Basically, your new PHP script would do the similar as the old one - call some data from some remote location. And then it would pass that data back to the browser that called it.

Connect to PHP MySQL with Javascript from external server

4 Answers4