Replying to Need Help With A PHP - MySQL Registration Script

After thinking about it I can see the advantage of writing a script that does what you're talking about vujsa. Have a function like build_db_query or something that builds the actually query and returns it as a string perhaps. You pass a table name, and an array of values in an assoc array indexing the related tables in the fields. You could make it even more flexible and add flag to determine whether it's a select, insert, update, or delete statement. Just have a switch on the flag, then build the appropriate sql statement.

This has been done before in the CakePHP framework (see http://api.cakephp.o...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!

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.c...lationships.png

Thanks for any help!
Jarrad

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

Your SQL Insert statement is wrong, you confused it with the UPDATE SQL Statement, the correct way to write it is:

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.

Your SQL Insert statement is wrong, you confused it with the UPDATE SQL Statement, the correct way to write it is:

$sql9="INSERT INTO User(
Username, Password, Name, Last, Sex, Month, Day, Year, 
Adresse, City, State, Zipcode, Country, Phone, Email, 
Father_Name, Mother_Name, Parent_Phone, Parent_Email, 
Level, Academic) 
Values('$Username', '$Password', '$Name', '$Last', '$Sex', '$Month', '$Day', '$Year', 
'$Adresse', '$City', '$State', '$Zipcode', '$Country', '$Phone', '$Email', 
'$Father_Name', '$Mother_Name', '$Parent_Phone', '$Parent_Email', 
'$Level', '$Academic')";

mysql_query($sql9) or die(mysql_errno(). ": " . mysql_error() );

I dont include the id field because i think it is an integer autonumeric field, let me know if im wrong about it, also you must verify all your data prior to your insertion, maybe using javascript in your forms page and another good practice to prevent sql injections is the use of the mysql_real_escape_string function if your system has the magic_quoutes_gpc off.

Best regards,

Actually you can do a traditional

INSERT INTO table(value1, value2) VALUES('SomeValue', 'SomeValue2')

But I think in mysql it'll let you address an insert statement as if you're updating, which isn't the same for postgres. Try it and let me know what you find out, but I'm pretty sure it's allowed. In this case he's wating to insert, not update. I was looking at a previous script that one of my professors wrote usuing a mysql database and his insert scripts looked a lot like the update statements. When we switched over to postgres we had to redo the insert statements to match to the traditional insert statement. It would probably be wise to stick with traditional unless you plan on staying with mysql.

Vujsa, you don't actually use a dbi? You just right your own code to supplement the use of db connections/queries? I was introduced to a dbi that's based on perl's standard dbi or something like that. I like it a lot better than all the php functions to deal with mysql/postgres. It standardizes everything, so whatever you want to do (use mysql, or postgres) you use same function call. You just change the inital connection's function to receieve a string argument of what database. I believe that's the way it's done, but with the script I'm working on there's about 10 different includes to every page in order to make it work right. Good experience tho.

After thinking about it I can see the advantage of writing a script that does what you're talking about vujsa. Have a function like build_db_query or something that builds the actually query and returns it as a string perhaps. You pass a table name, and an array of values in an assoc array indexing the related tables in the fields. You could make it even more flexible and add flag to determine whether it's a select, insert, update, or delete statement. Just have a switch on the flag, then build the appropriate sql statement.

I'm rather new to OOP and php, but can't this be done with polymorphism? (rather new to OOP in general)

hey well can some one helpme make this code work it won't INSERT INTO THE DATABSE

<?php
# register1.php
# common include file to MySQL
include("DB.PHP");

