Skip to content Skip to sidebar Skip to footer
Home / Html / Php

Running Multiple Queries In Php

Post a Comment
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

Solution 1:

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>

Solution 2:

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);

Post a Comment for "Running Multiple Queries In Php"