I'm trying to figure out how do I send a javascript array of x,y coordinates to a Mysql database? I don't have any forms or anything.
I'm very new the mysql so I basically know how to connect using this:
$connection = mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);
but I don't know how to connect that to the javascript. I know it has something to do with this code
jQuery.post(url, data);
Any help would be really appreciated!
I've put together this little one-page demo. Having a look at it might help you understand how it works.
To make it work, you need to put it on a server (no way!), and to have a table with x and y fields. Then, replace database credentials with your own.
References
Live demo
Note
This one does not check for types (you can input any String), but you can add that.
test.php
<?php
// Replace these parameters to match your database
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'mydatabase';
$table = 'coordinatesTable';
// Connect
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
// If parameters are received
if(isset($_POST['x']) && isset($_POST['y'])){
$error_messages = array();
if ($mysqli->connect_errno) {
$error_messages[] = "Couldn't connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
// Prepare insert
if (!($stmt = $mysqli->prepare("INSERT INTO `$table` (x,y) VALUES (?,?)"))) {
$error_messages[] = "Couldn't prepare statement: (" . $mysqli->errno . ") " . $mysqli->error;
}
// Bind parameters x and y
if (!$stmt->bind_param("dd", $_POST['x'], $_POST['y'])) {
$error_messages[] = "Couldn't bind parameters: (" . $stmt->errno . ") " . $stmt->error;
}
// Execute the insert
if (!$stmt->execute()) {
$error_messages[] = "Couldn't execute the query: (" . $stmt->errno . ") " . $stmt->error;
}
// Prepare some data to return to the client (browser)
$result = array(
'success' => count($error_messages) == 0,
'messages' => $error_messages
);
$stmt->close();
// Send it
echo json_encode($result);
// Exit (do not execute the code below this line)
exit();
} // end if
// Fetch all the coordinates to display them in the page
$res = $mysqli->query("SELECT x,y FROM `$table`");
$rows = array();
while ($row = $res->fetch_assoc()) {
$rows[] = $row;
}
$mysqli->close();
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Test page</title>
<style>
table{ border-collapse: collapse; }
th,td{ border: 1px solid #888; padding: .3em 2em; }
</style>
</head>
<body>
<h1>New coordinates</h1>
<p>
x: <input type="text" id="x">
y: <input type="text" id="y">
<button id="addBtn">Add</button>
</p>
<table id="coordinates">
<tr>
<th>x</th>
<th>y</th>
</tr>
<?php
if(count($rows)){
foreach($rows as $row){
?>
<tr>
<td><?=$row['x']?></td>
<td><?=$row['y']?></td>
</tr>
<?php
}
}
?>
</table>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script>
$(function(){
$('#addBtn').click(postCoordinates);
function postCoordinates(){
var x = $('#x').val(),
y = $('#y').val();
if(x.length && y.length){
// Change this if it is on a different page
var url = window.location.href;
$.ajax({
'url': url,
'method': 'post',
'data': {'x': x, 'y': y},
'dataType': 'json', // The server will return json
'success': function(res){
if(res.hasOwnProperty('success') && res.success){
$('#x, #y').val(''); // Empty the inputs
$('#coordinates').append('<tr><td>'+x+'</td><td>'+y+'</td></tr>');
} else if(res.hasOwnProperty('messages')) {
alert( res.messages.join('\n') );
}
},
'error': function(x, s, e){
alert( 'Error\n' + s + '\n' + e );
}
});
} else {
alert('You need to provide x and y coordinates');
}
}
});
</script>
</body>
</html>
Search for ajax. Send the data from client side to server side:
For example:
$('selector').on('click', function(){
var coordinates = [x,y];
$.ajax({
method: 'POST',
url: 'yourURL',
data: {coordinates: coordinates},
success: function(response){
Console.log(response);
}
});
});
In PHP:
$coor = $_POST['coordinates'];
echo $coor;
//Search the manual of php for storing it in the database
NOTE: Don't use mysql because it is deprecated, use mysqli or PDO.
mysql, which is deprecated since PHP 5.5.0 and was removed in PHP 7.0.0.