Another mysql_affected_rows error on update query

[rick.emmet]

Hi Everyone,

I have a script that calls a function which updates a table in the database. I've looked threw the forum and haven't found the answer (I've also looked elsewhere)

 

The value in the reg_var column is set to “No” in advance of the query and the update changes this value to “Yes”. Here's the function:

 

1 function confirm_update($username, $passwd) {

2 // check username and password with db

3 // if yes, return true

4 // else throw error

5

6  // connect to db

7  $conn = db_connect();

8

9  // check if username is unique while updating the database

10  $result = mysqli_query($conn, "update clients set reg_var = 'Yes' where

11 user_name='".$username."' and password = sha1('".$passwd."')");

12                         

13  if (!$result) {

14      echo "There's a problem with the user nane or password, please try again (#1 tfc_user_auth_fns.php).<br />";

15 do_html_url('login.php', 'Login');

16 exit;

17

18  } elseif (mysqli_affected_rows($result)>0) {

19  

20      return true;

21

22  } else {

23  

24      echo "You must first complete the registration process, please

25 check your email for a comfirmation email (#2 tfc_user_auth_fns.php).<br />";

26

27 display_login_form();

28 exit;

29  }

30

31 mysqli_free_result($result);

32 mysqli_close($conn);

33

34 }

 

 

I've tested this function where the user name and password are correct and in these cases, the function should update the reg_var column to “No”. However, I'm getting the following MySQL error message:

“Warning: mysqli_affected_rows() expects parameter 1 to be mysqli, boolean given...”. Now, what gets me is that this query updates the database every time, MySQL should return “1” as that is the number of rows updated by the query.

 

I have had similar error messages with other queries that also ran perfectly, but throw an error every time as well. It is as if one of the .ini files is turned off, but I've looked and can't find anything that looks suspicious . Does anybody have an idea what's going on with this function or the system??? Thanks in advance for your assistance!

Cheers,

Rick 

[PFMaBiSmAd]

The parameter that mysqli_affected_rows() takes is NOT a result resource. It is the mysqli link resource.

[rick.emmet]

Hello PFMaBiSmAd,

Thanks for the quick reply! I going off of what we learned in my last PHP class and specifically what PHP & MySQL Web Development says about the use of mysqli_affected_rows(). The book states that "when you write queries that change the database, you should use mysqli_affected_rows() instead [of mysqli_affected_rows()]. So, I'm a bit confused - do you know what function I should be using instead?

Thanks much!

Rick

 

[PFMaBiSmAd]

You are using the correct function. You are passing it the wrong parameter.

[rick.emmet]

Hello PFMaBiSmAd,

Sorry, I'm not quite understanding what you mean. At the point in the book that I referred to, they are writing nothing but OO PHP, and I still can't write OO PHP (I have to translate what they have in the book). Unfortunately, they aren't very explicit with procedural PHP in those later chapters. I did make some changes to the script, trying to pass a different parameter – like so:

 

$conn = db_connect();

 

  $query = "update clients set reg_var = 'Yes' where

user_name='".$username."' and password = sha1('".$passwd."')";

 

  // check if username is unique and update the database

  $result = mysqli_query($conn, $query);

                       

if (!$result) {

    echo "There's a problem with the user nane or password, please try again (#1 tfc_user_auth_fns.php).<br />";

do_html_url('login.php', 'Login');

exit;

 

  } elseif (mysqli_affected_rows($query)>0) {

 

    return true;

   

    // REST OF THE SCRIPT HERE

 

The book says that it is better to write a query first and then write “$result = mysqli_query($conn, $query); ”, though they never say why. With these changes, I now get a different error message:

 

Warning: mysqli_affected_rows() expects parameter 1 to be mysqli, string given.

 

I looked up this error message and what I found was cases where the person failed to use the database connection as a parameter. I have the db connection as a parameter in my script ($conn). What am I missing here?

Thanks again,

Rick

 

[jcbones]

mysqli_affected_rows($conn)

 

It needs the connection, not the resource returned by a query.

 

They even have an example in the manual>

mysqli_affected_rows()

[rick.emmet]

Well thank you very much,

I would not have guessed that I would use $conn as the parameter! As I said, the book is not very explicit (as far as procedural PHP) at that point in the book. The OO PHP uses a different format. The change worked like a charm - here's the script that works:

 

function confirm_update($username, $passwd) {

// check username and password with db

// if yes, return true

// else throw error

 

  // connect to db

  $conn = db_connect();

 

  // check if username is unique

  $result = mysqli_query($conn, "update clients set reg_var = 'Yes' where

user_name='".$username."' and password = sha1('".$passwd."')");

                       

if (!$result) {

    echo "There's a problem with the user nane or password, please try again (#1 tfc_user_auth_fns.php).<br />";

do_html_url('login.php', 'Login');

exit;

 

  } elseif (mysqli_affected_rows($conn)>0) {

 

    return true;

 

  } else {

 

    echo "You must first complete the registration process, please

check your email for a comfirmation email (#2 tfc_user_auth_fns.php).<br />";

 

display_login_form();

exit;

  }

 

mysqli_free_result($result);

mysqli_close($conn);

 

}

 

Again, thanks very much for your help!

Cheers,

Rick

[PFMaBiSmAd]

I would not have guessed that

 

In programming, there's no need to guess. All the basic information can be found in the documentation.

[rick.emmet]

I know, I know - I spent quite a bit of time at the site looking at the documentation. I was seeing what I was expecting to see and did not see what I was supposed to see. Thanks again for the heads up, sometimes you just can't see what's right there in front of you.

Cheers,

Rick