How to create two update statements in one query?

Question

I am making a form where you can insert, update and delete your data in database welkom from phpmyadmin. This is my file Formmenu.php. PS: This code contains Dutch language.

PHP

<?php
    if ( ! empty($_POST))
     {
            $mysqli = new mysqli('localhost','root','','welkom');

            if ($mysqli ->connect_error) 
            {
                die('connect error: '. $mysqli->connect_errno . ': ' . $mysqli->connect_error);
            }

                if (isset($_POST['insert'])) {
                     $sql = "INSERT INTO abdijbieren (naam, prijs) VALUES ('{$mysqli->real_escape_string($_POST['naam'])}','{$mysqli->real_escape_string($_POST['prijs'])}');";

                } else if (isset($_POST['delete'])) {
                    $sql = "DELETE FROM abdijbieren WHERE naam ='{$mysqli->real_escape_string($_POST['naam'])}';";
                }
                  else if (isset($_POST['update'])) {
                    $sql = "UPDATE abdijbieren SET id='{$mysqli->real_escape_string($_POST['id'])}' WHERE naam='{$mysqli->real_escape_string($_POST['naam'])}'; UPDATE abdijbieren SET prijs='{$mysqli->real_escape_string($_POST['prijs'])}' WHERE naam='{$mysqli->real_escape_string($_POST['naam'])}';";
                }

                  else {
                      /*nothing*/
                  }



           $insert = $mysqli->query($sql);

            if ($insert)
            {
                echo "Success! Keer terug naar de volgende pagina om te updaten.";
            }else
            {
                die("Error: {$mysqli->errno} : {$mysqli->error}");
            }



            $mysqli->close();
        }

    ?>

Form

<form method="post" action="">
              <input name="naam" type="text" placeholder="naam drank" required><br>
              <input name="prijs" type="text" placeholder="prijs" required><br>
              <input name="id" type="text" placeholder="tracking number*"><br><br>

        <input type="submit" name="insert" value="insert">
        <input type="submit" name="delete" value="delete">
        <input type="submit" name="update" value="update"><br>
        *= niet nodig bij "insert".
        </form>

This is what I am focusing on:

$sql = "UPDATE abdijbieren SET id='{$mysqli->real_escape_string($_POST['id'])}' WHERE naam='{$mysqli->real_escape_string($_POST['naam'])}'; UPDATE abdijbieren SET prijs='{$mysqli->real_escape_string($_POST['prijs'])}' WHERE naam='{$mysqli->real_escape_string($_POST['naam'])}';";

When I tried the code I recieved error #1064 after submitting the form. The error echoes;

Error: 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE abdijbieren SET prijs='5.00 EUR' WHERE id='9'; UPDATE a' at line 2

I wasn't sure if it is a typo or the real_escape_string. I tried looking for a solution with this:

The issue at the second link remained unsolved. I checked my code and it seemed fine, but after recieving error #1064 I am confused with the code. I need help with this issue.

Thank you.

Use `multi_query` if you have to do it in one execution, but you can also update both fields in one query.. — JustOnUnderMillions, Mar 16 '17 at 10:57

Mihai Matei · Answer 1 · 2017-03-16T11:44:34.400

2

You can update multiple columns in a single statement:

$sql = "UPDATE abdijbieren 
           SET id = '" . $mysqli->real_escape_string($_POST['id']) . "',
               prijs = '" . $mysqli->real_escape_string($_POST['prijs']) . "' 
         WHERE naam='" . $mysqli->real_escape_string($_POST['naam']) . "'"

edited Mar 16 '17 at 11:44

answered Mar 16 '17 at 10:57

Mihai Matei

24,166
5
32
50

U know its wrong to directly inject `$_POST` values in the query – Masivuye Cokile Mar 16 '17 at 11:09
@MasivuyeCokile They are not directly injected in the query. It is safe as long as `real_escape_string` method is used.. I just helped him to understand how the query must be done.. SQL injection was not part of this question.. – Mihai Matei Mar 16 '17 at 11:20
well alryt then but u may just give few suggestions, so that he can know what he's doing aint safe – Masivuye Cokile Mar 16 '17 at 11:32
I just told you that it is safe as long as he used `$mysqli->real_escape_string()` method.. it is not safe to use the variables directly which is the case when you must use bindings – Mihai Matei Mar 16 '17 at 11:43
will have to disagree with u on that one – Masivuye Cokile Mar 16 '17 at 11:50

score 0 · Accepted Answer · answered Mar 16 '17 at 10:58

0

Try this :

 $sql = "UPDATE abdijbieren SET id='{$mysqli->real_escape_string($_POST['id'])}' , prijs='{$mysqli->real_escape_string($_POST['prijs'])}' WHERE naam='{$mysqli->real_escape_string($_POST['naam'])}'";

answered Mar 16 '17 at 10:58

Sweta Parmar

269
1
11

score -2 · Answer 3 · edited Mar 16 '17 at 22:30

-2

There is no need to execute the update query for each column; you can achieve this by using a query like that:

'update table_name set col_1="col_1_value", col_2 ="col_2_value" where your_condition = abc';

edited Mar 16 '17 at 22:30

Neil

14,063
3
30
51

answered Mar 16 '17 at 11:06

Ankit Singh

1,477
1
13
22

How to create two update statements in one query?

3 Answers3