This is not necessarily true you can include the SET in an insert statement see MySQL:INSERT syntax for more and you will notice the second method of using the INSERT statement allows the use of the SET statement, you just don't see it used that much and instead see the UPDATE being used.

business logic validation
Need Help With A PHP - MySQL Registration Script

How to do business logic validation to check whether duplicate username is already existing in database using php

-question by kalai

Hi,

Below is a picture of my database tables, and their relationships. I was hoping someone might have a quick look and tell me if I'm on the right track, or if I need to:

Change PRIMARY keys
Change INDEX / FOREIGN keys
Change Table Structures

Also, I'd like to add a table that stores the various CALIBERS that firearms are available in. I'm thinking the relationship would be many-to-many, as a firearm can have many calibres, and a calibre is available for many firearms...?

How would I make the CALIBRE table? What fields should the table contain, and how should it relate to the FIREARMS table? I'd like to be able to output a list of firearms, and show available calibres for each of those firearms.

Just to help out, the database will be used to:
- search for firearms and their available calibres
- show retailers that sell a particular make of firearm
- show reviews that owners (users) submit. These will be shown alongside each firearm searched for


http://home.exetel.com.au/jazzy/relationships.png

Thanks for any help!
Jarrad

This has been done before in the CakePHP framework (see http://api.cakephp.org/class_model.html#eb...b5a35dd428f5c81 and I'm sure numerous other PHP frameworks that I don't work extensively with as well), except that Cake didn't take advantage of PHP's OOP because it also wanted to be PHP 4 compatible. It just requires that you pass in the variables in a certain format (in Cake's case it's an array) and then parse based on the fields and values in that format. It is really quite flexible and makes saving data a lot faster than writing the same old INSERT functions. Especially in this case where the insert function is exceptionally huge, having a function like that would be a huge advantage.

Hey hotsam!

I'd say for your firearms and reviews, you don't need a many-to-many relationship. After all, each review only has one firearm; each review wouldn't have multiple firearms, would they? Then each firearm would have many reviews, which would make sense. As for the business to firearm relationship, I'm not sure if a firearm can have many businesses--that would depend on the license, right? Aren't many firearms licensed so that they're only made by one business? (Then again, I'm not familiar with their creation...)

Everything else makes sense to me. :-) Good luck!