从一个查询中选择ID，然后在另一个查询中使用 [英] Select id from one query and use it in another

问题描述

我有这个代码.在第一个查询中，我希望它选择一个pid.然后，我想以某种方式在第二个查询中将所选的pid用作WHERE.这不起作用，但是我希望它能在此(相同)页面上起作用.我已经在其他论坛上阅读过有关此内容的信息，但仍没有解决.可能是某个地方的小错误.

I have this code. In the first query I want it to select a pid. Then I want to somehow use the selected pid as WHERE in the second query. This do not work but I want it to work on this(the same) page. I have read about this on other forums but I still didn't fix it. Probably a small mistake somewhere.

<?php
ini_set('display_errors',1);
error_reporting(E_ALL);

if(mysqli_connect_errno())
{
  echo mysqli_connect_error();
}

$loggedInUserId = $_SESSION['user_id'];
$resu = mysql_query("SELECT pid FROM users WHERE id='$loggedInUserId';");
$ro = mysql_fetch_row($resu);

$sql= "SELECT pid, project_name, image, image_type FROM project WHERE pid ='". $row["pid"]. "';";

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

if ($result->num_rows > 0) {
    while($row = $result->fetch_array()) {
    
        //$type= "Content-type:".$row['image_type'];
    //header ($type);
    echo "<form action='respodents.php' method='post'><button name='submit' id='projectbutton'>
            <div> 
                <img src=pic.php?pid=".$row['pid']." width=100px height=100px/>"." <div  id='project_name'>".$row['project_name']."</div>"."
       
            <input type='hidden' name='pid' value='".$row['pid']."'>
            <input type='hidden' name='project_name' value='".$row['project_name']."'>
            
            </div>
       </button></form>";
    }}

mysqli_close($mysqli);

?>

推荐答案

相对于sql，也许这可能有效

With respect to the sql, perhaps this might work

SELECT `pid`, `project_name`, `image`, `image_type` 
    FROM `project` WHERE `pid` = ( 
        SELECT `pid` FROM `users` WHERE `id`='$loggedInUserId'
    );

原始代码混合了mysql和mysqli函数以及Object Orientated和Procedural方法调用.虽然这不一定会导致错误，但这样做是错误的做法.以下是所有程序的样式-尚未经过测试，但是将两个查询合并为一个应该有效的查询(著名的遗言)

The original code had a mix of mysql and mysqli functions with a further mix of Object Orientated and Procedural method calls. Whilst this wouldn't cause an error necessarily it is bad practise to do so. Below is all in a procedural style - it's not tested but it incorporates the two queries into one which should work ( famous last words )

侧注:也就是说-使用mysqli您可以利用prepared statements来帮助减轻sql注入的威胁-精简和使用非常简单-因此，与其嵌入一个sql中的变量，您将使用占位符，然后将变量绑定到该占位符.

Sidenote: That said - with mysqli you can take advantage of prepared statements which help mitigate against the threat of sql injection - it's quite straightforward to lean and use - so rather than embedding a variable in the sql you would use a placeholder and then bind a variable to that.

<?php
    ini_set('display_errors',1);
    error_reporting(E_ALL);

    /* db connection? */


    if( mysqli_connect_errno() ) echo mysqli_connect_error();
    else {

        $loggedInUserId = $_SESSION['user_id'];

        $sql="select `pid`, `project_name`, `image`, `image_type` 
            from `project` 
            where `pid` = ( 
                select `pid` from `users` where `id`='$loggedinuserid'
            );";

        $resu=mysqli_query( $mysqli, $sql );
        if( $resu ){

            $ro = mysqli_fetch_row( $resu );

            while( $row=mysqli_fetch_object( $resu ) ){

                echo "<form action='respodents.php' method='post'>
                        <button name='submit' id='projectbutton'><!-- you cannot re-use IDs, they MUST be unique! -->
                            <div> 
                                <img src'=pic.php?pid=".$row->pid."' width='100px' height='100px'/>
                                <div id='project_name'>".$row->project_name."</div><!-- you cannot re-use IDs, they MUST be unique! -->
                                <input type='hidden' name='pid' value='".$row->pid."'/>
                                <input type='hidden' name='project_name' value='".$row->project_name."'/>
                            </div>
                        </button>
                   </form>";

            }
        }
        mysqli_close( $mysqli );
    }
?>

这篇关于从一个查询中选择ID，然后在另一个查询中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！