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@xxxxxxxxx> wrote in message news:i2je4d8ea9d1004102009r963ef9f3i930a2e757b5ce24a@xxxxxxxxxxxxxxxxx > On Sat, Apr 10, 2010 at 7:56 PM, Gary <gwp@xxxxxxxxxxxxxxxx> 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@xxxxxxxxx> wrote in message >> news:x2ge4d8ea9d1004101457r62b7c0b8uc3673353c3758394@xxxxxxxxxxxxxxxxx >> >>> On Sat, Apr 10, 2010 at 5:27 PM, Gary <gwpaul@xxxxxxx> 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
