Inserting into MySQL from PHP (jQuery/AJAX)

Question 1

I have seen many tutorials, but they're so confusing, and to do what I want to do, I just don't get how to use existing stuff from those tutorials and make them work they way I want them to.

I have a very simple form, containing a textbox, label and a submit button. When the user enters something into the form, then clicks submit, I would like to use php and ajax (with jquery) to insert the result of the form into a mysql database.

Can someone please show me how this can be achieved? Just something very basic is all i'm after to get me started. Any help is appreciated.

Thank you

Question 2

Hi here is just a quick example of how one might do it:

The HTML:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>
        <title>Quick JQuery Ajax Request</title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

        <!-- include the jquery lib -->
        <script type="text/javascript" src="jquery.js"></script>
        <script type="text/javascript">
            var ajaxSubmit = function(formEl) {
                // fetch where we want to submit the form to
                var url = $(formEl).attr('action');

                // fetch the data for the form
                var data = $(formEl).serializeArray();

                // setup the ajax request
                $.ajax({
                    url: url,
                    data: data,
                    dataType: 'json',
                    success: function() {
                        if(rsp.success) {
                            alert('form has been posted successfully');
                        }
                    }
                });

                // return false so the form does not actually
                // submit to the page
                return false;
            }
        </script>

    </head>
    <body>

        <form method="post" action="process.php"
              onSubmit="return ajaxSubmit(this);">
            Value: <input type="text" name="my_value" />
            <input type="submit" name="form_submit" value="Go" />
        </form>

    </body>
</html>

The process.php script:

<?php

function post($key) {
    if (isset($_POST[$key]))
        return $_POST[$key];
    return false;
}

// setup the database connect
$cxn = mysql_connect('localhost', 'username_goes_here', 'password_goes_here');
if (!$cxn)
    exit;
mysql_select_db('your_database_name', $cxn);

// check if we can get hold of the form field
if (!post('my_value'))
    exit;

// let make sure we escape the data
$val = mysql_real_escape_string(post('my_value'), $cxn);

// lets setup our insert query
$sql = sprintf("INSERT INTO %s (column_name_goes_here) VALUES '%s';",
                'table_name_goes_here',
                $val
);

// lets run our query
$result = mysql_query($sql, $cxn);

// setup our response "object"
$resp = new stdClass();
$resp->success = false;
if($result) {
    $resp->success = true;
}

print json_encode($resp);
?>

Please note that none of this has been tested. I hope it helps you thou.

Question 3

Use the ON DUPLICATE KEY UPDATE feature. It won't insert, if the primary key exists. But you have to update some value, so use the column which is in no index or in the least indexes () in your case probably result). Your primary key has to be composted out of the three FKs:

ALTER TABLE `predictions` ADD PRIMARY KEY( `userFK`, `matchFK`, `tournamentFK`);

PHP-Code, just the SQL statment (I'm a Java Guy, so i tried my best)

$sql.="INSERT INTO predictions (result, userFK, matchFK, tournamentFK) "
."VALUES('".$predictedMatch['result'] ."','".$userid."','"
.$predictedMatch['id']."','".$predictedMatch['tourid']."') "
."ON DUPLICATE KEY UPDATE result = result ;";

To know if the query was inserted you have to look at the affected row count:

1 Row - Insert
2 Rows - Update

Take a look at $conn->affected_rows after the query.

Performance

INSERT ... ON DUPLICATE KEY UPDATE is definitively faster than a SELECT and INSERT but it's slower than an INSERT of just the needed datasets. The update is done in the database, even if it is the same value. Unfortunately there is no ~~ON DUPLICATE KEY UPDATE INGNORE~~. If you have a lot of inserts, that will result in updates, than it may be better to use a cache, lookup values in an array and compare with the array before inserting. Only use the ON DUPLICATE KEY UPDATE as fallback.

Question 4

I would do this inside the loop where you fetch rows from DB2.

Assume you have a PDO connection $pdo to your MySQL database.

$stmt = $pdo->prepare("
    INSERT INTO skuplacement (sku_id, groupID, dealerID, startDate, expirationDate, placements)
    SELECT id, groupID, :DEALER, :SHIPDATE, :PLACEMENTS
    FROM sku
    WHERE groupID=:STYLE AND frame=:FRAME AND cover=:COVER AND color=:COLOR
");

$pdo->beginTransaction();
$i = 0;
while($db2row = odbc_fetch_array($result)) {

    if(++$i % 1000 == 0) {
        $pdo->commit();
        $pdo->beginTransaction();
    }

    $stmt->execute($db2row);

}
$pdo->commit();

PDO allows you to pass an associative array to execute(), and the array keys are matched to the named parameter placeholders in the prepared query. But your associative array must have exactly the same set of keys as the parameters.

So you will need to change your DB2 query to return only STYLE, DEALER, FRAME, COVER, COLOR, SHIPDATE, PLACEMENTS.

Question 5

This sounds like an urban legend to me.

bin2hex() maps each byte in the input to two bytes in the output ('a' -> '61'), so you should notice a significant memory increase of the script performing the query - it should use at least as much memory more as the byte length of the binary data to be inserted.

Furthermore, this implies that running bin2hex() on a long string takes much longer than running mysql_real_escape string(), which - as explained in MySQL's documentation - just escapes 6 characters: NULL, r, n, , , and 'Control-Z'.

That was for the PHP part, now for MySQL: The server needs to do the reverse operation to store the data correctly. Reversing either of the functions takes almost as long as the original operation - the reverse function of mysql_real_escape_string() needs to replace escaped values (\) with unescaped ones (), whereas the reverse of bin2hex() would need to replace each and every byte tuple with a new byte.

Since calling mysql_real_escape_string() on binary data is safe (according to MySQL's and PHP's documentation or even when just considering that the operation does not do any other conversions than the ones listed above), it would make absolutely no sense to perform such a costly operation.

Question 6

The problem is because of scoping of those variables. When you are trying to echo those variables outside the while loop; PHP can not find the varables as they were created (and assigned) inside the loop. To solve this, just assign a blank value to both variables outside too:

if(!$r) echo mysql_error();
$rat = 0;
$v = 1;    // In case there are no records.
while($row=mysql_fetch_array($r))
{
    $v = $row['total_votes'];
    $tv = $row['total_value'];
    $rat = $tv/$v;
}

Inserting into MySQL from PHP (jQuery/AJAX)

Answers

Performance

php,jquery,mysql,ajax