PDA

View Full Version : Forms and Create Tables in MySQL


cocoonfx
12-04-2007, 10:04 AM
Hello


I want to build a form which creates a new user account for a members area where upon i will store all the details for this user i want the information to be sent to a user table for the login area and then to create a table specifically for that user.

i.e

User 1 enters details i.e Username,Password,Age,Email

As soon as he hits send this will update the User Table in the DB for loggin in etc..

And at the same time i want a unique table created into the DB for that user as the members areas will have other forms and bits and bobs which need to be stored.

Example of code:

if($username && $password && $Email && $Submit){
INSERT into "User" ($Username,$Password,$Age,$Email,Username,Password ,Age,Email);
And
Create Table "username_data",(username,password,age,email,detail 1, detail2,detail3);
}else{
Echo "Error";
}

(excuse the wrong syntax but i just giving example for logic).

Does this make sense and is the principle right?

davidj
12-04-2007, 10:47 AM
creating a table for every user ?

this is not a nice way

why do you need a seperate table for evey user

cocoonfx
12-04-2007, 12:15 PM
Hello dj


Well the members area is for a personal trainer which needs to upload food diary's and training programmes. My idea was to have 1 table to manage the user names and passwords and sessions and then a table for each member (max will be 50) and then the table will have a field for each month so that the food diarys can be uploaded.

I guess i could have one table which has 100 columns in...

Its the first time i have tackled this and i just trying to workout the most efficent approach. I would in access just have one table so i surpose i could do this in MySQL.

Any advise on how to structure this would be appreciated.

davidj
12-04-2007, 12:33 PM
have one table for users

////////////////////////////
/// users table ///

user_id | name | age | username | password | date time stamp

////////////////////////////

user_id is an auto incrementing number

create another table which creates a row and adds the user_id to it when a user is created (there is a function which gets the last auto_id created)

its this second table which holds all the info and is referenced by the user_id

cocoonfx
12-04-2007, 01:15 PM
Thanks DJ superstar didn't even know that function existed last auto_id created....

I will have a play tonight.

davidj
12-04-2007, 01:21 PM
cocoon

if you need any help let me know

i can produce a diagram flow chart of how your database should look

need your field names though

cocoonfx
12-04-2007, 08:01 PM
Hello david


Thank you for your assistance so far, i have however got stuck on the first hurdle. I can not find the last auto_id.... i have check google but nothing seems to work.

I have created the table user

CREATE TABLE `user` (
`user_id` int(10) NOT NULL auto_increment,
`username` varchar(150) default NULL,
`password` varchar(150) default NULL,
`email` varchar(200) default NULL,
`level` text,
`date` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=82 ;

I have then created the table user_detail

CREATE TABLE `user_detail` (
`user_id` int(10) NOT NULL auto_increment,
`id` int(11) NOT NULL default '0',
`weight` varchar(150) default NULL,
`height` varchar(150) default NULL,
`age` varchar(200) default NULL,
`test` text,
`date` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=79 ;

When i update the table 'user' the 'user_detail' does not update with the 'user_id' from the 'user' i must be doing something wrong.... can assistance will be appreciated

davidj
12-04-2007, 08:08 PM
ok

////////////////////////////////////
// insert into user table here ///
////////////////////////////////////

//////////////////////////////////
// $id = mysql_insert_id();///// << $id contains the last generated auto id
//////////////////////////////////

//////////////////////////////////////
// insert into other_user_details table using $id which contains the last auto increment
//////////////////////////////////////

cocoonfx
12-04-2007, 08:20 PM
Sorry David

my brian is not computing with this....... :(

I am working within the MySQL admin area (server side)

is this meant to be done on the PHP side?

davidj
12-04-2007, 08:22 PM
its done using PHP insert query

do you hand code? or use dreamweaver code

cocoonfx
12-04-2007, 08:36 PM
Ah ha ok i get it now.....

bit of both, i think this is what confuses me.... it shouldn't but it does, i am a designer at heart not a programmer. Still learning.

so i do


<?PHP
include("includes/connect.php");

$id = mysql_insert_id();

$query =sprintf("INSERT into user (user_id) VALUES ('$id',')");
mysql_query($query,$dbh)or die(mysql_error());

$query2 =sprintf("INSERT into user_details (id) VALUES ('$id',')");
mysql_query($query,$dbh)or die(mysql_error());

?>

Is that correct? Sorry for the step by step guide but i have had a very very bad day.

davidj
12-04-2007, 08:41 PM
<?PHP
include("includes/connect.php");

$query =sprintf("INSERT into user (user_id) VALUES ('$id',')");
mysql_query($query,$dbh)or die(mysql_error());

$id = mysql_insert_id(); //<< needs to be after the auto id has been created and before the other insert

$query2 =sprintf("INSERT into user_details (id) VALUES ('$id',')");
mysql_query($query,$dbh)or die(mysql_error());

?>

cocoonfx
12-04-2007, 09:20 PM
Oooooh its not working.....:confused:


when i check the table in PHPadmin the user table updates with a row of nulls. see picky below...

I am struggling here mate not sure its because of its been a long day but i just don't seem to get it.

I have taken some screen shots of the PHPadmin tables

davidj
12-05-2007, 05:45 AM
look at this..

VALUES ('$id',')")

what is the ,' (comma quote) doing?

cocoonfx
12-05-2007, 06:13 PM
Hello David


6:45am are you mad!

sorry for being think last night. i am going to have enough stab at this at later tonight.

Could i take you up on the flow diagram as often this help me a great deal.

so the fields are:

Table = user - Controls Sessions and login

user id | username | password | email | timestamp

Table = user_detail - All details about user (s)

detail_id | age | weight | rbmpstart | gender | jandiary | janexec | febdiary | feb diary | bmistart | bmp | bmi | notes

Let me know if you need anything else.

Thanks again
James

davidj
12-05-2007, 06:49 PM
6:45am are you mad!


take the wife to work at 7am so check the forum while waiting for her to get ready.

hehe sad eh

davidj
12-05-2007, 07:18 PM
ok

here it is in full


<?php
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "database";

$connect = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);

$name = $_POST['name'];
$password = $_POST['password'];

if($name && $password){

mysql_select_db($database, $connect);
$query = sprintf("insert into user (name, password) values ('$name', '$password')");
$send = @mysql_query($query);
$result = @mysql_fetch_array($send);

$id = mysql_insert_id();

mysql_select_db($database, $connect);
$query2 = sprintf("insert into user_details (USER_ID) values ('$id')");
$send2 = @mysql_query($query2);
$result2 = @mysql_fetch_array($send2);
}
?>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="insert.php">
<p>
<input type="text" name="name" id="name" />
</p>
<p>
<input type="text" name="password" id="password" />
</p>
<p>
<input type="submit" name="button" id="button" value="Submit" />
</p>
</form>
</body>
</html>

cocoonfx
12-05-2007, 08:08 PM
Thanks mate works a dream....

I can see where i screwed up i did not set the unique key's on the tables and also i did not add in the '$results' variable.

Its quite funny i can look at code and see exactly how it works but ask me to write code thats where i fall apart.....

think with time i should get better.....problem is i don't do php daily so it a slow process to learn.

I owe you a case of beer .... :grin:

davidj
12-06-2007, 09:04 AM
you dont need the $result var / mysql_fetch_array function as your not returning stuff back

it was me just speed coding. I was on auto pilot.