[SOLVED] insert where

[Chris.P]

I'm trying to get this script I have to insert values into a table but only where the value of the id row matches what I have in a variable.

 

Script I had was this

$query = "INSERT INTO images (name, size, type, path ) ".
"VALUES ('$fileName', '$fileSize', '$fileType', '$filePath')";

 

I presumed I would be able to tag the WHERE parameter on at the end with the variable like so.

$query = "INSERT INTO images (name, size, type, path ) ".
"VALUES ('$fileName', '$fileSize', '$fileType', '$filePath') WHERE id = '$bandid'";

 

But apparently not as I get this

 

Error, query failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = '5'' at line 1

[bubblegum.anarchy]

Hmmm... an INSERT....SELECT might work:

 

INSERT INTO images (name, size, type, path) 
     SELECT $fileName, $fileSize, $fileType, $filePath FROM images 
     WHERE id = $bandid

 

 

[AndyB]

You might find it helpful to generate useful error messages when executing queries. For example, the code below might point you in the right direction for solving the problem:

 

$query = " ... your query string ...";
$result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query); // useful debug

[Chris.P]

You might find it helpful to generate useful error messages when executing queries. For example, the code below might point you in the right direction for solving the problem:

 

$query = " ... your query string ...";
$result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query); // useful debug

 

Noted thanks although I'm not too sure how to do that with a result variable like this:

 

$result = move_uploaded_file($tmpName, $filePath);

[Chris.P]

Hmmm... an INSERT....SELECT might work:

 

INSERT INTO images (name, size, type, path) 
     SELECT $fileName, $fileSize, $fileType, $filePath FROM images 
     WHERE id = $bandid

 

 

 

Just trying this out now although I'm getting this error:

 

Error, query failed : Operand should contain 1 column(s)

 

Does this basically mean nothing was selected matching my query?

 

Heres the full query I'm now using.

 

$query = "INSERT INTO images (name, size, type, path ) ".
"SELECT ('$fileName', '$fileSize', '$fileType', '$filePath') FROM images WHERE id = '$bandid'";

[bubblegum.anarchy]

There is probably an issue with the parenthesis used around the select columns.

[fenway]

There is probably an issue with the parenthesis used around the select columns.

i.e. they're not supposed to be there at all.

[Chris.P]

Cheers, I removed them and it works fine except it inserts a new row rather than inserting into the row with the selected id. I can't see why this is happening although I'm sure there is a simple explanation.  ???

 

edit heres the full script being adopted if it helps:

<?php
include_once  'functions.php';
loginDetails();
$uploadDir = 'image_upload/';
$bandid = $_SESSION['bandid'];

if(isset($_POST['upload']))
{
$fileName = $_FILES['userfile']['name'];
$tmpName = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

// get the file extension first
$ext = substr(strrchr($fileName, "."), 1);

// make the random file name
$randName = md5(rand() * time());

// and now we have the unique file name for the upload file
$filePath = $uploadDir . $randName . '.' . $ext;

$result = move_uploaded_file($tmpName, $filePath);
if (!$result) {
echo "Error uploading file";
exit;
}

if(!get_magic_quotes_gpc())
{
$fileName = addslashes($fileName);
$filePath = addslashes($filePath);
}

$query = "INSERT INTO images (name, size, type, path ) ".
"SELECT '$fileName', '$fileSize', '$fileType', '$filePath' FROM images WHERE id = $bandid";

mysql_query($query) or die('Error, query failed : ' . mysql_error());

echo "<br>Completed<br>";

}
?>

[fenway]

I'm not sure what you mean -- you obviously can't have a UID collision?

[Chris.P]

There is already a user id in the images field that is auto incremented ever time someone signs up. All I want to do is put in the information from the upload script into the row with the matching user id (bandid) from the session.

 

Should I be using an UPDATE WHERE instead of this or am I completely off track? Apologies if I'm way off I'm new to all this. 

 

[Chris.P]

Just solved it! It was indded an update I needed and all is working well! Thanks for the help.

 

This was the solution.

 

$query = "UPDATE images SET name = '$fileName', size = '$fileSize', type = '$fileType', path = '$filePath' WHERE id = $bandid";