Jump to content

Archived

This topic is now archived and is closed to further replies.

arre

swith between two SQL statements

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 :)

Share this post


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

Share this post


Link to post
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]

Share this post


Link to post
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 :(

Share this post


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

Share this post


Link to post
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

Share this post


Link to post
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?

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×

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.