$Username=$_POST['Username'];
$Password=$_POST['Password'];
$Name=$_POST['Name'];
$Last=$_POST['Last'];
$Sex=$_POST['Sex'];
$Month=$_POST['Month'];
$Day=$_POST['Day'];
$Year=$_POST['Year'];
$Adresse=$_POST['Adresse'];
$City=$_POST['City'];
$State=$_POST['State'];
$Zipcode=$_POST['Zipcode'];
$Country=$_POST['Country'];
$Phone=$_POST['Phone'];
$Email=$_POST['Email'];
$Father_Name=$_POST['Father_Name'];
$Mother_Name=$_POST['Mother_Name'];
$Parent_Phone=$_POST['Parent_Phone'];
$Parent_Email=$_POST['Parent_Email'];
$Level=$_POST['Level'];
$Academic=$_POST['Academic'];
$Image_Link=$_POST['prevImage'];
$sql9="INSERT INTO User 
SET id = 'NULL',
    Username = '$Username',
    Password = '$Password',
	Name='$Name',
    Last='$Last',
	Sex='$Sex', 
	Month='$Month',
	Day='$Day',
	Year='$Year',
	Adresse='$Adresse',
	City='$City',
	State='$State',
	Zipcode='$Zipcode',
	Country='$Country',
	Phone='$Phone',
	Email='$Email',
	Father_Name='$Father_Name',
	Mother_Name='$Mother_Name',
	Parent_Phone='$Parent_Phone',
	Parent_Email='$Parent_Email',
	Level='$Level',
	$Academic='$Academic'";

Your SQL Insert statement is wrong, you confused it with the UPDATE SQL Statement, the correct way to write it is:

$sql9="INSERT INTO User(
Username, Password, Name, Last, Sex, Month, Day, Year, 
Adresse, City, State, Zipcode, Country, Phone, Email, 
Father_Name, Mother_Name, Parent_Phone, Parent_Email, 
Level, Academic) 
Values('$Username', '$Password', '$Name', '$Last', '$Sex', '$Month', '$Day', '$Year', 
'$Adresse', '$City', '$State', '$Zipcode', '$Country', '$Phone', '$Email', 
'$Father_Name', '$Mother_Name', '$Parent_Phone', '$Parent_Email', 
'$Level', '$Academic')";

mysql_query($sql9) or die(mysql_errno(). ": " . mysql_error() );

I dont include the id field because i think it is an integer autonumeric field, let me know if im wrong about it, also you must verify all your data prior to your insertion, maybe using javascript in your forms page and another good practice to prevent sql injections is the use of the mysql_real_escape_string function if your system has the magic_quoutes_gpc off.

Best regards,

I tend to write a perfectly good function for one use. Then later when I need a similar function, I just modify the first function. Then later, I need yet another similar function and repeat the process. I tend to write very complex functions as a result which are able to handle several different requests and then the function call is very simple to write as a result.

Since most of my database functions are all-in-one, I just need to use the same call each time with different variables. Then I tend to name my function something like deal_with_the_db() which is what it does and I don't have to think about the DB anymore. LOL

I tend to get frustrated with the mySQL commands so I remove myself a little.

By the way, why is it so difficult to get information out of the database even if you have all of the required information and you only want one entry.

vujsa

I didn't look closely enough to see that there was both INSERT and SELECT commands being sent as a result, the very least you should use a different function for each type unless you are pretty good with your function writing.

vujsa

I guess there could be alternative methods inside the function. It would check whether the query starts with SELECT or INSERT and the select the proper action. Would be a bit messy function but on the other hand the actual code would look neat with only single type of function calls for database queries.

Yep, you're basically entering an empry query as $sql is not defined.

But a generic function for different types of queries might be a bit useless as the return values of mysql_query can be very different, ranging from simple boolean values of a say INSERT to big results of SELECT queries.
I don't if you meant to do it but although otherwise a possible thing to do would be to send multiple queries tot the databse at once, separated by semicolons this is not possible with PHP as it is considered a security threat. You code would miss the appending of the quesries anyways so you probably weren't thinking about this.

I didn't look closely enough to see that there was both INSERT and SELECT commands being sent as a result, the very least you should use a different function for each type unless you are pretty good with your function writing.

vujsa

Yep, you're basically entering an empry query as $sql is not defined.

But a generic function for different types of queries might be a bit useless as the return values of mysql_query can be very different, ranging from simple boolean values of a say INSERT to big results of SELECT queries.


I don't if you meant to do it but although otherwise a possible thing to do would be to send multiple queries tot the databse at once, separated by semicolons this is not possible with PHP as it is considered a security threat. You code would miss the appending of the quesries anyways so you probably weren't thinking about this.