INSERT into Two Tables

Anon

I'm trying to create an INSERT statement that will change a field in one
table based upon the id defined in another table.

Here is what I tried:

$sql1 = "INSERT INTO $table_name1 (FirstName,LastName,WorkPhone,
HomePhone,EmailName,Birthday)
VALUES
('[$FirstName]','[$LastName]','[$WorkPhone]','[$HomePhone]','[$EmailName]','
[Birthday]')
";

$sql2 = "INSERT INTO $table_name2 (CompanyName,WebSite)
VALUES
('[$CompanyName]','[$WebSite]')

WHERE contacts ON company.CompanyID =
contacts.CompanyID WHERE ContactID='$ContactID'
";

$result_1 = @($sql1,$connection) or die("Couldn't execute
query.");
$result_2 = @($sql2,$connection) or die("Couldn't execute
query.");
?>

The goal is based upon a unique field call ContactID I want to be able to
update the CompanyName associated with the ContactID.

For example: ContactID=1 might have an associated CompanyName= Smith, Inc.
What I want to do is UPDATE/INSERT a new CompanyName and have that reflected
by changing the CompanyID associated with the ContactID.

kparker

Assuming the ID column in question is auto_increment, you just need to call mysql_insert_id() after the first query. That will retrieve the value that was just auto-assigned during that insert.

sfmnetsys

I haven't used mysql_insert_id(). Could you please provide an example.

Thanks.

kparker

if ( mysql_query("insert into parent (...) values (...)", $conn))
  {
  $id = mysql_insert_id($conn);
  mysql_query("insert into child (parent_id, ...) values ($id, ..)", $conn);
...

sfmnetsys

In my example then it would be:

if ( mysql_query("insert into parent (company) values (CompanyName,WebSite)", $conn))
{
$CompanyID = mysql_insert_id($conn);
mysql_query("insert into child (ContactID) values ($ContactID)", $conn);

Is this correct? Also, when referring to $conn do you mean $connection or is it something different?

rsterbin

Steve Fitzgerald wrote:

In my example then it would be:

if ( mysql_query("insert into parent (company) values (CompanyName,WebSite)", $conn))
{
$CompanyID = mysql_insert_id($conn);

mysql_query("insert into child (ContactID) values ($ContactID)", $conn);

Is this correct? Also, when referring to $conn do you mean $connection or is it something different?

Steve Fitzgerald also wrote:

I'm trying to create an INSERT statement that will change a field in one table based upon the id defined in another table.

Here is what I tried:

$sql1 = "INSERT INTO $table_name1 (FirstName,LastName,WorkPhone,HomePhone,EmailName,Birthday) VALUES ('[$FirstName]','[$LastName]','[$WorkPhone]','[$HomePhone]','[$EmailName]','[Birthday]')";

$sql2 = "INSERT INTO $table_name2 (CompanyName,WebSite) VALUES ('[$CompanyName]','[$WebSite]') WHERE contacts ON company.CompanyID = contacts.CompanyID WHERE ContactID='$ContactID'";

The goal is based upon a unique field call ContactID I want to be able to update the CompanyName associated with the ContactID.

For example: ContactID=1 might have an associated CompanyName= Smith, Inc. What I want to do is UPDATE/INSERT a new CompanyName and have that reflected by changing the CompanyID associated with the ContactID.

And I will now attempt to reply:

Okay, here's the best I can figure: You have two tables, $table_name1 (which holds people's information and has an auto-increment column called ContactID, or something like that) and $table_name2 (which holds company information, and has an auto-increment column called CompanyID as well as another column called ContactID, which holds an ID from $table_name1). When you insert a new person into your database, you also want to insert the company associated with it. Since there are currently no companies associated with this person you just now added, you only need to worry about getting their ID into the ContactID field of $table_name2.

Assuming that I've got all that right, here's what you can do:

if (mysql_query($sql1, $connection)){
$ContactID = mysql_insert_id($connection);
$result = mysql_query("INSERT INTO $table_name2 (CompanyName,WebSite,ContactID) VALUES ('$CompanyName','$WebSite','$ContactID')", $connection);
}

And, if you need CompanyID:

if ($result){
$CompanyID = mysql_insert_id($connection);
}

If you're interested, here's the manual page on mysql_insert_id():
http://www.php.net/manual/en/function.mysql-insert-id.php

Hope that helps.

-Reha

sfmnetsys

Okay, so I added the code that Reha suggested. I don't get any errors, but the data is not added to the table.

<?
error_reporting(15);

$db_name = "dbname";

$table_name1 = "contacts";
$table_name2 = "company";

$connection = @mysql_connect("localhost","username","password") or die ("couldn't connect");

$db = @mysql_select_db($db_name, $connection) or die ("Couldn't select database.");

$sql1 = "UPDATE $table_name1 (FirstName,LastName,WorkPhone, HomePhone,EmailName,Birthday)
VALUES
('[$FirstName]','[$LastName]','[$WorkPhone]','[$HomePhone]','[$EmailName]','[Birthday]')
";
if (mysql_query($sql1, $connection)){
$ContactID = mysql_insert_id($connection);
$result = mysql_query("INSERT INTO $table_name2 (CompanyName,WebSite,ContactID) VALUES ('$CompanyName','$WebSite','$ContactID')", $connection);
}

if ($result){
$CompanyID = mysql_insert_id($connection);
}

?>

kparker

Looks good to me.

kparker

Not quite: mysql_insert_id() returns the last auto-incremented value that is created on the current db connection. This will not be meaningful if the previous query is an UPDATE rather than an INSERT. Actually if your previous really is supposed to be an UPDATE, then you don't need mysql_insert_id() in the first place since you'll already be aware of which row it is in that case.

rsterbin

You aren't getting any errors because everything's enclosed in "if" structures. Try adding "else"s for error reporting:

if (some query) { blah blah...
} else {
echo "Query error:" . mysql_error();
}

-Reha

alastair

How do you avoid multiple IDs for the same company where you have more than one contact in a company?

If you are updating an existing contact you could rocess your information using ...
<?
$sql1="SELECT CompanyID from company WHERE CompanyName=".$CompanyName;

$result1 = @($sql1,$connection);

if (mysql_num_rows($result1)==0){
mysql_query(INSERT INTO company C(CompanyName,WebSite) VALUES ('[$CompanyName]','[$WebSite]')",$connection);
$CID=mysql_insert_id($connection);
}else{
$row=mysql_fetch_object($result1);
$CID = $row->CompanyID;
}

Then use $CID as the CompanyID for either your Insert (New contact) or Update (Existing Contact) statement.

Or have I missed the point (it can happen!)

sfmnetsys

That's a very good question. I am open to suggestions. I want to be able to tie data that is not contact specific to companies so I can't include company information in the contact table.

I used the code you sent me, but it still does not update the company table.

I get and error that:
Warning: Supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\do_modcontact.php on line 17

Line 17 is:
if (mysql_num_rows($result1)==0){

Code I inserted:

$sql1="SELECT CompanyID from company WHERE CompanyName=".$CompanyName;

$result1 = @($sql1,$connection);

if (mysql_num_rows($result1)==0){
mysql_query("INSERT INTO company(CompanyName,WebSite) VALUES ('[$CompanyName]','[$WebSite]')",$connection);
$CID=mysql_insert_id($connection);
}else{
$row=mysql_fetch_object($result1);
$CID = $row->CompanyID;
}

alastair

Thats because Company Name is a string

Try :

$sql1="SELECT CompanyID from company WHERE CompanyName='".$CompanyName."'";

With problems like these it is worth putting in debug assistance while testing

After the above statement if you simply
<?
echo $sql1;
?>
You will be able to see if you have a valid sql statement..

You can also use error trapping to ensure that any mistakes (or entries where Company Name is left blank) do not result in error messages of the kind you saw.

kparker

You can also use error trapping

That's far too weak a statement. A better wording would be to say "You absolutely MUST use error checking if you want to have any hope of a reliable site." (And while you're at it, get rid of the uninformative variable names such as $sql1):

$find_co_sql="SELECT CompanyID from company WHERE CompanyName = '".$CompanyName . "'";

if (!!$result = @( $find_co_sql,$connection)))
die "Error locating company: " . mysql_error();
elseif (($rowcount = mysql_num_rows($result1)) == 1)
list($CID) = mysql_fetch_row($result);
elseif ( $rowcount == 0 )
  {
  $add_co_sql = "INSERT INTO company(CompanyName,WebSite)
    VALUES ('[$CompanyName]','[$WebSite]')";

  if (! @( $add_co_sql,$connection))
    die "Error adding company: " . mysql_error();
  else
  $CID=mysql_insert_id($connection);
  }
else // rowcount > 1
  die "Panic! too many company matches, don't know what to do!";

Note several other things here:

Since the find-company query looks up a single column, and since all you do if you add a company is to retrieve the new ID, there's no reason to retrieve the data as on object only to assign the value to a scalar variable. Instead, just use list() so that mysql_fetch_row() can put the value directly into the variable you want.
My code makes some attempt to deal with what happens if there's more than one company with the same name. It's not necessarily an intelligent attempt; you might want to display all the matches and have the user select the desired individual.

alastair

I don't think you should comment on matters of style - to say things like "(And while you're at it, get rid of the uninformative variable names such as $sql1):" ; It assumes that the whole world wants to sing from your hymnsheet.

Style is just style and it is important that you and the people you work with agree some form of style. Some people use such 'uninformative variable names' as a matter of course so that they can easily navigate through code. Some people use extensive comments. Some people just assume that everyone else is ignorant.

What we are trying to do here is to help people to work out how to do things with php not to grumble about how their code is laid out.

sfmnetsys

Perhaps it makes more sense for me to create the company info seperately and allow for a drop-down box with CompanyName prepopulated which would then populate the assocaited company fields.

alastair

Sounds good to me..

kparker

Some people use such 'uninformative variable names' as a matter of course
so that they can easily navigate through code.

Wow--can I quote you on this? I'm especially intrigued by the suggestion that using generic names somehow makes things more readable. Maybe you think we should all go back to Tiny Basic and name things A1, A2, and so forth?