select field from one db to use as variable in selecting a table in another db

[deansatch]

I want to write this and have $username replaced with the contents of the username field from database1 and "table1_$username" is a table in database2


[color=red]$query = mysql_query("SELECT * FROM table1_$username ORDER BY id DESC") or die(mysql_error()); [/color]

I tried this during pulling my hair out and I think I am on the right track but can't get it to work. Where am I going wrong?


[color=red]require("config.php");
require("functions.php");
$connection = mysql_connect($host,$usr,$pwd);
mysql_select_db('database1') or die('Cannot select database');
$username = mysql_db_query($db, "SELECT username FROM table_customers where id='$id'", $connection);


$connection = mysql_connect($host,$usr,$pwd);
mysql_select_db('database2') or die('Cannot select database');

$query = mysql_query("SELECT * FROM table1_$username ORDER BY id DESC") or die(mysql_error()); [/color]


but it just doesn't work. I can either get
"Table 'databse2.table1_' doesn't exist "
or
"Table 'databse2.table1_Resource' doesn't exist "

[micah1701]

if you are specifiying the database name IN your query, you do not need to use mysql_select_db()

[.josh]

[quote]
require("config.php");
require("functions.php");
$connection = mysql_connect($host,$usr,$pwd);
mysql_select_db('database1') or die('Cannot select database');
$username = mysql_db_query($db, "SELECT username FROM table_customers where id='$id'", $connection);


$connection = mysql_connect($host,$usr,$pwd);
mysql_select_db('database2') or die('Cannot select database');

$query = mysql_query("SELECT * FROM table1_$username ORDER BY id DESC") or die(mysql_error());
[/quote]
you don't need to connect and select more than once in the same script. Also, your first query, $username = mysql_query(...) returns a result source. you have to actually pull the data out from the result source.  Here is an example of how it should work:
[code]
<?php
require("config.php");
require("functions.php");
//connect and select database
$connection = mysql_connect($host,$usr,$pwd);
mysql_select_db('database1') or die('Cannot select database');

//query string
$query = "SELECT username FROM table_customers where id='$id'";
//run the query string and return the results
//as said, $result will be a result source containing your data, not
//simply the name
$result = mysql_query($query) or die(mysql_error());

//do some error checking to see if you got something back
//is always a good idea. we'll check to see if a row was returned
$rows = mysql_fetch_rows($result);
//and then base the rest of your code on if something was returned
if ($rows > 0) {
  //extract the info from the result source
  $info = mysql_fetch_array($result);
  //assign the name to your username variable
  $username = $info['username'];
  //do your next query string
  $query = "SELECT * FROM table1_$username ORDER BY id DESC";
  $result = mysql_query($query) or die(mysql_error());
}
?>
[/code]

[Birdman203]

dont do mysql_select_db();

on your query do mysql_db_query("db", "select...");

[Crimpage]

On a side note: I dont think its very smart from a design point of view to have a table per username. There should never really be the case where you need to do this either... Have a bit of a read into relational database structures and designs.

[deansatch]

I have to have one table per user as it is for a guestbook per user. I'd rather keep these tables in a seperate database to the customer database.
the code still doesn't work. Still getting the same messages. I thought I would have to select databases twice since I am selecting information from two different databases.
Still confused.