Running multiple queries in php

Question

I'm really new in PHP and HTML. After pressing the submit button I'm trying to populate the fields with the data that is already in the Users MySQL table (this works). I also want to insert that same data obtained with the SELECT into another SQL table called scan.

<?php
// php code to search data in mysql database and set it in input text
if(isset($_POST['search']))
{


    // id to search
    $user_id = $_POST['user_id'];

    // connect to mysql
    $connect = mysqli_connect("127.0.0.1", "root", "root","demodb");

    // mysql search query


    $query = "SELECT * FROM Users WHERE user_id = $user_id LIMIT 1";
    $query = "INSERT INTO scan (user_id, osha, firstname, lastname, company, trade, email, picture) SELECT user_id, osha, firstname, lastname, company, trade, email, picture FROM Users WHERE user_id = $user_id LIMIT 1";



    $result = mysqli_query($connect, $query);



    // if id exist 
    // show data in inputsi
    if(mysqli_num_rows($result) > 0)
    {
      while ($row = mysqli_fetch_array($result))
      {
        $osha = $row['osha'];
        $firstname = $row['firstname'];
        $lastname = $row['lastname'];
        $company = $row['company'];
        $trade = $row['trade'];
      }  
    }

    // if the id not exist
    // show a message and clear inputs
    else {
        echo "Undifined ID";

            $osha = "";
            $firstname = "";
            $lastname = "";
            $company = "";
            $trade = "";
    }


    mysqli_free_result($result);

    mysqli_close($connect);
}    


// in the first time inputs are empty
else{
            $osha = "";
            $firstname = "";
            $lastname = "";
            $company = "";
            $trade = "";
}


?>

<!DOCTYPE html>

<html>

    <head>

        <title> PHP FIND DATA </title>

        <meta charset="UTF-8">

        <meta name="viewport" content="width=device-width, initial-scale=1.0">

    </head>

    <body>

    <form action="barcode.php" method="post">

    Id:<input type="text" name="user_id"><br><br>

    Osha #:<input type="text" name="osha" value="<?php echo $osha;?>"><br><br>

        First Name:<input type="text" name="firstname" value="<?php echo $firstname;?>"><br>
<br>

        Last Name:<input type="text" name="lastname" value="<?php echo $lastname;?>"><br><br>

    Company:<input type="text" name="company" value="<?php echo $company;?>"><br><br>

    Trade:<input type="text" name="trade" value="<?php echo $trade;?>"><br><br>

    <input type="submit" name="search" value="Find">

           </form>

    </body>

</html>

But it seems that I can only run one query at the time in PHP. I tried integrating mysqli_multi_query but I kept getting the following error "mysqli_num_rows() expects parameter 1 to be mysqli_result".

How can I run both queries and at the same time populate the fields with the data.

ADDING Tables definitions

Users Table

