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?

Please provide your tables structure

Slava Rozhnev
– Slava Rozhnev

2020-12-30 09:30:47 +00:00
Commented Dec 30, 2020 at 9:30 — Slava Rozhnev
– Slava Rozhnev, Commented Dec 30, 2020 at 9:30

Zhorov · Accepted Answer · 2020-12-30 13:19:21Z

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;
}   
?>

edited Dec 30, 2020 at 13:19

answered Dec 30, 2020 at 8:06

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

maestrostas21 Over a year ago

Thank you for answer, but it's still not working. First query goes to database but second is not. Without any errors.

Zhorov Over a year ago

@maestrostas21 Well, probably the second SELECT statement (

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

) doesn't return any rows.

maestrostas21 Over a year ago

Yes, but why? The same query in managment studio works fine.

maestrostas21 Over a year ago

Now I see that there is something wrong with query. It's not working. I'll try to make it work.

maestrostas21 Over a year ago

Works like a dream. Thank you very much Zhorov

|

Collectives™ on Stack Overflow

Inserting into database from php

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related