-2

When I execute this query:

SELECT * FROM (SELECT `db` FROM site WHERE `url`='$site') . smspq

I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. smspq' at line 1

This query works, though:

SELECT * FROM `database` . `table`

Why?

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
h0mayun
  • 3,466
  • 31
  • 40
  • Why you want nested SELECT statements? – Mr. Alien Oct 28 '12 at 06:00
  • It seems that your derived table is missing an alias.[Here's a related stackoverflow entry.][1] [1]: http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias – nurettin Oct 28 '12 at 06:02
  • 1
    it seems that he tries to get result from different database depending on url ;o) – Sir Rufo Oct 28 '12 at 06:02
  • h0mayun, I've rolled back your edit because it would have made one of the existing answers, and several comments, make no sense. – Michael Petrotta Oct 28 '12 at 06:23

2 Answers2

1

Maybe you want to add an alias on the subquery, if that's so, replace . with AS or just remove the . as AS is optional

SELECT * 
FROM (SELECT `db` 
      FROM site 
      WHERE `url`='$site')  smspq
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • no i want query like SELECT * FROM database . table but database name is dynamic depending on url – – h0mayun Oct 28 '12 at 06:08
0

This is not the way SQL works. You have to state clearly what table in what database you like to work on.

You can set up the most liked db by

USE mydatabase;

and every request to a table will be passed to that database.

SELECT * FROM mytable;
SELECT * FROM mydatabase.mytable; -- same as above because of USE mytable;

The only way is to use Prepare and Execute (have a look at the docs), but with plain commands (SELECT, UPDATE,...) you can't reach your desired goal that way

UPDATE

You have to split the Query:

First Query

USE mycommondb;
SELECT `db` FROM site WHERE `url`='$site';

Second:

USE $db_from_site;
SELECT * FROM table;

(because of the $ i think it will be called from php ;o) )

Sir Rufo
  • 18,395
  • 2
  • 39
  • 73