Query SQL Tables from Web Server

[Avatarati]

Sorry in advance if I posted this under the wrong forum, I couldn't really find a category to throw it under...

I've seen a few servers that offer a way to manage auction house listings and lookup monster/item information. I know all of this is done through PHP and accessing their MySQL database tables. I got all that, but the issue I'm having is actually accessing it in the first place.

I found a post by Tagban from some time ago (been trying to relocate it for like the last 20 minutes...) about how to show a server status using PHP. Simple enough - just use a simple PHP script to access your MySQL. Trouble is, I cannot even access my database remotely.

I'm totally new to MySQL and networking, spent the last 24 hours it seems researching the subject and have hit dead ends left and right. I've added user accounts to my database under MySQL workbench, assigned them specifically to my web server IP (where my Wordpress site is hosted), tried adding rules to my firewall on my VPS, but I still have yet to successfully connect to MySQL database.

My end goal is to allow players the ability to modify auction house listings, but I'd be happy with just doing a simple item lookup for now. I'm using all of the software and programs listed in the Building the Server walkthrough. Can anyone point me in the right direction as to how to allow the ability to access my database from an outside source?

Would I need to add a bind-address setting in my.ini config for MySQL Server 5.6? While we're on that subject, I've seen several websites suggest commenting out 127.0.0.1 to allow for binding. I know that somehow I need to access my database using my VPS IP, but how do I do that? Add in a line in the my.ini?

Any help is greatly appreciated, thanks!

[kjLotus]

the bind address will depend on where you're accessing it from: 127.0.0.1 means only the same machine that is running the server can connect to it. if you want machines on your local area network to be able to access it, you bind to the local area network address (192.168.... usually). if you need to access it from somewhere over the internet, you bind with your external IP and you definitely want to restrict access to that (user/password, probably having external connections only read permissions).

my configuration file, for example, binds to 192.168.0.140, because that's my servers LAN IP address (and I can connect to it from my desktop using that IP)

[whasf]

the bind address will depend on where you're accessing it from: 127.0.0.1 means only the same machine that is running the server can connect to it. if you want machines on your local area network to be able to access it, you bind to the local area network address (192.168.... usually). if you need to access it from somewhere over the internet, you bind with your external IP and you definitely want to restrict access to that (user/password, probably having external connections only read permissions).

my configuration file, for example, binds to 192.168.0.140, because that's my servers LAN IP address (and I can connect to it from my desktop using that IP)

Now we know your IP address! I'm going to build a GUI to hack it!

[nasomi]

For modification, you're going to want some sort of authentication going on, else anyone can do anything, and that's bad.

For sever status, this one is easy, just pop in any html file.

Code: Select all

<?php
	#Define your variables.
	$sqlip = yourip;
	$sqluser = user;
	$sqlpass = password;
	$sqldb = database;
	#Define your sql setup.
	mysql_connect("$sqlip", "$sqluser", "$sqlpass") or die("My bad... " . mysql_error());
	mysql_select_db("$sqldb") or die("My bad... " . mysql_error());
	#Set server status defaults.
	$lobby_sts = "<font color=#FF0000>Down</font>";
	#Check if socket can be opened.
	$fp = fsockopen("10.0.0.220", 54231, $errno, $errstr, 1);
	if($fp !== false) $lobby_sts="<font color=#00FF00>Up</font>";
	#Print the results.
	echo "Lobby Server Status: " . $lobby_sts . "<br>Players Online: " . mysql_num_rows($result);
?>

[Avatarati]

Thank you for all the replies. Apologies - I completely forgot to check in on this.

Turns out my web host didn't allow remote SQL connections, so I actually switched web hosts to the same company that hosts my game server.

Got the player list working on my website, now I'm just trying to figure out a workaround for fsockopen. It's not working due to "security reasons." Probably a policy with host provider.

[wigit]

Late reply since you already have a solution but you could always to phpMyAdmin:

https://www.phpmyadmin.net/

Since I don't see an OS listed:

Windows:
https://www.devside.net/guides/windows/phpmyadmin

Ubuntu:
https://www.digitalocean.com/community/ ... untu-14-04

Hope that helps someone in the future. Also really good for folks that don't know much about SQL since it has an easy to use interface and will print the commands it ran.