I am trying to insert data to 2 tables. A customer connection table and equipment table. I have a form that defines "source equipment", "source slot and port" and "source FNN" , destination equipment values as well as customer connection values.
I want to insert into 'cust_port' table the source and destination system ports and FNNS as cust_port values as well as the customer connection values.
Please note: Source values can be empty, destination values can be empty, but not both. ie there has to be at least a source and/or destination values. I don't want blank values being written to equipment table (cust_port) if they are blank.
I have tried multiple inserts but i can't get it to work.
try{
$insert_cust_port="INSERT INTO cust_port (
system_name,
slot_no,
port_no,
port_fnn
) VALUES (
:system_name_source,
:slot_source,
:port_source
:source_fnn
),(
:system_name_dest,
:slot_dest,
:port_dest,
:dest_fnn
)";
$insert_cust_conn="INSERT INTO cust_connections (
short_name,
source_fnn,
dest_fnn,
service_type,
ladder_side
) VALUES (
:short_name,
:source_fnn,
:dest_fnn,
:service_type,
:ladder_side
)";
//prepare query for excecution
$database->query($insert_cust_port);
//bind the parameters
$database->bind(':system_name_source', $system_name_source );
$database->bind(':slot_source', $slot_source );
$database->bind(':port_source', $port_source );
$database->bind(':source_fnn', htmlentities($source_fnn) );
$database->bind(':system_name_dest', $system_name_dest );
$database->bind(':slot_dest', $slot_dest );
$database->bind(':port_dest', $port_dest );
$database->bind(':dest_fnn', htmlentities($dest_fnn) );
$database->query($insert_cust_conn);
//bind the parameters
$database->bind(':short_name', $short_name );
$database->bind(':source_fnn', htmlentities($source_fnn) );
$database->bind(':dest_fnn', htmlentities($dest_fnn) );
$database->bind(':source_fnn', htmlentities($source_fnn) );
$database->bind(':s_type', $s_type );
$database->bind(':ladder_side', $ladder_side );
// Execute the query
$database->execute();
echo "Record was added.";
header ("location: customer_list.php");
} catch (Exception $e)
{
throw new Exception( 'Something went wrong. Please contact Administrator', 0, $e);
}
Customer connection table:
CREATE TABLE IF NOT EXISTS `cust_connections` (
`id` int(11) NOT NULL,
`short_name` char(15) COLLATE utf8_unicode_ci NOT NULL,
`source_fnn` char(10) COLLATE utf8_unicode_ci NOT NULL,
`dest_fnn` char(10) COLLATE utf8_unicode_ci NOT NULL,
`service_type` char(32) COLLATE utf8_unicode_ci NOT NULL,
`ladder_side` char(10) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `cust_connections` (`id`, `short_name`, `source_fnn`, `dest_fnn`, `service_type`, `ladder_side`) VALUES
(1, 'cust1', 'N2843453A', '', 'HD_300_Connect', 'src only'),
(2, 'cust2', '', 'N2843600A', 'HD_300_Connect', 'dest only'),
(3, 'cust3', 'N2720257O', 'N2731164O', 'DVB25_188byte', 'both'),
(4, 'cust4', 'N27xxx7O', 'N2731164O', 'DVB25_188byte', 'src ukn'),
(5, 'cust4', 'N27xxx7O', '', 'DVB25_188byte', 'ukn +blk');
ALTER TABLE `cust_connections`
ADD PRIMARY KEY (`id`);
ALTER TABLE `cust_connections`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=18;
Customer equipment table:
CREATE TABLE IF NOT EXISTS `cust_port` (
`id` smallint(11) NOT NULL,
`system_name` char(32) COLLATE utf8_unicode_ci NOT NULL,
`slot_no` char(2) COLLATE utf8_unicode_ci NOT NULL,
`port_no` char(2) COLLATE utf8_unicode_ci NOT NULL,
`port_fnn` char(9) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `cust_port` (`id`, `system_name`, `slot_no`, `port_no`, `port_fnn`) VALUES
(1, '01-06C2:source', '7', '1', 'N2843453A'),
(2, '01-27B4:dest', '1', '2', 'N2843600A'),
(3, '01-27B6:source+dst', '17', '3', 'N2720257O'),
(4, '01-27B6:dst+src', '17', '3', 'N2731164O'),
(5, '01-32C6:dup_fnn1', '1', '2', 'N2845070O'),
(26, '01-32C6:dup_fnn2', '1', '3', 'N2845070O'),
(27, '01-32D6:no_fnn', '1', '4', ''),
(28, '01-32D6:diff_fnn', '1', '4', 'x123456');
ALTER TABLE `cust_port`
ADD PRIMARY KEY (`id`);
ALTER TABLE `cust_port`
MODIFY `id` smallint(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=29;
I resolved this by splitting the query into 3 inserts: 1st insert for source information to equipment 2nd for destination to equipment 3rd for customer information.
This makes it easy to check if values are set or not. I will also set this up to use transactions.
try{
$insert_cust_port_source="INSERT INTO cust_port (
system_name,
slot_no,
port_no,
port_fnn
) VALUES (
:system_name,
:slot_no,
:port_no,
:port_fnn
)";
$insert_cust_port_dest="INSERT INTO cust_port (
system_name,
slot_no,
port_no,
port_fnn
) VALUES (
:system_name,
:slot_no,
:port_no,
:port_fnn
)";
$insert_cust_conn="INSERT INTO cust_connections (
short_name,
source_fnn,
dest_fnn,
service_type,
ladder_side
) VALUES (
:short_name,
:source_fnn,
:dest_fnn,
:service_type,
:ladder_side
)";
//prepare query for excecution
$database->query($insert_cust_port_source);
//bind the parameters
$database->bind(':system_name', $system_name_source );
$database->bind(':slot_no', $slot_source );
$database->bind(':port_no', $port_source );
$database->bind(':port_fnn', htmlentities($source_fnn) );
// Execute the query
$database->execute();
$database->query($insert_cust_port_dest);
//bind the parameters
$database->bind(':system_name', $system_name_dest );
$database->bind(':slot_no', $slot_dest );
$database->bind(':port_no', $port_dest );
$database->bind(':port_fnn', htmlentities($dest_fnn) );
// Execute the query
$database->execute();
$database->query($insert_cust_conn);
//bind the parameters
$database->bind(':short_name', '$short_name' );
$database->bind(':source_fnn', htmlentities($source_fnn) );
$database->bind(':dest_fnn', htmlentities($dest_fnn) );
$database->bind(':service_type', '$service_type' );
$database->bind(':ladder_side', '$ladder_side' );
// Execute the query
$database->execute();