Jump to content

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


deansatch

Recommended Posts

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 "
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.