在php中运行多个查询 [英] Running multiple queries in php

查看：67 发布时间：2018/6/19 14:29:09 php html

本文介绍了在php中运行多个查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我是PHP和HTML的新手。按下提交按钮后，我试图用已经在用户MySQL表中的数据填充字段（这是可行的）。我还希望将使用SELECT获得的相同数据插入到另一个名为scan的SQL表中。

 <？php 
 // php代码在mysql数据库中搜索数据并将其设置为输入文本
 if（isset（$ _ POST ['search']））
 {
 
 
 // id搜索
 $ user_id = $ _POST ['user_id']; 
 
 //连接到mysql 
 $ connect = mysqli_connect（127.0.0.1，root，root，demodb）; 
 
 // mysql搜索查询
 
 
 $查询=SELECT * FROM用户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 exists 
 //在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']; 
} 
} 
 
 //如果ID不存在
 //显示一条消息并清除输入
 else {
 echoUndifined ID; 
 
 $ osha =; 
 $ firstname =; 
 $ lastname =; 
 $ company =; 
 $ trade =; 
} 
 
 
 mysqli_free_result（$ result）; 
 
 mysqli_close（$ connect）; 
} 
 
 
 //第一次输入为空
 else {
 $ osha =; 
 $ firstname =; 
 $ lastname =; 
 $ company =; 
 $ trade =; 
} 
 
 
？> 
 
<！DOCTYPE html> 
 
< html> 
 
< head> 
 
< title> PHP FIND DATA< / title> 
 
< meta charset =UTF-8> 
 
< meta name =viewportcontent =width = device-width，initial-scale = 1.0> 
 
< / head> 
 
< body> 
 
< form action =barcode.phpmethod =post> 
 
 Id：< input type =textname =user_id>< br>< br> 
 
 Osha＃：< input type =textname =oshavalue =<？php echo $ osha;？>>< br>< br> 
 
名字：< input type =textname =firstnamevalue =<？php echo $ firstname;？>>< br> 
< br> 
 
姓氏：< input type =textname =lastnamevalue =<？php echo $ lastname;？>>< br>< br> 
 
公司：< input type =textname =companyvalue =<？php echo $ company;？>>< br>< br> 
 
交易：< input type =textname =tradevalue =<？php echo $ trade;？>>< br>< br> 
 
< input type =submitname =searchvalue =Find> 
 
< / form> 
 
< / body> 
 
< / html>

但似乎我只能在PHP中运行一个查询。我尝试整合mysqli_multi_query，但我不断收到以下错误mysqli_num_rows（）期望参数1为mysqli_result。

如何运行两个查询并同时用数据填充字段。

添加表格定义

用户表

  |用户| 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，$ b $```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表格

  |扫描| 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，$ b $```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 |

解决方案

您正在覆盖变量 $ query 替换为新值，而不是先执行查询。这就是说，尽管你的代码有很多问题：
$ b $ ul

你不是逃避 $ _ POST ['user_id'] code>，请仔细阅读SQL注入攻击。

 
 您没有转义您的HTML，请阅读XSS攻击。您的逻辑流程会复制代码
 
 通过读取数据两次，只读取一次并插入一次，您将在数据库上增加额外的负载。 
 
 请参阅下面重写的代码。
 <？php 
 / / initalize变量
 $ osha =; 
 $ firstname =; 
 $ lastname =; 
 $ company =; 
 $ trade =; 
 
 php代码在mysql数据库中搜索数据并将其设置为输入文本
 if（isset（$ _ POST ['search']））
 {
 //连接到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']; 
 $ b $ 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 
 {
 echoUndefined ID; 
} 
} 
？> 
 
<！DOCTYPE html> 
 
< html> 
 
< head> 
 
< title> PHP FIND DATA< / title> 
 
< meta charset =UTF-8> 
 
< meta name =viewportcontent =width = device-width，initial-scale = 1.0> 
 
< / head> 
 
< body> 
 
< form action =barcode.phpmethod =post> 
 
 Id：< input type =textname =user_id>< br>< br> 
 
 Osha＃：< input type =textname =oshavalue =<？= htmlspecialchars（$ osha）？>>< br>< br> 
 
名字：< input type =textname =firstnamevalue =<？= htmlspecialchars（$ firstname）？>>< br> 
< br> 
 
姓氏：< input type =textname =lastnamevalue =<？= htmlspecialchars（$ lastname）？>>< br>< br> 
 
公司：< input type =textname =companyvalue =<？= htmlspecialchars（$ company）？>>< br>< br> 
 
交易：< input type =textname =tradevalue =<？= htmlspecialchars（$ trade）？>>< br>< br> 
 
< input type =submitname =searchvalue =Find> 
 
< / form> 
 
< / body> 
 
< / html> 
  
 
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 |

 解决方案 
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>


                        
这篇关于在php中运行多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！


                    
                        查看全文

在php中运行多个查询 [英] Running multiple queries in php

问题描述

相关文章

PHP最新文章

热门教程

热门工具

登录关闭

在php中运行多个查询 [英] Running multiple queries in php

问题描述

相关文章

PHP最新文章

热门教程

热门工具

登录 关闭

登录关闭