[SOLVED]How to update 2 tables at once

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Cateyes
Forum Commoner
Posts: 63
Joined: Mon Jun 14, 2004 5:06 pm

[SOLVED]How to update 2 tables at once

Post by Cateyes »

OK I have 2 table one is meminfo the other memdata when I update id#3 on meminfo I want to update memdata on masterid #3 I have the script made but I don't believe I am using the where statement properly any help or hints would be appreciated. Here is the code

Code: Select all

<?php
<body background="../flash/images/backgrnd.gif"> 
<? 
// Upload processor script 
// At this point your script would determine what storage server to connect to 
// I'm just going to hardcode it here 
// mysql_select_db(storage1, admin, password); 
$Storage_IP = "localhost"; 
$Storage_Port = 3306; 
$Storage_User = "Admin"; 
$Storage_Passwd = "password"; 
$Storage_DB = "covfiles"; 
$picid = $_GET['recordID']; 
//$memalias = $_POST['alias']; 
//$memname = $_POST['name']; 
//$memprof = $_POST['profile']; 
//$memage = $_POST['age']; 
//$memweapon = $_POST['weapon']; 
//$memquote = $_POST['quote']; 
//$connectto = $Storage_IP . ":" . $Storage_Port; 


if (!$linkid = @mysql_connect($connectto, $Storage_User, $Storage_Passwd)) { 
   die("Unable to connect to storage server!"); 
} 

if (!mysql_select_db($Storage_DB, $linkid)) { 
  die("Unable to connect to storage database!"); 
} 

// Init values - these are used incase you want to upload multiple files, you just 
// add them to the source form as file1, file2, file3, etc. 
$STARTFILE = 1; 
$ONFILE = "file" . $STARTFILE; 

while (isset($HTTP_POST_FILES["$ONFILE"])) { 

  // Try! 
  $SrcPathFile = $HTTP_POST_FILES["$ONFILE"]["tmp_name"]; 
  $SrcFileType = $HTTP_POST_FILES["$ONFILE"]["type"]; 
  $DstFileName = $HTTP_POST_FILES["$ONFILE"]["name"]; 

  clearstatcache(); 
  $time = filemtime($SrcPathFile); 
  $storedate = date("Y-m-d H:i:s", $time); 

  // File Processing 
  if (file_exists($SrcPathFile)) { 

// Insert into file table 
   $SQL  = "update into meminfo (datatype, name, size, filedate) values ('"; 
    $SQL .= $SrcFileType . "', '" . $DstFileName . "', " . filesize($SrcPathFile); 
    $SQL .= ", '" . $storedate . "' where id = $picid)"; 
    
    
    if (!$RES = mysql_query($SQL, $linkid)) { 
      die("Failure on insert to Member table!"); 
    } 

   $fileid = mysql_insert_id($linkid); 
    
  
    // Insert into the filedata table 
    $fp = fopen($SrcPathFile, "rb"); 
    while (!feof($fp)) { 

      // Make the data mysql insert safe 
      $binarydata = addslashes(fread($fp, 65535)); 

      $SQL = "update into memdata (masterid, filedata) values ("; 
      $SQL .= $fileid . ", '" . $binarydata . "' where masterid = $picid)"; 
       
       
      
     if (!mysql_query($SQL, $linkid)) { 
        die("Failure to update picture!"); 
      } 
    } 

    fclose($fp); 
  } 

  $STARTFILE ++; 
  $ONFILE = "file" . $STARTFILE; 
} 


//echo "Upload Complete"; 
?> 
<div align="center"><font color="#CC6600" size="7">Update Complete</font></div>
?>
Last edited by Cateyes on Fri Jul 02, 2004 8:07 am, edited 1 time in total.
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post by ol4pr0 »

Code: Select all

