0

I'm creating simple game for Facebook. All users who used app are written to database. I need always check If user already exists Is in database, how to do that correctly?

So I have variable $name = $user_profile['name']; It successfully returns user's name

And this is my part of code to check If user already exists in database.

$user_profile = $facebook->api('/me');
$name = $user_profile['name'];

      $mysqli = new mysqli("host","asd","pw","asdf");
                   echo "1";
               $sql = "SELECT COUNT(*) AS num FROM myTable WHERE userName = ?";
                   echo "2";
                if ($stmt = $mysqli->prepare($sql)) {
                   echo "3";
                $stmt->bind_param('s', $name);
                   echo "4";
                $stmt->execute();
                   echo "5";
                $results = $stmt->get_result();
                   echo "6";
                $data = mysqli_fetch_assoc($results);
                   echo "7";
                }
           if($data['num'] != 0)
            {
                    echo "bad";
                    print "user already exists\n";
            } else {
                    echo "good";    
                    $apiResponse = $facebook->api('/me/feed', 'POST', $post_data);
                    print "No user in database\n";
            }
    }

This code not working, It should post data on user's wall If user not exists in database. I spent many time to find reason why, but unsuccessfully. After debugging It don't show any errors. To find which line is incorrect after every line I used echo "number" so now I know which line is incorrect. It prints 1 2 3 4 5 and stucks. (everything what are below the code not loading.) So that means this line $results = $stmt->get_result(); is incorrect. But I misunderstood what's wrong with this line?

