Basic Mysql Db Question

[coolo]

How do I set up a MySQL database using the DirectAdmin. Or is there a way to do it outside of DirectAdmin that I don't know about. When I use DirectAdmin, I can set up a database, but I have no idea how to set up tables or anything. I can do it fine using a MySQL prompt. Any ideas?

[skidawg]

When you log into your Direct Admin control panel, there should be a link called phpMyAdmin under Advanced Features. There is also another link to phpMyAdmin on the MySQL Management page.

With phpMyAdmin, you can create tables, create entries to the tables, run sql queries, and some other things that I can't think of right now, all through your web browser.

HTH,
Doug

[ShadowLab]

Originally posted by coolo@Jul 8 2004, 01:29 PM
...Or is there a way to do it outside of DirectAdmin that I don't know about...
If you don't want to go through the DirectAdmin interface you can just go directly to the phpMyAdmin directory and log in with your user name and pw:

http://www.(yourdomainhere).com/phpMyAdmin

[johnta1]

What log-in and password do you use?
The same as the Directadmin one?

[thevillageinn]

if you use the same login and password as your Direct Admin account, you will have access to all of your mySQL databases and tables and info.

you can also use the database specific username and password to work only in the specific database where that user has adequate permissions.

does that help?

[dmc]

ok, how do i connect to the database for queries and updates?
i have a database.. and php code..
i was using

mysql_connect($hostname,$user,$password) syntax (i actually left off the user and password), and no i did not use those values..

my question is what is the host name... www.mydomain.com or is there more too it? and are the username as psswrd required.. i guess i can figure that out through trial and error if i can get it to get the right hostname.

thanks for the help

[eugene]

Originally posted by dmc@Jul 30 2004, 07:34 PM
ok, how do i connect to the database for queries and updates?
i have a database.. and php code..
i was using

mysql_connect($hostname,$user,$password) syntax (i actually left off the user and password), and no i did not use those values..

my question is what is the host name... www.mydomain.com or is there more too it? and are the username as psswrd required.. i guess i can figure that out through trial and error if i can get it to get the right hostname.

thanks for the help
$hostname='localhost';
$user := mysqlUserName, either the master one, your DA login or the one you created for this DB.
$password := the corresponding passwd.

[caddickj]

I'm installing an app and have a question about user names for MySQL.

Is it better to:
a. have a separate username for each DB I have?
b. Have a single DB that I use for all apps?
c. Use the admin (i.e., DA) login for all apps?

What are the pros/cons of these?

Thanks!

[thevillageinn]

In favor of overall security and damage control, I work on the assumption that each app should have it's own DB, and each DB should have it's own user and pass, not the same as the DA login and pass.

Theoretically, (in my opinion) this helps prevent the total loss of your site / DB data should a script / login / pass be compromised.

Of course, the quotas of the accounts may dictate one DB, but you could still use separate user / pass for each app and keep the permissions on the user low.

Hope that helps-

[dbmasters]

I don't think DA will allow you to make the same username for different databases.

I have never been a big fan of a different database for each app...I have different db's for different stages of devlopement. One for production, one for development and one for little client, one-off jobs. The problems comes in when you have 3 or 4 apps, all having data on different databases, the time will eventually come when you want to share data across databases. On a dedicated server, where the user is in total control it can be a bit easiesr to manage this, but in a shared environment, and the security measures and such that are in place, it makes it a bit more work to pull data from different databases together, it's much easier to just pull data acrss different tables in the same database.

[caddickj]

heh heh.... how did I know I'd get conflicting answers? :)

Though I lean towards thevillageinn's view by default, db makes a good point about sharing data. That's certainly a possibility with this (in fact, it's your CMS I'm thinking of installing --- nice job, btw).

[thevillageinn]

yeah, db brings up a good point. when I consider multiple apps on one domain / site and I want them to be interactive with each other, I'd recommend one DB. If you are simply adding a couple of scripts which will not interact with each other, it's up to you.

db's Content Manager is a great script. I've almost finished adding content on a site built with it. It'll be a huge improvement over the previous site which was put together a good number of years ago, by someone else. see it here (http://staging.cwcox.com)

[dbmasters]

