swith between two SQL statements

[arre]

I want to be able to switch between two sql statements.. I'm sending the values through a form calling the function below :)
[code]
//////////////////////////////////////////////////
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);
}
?>
[/code]
--------------------------------------
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 :)

[micah1701]

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]

[bbaker]

another way to do it:

[code]//////////////////////////////////////////////////
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);
}
?>
[/code]

[arre]

[!--quoteo(post=369247:date=Apr 27 2006, 09:45 AM:name=micah1701)--][div class=\'quotetop\']QUOTE(micah1701 @ Apr 27 2006, 09:45 AM) [snapback]369247[/snapback][/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 :(

[arre]

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?

[Zane]

you could much easier do the IF in the query itself like so
[code]
$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);[/code]

I think that should work

[arre]

[!--quoteo(post=369387:date=Apr 27 2006, 05:30 PM:name=zanus)--][div class=\'quotetop\']QUOTE(zanus @ Apr 27 2006, 05:30 PM) [snapback]369387[/snapback][/div][div class=\'quotemain\'][!--quotec--]
you could much easier do the IF in the query itself like so
[code]
$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);[/code]

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?

[bbaker]

[!--quoteo(post=369386:date=Apr 27 2006, 06:29 PM:name=arre)--][div class=\'quotetop\']QUOTE(arre @ Apr 27 2006, 06:29 PM) [snapback]369386[/snapback][/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: [i]if ($num_rows >= 1)[/i] 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.