Problem with update query

[genista]

Hopefully this should be a quick fix...

The first query in my script pulls information from two tables, one of which could be empty:

[code=php:0]
$query = "SELECT s.username, si.image1
FROM suppliers AS s
INNER JOIN supplierimages AS si ON s.username = si.username
WHERE s.username = '$id'";
[/code]

$id = username (part of the session). Now, the problem I am having is when I need to update the supplierimages table with a username, supplierid and the details of the image:

[code=php:0]
$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'";
[/code]


I get an error on this query:

"check the manual that corresponds to your MySQL server version for the right syntax to use near 'si.username` = '' INNER JOIN `suppliers` as s ON `s.supplierid` = si.supplierid ' at line 2"

Any ideas?


Thanks,

G

[Psycho]

You don't have a quote mark (`) at the end of [b]si.image1[/b] right after the SET

[genista]

Ok I have changed that but still no joy, here is the complete code that I have right now which may help answer:

[code=php:0]
<?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>
[/code]

Excuse all the comments, have been ttrying numerous ways to get this to work. The interesting thing is that when this query is run the $id field is blank, when it should contain the session's username.

Thanks.

[fenway]

And what error message are you getting now?