Jump to content

[SOLVED] insert where


Chris.P

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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'";

Link to comment
Share on other sites

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>";

}
?>

Link to comment
Share on other sites

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.  :-[

 

Link to comment
Share on other sites

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";

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.