[RESOLVED] Updating Strings in Multiple Tables

[swatisonee]

Hi.

1 issue:

a. I have to update my addressbook when a company changes name ( eg: XYZ Inc. becomes XYZ Systems Inc) and I do this using

$sqlb = "UPDATE `Adbk` SET `Company`='$company' WHERE `Cid` = $cid ";

b. I also have to simutaneously update the Shipping Table so that the new name gets reflected there. I tried the foll. two options both of which dont work .

$sqlb = "UPDATE `Shipping` SET `Company`='$company'  WHERE `Company` LIKE '%$company%' "; 

or

 $sqla = "SELECT * FROM  `Shipping` WHERE  `Company` LIKE '%$company%' " ;
 echo $sqla;

 $resulta = mysql_query($sqla) or die (mysql_error());

 if ($myrowa=mysql_fetch_array($resulta))  {
 do
 {
 $sid = $myrowa["Sid"];
  }
 $sqlb = "UPDATE `Shipping` SET `Company`='$company' WHERE `Sid` = $sid ";
 echo $sqlb;

 $resultb = mysql_query($sqlb) or die (mysql_error());
  } while ($myrowa= mysql_fetch_array($resulta));
  }

c. I guess i am using the wrong function/syntax but I dont know what to use instead of LIKE.

d. If I split $company to XYZ and Inc and then try to replace XYZ with XYZ Systems, I run the risk of altering a completely different record ie XYZ Designs Inc.

Thanks in advance,
Swati

[bodzan]

...and it is not flexible enough so that you can manipulate your data as you wish...

I would suggest you change the database structure, of course if you are able to, to something like...

Table 1 structure:
id | CompanyName | CompanyDesc | etc

with data like:
1 | Company 1 | Some company | etc
2 | Company 2 | Some other company | etc
etc

Table 2 structure:
id | cid | Orders | etc

with data like:
1 | 1 | Order 1 | etc
2 | 1 | Order 2 | etc
3 | 2 | Order 3 | etc
etc

Where cid in the second table would be the id of the first table or Foreign key to the Table 2...
So in the example would be that Company 1 with id 1 has two orders with id 1 and 2 and company 2 with id 2 one order with id 3..
That way you can make a very flexible database that you can stretch and modify virtually indefinitelly...
With that structure you would have to change the name once in the main table for every table that has company's id as a foreign key...

I hope I'm making sense and it is usefull to you...

[swatisonee]

Eminent Sense but as of now i can only create a column called cid in Table 2 and that will help for future records. How do i resolve the current issue though ?

[bodzan]

For immediate help on the matter I would suggest to get the old value from the Adbk table first then update it and then do the loop like this:

$sqla = "SELECT * FROM  `Shipping` WHERE  `Company` = '$oldCompany' " ;
$resulta = mysql_query($sqla) or die (mysql_error());
if(mysql_num_rows($resulta)>0){
     while($myrowa = mysql_fetch_assoc($resulta)){
          $sqlb = "UPDATE `Shipping` SET `Company`='$company' WHERE `Sid` = '$myrowa[Sid]'";
     }
}

Where $oldCompany is the name retrieved from the Adbk table BEFORE renaming it and $company is the new one...

Try that... I hope I was more helpful this time...

[swatisonee]

Thanks a ton. I had to tweak a bit but now got the replacement going but now a related issue has cropped up. I select the customer name from the customer table to populate Shipping using the foll.

So :

a) How do i ensure that both customerid(field name : cid) and company name(field name : company) get posted to the form processing page ?

Form 1:

Customer:<td><select name="customer"><option value="">[Select One]
      <?php

mysql_connect("localhost", $dbname, $dbpasswd )
    	or die ("Unable to connect to server.");

mysql_select_db($database)
    	or die ("Unable to select database.");

$result = mysql_query("SELECT DISTINCT `Company` FROM `Adbk` ORDER BY `Company` asc ");

if ($myrow = mysql_fetch_array($result)) {

  do {

printf("<option>
  %s", $myrow["Company"]);

  } while ($myrow = mysql_fetch_array($result));

} 
?></select></td>

Form2 :

<?php

$customer = $_POST["customer"];

mysql_connect("localhost", $dbname, $dbpasswd )
    	or die ("Unable to connect to server.");

mysql_select_db($database)
    	or die ("Unable to select database.");

$sql = "INSERT INTO `Shipping` (`Company` and so on ) VALUES ('$customer')

and so on...
?>

I want both values $cid and $customer to get posted to the form processing page.

Thanks.

[bodzan]

...modify your query a bit...

$result = mysql_query("SELECT DISTINCT `Company`,`cid` FROM `Adbk` ORDER BY `Company` asc "); 

[swatisonee]

Umm.. this is throwing up an error :

$result = mysql_query("SELECT DISTINCT `Company`, `Customerid` FROM `Adbk` ORDER BY `Company` asc "); 

if ($myrow = mysql_fetch_array($result)) { 

  do { 

printf("<option> 
  %s", $myrow["Company"],   "<option> 
  %s", $myrow["Customerid"], ); 

  } while ($myrow = mysql_fetch_array($result)); 

} 

[bodzan]

...does it throwing you?

[swatisonee]

A blank page - but i checked all my {} are ok as also my <? >?... But the result statement that i made is correct isnt it ?

[bodzan]

Did you check the source of the page?