Thanks folks, glad you like it. The next version will totally rock...if I ever get it done. I only have about 3 things left to do, just can't find the motivation to do it...

[D9r]

This is just the thread I was looking for. It addresses a couple of questions I've had.

First, I wish there were a way to access all of my databases through the phpMyAdmin control panel with just one login. I'd like to be able to jump from one to another without having to go find the username/password combo for each one. It sounds like thevillageinn gave the answer to that one:Originally posted by thevillageinn@Jul 8 2004, 11:27 PM
if you use the same login and password as your Direct Admin account, you will have access to all of your mySQL databases and tables and info.
Quoted post


Second, it's become annoying having to think of new database names and usernames over and over again. I thought the solution would be to allow one user to have access to multiple databases. But I like dbmasters's idea -- having less databases to keep track of sounds easier to manage.

dbmasters wrote "One for production, one for development and one for little client, one-off jobs." What's the difference between production and development?

[D9r]

Being the google-addict that I am, I found these potentially helpful references:
* Website Production Management Techniques - http://www.macromedia.com/resources/techniques/
* Web Development - http://www.december.com/web/develop.html

[caddickj]

dbmasters wrote "One for production, one for development and one for little client, one-off jobs." What's the difference between production and development?
In development, you can mess around and try things knowing that it might break and you have to wipe the whole thing and start over. In production (i.e., live), that would be a major inconvenience at the least and catastrophic at worst.

In other words, production is not a place to experiment ... development is.

[D9r]

OK. Let's say I create a database for development -- siteusername_development
Then I want to study lots of tutorials and create several projects:

mycms - My CMS from scratch
splogin - SitePoint's user login tutorial
wmlogin - Webmonkey's user login tutorial
punlogin - PunBB's user login system isolated from forum to work by itself
mylogin - My login assimilated from the others

How do you keep track of the tables for 5 to 10 projects within 1 database? It seems like it could get confusing. Wouldn't it be easier to create a separate database for each of those projects?

[eugene]

Many systems (CMS, BB, etc.) make use of a prefix for this purpose. So, you could have it set up so that:
mycms - tables were named mycms_*
splogin - splogin_*
wmlogin - wmlogin_*
etc.

[caddickj]

There are advantages to both methods. And actually, I think I've even asked that same basic question somewhere on these forums. There doesn't seem to be a prevailing point of view -- opinion seemed pretty split.

To keep track of the tables in a single database, you could prefix every related table name similarly. That's the only way I can think of to keep track of them.

But if you've got plenty of databases and don't mind connecting to them all individually, and managing them separately, I don't see why you shouldn't use multiples.

[D9r]

Thanks! I sort of half-way knew that - saw a reference to it once, but couldn't find anything about it this morning so it was unclear. (probably a little brain-numb from sitting here too long) Got it now.

[dbmasters]

Personally, I prefer to have dozens of tables in a single database rather than a few tables in several databases.

More databases make it a hassle to jump between while in PHPMyAdmin, as I stated earlier, harder to share data, more passwords and crap to remember adn all that stuff.

I name tables so they appear in a logical order, such as anything related to the forum could be forum_* tables related to a cms be prefixed cms_* and such. That way, even if you have 30+ tables in your database (such as my home recording site does) it's easy to find them cuz all tables related to a specific tool are all listed together...I do the same with file names...products.php, product_admin.pgp product_cat.php, etc, so all "product" related pages all list together...

make sense?

I dunno if it helps, but thats my two cents.

[D9r]

It makes sense. Sounds like maybe it would be a good habit to use those prefixes even when it's a stand-alone database -- that way it would be easier to merge/integrate in the future.

punBB has a variable for the prefixes in it's install.php page. I haven't tried using variables with phpMyAdmin queries -- can it be done? Would a variable for the prefix work in phpMyAdmin? Or should I just hard-code the prefix for now?

punBB PHP code: $sql = 'CREATE TABLE '.$db_prefix."topics (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
poster VARCHAR(200) NOT NULL DEFAULT '',
subject VARCHAR(255) NOT NULL DEFAULT '',
posted INT(10) UNSIGNED NOT NULL DEFAULT '0',
forum_id INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) TYPE=MyISAM;";

