[SOLVED] Noob question about using two tables and reducing redundancy

[atticus]

Here is the logical flow of what I am trying to do:

-I have two tables:company, client

-The first table (company) contains information about a company that is a customer

-The second table (client) contains the information about the users from that company.

-I want to assign a client access and associate that client with a company

-to avoid problems if the name of the company or the client changes, I am using an integer ID which is primary in MySQL for the company id.

-When I created the form to add a user, I do a query of the company table and create a drop down menu listing the names of the company  in the company table.

 

My question:

How do I convert that company name back to the com_id to be inserted into the client table.  I haven't posted code because my form works fine as long as I just insert the company name, I just don't know how to tell it to insert the id associated with the company name as opposed to the company name.  Thanks in advance.

 

 

 

 

[paul2463]

one way to do it is when you go away to the php page that does the insertion into the database you could run another quick query to get the id value

$query = "SELECT ID FROM table where company_name = companyname";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$IDrow = $row['ID'];
//you now have the ID of the company from the table

 

[atticus]

thanks...here is what I have got so far but I am not getting the value into the table

 

<?php
if(isset($_POST['add']))
{
include 'config.php';

$query = "SELECT com_id FROM company WHERE company_name = company_name";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$IDrow = $row['ID'];
//you now have the ID of the company from the table

$id = $_POST['$IDrow'];
$lastname = $_POST['lastname'];
$firstname = $_POST['firstname'];
$phone = $_POST['phone'];
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];

$query = "INSERT INTO client (last_name, first_name, phone, username, password, email, com_id) 
VALUES ('$lastname', '$firstname', '$phone', '$username', PASSWORD('$password'), '$email', '$id');"; 
mysql_query($query) or die('Error, insert query failed' . mysql_error());

[paul2463]

<?php
if(isset($_POST['add']))
{
include 'config.php';

//how is the company name being passed to this page???

$query = "SELECT com_id FROM company WHERE company_name = company_name"; //??????
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$rowid = $row['ID'];
//you now have the ID of the company from the table

//$id = $_POST['$IDrow']; //if you are using the $rowid above for this it is not needed like
$lastname = $_POST['lastname'];
$firstname = $_POST['firstname'];
$phone = $_POST['phone'];
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];

$query = "INSERT INTO client (last_name, first_name, phone, username, password, email, com_id) 
VALUES ('$lastname', '$firstname', '$phone', '$username', PASSWORD('$password'), '$email', '$rowid')"; //changed the com_id to = $rowid above
mysql_query($query) or die('Error, insert query failed' . mysql_error());
?>

[atticus]

when I print the results from this code:

$query = "SELECT com_id FROM company where company_name = company_name";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$IDrow = $row['com_id'];

 

I get the company id.

 

But the query is not inserting it into the database.  Did I do my variable wrong?

 

[paul2463]

yes if you have alook at the insert code I placed back on here I changed the value for the cinserted com_id to be the newly returned id from the database. I was just wondering where the company name came from and also I take it that you are using a vraible for the company name inthe query?

[atticus]

thanks...you were right about the: $id = $_POST['$IDrow'];

After I commented it out and used the other variable, it worked perfectly...again, thanks