connect to mysql directly

[Viscount]

Background…

Over the last year I’ve learning MySQL and recently trying to learn php.
I’d like to think I have a solid background in html and css but find learning php quite alien and confusing compared to html.

I’ve been reading Learning php, MySQL, JavaScript & CSS by Robin Nixon, which since I’ve found out is out of date on the php side of things, despite being published in 2012. But anyway I’ve put that to one side since I can still learn the basics of php and update to PDO later.
(I’ve skipped Javascript for now as php is confusing enough for me!)

On to my question…

I’ve got to the tutorials and how you have one php page that accesses the database via mysql_connect and then you include that on subsequent pages.

mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

I think it is saying that php needs to connect to the database with a username/password before you can do anything else? That kind of makes sense. However, when that tutorial goes on to creating usernames/passwords for each ‘user’ the user table is created within the database not in the MySQL.user table
That seems crazy to me since you can’t assign table permissions from a ‘user’ table within a database.

What I really want to do is have a login form on the php page which connects the user directly to my database using a username/password from the mysql user table not a ‘user’ table from within the database which is useless as permissions can’t be assigned to tables. (at least I think they can’t)

Ideally any queries sent back to the user will display data only they can see.

Such as “show tables from mydb;” will be unique to each user.

Is this possible in php?

[JimL]

You should handle permissions yourself, opening up your SQL server like that sounds like a bad idea.

I suspect this is a XY problem.

What is the XY problem?

What do you want to achieve?

Og and don’t use mysql_* functions, they’re deprecated. If you want easy, fast and secure php database handling you can check out my PDO class.

[Viscount]

When you connect to MySQL via say via MS Access or even on the command line using your username and password your table permissions have been set up in the MySQL users table
So if I’ve run the query “show tables from mydb”; it will be different to each user depending on their permissions.

In the php tutorials I’ve done the same thing with $query = “show tables from mydb”;
The credentials for this query have been set up previously in a php file.
Like this: http://php.about.com/od/phpwithmysql/qt/mysql_connect.htm

The php website which would access the database will be used by multiple users from the MySQL.users table. So they accessing a page with that query would get different results.
Is it possible to have different users?
How do they login if the credentials are setup for someone else in the php file?

P.S. I may not be able to respond until a few days, but thanks for looking.
It may be that I just need to go back to the tutorials and redo them.

[JimL]

As said, you should set up your own permissions system (or use a framework). You should not start to mix in MySQL server permissions into this.

[Redscouse]

I think it is saying that php needs to connect to the database with a username/password before you can do anything else? That kind of makes sense. However, when that tutorial goes on to creating usernames/passwords for each ‘user’ the user table is created within the database not in the MySQL.user table
That seems crazy to me since you can’t assign table permissions from a ‘user’ table within a database.

I think you may be getting confused between Mysql users and what will be your website users.

The login form you are creating will allow logging in to a restricted area of a website/app/etc. using a username/password combo stored in the database.

The website/app itself will need a connection to MySql and should be setup as a mysql user with as minimal MYSQL.USER permissions as possible IE: no CREATE, DROP, ALTER etc. permissions that could wreak havoc on your database.

Hope that makes things clearer.

Red

Note: I edited my answer to make it a little clearer.

[Viscount]

Thanks for your responses so far.

What I would like to achieve is an interface a bit like phpadmin but for users. They can log in see the tables/views they have permission to use and conduct searches for data.

This is going to be a internal website within a Windows domain with some external access but only via SSL VPN, not public facing.

Can this be done with php or should I be looking at another programming language?

[JimL]

Yes it is possible, it’s just not recommended if you don’t know exactly what you’re doing.

But yeah, if you’re going to make some kind of database management script then you could do what you’re sketching out. I would create a website where the user can login (to your app), then the app will connect to the DB with the same credentials (or stored in your users table) and query which databases/tables the user has access to.

[Viscount]

Well I definitely don’t know what I’m doing… so php may not be the best course of action

Currrently we use Microsoft Access to open the tables (ODBC connector) and setup credentials per user.
Access works fine for most tasks but requires using pass-through SQL for complex queries which many users are uncomfortable with. You also cannot use MS Access over a VPN.

So I’ve been looking an interface which could do this, either by buying or building and I thought PHP could do that.

Looking around the web I’ve found this http://stackoverflow.com/questions/10842618/gui-mysql-database-frontend which is basically what I’m looking for, accept ‘Free’ is not a requirement but cheap is. Otherwise I’d have to build it myself.

[Redscouse]

May I ask a question…

Only today I have been discussing with Jim creating a CMS system and I’m still trying to decide, do I re-invent the wheel (IE: build my own) or do i use a tried and tested system (Joomla, Wordpress etc.)

So, how does that relate to your problem/solution.

What I would like to achieve is an interface a bit like phpadmin but for users.

Why not use phpadmin ?

Red.

[Viscount]

If there is something I can use without re-inventing the wheel I’m all for it. Thus I’m pleased that Access can connect to a MySQL database.

phpmyadmin has too many features I would not want my users to see. I certainly would not want them to be able to modify table structures (although their privileges would not allow them) let alone show them that the option is available. We wouldn’t be able to customise it such as have saved queries.

I was looking at this earlier: http://mysqlreports.com/screenshots.html which has some of what I’m looking for. The login screen exactly what I want to see.