$db->query($sql) or exit('Unable to create table '.$db_prefix.'topics. Please check your settings and try again. <a href="JavaScript: history.go(-1)">Go back</a>.');


phpMyAdmin query, without prefix: CREATE TABLE `topics` (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
poster VARCHAR(200) NOT NULL DEFAULT '',
subject VARCHAR(255) NOT NULL DEFAULT '',
posted INT(10) UNSIGNED NOT NULL DEFAULT '0',
forum_id INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (id)
);



phpMyAdmin query, with prefix? CREATE TABLE $db_prefix.`topics` (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
poster VARCHAR(200) NOT NULL DEFAULT '',
subject VARCHAR(255) NOT NULL DEFAULT '',
posted INT(10) UNSIGNED NOT NULL DEFAULT '0',
forum_id INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (id)
);

[eugene]

You would have to "hard code" the prefix.

[D9r]

Yes. I don't see how the value for the variable can be included in that query. No problem.

BTW, each of our databases comes with a second database named 'test'. Seems to me that would be good for development and such, while using the first one for final products. In other words, each of our databases is really 2 databases. Right?

[D9r]

I appreciate getting these quick answers here. I wish HiveMinds had more activity -- their current lag time is ~12 hours.

[D9r]

So, just to make sure this horse is beaten good, ...

I'm going to install about 6-10 CMSs in one place for testing purposes. I don't anticipate any need for them to share data across each other, so theoretically it would be OK for each to have its own database. But I like Dan's suggestion to put it all in one so that's the way I'm planning to go.

Could there be any potential problems or inconveniences caused by installing 10 CMSs all in one database? They wouldn't all be accessed at the same time so I don't think performance is an issue.

[thevillageinn]

as long as none of the table names are the same (use a different prefix for each CMS) you shouldn't have any problems that I can think of.

[dbmasters]

that'll be one mosterous database tho :-)

[D9r]

Yeah, I know - somewhere in the range of 100 tables maybe. As I said elsewhere I'm already planning to install 6 CMSs that I know of and there will probably be more. The purpose is to have a place to test them out, and to be able to study how the database is designed and how the php is written. Being able to jump around from one CMS to another within the same database would be much easier than having to login to a different database each time. It seems like the right way to go. But you guys are the experts so I thought I should ask for your opinions first.

[eugene]

Should not be any extra problem. MySQL stores the database tables in separate files in a directory with the name of the database. In addition, there is a reference table / database for mysql to correlate users/database/tables.

[D9r]

* Can a database have more than one user?
* If it can, would you even want to have multiple users for the same database?

I'm trying to figure out a naming convention for the user.

[dbmasters]

In DA I don't know if you can have multiple users per database. In enterprise installations multiple users is nice cuz you can have DBA's have a login with god privledges, developers with select, insert,update and delete privledges, but no create or grant ability, web sites can login in having only select abaility....it can be used as a security issue, so if a web site gets hacked and the hacker finds passwords, it'll only get them select commands so as not to be able to do much damage...things like that.

[eugene]

Originally posted by D9r@Jul 12 2005, 06:08 PM
* Can a database have more than one user?
* If it can, would you even want to have multiple users for the same database?

I'm trying to figure out a naming convention for the user.
Quoted post

Yes, a database can have more than one user.

[dbmasters]

interesting, I didn't know DA allowed users to add more than one user per db...good to know.

[D9r]

(1) OK, so if a database has multiple users, does that cause the same problems dbmasters described earlier with respect to having multiple databases?

(2) And using the punbb forum as an example, should I set the user as 'blahblah_punbb'? And then if wordpress is also installed, set its user as 'blahblah_wordpress'? Or should I just use one user for both of them -- 'blahblah_website'?

[dbmasters]

I personally would have one user for all website access...seems overkill to do otherwise...

[D9r]

Yeah, I figured as much after thinking about it. What do you name your database user? Something like: blahblah_website ??? Seems a reasonable choice. (One of these days I really will read a book on databases -- promise.)

[eugene]

I take a bit of a different approach than Dan, but I usually have a different DB and user for each system I develop. If multiple apps need to share the same DB (for cross access to tables), they may still have different users. I often take it a step further and limit the privileges of each user depending on the system's need.