Is it possible to connect to a MySQL database from within a jQuery script, run a query, and return the results?
1 Like
Yes, if you are using AJAX to communicate with a PHP or ASP or [your server side scripting language here] script.
Using javascript only it’s not possible because javascript by itself cannot connect to MySQL.
Take a look at jQuery.ajax() – jQuery API
If you want the script to wait to get the results and return them, make sure you set async to false
However, it’s usually also possible if it’s set to true. Just takes some effort to change mindset of how you think about Javascript.
Asynchronous is better!
Thank you very much, I’ll try to make it work ^^
Sorry for the big up, but I’m having some problems here 
I’ve had a look at the documentation, but I haven’t really understood from the examples how the data is returned.
Let’s say that I have a php file named query.php, where I setup a database connection and I run a SELECT query. Now, how can I use the resulting rows inside my JS file?
Output data as json or xml using the proper headers.
Can you link to some examples or maybe make an example yourself?
I’m trying to make it work here. This is the php code for the connection to the database and the sql query:
$conn = mysqli_connect('localhost', 'root', 'root', 'db');
if(!$conn)
{
echo 'Database Error: ' . mysqli_connect_error() ;
exit;
}
$sql = "SELECT colum FROM table";
$result = mysqli_query($conn,$sql);
while ($row = mysqli_fetch_array($result))
{
$results[] = $row['column'];
}
And this is the jQuery code:
$(function() {
$.getJSON('query.php', function(data) {
$.each(data, function() {
alert(data.column);
})
})
})
This doesn’t alert anything.
Try this –
$conn = mysqli_connect('localhost', 'root', 'root', 'db');
if(!$conn)
{
echo 'Database Error: ' . mysqli_connect_error() ;
exit;
}
$results=array();
$sql = "SELECT colum FROM table";
$result = mysqli_query($conn,$sql);
while ($row = mysqli_fetch_array($result))
{
$results[] = $row['column'];
}
echo json_encode($results);
Also, data.column doesn’t exist in the javascript side of things. Try to alert (or even better, console.log if you have something like firebug) data first to see what you’re dealing with and take it from there.
If I alert data I get [object Object].
PS: how can I do console.log from Firebug? 
Ok, I’ve oversimplified everything to try to understand where I’m making a mistake. Now my PHP script return an array in JSON format, with one element:
{"column_name":"column_value"}
This is what I get when I echo the result from PHP. However, if I console.log data I get .
How can this be?
PS: I understood what you meant with console.log ^^ At first, I thought that I had to use that command in Firebug somehow 
I don’t know if you still have $.each in there but if you do you shouldn’t. Just start out with the very basic
$(function() {
$.getJSON('query.php', function(data) {
console.log(data);
})
})
That’s exactly how my script looks like right now And this one gives me an empty result.
That’s very odd!
Have you checked the exact response from query.php in firebug?
Do you have the page live somewhere by any chance?
Here’s a live page: test page
Credentials are: test test
By the way, I’ve realized that i was using an old query.php, now I use the correct one and Firebug returns null instead of .
The PHP doesn’t print anything (you can see that in firebug when you click the grey “+ GET <url>” line), so it’s no wonder JS doesn’t get anything. Somewhere something’s going wrong in your PHP. Are you sure you put echo (or print) in there?
Lol I uploaded the wrong version Now I’m echoing the results and I get an Object as a result. How can I extract the information I need from it?
Edit:
alert(data.column);
seems to work now…
Yup you can now use the array keys in php as object properties in javascript.
So if you have $arr=array('a'=>'b'); in javascript you get data.a and if you have $arr=array('a'=>array('b'=>'c')); in javascript you get data.a.b, etc
I have two questions:
-
why did you say, in a previous post, that “data.column doesn’t exist in the javascript side of things”. What did you mean, since now I did the same thing and it worked?
-
The goal of all this is to use it with an autocomplete plugin that I am using. This is the plugin: Ajax Autocomplete for jQuery
I would like to populate the lookup list with the data fetched from the database. And of course it doesn’t work
$(function() {
$lookup = '';
$.getJSON('query.php', function(data) {
$.each(data.nome, function() {
$lookup = $lookup + '\\'' + this + '\\', ';
}); // the data fetched from the db i used to create a list
var options, a;
jQuery(function(){
alert($lookup);
options = { lookup: [$lookup] }; // the list is used to populate the list of autosuggestions
a = $('input[name=nome]').autocomplete(options); // the list of autosuggestions is connected to the input field
});
})
})
As you can see on the test page (go to “Inserisci” in the main menu) in the alert, $lookup contains all the right values, but no autosuggestion appears when trying to fill in the first field of the page (nome).
Because at that point in time you had the following code
while ($row = mysqli_fetch_array($result))
{
$results[] = $row['column'];
}
and since that didn’t create a “column” key in the array it wouldn’t output that and javascript wouldn’t find. So it was more about the actual variable name than the general principle. The general principle works fine (as you know by now :))
First of all, you’ve wrapped jQuery(function(){ ... }) inside a $(function() {...}) block, which is really not needed. You can remove the inner jQuery(function(){ and }).
Second, the lookup option for the plugin expects an array of strings. Like the example says
lookup: ['January', 'February', 'March', 'April', 'May']
but what you’re providing it is a string that contains comma’s, and that’s not a string. I’m not going to tell you exactly what to do (“give a man a fish”, and all that), but I will tell you this:
- You don’t need that $.each block
- You don’t even need to create a variable or manipulate what you get back from your PHP in any way.
If you get stuck, just holler okay?
Also, why don’t you use the serviceUrl to let the plugin talk to your PHP directly?
The idea of the plugin is that you either provide the serviceUrl in the options so the plugin can fetch the results for itself, or you put the options in the HTML in the lookup property. You’re creating a mix, which is fine in itself will work once you get it fixed, but it’s not common practice.
-
Now I understand
-
Ahem… I completely forgot that the plugin could handle JSON data -_-’
Now that I am having a look at how it works, though, I am not sure that I understand how it works. I’ve modified my PHP file to make it return data as specified in the plugin’s how to page.
<?php
$conn = mysqli_connect('localhost', 'xxxx', 'xxxxx', 'xxxxxx');
if(!$conn)
{
echo 'Database Error: ' . mysqli_connect_error() ;
exit;
}
$query = $_GET['query'];
$associazioni=array();
$sql = "SELECT nome FROM appuntamenti WHERE nome LIKE '$query%'";
$result = mysqli_query($conn,$sql);
while ($row = mysqli_fetch_array($result))
{
$associazioni['suggestions'][] = $row['nome'];
}
echo json_encode($associazioni);
?>
I don’t know if this is the best way to do it but that’s the first solution that came up to my mind.
I have also modified the javascript code following your suggestions and commenting out the now useless lines.
Having a look at Firebug I see that the suggestions are correctly filtered when I enter text in the input field, but they don’t appear under it.
I sense that we are approaching a solution here xD
PS: thanks a lot for your help. This is still difficult stuff for me because everything is new and I’m trying to learn pretty much everything on my own, but it’s a long process apparently…