10 Replies - 789 Views - Last Post: 22 January 2016 - 03:49 PM

#1 Avatarati   User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 44
  • Joined: 15-December 15

Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 08:31 AM

Hi again!

I'm having a tough time figuring all of this out on my own, so I'm hoping to get some guidance/clarification on the steps I need to take on this project.

Here is my end result:

I have a VPS (Windows Server 2008) that has an instance of MySQL Server 5.7. I have 93 tables in my database that I want to retrieve data from to display on my Wordpress site. I plan to have a few "modules" where users can query data from the database, and also have access to a few tables where they can modify entries. I can worry about the modifications later, the main thing I'm trying to do now is provide my users the ability to retrieve data from various tables.

I've been having trouble connecting to the database, despite me setting up a user account specifically for this in MySQL Workbench. I'm a little confused about the whole thing to be honest, so I have a few questions that I hope someone can nudge me in the right direction on.

1. Should I be accessing my database directly using my VPS IP? Or should I be using an SSH tunnel? I looked in the config file for MySQL, I cannot seem to find where it's bound to any specific port. Even the localhost, 127.0.0.1 isn't listed. Several websites suggest that I comment this line out, but I can't even find it to begin with. If I were to access this database remotely from my website PHP code, do I need to define a bind-address in my config file? If so, should this be the IP of my VPS?

2. Do I need to access the specific 3306 port when trying to connect using PHP? Or should my VPS IP work by itself?

3. Should I change the default port for security reasons?

4. Shouldn't the port already be whitelisted in the firewall? Do I need to modify the firewall in any way?

All-in-all, I just would like to be able to connect to MySQL database using PHP code in Wordpress. I've put many hours of research into this, but cannot seem to crack it. I don't think I'm asking the right questions...

Any suggestions would be appreciated, thank you!

Is This A Good Question/Topic? 0
  • +

Replies To: Retrieving Data from MySQL Database via PHP

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3062
  • View blog
  • Posts: 11,753
  • Joined: 03-December 12

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 11:12 AM

If this for a WordPress site, you can connect to the Database, assuming it is hosted on the same MySQL Server, you just need to change the database that is being used.


I have concerns over users directly accessing any database, however.
Was This Post Helpful? 0
  • +
  • -

#3 Avatarati   User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 44
  • Joined: 15-December 15

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 12:47 PM

Ya, I have concerns over that too.

To expand a little on what I'm trying to do though...

I'm hosting a game server on a VPS. The game is written in C++ and uses Lua as well. The database, of course, contains all of the character data and variables, as well as NPC, zone, etc information. Players can buy and sell items in a virtual market. What I'd like is to offer my players a way of checking their market listings and be able to modify those listings from a web site.

So, the basic idea of my goal is to 1) be able to display if the server is up or not from the main page (simple PHP script) and 2) be able to connect to a MySQL server in order to query tables hosted on my VPS and display the information returned on a website.

I'm planning on having a few modules where players can log in, have their character data displayed, look up NPC information, manage their market listings, and so forth. For the most part, they just need rights to view data in the database (rank, level, playtime, etc). The only caveat is the market tool, where they are able to actually edit the price of a listing, or remove it from the listings completely. This should be the only table they have read/write access to...but only for their own particular character.

It's a daunting task for me (having no knowledge of PHP or MySQL). But I learn best by facing these kinds of challenges. The main goal of this whole project is for me to learn C++ and Lua better by practicing it through the modification of game behavior. I find it very enjoyable, actually, and keeps me engaged with learning C++. But, I'd like to build a solid player base by offering them useful tools, part of my "quality of life" effort. I plan on doing this for a long time, and I think the concepts and lessons I'll learn from it will be worth the effort. I'm working on game mechanics, scripting, HTML/CSS/PHP development, Javascript, MySQL database management, and many other things. It's fun and useful.

So, with all that said - the biggest part I'm stuck on right now is how to access and retrieve data from a MySQL server located on my VPS. I need to be able to pull data from those tables from my website in order to display information contained within that database to my players. I want them to be able to query those tables to retrieve the information they need in order to play the game.

Hopefully this fleshes out a few more details about what my goal is in this.

This post has been edited by Avatarati: 22 January 2016 - 12:50 PM

Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3062
  • View blog
  • Posts: 11,753
  • Joined: 03-December 12

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 12:52 PM

I don't have experience with databases in C++ and I have zero experience, yet, with Lua.

Questions:
Is the database server on the same server that is trying to access it or is it on another server?

The best thing to do, after you can connect, is to create stored procedures for the users. A user that has execute permissions. From there it is just a matter of passing in the proper procedures to the procedure so it can run.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,341
  • Joined: 12-December 12

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 01:14 PM

View Postastonecipher, on 22 January 2016 - 07:52 PM, said:

