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. 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. Your not sending anything to the PHP script. — Jay Blanchard
– Jay Blanchard, Commented May 14, 2015 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
– Axel, Commented May 14, 2015 at 12:28

Alex Tartan · Accepted Answer · 2015-05-14 12:30:44Z

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, 2015 at 12:30

Alex Tartan

6,84410 gold badges37 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Axel Over a year ago

Ok, I've changed to PDO.

Axel Over a year ago

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.

Alex Tartan Over a year ago

Is $rows empty? Is there any error? Can you paste a print_r($rows)?

Axel Over a year ago

It says that it can't find the variable.

Alex Tartan Over a year ago

right below $rows = $stmt->fetchAll(PDO::FERTCH_ASSOC); add a print_r($rows);

|

Community · Accepted Answer · 2017-05-23 10:28:13Z

2

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.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered May 14, 2015 at 12:28

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

Collectives™ on Stack Overflow

Fetch from MySQL with AJAX

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related