Jump to content


Photo

swith between two SQL statements


  • Please log in to reply
7 replies to this topic

#1 arre

arre
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 April 2006 - 02:30 PM

I want to be able to switch between two sql statements.. I'm sending the values through a form calling the function below :)
//////////////////////////////////////////////////
function checkUser($name,$mail) {
  $conn_id = ifx_connect ("db@atkin")
    or die("Cant open database");

  $query = "SELECT name FROM bUser where name == '$name'";
  $result = ifx_query ($query, $conn_id)
    or die("Cant SELECT");

  if ($result !== null)
    {
      print "UPDATE";
      $query = "UPDATE bUser SET email = '$mail' where name == '$name'";
      $result = ifx_query ($query, $conn_id)
        or die("Cant UPDATE");
    } else {
      print "INSERT";
      $query = "INSERT INTO bUser Values ('$name','$mail')";
      $result = ifx_query ($query, $conn_id)
        or die("Cant INSERT");
    }
  ifx_free_result($result);
  ifx_close($conn_id);
}
?>
--------------------------------------
Well, the idea is to update the email of a person if that person exists in the database. If not then add him/her to the database. But the If statement doesn't work, the only thing that works is the update query.
When I try to print $result to see whether it's null or not I see that it contains a string "Resource id2".. so that's why the update query works and not the insert. Shouldn't $result be null if name doesn't exist?

Thx in advande :)

#2 micah1701

micah1701
  • Members
  • PipPipPip
  • Advanced Member
  • 613 posts
  • LocationEllington, CT USA

Posted 27 April 2006 - 02:45 PM

check your operator in the if() statement.

you have !== for "is not equal to"
you only need !=

check out [a href=\"http://www.w3schools.com/php/php_operators.asp\" target=\"_blank\"]http://www.w3schools.com/php/php_operators.asp[/a]

"Confidence in the face of risk."

#3 bbaker

bbaker
  • Members
  • PipPipPip
  • Advanced Member
  • 127 posts
  • LocationNY

Posted 27 April 2006 - 02:51 PM

another way to do it:

//////////////////////////////////////////////////
function checkUser($name,$mail) {
  $conn_id = ifx_connect ("db@atkin")
    or die("Cant open database");

  $query = "SELECT name FROM bUser where name = '$name'";   //  ### MODIFIED ###  only need 1 = sign
  $result = ifx_query ($query, $conn_id)
    or die("Cant SELECT");
  $num_rows = ifx_num_rows($result);  //   ### ADDED ### - checks number of rows resulted from query

  if ($num_rows >= 1)   //  ### MODIFIED ### if user exists $num_rows will be >= 1 and will run update, else it'll run insert.
    {
      print "UPDATE";
      $query = "UPDATE bUser SET email = '$mail' where name = '$name'";  //  ### MODIFIED ###  only need 1 = sign
      $result = ifx_query ($query, $conn_id)
        or die("Cant UPDATE");
    } else {
      print "INSERT";
      $query = "INSERT INTO bUser Values ('$name','$mail')";
      $result = ifx_query ($query, $conn_id)
        or die("Cant INSERT");
    }
  ifx_free_result($result);
  ifx_close($conn_id);
}
?>


#4 arre

arre
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 April 2006 - 10:16 PM

[!--quoteo(post=369247:date=Apr 27 2006, 09:45 AM:name=micah1701)--][div class=\'quotetop\']QUOTE(micah1701 @ Apr 27 2006, 09:45 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
check your operator in the if() statement.

you have !== for "is not equal to"
you only need !=

check out [a href=\"http://www.w3schools.com/php/php_operators.asp\" target=\"_blank\"]http://www.w3schools.com/php/php_operators.asp[/a]
[/quote]

Thx for replying.. changed it to != , still doesn't work :(

#5 arre

arre
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 April 2006 - 10:29 PM

I tried to run the code-example you gave me bbaker, but now it only inserts. How does the code compare the name if it's already in the database?

#6 Zane

Zane
  • Administrators
  • Advanced Member
  • 4,134 posts

Posted 27 April 2006 - 10:30 PM

you could much easier do the IF in the query itself like so
$sql = "
IF ( SELECT name FROM bUser where name = '$name' IS NULL ) THEN
     INSERT INTO bUser Values ('$name','$mail');
ELSE
     UPDATE bUser SET email = '$mail' where name = '$name';
END IF;
";
mysql_query($sql);

I think that should work

btn_donate_SM.gif Want to thank me? Contribute to my PayPal piggy-bank
 

172938.png

#7 arre

arre
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 April 2006 - 10:58 PM

[!--quoteo(post=369387:date=Apr 27 2006, 05:30 PM:name=zanus)--][div class=\'quotetop\']QUOTE(zanus @ Apr 27 2006, 05:30 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
you could much easier do the IF in the query itself like so
$sql = "
IF ( SELECT name FROM bUser where name = '$name' IS NULL ) THEN
     INSERT INTO bUser Values ('$name','$mail');
ELSE
     UPDATE bUser SET email = '$mail' where name = '$name';
END IF;
";
mysql_query($sql);

I think that should work
[/quote]

That seems logical zanus.. got a "prepare fails" error though. Maybe I'm doing something wrong :S.. is spacing sensitive when writing the if function inside the sql query?

#8 bbaker

bbaker
  • Members
  • PipPipPip
  • Advanced Member
  • 127 posts
  • LocationNY

Posted 28 April 2006 - 02:11 PM

[!--quoteo(post=369386:date=Apr 27 2006, 06:29 PM:name=arre)--][div class=\'quotetop\']QUOTE(arre @ Apr 27 2006, 06:29 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I tried to run the code-example you gave me bbaker, but now it only inserts. How does the code compare the name if it's already in the database?
[/quote]
I tested this on my server & it works fine for me.

if the name already exists, the $num_rows = ifx_num_rows($result); will equal 1
if the name does NOT exist, $num_rows = ifx_num_rows($result); will equal 0

so the if statement: if ($num_rows >= 1) basically says if $num_rows is greater than or equal to 1, UPDATE. If it's not greater than or equal to 1 (meaning 0), INSERT.

You should NOT use a query that checks for a NULL name. If the name does not exist in the database, it cannot be NULL. On this same note, you should do some sort of validation for the name & email address so that you don't get duplicates AND to make sure the user enters a name & email. If they leave it blank, you'll have NULL entries(maybe multiple NULL entries). If you do have multiple NULL entries & run the script searching where name = NULL, it'll update ALL NULL names with the new info.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users