Can't list items when updating a table with prepared statements

mysql

#1

I have a problem with while loop I used

$row = $result->fetch_array(MYSQLI_ASSOC);

Since result was set contains only one row it was fine,
but I needed to check editer with both ids, item_id and user_id,
then had to use while loop and code stopped listing values.
why I needed to check editer with item_id and user_id ?
I have 3 user roles if a user in same role steals the ids from another user oops he/she could delete,edit, item.

here is the code SELECT PART OF THE CODE (which will selet item values from items table and fill in the form )
So this is a question and a lesson (espacialy for newcomers like me :slight_smile: )

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

and Html form in update_item.php

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

And this is a small function to clear some html tags in posts can be use in forms when validating.

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

Ok Main problem is I cant list item values from database to update table.
need your help, I am using $conn = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);oop


#2

The first thing you want to do is stop suppressing mysqli. If you have an error some where, you'd want to know so that you can fix it. Suppressing mysqli just "hides" the errors and will create further errors later on.

You don't need this function if you aren't even going to use it. No where in your code have I seen this code being used. It is useless to have something just sitting there and not even being used and it also wastes resources.

You don't need this if you aren't uploading files.

How are you getting these variables if you haven't declared them yet?


This whole thing is flawed and that's why it isn't listing anything after updating your tables.


#3

lol. connection is corrected in question.

need to use it if user want to add anew image to item or change current image, as this code is working in an update page.

I am not getting them, html form is eemty. how to declare them ? can you please show me an example ?

This code is used in main code which is updating table and in original code instead of htmlspecialchars.

ı removed from this code for a test to see if this code is cousing a problem but no

here is an example of the main code

    $mysqli->close();
} else{
    if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
        $id =  trim($_GET["id"]);
        $sql = "SELECT * FROM users WHERE id = ?";
        if($stmt = $mysqli->prepare($sql)){
            $stmt->bind_param("i", $param_id);
            $param_id = $id;
            if($stmt->execute()){
                $result = $stmt->get_result();
                if($result->num_rows == 1){
                    $row = $result->fetch_array(MYSQLI_ASSOC);
                    $name = $row["name"];
                    $address = $row["address"];
                    $salary = $row["salary"];
                } else{
                    header("location: error.php");
                    exit();
                }
                
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }
        }
        // Close statement
        $stmt->close();
        // Close connection
        $mysqli->close();
    }  else{
        // URL doesn't contain id parameter. Redirect to error page
        header("location: error.php");
        exit();
    }
}

#4

That's because you have to create those variables before you can use them. You haven't created them yet.


#5

Can you show me an example of decleration please ?


#6

This is creating and assigning the variable.


#7

oh ok, I got that on top of the page

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

and here


#8

No, you're supposed to create

These variables so that it isn't empty. If you enabled error logs, you'd most likely see Undefined Index error because you are trying to reference variables that don't exist.


#9

create in where ?
arent they allready created as parameters ?

possible to have a code example where to writethem ?


#10

Created in your first PHP snippet above.

Nope. They aren't "created". You have to do it yourself.

I just gave you a code example above.

That's how you're supposed to create your variables. I am trying to point you in the right direction, but I am also NOT going to do it for you.


I have also confirmed that you will get Undefined Index errors if you turn your error logs on.


#11

thanks a lot for helping.
I dont want you to write it for me I need to learn.
just tell me where to write them ?

here ?

if(isset($GET['itemid']) && !empty(test_input($GET['itemid'])) AND isset($GET['userid']) && !empty(test_input($GET['userid']))){
$item_id = test_input($GET['itemid']);
$user_id = test_input($GET['userid']);

under this values ?

    $sql = "SELECT * FROM users WHERE id = ?";

or on to the of the form where $item_id = test_input($GET['itemid']); is?


#12

Nope. You have to understand where it's supposed to be placed. If you randomly put them where you "think" might be correct, you'll just create more errors for yourself.

If I am referring to these lines, where do you think you should create those missing variables?


#13

here is I think where they sould be

            $stmt->bind_param("ii", $param_item_id, $param_user_id);

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

#14

Wrong. Stop guessing. Look at the lines I am talking about. Also, that was a rhetorical question.


#15

$result = $stmt->get_result($id, $name);

or need to go for
$stmt->bind_result($id, $name);

I think they should be declared in there


#16

Still wrong. Again, look at these lines

And tell me where you should create the appropriate variables without guessing.


Though using bind_result() would help you more because you don't understand where you create those variables. So in a way, this is correct. But you would have to re-write your whole output code.


#17

This the best knowledge from me

            if($stmt->num_rows == 1){     
                $stmt->bind_result($uid, $name);

that is all I think really no clue where else they canbe writen, if there is an easy way I will be glad to hear.

its 4.40 am here :slight_smile:


#18

I'll give you the answer since you can't figure it out yourself. You have to create the missing variables within the while() loop. This means that you have to manually do

$cat_id = $row['cat_id'];
...

And so on. That is also the reason why I mentioned

Because you "create" and "assign" variables the same way you are doing above.

This is also because you are trying to get data and these variables aren't "magically" or "automatically" created. You have to create them yourself. Even in bind_result, you have to do the same thing. In bind_result, it is a little more easier because all you have to do is add the correct amount of variables to this function and then you can reference those variables whenever.

But get_result and bind_result require something different from one another. So this is why I said

Because get_result uses only uses fetch_assoc which bind_result doesn't. You will get an error if you use fetch_assoc for bind_result. Next, I already see a reason why those outputs won't really display because you are using a ; after the while loop. You are ending it there. You need to use curly braces and wrap your results in them right after the while loop.


#19

thank you very very much, I just changed whileloop to
while($row = $result->fetch_array()){
$item_name = $row['item_name'];
}
and tried it worked :slight_smile: but only between while {} stil dont work in form.
thanks for the tips I will work on it whenI wake up in morning.
well its worning right now need some rest.
gnight budd


#20

Just to let you know,
My problem was in here
if(isset($GET['itemid']) && !empty($GET['itemid']) AND isset($GET['userid']) && !empty($GET['userid'])){

Rest of code is fine.

But my security problem is not solved
I have checked it with two ids but still same as checking with only id.

This is the link to go to update page

Esupdate.php?item_id=44&user_id=49

if someone in same member role steals
item_id and user_id they can edit or delete current item.
Need to find a beter solution

EDIT : Solved that problem now
here is what I did still not sure if I am doing it right way

if(isset($_GET['item_id']) && !empty($_GET['item_id']) AND isset($_GET['user_id']) && !empty(htmlspecialchars($_SESSION['id']))){
    $item_id = $conn->escape_string($_GET['item_id']); 
    $user_id = $conn->escape_string($_SESSION['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);