Insert value upon selection

[tryphy]

A):
First I have a company form, which i create a company name and it is saved in a company table..with an autoincrement id as 001( I used Zerofill in it.)
Creating the company --> I did this..

B):
Then, I have a project form which create project details, in this i have a company field, which can be selected from a drop down list.. I have a project table where is stores the project form details..

I want the project id to be 01, 02...and so on..Each company can have many projects..

eg:
C1 - P1, P2 (id : 01, 02,..)
C2 - P1 (id : 01,..)
C3 - p1, p2, p3 (id: 02..)

Everytime the project is created for a particular company i want the project id to be 01, 02..
If new project from new company then its again 01, 02..and so on..

And also need another validation check in image (B). There are some login field. Every time when the project is being created must check that the username and password doesnt already exists..

Mainly I need to find out the (B) part.

Can any1 help me out to solve this?

Thanks in advance.

[vsmash]

You need a sorting column.
Your company table can autoincrement with the id
Your project table should also auto increment but it won't start again - you need an extra two columns
COMPANY TABLE
COLUMNS: [id (autoincrement)], [Company_name]

PROJECT TABLE
COLUMNS: [projectID (autoincrement)], [companyID (Integer-foreign key)], [Sort (integer)]

every time a project is added, you need to select the MAX sort value from project table where companyID=selected company id. Add one to that value and use it for the insert of your project record.

Suggest you keep your creation of users and passwords to a separate process so that you can select people from a pulldown list on this page based on the chosen company.

In your add user section, you need to check on usernames only for duplicate, ignoring password.

so back to your project page - you need a project members table:
Columns: [projectID (INT foreign key)], [memberID (INT foreign key)], [project role (description such as 'Project Manager')]

[tryphy]

You need a sorting column.
Your company table can autoincrement with the id
Your project table should also auto increment but it won't start again - you need an extra two columns
COMPANY TABLE
COLUMNS: [id (autoincrement)], [Company_name]

PROJECT TABLE
COLUMNS: [projectID (autoincrement)], [companyID (Integer-foreign key)], [Sort (integer)]

every time a project is added, you need to select the MAX sort value from project table where companyID=selected company id. Add one to that value and use it for the insert of your project record.

Suggest you keep your creation of users and passwords to a separate process so that you can select people from a pulldown list on this page based on the chosen company.

In your add user section, you need to check on usernames only for duplicate, ignoring password.

so back to your project page - you need a project members table:
Columns: [projectID (INT foreign key)], [memberID (INT foreign key)], [project role (description such as 'Project Manager')]

Hey
Thanks,
For ur reply..
That was the same idea i was thinking...when i worked it out it gives me a Duplicate error...

[Banderson]

Hey
Thanks,
For ur reply..
That was the same idea i was thinking...when i worked it out it gives me a Duplicate error...

What is the duplicate error? Are you auto incrementing as previously stated?

Echo your error to screen with :

echo mysql_error ();

[tryphy]

What is the duplicate error? Are you auto incrementing as previously stated?

Echo your error to screen with :

echo mysql_error ();

This is my code:

<?php 
$sql = mysql_query("SELECT id from create_project");
$result = mysql_fetch_assoc($sql);
$s_id=$result['id'];
echo "$s_id" ;
echo "<br>";
$num = 01;

