How do I select a database using a variable? php/mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bmac11
Forum Newbie
Posts: 9
Joined: Thu Feb 17, 2005 3:14 pm

How do I select a database using a variable? php/mysql

Post by bmac11 »

I need to select the database, based on a variable. The variable is composed for two variables. One is chars ($front_db_name) and the other a integer ($topic_id).

I can't figure this out. I have tried several different methods (using mysql_select_db). Either the page doesn't show up or I get the message "No database selected" in my output. Later, I tried to use the the db name, not the variable, and it still wouldn't connect.

Here is the part of my code that needs some help.

Code: Select all

//----------------selects db based on topic id---------------------
$front_db_name = "username_x";
$use_db = "$front_db_name$topic_id";


$connect = mysql_connect("XXXXXX", "XXXXXX", "XXXXXX") or
	die ("Check your server connection.");

//--------------make sure we're using the right database------------
$query = mysql_query("USE $use_db");


//-----------find max number of links in database--------------
$query = mysql_query("SELECT * FROM $linksinfo");
$num_of_links == 1;
while($row = mysql_fetch_array($query)) {
$searching_row = $rowї"entered"];
$num_of_links++;
}

I dont like the way I select the db and query it. I see a lot of other people's code and they do it differently. I'm rather new to this and learned this style from a book. Someone please show me a different way.

Thank you!!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

different way: mysql_select_db()


Moved to PHP - Code.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

This may not be necessary but it is always good practice to pass your connect command in your select database function like this

Code: Select all

$connect = mysql_connect("localhost", "username", "password");
mysql_select_db($use_db, $connect);
also in your die() function use die(mysql_error()) to see exactly what the problem is
bmac11
Forum Newbie
Posts: 9
Joined: Thu Feb 17, 2005 3:14 pm

Post by bmac11 »

Do I have to close the connection to the db or does it do that automatically?

I'll try the other method now.

Thanks!
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

It's good practice to close the connection.
bmac11
Forum Newbie
Posts: 9
Joined: Thu Feb 17, 2005 3:14 pm

Post by bmac11 »

I still have the same problem. Heres what my code looks like now.

Code: Select all

$front_db_name = "hostusername";
$use_db = "$front_db_name$topic_id";


$connect = mysql_connect("XXXXXX", "XXXXXX", "XXXXXX") or
	die ("Cant connect " . mysql_error());

//----------------make sure we're using the right database---------------
$db_select = mysql_select_db($use_db, $connect);
if (!$db_select) {
     die ("Cant use db " . mysql_error());
 }


//---------------find max number of links in database----------------------
$query = mysql_query("SELECT * FROM $linksinfo");
$num_of_links == 1;
while($row = mysql_fetch_array($query)) {
$searching_row = $rowї"entered"];
$num_of_links++;
}
It just hangs and does nothing.
If I skip mysql_select_db and the next 4 lines with "//", it ofcourse works, but says "No Database Selected". Why won't it let me use it?

:: also, I had this problem before while teaching myself php/mysql. The book I read also used mysql_select_db and it didnt work then too. I practicing on a test site that I ran from my machine. I had to use mysql_query("USE db_name") to get it to work. But now I'm on a webhost and need db_name to be a variable. It wouldn't work as mysql_query("USE $db_name").
Last edited by bmac11 on Thu Feb 17, 2005 5:22 pm, edited 1 time in total.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

After $use_db = "$front_db_name$topic_id"; what does:
echo $use_db; show? Is it correct?

Also change:
$query = mysql_query("SELECT * FROM $linksinfo");
to:
$query = mysql_query("SELECT * FROM $linksinfo") or die(mysql_error());
bmac11
Forum Newbie
Posts: 9
Joined: Thu Feb 17, 2005 3:14 pm

Post by bmac11 »

$use_db does show the correct db name.

Ok, I finally get an error message.

"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 '' at line 1"

line 1??
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Sounds to me like $linksinfo isn't set or is empty.
Do an:
echo '**'.$linksinfo.'**';
to check (the **'s are just so you can see the output if it's empty).
bmac11
Forum Newbie
Posts: 9
Joined: Thu Feb 17, 2005 3:14 pm

Post by bmac11 »

hmmm, probably because its not supposed to be a variable.

Must of added it when moving code and debugging.

I removed the $.. and eventually got it to work. But now my php code isnt working right. I dont understand. The same exact code is working on my test site on my computer. Now its on a webhost and it is access a "if" statement that it shouldnt.

it never ends....

I'm gonna go over this some more.. be back if I get stuck again.

Thanks for your help!!
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

When testing it's a good idea to have error_reporting turned up, eg at the top of your code put:
error_reporting(E_ALL);

It will then catch undefined variables etc..etc. ( you may also need to turn display_errors On too either in your php.ini, httpd.conf, a .htaccess or using ini_set() )
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I had a problem before too about an error at line one. Eventually I found out that I had an error in an SQL query somewhere down the page. I had something like this:

SELECT * FROM table WHERE table = 'John's Stuff'";

noties the single ' inside of the two ' 's in my where clause. Check for something like that.

You could most likely figure out if this is the problem by printing all of your SQL queries to the browser.

echo $sql;
Post Reply