Would someone translate this PHP into MySQL?

  • DartHead
  • Beginner
  • Beginner
  • User avatar
  • Posts: 56
  • Loc: NYC

Post 3+ Months Ago

Is there a way to do this purely with MySQL? I'd like to only insert a record if a unique index value doesn't exist in the table:

Code: [ Select ]
$query = "select count(*) as itisexist from AA where BB='$var'";
$result = mysql_query($query);
$line = mysql_fetch_array($result, MYSQL_ASSOC)
if ($line['itisexist'] == 0 ) {
$query = "insert into AA set BB='$var'";
mysql_query($query);
}
  1. $query = "select count(*) as itisexist from AA where BB='$var'";
  2. $result = mysql_query($query);
  3. $line = mysql_fetch_array($result, MYSQL_ASSOC)
  4. if ($line['itisexist'] == 0 ) {
  5. $query = "insert into AA set BB='$var'";
  6. mysql_query($query);
  7. }


Thank you for your help.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

Add a unique key to the table and instead of inserting the duplicates, the server will return an error to whatever is attempting the insert.

Note that you will need to make sure there's no duplicate rows on your own before adding the unique key or else you're get an error during creation.

Code: [ Select ]
mysql> use test;
Database changed
 
mysql> CREATE TABLE `ukey`(
    -> aa INT(8) AUTO_INCREMENT PRIMARY KEY,
    -> bb VARCHAR(8)
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> INSERT INTO `ukey` (bb) VALUES ('one'),('two'),('three');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM `ukey`;
+----+-------+
| aa | bb    |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
+----+-------+
3 rows in set (0.00 sec)
 
mysql> ALTER TABLE `ukey` ADD UNIQUE KEY `uukey` (bb);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO `ukey` (bb) VALUES ('one');
ERROR 1062 (23000): Duplicate entry 'one' for key 2
 
mysql> SELECT * FROM `ukey`;
+----+-------+
| aa | bb    |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
+----+-------+
3 rows in set (0.00 sec)
 
  1. mysql> use test;
  2. Database changed
  3.  
  4. mysql> CREATE TABLE `ukey`(
  5.     -> aa INT(8) AUTO_INCREMENT PRIMARY KEY,
  6.     -> bb VARCHAR(8)
  7.     -> );
  8. Query OK, 0 rows affected (0.02 sec)
  9.  
  10. mysql> INSERT INTO `ukey` (bb) VALUES ('one'),('two'),('three');
  11. Query OK, 3 rows affected (0.00 sec)
  12. Records: 3  Duplicates: 0  Warnings: 0
  13.  
  14. mysql> SELECT * FROM `ukey`;
  15. +----+-------+
  16. | aa | bb    |
  17. +----+-------+
  18. |  1 | one   |
  19. |  2 | two   |
  20. |  3 | three |
  21. +----+-------+
  22. 3 rows in set (0.00 sec)
  23.  
  24. mysql> ALTER TABLE `ukey` ADD UNIQUE KEY `uukey` (bb);
  25. Query OK, 3 rows affected (0.02 sec)
  26. Records: 3  Duplicates: 0  Warnings: 0
  27.  
  28. mysql> INSERT INTO `ukey` (bb) VALUES ('one');
  29. ERROR 1062 (23000): Duplicate entry 'one' for key 2
  30.  
  31. mysql> SELECT * FROM `ukey`;
  32. +----+-------+
  33. | aa | bb    |
  34. +----+-------+
  35. |  1 | one   |
  36. |  2 | two   |
  37. |  3 | three |
  38. +----+-------+
  39. 3 rows in set (0.00 sec)
  40.  
  • DartHead
  • Beginner
  • Beginner
  • User avatar
  • Posts: 56
  • Loc: NYC

Post 3+ Months Ago

Thank you, that is helpful. I would just need to add an '@' to ignore the duplicate error, otherwise it'll print to screen, right? Is there a way to suppress it through sql?

Code: [ Select ]
 
$query = "INSERT INTO `ukey` (bb) VALUES ('one');";
@mysql_query($query);
 
  1.  
  2. $query = "INSERT INTO `ukey` (bb) VALUES ('one');";
  3. @mysql_query($query);
  4.  
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

Even with error_level set to E_ALL, mysql_query does not print the error number/message unless you ask for it with mysql_errno or mysql_error.

So no, you don't need to surpress errors with @.
  • DartHead
  • Beginner
  • Beginner
  • User avatar
  • Posts: 56
  • Loc: NYC

Post 3+ Months Ago

Hmm. It is printing errors for me, but it must be something else. I'll figure that part out. Thanks for your help, joebert.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

What kind of error are you getting?
  • DartHead
  • Beginner
  • Beginner
  • User avatar
  • Posts: 56
  • Loc: NYC

Post 3+ Months Ago

I'm getting that mysql error: ERROR 1062 (23000): Duplicate entry 'one' for key 2. I'm also working with Zen Cart so I'm using the zen db functions.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

I don't think you can have two values with the same key value...

KEY | VALUE
-----------
1 | Bob
2 | Joe
3 | Mary

Above is okay.. whereas:

KEY | VALUE
-----------
1 | Bob
2 | Joe
2 | Mary

Is not okay. At least that is how I'm reading that error... maybe someone more knowledged in this would be able to help you here.

Post Information

  • Total Posts in this topic: 8 posts
  • Users browsing this forum: No registered users and 92 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.