php sql question: use of IF EXISTS

Question

I am struggling to get the proper syntax for a SQL query. I have looked through all I can but cant seem to get anything that works.

I want to check to see if a record is in the database e.g.

SELECT Date,Time FROM loadings WHERE Date='21-06-2011' AND Time ='7'

If it does then I can update it, if it doesnt I need to add it first before I can cary out some operations.

I tried

[php]

IF EXISTS(SELECT Date,Time FROM loadings WHERE Date='21-06-2011' AND Time ='7')
{
// Record exists do nothing
}
else
{
// Insert record
INSERT Date,Time INTO loadings VALUES Date='21-06-2011' AND Time ='7'
}

[/php]

Can anyone guide me on the correct syntax to use.

By the way just founf this site and reading through seems excellent. Particularly the suggestions given from previos submissions to answer the question. I look through all them but didn't find my answer.

Regards Trevor

If you use MySQL, try the questions from this page: http://stackoverflow.com/search?q=mysql+if+not+exists+insert — ypercubeᵀᴹ, Jun 23 '11 at 10:26
Or this for various SQL systems: http://stackoverflow.com/search?q=sql+if+not+exists+insert — ypercubeᵀᴹ, Jun 23 '11 at 10:27
Possible duplicate of: http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table — ypercubeᵀᴹ, Jun 23 '11 at 10:33

score 1 · Answer 1 · answered Jun 23 '11 at 10:30

You need something like the code below

<?php

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT Date,Time FROM loadings WHERE Date='21-06-2011' AND Time ='7'", $link);
$num_rows = mysql_num_rows($result);

if $num_rows == 0 { code to insert } /* when query returns no rows insert into the database*/

?>

score 1 · Answer 2 · answered Jun 23 '11 at 10:37

1

In Summary: Don't use reserved words.

It looks like you are using reserved words. using reserved words make for very tricky bugs.

for your reserved fields and variables i'd add the or a or some sort of prefix.

adate instead of date
atime instead of time

to see the list of reserved words go here.

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

answered Jun 23 '11 at 10:37

Gidon Wise

1,868
1
11
10

Thanks for pointing that out. I shall be more careful in future. – Trevor Weaver Jun 23 '11 at 13:50

score 0 · Answer 3 · answered Jun 23 '11 at 10:24

0

$rs = mysql_query("SELECT Date,Time FROM loadings WHERE Date='21-06-2011' AND Time ='7'");
$row = mysql_fetch_array($rs);
$num = mysql_num_rows($rs);

if($num > 0) {
//Does nothing
} else {
//insert here
}

answered Jun 23 '11 at 10:24

easyb

68
1
8

Thanks you. Works great. regards Trevor – Trevor Weaver Jun 23 '11 at 13:51
Thumb up? Hahaha just kidding man. Good to know it helped you out. =)) – easyb Jun 24 '11 at 03:38

php sql question: use of IF EXISTS

3 Answers3