SQL Insert values to NORMALIZED DB

Question

This is tables:

CREATE TABLE IF NOT EXISTS `dvlaInfoBasic` (
  `id_uDInfoB` int(11) NOT NULL AUTO_INCREMENT,
  `make` varchar(50) NOT NULL,
  `model` varchar(50) NOT NULL,
  `twelveMonthRate` varchar(50) NOT NULL,
  `yearOfManufacture` varchar(50) NOT NULL,
  `cylinderCapacity` varchar(50) NOT NULL,
  `dateofFirstRegistration` varchar(50) NOT NULL,
  `co2Emissions` varchar(50) NOT NULL,
  `fuelType` varchar(50) NOT NULL,
  `colour` varchar(50) NOT NULL,
  `mot` varchar(50) NOT NULL,
  `motDetails` varchar(50) NOT NULL,
  `vin` varchar(50) NOT NULL,
  `transmission` varchar(50) NOT NULL,
  PRIMARY KEY (`id_uDInfoB`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

-- --------------------------------------------------------

--
-- Table structure for table `dvlaInfoMore`
--

CREATE TABLE IF NOT EXISTS `dvlaInfoMore` (
  `id_uDInfoM` int(11) NOT NULL AUTO_INCREMENT,
  `sixMonthRate` varchar(50) NOT NULL,
  `wheelPlan` varchar(50) NOT NULL,
  `revenueWeight` varchar(50) NOT NULL,
  `typeApproval` varchar(50) NOT NULL,
  `taxStatus` varchar(50) NOT NULL,
  `taxed` varchar(50) NOT NULL,
  `taxDetails` varchar(50) NOT NULL,
  PRIMARY KEY (`id_uDInfoM`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

-- --------------------------------------------------------

--
-- Table structure for table `subscription`
--

CREATE TABLE IF NOT EXISTS `subscription` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(25) NOT NULL,
  `carBSR` varchar(11) NOT NULL,
  `carMake` varchar(25) NOT NULL,
  `carYearFrom` int(4) NOT NULL,
  `carYearTo` int(4) NOT NULL,
  `carTransmissionType` varchar(11) NOT NULL,
  `carFuelType` varchar(11) NOT NULL,
  `carTaxed` varchar(5) NOT NULL,
  `carMot` varchar(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;

-- --------------------------------------------------------

--
-- Table structure for table `userBasic`
--

CREATE TABLE IF NOT EXISTS `userBasic` (
  `id_uB` int(11) NOT NULL AUTO_INCREMENT,
  `userNameG` varchar(50) NOT NULL,
  `userEmailG` varchar(50) NOT NULL,
  PRIMARY KEY (`id_uB`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

-- --------------------------------------------------------

--
-- Table structure for table `userChioce`
--

CREATE TABLE IF NOT EXISTS `userChioce` (
  `id_uChoice` int(11) NOT NULL AUTO_INCREMENT,
  `doWithCar` varchar(10) NOT NULL,
  `plateNumber` varchar(20) NOT NULL,
  PRIMARY KEY (`id_uChoice`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

-- --------------------------------------------------------

--
-- Table structure for table `userLocation`
--

CREATE TABLE IF NOT EXISTS `userLocation` (
  `id_uLoc` int(11) NOT NULL AUTO_INCREMENT,
  `latitude` varchar(50) NOT NULL,
  `longitude` varchar(50) NOT NULL,
  `postCode` varchar(10) NOT NULL,
  PRIMARY KEY (`id_uLoc`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

-- --------------------------------------------------------

--
-- Table structure for table `userMain`
--

CREATE TABLE IF NOT EXISTS `userMain` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userIdG` int(50) NOT NULL,
  `id_uB` int(11) NOT NULL,
  `id_uM` int(11) NOT NULL,
  `id_uDInfoB` int(11) NOT NULL,
  `id_uDInfoM` int(11) NOT NULL,
  `id_uChoice` int(11) NOT NULL,
  `id_uLoc` int(11) NOT NULL,
  `id_uStat` int(11) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `id_uB` (`id_uB`),
  KEY `id_uB_2` (`id_uB`),
  KEY `id_uM` (`id_uM`),
  KEY `id_uDInfoB` (`id_uDInfoB`),
  KEY `id_uDInfoM` (`id_uDInfoM`),
  KEY `id_uChoice` (`id_uChoice`),
  KEY `id_uLoc` (`id_uLoc`),
  KEY `id_uStat` (`id_uStat`),
  KEY `id_uStat_2` (`id_uStat`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `userMore`
--

CREATE TABLE IF NOT EXISTS `userMore` (
  `id_uM` int(11) NOT NULL AUTO_INCREMENT,
  `userGenderG` tinyint(1) NOT NULL DEFAULT '9',
  `userAboutG` varchar(255) NOT NULL,
  `userBirthdayG` varchar(50) NOT NULL,
  `userLanguageG` varchar(50) NOT NULL,
  PRIMARY KEY (`id_uM`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;


-- --------------------------------------------------------

--
-- Table structure for table `userStatus`
--

CREATE TABLE IF NOT EXISTS `userStatus` (
  `id_uStat` int(11) NOT NULL AUTO_INCREMENT,
  `termCheck` varchar(3) DEFAULT 'tes',
  PRIMARY KEY (`id_uStat`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;


--
-- Constraints for dumped tables
--

--
-- Constraints for table `userMain`
--
ALTER TABLE `userMain`
  ADD CONSTRAINT `userMain_ibfk_1` FOREIGN KEY (`id_uB`) REFERENCES `userBasic` (`id_uB`),
  ADD CONSTRAINT `userMain_ibfk_2` FOREIGN KEY (`id_uM`) REFERENCES `userMore` (`id_uM`),
  ADD CONSTRAINT `userMain_ibfk_3` FOREIGN KEY (`id_uDInfoB`) REFERENCES `dvlaInfoBasic` (`id_uDInfoB`),
  ADD CONSTRAINT `userMain_ibfk_4` FOREIGN KEY (`id_uDInfoM`) REFERENCES `dvlaInfoMore` (`id_uDInfoM`),
  ADD CONSTRAINT `userMain_ibfk_5` FOREIGN KEY (`id_uChoice`) REFERENCES `userChioce` (`id_uChoice`),
  ADD CONSTRAINT `userMain_ibfk_6` FOREIGN KEY (`id_uLoc`) REFERENCES `userLocation` (`id_uLoc`),
  ADD CONSTRAINT `userMain_ibfk_7` FOREIGN KEY (`id_uStat`) REFERENCES `userStatus` (`id_uStat`);

And this is how I insert data:

<?php

if($_SERVER["REQUEST_METHOD"]=="POST"){
    require 'connection.php';
    createStudent();
}


function createstudent()
{
    global $connect;

        $userIdG= $_POST["userIdG"];        
        $userNameG= $_POST["userNameG"];
        $userEmailG= $_POST["userEmailG"];  
    $plateNumber = $_POST["plateNumber"];
        $recAccuracy= $_POST["recAccuracy"];
    $latitude = $_POST["latitude"];
    $longitude= $_POST["longitude"];
    $postCode = $_POST["postCode"];
    $doWithCar = $_POST["doWithCar"];
    $make= $_POST["make"];
    $model= $_POST["model"];
    $sixMonthRate= $_POST["sixMonthRate"];
    $twelveMonthRate= $_POST["twelveMonthRate"];
    $dateofFirstRegistration= $_POST["dateofFirstRegistration"];
    $yearOfManufacture= $_POST["yearOfManufacture"];
    $cylinderCapacity= $_POST["cylinderCapacity"];
    $co2Emissions= $_POST["co2Emissions"];
    $fuelType = $_POST["fuelType"];
    $taxStatus = $_POST["taxStatus"];
    $colour= $_POST["colour"];
    $typeApproval= $_POST["typeApproval"];
    $wheelPlan = $_POST["wheelPlan"];
    $revenueWeight= $_POST["revenueWeight"];
    $taxDetails= $_POST["taxDetails"];
    $motDetails= $_POST["motDetails"];
    $taxed= $_POST["taxed"];
    $mot= $_POST["mot"];
    $vin= $_POST["vin"];
    $transmission = $_POST["transmission"];


$query = "Insert into userStatus (termCheck)
values ('$termCheck');";

$query .= "Insert into userLocation (latitude, longitude, postCode)
values ('$latitude', '$longitude', '$postCode');";

$query .= "Insert into userChioce (doWithCar, plateNumber)
values ('$doWithCar', '$plateNumber');";

$query .= "Insert into dvlaInfoMore (sixMonthRate, wheelPlan, revenueWeight, typeApproval, taxStatus, taxed, taxDetails)
values ('$sixMonthRate', '$wheelPlan', '$revenueWeight', '$typeApproval', '$taxStatus', '$taxed', '$taxDetails');";

$query .= "Insert into dvlaInfoBasic (make, model, twelveMonthRate, yearOfManufacture, cylinderCapacity, dateofFirstRegistration, co2Emissions, fuelType, colour, mot, motDetails, vin, transmission)
values ('$make', '$model', '$twelveMonthRate', '$yearOfManufacture', '$cylinderCapacity', '$dateofFirstRegistration', '$co2Emissions', '$fuelType', '$colour', '$mot', '$motDetails', '$vin', '$transmission');";

$query .= "Insert into userMore (userGenderG, userAboutG, userBirthdayG, userLanguageG)
values ('$userGenderG', '$userAboutG', '$userBirthdayG', '$userLanguageG');";

$query .= "Insert into userBasic (userNameG, userEmailG)
values ('$userNameG', '$userEmailG');";

$query .= "Insert into userMain (userIdG)
values ('$userIdG')";


if ($connect->multi_query($query) === TRUE) {
    echo "New records created successfully";
} else {
    echo "Error: " . $query . "<br>" . $connect->error;
}

$connect->close();

}

?>

EVERYTHING is WORKING ... data inserting to each table BUT do not insert to table userMain or if I manage to insert, happens only once. By processing this PHP query, all table keep updating and inseting the data except userMain

This is another version of userMain which I user and failed:

$query .= "Insert into userMain (userIdG, id_uB, id_uM, id_uDInfoB, id_uDInfoM, id_uChoice, id_uLoc, id_uStat)
values ('$userIdG', userBasic.id_uB, userMore.id_uM, dvlaInfoBasic.id_uDInfoB, dvlaInfoMore.id_uDInfoM, userChioce.id_uChoice, userLocation.id_uLoc, userStatus.id_uStat)";

why userMain only have one column for insert? their are a lot of not null columns? — Dr. Stitch, Apr 14 '16 at 02:49
you don't call a column value like that, userBasic.id_uB object is non-existing i believe? — Dr. Stitch, Apr 14 '16 at 02:54
last time when I managed to insert it, was 6, all ids was 6 which in this situation was correct. But it's keep adding all the rest tables accept userMain — , Apr 14 '16 at 02:55
I called them as well like this, ('$userIdG', (SELECT id_uB from userBasic),(SELECT id_uM from userMore),(SELECT id_uDInfoB from dvlaInfoBasic),(SELECT id_uDInfoM from dvlaInfoMore),(SELECT id_uChoice from userChioce),(SELECT id_uLoc from userLocation),(SELECT id_uStat from userStatus) — , Apr 14 '16 at 02:55
SELECT max(id_uDInfoB) from dvlaInfoBasic, you're inserting one row only you're queries will return multiple rows. — Dr. Stitch, Apr 14 '16 at 02:56

score 0 · Accepted Answer · edited May 23 '17 at 10:28

0

Make sure to populate all the required columns (not null) in the userMain table.

In getting the latest id store for each table use max to get the latest id:

SELECT max(id_uDInfoB) from dvlaInfoBasic

For multiple transaction, its better to search about the @@identity or IDENT_CURRENT('tableName') topics (Best way to get identity of inserted row?).

Thanks.

edited May 23 '17 at 10:28

Community

1
1

answered Apr 14 '16 at 03:14

Dr. Stitch

908
6
15

SQL Insert values to NORMALIZED DB

1 Answers1