Send data from javascript to a mysql database

Question

I have this little click counter. I would like to include each click in a mysql table. Can anybody help?

var count1 = 0;
function countClicks1() {
count1 = count1 + 1;
document.getElementById("p1").innerHTML = count1;
}


document.write('<p>');
document.write('<a href="javascript:countClicks1();">Count</a>');
document.write('</p>');

document.write('<p id="p1">0</p>');

Just in case anybody wants to see what I did:

var count1 = 0;
function countClicks1() {
count1 = count1 + 1;
document.getElementById("p1").innerHTML = count1;
}
function doAjax()
$.ajax({
   type: "POST",
   url: "phpfile.php",
   data: "name=name&location=location",
    success: function(msg){
     alert( "Data Saved: " + msg );
   }
 });
}

document.write('</p>');
document.write('<a href="javascript:countClicks1(); doAjax();">Count</a>');
document.write('</p>');
document.write('<p id="p1">0</p>');

This is phpfile.php which for testing purposes writes the data to a txt file

<?php
$name = $_POST['name'];
$location = $_POST['location'];
$myFile = "test.txt";
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $name);
fwrite($fh, $location);
fclose($fh);
?>

score 20 · Accepted Answer · edited Jun 20 '20 at 09:12

JavaScript, as defined in your question, can't directly work with MySql. This is because it isn't running on the same computer.

JavaScript runs on the client side (in the browser), and databases usually exist on the server side. You'll probably need to use an intermediate server-side language (like PHP, Java, .Net, or a server-side JavaScript stack like Node.js) to do the query.

Here's a tutorial on how to write some code that would bind PHP, JavaScript, and MySql together, with code running both in the browser, and on a server:

http://www.w3schools.com/php/php_ajax_database.asp

And here's the code from that page. It doesn't exactly match your scenario (it does a query, and doesn't store data in the DB), but it might help you start to understand the types of interactions you'll need in order to make this work.

In particular, pay attention to these bits of code from that article.

Bits of Javascript:

xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();

Bits of PHP code:

mysql_select_db("ajax_demo", $con);
$result = mysql_query($sql);
// ...
$row = mysql_fetch_array($result)
mysql_close($con);

Also, after you get a handle on how this sort of code works, I suggest you use the jQuery JavaScript library to do your AJAX calls. It is much cleaner and easier to deal with than the built-in AJAX support, and you won't have to write browser-specific code, as jQuery has cross-browser support built in. Here's the page for the jQuery AJAX API documentation.

The code from the article

HTML/Javascript code:

<html>
<head>
<script type="text/javascript">
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
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("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select a person:</option>
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Glenn Quagmire</option>
<option value="4">Joseph Swanson</option>
</select>
</form>
<br />
<div id="txtHint"><b>Person info will be listed here.</b></div>

</body>
</html>

PHP code:

<?php
$q=$_GET["q"];

$con = mysql_connect('localhost', 'peter', 'abc123');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("ajax_demo", $con);

$sql="SELECT * FROM user WHERE id = '".$q."'";

$result = mysql_query($sql);

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

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

mysql_close($con);
?>

@victor - the person who edited the answer: I copy/pasted the code from the w3schools article. When you make edits to the code it is no longer the same code, and thus saying "the code from the article" makes no sense. I suggest you edit the source that I quoted from before editing it here, or add your own answer, or change the title of that section so it no longer says that it is from the article. — Merlyn Morgan-Graham, Jun 15 '19 at 01:32
Sorry to bother you, but I just wanted to make it better. Your answer has several upvotes and it is chosen as an accepted answer. This I why I thought that it is a bad idea to promote bad practices (such as dirty formatting or SQL injection and FPD vulnerabilities). By the way, I checked it on W3Schools: they have already formatted the code and fixed the SQL injection vulnerability (btw, FPD is still not fixed). — Victor, Jun 15 '19 at 08:41
@Victor No bother, I just didn't think the result was quite right. I think your code edits were (probably) fine. I didn't fully go over them. I just think you should also change the section so it is obvious you've improved it and not just copy/pasted it. Like instead of "The code from the article", rename it "Code Sample (adapted from the article linked above)". Or you can update it to match the source article (it looked liked it hadn't changed to me, at a brief glance, but now I see the changes they made). One of those two things, if you'd like to go ahead and make the change. — Merlyn Morgan-Graham, Jun 16 '19 at 02:53
I think this is the smallest problem, because the real problem is that beginner developers will copy-paste your code, considering it to be good. Anyway, thanks for the explanation. — Victor, Jun 16 '19 at 04:15

score 3 · Answer 2 · answered Dec 07 '11 at 08:52

You will have to submit this data to the server somehow. I'm assuming that you don't want to do a full page reload every time a user clicks a link, so you'll have to user XHR (AJAX). If you are not using jQuery (or some other JS library) you can read this tutorial on how to do the XHR request "by hand".

score 1 · Answer 3 · answered Dec 07 '11 at 09:09

The other posters are correct you cannot connect to MySQL directly from javascript. This is because JavaScript is at client side & mysql is server side.

So your best bet is to use ajax to call a handler as quoted above if you can let us know what language your project is in we can better help you ie php/java/.net

If you project is using php then the example from Merlyn is a good place to start, I would personally use jquery.ajax() to cut down you code and have a better chance of less cross browser issues.

http://api.jquery.com/jQuery.ajax/

Send data from javascript to a mysql database

3 Answers3

The code from the article

Linked