2VmwagD7

1CREATE TABLE `transcripts` (
2`ensembl_transcript_id` varchar(20) NOT NULL,
3`transcript_chrom_start` int(10) unsigned NOT NULL,
4`transcript_chrom_end` int(10) unsigned NOT NULL,
5PRIMARY KEY (`ensembl_transcript_id`)
6) ENGINE=InnoDB DEFAULT CHARSET=latin1;
7	
8REPLACE INTO `transcripts`
9SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
10`transcript_chrom_start` = 12345,
11`transcript_chrom_end` = 12678;
12	
13INSERT IGNORE INTO `transcripts`
14SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
15`transcript_chrom_start` = 12345,
16`transcript_chrom_end` = 12678;
17	
18INSERT INTO `table` (value1, value2) 
19SELECT 'stuff for value1', 'stuff for value2' FROM `table` 
20WHERE NOT EXISTS (SELECT * FROM `table` 
21      WHERE value1='stuff for value1' AND value2='stuff for value2') 
22LIMIT 1
23	
24INSERT INTO `table` (value1, value2) 
25SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
26WHERE NOT EXISTS (SELECT * FROM `table` 
27      WHERE value1='stuff for value1' AND value2='stuff for value2') 
28LIMIT 1
29	
30INSERT INTO table (a,b,c) VALUES (1,2,3)
31  ON DUPLICATE KEY UPDATE c=c+1;
32
33UPDATE table SET c=c+1 WHERE a=1;
34	
35INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
36    [INTO] tbl_name [(col_name,...)]
37    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
38    [ ON DUPLICATE KEY UPDATE
39      col_name=expr
40        [, col_name=expr] ... ]
41	
42INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
43    [INTO] tbl_name
44    SET col_name={expr | DEFAULT}, ...
45    [ ON DUPLICATE KEY UPDATE
46      col_name=expr
47        [, col_name=expr] ... ]
48	
49INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
50    [INTO] tbl_name [(col_name,...)]
51    SELECT ...
52    [ ON DUPLICATE KEY UPDATE
53      col_name=expr
54        [, col_name=expr] ... ]
55	
56REPLACE INTO `transcripts`
57SET `ensembl_transcript_id` = 'ENSORGT00000000001',
58`transcript_chrom_start` = 12345,
59`transcript_chrom_end` = 12678;
60	
61function insert_unique($table, $vars)
62{
63  if (count($vars)) {
64    $table = mysql_real_escape_string($table);
65    $vars = array_map('mysql_real_escape_string', $vars);
66
67    $req = "INSERT INTO `$table` (`". join('`, `', array_keys($vars)) ."`) ";
68    $req .= "SELECT '". join("', '", $vars) ."' FROM DUAL ";
69    $req .= "WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";
70
71    foreach ($vars AS $col => $val)
72      $req .= "`$col`='$val' AND ";
73
74    $req = substr($req, 0, -5) . ") LIMIT 1";
75
76    $res = mysql_query($req) OR die();
77    return mysql_insert_id();
78  }
79
80  return False;
81}
82	
83<?php
84insert_unique('mytable', array(
85  'mycolumn1' => 'myvalue1',
86  'mycolumn2' => 'myvalue2',
87  'mycolumn3' => 'myvalue3'
88  )
89);
90?>
91	
92IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
93  UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
94ELSE
95BEGIN
96  INSERT INTO beta (name) VALUES ('John')
97  INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
98END
99	
100// Check if exist cod = 56789
101include "database.php";
102
103$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
104$countrows = mysql_num_rows($querycheck);
105if($countrows == '1')
106{
107  // Exist 
108}
109else
110{
111 // .... Not exist
112}
113	
114// Check if exist cod = 56789
115include "database.php";
116
117$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
118$countrows = mysql_num_rows($querycheck);
119while($result = mysql_fetch_array($querycheck))
120{
121    $xxx = $result['xxx'];
122    if($xxx == '56789')
123    {
124      // Exist
125    }
126    else
127    {
128      // Not exist
129    }
130}
131	
132UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
133INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);