1

Need Help understanding register.php

        $result = query("INSERT IGNORE INTO users (username, hash, cash) VALUES(?, ?, 10000.0000)", $_POST["username"], password_hash($_POST["password"], PASSWORD_DEFAULT));

        if(result == false)
            apologize("ERROR : Duplicate username");
        else //find the ID 
        {
            $rows = query("SELECT LAST_INSERT_ID() AS id");
            $id = $rows[0]["id"];
            $_SESSION["id"]=$id;
            redirect("index.php");
        }

1) What's the return type of $rows ? Is it an array or just an int ? Because SELECT LAST_INSERT_ID() will return the last inserted ID , which is going to be a number(int) rather than an array ?

2) If $rows is not an array , then how can we access $id = $rows[0]["id"] ?

3) Will the above code still works if we replace SELECT LAST_INSERT_ID() AS id by just SELECT LAST_INSERT_ID() (omitting id) ?

1
  • Besides the questions you asked, here's something else to think about: if INSERT IGNORE INTO users returns ($result === false), it does not necessarily mean that there is already a user in the users table with the same username. This could fail for other reasons. It's better first to check whether the username already exists in the database. If your query returns exactly one row, don't register. Otherwise, you may INSERT IGNORE INTO users as above. – hotwebmatter Oct 3 '16 at 22:33
2

1) What's the return type of $rows ? Is it an array or just an int ? Because SELECT LAST_INSERT_ID() will return the last inserted ID , which is going to be a number(int) rather than an array?

$rows is a numerically-indexed array of rows. This is true even when only one row is returned -- it will be numerically indexed as $rows[0]. (If multiple rows are returned, the second row will be $rows[1], and so on.) Each row in $rows is a nested associative array.

$rows[0]["id"] is the integer value corresponding to the key id in the nested associative array comprising the first (or "zero-th") numerically-indexed row.

2) If $rows is not an array , then how can we access $id = $rows[0]["id"] ?

But $rows is an array. See above.

You can access the integer value associated with the key ["id"] in the first row [0] of the numerically-indexed array $rows using exactly the syntax you mention here.

I think you actually understand this better than you think you do!

3) Will the above code still works if we replace SELECT LAST_INSERT_ID() AS id by just SELECT LAST_INSERT_ID() (omitting id) ?

No, it won't work the way you want it to work.

Why don't we try it in the mysql50 shell and see what happens?

mysql> SELECT LAST_INSERT_ID() AS id;
+----+
| id |
+----+
| 13 |
+----+
1 row in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               13 |
+------------------+
1 row in set (0.00 sec)

Do you see what AS id does in the SQL query?

For a better understanding, you should read about SQL aliases.

If you left off the AS id part of the query, you'd need to rewrite the next line:

    $rows = CS50::query("SELECT LAST_INSERT_ID()");
    $id = $rows[0]["LAST_INSERT_ID()"];

Do you see why?

I'm not even 100% sure this would work, because I don't know if there could be problems caused by using parenthesis in an associative array key. Even if it did work, the code is just cleaner if we use id as an alias.

A final word of advice: you should be using the dump() function from helpers.php to answer questions like these. Simply add a line like this:

dump($rows);

or this:

dump($id);

or this:

dump($_SESSION);

at the appropriate place in your code. This will allow you to inspect the value of the variable in question.

Hope this helps!

2
  • just one more doubt : $rows will ALWAYS return an array or rows ? or is it just in this case ? I mean whenever we use SELECT statement in query , the return type ALWAYS be an array of rows ? – Tushar Watts Oct 4 '16 at 19:24
  • Yes, always. If there is only one row, it will return a "singleton" -- that is to say, an array with only one element indexed to 0. (i.e., $rows[0]) More precisely, the query() function will always return an array. (The value of $rows in this example is returned by the query() function. You could call the variable something else, and it would still be an array.) Also, what we are calling "an array of rows" is really an array of hashes (associative arrays), each one of which represents a row from a database table. – hotwebmatter Oct 4 '16 at 21:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .