Update Multiple Fields at once.

[bradjtrammell]

I am trying to create a script that allows for multiple fields to be updated in a mysql database at once. Here’s the script I wrote to update one at a time, however with over 50,000 records. It’s not efficent for me to go through and update them all one at a time, I’d like to update all of them at once and process the update using a form.

How can I do this?

Here is my current code:

[php]// function will hide entry from search results
if($_POST[‘action’] == ‘update’) {
$id = $_POST[‘id’];
$title = preg_replace("/&(?![#0-9a-z]+;)/i", “&”, $_POST[‘title’]);
$artist = preg_replace("/&(?![#0-9a-z]+;)/i", “&”, $_POST[‘artist’]);
if (mysqli_connect_errno()) { echo “Failed to connect to MySQL: " . mysqli_connect_error(); }
$query = “UPDATE songlist SET title=’”.$title.”’, artist=’".$artist."’ WHERE id=’".$id."’";
mysqli_query($con,$query);
// echo "Edited ".$title. " - ".$artist;
header(’/request.php’);
mysqli_close($con);
}
[/php]

[JimL]

With 50.000 records you’re better off importing them directly to mysql.

Either that or you could try to insert them with prepared statements which should work beautifully for this (the query remains active in the db, you just change the input data).

This should give you an idea
[php]<?php

$insertedRows = 0;
$lines = file_get_contents(‘dataForImport.txt’);

if ($stmt = mysqli_prepare($link, ‘UPDATE songlist SET title= ?, artist = ? WHERE id= ?’)) {
foreach ($lines as $line) {
mysqli_stmt_bind_param($stmt, “i”, $line->id);
mysqli_stmt_bind_param($stmt, “s”, $line->title);
mysqli_stmt_bind_param($stmt, “s”, $line->artist);
mysqli_stmt_execute($stmt);
$insertedRows += mysqli_stmt_affected_rows($stmt);
}
}
?>

Rows in file: <?= count($lines) ?>
Rows inserted: <?= $insertedRows ?>[/php]

ps: consider changing to object oriented programming, it is so much better to work with.

Oh, and your code is vulnerable to sql injection.

[bradjtrammell]

Can you provide a solution that makes my script(s) not vulnerable to SQL Injections?

[JimL]

the code I posted is not vulnerable. Use parameterized queries (binding) and you’re safe.