#shouldnt that be
("UPDATE tablename SET fieldname='".['something1'] ."', and the next and next... WHERE something=something)
Last edited by ol4pr0 on Thu Jul 01, 2004 11:59 pm, edited 1 time in total.
Cateyes
Forum Commoner
Posts: 63
Joined: Mon Jun 14, 2004 5:06 pm

Post by Cateyes »

Well made that change but it still doesn't update acts like it does but doesn't. here is the new code

Code: Select all

<?php
<body background="../flash/images/backgrnd.gif">
<? 
// Upload processor script 
// At this point your script would determine what storage server to connect to 
// I'm just going to hardcode it here 
// mysql_select_db(storage1, Admin, password);
$Storage_IP = "localhost"; 
$Storage_Port = 3306; 
$Storage_User = "Admin"; 
$Storage_Passwd = "password"; 
$Storage_DB = "covfiles"; 
$picid = $_GET['recordID'];
//$memalias = $_POST['alias'];
//$memname = $_POST['name'];
//$memprof = $_POST['profile'];
//$memage = $_POST['age'];
//$memweapon = $_POST['weapon'];
//$memquote = $_POST['quote'];
//$connectto = $Storage_IP . ":" . $Storage_Port; 


if (!$linkid = @mysql_connect($connectto, $Storage_User, $Storage_Passwd)) { 
	die("Unable to connect to storage server!"); 
} 

if (!mysql_select_db($Storage_DB, $linkid)) { 
  die("Unable to connect to storage database!"); 
} 

// Init values - these are used incase you want to upload multiple files, you just 
// add them to the source form as file1, file2, file3, etc. 
$STARTFILE = 1; 
$ONFILE = "file" . $STARTFILE; 

while (isset($HTTP_POST_FILES["$ONFILE"])) { 

  // Try! 
  $SrcPathFile = $HTTP_POST_FILES["$ONFILE"]["tmp_name"]; 
  $SrcFileType = $HTTP_POST_FILES["$ONFILE"]["type"]; 
  $DstFileName = $HTTP_POST_FILES["$ONFILE"]["name"]; 

  clearstatcache(); 
  $time = filemtime($SrcPathFile); 
  $storedate = date("Y-m-d H:i:s", $time); 

  // File Processing 
  if (file_exists($SrcPathFile)) { 

 // Insert into file table 
   $SQL  = "UPDATE meminfo SET(datatype, name, size, filedate) values ('"; 
    $SQL .= $SrcFileType . "', '" . $DstFileName . "', " . filesize($SrcPathFile); 
    $SQL .= ", '" . $storedate . "' where id = $picid)"; 
	
	
    if (!$RES = mysql_query($SQL, $linkid)) { 
      die("Failure on insert to Member table!"); 
    } 

	$fileid = mysql_insert_id($linkid);
    
  
    // Insert into the filedata table 
    $fp = fopen($SrcPathFile, "rb"); 
    while (!feof($fp)) { 

      // Make the data mysql insert safe 
      $binarydata = addslashes(fread($fp, 65535)); 

      $SQL = "UPDATE memdata SET(masterid, filedata) values ("; 
      $SQL .= $fileid . ", '" . $binarydata . "' where masterid = $picid)"; 
		
		
      
	  if (!mysql_query($SQL, $linkid)) { 
        die("Failure to update picture!"); 
      } 
    } 

    fclose($fp); 
  } 

  $STARTFILE ++; 
  $ONFILE = "file" . $STARTFILE; 
} 
 

//echo "Upload Complete"; 
?> 
<div align="center"><font color="#CC6600" size="7">Update Complete</font></div>

?>
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post by ol4pr0 »

oke i will try to explain you .
you try to update the values hwoever you using it like ure about to make a insert.

so.

Code: Select all

$SQL = ("UPDATE memdata  SET(masterid=0012, filedate=somedata where masterid='.$picid.'");
                                               '.$_POST['newmasterid'] .' ect.....
As you can see in that example as obove it is actually replacing a value where it needs to do.. i this case it SETS a new masterid and filedata where masterid =something
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

are you aware you're trying to update the masterid for which it won't change? Unless you're doing an insert, which in this script, you aren't.
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post by ol4pr0 »

lol, now that was stupid of me that i didnt even notice that one..
Cateyes
Forum Commoner
Posts: 63
Joined: Mon Jun 14, 2004 5:06 pm

Post by Cateyes »

Basically the id from meminfo and the masterid from memdata never change just the other fields. And I didn't even relize I forgot to remove the masterid from the update field.
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post by ol4pr0 »

Well in the query as you have it above you want to change the very same masterid as you are using for a reference.
so using the example query above but without changing the masterid, than it should work just fine.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

there's no reason to update masterid then is there? So just do something like:

Code: Select all

<?php

$SQL  = "UPDATE `meminfo` SET `datatype` = '{$SrcFileType}', `name` = '{$DstFileName}', `size` = '".filesize($SrcPathFile)."', `filedate` = '{$storedate}' WHERE `id` = '{$picid}'";
mysql_query($SQL) or die(mysql_error());

if(mysql_affected_rows())
{
  $data = addslashes(file_get_contents($SrcPathFile));
  $SQL = "UPDATE `memdata` SET `filedata` = '{$data}' WHERE `masterid` = '{$picid}'";
  mysql_query($SQL) or die(mysql_error());
  if(!mysql_affected_rows())
    die('no member data updated.');
}

?>
Cateyes
Forum Commoner
Posts: 63
Joined: Mon Jun 14, 2004 5:06 pm

Post by Cateyes »

Well Thanks again guys that works great here is the finished code if anyone is interested almost 40 lines shorter :)

