Inserting into multiple tables
in [General]
|
From: "Gary" on 10 Apr 2010 17:27 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.'); 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
From: Adam Richardson on 10 Apr 2010 17:57 On Sat, Apr 10, 2010 at 5:27 PM, Gary <gwpaul(a)ptd.net> wrote: > 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? > > I'm not aware of any multi-table insert syntax: http://lists.mysql.com/mysql/171921 You'd have to perform 4 separate mysql_query() calls to accomplish what you're looking for (one for each insert.) That said, I'd recommend using a transaction so you make sure all of the inserts succeed or fail together (it would cause issues down the road if one table insert worked and another failed.) Additionally, I'd recommend using prepared statements to avoid SQL injection (I'd mention validation your POST data, too ;) See the tutorial below: http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html Adam -- Nephtali: PHP web framework that functions beautifully http://nephtaliproject.com
From: "Gary" on 10 Apr 2010 19:56 Adam Thanks for your reply, but I think I am totally confused. Not so much by your answer (although I admit I did not get it to work yet), but by the entire multiple table issue. Several books I use talk about the importance of using multiple tables, yet are thin on the insert issue. Searching the web I have read several answers saying you simply cant insert into multiple tables at once. So if multiple tables is such an important issue, why is there no insert solution. What am I missing? Gary "Adam Richardson" <simpleshot(a)gmail.com> wrote in message news:x2ge4d8ea9d1004101457r62b7c0b8uc3673353c3758394(a)mail.gmail.com... > On Sat, Apr 10, 2010 at 5:27 PM, Gary <gwpaul(a)ptd.net> wrote: > >> 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? >> >> > I'm not aware of any multi-table insert syntax: > http://lists.mysql.com/mysql/171921 > > You'd have to perform 4 separate mysql_query() calls to accomplish what > you're looking for (one for each insert.) > > That said, I'd recommend using a transaction so you make sure all of the > inserts succeed or fail together (it would cause issues down the road if > one > table insert worked and another failed.) Additionally, I'd recommend > using > prepared statements to avoid SQL injection (I'd mention validation your > POST > data, too ;) > > See the tutorial below: > http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html > > Adam > > -- > Nephtali: PHP web framework that functions beautifully > http://nephtaliproject.com > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 5016 (20100410) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 5016 (20100410) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: Adam Richardson on 10 Apr 2010 23:09 On Sat, Apr 10, 2010 at 7:56 PM, Gary <gwp(a)paulgdesigns.com> wrote: > Adam > > Thanks for your reply, but I think I am totally confused. Not so much by > your answer (although I admit I did not get it to work yet), but by the > entire multiple table issue. Several books I use talk about the importance > of using multiple tables, yet are thin on the insert issue. Searching the > web I have read several answers saying you simply cant insert into multiple > tables at once. > > So if multiple tables is such an important issue, why is there no insert > solution. What am I missing? > > Gary > > > "Adam Richardson" <simpleshot(a)gmail.com> wrote in message > news:x2ge4d8ea9d1004101457r62b7c0b8uc3673353c3758394(a)mail.gmail.com... > >> On Sat, Apr 10, 2010 at 5:27 PM, Gary <gwpaul(a)ptd.net> wrote: >> >> 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? >>> >>> >>> I'm not aware of any multi-table insert syntax: >> http://lists.mysql.com/mysql/171921 >> >> You'd have to perform 4 separate mysql_query() calls to accomplish what >> you're looking for (one for each insert.) >> >> That said, I'd recommend using a transaction so you make sure all of the >> inserts succeed or fail together (it would cause issues down the road if >> one >> table insert worked and another failed.) Additionally, I'd recommend >> using >> prepared statements to avoid SQL injection (I'd mention validation your >> POST >> data, too ;) >> >> See the tutorial below: >> http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html >> >> Adam >> >> -- >> Nephtali: PHP web framework that functions beautifully >> http://nephtaliproject.com >> >> >> >> __________ Information from ESET NOD32 Antivirus, version of virus >> signature database 5016 (20100410) __________ >> >> The message was checked by ESET NOD32 Antivirus. >> >> http://www.eset.com >> >> >> > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 5016 (20100410) __________ > > The message was checked by ESET NOD32 Antivirus. > > > http://www.eset.com > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Hi Gary, The benefit of breaking up the data across multiple tables (when the data has certain characteristics) is that you can better protect the integrity of the data through what's called normalization ( http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html.) If you have a database in which you'd like to store employees, perhaps one of the fields would be the address of the office building they work in. Now, you could build the database so it uses one table to store all of the data (first name, last name, office building address, etc.) Up front, this might seem reasonable, but what happens when employees in office building A move to a new location? In this table, you'd have to carefully replace the address for each employee with the new address. How will you find all of the employees that need to have the address updated? Did you type in the address exactly the same (down to even the spaces in the words) in every row? It's very possible that this scheme will eventually lead to errors. However, the other approach would be to break this database up into 2 tables, one for the core employee data (first name, last name, etc.), and one table for office building locations because there is redundancy. Now the employee table wouldn't actually contain the actual office building address, it would contain a reference to the row in the office building address table. When you want to change the address for an office building, you merely change one row and it impacts every employee avoiding the potential pitfalls of the first example using 1 table for everything. That said, just because this scheme better protects the integrity of the data doesn't mean it's easier. Sometimes your inserts will have to impact multiple tables, and they will all have to be performed separately. However, after inserting OR updating your data, you will be able to sleep at night knowing that your data properly reflects the intentions of the changes you've made. Now, looking at your example, I notice very little redundancy. That is, unless someone can submit comments again later on, all of the fields would be unique to the particular individual submitting the form. This data could easily be stored in the same table (just include an id field as a primary key), and there shouldn't be any issues. I'd recommend reading a book like that below that works through PHP AND MySQL in a very nice manner: http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/ref=sr_1_1?ie=UTF8&s=books&qid=1270953434&sr=8-1 I probably won't provide much follow-up on MySQL database normalization in follow-up emails so-as not to upset others on the list as this list focuses on PHP, but I hope I helped you get started on the right track and avoid becoming discouraged. It takes some time to get it to "click", but once it does, DB development will be just as fun and easy as PHP ;) Adam -- Nephtali: PHP web framework that functions beautifully http://nephtaliproject.com
From: "Gary" on 11 Apr 2010 00:21 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? Again, thank you for your replies. Gary "Adam Richardson" <simpleshot(a)gmail.com> wrote in message news:i2je4d8ea9d1004102009r963ef9f3i930a2e757b5ce24a(a)mail.gmail.com... > On Sat, Apr 10, 2010 at 7:56 PM, Gary <gwp(a)paulgdesigns.com> wrote: > >> Adam >> >> Thanks for your reply, but I think I am totally confused. Not so much by >> your answer (although I admit I did not get it to work yet), but by the >> entire multiple table issue. Several books I use talk about the >> importance >> of using multiple tables, yet are thin on the insert issue. Searching >> the >> web I have read several answers saying you simply cant insert into >> multiple >> tables at once. >> >> So if multiple tables is such an important issue, why is there no insert >> solution. What am I missing? >> >> Gary >> >> >> "Adam Richardson" <simpleshot(a)gmail.com> wrote in message >> news:x2ge4d8ea9d1004101457r62b7c0b8uc3673353c3758394(a)mail.gmail.com... >> >>> On Sat, Apr 10, 2010 at 5:27 PM, Gary <gwpaul(a)ptd.net> wrote: >>> >>> 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? >>>> >>>> >>>> I'm not aware of any multi-table insert syntax: >>> http://lists.mysql.com/mysql/171921 >>> >>> You'd have to perform 4 separate mysql_query() calls to accomplish what >>> you're looking for (one for each insert.) >>> >>> That said, I'd recommend using a transaction so you make sure all of the >>> inserts succeed or fail together (it would cause issues down the road if >>> one >>> table insert worked and another failed.) Additionally, I'd recommend >>> using >>> prepared statements to avoid SQL injection (I'd mention validation your >>> POST >>> data, too ;) >>> >>> See the tutorial below: >>> http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html >>> >>> Adam >>> >>> -- >>> Nephtali: PHP web framework that functions beautifully >>> http://nephtaliproject.com >>> >>> >>> >>> __________ Information from ESET NOD32 Antivirus, version of virus >>> signature database 5016 (20100410) __________ >>> >>> The message was checked by ESET NOD32 Antivirus. >>> >>> http://www.eset.com >>> >>> >>> >> >> __________ Information from ESET NOD32 Antivirus, version of virus >> signature database 5016 (20100410) __________ >> >> The message was checked by ESET NOD32 Antivirus. >> >> >> http://www.eset.com >> >> >> >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > Hi Gary, > > The benefit of breaking up the data across multiple tables (when the data > has certain characteristics) is that you can better protect the integrity > of > the data through what's called normalization ( > http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html.) > > If you have a database in which you'd like to store employees, perhaps one > of the fields would be the address of the office building they work in. > Now, you could build the database so it uses one table to store all of the > data (first name, last name, office building address, etc.) Up front, > this > might seem reasonable, but what happens when employees in office building > A > move to a new location? In this table, you'd have to carefully replace > the > address for each employee with the new address. > > How will you find all of the employees that need to have the address > updated? Did you type in the address exactly the same (down to even the > spaces in the words) in every row? It's very possible that this scheme > will > eventually lead to errors. > > However, the other approach would be to break this database up into 2 > tables, one for the core employee data (first name, last name, etc.), and > one table for office building locations because there is redundancy. Now > the employee table wouldn't actually contain the actual office building > address, it would contain a reference to the row in the office building > address table. When you want to change the address for an office > building, > you merely change one row and it impacts every employee avoiding the > potential pitfalls of the first example using 1 table for everything. > > That said, just because this scheme better protects the integrity of the > data doesn't mean it's easier. Sometimes your inserts will have to impact > multiple tables, and they will all have to be performed separately. > However, after inserting OR updating your data, you will be able to sleep > at night knowing that your data properly reflects the intentions of the > changes you've made. > > Now, looking at your example, I notice very little redundancy. That is, > unless someone can submit comments again later on, all of the fields would > be unique to the particular individual submitting the form. This data > could > easily be stored in the same table (just include an id field as a primary > key), and there shouldn't be any issues. > > I'd recommend reading a book like that below that works through PHP AND > MySQL in a very nice manner: > http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/ref=sr_1_1?ie=UTF8&s=books&qid=1270953434&sr=8-1 > > I probably won't provide much follow-up on MySQL database normalization in > follow-up emails so-as not to upset others on the list as this list > focuses > on PHP, but I hope I helped you get started on the right track and avoid > becoming discouraged. It takes some time to get it to "click", but once > it > does, DB development will be just as fun and easy as PHP ;) > > Adam > > -- > Nephtali: PHP web framework that functions beautifully > http://nephtaliproject.com > > > > __________ Information from ESET Smart Security, version of virus > signature database 5016 (20100410) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > __________ Information from ESET Smart Security, version of virus signature database 5016 (20100410) __________ The message was checked by ESET Smart Security. http://www.eset.com
|
Next
|
Last
Pages: 1 2 3 Prev: wiki api help needed with php Next: PHP execute very slow : PHP Version 5.2.6 |