sql server - Inserting into database from php

Question

I'm trying to insert data to my SQL Server database from PHP. I don't get any error, but data is not applied to db. I was trying with the same query but reformed to normal database query and it worked so I assume there is something wrong with my code:

$query1 = "
   INSERT INTO KPI(KPI_new_name, KPI_definition, KPI_tech_name) 
   VALUES('$KPI_new_name','$KPI_definition','$KPI_tech_name')
";

$query2 = "
   INSERT INTO ReportsKpiRel(Report_Id, KPI_Id) 
   select r.Report_Id, kpis.KPI_Id 
   from Reports r 
   inner join ReportsKpiRel RKR on r.Report_Id = RKR.Report_Id
   inner join KPI kpis on RKR.KPI_Id = kpis.KPI_Id
   where r.Report_Id = '".$repid."' and kpis.KPI_new_name = '".$KPI_new_name."'
";

$qresult = sqlsrv_query($conn, $query1); 
if ($qresult)
{
  $qresult2 = sqlsrv_query($conn, $query2); 
  if($qresult2)
  {
    echo "Success!";
  }
  else
    echo "Failed!";
}
else
echo "error!";

First query goes smoothly. Second is not working. Have you got any idea what is wrong?

Answer 1

Original answer:

You need to check for errors after each sqlsrv_query() call. But, the more important issue with your approach, is that your code is open to possible SQL injection attacks. Always try to use parameterized queries. As is mentioned in the documentation, the sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply and sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.

The following code, based on the code in the question, is a possible solution to your problem:

<?php
$query1 = "
    INSERT INTO KPI(KPI_new_name, KPI_definition, KPI_tech_name) 
    VALUES(?, ?, ?)
";

$query2 = "
    INSERT INTO ReportsKpiRel(Report_Id, KPI_Id) 
    select r.Report_Id, kpis.KPI_Id 
    from Reports r 
    inner join ReportsKpiRel RKR on r.Report_Id = RKR.Report_Id
    inner join KPI kpis on RKR.KPI_Id = kpis.KPI_Id
    where r.Report_Id = ? and kpis.KPI_new_name = ? 
";

$params1 = array($KPI_new_name, $KPI_definition, $KPI_tech_name);
$result1 = sqlsrv_query($conn, $query1, $params1);
if ($result1 === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}   

$params2 = array($repid, $KPI_new_name);
$result2 = sqlsrv_query($conn, $query2, $params2);
if ($result2 === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

echo "Success!";    
?>

Update:

It seems that you have a different problem. So, if you want to handle many-to-many relationship and the KPI table has an identity column, you need to get the generated value using SCOPE_IDENTITY():

<?php
// INSERT into KPI
$query1 = "
    SET NOCOUNT ON;
    INSERT INTO KPI(KPI_new_name, KPI_definition, KPI_tech_name) 
    VALUES(?, ?, ?);
    SELECT SCOPE_IDENTITY() AS KPI_Id
";
$params1 = array($KPI_new_name, $KPI_definition, $KPI_tech_name);
$result1 = sqlsrv_query($conn, $query1, $params1);
if ($result1 === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
$row = sqlsrv_fetch_array($result1, SQLSRV_FETCH_ASSOC);
$kpiid = $row['KPI_Id'];

// INSERT into ReportsKpiRel
$query2 = "
    INSERT INTO ReportsKpiRel(Report_Id, KPI_Id) 
    VALUES (?, ?) 
";
$params2 = array($repid, $kpiid);
$result2 = sqlsrv_query($conn, $query2, $params2);
if ($result2 === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}   
?>