create tables with php

[stu]

Hi all


I'm having a little problem creating a php script to create database tables. Heres what i have got

   $qry = "CREATE TABLE $userid (id int not null primary key auto_increment,
testField varchar (75))";


this does create a databse table fine. it creates a table based on the users id but what i want is two tables based on the users id somthing like

$userid_contacts   and   $userid_info

is this possible what i want and how do i do it

thanks

stuart

[stu]

Hi

I've got it working now fine with this code


   $qry = "CREATE TABLE diary_$login (id int not null primary key auto_increment,
testField varchar (75))";


but when i go to add a second qry to create a second table it will still only create one table and its the bottom qry

   $qry = "CREATE TABLE diary_$login (id int not null primary key auto_increment,
testField varchar (75))";

   $qry = "CREATE TABLE contacts_$login (id int not null primary key auto_increment,
testField varchar (75))";

any ideas?

thanks

stuart

[Andy]

Hi Stu,

I normally create the database tables in PHPMyAdmin in my Cpanel and this gives you the code that works. But it may contain additional stuff that is not needed that you can trim out, but you will have to test it.

But, I suspect that you are not executing the MySQL command to actually generate the table between the $qry string assignments. Surf over php.net to look for examples.

OK, I am feeling generous since it is near Christmas  , here is a snippet from my code of how to generate a MySQL database table in an install script:

Code:

$q = "CREATE TABLE `" . TABLE_PREFIX . "links` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `count` int(10) unsigned NOT NULL default '0',
  `frame` int(11) NOT NULL,
  `cloak` int(11) NOT NULL,
  `url` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `goal` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`)
) ENGINE = MYISAM";

connect_to_db();
$r = mysql_query($q);
if (!$r) {
echo "Query: $q ";
die('Invalid query: ' . mysql_error());
}

The freaky left-slanted tick marks are used in MySQL, so to get more exam points, make sure you use them. Your teachers maybe don't know about this. They are used for security reasons so the code breaks in the event of SQL injections rather than contorting off hackers.

TABLE_PREFIX  is a constant value that is included via the config file like with WordPress where they have the wp_ prefix as the default value. In your code you have a $ sign that may be a bug. so you may be wanting to insert the $login string variable into the table name, so you need to concatenate the elements of the string using dots to join them up.

[stu]

Hi

Thank you. I have managed to get it working how i want today. I to normaly create my tables in phpmyadmin. But I started php a few months ago and thought as I wasnt busy over xmas i would give myself a challange. What I am creating is a website called http://www.mylifelink.co.uk it intends to be a website rarther like a diary. I want it to have the ability for users to have there own account and in their a diary, address book, search the internet, search for friends that are signed up to the site.  I know its all been done before but I am trying to do this as it uses pretty much all php coding and I thought it would be a good way to learn.

The reason i needed the php code to create the database tables was so that when some one registers the register script will insert there registration details into the table users and the create the tables need for the diary and contacts. Each user will need to have a database table that is unique to them so hence the tables called contacts_$userid as only one username can be registered under that name.

I have just about finished today the login functions of the website. Its not uploaded yet. but its tested and working.

Login uses md5 encryption
register uses's form takes down all there details stores it in the database creates all the tables needed under each users userid name.
database stores users last login data and registration data
login uses session and displates the users id on screen <?php echo $_SESSION['SESS_userid'];?>  i then uses this to call there details from the database
logout closes the user session.

all this works fine. ok its probably not up to proffessinal standards. I've just taken it down now so that i can create the style and graphics and make it W3C compliant then its just a case of the contacts script, diary script, search users script, invite friends script extra.

I could do with something els, i was thinking something like a few simple games or applications as i know they can be done in php carnt they. any ideas welcome.

The script i used to get the registraton form to create the tables was, ok I've not created big tables yet but not sure what feilds are required so its just basic tables to test function.

   //Create insert query
   $qry = "INSERT INTO members(firstname, lastname, login, passwd, passwdr) VALUES('$fname','$lname','$login','".md5($_POST['password'])."','$cpassword')";
   $result = @mysql_query($qry);
   
   //Create Table for diary query
       $sql = "CREATE TABLE diary_$login (id int not null primary key auto_increment,
testField varchar (75))";

mysql_query( $sql );

//Create Table for contacts query
   $sql = "CREATE TABLE contacts_$login (id int not null primary key auto_increment,
testField varchar (75))";

mysql_query( $sql );

//Create insert for user activity query
$qry = "INSERT INTO activity(user_id, date) VALUES('$login','$date')";
   $result = @mysql_query($qry);
   
   //Check whether the query was successful or not
   if($result) {
      header("location: register-pass.php");
      exit();
   }else {
      die("Query 2 failed ");
   }

thanks

stuart

[Andy]

Stu,

you are doing the opposite of what is standard practise. You should have common tables that all the users share. The database engine is optimized for speed to search and manipulate data in a table. You need to read up on database design (design of the structure of your database).

So your diary table should be for all the users with an index (Key) column of user ID as well as a primary index that is like a row count. Then the database engine can quickly find data relevant to a particular user.

[stu]

hi

thanks, i think i know what u mean. I'll do a bit of reading up and see what i can cum up with.

thanks

stu

[stu]

Hi

I've been having a look at how to do these tables could you tell me if i'm on the right lines Create one single table for all users contacts somthing like

Create TABLE contacts (
id auto incrument,
user_id
firstname,
lastname,
phone,
fax,

Extra, I know thats not in detail but its just to show an example. All users would use this one table then. As the userid is unique to each user the search would need to be somthing like

on the find contact  it would be post values of firstname aqnd lastname


$fname = $_POST['firstname'];
$lname = $_POST['lastname'];
$userid = $_SESSION['SESS_userid']

qry = SELECT FROM TABLE contacts WHERE userid = $userid and WHERE firstname = $fname and WHERE lastname = $lname;

How would i write a mulitple where statment like this and is it possible?

thanks

stuart

[Andy]

Yes, your table is along the right lines.

Here is a tutorial: http://dev.mysql.com/doc/refman/5.1/en/tutorial.html