Multiple tables with 1-1 relationship

PHP, MySQL

[naiquevin]

I am developing an application (for learning purpose only) which requires a table with 24 columns.
For organizational purpose, I decided to make 3 different tables instead of a single one.
1) which carries info of highest importance and assigned as 'NOT NULL'-------(say Table A )
2) Info which can be null ---(Table B )
3) only 2-3 columns as of now. Any additional columns required in future would be added to this..

I know this would make things complicated but is it still a good idea? .. as in does it serve any purpose apart from convenience .. or should I go back to the single table approach

[d40sithui]

It's usually easier to manage 3 small tables than 1 big one. To answer your question, I think we would need some more information about your project - i.e what will it do? You can't simply divide tables based on null/non null data.

[naiquevin]

ok I created 3 tables .. but I am facing a problem now..

There is a form which asks for the info to be inserted in these tables ..

'id' is primary key,(set to auto_incr) in table A and its the foreign key in table B and C

Now the first 4 form fields refer to table A and so INSERT INTO works perfectly fine here..

the next form fields are to be stored in either table B or C..
SInce id is a foreign key in B & C which refers to table A, will an entry in table A automatically create a new entry in table B and C for the same id?
I mean how can I insert into table B and C for the same id?

do i need to use UPDATE here ? as INSERT INTO will create a new row.
thanks

[naiquevin]

OK .. I changed the tables a bit.....
Now I have split up the table into 2 different tables with the same primary key 'user_id' and using the following code to insert data

if($password == $password2) {

$query1 = "SELECT * FROM login WHERE username = '$username'";
$result1 = mysqli_query($con,$query1);

if(mysqli_num_rows($result1) == 0) {

//the new username chosen is unique so proceed

$query2 = "INSERT INTO login (username, password, join_date) VALUES" .
"('$username' , SHA('$password'), NOW())";

$result2 = mysqli_query($con,$query2);

$query3 = "INSERT INTO general (email, day,month,year,gender,location)" .
"VALUES ($email, $day, $month, $year, $gender, $location)";

$result3 = mysqli_query($con,$query3);
//....and the rest of the code

query2 works beautifully and a new entry is created in the login table..
query3 doesnt work ... no new entry in general table

PS> A single HTML form is used to grab all the information

[d40sithui]

should be

$query3 = "INSERT INTO general (email, day,month,year,gender,location) VALUES (\"$email\", \"$day\", \"$month\", \"$year\", \"$gender\", \"$location\")";

if these fields are all varchar.

Great! You're making progress. I think since you only have about 9 data types, it would be easier to put this into one table. Also, the data is user information so it makes sense that they go into one table. You wouldn't have to make multiple queries.

Something that is useful if you're going to continue this path - "SELECT LAST_INSERT_ID()" will find the last id that was inserted. After query2 is executed, you can find the id with this query.

Good luck.

[naiquevin]

thanks for the reply, but it is still not working ...:(
gender,location, email are varchar
day, month, year are int
All these are assigned not null constraint

What can possibly be wrong? Any thing to do with the not null constraints on the fields in general table?

I think putting all the fields in one table will make things simpler .. but these are not all the fields .. there are abt 15 more fields apart from these 9.

With 24 fields, will it still be a good idea to use a single table?

[naiquevin]

Ok finally I decided to combine the fields into a single table but the same problem now ..
no new entry gets created in the table..
no error shown, and it shows 'Success' msg instead while nothing is stored in the table

Here is the code
if($password == $password2) {

$query1 = "SELECT * FROM login WHERE username = '$username'";
$result1 = mysqli_query($con,$query1);

if(mysqli_num_rows($result1) == 0) {

//the new username chosen is unique so proceed

$query2 = "INSERT INTO login (username,password,join_date,email,day,month,year,gender,location) VALUES" .
"(\"$username\" , SHA('$password'), NOW(), \"$email\", \"$day\", \"$month\", \"$year\", \"$gender\", \"$location\")";

$result2 = mysqli_query($con,$query2);

//Confirm success with the user

echo '<p> Welcome <span style="color:green; font-size:150%"> '. $username .'</span>, <br/> your new account has been succesfully created. You are now ready to <a href="login.php">log in</a> </p>';

---------------------------------------
so I put this code after $result2
if(!$result2) {
die ('error:' . mysqli_error());
}

it gives a warning this way..

Warning: mysqli_error() expects exactly 1 parameter, 0 given in D:\xampp\htdocs\project\signup.php on line 69
error:

-------------------
any problem with the code ?
Thanks for bearing with my (stupid) queries..

[d40sithui]

You're getting that error because the function mysqli_error needs a mysql connection link parameter. So to fix this you would do

die ('error:' . mysqli_error($con));

This checks if the query was valid.

if(!$result2)

The below checks if the last processed query was successful in that one or more rows was changed usually after an INSERT, ALTER, DELETE query. So if this fails, your previous query did not do anything. If this is the case, then you print out the actual query to see what went wrong.

if(mysql_affected_rows()){}