Fetch from MySQL with AJAX

Question

I'm building a web application that connects to a database which has got a couple of variables stored that's necessary for the site to function properly.

Right now I'm using php to connect and fetch the information but in the end it would be much easier to use a .html file, so I'm planning on using AJAX to fetch the information instead.

I've got this code:

$(function () 
  {
     $.ajax({                                      
  url: 'api.php',                  
  data: {
        paramName: $("#partyIDInput").val()
    },
  method: post,               
      dataType: 'json',        
      success: function(data)         
      {
        var id = data[0];       
        var pID = data[1];      
        var pLOC = data[2];      
        var pNAME = data[3]; 
        var pFORMID = data[5];     
        var pFORMSONG = data[6];      
        var pFORMARTIST = data[7];       
        var pFORMNAME = data[8];    

        $('#partyTitle').append(""+pNAME+"");
      } 
    });
  });

Which fetches all the variables via api.php which looks like this:

    <?php

$db = new PDO('mysql:host=xxxxx;dbname=xxxxx;charset=utf8', 'xxxxx', 'xxxxx');

$partyInput = $_POST['paramName'];

$stmt = $db->prepare("SELECT * FROM playr_partyID_db WHERE partyID = $partyInput");

$stmt->execute(array($_POST['paramName']));

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

?>

But my problem is that I've got a text input on the web page, when a user writes a "partyID" in it and clicks continue I want the script to fetch the information from the row with that ID. Now it's only fetching from the 1st row.

I've never worked with AJAX before so if you've got the time, please explain what's happening too.

Thanks in advance.

Edit:

Here's the input, note that I haven't optimized the page in terms of css yet.

<form style="margin-top:150px;" method="post" action="">
              <div class="list-block">
                <ul>
                  <li class="item-content" style="background: #fff;margin-bottom: -26px;">
                    <div class="item-inner">
                      <div class="item-input">
                        <input style="text-align:center;padding:0;" type="text" name="partyID" placeholder="Ange PartyID">
                      </div>
                    </div>
                  </li>
                </ul>
              </div>
              <div class="list-block">
                <ul>
                  <li>
                      <input type="submit" name="submit" value="Festa p&aring;!" class="login_bttn" style="  background: none;color: white;border: 1px solid white;border-radius: 500px;width: auto;margin-left: auto;margin-right: auto;margin-top: 40px;"></input>
                  </li>
                </ul>
                <div class="list-block-labe"></div>
              </div>
            </form>

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). Your not sending anything to the PHP script. — Jay Blanchard, May 14 '15 at 12:22
This isn't the complete page, just the parts that has to to with the db. I'll add the input, — Axel, May 14 '15 at 12:28

score 2 · Answer 1 · edited May 23 '17 at 10:28

You have to send something in the AJAX call -

$(function () 
  {
    $.ajax({                                      
      url: 'api.php',                  
      data: "", // needs to contain the data you want to send
      method: post, // you also need a method

Once you do that the variables you send will be available in the $_POST array of the PHP and you can include those variables in your SQL query. Here is a basic AJAX guide which should make things clearer for you.

In addition you should stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO, it's not as hard as you think.

score 2 · Accepted Answer · answered May 14 '15 at 12:30

2

First of all, to pass that extra parameter, update data: "", to

data: {
    paramName: $("#idOfField").val()
},

Secondly, as Jay Blanchard mentioned, switch to PDO

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

$stmt = $db->prepare("SELECT * FROM playr_partyID_db WHERE partyID =?");

$stmt->execute(array($_POST['paramName']));

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

answered May 14 '15 at 12:30

Alex Tartan

6,736
10
34
45

Ok, I've changed to PDO. – Axel May 14 '15 at 12:33
I'm sorry but I can't get it to work. I updated the code in the original post to what I've got now. – Axel May 14 '15 at 12:38
Is $rows empty? Is there any error? Can you paste a print_r($rows)? – Alex Tartan May 14 '15 at 12:41
It says that it can't find the variable. – Axel May 14 '15 at 12:47
right below $rows = $stmt->fetchAll(PDO::FERTCH_ASSOC); add a print_r($rows); – Alex Tartan May 14 '15 at 12:49
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77804/discussion-between-9focuspoints-and-alex-tartan). – Axel May 14 '15 at 12:50

Fetch from MySQL with AJAX

2 Answers2