mysql: works ok in console, in php will not access data

Question

connecting either as root or regular user, everything works fine from mysql console

on php, connects fine, set schema fine, but will not pull data from schema.

// connect and set schema
$_db = mysql_connect( '127.0.0.1', 'root', '*******' );
mysql_select_db( 'suangao' );
echo "schema = ".db_get_col("select database()");
// => schema = suangao

// non-table query
$result = mysql_query( "select 1" );
echo "rows 1 = ".mysql_num_rows($result);
// => rows 1 = 1

// simplest table query
$result = mysql_query( "select 1 from users" );
echo "rows 2 = ".mysql_num_rows($result);
// => rows 2 = 0

php does not even see users table, but in mysql created, inserted, etc

have same setup working fine on different machine, but cannot get this to fly

mea culpa! indeed the table users was empty! I had mistakenly believed that select 1 from table would always return 1 if the table existed - evidently I was incorrect!

Are you sure your table users isn't empty? In all other cases it have to work… EDIT: "through php does not even see users table" that again must have something to do with user rights in MySQL… — feeela
@feeela - that's what I was thinking, but users exists and has rows - and when logged in through console have full access - only through php connection is access limited. — cc young

TRD TRD · Accepted Answer · 2011-08-02T11:41:01

As far as I know a mysql user has privileges depending on his host mask.
I don't know which host is used if you connect via mysql console and don't add a host while connecting. But it seems to be a different one than your script uses and so your permissions are insufficient.

Try changing 127.0.0.1 in your connection script into localhost. Although this is meant to be the same it isn't the same in detail. Then the connected user should have another host mask and hopefully the same for which you have granted the needed permissions.

Finally you could check the privileges by connection via console with root account and looking into the table user_privileges. The entries there should speak for themselves.

mysql: works ok in console, in php will not access data

1 Answers