Best approach to add records into DB using php/ajax/mysql?

Question

Wondering if someone can suggest a better approach to the current method I have of integrating php/js/mysql.

I use this method just fine for returning sample data, setting a unique value, etc, and it works just fine (with various authentication methods to slow an attack attempt). However, I want to now add relatively large amount of data and wondering if there is a better method?

Currently I do this:

JS

$.ajax( {
    type : "GET",
    url : "_dbGetSomeData",
    dataType : "html",
    success: function(data) {
        parseData(data);
    }
});

PHP

<?php 
include '_common.php'; 
include '_auth.php';

$INPUT_EMAIL= mysql_real_escape_string($_GET["e"]);
$INPUT_FINGERPRINT = $_SESSION[SESH_VAR_NAME];

//--- Do some SQL stuff in the DB
?>

This works fine for a one-off request for data or setting one value, but can I use this method to save more data? I don't really want to start messing about with a huge querystring posts to the _db PHP file.

Use POST instead of GET, use PDO or mysqli and prepared statements instead of mysql and real_escape_string. — user1233508
– user1233508, Commented Aug 19, 2012 at 14:10
No, I'm wanting to just set/store a lot of data. I'll look into prepared statements. Seems like a better way to do things than many async ajax calls in a loop to a php page which saves data in the local db. Cheers chaps. — Jammo
– Jammo, Commented Aug 19, 2012 at 14:20
DCoder, ok, read up on those, they look great and far better way to prepare my db interaction. However, it doesn't really explain how I will eventually be sending the data from the JS/HTML page (example a table object with rows) to the PHP page (via an ajax call). I want to avoid, if possible, a huge query string sent to the PHP page to then be parsed — Jammo
– Jammo, Commented Aug 19, 2012 at 14:30
POST doesn't send a query string. If you want to set a lot of data, you have to send it all somehow, what alternative is there? — user1233508
– user1233508, Commented Aug 19, 2012 at 14:45

chrislondon · Accepted Answer · 2013-05-29 23:40:16Z

1

Here's an example of how you should do Ajax requests with jQuery/PHP/MySQL

JS

var postData = {
    first_name: 'Foo',
    last_name: 'Bar',
    phone: '1-555-432-1234'
};

$.post('path/to/ajax/url.php', postData)
 .done(function(response) {
    alert("Data Loaded: " + response);
 });

PHP

// Connect to MySQL
$dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

// We want do some validation on the data so we can make a function called
// `validate()` if you want.
$data = validate($_POST);

$stmt = $dbh->('INSERT INTO my_table (first_name, last_name, phone) 
    VALUES (:first_name, :last_name, :phone)');

$stmt->execute($data);

answered May 29, 2013 at 23:40

chrislondon

12.6k5 gold badges29 silver badges66 bronze badges

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

1 Comment

Jammo Over a year ago

Nice solution. Much tidier than my code. Many thanks ChrisLondon

Collectives™ on Stack Overflow

Best approach to add records into DB using php/ajax/mysql?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related