ShaolinF Posted October 17, 2007 Share Posted October 17, 2007 Hey Guys, I want to create a table with the following columns. Can you please give me your recommendations: ID (Must be unique, starting with 00001, id should increment by 1) Name Gender (Male or female) Contact Number Email Address Time Of Registration IP Paid (Yes/No) Link to comment https://forums.phpfreaks.com/topic/73687-create-table/ Share on other sites More sharing options...
fenway Posted October 17, 2007 Share Posted October 17, 2007 ID (Must be unique, starting with 00001, id should increment by 1) -PK, auto_increment, BIGINT UNSIGNED, deal with the zerofill on the output only, no in the DB Name -varchar, 255 Gender (Male or female) -ENUM Contact Number -varchar,255 -- unless you can be sure you're only storing north american numbers, in which case CHAR(10) is sufficient Email Address -varchar,255 Time Of Registration -store date & time, in the DATETIME() field, use NOW() on INSERT IP -UNSIGNED INT, store with INET_ATON() -- don't store the dotted octet Paid (Yes/No) -ENUM... but it seems like it's missing lots of info (when, how much, for what, etc.) Could use some normalization when there are more fields. At the very least, index name -- but that really should be first and last separately, right? Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-371781 Share on other sites More sharing options...
ShaolinF Posted October 17, 2007 Author Share Posted October 17, 2007 Thanks. Well Im not too good with MYSQL so you'll have to excuse me if I ask some daft questions. The paid is only for a set of tickets, a simple paypal buynow type of button. So how exactly would I create the query, something like this: CREATE userinfo ( Name varchar(255), Gender ENUM, ContactNo varchar(255), EmailAdd varchar(255), TimeOfReg ( ??? ), IP UNSIGNED INT (not sure entirely), Paid ENUM ); What is the purpose of an index name ? Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-371794 Share on other sites More sharing options...
ShaolinF Posted October 18, 2007 Author Share Posted October 18, 2007 Also, could you give me a sample of data I could use in the fields (just to be sure). Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-372055 Share on other sites More sharing options...
fenway Posted October 18, 2007 Share Posted October 18, 2007 Thanks. Well Im not too good with MYSQL so you'll have to excuse me if I ask some daft questions. The paid is only for a set of tickets, a simple paypal buynow type of button. So how exactly would I create the query, something like this: CREATE userinfo ( Name varchar(255), Gender ENUM, ContactNo varchar(255), EmailAdd varchar(255), TimeOfReg ( ??? ), IP UNSIGNED INT (not sure entirely), Paid ENUM ); What is the purpose of an index name ? Even so, when did they buy the tickets? How many? Confirmation #? etc. ENUM requires a list of possible values; check the refman for syntax. Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-372389 Share on other sites More sharing options...
ShaolinF Posted October 18, 2007 Author Share Posted October 18, 2007 Well the thing is, they fill in a small form and press next where they are redirected to paypal. They choose how many tickets they want on the paypal system. Their is little control over what data I can extract from paypal. So the timeOfReg can be the purchase date, or maybe I could add another timeOfPurchase field ? They will recieve a confirmation email from me once they have submitted and also a receipt from paypal. Is it ok if you could go through the create statement and correct any errors in it for me please ? Im quite new to SQL and still have a long way to go. And thanks for your suggestions, some thought provoking questions. Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-372470 Share on other sites More sharing options...
fenway Posted October 19, 2007 Share Posted October 19, 2007 I'm sure there is a lot of info you can ask paypal to "send back" to you... ENUM ('Male','Female') and ENUM( 'Yes','No' ) are examples of correct syntax. Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-373156 Share on other sites More sharing options...
ShaolinF Posted October 19, 2007 Author Share Posted October 19, 2007 Lol, I was checking out paypal's site and yes they can send back alot of data, which now means I must postpone the DB creation untill I can get a better view of all the table structure. Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-373214 Share on other sites More sharing options...
fenway Posted October 19, 2007 Share Posted October 19, 2007 I would consider dumping all the paypul info into another table entirely. Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-373224 Share on other sites More sharing options...
ShaolinF Posted October 19, 2007 Author Share Posted October 19, 2007 Why would you say that ? Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-373315 Share on other sites More sharing options...
fenway Posted October 19, 2007 Share Posted October 19, 2007 Because it's not user info... it's purchase info. Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-373383 Share on other sites More sharing options...
ShaolinF Posted October 19, 2007 Author Share Posted October 19, 2007 Thanks. So would I create a relationship with the two tables though the same ID ? Correct ? Here is an example set of tables: table 1 UserID, name, age, gender, table 2 UserID, ProductName, purchase ID, quantity, unit price, total. So if I wanted to display UserId, name, productname, quantity, total - how would I do that ? Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-373443 Share on other sites More sharing options...
fenway Posted October 19, 2007 Share Posted October 19, 2007 You'd simply join the tables. Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-373489 Share on other sites More sharing options...
ShaolinF Posted October 20, 2007 Author Share Posted October 20, 2007 Thanks. Ok, earlier on I mentioned that I wanted my ID numbers to be complete random numbers rather than incremented. Well, I have plans of putting a system into place where current registered users enter their ID into a textbox and it will display their reg. details. The problem with incremented IDs is someone could probably 'guess' the number (or atleast increases its chance with a incremented ID). The reason I only want them to beable to enter IDs is to make things quicker, its not a large system and its only to purchase tickets (for university events). Whats your take on this ? Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-374372 Share on other sites More sharing options...
fenway Posted October 22, 2007 Share Posted October 22, 2007 Then you need usernames & passwords. Link to comment https://forums.phpfreaks.com/topic/73687-create-table/#findComment-375189 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.