Page 1 of 1

[SOLVED]How to update 2 tables at once

Posted: Thu Jul 01, 2004 11:47 pm
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>
?>

Posted: Thu Jul 01, 2004 11:54 pm
by ol4pr0

Code: Select all

#shouldnt that be
("UPDATE tablename SET fieldname='".['something1'] ."', and the next and next... WHERE something=something)

Posted: Thu Jul 01, 2004 11:59 pm
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>

?>

Posted: Fri Jul 02, 2004 12:02 am
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

Posted: Fri Jul 02, 2004 12:05 am
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.

Posted: Fri Jul 02, 2004 12:07 am
by ol4pr0
lol, now that was stupid of me that i didnt even notice that one..

Posted: Fri Jul 02, 2004 12:09 am
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.

Posted: Fri Jul 02, 2004 12:12 am
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.

Posted: Fri Jul 02, 2004 12:24 am
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.');
}

?>

Posted: Fri Jul 02, 2004 8:06 am
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>

?>