Code: Select all

<?php
<?
$Storage_IP = "localhost"; 
$Storage_Port = 3306; 
$Storage_User = "Admin"; 
$Storage_Passwd = "password"; 
$Storage_DB = "covfiles"; 
$picid = $_GET['recordID'];
$connectto = $Storage_IP . ":" . $Storage_Port; 
if (!$linkid = @mysql_connect($connectto, $Storage_User, $Storage_Passwd)) { 
	die("Unable to connect to storage server!"); 
} 
if (!mysql_select_db($Storage_DB, $linkid)) { 
  die("Unable to connect to storage database!"); 
} 
$ONFILE = "pic1"; 

//while (isset($HTTP_POST_FILES["$ONFILE"])) { 

  // Try! 
  $SrcPathFile = $HTTP_POST_FILES["$ONFILE"]["tmp_name"]; 
  $SrcFileType = $HTTP_POST_FILES["$ONFILE"]["type"]; 
  $DstFileName = $HTTP_POST_FILES["$ONFILE"]["name"]; 

  clearstatcache(); 
  $time = filemtime($SrcPathFile); 
  $storedate = date("Y-m-d H:i:s", $time); 

  // File Processing 
  if (file_exists($SrcPathFile)) { 

 // Insert into file table 
 //  $SQL  = "UPDATE meminfo SET(datatype='.$SrcFileType.', name='.$DstFileName.', size='.filesize($SrcPathFile).', filedate='.$storedate.' where id = '.$picid.')"; 
$SQL  = "UPDATE `meminfo` SET `datatype` = '{$SrcFileType}', `name` = '{$DstFileName}', `size` = '".filesize($SrcPathFile)."', `filedate` = '{$storedate}' WHERE `id` = '{$picid}'"; 
mysql_query($SQL) or die(mysql_error());	
if(mysql_affected_rows()) 
{ 
  $data = addslashes(file_get_contents($SrcPathFile)); 
  $SQL = "UPDATE `memdata` SET `filedata` = '{$data}' WHERE `masterid` = '{$picid}'"; 
  mysql_query($SQL) or die(mysql_error()); 
  if(!mysql_affected_rows()) 
    die('no member data updated.');
	} 
		}
?>
<body background="../../Documents%20and%20Settings/Darrell/My%20Documents/COV%20Website/flash/images/backgrnd.gif">
<div align="center"><font color="#CC6600" size="7">Update Complete</font></div>

?>
Post Reply