PHP with multiple queries

Question

I'm making a code where I can update "records" of students from my database. The problem is there were multiple queries and being new to php as a whole, I don't know how to do it.

Below is my code:

<html>
    <head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <title>PRACTICE 04</title>
    </head>
    <body>
    <?php
        $link = mysqli_connect('localhost', 'root', '','student');
        if (!$link) {
            die('Could not connect: ' . mysqli_error());
        }
        echo 'Connected successfully';

        $IDNoOld = $_POST["IDNoOld"];
        $IDNoNew = $_POST["IDNoNew"];
        $FirstName = $_POST["FNameNew"];
        $MiddleName = $_POST["MNameNew"];
        $LastName = $_POST["LNameNew"];
        $Grade = $_POST["GradeNew"];
        $Sec = $_POST["SecNew"];
        $Add = $_POST["AddNew"];
        $Age = $_POST["AgeNew"];
        $FSub = $_POST["FSubNew"];
        $LSub = $_POST["LSubNew"];


        $sql_query = "UPDATE student1 SET IDNo='$IDNoNew' WHERE IDNo = '$IDNoOld'"; "UPDATE student1 SET FirstName='$FirstName' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET FirstName='$FirstName' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET MiddleName='$MiddleName' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET LastName='$LastName' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET Grade='$Grade' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET Section='$Sec' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET Address='$Add' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET Age='$Age' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET FavoriteSubject='$FSub' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;
        
        $sql_query = "UPDATE student1 SET LeastFavoriteSubject='$LSub' WHERE IDNo = '$IDNoOld'";
        echo $sql_query;

        if (mysqli_query($link, $sql_query))
        {
            echo "Data successfully updated!";
        }


        mysqli_close($link);
    ?>
    </body>
</html>

A little help would absolutely help me, thanks in advance.

you can update multiple columns in 1 query, just use ",". SET IDNo='%s', FirstName='%s' — Ainz, Jul 08 '22 at 06:25
Does this answer your question? [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Ainz, Jul 08 '22 at 06:26
The use of variables within the SQL directly is **not** the way to do it - you have exposed your code to SQL injection by doing so — Professor Abronsius, Jul 08 '22 at 06:27
You also cannot have two sql queries in the same `$sql_query` string using regular `mysqli_query` — Professor Abronsius, Jul 08 '22 at 06:30
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Jul 08 '22 at 08:37

score 1 · Answer 1 · answered Jul 08 '22 at 06:48

Rather than many queries that update a single column you can, as mentioned, update multiple columns by specifying them as name=value pairs in the query separated by a comma. You would not however wish to embed the POST variables directly in the SQL as your database could very quickly become violated. Instead you should always use Prepared statements when dealing with user supplied data - even when you think you can trust the user.

You could try something like this (untested)

<?php

    error_reporting( E_ALL );

    # Object Orientated syntax is less verbose than procedural!
    
    mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
    $link = new mysqli('localhost', 'root', '','student');
    
    # only proceed if ALL post variables are present
    if( $_SERVER['REQUEST_METHOD']=='POST' && isset(
        $_POST['IDNoNew'],
        $_POST['FNameNew'],
        $_POST['MNameNew'],
        $_POST['LNameNew'],
        $_POST['GradeNew'],
        $_POST['SecNew'],
        $_POST['AddNew'],
        $_POST['AgeNew'],
        $_POST['FSubNew'],
        $_POST['LSubNew'],
        $_POST['IDNoOld']
    )){
        # construct the basic update command with placeholders for variable substitution
        $sql='update `student1` set `IDNo`=?, `FirstName`=?, `MiddleName`=?, `LastName`=?, `Grade`=?, `Section`=?, `Address`=?, `Age`=?, `FavoriteSubject`=?, `LeastFavoriteSubject`=? where `IDNo`=?';
        
        # the sequence here matches the placeholders!
        $args=array( 
            $_POST['IDNoNew'],
            $_POST['FNameNew'],
            $_POST['MNameNew'],
            $_POST['LNameNew'],
            $_POST['GradeNew'],
            $_POST['SecNew'],
            $_POST['AddNew'],
            $_POST['AgeNew'],
            $_POST['FSubNew'],
            $_POST['LSubNew'],
            $_POST['IDNoOld']
        );
        
        # create a string of variable types for use in the statement. String is OK here for all.
        $types=str_repeat('s',count( $args ) );

        # create the prepared statement, bind the params and execute.
        $stmt=$link->prepare( $sql );
        $stmt->bind_params( $types, ...$args );
        $stmt->execute();           
    }
?>

PHP with multiple queries

1 Answers1