Using PHP and MySQL there are a couple of ways to select which specific database you want to query.
For instance, $connection->select_db("target_db"); could be used in PHP to predefine the database before running the sql code. Then run "SELECT * FROM example_tbl".
Or you could use "SELECT * FROM target_db.example_tbl" to specify the database inside of the sql syntax itself.
My question is, what is considered best practice? I seem to find myself using the latter example more, simply because of the necessity to switch target databases mid-query if performing JOINs. It also means a line less of PHP and I think makes a bit more sense when reading back through as the selection of the database is in the exact same place as the table etc.
Also, slight aside, how often should I need to be changing databases? When is it recommended to store tables in separate databases? Users should probably go in a separate database to something like a 'posts_tbl' and 'comments_tbl' if creating a social network I assume. But would 'posts_tbl' and 'comments_tbl' reside in the same database or have their own dedicated 'posts_db' and 'comments_db'?
It comes down to what is more convenient. Speed differences are negligible, so as you said, the later is more convenient if you need to operate on more than one database, however the initial one would be better in the case that you don't need to change your database, since it'll make your code cleaner / easier to read.
Best practice is to have as less databases as possible. Post and comments should be definetaly in the same db for joins. Two databases should be considered if you have to two applications running on the same server but connection between them is quite unique
If both databases are limited to the same application then you should only use one, however if there exists data entry from external sources (another application) then it's a good option to have two databases, however if you're going to use multiple databases and feel the explicit need of switching between them for specific queries then you can create a routine in a common schema that receives a parameter (maybe from an already defined array) to indicate the database and build the query inside using PREPARE and EXECUTE statements.
DELIMITER $$
CREATE PROCEDURE (IN db_name varchar(255)
, IN TableName varchar(255))
BEGIN
DECLARE query VARCHAR(1000);
DECLARE allowed BOOLEAN;
SELECT 1 INTO NameAllowed WHERE db_name IN ('db1','db2');
IF (allowed = 1) THEN BEGIN
SET query = CONCAT('SELECT * FROM'
,@db_name,'.',@TableName);
PREPARE stmt FROM query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END; END IF;
END $$
However i would recommed having more specific stored procedures that doesn't take parameters to indicate data source.