Insert Query Error

[mj99]

I am trying to run this query

$query = "INSERT INTO breed_to_attribute (att_id,breed_id)
VALUES ( SELECT 'att_id','$id' FROM 'breed_to_attribute' WHERE 'breed_id' = '$template' )";

and keep getting this error

Error: 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 'SELECT 'att_id','78' FROM 'breed_to_attribute' WHERE 'breed_id' = '71' )' at line 2

This is the basic mysql ststement, which runs fine in phpmyadmin

insert into breed_to_attribute
(att_id,breed_id)
select att_id,'77' FROM breed_to_attribute WHERE breed_id='71'

The query populates a new row with all the values from an existing row while changing the id.

What am I doing wrong? PHP does not seem to want to stop at the end of the query I guess or something.

[bpat1434]

You're missing the second column's value.... have your SELECT statement return just the att_id and then manually set the second column to '77':

INSERT INTO `breed_to_attribute` (`att_id`, `breed_id`)
VALUES (
    (SELECT `att_id`
    FROM `breed_to_attribute`
    WHERE `breed_id` = '$template'), '$id')

[mj99]

Now I get this

Error: You can't specify target table 'breed_to_attribute' for update in FROM clause

[bpat1434]

Oh, oops. Haha... yeah, you can't do that. You have to get a list manually and then do an INSERT statement. MySQL won't allow you to read data from a subquery and then modify (update, delete, insert) data into the same table you're reading your subquery from.

So you have to take the subquery out, and run it first, and get your response. Then use that response in your second query.

$subquery = "SELECT `att_id`
         FROM `breed_to_attribute`
         WHERE `breed_id` = '$template'";
$result = mysql_query($subquery) or die(mysql_error());
$att_id = mysql_result($result, 0, 'att_id');
mysql_free_result($result);
$query = "INSERT INTO `breed_to_attribute` (`att_id`, `breed_id`)
         VALUES ('$att_id', '$id')";
$result = mysql_query($query) or die(mysql_error());

[mj99]

That is very close, It does actually insert the first value twice but does not insert all the existing values.

[bpat1434]

That's because I'm not looping through the results of the first SELECT query.

For that you'd have to do something like:

$subquery = "SELECT `att_id`
         FROM `breed_to_attribute`
         WHERE `breed_id` = '$template'";
$result = mysql_query($subquery) or die(mysql_error()); 
while($row = mysql_fetch_assoc($result))
{
    $att_ids[] = $row['att_id'];
}
@mysql_free_result($result);

foreach($att_ids as $att_id)
{
    $query = "INSERT INTO `breed_to_attribute` (`att_id`, `breed_id`)
         VALUES ('$att_id', '$id')";
    $result = mysql_query($query) or die(mysql_error());

if(!$result) { $errors[] = $att_id; }
}

if(isset($errors) && !empty($errors))
{
    echo 'The following att_id's weren\'t able to be updated:<br />';
    implode('<br />', $errors);
}
else
{
    echo 'Inserted successfully.';
}
?>

[mj99]

That got it. There sure is a lot more to running a query like that using PHP than I thought.

Thanks so much.

[bpat1434]

Well, you started with a fairly complex query. Simpler queries are easier to do (i.e. the first one) but the more dynamic you get, the more and more stored procedures can be your friend (especially if you're doing the same thing over and over again).