SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 31
  1. #1
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Run a database query in a JS file

    Is it possible to connect to a MySQL database from within a jQuery script, run a query, and return the results?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    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!
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much, I'll try to make it work ^^

  4. #4
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Output data as json or xml using the proper headers.
    The only code I hate more than my own is everyone else's.

  6. #6
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Output data as json or xml using the proper headers.
    Can you link to some examples or maybe make an example yourself?

  7. #7
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to make it work here. This is the php code for the connection to the database and the sql query:

    PHP Code:
    $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:

    Code:
    $(function() {
    	
    	$.getJSON('query.php', function(data) {
    		
    		$.each(data, function() {
    			alert(data.column);
    		})
    	
    	})
    })
    This doesn't alert anything.

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Try this --

    PHP Code:
    $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.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  9. #9
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Try this --

    PHP Code:
    $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?

  10. #10
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    {"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

  11. #11
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    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

    Code:
    $(function() {
    	
    	$.getJSON('query.php', function(data) {
    		
    		console.log(data);
    	
    	})
    })
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  12. #12
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's exactly how my script looks like right now And this one gives me an empty result.

  13. #13
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    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?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  14. #14
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 [ ].

  15. #15
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    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?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  16. #16
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    alert(data.column);
    seems to work now...

  17. #17
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    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

    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  18. #18
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    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:

    1. 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?

    2. 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

    Code:
    $(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).

  19. #19
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by D3V4 View Post
    1. 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?
    Because at that point in time you had the following code

    PHP 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 )

    Quote Originally Posted by D3V4 View Post
    2. 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
    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

    Code:
    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:

    1) You don't need that $.each block
    2) 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.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  20. #20
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1. Now I understand

    2. 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 Code:
    <?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...
    Last edited by ScallioXTX; Apr 17, 2011 at 14:22. Reason: keep private info private please

  21. #21
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Your live site doesn't work at the moment (mysql connection error). But, the problem is here:

    PHP Code:
    $associazioni['suggestions'][] = $row['nome']; 
    See, the autocomplete plugin is expecting an array, but you are creating an array with the key 'suggestions' whose value is another array.
    Instead of putting the in array in another array, you should ... (the rest of this sentence is left an exercise to the reader)
    Last edited by ScallioXTX; Apr 17, 2011 at 15:23.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  22. #22
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't have access to a computer at the moment, but maybe I added 2 extra square brackets?

  23. #23
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by D3V4 View Post
    I don't have access to a computer at the moment, but maybe I added 2 extra square brackets?
    No you have 15 characters too many
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  24. #24
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    No you have 15 characters too many
    Ehehe ok

    I don't understand this though: since I have to return the data in the following JSON format

    Code:
    {
     query:'Li',
     suggestions:['Liberia','Libyan Arab Jamahiriya','Liechtenstein','Lithuania']
    }
    How can I specify the key "suggestions" if I don't create an array with and index named suggestions? There are some information I'm missing here

  25. #25
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,906
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by D3V4 View Post
    How can I specify the key "suggestions" if I don't create an array with and index named suggestions? There are some information I'm missing here
    You're right, the 'suggestions' key should indeed be in there. I confused the different methods. Forget what I said, you PHP is fine

    It can't connect to MySQL though and that's why it won't work

    <br />
    <b>Warning</b>: mysqli_connect() [<a href='function.mysqli-connect'>function.mysqli-connect</a>]: (42000/1044): Access denied for user 'xxxxx'@'%' to database 'xxxxx' in <b>/home/xxxxx/hosting/xxxxxx/subdomains/xxxxx/xxxxx/xxxxxx.php</b> on line <b>3</b><br />
    Database Error: Access denied for user 'xxxx'@'%' to database 'xxxx'
    sensitive info x'd out by me
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •