MySQL Fetch Array Error

Question

Possible Duplicate:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

I have recieved the following PHP error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/web03/b556/pow.kkarimu/htdocs/F12_MEDIA/Site/develop/v1/twitter/twitter_oauth.php on line 32

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/web03/b556/pow.kkarimu/htdocs/F12_MEDIA/Site/develop/v1/twitter/twitter_oauth.php on line 38

From this php file:

 $query = mysql_query("SELECT * FROM users_entity WHERE oauth_provider = 'twitter' AND oauth_id = ".$user_info->id);
    $result = mysql_fetch_array($query);

    // If not, let's add it to the database
    if(empty($result)){
        $query = mysql_query("INSERT INTO users_entity (oauth_provider, oauth_id, oauth_username, oauth_token, oauth_secret) VALUES ('twitter', {$user_info->id}, '{$user_info->screen_name}', '{$access_token['oauth_token']}', '{$access_token['oauth_token_secret']}')");
        $query = mysql_query("SELECT * FROM users_entity WHERE id = ".mysql_insert_id());
        $result = mysql_fetch_array($query);
    } else {
        // Update the tokens
        $query = mysql_query("UPDATE users_entity SET oauth_token = '{$access_token['oauth_token']}', oauth_secret = '{$access_token['oauth_token_secret']}' WHERE oauth_provider = 'twitter' AND oauth_id = {$user_info->id}");

*UPDATE FULL CODE:

<?
require_once('../Connections/f12_database_connect.php');
?>
<?

$root = realpath($_SERVER["DOCUMENT_ROOT"]);

require("twitteroauth/twitteroauth.php");
session_start();

if(!empty($_GET['oauth_verifier']) && !empty($_SESSION['oauth_token']) && !empty($_SESSION['oauth_token_secret'])){

// We've got everything we need
// TwitterOAuth instance, with two new parameters we got in twitter_login.php
$twitteroauth = new TwitterOAuth('Tt7HVYyZJC25GWMybpemg', '11nazzxqFrJEqVCxpdLfMBxq4fsh1iPsaaEIma5sI', $_SESSION['oauth_token'], $_SESSION['oauth_token_secret']);
// Let's request the access token
$access_token = $twitteroauth->getAccessToken($_GET['oauth_verifier']);
// Save it in a session var
$_SESSION['access_token'] = $access_token;
// Let's get the user's info
$user_info = $twitteroauth->get('account/verify_credentials');
/*// Print user's info
print_r($user_info);*/

//Check and update database
if(isset($user_info->error)){
    // Something's wrong, go back to square 1
    header('Location: /twitter/twitter_login.php');
} else {
    // Let's find the user by its ID
    $query = mysql_query("SELECT * FROM users_entity WHERE oauth_provider = 'twitter' AND oauth_id = ".$user_info->id);
    $result = mysql_fetch_array($query);

    // If not, let's add it to the database
    if(empty($result)){
        $query = mysql_query("INSERT INTO users_entity (oauth_provider, oauth_id, oauth_username, oauth_token, oauth_secret) VALUES ('twitter', {$user_info->id}, '{$user_info->screen_name}', '{$access_token['oauth_token']}', '{$access_token['oauth_token_secret']}')");
        $query = mysql_query("SELECT * FROM users_entity WHERE id = ".mysql_insert_id());
        $result = mysql_fetch_array($query);
    } else {
        // Update the tokens
        $query = mysql_query("UPDATE users_entity SET oauth_token = '{$access_token['oauth_token']}', oauth_secret = '{$access_token['oauth_token_secret']}' WHERE oauth_provider = 'twitter' AND oauth_id = {$user_info->id}");
    }

    $_SESSION['twitter_id'] = $result['user_primary_id'];
    $_SESSION['twitter_username'] = $result['oauth_username'];
    $_SESSION['twitter_oauth_uid'] = $result['oauth_id'];
    $_SESSION['twitter_oauth_provider'] = $result['oauth_provider'];
    $_SESSION['twitter_oauth_token'] = $result['oauth_token'];
    $_SESSION['twitter_oauth_secret'] = $result['oauth_secret'];

    if(!empty($_SESSION['twitter_username'])){
    // User is logged in, redirect
    header('Location: /catalog/edition/standard/authenticate/login_success_twitter.php');
    }

}


} else {
    // Something's missing, go back to square 1
    header('Location: /twitter/twitter_login.php');
}

?>

Check if you're getting any errors (`mysql_error()`) and that you're getting results back from the query? — Dan, Jun 16 '11 at 21:04
Given how often this exact error shows up in questions, we should probably make it a hard-coded tip for PHP-tagged questions while typing up the question... — Marc B, Jun 16 '11 at 21:06

score 2 · Answer 1 · answered Jun 16 '11 at 21:04

2

This means that your query failed. I bet if you check the value of $query, you will see that it says false, which is what is returned from mysql_query() when an error occurs.

Furthermore, your code is WIDE OPEN to SQL injection attacks. You should be escaping your data, at least with mysql_real_escape_string(), but I'd strongly recommend taking a parameterized approach with PDO instead, to avoid the problem altogether.

answered Jun 16 '11 at 21:04

Brad

159,648
54
349
530

He might be doing proper validation on the `$user_info` class? – Shef Jun 16 '11 at 21:16
@Shef, given that it is an ID number, probably, but escaping data for SQL is something that should be done where it is used in the context of SQL, not a normal class. In either case, it is a problem that should be fixed. – Brad Jun 16 '11 at 21:17
@Brad: That's a choice you make, as to where do you close the gates. If you let your variables come down to that query unescaped, then you are doomed, especially if you use a class and don't do proper validation at the point where you assign class variables. Personally, I prefer to maintain a variable integrity throughout all my classes, so I know what will that function return every time I call it. – Shef Jun 16 '11 at 21:21
@Shef, agreed, and for especially something like an integer, that is fine. The issue comes when he tries to do something similar for a string. You don't want to store an SQL-escaped version of your string in your class... that would muddy up your data for other purposes. You would only escape that string for SQL when you use it for SQL. Just like you would only run `htmlentities()` on it when you used that data in an HTML context. – Brad Jun 16 '11 at 21:22
@Brad: Yes, you are right, but he can always keep the data safe and `stripslash()` or `htmlentities()` anything he wants out of it, when he is echoing, but it's a personal choice, to the gate keeper to make. This is my way though. :) – Shef Jun 16 '11 at 21:30
I have uploaded a full version of the php file where the error occurs. – Kevin Jun 17 '11 at 00:05

Shef · Answer 2 · 2011-06-17T06:22:08.153

1

Try this code:

$query  =   "SELECT * FROM users_entity WHERE oauth_provider = 'twitter' AND oauth_id = ".$user_info->id.' LIMIT 0,1';
$result = mysql_query($query);

// If exists, let's update it, else add it
if(is_resource($result) && mysql_num_rows($result) > 0){ // a record exists
    $row = mysql_fetch_array($query); // fetch record details

    // Update the tokens
    $query = mysql_query("UPDATE users_entity SET oauth_token = '{$access_token['oauth_token']}', oauth_secret = '{$access_token['oauth_token_secret']}' WHERE oauth_provider = 'twitter' AND oauth_id = {$user_info->id}");
} else{
    $query = mysql_query("INSERT INTO users_entity (oauth_provider, oauth_id, oauth_username, oauth_token, oauth_secret) VALUES ('twitter', {$user_info->id}, '{$user_info->screen_name}', '{$access_token['oauth_token']}', '{$access_token['oauth_token_secret']}')");
    $query = mysql_query("SELECT * FROM users_entity WHERE id = ".mysql_insert_id());
    $result = mysql_fetch_array($query);
}

There was a problem with your first query, mysql_fetch_array() will fetch a single row. Your query was not limiting it to a single row. I added LIMIT 0,1 to do just that.

Before you do a mysql_fetch_array() you should make sure the result returned by mysql_query() is a DB resource, not another type, then you can fetch its data. That's exactly why I added if(is_resource($result) && mysql_num_rows($result) > 0), which will check the result is a resource, and we have at least a record out of that mysql_query() result.

Suggestion #1

If I were you, I would make the oauth_provider and oauth_id a UNIQUE index, and do this job on a single query, i.e. INSERT ... ON DUPLICATE ...

Suggestion #2

If I were you, I would not use mysql_insert_id(), this is not exactly accurate for you, because there might have been inserted another record in-between and you end up with another user's details.

This would give you the accurate information of the newly inserted user:

$query = mysql_query("SELECT * FROM users_entity WHERE oauth_provider = 'twitter' AND oauth_id = {$user_info->id} AND oauth_token = '{$access_token['oauth_token']}' ORDER BY id DESC LIMIT 0,1");

edited Jun 17 '11 at 06:22

answered Jun 16 '11 at 21:08

Shef

44,808
15
79
90

If you are going to provide examples, please please please escape your data. – Brad Jun 16 '11 at 21:14
@Brad: He might be escaping it on the $user_info class? – Shef Jun 16 '11 at 21:17
I have uploaded a full version of the php file where the error occurs. – Kevin Jun 16 '11 at 23:57
I thought it was the result, but now that i think of it is impossible for me to have more than one result – Kevin Jun 16 '11 at 23:58
@Kevin Olseun Karimu: Did my code fix the warning on line `32`? Also, I would strongly suggest that you don't use `mysql_insert_id()`, this is not exactly accurate for you, because there might have been inserted another record in-between and you end up with another id. This would give you the accurate information of the newly inserted user: `"SELECT * FROM users_entity WHERE oauth_provider = 'twitter' AND oauth_id = {$user_info->id} AND oauth_token = '{$access_token['oauth_token']}' ORDER BY id DESC LIMIT 0,1"` – Shef Jun 17 '11 at 06:19
@Shef Yes it did. I have a question: $query = mysql_query("SELECT * FROM users_entity WHERE id = ".mysql_insert_id()); is this a valid? – Kevin Jun 17 '11 at 21:32
@Kevin Oluseun Karimu: It is valid, but it will create problems for you if another record has been inserted at the same time the current insert happens, thus you will end up with the other id. That's why I gave you a suggestion to change that line to an accurate query. The query I am suggesting you will make sure you always get the exact id. :) – Shef Jun 17 '11 at 21:37
Shef, I found that that isn't a issue because it is the last inserted record in the MYSQL CONNECTION. However, i am getting an error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/web03/b556/pow.kkarimu/htdocs/F12_MEDIA/Site/develop/v1/twitter/twitter_oauth.php on line 38 – Kevin Jun 17 '11 at 21:40

MySQL Fetch Array Error

2 Answers2

Suggestion #1

Suggestion #2