Insert values to SQL Server & PHP connection with foreign key

Question

We are developing a columbarium management system

We are trying to get data entry from deceased and customer entries (single form) to their tables and for some reason we cannot get the data to store in database

Here is the stored procedure

CREATE PROCEDURE insertDC(
  @DLastName VARCHAR(50)
, @DFirstName VARCHAR (50)
, @DSex VARCHAR (6)
, @DateOfBirth DATE
, @DeathDate DATE
, @DeceasedNo INT
, @CLastName varchar(50)
, @CFirstName varchar(50)
, @CSex varchar(6)
, @ContactNo numeric(11)
, @CustAddress varchar(50)
)
AS
BEGIN

INSERT INTO Deceased(DLastName, DFirstName, DSex, DateOfBirth, DeathDate)
VALUES(@DLastName,@DFirstName ,@DSex ,@DateOfBirth ,@DeathDate)

INSERT INTO Customer(DeceasedNo, CLastName, CFirstName, CSex,ContactNo,CustAddress)
VALUES(SCOPE_IDENTITY(), @CLastName, @CFirstName, @CSex,@ContactNo,@CustAddress)
END

And this is the database connection in PHP

if($_POST['CSubmit'] == "ADD")
{   
    $serverName = ""; //serverName\instanceName
    $connectionInfo = array("Database"=>"");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    if( $conn ) 
    {
        //setup variable data
        $DLastName = $_POST['DLastName'];
        $DFirstName = $_POST['DFirstName'];
        $DSex = $_POST['DSex'];
        $DateOfBirth = $_POST['DateOfBirth'];
        $DeathDate = $_POST['DeathDate'];
        $CLastName = $_POST['CLastName'];
        $CFirstName = $_POST['CFirstName'];
        $CSex = $_POST['CSex'];
        $ContactNo = $_POST['ContactNo'];
        $CustAddress = $_POST['CustAddress'];       

        $sql = "EXEC insertDC @DLastName = '$DLastName', @DFirstName = '$DFirstName', 
        @DSex = '$DSex', @DateOfBirth = '$DateOfBirth',@DeathDate='$DeathDate',
        @CLastName = '$CLastName', @CFirstName = '$CFirstName', 
        @CSex = '$CSex', @ContactNo = '$ContactNo',@CustAddress='$CustAddress'";        

        $stmt = sqlsrv_query($conn, $sql);
    }
}

We are also having trouble on how do we link the foreign key to other tables.

Please help.

Careful, what you have is **wide open** to injection attacks. Before fixing anything else fix your security flaws. — Thom A, Mar 01 '21 at 19:05
this is a just a college project and we are really just trying to insert because we cant make any progress until we achieved that. and sorry if my english is bad — Charlotte V, Mar 01 '21 at 19:14
So what happens when you run your code? Do you get an error? — Dale K, Mar 01 '21 at 19:17
If it's a college project, you should be making sure you do it right when you hand it in. If I were a tutor, I would be (significantly) marking down a student's project if it has huge security vulnerabilities. — Thom A, Mar 01 '21 at 19:19
there's no error but there is no insertion happening too. the data entered are not in the database record. — Charlotte V, Mar 01 '21 at 19:20
This is just two inserts. Why do you need a stored procedure? — Tangentially Perpendicular, Mar 01 '21 at 19:46
@TangentiallyPerpendicular our prof required us to use stored procedure that's why. she said we cant use "SELECT * FROM TABLE", etc. on the php connection file. and we are thinking that our php connection file is the problem.... — Charlotte V, Mar 01 '21 at 19:48
Two notes: 1) Use [parameterized statements](https://learn.microsoft.com/en-us/sql/connect/php/sqlsrv-query?view=sql-server-ver15#remarks), always; 2) Pass date values as text using unambiguous date format (`yyyymmdd` in your case). — Zhorov, Mar 01 '21 at 19:59

Insert values to SQL Server & PHP connection with foreign key

0 Answers0