Cant list items when updating with prepare statmen

问题:

I am trying to create an update page with prepare statents, posting item_id and user_id going to update page with right vaules. but it doesnt list items values in inputs.

no errors, looks like everything fine except listing problem. when I create link with only item_id it works but when I change link to link below it doesnt work.

<a href='Esupdate.php?item_id=". $row['item_id'] ."&user_id=".htmlspecialchars($_SESSION['id']) ."' title='Update Record' data-toggle='tooltip'><span class='glyphicon glyphicon-pencil'></span></a>

here is my select code part.

if(isset($_GET['item_id']) && !empty(test_input($_GET['item_id'])) AND isset($_GET['user_id']) && !empty(test_input($_GET['user_id']))){
    $item_id = test_input($_GET['item_id']); 
    $user_id = test_input($_GET['user_id']); 
        // Prepare a select statement
        $sql = "SELECT * FROM items WHERE item_id = ? AND user_id = ?";
        if($stmt = $conn->prepare($sql)){
            // Bind variables to the prepared statement as parameters
            $stmt->bind_param("ii", $param_item_id, $param_user_id);

            // Set parameters
            $param_item_id = $item_id;
            $param_user_id = $user_id;

            // Attempt to execute the prepared statement
            if($stmt->execute()){
                $result = $stmt->get_result();

                if($result->num_rows == 1){
                    while($row = $result->fetch_assoc());
                    // Retrieve individual field value
                    $param_cat_id = htmlspecialchars($cat_id);
                    $param_item_name = htmlspecialchars($item_name);
                    $param_item_title = htmlspecialchars($item_title);
                    $param_item_image = htmlspecialchars($item_image);
                    $param_item_seo_url = htmlspecialchars($item_seo_url);
                    $param_item_detail = htmlspecialchars($item_detail);

                } else{
                    // URL doesn't contain valid id. Redirect to error page
                    header("location: error.php");
                    exit();
                }
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }
        }

        // Close statement
        $stmt->close();

        // Close connection
        $conn->close();
    }  else{
        // URL doesn't contain id parameter. Redirect to error page
        header("location: error.php");
        exit();
    }

Here is the form Should list items in.

        <form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post" enctype="multipart/form-data">
            <div class="form-group <?php echo (!empty($item_name_err)) ? 'has-error' : ''; ?>">
                <label>Name</label>
                <input type="text" name="item_name" class="form-control" value="<?php echo $item_name; ?>">
                <span class="help-block"><?php echo $item_name_err;?></span>
            </div>
            <div class="form-group <?php echo (!empty($item_title_err)) ? 'has-error' : ''; ?>">
                <label>Title/label>
                <input type="text" name="item_title" class="form-control" value="<?php echo $item_title; ?>">
                <span class="help-block"><?php echo $item_title_err;?></span>
            </div>
            <div class="form-group <?php echo (!empty($item_image_err)) ? 'has-error' : ''; ?>">
                <label for="item_image">Image</label>
                <input type="file" name="item_image" id="item_image" value="<?php echo $item_image; ?>">
                <p><strong>Note:</strong> Only .jpg, .jpeg, .gif, .png formats allowed to a max size of 5 MB.</p>
                <span class="help-block"><?php echo $item_image_err;?></span>
            </div>
            <div class="form-group <?php echo (!empty($item_detail_err)) ? 'has-error' : ''; ?>">
                <label>Detail</label>
                <textarea name="item_detail" class="form-control"><?php echo $item_detail; ?></textarea>
                <span class="help-block"><?php echo $item_detail_err;?></span>
            </div>                        
            <div class="form-group <?php echo (!empty($cat_id_err)) ? 'has-error' : ''; ?>">
                <select class="form-control" name="cat_id" value="<?php echo $cat_id; ?>">
                <option value="">Categories</option>
                <?php 
                   categoryTree();
                ?>
                </select>
                <span class="help-block"><?php echo $cat_id_err;?></span>
            </div> 
            <input type="submit" class="btn btn-primary" value="Submit">
            <a href="index.php" class="btn btn-default">Cancel</a>
        </form>

this is the test_input code comes from function.php I removed categoryTree function(which is listing categories) and test_input in codes nothing changed. I suspect something in code.

function test_input($data) {
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

EDIT : do I have to declare these variables ?

                $param_cat_id = htmlspecialchars($cat_id);
                $param_item_name = htmlspecialchars($item_name);
                $param_item_title = htmlspecialchars($item_title);
                $param_item_image = htmlspecialchars($item_image);
                $param_item_seo_url = htmlspecialchars($item_seo_url);
                $param_item_detail = htmlspecialchars($item_detail);

Like this ?

$item_id = test_input($_GET['item_id']); 
$user_id = test_input($_GET['user_id']); 

回答1:

I would advise changing the bind_param to

    // Bind variables to the prepared statement as parameters
    $stmt->bindParam(1, $param_item_id);
    $stmt->bindParam(2, $param_user_id);

and then you can set variables afterwards (although I always put them before the bind)

    // Set parameters
    $param_item_id = $item_id;
    $param_user_id = $user_id;

I would also suggest you change your sql to

$sql = "SELECT * FROM items WHERE item_id = :itemId AND user_id = :userId";

and then make your binds as follows;

    $stmt->bindParam(':itemId', $param_item_id);
    $stmt->bindParam(':userId', $param_user_id);