Page 1 of 1

Problem with update

Posted: Sun Nov 05, 2006 2:01 pm
by genista
Hi all,

As you can see from the following script I am pulling data from 2 tables to upload an image, then update one of the tables with the image details. The problem I have is that the last query:

$query = "UPDATE
`supplierimages` as si,
suppliers as s,
SET
`si.image1 = '$image1',
`si.username` = '$id'
WHERE
`s.supplierid` = si.supplierid
AND s.username = '$id'";

Is not working, ie when I echo it it comes out like:

UPDATE `supplierimages` as si, `suppliers` as s SET `image1` = 'image.jpg', si.username = '' WHERE s.username = ''

As you can see the image details is echo'd but any of the other data is not hence why my table is not being updated, any ideas?

Code: Select all

<?php 
session_start();
include_once ("../../private/supplierconfig.php"); 
checkLoggedIn("yes");
error_reporting (E_ALL); 

//Retrieve details from database to start (image1 also part of suppliers table - need to have this only )

$id = $_SESSION['username'];
echo $id;
/*$query1 = "SELECT username FROM suppliers WHERE username = '$id'";
echo $query1;
        $result=mysql_query($query1, $link) or die("MySQL query $query1 failed.  Error if any: ".mysql_error());*/
/*$query = "select username, supplierid, image1 from suppliers where username='$id'";*/

$query = "SELECT s.username, si.image1
FROM suppliers AS s
INNER JOIN supplierimages AS si ON s.username = si.username
WHERE s.username = '$id'";

        $result=mysql_query($query, $link) or die("MySQL query $query failed.  Error if any: ".mysql_error());

echo $query;
    //get the first (and only) row from the result 
    $row = mysql_fetch_array($result, MYSQL_ASSOC); 

  $username=$row['username'];
  $image1 = $row['image1'];
  
   
       
      
      if(isset( $submit )) 
{      

//If the Submitbutton was pressed do: 
 
if ($_FILES['imagefile']['type'] == "image/jpeg"){ 


 
 $_FILES['imagefile']['name'] = basename($_FILES['imagefile']['name']);

copy ($_FILES['imagefile']['tmp_name'], "files/".$username.$_FILES['imagefile']['name'])
    or die ("Could not copy"); 
     

echo "";  
        echo "Name: ".$_FILES['imagefile']['name']."";  
        echo "Size: ".$_FILES['imagefile']['size']."";  
        echo "Type: ".$_FILES['imagefile']['type']."";  
        echo "Upload Complete...."; 

      
     
        $image1 = $username.$_FILES['imagefile'] ['name'];
        
        echo "$image1"; 
        
        /*$query = "UPDATE `supplierimages` 
              SET `image1` = '$image1', `username` = '$username' WHERE `username` = '". mysql_real_escape_string($_SESSION['username']). "' 
              LIMIT 1";*/
        
$query = "UPDATE 
`supplierimages` as si,
suppliers as s,
SET 
`si.image1 = '$image1', 
`si.username` = '$id' 
WHERE
`s.supplierid` = si.supplierid 
AND s.username = '$id'"; 
      /* $query = "UPDATE `supplierimages` as si
       SET `si.image1` = '$image1', `si.username` = '$id' INNER JOIN `suppliers` as s ON `s.supplierid` = si.supplierid WHERE s.username = '$id'";*/
       
    /*   s.username, si.image1
FROM suppliers AS s
INNER JOIN supplierimages AS si ON s.supplierid = si.supplierid
WHERE s.supplierid = '$id'";*/

/*$query = "UPDATE `suppliers` 
              SET `image1` = '$image1' WHERE `username` = '". mysql_real_escape_string($_SESSION['username']). "' 
              LIMIT 1";
              $result = mysql_query($query, $link) or die('Update failed: ' . mysql_error()); */
       
            
              
              $result = mysql_query($query, $link) or die('Update failed: ' . mysql_error()); 
echo $query; 
//print_r($query);  
 mysql_info($link) ; 
    if(mysql_affected_rows($link) == 0);  
        
        } 
}
 else { 
            echo "<br><br>"; 

        } 
 

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN "http://www.w3.org/TR/html4/strict.dtd"> 
   <html lang="en"> 
    <form name="form1" method="post" action="#" enctype="multipart/form-data">
    <input type=text name="username" value="<?php echo $username; ?>"> 
    <input type="file" name="imagefile"> 
    <p>*</td><td><input name="submit" type="submit" value="Submit"></p> 
    </table> 
   </form>
Edited - tidied up.

Posted: Sun Nov 05, 2006 3:41 pm
by printf
Your missing ` after -> si.image1!

That's why I never use them things unless I need to, which should never be the case, if you don't use reserved words for table and field names! Why do most people use them, because they see others use them, and that's the only reason 9 out of 10 times!

printf

Posted: Mon Nov 06, 2006 2:02 pm
by genista
Ah yes, I have fixed that, but still no joy, it is still echo'ing out the same, could this be a PHP problem?

Posted: Mon Nov 06, 2006 2:58 pm
by volka
maybe you should let the script start with error_reporting(E_ALL) so you can see all errors.

Code: Select all

error_reporting (E_ALL);
ini_set('display_errors', true);

session_start();
include_once ("../../private/supplierconfig.php");
checkLoggedIn("yes");

//Retrieve details from database to start (image1 also part of suppliers table - need to have this only )

$id = $_SESSION['username'];
echo '<p>username/id: ', htmlentities($id), "<p>\n";

...
what does that print?