SQL fails in PHP but works in command line mysql

Question

SQL Code:

SELECT id, album_date AS timestamp, CONCAT((SELECT detail_value
FROM people_db.user_details_tbl WHERE detail_field = 'first_name' AND user_id = pictures_db.albums.owner), ' uploaded pictures!') AS title_html 
FROM pictures_db.albums 
WHERE id IN 
(SELECT DISTINCT(album_id) 
FROM pictures_db.album_pics 
WHERE pic_id IN 
    (SELECT DISTINCT(picture_id) 
    FROM pictures_db.picture_access_tbl 
    WHERE grantee_group_id IN 
        (SELECT group_id 
        FROM people_db.group_membership_tbl
        WHERE member_id = '2'
        )
     )
);

PHP Code:

$albums_sql = mysql_query("SELECT id, album_date AS timestamp, CONCAT((SELECT detail_value 
    FROM people_db.user_details_tbl 
    WHERE detail_field = 'first_name' AND user_id = pictures_db.albums.owner), ' uploaded pictures!') AS title_html 
    FROM pictures_db.albums 
    WHERE id IN (
          SELECT DISTINCT(album_id) 
          FROM pictures_db.album_pics 
          WHERE pic_id IN (
              SELECT DISTINCT(picture_id) 
              FROM pictures_db.picture_access_tbl 
              WHERE grantee_group_id IN (
                  SELECT group_id 
                  FROM people_db.group_membership_tbl 
                  WHERE member_id = '2'
                  )
               )
             )") or die(mysql_error());

When the PHP is run, the error is: Table 'pictures_db.albums' doesn't exist

I tried executing as the same user, regranted all permissions, and even flushed privileges. Works in shell, not in PHP. Any ideas?

My first thought is that maybe you shouldn't have pictures_db in the front. Just refer to the table name, since you should already be connected to a specific database. Also, would you consider formatting the code on multiple lines or something? — Dan
– Dan, Commented Jan 28, 2013 at 20:07
B.T.W. is member_id a VARCHAR? And if this is new code use MYSQLI instead of MYSQL. — Mr. Radical
– Mr. Radical, Commented Jan 28, 2013 at 20:13

Eric J. · Accepted Answer · 2013-01-28 20:08:52Z

1

The error message is quite clear: MySQL sees the database pictures_db but not the table albums.

That could be due to permissions, but you seem to have checked that thoroughly.

Another possible reason is that the connection string you're using in PHP points to a different database instance than the one you're using at the command line. Perhaps the connection string still points to a different environment, such as DEV but you're in QA or points to an old test version of the database?

answered Jan 28, 2013 at 20:08

Eric J.

151k65 gold badges353 silver badges563 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1955162 Over a year ago

Right on! Wrong instance... I am coding too late to catch that myself. Thanks

Mr. Radical Over a year ago

@user1955162 is your question solved? Please accept the answer.

hall.stephenk · Accepted Answer · 2013-01-28 20:08:47Z

0

Do you call mysql_select_db() before running the query?

mysql_select_db('pictures_db');

answered Jan 28, 2013 at 20:08

hall.stephenk

1,1657 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL fails in PHP but works in command line mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related