PHP Script. Check If user already is in MySQL database

Question

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

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

This is my function to insert user to database.

    $mysqli = new mysqli("asd","asd","pw","asd");
if ($stmt = $mysqli->prepare("INSERT into myTable (score, userName) VALUE (?,?) ")) {
if (!$mysqli->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $mysqli->error);
} else {

}
$stmt->bind_param('ss', $score, $name);
   $stmt->execute();
   if ($stmt->error != '') {
       echo ' error:'.$stmt->error;
   } else {
       echo 'success';
   }
   $stmt->close();
} else {
   echo 'error:'.$mysqli->error;
}
$mysqli->close();

Now I need If function to check If user already in database. As I understand It should be similiar as insert to database, just instead INSERT into I need to use SELECT from just how to do It successfully? Thank you.

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

UPDATE

Now my code looks like:

   $mysqli = new mysqli("host","asd","pw","asdf");
   echo "1";
 $sql = "SELECT COUNT(*) AS num FROM myTable WHERE userName = ?";
    echo "2";
 if ($stmt = $mysqli->prepare("SELECT COUNT(*) AS num FROM under WHERE userName = ? ")) {
    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";
    }

It print 1 2 3 4 5. that means this line $results = $stmt->get_result(); is incorrect, because after It not printing value. What can be problem here?

You should not use the FB username for this (after all, the user can change it), but the FB user id. — CBroe, Dec 11 '13 at 15:03
@CBroe thank u for answer, in this case I need to use username, not FB user ID, but thank you for answer. — , Dec 11 '13 at 22:25

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

This query is simple:

SELECT
    COUNT(*)
FROM
    myTable
WHERE
    userName = :user_name

If the result is not 0, then the user exists.

mysqli implementation in php

$sql = "SELECT
    COUNT(*) AS num
FROM
    myTable
WHERE
    userName = ?";

if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param('s', $name);
    $stmt->execute();
    $results = $stmt->get_result();
    $data = mysqli_fetch_assoc($results);
    if($data['num'] != 0)
    {
        print "user already exists\n";
    }
}

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 11 '13 at 15:02

user4035

21,015
10
54
86

Thank you for answer, but I misunderstood how my PHP script should look like now? `$query = (SELECT COUNT(*) FROM myTable WHERE userName = :user_name` and `if ($query == 0) { .... } else { ... }`? – Dec 11 '13 at 15:19
@RimantėBaltiejūtė Look at the updated answer. I hoped, you'll write php code yourself, when I give you SQL ;) – user4035 Dec 11 '13 at 15:39
Thank you for answer again, but still not working, I updated my code with code how It looks like now. – Dec 11 '13 at 16:11
@RimantėBaltiejūtė What do you mean "not working"? What errors do you get? Did you try to debug? – user4035 Dec 11 '13 at 17:33
After debugging I don't get any errors. I will try to explain. In Facebook window is Flash game, I'm trying your code use in top of the game, so I see white window (game not appears), but debugging show "success" no errors. If I change this `COUNT(*)` to for example `COUNT(adasda)` I got error: "There is no field "adasda"" and game appearing on the screen. So that means something wrong with this `COUNT(*)` line. – Dec 11 '13 at 17:49
I updated my question with the code how It looks like now, what's wrong with It? Nothing what is bottom of this code not loading if is used COUNT(*) and not print any result. – Dec 11 '13 at 17:58
@RimantėBaltiejūtė No, the COUNT(\*) is absolutely correct. What data do you have in myTable? What is the value of $name? If you don't have this name, in the database, the result of COUNT(*) is 0. – user4035 Dec 11 '13 at 18:18
userName - varchar(100) (here keeping user's First and Last names), $name value is user's name from facebook (If I test It echo $name; It successfully prints First and Last names). I tried to delete from database name and insert It again, but the same problem. – Dec 11 '13 at 18:32
I've tried too: `if($data['num'] != 0) { echo "good"; } else {echo "bad"; }` but It not working. – Dec 11 '13 at 18:34
In my database table first collumn is "score" and sceond "userName" or this doesn't matter? – Dec 11 '13 at 18:46
@RimantėBaltiejūtė It doesn't matter. Did it print good or bad? It's a very simple task, I don't know what's wrong. Try to put some simple name like "aaa" into the table and set $name="aaa". – user4035 Dec 11 '13 at 18:50
It doesn't print anything and nothing loading if is bottom of this script... But debugging show success... Interesting... But If I change COUNT(*) to COUNT(sdadsa) loading successfully content, but get error. But in one and other ways It not print anything.. – Dec 11 '13 at 18:59
@RimantėBaltiejūtė This means, that the query is incorrect, and it doesn't run it. You can change COUNT(*) to COUNT(userName) also print mysqli error, using these instructions: http://stackoverflow.com/questions/17053466/how-to-display-errors-for-my-mysqli-query – user4035 Dec 11 '13 at 20:28
@RimantėBaltiejūtė Run this query in phpmyadmin: what is the result? – user4035 Dec 11 '13 at 20:55
This line `$results = $stmt->get_result();` is incorrect, after this line stop printing results. I updated my question. – Dec 11 '13 at 21:02

score 0 · Answer 2 · answered Dec 11 '13 at 16:12

0

1) If you write FB user data, you should not check NAME! You should check FB user ID - it is always unique, and names can be the same.

2) Never do COUNT(*) - use COUNT(id) instead

answered Dec 11 '13 at 16:12

kovpack

4,757
7
36
55

Thank you for answer, but for now I need to use user name, can't use ID in this case. My example still wont work with COUNT(id), nothing happens – Dec 11 '13 at 16:40
I've added to check If here are errors and I got 1: `error:Unknown column 'id' in 'field list'` – Dec 11 '13 at 16:45
When I use * instead id `COUNT(*) AS num` I got success, no errors, but still not working. – Dec 11 '13 at 16:47
I think somewhere here is mistake, because It successfully connects to database: `results = $stmt->get_result(); $data = mysqli_fetch_assoc($results); if($data['num'] != 0) { echo "good"; $apiResponse = $facebook->api('/me/feed', 'POST', $post_data); } else { echo "bad"; $apiResponse = $facebook->api('/me/feed', 'POST', $post_data); }` I'm trying to post data in both ways If user is in database and if not, but It not working anyway. – Dec 11 '13 at 17:02
Print value of $data after query and than you'll understand where is the problem. If $data returns normal thing, than try to find error in your facebook API. You need to debug it step by step, else you'll spend too much time on things that work OK, but will not notice where is the problem. Check what your `$mysqli->prepare($sql)` returns. It seems your `if (bla-bla)` does not work. – kovpack Dec 11 '13 at 20:26
Just put `echo "this is step 1"` after each line and you'll find problematic place. – kovpack Dec 11 '13 at 20:29
thank you for answer, I've tried you echo method and 6 step fails. I updated my question with the code. – Dec 11 '13 at 21:00
If I comment this line `// $results = $stmt->get_result();` all echo print values `1,2,3,4,5,6,7` and text `user already exists` and post on the wall. But now It do always doesn't matter if user is already on database or not. That mean problem with this line. Could you help me? – Dec 11 '13 at 21:39
Anyone have ideas? :/ Maybe here are other ways to check if user already exist in database? – Dec 11 '13 at 22:23
Take a look on `try {} catch() {}` and try to use it for your code to get $e->getMessage() and find out what is going on. You can find example here - http://stackoverflow.com/questions/10466530/mysqli-prepared-statement-unable-to-get-result – kovpack Dec 12 '13 at 07:36

PHP Script. Check If user already is in MySQL database

2 Answers2

mysqli implementation in php