if($companyname == $s_id)
{
$max_sql = mysql_query("SELECT MAX(p_id) as proj_id FROM create_project where id = '$companyname'",$link);
$max_result = mysql_fetch_assoc($max_sql);
$max_id = $max_result['proj_id'];
$m_id = $max_id + 01;
echo "$max_id" ;
echo "<br>";
echo "$m_id" ;
echo "<br>";
$query = "INSERT INTO create_project(p_id, company_name, id, Project_title, total_grant, partner_company, end_date, c_name, c_pass, a_name, a_pass)"."VALUES('$m_id','$companyname','$companyname','$title','$grant','$pcompany','$date','$cname','$cpass','$aname','$apass')"."WHERE id = '$companyname'";
mysql_query($query,$link)
					or die("Couldn't add data to add to \"Create Project\" table: 
".mysql_error($link));
}
else 
{
//echo "Adding new record";
$query1 = "INSERT INTO create_project(p_id, company_name, id, Project_title, total_grant, partner_company, end_date, c_name, c_pass, a_name, a_pass)"."VALUES('$num','$companyname','$companyname','$title','$grant','$pcompany','$date','$cname','$cpass','$aname','$apass')";

mysql_query($query1,$link)
					or die("Couldn't add data to add to \"Create Project\" table: 
".mysql_error($link));

}

and my create table is
create table create_project
(p_id INT(2) NOT NULL auto_increment,
company_name varchar (50),
id INT(3) NOT NULL,
Project_title varchar (100),
total_grant float (8),
partner_company varchar(100),
end_date DATE,
c_name varchar (15),
c_pass varchar (15),
a_name varchar (15),
a_pass varchar (15),
UNIQUE KEY p_id (p_id)
)
auto_increment=1 TYPE=MyISAM;

I get the error as
"Couldn't add data to add to "Create Project" table: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = '003'' at line 1"

[tryphy]

Where id is the company id, from the company table..

[Banderson]

Where id is the company id, from the company table..

What is the echo statement at line 15

echo "$max_id" ;

Change this to

echo "$m_id" ;

because this is the data that is actually being inserted. Let's see what this prints?
Also uncomment line 26, unless your sure which query is failing. I would also change the echo errors to something else to differentiate them.

[tryphy]

In line 17 i already have the

echo "$m_id" ;

It doesnt print any thing

[Banderson]

In line 17 i already have the

echo "$m_id" ;

It doesnt print any thing

Sorry I missed that particular line. I would say this is where your query is failing. If it is not echoing any data here, hence the variable does not exist, so it cannot be passed into the query.

Also, I am noticing now that you have $companyname listed consecutively within your insert values statement.

I would recommend changing these, it can get confusing, not to mention cause buggy errors that will be hard to find.

I would also recommend executing var_dump(); to see what exactly is being passed.

Please don't take this wrong I am trying to be helpful, not hateful.

[tryphy]

Hey, thanks a lot for the help..
No i didnt think anything..
I will do some changes..and let u know..
I will send the code to you, can u check and correct it for me..

Thanks for ur help again

[Banderson]

Hey, thanks a lot for the help..
No i didnt think anything..
I will do some changes..and let u know..
I will send the code to you, can u check and correct it for me..

Thanks for ur help again

I will be glad to help you out any way I can. Send me a pm when your ready to proceed and I will help you.

Usually when I start a project, especially large scale , I sit down with a piece of paper and list out all my methods,classes, and variables in each one and then state what purpose they will serve within the program.

Some call it 'Pseudocode', I call it 'jotting' ;)

It all begins in your mind and on paper. This is where dreams become the reality.

[tryphy]

Thanks for ur help..

:)

I have sent a pm to u.

[tryphy]

woooh... hooo. ;)

I managed to solve the problem, with the help of Banderson and vsmash,..

Thanks a lot guys,,
:)

[vsmash]

Seems to me you don't have a sort column in your project table and you are trying to do it all in one table.
You need to have a separate company and projects table, thence you don't need a 'company name' column in your projects table because the foreign key value points to the company table's id enabling you to select with inner join.

Aside of your foreign key for company id (not unique in the projects table), you don't seem to have a sort column. This is the column you need to select max on.
eg:
Example Company table values:
RECORD 1: companyid=1, companyname='acme'
RECORD 2: companyid=2, companyname='B company'

Example Project table values
RECORD 1: projectid=1, fk_companyid=2, sortcolumn=1, projectname='foo project'
RECORD 2: projectid=2, fk_companyid=2, sortcolumn=2, projectname='anotherproject'

So, here there are two projects. the foreign key id of the company can be duplicate, as can the sortcolumn. If you select max(sortcolumn) from projecttable where fk_companyid=2 your result will be 2. You then add 1 for your next insert for that company.
If you select max for 'acme' company, you will get null because there are currently no projects. You need to convert your nulls to zeros then add 1 as above for the insert of this company's first project.

Make sense?
Sort column is not unique because it has to start again at 1 for each company, which is why you need to select max on sortcolumn.