Streamlining many PHP variables in MySQL update script

Question

I have 33 variables from passed from an Ajax call into my PHP MySQL update script, I want to streamline the code. Here is what I have now:

<?php
include 'dbconfig.php';

    $p1 = ($_POST['price-1']);
    $p2 = ($_POST['price-2']);
    $p3 = ($_POST['price-3']);
    $p4 = ($_POST['price-4']);
    $p5 = ($_POST['price-5']);
    $p6 = ($_POST['price-6']);
    $p7 = ($_POST['price-7']);
    $p8 = ($_POST['price-8']);
    $p9 = ($_POST['price-9']);
    $p10 = ($_POST['price-10']);
    $p11 = ($_POST['price-11']);
    $p12 = ($_POST['price-12']);
    $p13 = ($_POST['price-13']);
    $p14 = ($_POST['price-14']);
    $p15 = ($_POST['price-15']);
    $p16 = ($_POST['price-16']);
    $p17 = ($_POST['price-17']);
    $p18 = ($_POST['price-18']);
    $p19 = ($_POST['price-19']);
    $p20 = ($_POST['price-20']);
    $p21 = ($_POST['price-21']);
    $p22 = ($_POST['price-22']);
    $p23 = ($_POST['price-23']);
    $p24 = ($_POST['price-24']);
    $p25 = ($_POST['price-25']);
    $p26 = ($_POST['price-26']);
    $p27 = ($_POST['price-27']);
    $p28 = ($_POST['price-28']);
    $p29 = ($_POST['price-29']);
    $p30 = ($_POST['price-30']);
    $p31 = ($_POST['price-31']);
    $p32 = ($_POST['price-32']);
    $p33 = ($_POST['price-33']);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "UPDATE hubs SET price='" . $p1 . "' WHERE id='1';";
$sql.= "UPDATE hubs SET price='" . $p2 . "' WHERE id='2';";
$sql.= "UPDATE hubs SET price='" . $p3 . "' WHERE id='3';";
$sql.= "UPDATE hubs SET price='" . $p4 . "' WHERE id='4';";
$sql.= "UPDATE hubs SET price='" . $p5 . "' WHERE id='5';";
$sql.= "UPDATE hubs SET price='" . $p6 . "' WHERE id='6';";
$sql.= "UPDATE hubs SET price='" . $p7 . "' WHERE id='7';";
$sql.= "UPDATE hubs SET price='" . $p8 . "' WHERE id='8';";
$sql.= "UPDATE hubs SET price='" . $p9 . "' WHERE id='9';";
$sql.= "UPDATE hubs SET price='" . $p10 . "' WHERE id='10';";
$sql.= "UPDATE hubs SET price='" . $p11 . "' WHERE id='11';";
$sql.= "UPDATE hubs SET price='" . $p12 . "' WHERE id='12';";
$sql.= "UPDATE hubs SET price='" . $p13 . "' WHERE id='13';";
$sql.= "UPDATE hubs SET price='" . $p14 . "' WHERE id='14';";
$sql.= "UPDATE hubs SET price='" . $p15 . "' WHERE id='15';";
$sql.= "UPDATE hubs SET price='" . $p16 . "' WHERE id='16';";
$sql.= "UPDATE hubs SET price='" . $p17 . "' WHERE id='17';";
$sql.= "UPDATE hubs SET price='" . $p18 . "' WHERE id='18';";
$sql.= "UPDATE hubs SET price='" . $p19 . "' WHERE id='19';";
$sql.= "UPDATE hubs SET price='" . $p20 . "' WHERE id='20';";
$sql.= "UPDATE hubs SET price='" . $p21 . "' WHERE id='21';";
$sql.= "UPDATE hubs SET price='" . $p22 . "' WHERE id='22';";
$sql.= "UPDATE hubs SET price='" . $p23 . "' WHERE id='23';";
$sql.= "UPDATE hubs SET price='" . $p24 . "' WHERE id='24';";
$sql.= "UPDATE hubs SET price='" . $p25 . "' WHERE id='25';";
$sql.= "UPDATE hubs SET price='" . $p26 . "' WHERE id='26';";
$sql.= "UPDATE hubs SET price='" . $p27 . "' WHERE id='27';";
$sql.= "UPDATE hubs SET price='" . $p28 . "' WHERE id='28';";
$sql.= "UPDATE hubs SET price='" . $p29 . "' WHERE id='29';";
$sql.= "UPDATE hubs SET price='" . $p30 . "' WHERE id='30';";
$sql.= "UPDATE hubs SET price='" . $p31 . "' WHERE id='31';";
$sql.= "UPDATE hubs SET price='" . $p32 . "' WHERE id='32';";
$sql.= "UPDATE hubs SET price='" . $p33 . "' WHERE id='33';";


    if ($conn->multi_query($sql)  === TRUE) {
    echo "Pricing successfully updated";
        } else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

It seems very clunky and inefficient. How can I automatically grab and define prices-1 - prices-33 and update it accordingly without having to do it that way I did it? I want to use this script as a template in the future and save myself from typos.

Thanks.

It's actually quite efficient in terms of performance. But it's prone to [SQL injections](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Paul Spiegel, Feb 22 '20 at 12:42

score 2 · Accepted Answer · answered Feb 22 '20 at 12:29

2

First change your HTML input name attributes from

<input type="text" name="price-23">

to

<input type="text" name="prices[23]">

You will have all prices in a single array $_POST['prices']. You can now create a prepared statement and update all values in a foreach loop:

$stmt = $conn->prepare('UPDATE hubs SET price = ? WHERE id = ?');

$conn->begin_transaction();

foreach ($_POST['prices'] as $id => $price) {
    $stmt->bind_param('si', $price, $id);
    $stmt->execute();
}

$conn->commit();

answered Feb 22 '20 at 12:29

Paul Spiegel

30,925
5
44
53

For error handling please read [this post](https://stackoverflow.com/a/22662582/5563083). – Paul Spiegel Feb 22 '20 at 12:36
Perfect, but how can I report (echo) a successful update? – tony Feb 22 '20 at 12:58
This depends on the definition of a "successful update". If you meen "no errors occured" - Then you just print "Pricing successfully updated" . If something fails, then an exception should be throws (please read the post I linked above). In that case you should handle it globally and deliver a HTTP code 500 (internal server error). It is nothing the user can do about - so no need to tell what's wrong. Say "Sorry!" and fix it. – Paul Spiegel Feb 22 '20 at 13:11

Streamlining many PHP variables in MySQL update script

1 Answers1