PHP Using PHP to connect to your database and get information

[Rewexx]

Hi folks,

It is my pleasure to post this tutorial, fully made by me.
This is not a hard code at all but I can tell you that it's extremely needed to have knowledge of when creating PHP scripts and you want them stored in your database.

I recommend you to read Paxors PHP tutorials before going to this one, so you really get a point what it's all about.

So let's say we want to make a staff list but we don't want to waste time and write them manually in our PHP file but in a MySQL database which is listening to a special group ID, just like the one Gesior is using for his gamemaster list.

First we start off by making ourselves an config. Like this:

<?php
$dbhost = "localhost"; //Your database host, running on localhost
$dbuser = "root"; //Your database user that you login with
$dbpass = "password"; //The password you use to login with
$db = "database"; //The database you've created in your MySQL wizard.
?>

Now let me explain this code for you why it can save you some time etc.
We always start our PHP scripts with a <?php. Then after, we do some variables or so called strings that we'll use later in our code. Then we will not have to think of filling in the database connection in our script because we have already done that in our config, this will surely save you loads of time and it is not difficult at all.

Now we will continue with our PHP script. Firstly as you know, we'll need to connect to the database before it can eject the information we need. So we connect to the database like this:

mysql_connect($dbhost, $dbuser, $dbpass)or die(mysql_error());
echo "Successfully connected to MySQL database. Everything should work as it should"; //If the MySQL connection config is typed correctly, it will now connect tot he database. If not, it will show a Mysql error.
mysql_select_db($db)or die(mysql_error());
echo "MySQL Database has been selected"; //same as above, if works it'll show you "MySQL Database has been selected

// Now let's create the specific table we're going to use
mysql_query("CREATE TABLE table(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
 name VARCHAR(30), 
 age INT)")
 or die(mysql_error());  

?>

Okay good, so now we have connected to the database and also connected to the database we're going to eject information from. So let's continue with the code that ejects the information we need, like this:

$query = "SELECT * FROM * TABLE"; //You will need to change the table to the table you're going to use
$result = mysql_query($query)or die(mysql_error()); //The result of the attempt to select the specific table, so we use the variable $query
$row = mysql_fetch_array($result)or die(mysql_error()); //This time we use the variable $result from the above example. 

//Now we'll see if it works. 
echo $row['Name']. " - ". $row['position'];
?>

These columns will of course be needed to created.

Any questions, feel free to post in this thread and I'll try to answer them. I recommend that you should not use this script but I'm working on a bigger one that'll be released soon.


EDIT: Sorry, I missed this part. Here is the whole code:

<?php
$dbhost = "localhost"; //Your database host, running on localhost
$dbuser = "root"; //Your database user that you login with
$dbpass = "password"; //The password you use to login with
$db = "database"; //The database you've created in your MySQL wizard.

mysql_connect($dbhost, $dbuser, $dbpass)or die(mysql_error());
echo "Successfully connected to MySQL database. Everything should work as it should"; //If the MySQL connection config is typed correctly, it will now connect tot he database. If not, it will show a Mysql error.
mysql_select_db($db)or die(mysql_error());
echo "MySQL Database has been selected"; //same as above, if works it'll show you "MySQL Database has been selected

// Now let's create the specific table we're going to use
mysql_query("CREATE TABLE table(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
 name VARCHAR(30), 
 age INT)")
 or die(mysql_error());  

$query = "SELECT * FROM * TABLE"; //You will need to change the table to the table you're going to use
$result = mysql_query($query)or die(mysql_error()); //The result of the attempt to select the specific table, so we use the variable $query
$row = mysql_fetch_array($result)or die(mysql_error()); //This time we use the variable $result from the above example. 

//Now we'll see if it works. 
echo $row['Name']. " - ". $row['position'];
?>

Regards
Nikkster

 

[Chris]

You should switch 'connect_mysql' to 'mysql_connect', although I would personally advice you to get the hang of either MySQLi or PDO (not you in particular, but everyone).

 

[Rewexx]

You should switch 'connect_mysql' against 'mysql_connect', although I would personally advice you to get the hang of either MySQLi or PDO (not you in particular, but everyone).

Oh, thanks for pointing that out! Guess I missed that.

 

[Chris]

You're welcome. Nice job on the tutorial by the way.

 

[Zoriath]

Example on MySQLi (with OOP):

class Connect extends MySQLi {
     private $mysqlHost = 'localhost';
     private $mysqlUser = 'root';
     private $mysqlPass = 'YOUR_PASSWORD';
     private $mysqlDB   = 'YOUR_DATABASE_NAME';
     
     public function __construct() {
          parent::__construct($this->mysqlHost, $this->mysqlUser, $this->mysqlPass, $this->mysqlDB);

          $this->query("SELECT * FROM `accounts`;");
     }
}

OR a bit less user-friendly (because people might not know what to place where in the array, but you can explain that in a comment), but shorter:

class Connect extends MySQLi {
     private $connectionData = array('localhost', 'root', 'YOUR_PASSWORD', 'YOUR_DATABASE_NAME');
     
     public function __construct() {
          parent::__construct($this->connectionData[0], $this->connectionData[1], $this->connectionData[2], $this->connectionData[3]);

          $this->query("SELECT * FROM `accounts`;");
     }
}

Might have made a little typo or something..

 

[Chris]

$this->connectionDate[1] should be $this->connectionData[1]

 

[Zoriath]

Ah lol, thanks, already warned you for typo's ^_^