INSERT INTO Query

[halifaxer]

Struggling with something.

Have an INSERT INTO statement for my base but I'm trying to add a WHERE clause to it and it's not happening for me. I think because I'm trying to use NOT LIKE and I'm not sure how to use it. ie.


'INSERT INTO database WHERE name NOT LIKE "blue"';

Do I need to use %?

Please help!

[thetestingsite]

I was always under the impression that the WHERE clause does not work with the INSERT clause. The MySQL Dev site does not say anything about it either.

http://dev.mysql.com/doc/refman/5.1/en/insert.html

What exactly are you trying to accomplish?

[halifaxer]

Sure, the query basically is:



foreach ($xml->xpath('//prod') as $character) {

$name = $character["name"];

mysql_query("
INSERT INTO xml (
name)
VALUES (
'$name')
WHERE name NOT LIKE 'Tom'") or die(mysql_error());

}


In other words using a foreach statement, run through a list of names and only "insert" ones that aren't like Tom.

[thetestingsite]

I think your best bet would be to use an if conditional statement to check if $name or $character['name'] is not equal to the name "Tom"( if (strtolower($name) != 'tom') ) then execute the insert statement in the mysql_query. Let me know if you need to me clarify this more.

Hope this helps.

[traq]

I think you need to use UPDATE instead of INSERT. This is a name column that already holds a "name" value, is it not (I assume so, since we're checking against it)?
[not tested]


<?php
foreach ($xml->xpath('//prod') as $character) {

$name = $character["name"];

mysql_query("UPDATE xml SET name = '$name' WHERE name NOT LIKE 'Tom'") or die(mysql_error());

}

however, I suspect this won't do what you expect. (I don't think your original code would, either, if it did work.) It would loop through all the new names, but each UPDATE would be performed on the entire table. So, you'd end up with everyone's name being set to the name of the last character to go through the foreach loop (except for the characters named Tom, of course).

Try incorporating thetestingsite's suggestion - compare the existing name first, then make a more specific replacement:
[not tested]


<?php
foreach ($xml->xpath('//prod') as $character) {

if(strtolower($character['name']) != 'tom'){
$name = $character["name"];
mysql_query("UPDATE xml SET name = '$name' WHERE id = '$character['id']'") or die(mysql_error());
}
}

[JShor]

I don't know why, but it's a really common problem for people to use WHERE in INSERT clauses [this is incorrect of course].