1

I am trying to run an query using php into a database, I need to check if the customers address is already exists then do not insert, but if not exists then insert into table, I have based it around the following:

$query = "INSERT INTO address(id,address_type)
   SELECT('1111','bill')FROM DUAL 
   WHERE NOT EXISTS 
      (SELECT * FROM address 
       WHERE id='1111' AND address_type='bill')";

$n = mysql_query($query, $connect ) or die(mysql_error());

This allows me to insert just the id but not the address_type. I will have the same id for both bill & ship addresses making a unique field not possible.

I have about 8 fields to insert, what am I doing wrong?

Deep Kakkar
  • 5,831
  • 4
  • 39
  • 75
Adam
  • 11
  • 3
  • What is the size of address_type field? I notice that you are trying to insert "billing" where as address_type is "bill" in the internal query. – Nagasimha Iyengar May 13 '15 at 12:20
  • Do you not have an autonumber field just so you can select a unique value? – Machavity May 13 '15 at 12:23
  • 3
    Your question doesn't make sense. You are inserting into one table but checking for existence in another. – Gordon Linoff May 13 '15 at 12:25
  • sorry edited to make more sense! address_type will only ever have either bill or ship in it. I have an Unique_id field that numbers each entry but unsure of how I could use that in this query – Adam May 13 '15 at 12:34
  • Error it gives is Operand should contain 1 column(s) – Adam May 13 '15 at 12:47
  • [Please avoid using `mysql_query()`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – Bacon Bits May 13 '15 at 12:55
  • I wouldn't expect the Operand should contian 1 column error with this SQL, since `SELECT * ` is permitted in an `EXISTS` subquery (as opposed to `SELECT 1` for example) but maybe some faulty parsing is happening because you are leaving out a lot of whitespace surrounding `()`. Add space after `address, SELECT, FROM` – Michael Berkowski May 13 '15 at 12:56
  • It does look like a better solution to what you're attempting would be to just use `INSERT IGNORE` and create a `UNIQUE` index across `(id, address_type)`. If the row already exists, a new one won't be inserted and MySQL won't report a duplicate key error. – Michael Berkowski May 13 '15 at 12:57
  • Thanks for the suggestion but unfortunately it still shows the same error. – Adam May 13 '15 at 13:02
  • I will look into INSERT IGNORE and see if that would be a more efficient way of doing this. – Adam May 13 '15 at 13:03
  • I have added an UNIQUE index across (id,address_type) and this seems to have created the desired affect using an normal INSERT INTO query. thank you for your help! :) – Adam May 13 '15 at 13:09
  • Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard May 13 '15 at 13:12

1 Answers1

0
$query = "INSERT INTO address(id,address_type)
   SELECT('1111','bill')FROM DUAL 
   WHERE NOT EXISTS 
      (SELECT * FROM address 
       WHERE id='1111' )";

remove address_type='bill' and check

Pang
  • 9,564
  • 146
  • 81
  • 122
Saravanan R
  • 21
  • 3