Problem with update

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Problem with update

Post 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.
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
Post Reply