Page 1 of 1

Getting Error: could not prepare SQL statement

Posted: Mon May 14, 2012 12:00 pm
by Mythri
Hi All,

I have been trying to resolve this issue but couldnt do it. Please help me in this.

Code: Select all

<?php 

        include("db_connect.php"); 
         $mysqli = new mysqli("localhost", "user", "pass", "db"); 
                  if ($mysqli->connect_errno) { 
    printf("Connect failed: %s\n", $mysqli->connect_error); 
    exit(); 
} 
      
        function renderForm($ProductName = '', $Description ='', $Price ='', $Size ='', $error = '', $ProductID = '') 
        { ?> 
                <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> 
                <html> 
                        <head>  
                                <title> 
                                        <?php if ($ProductID != '') { echo "Edit Record"; } else { echo "New Record"; } ?> 
                                </title> 
                                <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> 
                        </head> 
                        <body> 
                                <h1><?php if ($ProductID != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1> 
                                <?php if ($error != '') { 
                                        echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error 
                                                . "</div>"; 
                                } ?> 
                                
                                <form action="" method="post"> 
                                <div> 
                                        <?php if ($ProductID != '') { ?> 
                                                <input type="hidden" name="id" value="<?php echo $ProductID; ?>" /> 
                                                <p>ID: <?php echo $id; ?></p> 
                                        <?php } ?> 
                                        
                                        <strong>Product Name: *</strong> <input type="text" name="ProductName" 
                                                value="<?php echo $ProductName; ?>"/><br/> 
                                        <strong>Description: *</strong> <input type="text" name="Description" 
                                                value="<?php echo $Description; ?>"/> 
                                         <strong>Price: *</strong> <input type="text" name="Price" 
                                                value="<?php echo $Price; ?>"/> 
                                          <strong>Size: *</strong> <input type="text" name="Size" 
                                                value="<?php echo $Size; ?>"/>            
                                        <p>* required</p> 
                                        <input type="submit" name="submit" value="Submit" /> 
                                </div> 
                                </form> 
                        </body> 
                </html> 
                
        <?php } 



        /* 

           EDIT RECORD 

        */ 
        
        if (isset($_GET['ProductID'])) 
        { 
                
                if (isset($_POST['submit'])) 
                { 
                        
                        if (is_numeric($_POST['ProductID'])) 
                        { 
                                
                                $productid = $_POST['ProductID']; 
                                $productname = htmlentities($_POST['ProductName'], ENT_QUOTES); 
                                $description = htmlentities($_POST['Description'], ENT_QUOTES); 
                                $price = htmlentities($_POST['Price'], ENT_QUOTES); 
                                $size = htmlentities($_POST['Size'], ENT_QUOTES); 
                                
                                
                                if ($productname == '' || $description == '' || $price =='' || $size =='') 
                                { 
                                        // if they are empty, show an error message and display the form 
                                        $error = 'ERROR: Please fill in all required fields!'; 
                                        renderForm($productname, $description, $price, $size, $error, $productid); 
                                } 
                                else 
                                { 
                                
                                      
                                        if ($stmt = $mysqli->prepare("UPDATE Products SET ProductName = ?, Description = ?, Price = ?, Size = ? 
                                                WHERE id=?")) 
                                        { 
                                                $stmt->bind_param("ssi", $productname, $description, $price, $size, $id); 
                                                $stmt->execute(); 
                                                $stmt->close(); 
                                        } 
                                        
                                        else 
                                        { 
                                                echo "ERROR: could not prepare SQL statement."; 
                                        } 
                                        
                                        
                                        header("Location: view.php"); 
                                } 
                        } 
                        
                        else 
                        { 
                                echo "Error!"; 
                        } 
                } 
                
                else 
                { 
                        
                        if (is_numeric($_GET['ProductID']) && $_GET['ProductID'] > 0) 
                        { 
                                $id = $_GET['ProductID']; 
                                
                                
                                if($stmt = $mysqli->prepare("SELECT * FROM Products WHERE id=?")) 
                                { 
                                        $stmt->bind_param("i", $id); 
                                        $stmt->execute(); 
                                        
                                        $stmt->bind_result($id, $ProductName, $Description, $Price,  $Size); 
                                        $stmt->fetch(); 
                                        
                                        
                                        renderForm($ProductName, $Description, $Price, $Size, NULL, $id); 
                                        
                                        $stmt->close(); 
                                } 
                                
                                else 
                                { 
                                        echo "Error: could not prepare SQL statement"; 
                                } 
                        } 
                        
                        else 
                        { 
                                header("Location: view.php"); 
                        } 
                } 
        } 



        /* 

           NEW RECORD 

        */ 
        
        else 
        { 
                
                if (isset($_POST['submit'])) 
                { 
                        // get the form data 
                        
                        $ProductName = htmlentities($_POST['ProductName'], ENT_QUOTES); 
                        $Description = htmlentities($_POST['Description'], ENT_QUOTES); 
                        $Price = htmlentities($_POST['Price'], ENT_QUOTES); 
                        $Size = htmlentities($_POST['Size'], ENT_QUOTES); 
                        
                        
                        if ($ProductName == '' || $Description == '' || Price =='' || Size =='') 
                        { 
                                
                                $error = 'ERROR: Please fill in all required fields!'; 
                                renderForm($ProductName, $Description, $Price,  $Size, $error); 
                        } 
                        else 
                        { 
                                
                                
                                if ($stmt = $mysqli->prepare("INSERT Products (ProductName, Description, Price, Size) VALUES (?, ?, ?, ?)")) 
                                { 
                                        $stmt->bind_param("ss", $ProductName, $Description, $Price,  $Size); 
                                        $stmt->execute(); 
                                        $stmt->close(); 
                                } 
                                // show an error if the query has an error 
                                else 
                                { 
                                        echo "ERROR: Could not prepare SQL statement."; 
                                } 
                                
                                // redirec the user 
                                header("Location: view.php"); 
                        } 
                        
                } 
                
                else 
                { 
                        renderForm(); 
                } 
        } 
        
        
        
?>
I am getting Error: could not prepare SQL statement
Please help me in this

Re: Getting Error: could not prepare SQL statement

Posted: Mon May 14, 2012 12:33 pm
by Celauran
Your bind statement specifies three parameter types, but tries to bind five parameters.

Code: Select all

$stmt->bind_param("ssi", $productname, $description, $price, $size, $id); 
Something like this should fix it:

Code: Select all

$stmt->bind_param("ssssi", $productname, $description, $price, $size, $id); 

Re: Getting Error: could not prepare SQL statement

Posted: Mon May 14, 2012 12:59 pm
by Mythri
i have corrected it, still getting
Warning: mysqli_stmt::bind_result() [mysqli-stmt.bind-result]: Number of bind variables doesn't match number of fields in prepared statement in /home/netelmbn/public_html/test/records.php on line 114 error

Re: Getting Error: could not prepare SQL statement

Posted: Mon May 14, 2012 1:00 pm
by Celauran
Then you clearly haven't corrected it. Please post the appropriate code.