Page 1 of 1

need help in creating users table in php

Posted: Mon Oct 10, 2011 9:26 am
by yankie31
Hi. Im new to the world of programming, right now Im on self study in Php.. I want to learn php so I study all by myself using different forums and tutorials..Currently Im creating a Phonebook system which has a Login and Registration. Im done with the Login system and Im having a trouble in Registration. Here's the idea, When a user register, the program register him to the table users, and also creates a table for the user. So Every user has a 1 personal table (phonebook). Here is the code:

Code: Select all

      $myusername = $_POST["myusername"];
        $mypassword = $_POST["mypassword"];
        
        $queryreg = mysql_query("
            INSERT INTO table_users VALUES ('','$myusername','$mypassword')
        ");
        $crt_Table = "CREATE TABLE '$myusername' (
                'IDnum' int NOT NULL AUTO_INCREMENT,
                    PRIMARY KEY('IDnum'),
                'FirstName' varchar(20),
                'LastNam'e varchar(20),
                'ContactNo' int(20),
                'EmailAdd' varchar(32)
                )";
        mysql_query($crt_Table,$consql);
when I run the program, it doesnt show what part did I go wrong. It Registers the new user to the table users but it doesnt create the user's own table..Why is that? Pls. Help me on this one, I tried to change variable names maybe i had an error but It also doesn't show where or what part has an error only the blank page..

Thanks.. Looking forward to some help, pls..

Re: need help in creating users table in php

Posted: Mon Oct 10, 2011 9:55 am
by social_experiment

Code: Select all

<?php
mysql_query($crt_Table,$consql) or die(mysql_error());
?>
This will display a mysql error, if any. Does the username you use to logon to the database have sufficient privileges?

Re: need help in creating users table in php

Posted: Mon Oct 10, 2011 3:01 pm
by flying_circus
It looks like a SQL syntax error, look at where you are defining 'Lastnam'e, you'll spot a quote out of place.

Perhaps the better question is, about your design.

Rather than create a seperate table for each user, the more common way is to create 1 global phonebook table. In the phonebook table you will have a column for user id, so you can tell which contact belongs to which user.

Re: need help in creating users table in php

Posted: Mon Oct 10, 2011 8:37 pm
by yankie31
flying_circus wrote:It looks like a SQL syntax error, look at where you are defining 'Lastnam'e, you'll spot a quote out of place.

Perhaps the better question is, about your design.

Rather than create a seperate table for each user, the more common way is to create 1 global phonebook table. In the phonebook table you will have a column for user id, so you can tell which contact belongs to which user.
...ok let me try that..what do you mean by global phonebook table? What I did is I create 1 table for all the users..But I also want to create a table for the user's contacts

Re: need help in creating users table in php

Posted: Mon Oct 10, 2011 11:41 pm
by yankie31
social_experiment wrote:

Code: Select all

<?php
mysql_query($crt_Table,$consql) or die(mysql_error());
?>
This will display a mysql error, if any. Does the username you use to logon to the database have sufficient privileges?
..what do you mean by sufficient privileges? like what administrators can do? at this moment, there is still no rules yet..just register then login then create or edit or delete contacts..but I'm stock at registration in creating user's own table (own phone book)

"sorry for the grammar, I'm not good at English.."

Re: need help in creating users table in php

Posted: Tue Oct 11, 2011 12:50 am
by social_experiment
yankie31 wrote:..what do you mean by sufficient privileges? like what administrators can do?
When you create the SQL user that you will use to interact with the database, you have to grant certain privileges to the user. Below is a list of privileges. Do you receive any error messages?
SQL reference manual wrote: ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION
ALTER Enable use of ALTER TABLE
ALTER ROUTINE Enable stored routines to be altered or dropped
CREATE Enable database and table creation
CREATE ROUTINE Enable stored routine creation
CREATE TABLESPACE Enable tablespaces and log file groups to be created, altered, or dropped
CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE
CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEW Enable views to be created or altered
DELETE Enable use of DELETE
DROP Enable databases, tables, and views to be dropped
EVENT Enable use of events for the Event Scheduler
EXECUTE Enable the user to execute stored routines
FILE Enable the user to cause the server to read or write files
GRANT OPTION Enable privileges to be granted to or removed from other accounts
INDEX Enable indexes to be created or dropped
INSERT Enable use of INSERT
LOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS Enable the user to see all processes with SHOW PROCESSLIST
REFERENCES Not implemented
RELOAD Enable use of FLUSH operations
REPLICATION CLIENT Enable the user to ask where master or slave servers are
REPLICATION SLAVE Enable replication slaves to read binary log events from the master
SELECT Enable use of SELECT
SHOW DATABASES Enable SHOW DATABASES to show all databases
SHOW VIEW Enable use of SHOW CREATE VIEW
SHUTDOWN Enable use of mysqladmin shutdown
SUPER Enable use of other adminstrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command
TRIGGER Enable triggers to be created or dropped
UPDATE Enable use of UPDATE
USAGE Synonym for “no privileges”

Re: need help in creating users table in php

Posted: Tue Oct 11, 2011 11:52 am
by flying_circus
yankie31 wrote:what do you mean by global phonebook table? What I did is I create 1 table for all the users..But I also want to create a table for the user's contacts
Basically I mean create 1 table for the users and 1 table for the phonebook

Users Table
---------------------
User_Id (int)
User_name (string)
etc

Phonebook Table
----------------------
Phonebook_Id (int)
User_Id (int)
phonebook_contact_name (string)
etc


Then, you would just query the phonebook table based on user id. So, lets say you are logged in as admin, and admin has a user id of 3

psuedo_query = "SELECT `phonebook_Id`, `phonebook_contact_name` FROM `Phonebook_Table` WHERE `User_Id`='3';"

That will return all of the contacts in the phonebook table belonging to the admin user (user with id of 3).


The more important design consideration is to organize your stored data by type. Since it would seem logical that all of your contacts in all of your user's phonebook would consist of the same thing (name, phone number, email, address, etc) it seems silly to create many unique tables that all hold the same format of data, when 1 table will suffice with the addition of an index to be used as a reference.

Hopefully that will get you started.

Re: need help in creating users table in php

Posted: Tue Oct 11, 2011 6:54 pm
by yankie31
flying_circus wrote:
yankie31 wrote:what do you mean by global phonebook table? What I did is I create 1 table for all the users..But I also want to create a table for the user's contacts
Basically I mean create 1 table for the users and 1 table for the phonebook

Users Table
---------------------
User_Id (int)
User_name (string)
etc

Phonebook Table
----------------------
Phonebook_Id (int)
User_Id (int)
phonebook_contact_name (string)
etc


Then, you would just query the phonebook table based on user id. So, lets say you are logged in as admin, and admin has a user id of 3

psuedo_query = "SELECT `phonebook_Id`, `phonebook_contact_name` FROM `Phonebook_Table` WHERE `User_Id`='3';"

That will return all of the contacts in the phonebook table belonging to the admin user (user with id of 3).


The more important design consideration is to organize your stored data by type. Since it would seem logical that all of your contacts in all of your user's phonebook would consist of the same thing (name, phone number, email, address, etc) it seems silly to create many unique tables that all hold the same format of data, when 1 table will suffice with the addition of an index to be used as a reference.

Hopefully that will get you started.
..ok.. I get it, let me try that later after my work..hopefully would work this time..thanks! ;)