-1

I have started to build a basic system to contain my client data, I can add and delete rows no problem. I have now added an extra form to update information. I cannot get it to work and replace data, it simply adds a new row of data. I am new with PHP and mySQL. I have used MongoDB for much longer. The PHP code is as follows:

<?php 

$thisId = $_GET['id'];


    $thisClient = "SELECT * FROM clients WHERE id='".$thisId."'";
    $results = mysqli_query($link, $thisClient);

    if (mysqli_num_rows($results)){
        while ($row = mysqli_fetch_array($results)) {
            $client_name = $row['client_name'];
            $client_email = $row['client_email'];
            $client_number = $row['client_number'];
            $business_name = $row['business_name'];
            $business_email = $row['business_email'];
            $business_number = $row['business_number'];
            $address_1 = $row['address_1'];
            $address_2 = $row['address_2'];
            $address_town = $row['address_town'];
            $address_county = $row['address_county'];
            $address_code = $row['address_code'];
            $business_url = $row['business_url'];
            $colours = $row['colours'];
            $notes = $row['notes']; 
        }
    }

    if ($_SERVER["REQUEST_METHOD"]=="POST"){
    include("config.php");

    $client_name     = $_POST['client_name'];
    $client_email    = $_POST['client_email'];
    $client_number   = $_POST['client_number'];
    $business_name   = $_POST['business_name'];
    $business_email  = $_POST['business_email'];
    $business_number = $_POST['business_number'];
    $address_1       = $_POST['address_1'];
    $address_2       = $_POST['address_2'];
    $address_town    = $_POST['address_town'];
    $address_county  = $_POST['address_county'];
    $address_code    = $_POST['address_code'];
    $business_url    = $_POST['business_url'];
    $colours         = $_POST['colours'];
    $notes           = $_POST['notes'];

    $sql = "UPDATE INTO clients SET (client_name, client_email, 
                        client_number, business_name, business_email, 
                        business_number, address_1, address_2, 
                        address_town, address_county, address_code, 
                        business_url, colours, notes) 
            VALUES ('$client_name', '$client_email', 
                    '$client_number', '$business_name', '$business_email', 
                    '$business_number', '$address_1', '$address_2', 
                    '$address_town', '$address_county', '$address_code', 
                    '$business_url', '$colours', '$notes') 
            WHERE id='".$thisId."'";

        if (mysqli_query($link, $sql)) {
              header('Location:/Client');
        } else {
              echo "Error: " . $sql . "<br>" . mysqli_error($link);
        }
        mysqli_close($link);
}

?>
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
RevolutionaryOne
  • 1
  • 3
  • 6
    Please read about **[SQL injection](https://en.wikipedia.org/wiki/SQL_injection)**. Instead of building queries with string concatenation, use **[prepared statements](https://secure.php.net/manual/en/pdo.prepare.php)** with **[bound parameters](https://secure.php.net/manual/en/pdostatement.bindparam.php)**. See **[this page](https://phptherightway.com/#databases)** and **[this post](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)** for some good examples. – John Conde May 13 '19 at 13:03
  • Please do this before taking another step ^^^^^^^^^^ – Strawberry May 13 '19 at 13:06
  • I am doubtful is there any `Update into` query, it looks like `insert into` :| – Rahul May 13 '19 at 13:07
  • The syntax is just `UPDATE clients`, not `UPDATE INTO`. INTO is used for INSERT – aynber May 13 '19 at 13:07
  • Then check the [SYNTAX for a SQL UPDATE](https://dev.mysql.com/doc/refman/8.0/en/update.html). Hint its not the same as a INSERT! _And I very much doubt this add new rows :)_ – RiggsFolly May 13 '19 at 13:07
  • Your `"UPDATE"` query is just an `INSERT` query with the word `INSERT` replaced with `UPDATE`, which is never going to work. I suggest you read the [manual on `UPDATE`](https://dev.mysql.com/doc/refman/8.0/en/update.html) – Nick May 13 '19 at 13:07
  • Correct way to update values in MySQL is `UPDATE table_name SET field = new_value, field2 = new_value, ... WHERE condition` – Maxime Launois May 13 '19 at 13:08
  • The system is going to be kept offline, I will be tightening up the security later anyway. Just want to box off the functionality for now. – RevolutionaryOne May 13 '19 at 13:08
  • 2
    Just a note, prepared statements/parameter binding is not just for security. It prevents quoting issues, which eliminates a whole host of headaches. – aynber May 13 '19 at 13:09
  • 2
    Plus, no one ever does it 'later' - only when it's too late. – Strawberry May 13 '19 at 13:11

1 Answers1

1

Your query should be,

$sql = "UPDATE clients SET 
            client_name = ?, client_email = ?, client_number = ?, 
            business_name = ?, business_email = ?, business_number = ?, 
            address_1 = ?, address_2 = ?, address_town = ?, 
            address_county = ?, address_code = ?, business_url = ?, 
            colours = ?, notes = ? 
        WHERE id=?";

$statement = $link->prepare($sql);
//s here means string and i indicates integer
$statement->bind_param("ssssssssssssssi",  $client_name, $client_email, 
                        $client_number, $business_name, $business_email, 
                        $business_number, $address_1, $address_2, 
                        $address_town, $address_county, $address_code, 
                        $business_url, $colours, $notes, 
                        $_GET['id']);
$statement->execute();

Please go through documentation from here.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Rahul
  • 18,271
  • 7
  • 41
  • 60