Having trouble fetching data using mysql_fetch_assoc

Dev Shed Forums Sponsor:

November 25th, 2012, 04:10 PM

codeman061988

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 4 h 55 m 51 sec
Reputation Power: 0

Having trouble fetching data using mysql_fetch_assoc

Hello,
I writing a script that is supposed to fetch specific user data and display it in an array. I don't know if I am writing the query wrong. I've tried using ' and just leaving the fields blank but either way I get a Boolean message.

PHP Code:


		
			
 $fields = ' `' . implode(' `', $func_get_args) . ' `';

$data = mysql_fetch_assoc(mysql_query("SELECT $fields FROM users WERE user_id = $user_id"));



print_r($data);

die();

here is the error...

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\core\functions\users.php on line 13

I don't see the Boolean, any suggestions?

November 25th, 2012, 04:22 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,835

Time spent in forums: 1 Month 2 Weeks 1 Day 4 h 22 m 17 sec
Reputation Power: 811

Hi,

mysql_query() returns a boolean "false" in case the query is incorrect. So obviously there's something wrong with the SQL. Output it and check it. You can also test it in phpmyadmin.

Shouldn't the "WERE" be a "WHERE"? And what's with the strange $func_get_args? Are you sure it's not func_get_args() as a function call? And shouldn't you put commas between the fields?

November 25th, 2012, 04:35 PM

msteudel

Contributing User

Join Date: Sep 2002

Location: Seattle, U.S.A.

Posts: 712

Time spent in forums: 4 Days 11 h 4 m 59 sec
Reputation Power: 11

If you check for errors in your code and output the mysql_error report, then you'll get some more information.

For example (from the php.net/mysql_query page )

PHP Code:


		
			
 $result = mysql_query($query);



if (!$result) {

    $message  = 'Invalid query: ' . mysql_error() . "\n";

    $message .= 'Whole query: ' . $query;

    die($message);

}

November 25th, 2012, 04:38 PM

codeman061988

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 4 h 55 m 51 sec
Reputation Power: 0

Ah, well the typo might be part of the problem. well, I can echo the SQL query and it will display the results in an array...the problem is when I try to enclose the query in the mysql_fetch_assoc function.

PHP Code:


		
			
echo "SELECT $fields FROM users WHERE user_id = $user_id";

PHP Code:


		
			
<?php 

function user_data($user_id) {

    $data = array();

    $user_id = (int)$user_id;

    

    $func_num_args = func_num_args();

    $func_get_args = func_get_args();

    

    if ($func_num_args > 1) {

        unset($func_get_args[0]);

        

        $fields = ' `' . implode(' `', $func_get_args) . ' `';

        $data = mysql_fetch_assoc(mysql_query("SELECT $fields FROM users WHERE user_id = $user_id"));

        

        print_r($data);

        die();

        

        return $data;

    }

}



function logged_in() {

    return (isset($_SESSION['user_id'])) ? true : false;

}



function user_exists($username) {

    $username = sanitize($username);

    return (mysql_result(mysql_query("SELECT COUNT(user_id) FROM users WHERE username ='$username'"), 0) == 1) ? true : false;

}

function user_active($username) {

    $username = sanitize($username);

    return (mysql_result(mysql_query("SELECT COUNT(user_id) FROM users WHERE username ='$username' AND active ='1'"), 0) == 1) ? true : false;



}



function user_id_from_username($username){

    $username = sanitize($username);

    return mysql_result(mysql_query("SELECT user_id FROM users WHERE username ='$username'"), 0, 'user_id');

}



function login($username, $password) {

    $user_id = user_id_from_username($username);

    

    $username = sanitize($username);

    $password = md5($password);

    

    return (mysql_result(mysql_query("SELECT COUNT(user_id) FROM users WHERE username ='$username' AND password = '$password'"), 0) == 1) ? $user_id : false;

}



?>

November 25th, 2012, 04:59 PM

codeman061988

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 4 h 55 m 51 sec
Reputation Power: 0

Ill try the error reporting in a bit after some tasty bbq.
I'm sure its something simple. By the way, this code is from a login script from alex at php tutorials. Found it on youtube. There are several parts to it. It has a forum but I can't find anyone who has had a problem on this line.

November 25th, 2012, 05:03 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,835

Time spent in forums: 1 Month 2 Weeks 1 Day 4 h 22 m 17 sec
Reputation Power: 811

Quote:

Originally Posted by codeman061988

PHP Code:


		
			
echo "SELECT $fields FROM users WHERE user_id = $user_id";

And what does that say? What happens when you execute the query in phpmyadmin?

And to ask once again: Should there not be commas between the fields? If I call your method with multiple args, I get this:

Code:

SELECT `field1 `field2 `field3` ...

That's obviously not right. The implode() separator must be "`,`" with two backticks and a comma in between.

Like I already said: A boolean "false" means that your query is wrong. It is not a problem of mysql_fetch_assoc. That's just were the error will take effect.

November 25th, 2012, 05:25 PM

codeman061988

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 4 h 55 m 51 sec
Reputation Power: 0

Well,
I ran the query in phpmyadmin and it resulted in this error...

#1054 - Unknown column '$fields' in 'field list'

I fixed the separator problem in the implode function.
In the tutorial, the guy echos the query and get the fields in an array which is good but placing the query in mysql_fetch_assoc is supposed to grab the field values to be displayed later.

November 25th, 2012, 05:28 PM

codeman061988

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 4 h 55 m 51 sec
Reputation Power: 0

I guess I need to format the implode correctly when I assign it to to $fields.

PHP Code:


		
			
 $fields = "`,`" . implode("`,`" $func_get_args) . "`,`";

November 25th, 2012, 06:13 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,835

Time spent in forums: 1 Month 2 Weeks 1 Day 4 h 22 m 17 sec
Reputation Power: 811

Quote:

Originally Posted by codeman061988

Well,
I ran the query in phpmyadmin and it resulted in this error...

#1054 - Unknown column '$fields' in 'field list'

Now you've taken the PHP string expression from the code. You need to actually output it with "echo" and use the evaluated result. Raw PHP code with variables in it won't work as an SQL query.

Echo the query and copy it here into the forum. Then it shouldn't be too hard to find out why it doesn't work.

Quote:

Originally Posted by codeman061988

[QUOTE=codeman061988]I guess I need to format the implode correctly when I assign it to to $fields.

PHP Code:


		
			
 $fields = "`,`" . implode("`,`" $func_get_args) . "`,`";

Now your query looks like this:

Code:

SELECT `,`col1`,`col1`,` ...

This cannot possibly work.

You need commas between the fields, but not before and after the fields.

Last edited by Jacques1 : November 25th, 2012 at 06:16 PM.

#10

November 25th, 2012, 07:42 PM

codeman061988

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 4 h 55 m 51 sec
Reputation Power: 0

And the winner is....

PHP Code:


		
			
 $fields = '`' . implode('`, `', $func_get_args) . '`';

my punctuation was wrong for this array.

Here is the result I was looking for...

Array ( [user_id] => 1 [username] => codeman061988 [password] => 5f4dcc3b5aa765d61d8327deb882cf99 [first_name] => Cody [last_name] => Hicks [email] => codyhicks57@hotmail.com )

This is actually a cool script for login. It's recent too. It teaches you how to separate header/footer and several logic components....

http://www.youtube.com/watch?v=9kyQGBABA38&feature=BFa&list=ECE134D877783367C7

I appreciate your help!

Having trouble fetching data using mysql_fetch_assoc

Developer Shed Advertisers and Affiliates