CRUD使用prepare语句不起作用。

Question

Database connecting is working. The SELECT AND UPDATE FUNCTION in the class is not working at all.It is not even showing errors to help me sort out the problem. I am trying to learn how to use the prepare ,bind-param and execute statement. Please can someone help look at the codes and advise what may be wrong with it. Just spent loads of hours on this and just cant figure where the problems is. please can some help me.I am a novice and writing my very first codes . Many thanks in advance

 <?php class connect_dbase{
       public $mysqli;
       public function connection($host="localhost",$user="root",$password="london",$db_name="users")       
        {
            $this->mysqli=new mysqli($host,$user,$password,$db_name);
            if ($this->mysqli->connect_error) {
               die('Connect Error: ' . $this->mysqli->connect_error);   
            }
            else{
                echo " Database connection successful";
                }           
        }       
        public function display_all($id){
                   if($stmt = $this->mysqli->prepare("SELECT * FROM user WHERE id =?")){
                      /* bind parameters for markers */
                      $stmt->bind_param('i',$id);

                      /* execute query */
                      $stmt->execute();

                      if($stmt->num_row() >0){ 
                         echo 'Total results: ' . $resultrol->num_rows;
                     $result = $stmt->get_result();

                         while ($row = $result->fetch_assoc()) {
                               echo $row['name'];
                               echo $row['email'];
                               echo $row['address'];}  
                  }    
                      else {  echo "no result found";}
       }    
       else
       {
        echo "cant prepare result";     
       }    
    }

    public function update_post($name, $address,$email,$mob,$id)
        {
            $up="UPDATE user SET name=?, address =?,email=?,mobile=? WHERE id =?";

            if($stmt=$mysqli->prepare($up))
            {
                $stmt->bind_param("sssii", $name, $address,$email,$mob,$id);

                            if($stmt->excute()) { 
                              echo " post updated";
                  header('location:index.php'); 
                }
                else
                {
                   echo "post not executed";

                }
            }else{ echo " cannot prepare statement";}

            }

    }
    $connect_dbase=new connect_dbase();     
    $connect_dbase->connection();
    $connect_dbase->display_all(2);
    $connect_dbase-> update_post("john","kkkkk","[e-mail]",98765,2);

    // These 2 functions- $connect_dbase->display_all(2); and 
    $connect_dbase-> update_post("john","kkkkk","[e-mail]",98765,2); are not working when called from the class above .

    ?>

Answer 1

I agree with @MikeBrant's comments. You should make the connection happen in the constructor if you want to be assured that the connection is successful before you try to call it.

Here's another tip:

if($stmt->num_row() >0){ 

Note that num_rows() doesn't return anything useful until after the client has fetched the rows. So calling it right after execute() is pretty much guaranteed to make it return the wrong number.

You need to use mysqli::store_result() to transfer the result set from the server to the client, and then num_rows() will work. But be careful if the result set is very large, it could use too much memory.