.. and I have zero experience, yet, with Lua.

If it is of interest (to anyone) this site gives a quick look at different languages:

Learn X in Y Minutes
Was This Post Helpful? 0
  • +
  • -

#6 Avatarati   User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 44
  • Joined: 15-December 15

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 01:44 PM

View Postastonecipher, on 22 January 2016 - 10:52 PM, said:

I don't have experience with databases in C++ and I have zero experience, yet, with Lua.

Questions:
Is the database server on the same server that is trying to access it or is it on another server?

The best thing to do, after you can connect, is to create stored procedures for the users. A user that has execute permissions. From there it is just a matter of passing in the proper procedures to the procedure so it can run.


The C++ and Lua are outside the scope, those are just the mechanics behind how the game is executed. All of the data are stored in a simple MySQL database in which the C++ and Lua references.

The game servers and database server are all located on the same VPS machine. My website is hosted on another IP, a dedicated web server. What I need to do is use PHP on my Wordpress site, let's say IP 11.111.111.111, and query tables hosted under 22.222.222.222.

I've set up user accounts through MySQL Workbench for this particular database, and even associated them with 11.111.111.111, but I still cannot get access to it from 22.222.222.222. Always says that my connection failed. I'm wondering if it could be a firewall issue? I've tried isolating the port call though, at 3306, and it still fails.

I'm primarily interested in IP bindings...do I need to somehow enable MySQL database to receive external connections? Perhaps add bind-address rule in the my.ini config to allow it to bind to my VPS IP?

Sorry, I'm not sure if I'm even asking the right questions or framing the issue correctly. I've just been a little frustrated with it as of late. It's turned into an obsession trying to figure it out haha.

Quote

If it is of interest (to anyone) this site gives a quick look at different languages:

Learn X in Y Minutes


Thank you, Andrewsw. Lua is actually pretty weird to me. It's so simple yet strangely complicated to get the hang of...I find myself making more mistakes in that than in C++! It's such a loose structure.
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3062
  • View blog
  • Posts: 11,753
  • Joined: 03-December 12

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 02:05 PM

Okay, if you are using PHP, how are you querying the database? mysqli_ and PDO are the norm. Are they giving error messages? What are they?
Was This Post Helpful? 0
  • +
  • -

#8 Avatarati   User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 44
  • Joined: 15-December 15

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 02:22 PM

View Postastonecipher, on 23 January 2016 - 12:05 AM, said:

Okay, if you are using PHP, how are you querying the database? mysqli_ and PDO are the norm. Are they giving error messages? What are they?


using mysql_connect then querying select tables.

<?php

   mysql_connect("VPS_IP_HERE", "USER_NAME_HERE", "PASSWORD_HERE") or die(mysql_error());
   mysql_select_db("database_name") or die(mysql_error());
      $query = "SELECT chars.charid, chars.charname, accounts_sessions.charid, zone_settings.zoneid, zone_settings.name, chars.pos_zone FROM accounts_sessions, chars, zone_settings WHERE accounts_sessions.charid = chars.charid AND zone_settings.zoneid = chars.pos_zone";
    
      $result = mysql_query($query) or die(mysql_error());

      while($row = mysql_fetch_array($result)){
         echo $row['charname']. " - ". $row['name'];
         echo "<br />";
      }   

?>


Error is just simply a failure to connect message:

Can't connect to MySQL server on 'XX.XXX.XXX.XXX' (111)

This post has been edited by Avatarati: 22 January 2016 - 02:22 PM

Was This Post Helpful? 0
  • +
  • -

#9 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3062
  • View blog
  • Posts: 11,753
  • Joined: 03-December 12

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 02:34 PM

Okay, first thing first. You php code needs to be updated. As I said, mysqli_ and pdo are the standard. mysql_ functions are deprecated and not in the current release (PHP 7).

The next thing you need to check, being that the database and where your requests are coming from are on different servers in the actual permissions on the database server. Hopefully, you have something like cPanel to make this easier, but you need to allow remote connections to the database or it doesn't matter what you do, it will not connect.

If you are using cPanel, you want to go to the users account that the database is under. Scroll down to the mysql section and select Remote MySQL to add the IP Address to the allow list.
Was This Post Helpful? 0
  • +
  • -

#10 Avatarati   User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 44
  • Joined: 15-December 15

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 02:50 PM

I'm sorry, I'm using Windows Server 2008. As I understand cPanel is written for Linux servers, correct?
Was This Post Helpful? 0
  • +
  • -

#11 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3062
  • View blog
  • Posts: 11,753
  • Joined: 03-December 12

Re: Retrieving Data from MySQL Database via PHP

Posted 22 January 2016 - 03:49 PM

Correct, cPanel is for Linux.

Try this, Connecting to database remotely
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1