Page 1 of 1
How do I select a database using a variable? php/mysql
Posted: Thu Feb 17, 2005 3:32 pm
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!!
Posted: Thu Feb 17, 2005 3:35 pm
by feyd
different way:
mysql_select_db()
Moved to PHP - Code.
Posted: Thu Feb 17, 2005 4:34 pm
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
Posted: Thu Feb 17, 2005 4:39 pm
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!
Posted: Thu Feb 17, 2005 4:40 pm
by s.dot
It's good practice to close the connection.
Posted: Thu Feb 17, 2005 5:16 pm
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").
Posted: Thu Feb 17, 2005 5:21 pm
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());
Posted: Thu Feb 17, 2005 5:30 pm
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??
Posted: Thu Feb 17, 2005 5:35 pm
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).
Posted: Thu Feb 17, 2005 6:30 pm
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!!
Posted: Thu Feb 17, 2005 6:38 pm
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() )
Posted: Thu Feb 17, 2005 7:11 pm
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;