insert into multiple tables linked by foreign keys (PHP, MySQL)

Question

sgubu 0 Newbie Poster

11 Years Ago

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 29, 2013 at 04:31 PM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `deeds`
--

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

--
-- Table structure for table `asset`
--

CREATE TABLE IF NOT EXISTS `asset` (
  `assetID` int(11) NOT NULL AUTO_INCREMENT,
  `isAnimal` varchar(255) DEFAULT NULL,
  `creditor(brand)` varchar(255) DEFAULT NULL,
  `items` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `UniqueID` int(11) NOT NULL,
  PRIMARY KEY (`assetID`),
  KEY `fk_Asset_deed1` (`UniqueID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `brand`
--

CREATE TABLE IF NOT EXISTS `brand` (
  `brandID` int(11) NOT NULL,
  `brand` varchar(45) DEFAULT NULL,
  `assetID` int(11) NOT NULL,
  PRIMARY KEY (`brandID`),
  KEY `fk_brand_Asset1` (`assetID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `client`
--

CREATE TABLE IF NOT EXISTS `client` (
  `clientID` int(11) NOT NULL AUTO_INCREMENT,
  `IsCorporate` varchar(255) DEFAULT NULL,
  `salutation` varchar(45) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `middlename` varchar(255) DEFAULT NULL,
  `surname` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `identity_no` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `conveyancer`
--

CREATE TABLE IF NOT EXISTS `conveyancer` (
  `CID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `FID` int(11) NOT NULL,
  PRIMARY KEY (`CID`),
  KEY `fk_conveyancer_firm1` (`FID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `deed`
--

CREATE TABLE IF NOT EXISTS `deed` (
  `UniqueID` int(11) NOT NULL AUTO_INCREMENT,
  `deed_no` int(11) DEFAULT NULL,
  `captured_date` year(4) DEFAULT NULL,
  `NID` int(11) NOT NULL,
  `MID` int(11) NOT NULL,
  `LID` int(11) NOT NULL,
  `FID` int(11) NOT NULL,
  `SID` int(11) NOT NULL,
  `EID` int(11) NOT NULL,
  PRIMARY KEY (`UniqueID`),
  KEY `fk_deed_nature` (`NID`),
  KEY `fk_deed_master_index1` (`MID`),
  KEY `fk_deed_Land_Authority1` (`LID`),
  KEY `fk_deed_firm1` (`FID`),
  KEY `fk_deed_status1` (`SID`),
  KEY `fk_deed_employee1` (`EID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
  `EID` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `surname` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `saltkey` varchar(255) DEFAULT NULL,
  `user_level` varchar(255) DEFAULT NULL,
  `security_question` varchar(255) DEFAULT NULL,
  `security_answer` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`EID`),
  UNIQUE KEY `username_UNIQUE` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `firm`
--

CREATE TABLE IF NOT EXISTS `firm` (
  `FID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`FID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `general_plan`
--

CREATE TABLE IF NOT EXISTS `general_plan` (
  `GID` int(11) NOT NULL AUTO_INCREMENT,
  `dsm_no` varchar(255) DEFAULT NULL,
  `reg_date` date DEFAULT NULL,
  `situate` varchar(255) DEFAULT NULL,
  `surveyor` varchar(255) DEFAULT NULL,
  `surveyor_date` date DEFAULT NULL,
  `SID` int(11) NOT NULL,
  PRIMARY KEY (`GID`),
  KEY `fk_general_plan_status1` (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `land_authority`
--

CREATE TABLE IF NOT EXISTS `land_authority` (
  `LID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `office` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`LID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `master_index`
--

CREATE TABLE IF NOT EXISTS `master_index` (
  `MID` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) DEFAULT NULL,
  `file_no` varchar(255) DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `range` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`MID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `nature`
--

CREATE TABLE IF NOT EXISTS `nature` (
  `NID` int(11) NOT NULL,
  `Bond` varchar(45) DEFAULT NULL,
  `Consent` varchar(45) DEFAULT NULL,
  `Tribal lease` varchar(45) DEFAULT NULL,
  `Tribal grant` varchar(45) DEFAULT NULL,
  `Applications` varchar(45) DEFAULT NULL,
  `Sectional bond` varchar(45) DEFAULT NULL,
  `Sectional title` varchar(45) DEFAULT NULL,
  `Hypothecation Act` varchar(45) DEFAULT NULL,
  `Mining Lease` varchar(45) DEFAULT NULL,
  `CRR` varchar(45) DEFAULT NULL,
  `CRM` varchar(45) DEFAULT NULL,
  `CRST` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`NID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `plot`
--

CREATE TABLE IF NOT EXISTS `plot` (
  `PID` int(11) NOT NULL AUTO_INCREMENT,
  `Lot_no` varchar(255) DEFAULT NULL,
  `situate` varchar(255) DEFAULT NULL,
  `size` varchar(255) DEFAULT NULL,
  `extent` varchar(255) DEFAULT NULL,
  `UniqueID` int(11) NOT NULL,
  `GID` int(11) NOT NULL,
  PRIMARY KEY (`PID`),
  KEY `fk_plot_deed1` (`UniqueID`),
  KEY `fk_plot_general_plan1` (`GID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `status`
--

CREATE TABLE IF NOT EXISTS `status` (
  `SID` int(11) NOT NULL AUTO_INCREMENT,
  `torn` varchar(255) DEFAULT NULL,
  `missing` varchar(255) DEFAULT NULL,
  `worn` varchar(255) DEFAULT NULL,
  `faded` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `transferee`
--

CREATE TABLE IF NOT EXISTS `transferee` (
  `TID` int(11) NOT NULL AUTO_INCREMENT,
  `share` varchar(45) DEFAULT NULL,
  `UniqueID` int(11) NOT NULL,
  `clientID` int(11) NOT NULL,
  PRIMARY KEY (`TID`),
  KEY `fk_transferee_deed1` (`UniqueID`),
  KEY `fk_transferee_client1` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `transferer`
--

CREATE TABLE IF NOT EXISTS `transferer` (
  `TID` int(11) NOT NULL AUTO_INCREMENT,
  `share` varchar(255) DEFAULT NULL,
  `UniqueID` int(11) NOT NULL,
  `clientID` int(11) NOT NULL,
  PRIMARY KEY (`TID`),
  KEY `fk_transferer_deed1` (`UniqueID`),
  KEY `fk_transferer_client1` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `asset`
--
ALTER TABLE `asset`
  ADD CONSTRAINT `fk_Asset_deed1` FOREIGN KEY (`UniqueID`) REFERENCES `deed` (`UniqueID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `brand`
--
ALTER TABLE `brand`
  ADD CONSTRAINT `fk_brand_Asset1` FOREIGN KEY (`assetID`) REFERENCES `asset` (`assetID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `conveyancer`
--
ALTER TABLE `conveyancer`
  ADD CONSTRAINT `fk_conveyancer_firm1` FOREIGN KEY (`FID`) REFERENCES `firm` (`FID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `deed`
--
ALTER TABLE `deed`
  ADD CONSTRAINT `fk_deed_nature` FOREIGN KEY (`NID`) REFERENCES `nature` (`NID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_deed_master_index1` FOREIGN KEY (`MID`) REFERENCES `master_index` (`MID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_deed_Land_Authority1` FOREIGN KEY (`LID`) REFERENCES `land_authority` (`LID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_deed_firm1` FOREIGN KEY (`FID`) REFERENCES `firm` (`FID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_deed_status1` FOREIGN KEY (`SID`) REFERENCES `status` (`SID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_deed_employee1` FOREIGN KEY (`EID`) REFERENCES `employee` (`EID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `general_plan`
--
ALTER TABLE `general_plan`
  ADD CONSTRAINT `fk_general_plan_status1` FOREIGN KEY (`SID`) REFERENCES `status` (`SID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `plot`
--
ALTER TABLE `plot`
  ADD CONSTRAINT `fk_plot_deed1` FOREIGN KEY (`UniqueID`) REFERENCES `deed` (`UniqueID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_plot_general_plan1` FOREIGN KEY (`GID`) REFERENCES `general_plan` (`GID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `transferee`
--
ALTER TABLE `transferee`
  ADD CONSTRAINT `fk_transferee_deed1` FOREIGN KEY (`UniqueID`) REFERENCES `deed` (`UniqueID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_transferee_client1` FOREIGN KEY (`clientID`) REFERENCES `client` (`clientID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `transferer`
--
ALTER TABLE `transferer`
  ADD CONSTRAINT `fk_transferer_deed1` FOREIGN KEY (`UniqueID`) REFERENCES `deed` (`UniqueID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_transferer_client1` FOREIGN KEY (`clientID`) REFERENCES `client` (`clientID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

my database and i want a php code that can store into multiple tables

mysql php

Edited 11 Years Ago by Dani because: Formatting fixed

2 Contributors
3 Replies
2K Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by diafol

diafol

11 Years Ago

You've got an sql dump. Fine.

my database and i want a php code that can store into multiple tables

So? Are you going to give us an example of what you need? have a look at the php sticky:
http://www.daniweb.com/web-development/php/threads/435023/read-this-before-posting-a-question

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sgubu 0 Newbie Poster · Answer 1 · 2013-06-06T08:08:04+00:00

my add deed class 
<?
 include('db.php');
 require_once('functions/functions.php');
 $functions = new funtions();

    $year = $_POST['year'];
    $fileno = $_POST['file_no'];
    $deed_no = $_POST['deed_no'];

    $MID= $functions->getMID($year,$fileno);
    $query = "INSERT INTO 'deed' ('deed_no','MID','LID','FID','SID','EID') VALUES ('$deed_no','$MID','$LID','$FID','$SID','$EID')";
    mysql_query($query) or die(mysql_error());

?>

my function class 
<?
function getMID($fileno, $year)
{
    $query = "SELECT MID from 'master_index' WHERE file_no = '$fileno' AND year = '$year'";
        //run query get results and row

        $MID = $row['MID'];

        return $MID;
}

?>

i want to add to deed table with other foreign keys

diafol · Answer 2 · 2013-06-06T17:46:51+00:00

Well, your code looks OK to me. You're selecting an index from master_index based on year/fileno and then using that value to insert into deeds table. By means of other indexes you're referring to LID, FID etc?

I have no idea how those bits of data should be retrieved in order to insert them. Could you be more specific?