Jump to content


Photo

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


  • Please log in to reply
5 replies to this topic

#1 deansatch

deansatch
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 07 July 2006 - 06:22 PM

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


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

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?


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());



but it just doesn't work. I can either get
"Table 'databse2.table1_' doesn't exist "
or
"Table 'databse2.table1_Resource' doesn't exist "
I love it when a plan comes together!

#2 micah1701

micah1701
  • Members
  • PipPipPip
  • Advanced Member
  • 613 posts
  • LocationEllington, CT USA

Posted 07 July 2006 - 07:19 PM

if you are specifiying the database name IN your query, you do not need to use mysql_select_db()
"Confidence in the face of risk."

#3 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 07 July 2006 - 07:36 PM

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());

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:
<?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());
}
?>

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#4 Birdman203

Birdman203
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 07 July 2006 - 09:47 PM

dont do mysql_select_db();

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

#5 Crimpage

Crimpage
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts
  • LocationBrisbane, Australia

Posted 08 July 2006 - 01:42 AM

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.

#6 deansatch

deansatch
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 08 July 2006 - 09:09 AM

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.
I love it when a plan comes together!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users