Data From Forum To External DB

[russellr]

External Database & Interaction JS to PHP

What we will cover:
- Basic MySQL
- GET Variables
- JS RegExp

In this example (proboards) we will get the username, displayname and users posts from the forum and put these into your MySQL database located on a different server.

Ok the SQL table consists of: username, displayname and posts.

---

Now lets first off grab the username and displayname and talk to the database.

<script type="text/javascript">
var k = document.createElement('script');
k.src = 'http://mydomain.com/grabuser.php?user='+pb_username+'&display='+pb_displayname+'';
document.getElementsByTagName('head')[0].appendChild(k);
</script>

Now as proboards sets these variables for us we can just add them to the url which calls the first script we need. grab user.php will look something like this.

<?php
$user=$_GET['user'];
$display=$_GET['display'];

$server = 'xxxxxx';
$username = 'xxxxxxxx';
$password = 'xxxxxxxxx';
$database = 'xxxxxxxxxx';

$con = mysql_connect($server, $username, $password);
if(!$con)
{
exit('Error: could not establish database connection');
}
if(!mysql_select_db($database))
{
exit('Error: could not select the database');
}

if(mysql_num_rows(mysql_query("SELECT `username` FROM `mytable` WHERE `username` = '$user'"))){

(mysql_query("UPDATE `mytable` SET `displayname` = '$display' WHERE `username` = '$user'"));

}else{
mysql_query("INSERT INTO `mytable` (username, displayname)
VALUES ('$user', '$display')");

}

?>

Now as you can see we there is some code there some of you may not recognise, lets start at the top.

$user=$_GET['user'];
$display=$_GET['display'];

This part grabs the data from the url and turns in into a php variable so we can use it later on.

$server = 'xxxxxx';
$username = 'xxxxxxxx';
$password= 'xxxxxxxxx';
$database= 'xxxxxxxxxx';

$con = mysql_connect($server, $username, $password);
if(!$con)
{
exit('Error: could not establish database connection');
}
if(!mysql_select_db($database))
{
exit('Error: could not select the database');
}

This may look complex but its just a simple piece of code, setting variables first of your mysql connect data then using this to connect to the database a very important step.

Now we move onto the main part.

if(mysql_num_rows(mysql_query("SELECT `username` FROM `mytable` WHERE `username` = '$user'"))){

(mysql_query("UPDATE `mytable` SET `displayname` = '$display' WHERE `username` = '$user'"));

}else{
mysql_query("INSERT INTO `mytable` (username, displayname)
VALUES ('$user', '$display')");

}

The first line check to see if this username already exists as an if statement so the second line is what we want to do if the user exists and we just make it update the displayname and some people like to change this from time to time. now the part we see here

}else{

is where we realise the user is not already in the database so we:

mysql_query("INSERT INTO `mytable` (username, displayname)
VALUES ('$user', '$display')");

Insert the new user to the database as they have not been added before.

Part Two: Updating post count coming soon.