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
