Jump to content

swith between two SQL statements


arre

Recommended Posts

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 :)
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

[!--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 :(
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[!--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?
Link to comment
Share on other sites

[!--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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.