If I comment this line all code loading (then print 1 2 3 4 5 6 7 No user in database! and It post data on user's wall.) but in this case program always do the same, not checking database.

Also I've tried to change COUNT(*) to COUNT(userName), but the same.

So could you help me, please?

I've read this: Best way to check for existing user in mySQL database? but It not helped me.

P.s. In this case i need to use FB username.

Community
  • 1
  • 1
Rs Penki
  • 61
  • 2
  • 3
  • 9
  • When using object oriented connect, query, bind and execute, you might want to use object oreinted fetch, instead of `mysqli_fetch_assoc` – Royal Bg Dec 12 '13 at 08:59
  • A little off the question but important: You should not use the username (it can change!), the better approach would be using the user-id. – Fex del Sollo Dec 12 '13 at 09:00
  • @RoyalBg Thank you for answer, but I don't know really how to do that correctly. – Rs Penki Dec 12 '13 at 09:05
  • @FexdelSollo Thank you for answer, but in this case I need to use FB username, not ID – Rs Penki Dec 12 '13 at 09:06
  • @RsPenki check my answer, I showed an example and linked the documentation in case my fix does not work, you can read the flow of fetching – Royal Bg Dec 12 '13 at 09:06
  • This code is not checking for errors - do that first, it could be an invalid column name or something else silly. Also, instead of `count(..)` (which will return one record), simply check [mysqli->num_rows](http://php.net/manual/en/mysqli-result.num-rows.php) and omit the `count(..)` in the query. – user2864740 Dec 12 '13 at 09:10
  • @user2864740 Thank you for answer, how could I user `mysqli->num_rows` correctly in my case? – Rs Penki Dec 12 '13 at 09:17
  • @RsPenki "SELECT userName FROM myTable WHERE userName = ?" .. `if ($result->num_rows == 1) { /* found user */ }` (if the username column is unique then the query can return at most one record). Also, make sure to add the applicable error checking, as covered in the MySQLi examples. Inspect/log `$mysqli->error` as appropriate. – user2864740 Dec 12 '13 at 09:19
  • @user2864740 Thank you for answer so I don't need to use COUNT(*)? Could you select answer and show how It should be? If you have time, thank you very much. – Rs Penki Dec 12 '13 at 09:24
  • @RsPenki Count is useful for .. counting. Like say, to determine how many users have red hair (according to their profile :). Here we're just interested in determining if there is *a* result (and perhaps getting other user properties). Actually, one doesn't need to use `num_rows` in this case because we can rely on 0..1 rows being returned; if `$results->fetch_assoc` returns an array (non-FALSE) then there is a record or, "a user with the given username". However, with COUNT there will always be one row (and then we need to get the value and compare that .. which is unnecessary work). – user2864740 Dec 12 '13 at 09:29
  • I changed $sql to `"SELECT userName FROM myTable WHERE userName = ?"` and `if ($result->num_rows == 1)` instead `if($data['num'] != 0)`, but the same It print only `1 2 3 4 5` and in this line `$results = $stmt->get_result();` stucks. – Rs Penki Dec 12 '13 at 09:36
  • A side note like @FexdelSollo said, you should not use username. 1 it could change and 2 it might not be unique. What 2 people have the same name and one of them is a user of your app already. The 2nd user with the same name then couldn't use it – TommyBs Dec 12 '13 at 11:14
  • @TommyBs I know It, just in this case I need to use username. – Rs Penki Dec 12 '13 at 11:25
  • @TommyBs: Unique name is a really good point, totally forgot about that. Best solution would be the combination of name AND ID, this way you will have a user in you database with his name and his unique ID. – Fex del Sollo Dec 12 '13 at 11:26
  • Guys maybe do you know solution what the problem is with the code? We disscussing on @user876345 answer's comments. – Rs Penki Dec 12 '13 at 11:38

1 Answers1

0

Can you try this, $stmt->fetch() instead of mysqli_fetch_assoc($results)

    $mysqli = new mysqli("host","asd","pw","asdf");
echo "1";
/* Create the prepared statement */
$stmt = $mysqli->prepare("SELECT COUNT(*) AS num FROM myTable WHERE userName = ?") or die("Prepared Statement Error: %s\n". $mysqli->error);
    /* Execute the prepared Statement */
    $stmt->execute();

    /* Bind results to variables */
    $stmt->bind_result($name);

    $data = $stmt->fetch();
    if($data['num'] > 0)
    {
        echo "bad";
        print "user already exists\n";
    } else {
        echo "good";
        $apiResponse = $facebook->api('/me/feed', 'POST', $post_data);
        print "No user in database\n";
    }   

    /* Close the statement */
    $stmt->close();

Ref: http://forum.codecall.net/topic/44392-php-5-mysqli-prepared-statements/

Krish R
  • 22,188
  • 7
  • 49
  • 57
  • 1
    This won't work because of mixing db api's. Also there's no explanation – Royal Bg Dec 12 '13 at 09:08
  • @Royal Bg, Thanks, it my bad. Updated answer. thanks lot – Krish R Dec 12 '13 at 09:09
  • Thank you for answer, but as I said It print only `1 2 3 4 5` from 5 It stop working. So as I understand this line `$results = $stmt->get_result();` is incorrect. I changed as you said, but the same problem - not working, print only to 5 value and stucks. – Rs Penki Dec 12 '13 at 09:20
  • and if I use this `$stmt = $mysqli->prepare($sql);` instead `if(..)` It prints only value to 3 and stucks. – Rs Penki Dec 12 '13 at 09:30
  • @Rs Penki, Updated my answer. Please check – Krish R Dec 12 '13 at 09:37
  • Thank you, but for now I always get this value `1goodNo user in database` nevermind if user already exists in database or not. – Rs Penki Dec 12 '13 at 09:44
  • I have updated my answer? please try this. it may helps you resove the same – Krish R Dec 12 '13 at 10:01
  • @user876345 Maybe that because usernames are with UTF-8 letters like `š į ė č ę...` or this doesn't matter? Just checking with facebook account which is without UTF-8 letter, but the same thing, always print 'No username in database'. If I try to `echo $name` It print name successfully – Rs Penki Dec 12 '13 at 10:04
  • Thank you for answer, but the same `1goodNo user in database ` nevermind if user is in database or not. – Rs Penki Dec 12 '13 at 10:08
  • What is the username value ? – Krish R Dec 12 '13 at 10:09
  • Auguste Lapinskaite fb username and `userName type` in database is `varchar(100)` – Rs Penki Dec 12 '13 at 10:16
  • Can you run this query in mysql and check the rows ? `SELECT COUNT(*) AS num FROM myTable WHERE userName ='your username'` – Krish R Dec 12 '13 at 10:20
  • I've used this: `SELECT COUNT(*) AS num FROM myTable WHERE userName ='Auguste Lapinskaite'` and It worked. If user deleted from database It return `num 0` and If user is in database It returns `num 1`. – Rs Penki Dec 12 '13 at 10:31
  • And I just tried `echo $data['num'];` but It nothing returns. – Rs Penki Dec 12 '13 at 10:34
  • Maybe here miss this line `$stmt->bind_param('s', $name);` or maybe It should be before execute..? I don't have ideas what's wrong :/ – Rs Penki Dec 12 '13 at 10:48
  • I've checked this, as you gave me code It looks like fine, so where is the problem? Have you ideas? – Rs Penki Dec 12 '13 at 11:06
  • $stmt->bind_param('s', $name); can add this before execute statment and update me – Krish R Dec 12 '13 at 11:08
  • I've added before execute statement `$stmt->bind_param('s', $name);`, but the same, later tried to add this `$stmt->bind_result($name);` but the same too. – Rs Penki Dec 12 '13 at 11:16
  • Maybe one of these lines is incorrect: `$stmt->bind_result($name); $data = $stmt->fetch();` – Rs Penki Dec 12 '13 at 11:19
  • You can remove the `$stmt->bind_result($name);` and try it – Krish R Dec 12 '13 at 11:23
  • I've tried, the same too. $data['num']; always return 0 to php :/ – Rs Penki Dec 12 '13 at 11:32
  • Maybe here is other way to check If user already exists in database? – Rs Penki Dec 12 '13 at 11:56