MYSQL insertion using PHP prepared Statement

Question

I am trying to insert a row in mysql table using the below php prepared statement, but the code always pass the statement and move to echo "failed". what is missing in the below code?

(My Database has extra columns but I didn't add it as I don't want to insert values inside (one of these columns are auto increment))

<?php
    $ActivityDate = $_POST["ActivitytDate"];
    $CoreSite = $_POST["CoreSite"];
    $ActionAuditor = $_POST["ActionAuditor"];
    $DCOSPOC = $_POST["DCOSPOC"];

    if($stmt = $mysqli->prepare("Insert INTO DCO_Database (ActivityDate, CoreSite, ActionAuditor, DCOSPOC) Where (ActivityDate=? AND CoreSite=? AND ActionAuditor=? AND DCOSPOC=?)"))
    {
        $stmt->bind_param("ssss", $ActivityDate, $CoreSite, $ActionAuditor, $DCOSPOC);
        $stmt->execute();
        $stmt->close();
    }
    else{
        echo ("Failed");
        $mysqli->close();    
    }
?>

I have editied the code to use values instead, it is not echoing Failed but echoing success .. but still not adding values to database

<?php
$ActivityDate = $_POST["ActivitytDate"];
$CoreSite = $_POST["CoreSite"];
$ActionAuditor = $_POST["ActionAuditor"];
$DCOSPOC = $_POST["DCOSPOC"];
$AreaOwner = $_POST["AreaOwner"];
$ActionImplementer = $_POST["ActionImplementer"];
$ActionOwner = $_POST["ActionOwner"];
$MailSubject = $_POST["MailSubject"];
$ActionType = $_POST["ActionType"];
$RequestType = $_POST["RequestType"];
$RequestNumber = $_POST["RequestNumber"];
$OpenTime = $_POST["OpenTime"];
$CloseTime = $_POST["CloseTime"];
$ActionResult = $_POST["ActionResult"];
$Violation = $_POST["Violation"];
$ActionDetails = $_POST["ActionDetails"];
$Snags = $_POST["Snags"];
$SnagDesc = $_POST["SnagDesc"];
$Layout = $_POST["Layout"];
$LayoutDesc = $_POST["LayoutDesc"];
$CabinetLocation = $_POST["CabinetLocation"];
$Mapping = $_POST["Mapping"];
$MappingDesc = $_POST["MappingDesc"];
$Notes = $_POST["Notes"];

if($stmt = $mysqli->prepare("Insert INTO DCO_Database (ActivityDate, CoreSite, ActionAuditor, DCOSPOC, AreaOwner, ActionImplementer, ActionOwner, MailSubject, ActionType, RequestType, RequestNumber, OpenTime, CloseTime, ActionResult, Violation, ActionDetails, Snags, SnagDesc, Layout, LayoutDesc, CabinetLocation, Mapping, MappingDesc, Notes) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"))
{
$stmt->bind_param("ssssssssssssssssssssssss", $ActivityDate, $CoreSite, $ActionAuditor, $DCOSPOC, $AreaOwner, $ActionImplementer, $ActionOwner, $MailSubject, $ActionType, $RequestType, $RequestNumber, $OpenTime, $CloseTime, $ActionResult, $Violation, $ActionDetails, $Snags, $SnagDesc, $Layout, $LayoutDesc, $CabinetLocation, $Mapping, $MappingDesc, $Notes);
$stmt->execute();
$stmt->close();
}
else{
echo ("Failed");
$mysqli->close();    
}
echo ("Successful");
?>

You can't use where clause in INSERT query. I think you are looking for UPDATE! — Saty, May 23 '16 at 11:24
It seems like the ``prepare`` method of the class object ``$mysqli`` (which by the way is defined nowhere in your code) is returning a falsy value. You may want to revisit that part, or paste code from the class that handles the database stuff. — designcise, May 23 '16 at 11:28
An insert query should look like this `INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);` — Matheno, May 23 '16 at 11:30
Possible duplicate of [Inserting data to table (mysqli insert)](http://stackoverflow.com/questions/16835753/inserting-data-to-table-mysqli-insert) — Matheno, May 23 '16 at 11:31

score 5 · Accepted Answer · answered May 23 '16 at 11:31

// think you should do as follows, insert statement only allows where clause when select statement is used,

<?php
$ActivityDate = $_POST["ActivitytDate"];
$CoreSite = $_POST["CoreSite"];
$ActionAuditor = $_POST["ActionAuditor"];
$DCOSPOC = $_POST["DCOSPOC"];

if($stmt = $mysqli->prepare("Insert INTO DCO_Database (ActivityDate, CoreSite, ActionAuditor, DCOSPOC) values (?,?,?,?)");
{
$stmt->bind_param("ssss", $ActivityDate, $CoreSite, $ActionAuditor, $DCOSPOC);
$stmt->execute();
$stmt->close();
}
else{
echo ("Failed");
$mysqli->close();    
}
?>

I updated the code and updated the post with values but still not working, check the edit if possible — Sheriff Said Elahl, May 23 '16 at 12:14

score 2 · Answer 2 · answered May 23 '16 at 11:37

use values insted of where

<?php
 $ActivityDate = $_POST["ActivitytDate"];
 $CoreSite = $_POST["CoreSite"];
 $ActionAuditor = $_POST["ActionAuditor"];
  $DCOSPOC = $_POST["DCOSPOC"];

  if($stmt = $mysqli->prepare("Insert INTO DCO_Database (ActivityDate,  CoreSite, ActionAuditor, DCOSPOC) values(?,?,?,?)"))
  {

 $stmt->bind_param("ssss", $ActivityDate, $CoreSite, $ActionAuditor, $DCOSPOC);
 $stmt->execute();
 $stmt->close();

 }
 else{
   echo ("Failed");
   $mysqli->close();    
 }

?>

Deep Kakkar · Answer 3 · 2016-05-23T11:37:35.017

1

You have to use

values

in your query. and remove

AND

from the query too. Please check the following code.

if($stmt = $mysqli->prepare("Insert INTO DCO_Database (ActivityDate, CoreSite, ActionAuditor, DCOSPOC) values (?,?,?,?)"))
{
$stmt->bind_param("ssss", $ActivityDate, $CoreSite, $ActionAuditor, $DCOSPOC);
$stmt->execute();
$stmt->close();
}
else{
echo ("Failed");
$mysqli->close();    
}

Please note that where is used for update. so to insert use the query as follows:

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

edited May 23 '16 at 11:37

answered May 23 '16 at 11:30

Deep Kakkar

5,831
4
39
75

This is not a valid query. – Matheno May 23 '16 at 11:31
thanks, I updated the code with values but still not working, would you please take a look on the code edit in the post – Sheriff Said Elahl May 23 '16 at 12:15
1

Solved, I was writing extra fetch commands caused the problem. many thanks – Sheriff Said Elahl May 23 '16 at 13:09

MYSQL insertion using PHP prepared Statement

3 Answers3