Inserting into multiple tables
in [General]
|
From: "Tommy Pham" on 11 Apr 2010 02:18 Hi Gary, > -----Original Message----- > From: Gary [mailto:gwpaul(a)ptd.net] > Sent: Saturday, April 10, 2010 2:28 PM > To: php-general(a)lists.php.net > Subject: [PHP] Inserting into multiple tables > > I am experimenting with multiple tables, it is only a test that is my > local > machine only. This is the current code, which does not work , I have > tried > to concatonate the insert statements. I have tried multiple $query > variables, but it is just overwriting itself (only the last one gets > inserted). I also tried writing the $query as an array, which got me an > error message (saying it was expecting a string and I offered an > array). > > Someone point me in the right direction? > > Gary > > <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> > <html xmlns="http://www.w3.org/1999/xhtml"> > <head> > <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> > <title>Untitled Document</title> > </head> > > <body> > > <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post"> > > <label>First Name </label> <input name="fname" type="text" /><br /><br > /> > <label>Last Name </label><input name="lname" type="text" /><br /><br /> > <label>Street Address </label><input name="street" type="text" /><br > /><br > /> > <label>Town </label><input name="town" type="text" /><br /><br /> > <label>State </label><input name="state" type="text" /><br /><br /> > <label>Zip Code</label><input name="zip" type="text" /><br /><br /> > <label>Telephone</label><input name="phone" type="text" /><br /><br /> > <label>Fax</label><input name="fax" type="text" /><br /><br /> > <label>E-Mail</label><input name="email" type="text" /><br /><br /> > <label>Comments</label><br /><textarea name="comments" cols="100" > rows="15"></textarea><br /><br /> > > <input name="submit" type="submit" value="submit" /> > </form> > > <?php > > $fname=($_POST['fname']); > $lname=($_POST['lname']); > $street=($_POST['street']); > $town=($_POST['town']); > $state=($_POST['state']); > $zip=($_POST['zip']); > $phone=($_POST['phone']); > $fax=($_POST['fax']); > $email=($_POST['email']); > $comments=($_POST['comments']); > $REMOTE_ADDR=$_SERVER['REMOTE_ADDR']; > > $dbc=mysqli_connect('localhost','root','','test'); > $query="INSERT INTO address (street, town, state, > zip)"."VALUES('$street','$town','$state','$zip')". > "INSERT INTO comments(comments)"."VALUES('$comments')". > "INSERT INTO > contact(phone,fax,email)"."VALUES('$phone','$fax','$email')". > "INSERT INTO name (fname, lname)"."VALUES('$fname','$lname')"; > > $result = mysqli_query($dbc, $query) > or die('Error querying database.'); > I see 2 problems: 1) your sql statements are not separated by semicolon <- very important when executing multiquery 2) you could try mysql_multi_query http://www.php.net/manual/en/mysqli.multi-query.php Regards, Tommy > mysqli_close($dbc); > > ?> > </body> > </html> > > > > __________ Information from ESET Smart Security, version of virus > signature database 5016 (20100410) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php
From: "Gary" on 11 Apr 2010 11:02 Tommy Thanks for your reply. The code you had read I was trying to concatonate the insert commands without the semicolon at the end....I had also tried using the semicolons on each line...same result. I am reading about the mysqli_multi_query now, so far I am not getting the results. Interestingly, it lead me to the mysqli_store_result(), however it said it returned a false result on the insert command. Thank you for your reply... Gary ""Tommy Pham"" <tommyhp2(a)gmail.com> wrote in message news:013601cad93e$e0bca6a0$a235f3e0$@com... Hi Gary, > -----Original Message----- > From: Gary [mailto:gwpaul(a)ptd.net] > Sent: Saturday, April 10, 2010 2:28 PM > To: php-general(a)lists.php.net > Subject: [PHP] Inserting into multiple tables > > I am experimenting with multiple tables, it is only a test that is my > local > machine only. This is the current code, which does not work , I have > tried > to concatonate the insert statements. I have tried multiple $query > variables, but it is just overwriting itself (only the last one gets > inserted). I also tried writing the $query as an array, which got me an > error message (saying it was expecting a string and I offered an > array). > > Someone point me in the right direction? > > Gary > > <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> > <html xmlns="http://www.w3.org/1999/xhtml"> > <head> > <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> > <title>Untitled Document</title> > </head> > > <body> > > <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post"> > > <label>First Name </label> <input name="fname" type="text" /><br /><br > /> > <label>Last Name </label><input name="lname" type="text" /><br /><br /> > <label>Street Address </label><input name="street" type="text" /><br > /><br > /> > <label>Town </label><input name="town" type="text" /><br /><br /> > <label>State </label><input name="state" type="text" /><br /><br /> > <label>Zip Code</label><input name="zip" type="text" /><br /><br /> > <label>Telephone</label><input name="phone" type="text" /><br /><br /> > <label>Fax</label><input name="fax" type="text" /><br /><br /> > <label>E-Mail</label><input name="email" type="text" /><br /><br /> > <label>Comments</label><br /><textarea name="comments" cols="100" > rows="15"></textarea><br /><br /> > > <input name="submit" type="submit" value="submit" /> > </form> > > <?php > > $fname=($_POST['fname']); > $lname=($_POST['lname']); > $street=($_POST['street']); > $town=($_POST['town']); > $state=($_POST['state']); > $zip=($_POST['zip']); > $phone=($_POST['phone']); > $fax=($_POST['fax']); > $email=($_POST['email']); > $comments=($_POST['comments']); > $REMOTE_ADDR=$_SERVER['REMOTE_ADDR']; > > $dbc=mysqli_connect('localhost','root','','test'); > $query="INSERT INTO address (street, town, state, > zip)"."VALUES('$street','$town','$state','$zip')". > "INSERT INTO comments(comments)"."VALUES('$comments')". > "INSERT INTO > contact(phone,fax,email)"."VALUES('$phone','$fax','$email')". > "INSERT INTO name (fname, lname)"."VALUES('$fname','$lname')"; > > $result = mysqli_query($dbc, $query) > or die('Error querying database.'); > I see 2 problems: 1) your sql statements are not separated by semicolon <- very important when executing multiquery 2) you could try mysql_multi_query http://www.php.net/manual/en/mysqli.multi-query.php Regards, Tommy > mysqli_close($dbc); > > ?> > </body> > </html> > > > > __________ Information from ESET Smart Security, version of virus > signature database 5016 (20100410) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php __________ Information from ESET NOD32 Antivirus, version of virus signature database 5017 (20100411) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 5017 (20100411) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: Nathan Rixham on 11 Apr 2010 14:57 Gary wrote: > Adam > > Thank you for your well thought out response. > > Two points: > > I did not include any anti-injection functions because this was an > experiment for multiple tables, it is on my machine only. > > Since these are php scripts, I dont think anyone will mind (not to mention > this board always provides great answers)., > > However I think I may have answered my question about the importance of > normalization of tables. I have written a number of databases used on > various web sites, however, they all are used as a collection of data from > input forms. > > Most of the information about mutilple tables deals with the retreval of > data from, not inserting into, meaning they are more used for known data > inserted by the database owner/administrator to be retrieved by queries into > the DB. > > Am I on the right track? > I'm unsure if this is of use to you or not (and it has been covered in part already), but here goes: Generally when working with database tables, we normalise, or split information up at natural points where you have a greater than 1-1 relationships between the data items. An example may be a table structure to store User, Blog Post and Comments. With this common example it's very inefficient to store all the information in one table, because it is split naturally in to three. We have three relationships here; all 1-* (meaning "one to many"). 1 User to * Blog Posts 1 Blog Post to * Comments Thus naturally, and on first glance we would have 3 tables: | Table User -------------------------------------- | UserID | Username | Password | ... | Table BlogPost -------------------------------------- | PostID | PostTitle | PostersUserID | ... | Table Comment -------------------------------------- | CommentID | CommentOnPostID | CommentersUserID | ... As you can see from the above, all of the rows (or items) in our tables are linked via IDs to each other. We can further normalise the above tables to take in to account *-* (many to many) type relationships, and to fully separate cross cutting concerns. For instance it may be that a BlogPost has 3 different Users as author(s). aside: cross cutting concerns can be considered as something (in this case a table) trying to handle something which is of no concern to it (in this case the BlogPost table needs to be aware of Users and their IDs). To handle the aforementioned we can introduce something commonly referred to as "link tables", consider: | Table User -------------------------------------- | UserID | Username | Password | ... | Table BlogPost -------------------------------------- | PostID | PostTitle | ... | Table BlogPostAuthors -------------------------------------- | BlogPostID | UserID | The "link table" BlogPostAuthors acts as a many-to-many join table between BlogPosts and Users. Similarly we could introduce the same kind of link table between BlogPosts and Comments, / Users and Comments. In a real system we may even have another two primary tables introduced, Roles and UserRoles, as the system may have multiple Roles (Author, Commenter, Admin etc) and each User may have multiple Roles themselves, in one capacity I am an Admin, in another I'm an Author. (UserRoles may be better considered as Personas?) Ultimately there are many considerations to take in to account, the relationships between types of data, the frequency at which inserts/updates/selects occur, the complexity and speed of each query, and much more. Designing a table structure is different for each job, with different considerations and things to weigh up, generally though normalisation can cater for at least some future scope creep. It's also worth noting that some consider it bad practise to design a system from the storage point upwards, because the application and data should not be constrained by persistence layer features or limitations - which would indicate designing the data model in UML or suchlike and dealing with Objects rather than Tables (then later mapping objects to tables in order to persist them, if choosing a RDBMS as the persistence layer). It may also be worth noting that an EAV model is the ultimate in normalisation and allows all data to be persisted in a single 3 column structure (or 4 if you partition data). I'll save details of this though. Do hope that helps in some way, and if you need any more info just shout. Nathan
From: Paul M Foster on 11 Apr 2010 22:57 On Sun, Apr 11, 2010 at 12:21:28AM -0400, Gary wrote: <snip> > > Most of the information about mutilple tables deals with the retreval of > data from, not inserting into, meaning they are more used for known data > inserted by the database owner/administrator to be retrieved by queries into > the DB. > > Am I on the right track? Yes. The emphasis on multiple tables is mostly to ensure data integrity. It goes back to the beginning of relational databases. And where you have multiple tables, the most difficult task (and the one which takes up the most pages in texts) is queries on those multiple tables. Insertions are considered elementary, and they are typically done one table at a time. You'll notice the syntax for queries is considerably more complex than that for inserts, because the task is more complex. Paul -- Paul M. Foster
From: "Gary" on 12 Apr 2010 11:05 Nathan Thank you for your excellent explanation! One of the reasons I love this board is the vast knowledge that people are willing to share. I believe I understand the importance of normalization, however one of my original questions seems to still stand. If normalization is so important, why is it that the INSERT INTO multiple tables is not a standard command or procedure?. I'm not saying it has to be easy, but it should be well known. I really thought when I first asked the question I was going to get multiple similar answers, or someone was going to look at my script and tell me I omitted some simple puncuatuion (or other simple mistake)...which has not been the case. Even if the answer were "Cant be done", you need to write a separate script for each insert, that would be ok. But I have to think that someone reading this board has accomplished, somehow, writing to separate tables in the same DB. Again, thank you for all the information and your time. Gary "Nathan Rixham" <nrixham(a)gmail.com> wrote in message news:4BC21B88.3090408(a)gmail.com... > Gary wrote: >> Adam >> >> Thank you for your well thought out response. >> >> Two points: >> >> I did not include any anti-injection functions because this was an >> experiment for multiple tables, it is on my machine only. >> >> Since these are php scripts, I dont think anyone will mind (not to >> mention >> this board always provides great answers)., >> >> However I think I may have answered my question about the importance of >> normalization of tables. I have written a number of databases used on >> various web sites, however, they all are used as a collection of data >> from >> input forms. >> >> Most of the information about mutilple tables deals with the retreval of >> data from, not inserting into, meaning they are more used for known data >> inserted by the database owner/administrator to be retrieved by queries >> into >> the DB. >> >> Am I on the right track? >> > > I'm unsure if this is of use to you or not (and it has been covered in > part already), but here goes: > > Generally when working with database tables, we normalise, or split > information up at natural points where you have a greater than 1-1 > relationships between the data items. > > An example may be a table structure to store User, Blog Post and Comments. > > With this common example it's very inefficient to store all the > information in one table, because it is split naturally in to three. > > We have three relationships here; all 1-* (meaning "one to many"). > > 1 User to * Blog Posts > 1 Blog Post to * Comments > > Thus naturally, and on first glance we would have 3 tables: > > | Table User > -------------------------------------- > | UserID | Username | Password | ... > > > | Table BlogPost > -------------------------------------- > | PostID | PostTitle | PostersUserID | ... > > > | Table Comment > -------------------------------------- > | CommentID | CommentOnPostID | CommentersUserID | ... > > > As you can see from the above, all of the rows (or items) in our tables > are linked via IDs to each other. > > We can further normalise the above tables to take in to account *-* > (many to many) type relationships, and to fully separate cross cutting > concerns. For instance it may be that a BlogPost has 3 different Users > as author(s). > > aside: cross cutting concerns can be considered as something (in this > case a table) trying to handle something which is of no concern to it > (in this case the BlogPost table needs to be aware of Users and their > IDs). > > To handle the aforementioned we can introduce something commonly > referred to as "link tables", consider: > > > | Table User > -------------------------------------- > | UserID | Username | Password | ... > > > | Table BlogPost > -------------------------------------- > | PostID | PostTitle | ... > > > | Table BlogPostAuthors > -------------------------------------- > | BlogPostID | UserID | > > > The "link table" BlogPostAuthors acts as a many-to-many join table > between BlogPosts and Users. Similarly we could introduce the same kind > of link table between BlogPosts and Comments, / Users and Comments. > > In a real system we may even have another two primary tables introduced, > Roles and UserRoles, as the system may have multiple Roles (Author, > Commenter, Admin etc) and each User may have multiple Roles themselves, > in one capacity I am an Admin, in another I'm an Author. (UserRoles may > be better considered as Personas?) > > Ultimately there are many considerations to take in to account, the > relationships between types of data, the frequency at which > inserts/updates/selects occur, the complexity and speed of each query, > and much more. > > Designing a table structure is different for each job, with different > considerations and things to weigh up, generally though normalisation > can cater for at least some future scope creep. > > It's also worth noting that some consider it bad practise to design a > system from the storage point upwards, because the application and data > should not be constrained by persistence layer features or limitations - > which would indicate designing the data model in UML or suchlike and > dealing with Objects rather than Tables (then later mapping objects to > tables in order to persist them, if choosing a RDBMS as the persistence > layer). > > It may also be worth noting that an EAV model is the ultimate in > normalisation and allows all data to be persisted in a single 3 column > structure (or 4 if you partition data). I'll save details of this though. > > Do hope that helps in some way, and if you need any more info just shout. > > Nathan > > __________ Information from ESET Smart Security, version of virus > signature database 5021 (20100412) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 5021 (20100412) __________ The message was checked by ESET Smart Security. http://www.eset.com
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: wiki api help needed with php Next: PHP execute very slow : PHP Version 5.2.6 |