| Users | CREATE TABLE `Users` (
  `user_id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `osha` int(50) DEFAULT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `company` varchar(50) DEFAULT NULL,
  `trade` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `picture` varchar(50) DEFAULT NULL,
  `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=98819 DEFAULT CHARSET=latin1 |

scan Table

| scan  | CREATE TABLE `scan` (
  `user_id` int(6) unsigned NOT NULL DEFAULT '0',
  `osha` int(50) DEFAULT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `company` varchar(50) DEFAULT NULL,
  `trade` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `picture` varchar(50) DEFAULT NULL,
  `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

have you read the docs for [`mysqli_multi_query()`](http://php.net/manual/en/mysqli.multi-query.php)? (1) your querys need to end with `;`, and (2) you need to concatenate your queries -> `$query .=`, as you are currently overwriting the 1st query with the second. — Sean, May 23 '18 at 02:35
Some people find [this](https://stackoverflow.com/a/22469722/2943403) enlightening — mickmackusa, May 23 '18 at 03:33
I'm trying to imagine a slick single query with `INSERT INTO ON DUPLICATE KEY UPDATE` AND `UNION` then check the number of affected rows... _I believe the results could be 0, 1, or 2_. ...something akin to [this](https://stackoverflow.com/a/29787059/2943403). — mickmackusa, May 23 '18 at 03:41

Geoffrey · Answer 1 · 2018-05-23T02:53:49.087

You are overwriting the variable $query with a new value instead of performing the query first. That said though your code has numerous problems:

You are not escaping $_POST['user_id'], please read up on SQL injection attacks.
You are not escaping your HTML, please read up on XSS attacks.
Your logic flow duplicates code
You are adding additional load on the database by fetching the data twice, only fetch once, and insert once.

See the rewritten code below.

<?php
// initalize the variables 
$osha      = "";
$firstname = "";
$lastname  = "";
$company   = "";
$trade     = "";

// php code to search data in mysql database and set it in input text
if(isset($_POST['search']))
{
    // connect to mysql
    $dbc = mysqli_connect("127.0.0.1", "root", "root","demodb");

    // id to search
    $user_id = mysqli_real_escape_string($dbc, $_POST['user_id']);

    $query = "SELECT * FROM Users WHERE user_id = '$user_id' LIMIT 1";
    $rs    = mysqli_query($dbc, $query);
    if (mysqli_num_rows($rs) == 1)
    {
      $row       = mysqli_fetch_array($rs);
      $osha      = $row['osha'];
      $firstname = $row['firstname'];
      $lastname  = $row['lastname'];
      $company   = $row['company'];
      $trade     = $row['trade'];

      $query     = "INSERT INTO scan (user_id, osha, firstname, lastname, company, trade, email, picture) VALUES (" .
        "'" . $user_id . "', '" .
        "'" . mysqli_real_escape_string($dbc, $osha     ) . "', '" .
        "'" . mysqli_real_escape_string($dbc, $firstname) . "', '" .
        "'" . mysqli_real_escape_string($dbc, $lastname ) . "', '" .
        "'" . mysqli_real_escape_string($dbc, $company  ) . "', '" .
        "'" . mysqli_real_escape_string($dbc, $trade    ) . "')";
      mysqli_query($dbc, $query);
    }
    else
    {
      echo "Undefined ID";
    }
}    
?>

<!DOCTYPE html>

<html>

    <head>

        <title> PHP FIND DATA </title>

        <meta charset="UTF-8">

        <meta name="viewport" content="width=device-width, initial-scale=1.0">

    </head>

    <body>

    <form action="barcode.php" method="post">

    Id:<input type="text" name="user_id"><br><br>

    Osha #:<input type="text" name="osha" value="<?= htmlspecialchars($osha) ?>"><br><br>

        First Name:<input type="text" name="firstname" value="<?= htmlspecialchars($firstname) ?>"><br>
<br>

        Last Name:<input type="text" name="lastname" value="<?= htmlspecialchars($lastname) ?>"><br><br>

    Company:<input type="text" name="company" value="<?= htmlspecialchars($company) ?>"><br><br>

    Trade:<input type="text" name="trade" value="<?= htmlspecialchars($trade) ?>"><br><br>

    <input type="submit" name="search" value="Find">

           </form>

    </body>

</html>

Why does this have a down vote for a perfectly acceptable, concise and correct answer? — Geoffrey, May 23 '18 at 02:47
I wonder... probably some PDO fanboy not happy that PDO wasn't used here — Mouradif, May 23 '18 at 07:49
Thanks for the help! However, I was trying your code above and it seems that the second query doesn't INSERT anything on the table scan. any idea? — Unxcellent, May 23 '18 at 11:37
Hey guys, any idea how i can make the second work with the above code? — Unxcellent, May 24 '18 at 21:03
@Unxcellent Please show your table definitions (`show create table Users` and `show create table scan`) so we can reproduce your setup and test. — Geoffrey, May 24 '18 at 21:04
@Geoffrey sry for the late response. Added the definitions above. Thanks in advance. — Unxcellent, May 29 '18 at 03:41
@Geoffrey the table Scan is just a duplicate of the table Users. They both have the same columns and everything. Plan was to use Users for searching like in the example above and scan to have timestamp on when a user was searched and create a report from that. Hope that clears things. — Unxcellent, May 30 '18 at 12:20
I was able to make it work. it seems that it was a combination of both having too many quotes and inserting less data. So, I removed extra quotes as well as added the rest of the data missing .Thanks for the help. — Unxcellent, Jun 01 '18 at 13:51

UkFLSUI · Answer 2 · 2018-05-23T07:45:33.207

First of all don't use the variables directly inside the query. For security purpose, prepared statement is highly recommended now a days.

So, change your query like this and as you are executing two queries one after another at the same time, it is necessary to name the variables to different name else the later will overwrite the previous one:

$query1 = "SELECT * FROM Users WHERE user_id = ? LIMIT 1";
$query2 = "INSERT INTO scan (user_id, osha, firstname, lastname, company, trade, email, picture) SELECT user_id, osha, firstname, lastname, company, trade, email, picture FROM Users WHERE user_id = ? LIMIT 1";

Then create prepared statement like below:

$stmt = mysqli_stmt_init($connect);
$stmt2 = mysqli_stmt_init($connect);

mysqli_stmt_prepare($stmt, $query1);
mysqli_stmt_prepare($stmt2, $query2);

mysqli_stmt_bind_param($stmt, "s", $user_id);
mysqli_stmt_bind_param($stmt2, "s", $user_id);

Then execute the queries:

mysqli_stmt_execute($stmt);
mysqli_stmt_execute($stmt2);

Finally, you get the results of $query1 by this:

$result = mysqli_stmt_get_result($stmt);

You can also use `mysqli_real_escape_string` as a perfectly acceptable alternative to parameterised queries, it is actually faster, produces cleaner code, and just as secure provided you set your charset properly. Parameterised queries are faster when iterating and inserting sets of data, but for singular queries it adds additional overheads. — Geoffrey, May 23 '18 at 02:50

Running multiple queries in php

2 Answers2

Linked

Related