Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: HELP: insert data into 4 mysql tables

  1. #11
    Join Date
    Feb 2006
    Posts
    4,242
    Rep Power
    0

    Default

    I set a info text to indicate the phone number format to the user.

    This works but I'm getting many records duplicating when I select all from the 4 table
    i.e 'instead of one record being inserted, the record insert about 10 time'

    PHP Code:
    $query  = ("INSERT INTO name (fname,lname,..) VALUES ("Mary","Blige",..)") or die(mysql_error());  
    $query = ("INSERT INTO address (town,parish,..) VALUES ("Mona","StAndrew",..)") or die(mysql_error());  
    $query = ("INSERT INTO phone (cell,fax,..) VALUES (8760001234,8769230002,..)") or die(mysql_error());  
    $query = ("INSERT INTO computer (brand,type,..) VALUES ("Dell","Laptop",..)") or die(mysql_error());  
      
    mysql_query($query)or die(mysql_error()); 
    Last edited by Utech22; Aug 1, 2007 at 07:39 PM.
    |--- www.RealJamaicaEstate.com ™ ---|
    Invest small = small returns [micro enterprise] | Invest Big = returns Big [macro enterprise]
    --- www.fashionsJAMAICA.com ™ -|- www.ChampsJamaica.com

  2. #12
    Join Date
    Sep 2004
    Posts
    356
    Rep Power
    0

    Default

    Quote Originally Posted by Utech22 View Post
    I set a info text to indicate the phone number format to the user.

    This works but I'm getting many records duplicating when I select all from the 4 table
    i.e 'instead of one record being inserted, the record insert about 10 time'

    PHP Code:
    $query  = ("INSERT INTO name (fname,lname,..) VALUES ("Mary","Blige",..)") or die(mysql_error());  
    $query = ("INSERT INTO address (town,parish,..) VALUES ("Mona","StAndrew",..)") or die(mysql_error());  
    $query = ("INSERT INTO phone (cell,fax,..) VALUES (8760001234,8769230002,..)") or die(mysql_error());  
    $query = ("INSERT INTO computer (brand,type,..) VALUES ("Dell","Laptop",..)") or die(mysql_error());  
      
    mysql_query($query)or die(mysql_error()); 
    Check out www.sqlzoo.net
    you cant go wrong

  3. #13
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    Quote Originally Posted by Utech22 View Post
    I set a info text to indicate the phone number format to the user.

    This works but I'm getting many records duplicating when I select all from the 4 table
    i.e 'instead of one record being inserted, the record insert about 10 time'

    PHP Code:
    $query  = ("INSERT INTO name (fname,lname,..) VALUES ("Mary","Blige",..)") or die(mysql_error());  
    $query = ("INSERT INTO address (town,parish,..) VALUES ("Mona","StAndrew",..)") or die(mysql_error());  
    $query = ("INSERT INTO phone (cell,fax,..) VALUES (8760001234,8769230002,..)") or die(mysql_error());  
    $query = ("INSERT INTO computer (brand,type,..) VALUES ("Dell","Laptop",..)") or die(mysql_error());  
      
    mysql_query($query)or die(mysql_error()); 
    maybe your problem is not the insert but the query to retrieve it again.
    is it that you actually see multiple entries when you look at the database with a MySQL database tool, or when you query it you see multiple results.

    in general, the idea of splitting data across multiple tables is called partitioning, for your implementation - horizontal partitioning to be precise. in order to put the piecewise data back together as one record there must be a linking field, a key. then you simply join all the partitioned tables on the key field (normally associated as foreign key references).

    lets assume you have four tables to store the complete client information:
    name, address, phone, computer, all this information is for a client, so you keep a reference to the client key (client_id) in each of the related tables. so id assume you have references in the other tables :

    Code:
    name (client_id,fname,lname,..) 
    address (client_id,town,parish,..) 
    phone (client_id,cell,fax,..) 
    computer (client_id,brand,type,..)
    if you do it like that then you can only have one computer, one address,one phone number list, per name (foreign key = primary key) . so when you query you will get one row for each client... no duplicates.

    you can choose the alternative

    Code:
    name (client_id,fname,lname,..) 
    address (client_id,town,parish,..) 
    phone (client_id,cell,fax,..) 
    computer (client_id,brand,type,..)
    if you do it like that then you can only have many computer, many address,many phone number list, per name (primary key on each table is independent of the client_id foreign key) . so when you do a join query you will (seem to) get duplicates for each additional record.

    ex.
    Code:
    name		- 1 - one name
    address		- 2 - postal address and alternative address
    phone		- 1 - one list of contact info
    computer	- 2 - home PC and laptop
    number of records for the client in a join = 1 * 2 * 1 * 2 = 4

    will add more to this.
    Last edited by icymint3; Aug 3, 2007 at 12:42 PM.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  4. #14
    Join Date
    Feb 2006
    Posts
    4,242
    Rep Power
    0

    Default

    Thank for the info so far. Let I modify to a more exact example
    PHP Code:
    $id $_POST['id']; //primary key: name table, foriegn key: other tables  
    $fname $_POST['fname'];
    $lname $_POST['lname'];
    $town $_POST['town'];
    $parish $_POST['parish'];
    $cell $_POST['cell'];
    $fax $_POST['fax'];
    $brand $_POST['brand'];
    $type $_POST['type'];

    $query  = ("INSERT INTO name (id,fname,lname,..) VALUES ('$id','$fname','$lname',..)") or die(mysql_error());  
    $query = ("INSERT INTO address (id,town,parish,..) VALUES ($id','$town','$parish',..)") or die(mysql_error());  
    $query = ("INSERT INTO phone (id,cell,fax,..) VALUES ('$id','$cell','$fax',..)") or die(mysql_error());  
    $query = ("INSERT INTO computer (id,brand,type,..) VALUES ('$id','$brand','$type',..)") or die(mysql_error());  
      
    mysql_query($query)or die(mysql_error()); 
    This is what I have for the query:
    PHP Code:
    $query mysql_query('SELECT * FROM name, address, phone, computer')or die (mysql_error());
    while (
    $result mysql_fetch_array($query))
        {
                          echo 
    $result['fname'];
                          
    //other echo statements
                 

    Last edited by Utech22; Aug 4, 2007 at 06:50 AM.
    |--- www.RealJamaicaEstate.com ™ ---|
    Invest small = small returns [micro enterprise] | Invest Big = returns Big [macro enterprise]
    --- www.fashionsJAMAICA.com ™ -|- www.ChampsJamaica.com

  5. #15
    Join Date
    Feb 2006
    Posts
    4,242
    Rep Power
    0

    Default

    Is this query statement the correct one or ... /inner join is needed/???
    PHP Code:
    $query mysql_query('SELECT * FROM name, address, phone, computer WHERE name.id = address.id AND name.id = phone.id AND name.id = computer.id')or die (mysql_error()); 
    while (
    $result mysql_fetch_array($query)) 
        { 
                          echo 
    $result['fname']; 
                          
    //other echo statements 
         

    Last edited by Utech22; Aug 6, 2007 at 06:47 PM.
    |--- www.RealJamaicaEstate.com ™ ---|
    Invest small = small returns [micro enterprise] | Invest Big = returns Big [macro enterprise]
    --- www.fashionsJAMAICA.com ™ -|- www.ChampsJamaica.com

  6. #16
    Join Date
    Feb 2006
    Posts
    4,242
    Rep Power
    0

    Default

    Any more help as yet ???
    |--- www.RealJamaicaEstate.com ™ ---|
    Invest small = small returns [micro enterprise] | Invest Big = returns Big [macro enterprise]
    --- www.fashionsJAMAICA.com ™ -|- www.ChampsJamaica.com

  7. #17
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    PHP Code:
    $query mysql_query('SELECT * FROM name, address, phone, computer WHERE name.id = address.id AND name.id = phone.id AND name.id = computer.id')or die (mysql_error()); 
    while (
    $result mysql_fetch_array($query)) 
        { 
                          echo 
    $result['fname']; 
                          
    //other echo statements 
         

    yes the query looks correct...

    just one thing... if you need a particular record back you need to add ' AND name.id = $someRefId', but other than that ur query looks correct.

    is there still a problem?
    Last edited by icymint3; Aug 7, 2007 at 12:04 PM.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  8. #18
    Join Date
    Feb 2006
    Posts
    4,242
    Rep Power
    0

    Default

    I am going to digg some more info on inner join;
    THANKS
    |--- www.RealJamaicaEstate.com ™ ---|
    Invest small = small returns [micro enterprise] | Invest Big = returns Big [macro enterprise]
    --- www.fashionsJAMAICA.com ™ -|- www.ChampsJamaica.com

  9. #19
    Join Date
    Feb 2006
    Posts
    4,242
    Rep Power
    0

    Post

    This INSERT statement in ok.
    PHP Code:
    $id $_POST['id']; //primary key: name table, foriegn key: other tables   
    $fname $_POST['fname']; 
    $lname $_POST['lname']; 
    $town $_POST['town']; 
    $parish $_POST['parish']; 
    $cell $_POST['cell']; 
    $fax $_POST['fax']; 
    $brand $_POST['brand']; 
    $type $_POST['type']; 

    $query  = ("INSERT INTO name (id,fname,lname,..) VALUES ('$id','$fname','$lname',..)") or die(mysql_error());   
    $query1 = ("INSERT INTO address (id,town,parish,..) VALUES ($id','$town','$parish',..)") or die(mysql_error());   
    $query2 = ("INSERT INTO phone (id,cell,fax,..) VALUES ('$id','$cell','$fax',..)") or die(mysql_error());   
    $query3 = ("INSERT INTO computer (id,brand,type,..) VALUES ('$id','$brand','$type',..)") or die(mysql_error());   
       
    mysql_query($query)or die(mysql_error()); 
    mysql_query($query1)or die(mysql_error()); 
    mysql_query($query2)or die(mysql_error()); 
    mysql_query($query3)or die(mysql_error()); 
    It my query that is causing the problem.
    Any HELP WITH THE QUERY??
    Last edited by Utech22; Aug 9, 2007 at 01:41 PM.
    |--- www.RealJamaicaEstate.com ™ ---|
    Invest small = small returns [micro enterprise] | Invest Big = returns Big [macro enterprise]
    --- www.fashionsJAMAICA.com ™ -|- www.ChampsJamaica.com

  10. #20
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    if thats the exact query you're using... then its the problem. you have ellipsis in it.

    if thats not the exact code, then post it, that would be most helpful.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •