Generate PHP profile page with PHP data

[matthijs110]

Hello all,

 

I'm wondering, how can I generate a PHP page when a new row has been added to the SQL database. For example:

 

If I add a row in my database with this info:

Username: matthijs110

 

PHP should look to the row Username, and when there one be added, it should generate a URL like this:

http://domain.com/profile/matthijs110

 

---

I also got a other question about this what might be more complex, but first I want to know if this is possible

 

 

[PravinS]

do you know URL rewriting?

 

check : http://httpd.apache.org/docs/2.0/misc/rewriteguide.html

[matthijs110]

do you know URL rewriting?

 

check : http://httpd.apache.org/docs/2.0/misc/rewriteguide.html

Yes I know it, I actually never work with it. But how would I make a URL with it, IF there is a new row in the database and gets the info from a column?

[Jacques1]

Sorry, but what you're saying doesn't make much sense. Maybe it's just the wording, maybe there's a fundamental misunderstanding regarding PHP.

 

You do not “make URLs” (whatever that means). A visitor requests a certain URL, and then you act upon it.

 

For example, a visitor might request https://yourdomain.com/profile/foobar. The first step is that your webserver internally rewrites the URL to https://yourdomain.com/profile.php?name=foobar and calls the profile.php script. Then this script looks up the name in the database. If the user exists, you render the profile from the data. If the user does not exist, you respond with a 404 message saying something like “Sorry, this user does not exist”.

 

Of course you can also provide a list of all current users or something like that. But you do not literally “make links”.

[matthijs110]

Sorry, but what you're saying doesn't make much sense. Maybe it's just the wording, maybe there's a fundamental misunderstanding regarding PHP.

 

You do not “make URLs” (whatever that means). A visitor requests a certain URL, and then you act upon it.

 

For example, a visitor might request https://yourdomain.com/profile/foobar. The first step is that your webserver internally rewrites the URL to https://yourdomain.com/profile.php?name=foobar and calls the profile.php script. Then this script looks up the name in the database. If the user exists, you render the profile from the data. If the user does not exist, you respond with a 404 message saying something like “Sorry, this user does not exist”.

 

Of course you can also provide a list of all current users or something like that. But you do not literally “make links”.

Ah right, you make exactly clear what I mean So my question follows: How would I do that?

[Jacques1]

I already told you: You search the users table in your database for the name from the URL. If the name exists, you fetch the data and render your HTML from it. If the name does not exist, you render an error page and emit a 404 response.

 

If you want a more concrete answer, show us the current code.

[matthijs110]

I already told you: You search the users table in your database for the name from the URL. If the name exists, you fetch the data and render your HTML from it. If the name does not exist, you render an error page and emit a 404 response.

 

If you want a more concrete answer, show us the current code.

The only code I have is a login/register page that works fine after you registered. I don't know how to do that what you said I have to do. Thats why I'm asking it here.

But here is my code with the working login/register code:

https://github.com/matthijs110/Login-Project

[Jacques1]

You do not know what?

 

You obviously do know how to access GET/POST data, query the database and render an HTML page. And that's all you need for this task. So just start and see how far you get.

[matthijs110]

You do not know what?

 

You obviously do know how to access GET/POST data, query the database and render an HTML page. And that's all you need for this task. So just start and see how far you get.

I do know how to get info from the Database. How do you mean: for the name from the URL?

Edited May 8, 2014 by matthijs110

[Jacques1]

The URL has a parameter which contains the username, right? For example:

https://yoursite.com/profile.php?name=matthijs110

There's a parameter called “name”, and the parameter has the value “matthijs110”. This is the username you're looking for.

 

So you take the “name” parameter and search the users table for this exact username.

[matthijs110]

The URL has a parameter which contains the username, right? For example:

https://yoursite.com/profile.php?name=matthijs110

There's a parameter called “name”, and the parameter has the value “matthijs110”. This is the username you're looking for.

 

So you take the “name” parameter and search the users table for this exact username.

Okay, I'm stuck now. I just don't know how to get that parameter after the member.php :/ 

[adam_bray]

You need to read up on using $_GET - my basic post on it.

 

In the example above you'd do:

<?php

$member = $_GET['name'];

// Sanitize the string

// Query the database

// Display the results 

?>

The comments are the steps following. When using $_GET with a MySQL query you need to check for malicious characters to prevent injections.

[matthijs110]

You need to read up on using $_GET - my basic post on it.

 

In the example above you'd do:

<?php

$member = $_GET['name'];

// Sanitize the string

// Query the database

// Display the results 

?>

The comments are the steps following. When using $_GET with a MySQL query you need to check for malicious characters to prevent injections.

I do this on the index.php file or member.php ?

 

btw, I'm going to make this without login/register possibilities. I got a other way to push info TO the MySQL database.

Edited May 8, 2014 by matthijs110

[matthijs110]

You need to read up on using $_GET - my basic post on it.

 

In the example above you'd do:

<?php

$member = $_GET['name'];

// Sanitize the string

// Query the database

// Display the results 

?>

The comments are the steps following. When using $_GET with a MySQL query you need to check for malicious characters to prevent injections.

Okay, I tried it, and it kinda works

 

This is my code so far:

https://github.com/matthijs110/Minecraft-User-Info/blob/master/member.php

 

But when I go to:

http://localhost/Minecraft-User-Info/member.php?MCUser=about

 

It returns:

 

Notice: Undefined index: Username in /Applications/XAMPP/xamppfiles/htdocs/Minecraft-User-Info/member.php on line 2

This is the home page

 

How can I fix this? And how can I check if the parameter that is given in the URL exists in a database row?

[adam_bray]

You don't need the switch in your code, that's for something else.

 

You're also missing the point of $_GET.. you're using $_GET['username'], but then you're browsing to ?MCUser=about. To do that you need to use $_GET['MCUser'].

 

Try this -

<?php
    $MCUser = ( isset( $_GET['user'] ) ) ? $_GET['user'] : false; // Check if the user variable has been set
	
	if( $MCUser )
	{
		$MCUser = mysql_real_escape_string( $MCUser ); // Basic protection against attacks
		
		$username = "root"; 
		$password = ""; 
		$host = "localhost"; 
		$dbname = "User_Management"; 
		
		$db_handle = mysql_connect($host, $username, $password) or die(mysql_error());
		$db_found = mysql_select_db($dbname, $db_handle) or die(mysql_error());
	
	
		$data = mysql_query("SELECT * FROM Player_Data WHERE Username = {$MCUser} LIMIT 1;") or die(mysql_error());
		
		if( mysql_num_rows( $data ) > 0 )
		{
			$user_info = mysql_fetch_array( $data );
			
			print_r( $user_info );
		}
		else
		{
			die('User not found!');	
		}
	}
	else
	{
		die( 'Please enter a user to search' ); // or show a list of users	
	}
?>

You need to browse to member.php?user=username.

[matthijs110]

You don't need the switch in your code, that's for something else.

 

You're also missing the point of $_GET.. you're using $_GET['username'], but then you're browsing to ?MCUser=about. To do that you need to use $_GET['MCUser'].

 

Try this -

<?php
    $MCUser = ( isset( $_GET['user'] ) ) ? $_GET['user'] : false; // Check if the user variable has been set
	
	if( $MCUser )
	{
		$MCUser = mysql_real_escape_string( $MCUser ); // Basic protection against attacks
		
		$username = "root"; 
		$password = ""; 
		$host = "localhost"; 
		$dbname = "User_Management"; 
		
		$db_handle = mysql_connect($host, $username, $password) or die(mysql_error());
		$db_found = mysql_select_db($dbname, $db_handle) or die(mysql_error());
	
	
		$data = mysql_query("SELECT * FROM Player_Data WHERE Username = {$MCUser} LIMIT 1;") or die(mysql_error());
		
		if( mysql_num_rows( $data ) > 0 )
		{
			$user_info = mysql_fetch_array( $data );
			
			print_r( $user_info );
		}
		else
		{
			die('User not found!');	
		}
	}
	else
	{
		die( 'Please enter a user to search' ); // or show a list of users	
	}
?>

You need to browse to member.php?user=username.

When I do this, it results something, I used member.php?user=username. like you said, but then it returns:

Array ( [0] => matthijs110 [username] => matthijs110 [1] => 72bc0e6c93da4bacaa9b680936c4bd82 [uUID] => 72bc0e6c93da4bacaa9b680936c4bd82 [2] => IPHidden [iP] => IPHidden [3] => Owner [Rank] => Owner [4] => 12 [Tokens] => 12 )

 

I changed my IP address to IPHidden. When I go to member.php?user=matthijs110, It returns:

Unknown column 'matthijs110' in 'where clause'

[Jacques1]

No offense, adam_bray, but if you hand out complete code to other people, at least make sure it's good.

 

This definitely isn't. The mysql_* functions are obsolete since more than a decade and will be removed in one of the next PHP releases. Nowadays, we use PDO. This or die(mysql_error()) stuff makes absolutely no sense and is actually a security vulnerability. Why would you want your users to see the exact MySQL error message with all information about your internal database structure? The whole code structure is very cumbersome and doesn't make a lot of sense.

 

Besides that, how exactly does it help Matthijs to give him yet another piece of code to copy and paste? As far as I can tell, his GitHub account is already full of those scripts, and yet he struggles with the basics of PHP. Wouldn't it be much better to encourage people to actually learn the language and write their own code?

 

I'm sorry for being harsh, but I think this copypasta mania is the cancer that's killing PHP. It doesn't help anybody to just blindly duplicate bad code. You learn absolutely nothing from it.

Edited May 8, 2014 by Jacques1

[matthijs110]

No offense, adam_bray, but if you hand out complete code to other people, at least make sure it's good.

 

This definitely isn't. The mysql_* functions are obsolete since more than a decade and will be removed in one of the next PHP releases. Nowadays, we use PDO. This or die(mysql_error()) stuff makes absolutely no sense and is actually a security vulnerability. Why would you want your users to see the exact MySQL error message with all information about your internal database structure? The whole code structure is very cumbersome and doesn't make a lot of sense.

 

Besides that, how exactly does it help Matthijs to give him yet another piece of code to copy and paste? As far as I can tell, his Git account is already full of those scripts, and yet he struggles with the basics of PHP. Wouldn't it be much better to encourage people to actually learn the language and write your own code?

 

I'm sorry for being harsh, but I think this copypasta mania is the cancer that's killing PHP. It doesn't help anybody to just blindly duplicate bad code. You learn absolutely nothing from it.

I use the or die(mysql_error()) When developing the webpage. If its all done, I remove it because I don't expect issues. Outside of that, I actually appreciate the copy paste. Don't worry about learning it, I understand the code when I see it pretty quickly. Mainly because I code Java too. 

 

But I'm not saying you aren't right. 

Edited May 8, 2014 by matthijs110

[Jacques1]

... and I forgot: The code is wide open to SQL injections, because the name isn't quoted. Escaping without quoting does absolutely nothing.

 

 

 

I use the or die(mysql_error()) When developing the webpage. If its all done, I remove it because I don't expect issues.

 

That makes no sense to me.

 

You expect no errors after you've written the code? That's some statement. In reality, however, applications do fail for all kinds of reasons: bugs, server issues etc. It's better to acknowledge this and log those errors.

 

And why would you want to go through your entire code to remove this die() stuff everytime you put the application online? Wouldn't it make much more sense to skip this stupid routine and simply write sensible error handling from the beginning? 

 

Modern database interfaces (PDO and MySQLi) make this easy, because they already do it for you. If you absolutely must stick to the old MySQL extensions, use proper PHP errors:

$user_query = mysql_query('this will fail');
if ($user_query === false)
{
	trigger_error(mysql_error(), E_USER_ERROR);
}

Now the error is treated correctly according to the environment: During development, you'll want to see the message on the screen. In a live environment, you do not want to see it on the screen but write it to the error log.

[matthijs110]

... and I forgot: The code is wide open to SQL injections, because the name isn't quoted. Escaping without quoting does absolutely nothing.

 

 

 

 

That makes no sense to me.

 

You expect no errors after you've written the code? That's some statement. In reality, however, applications do fail for all kinds of reasons: bugs, server issues etc. It's better to acknowledge this and log those errors.

 

And why would you want to go through your entire code to remove this die() stuff everytime you put the application online? Wouldn't it make much more sense to skip this stupid routine and simply write sensible error handling from the beginning? 

 

Modern database interfaces (PDO and MySQLi) make this easy, because they already do it for you. If you absolutely must stick to the old MySQL extensions, use proper PHP errors:

$user_query = mysql_query('this will fail');
if ($user_query === false)
{
	trigger_error(mysql_error(), E_USER_ERROR);
}

Now the error is treated correctly according to the environment: During development, you'll want to see the message on the screen. In a live environment, you do not want to see it on the screen but write it to the error log.

So like this?

$data = mysql_query("SELECT * FROM Player_Data WHERE Username = {$MCUser} LIMIT 1;");
        if ($data === false) {
            trigger_error(mysql_error(), E_USER_ERROR);   
        }

Its giving a other error indeed. When I browse to:

member.php?user=matthijs110, it gives me this error:

 

Fatal error: Unknown column 'matthijs110' in 'where clause' in /Applications/XAMPP/xamppfiles/htdocs/Minecraft-User-Info/member.php on line 19

Line 19 = 

trigger_error(mysql_error(), E_USER_ERROR);   

And this when going to member.php?user=username:

Array ( [0] => matthijs110 [username] => matthijs110 [1] => 72bc0e6c93da4bacaa9b680936c4bd82 [uUID] => 72bc0e6c93da4bacaa9b680936c4bd82 [2] => IPHidden [iP] => IPHidden [3] => Owner [Rank] => Owner [4] => 12 [Tokens] => 12 )

 

I changed my IP address to IPHidden. 

 

How would I fix this? What comes after  =, should be searched in the SQL database column: Username. If it has been found, it should return the other data ( I know how to do this). If it isn't found, then go back to the home page. 

Edited May 8, 2014 by matthijs110

[ginerjm]

You need quotes around your username value, just like one needs quotes around any string argument in a query.  Otherwise MySQL thinks you have provided a column name.

[Jacques1]

I strongly recommend that you forget about the code above and implement the profile yourself using up-to-date PHP. 

 

I mean, it's just silly: You came here with one problem (writing a profile script), now you got two problems (writing a profile script and fixing bugs of other people).

 

You should start by learning how to use PDO. This is basic knowledge for every modern PHP application

Edited May 8, 2014 by Jacques1

[matthijs110]

I strongly recommend that you forget about the code above and implement the profile yourself using up-to-date PHP. 

 

I mean, it's just silly: You came here with one problem (writing a profile script), now you got two problems (writing a profile script and fixing bugs of other people).

 

You should start by learning how to use PDO. This is basic knowledge for every modern PHP application

I just tried PDO with the link you gave. when I use it, the variables I made can't be reached anymore because some are in functions. 

 

But I will look into this when I got the code finished and try to make use of it, Thanks for the info about it, never heard of it.

You need quotes around your username value, just like one needs quotes around any string argument in a query.  Otherwise MySQL thinks you have provided a column name.

I got it working now  member.php?user=matthijs110 is printing the right info now. When a user does not exists, it returns User not found! as it should for now

 

Now my last question that follows (and no its not new problem, I asked it in my first post ) How would I convert:

 

http://localhost/Minecraft-User-Info/member.php?user=matthijs110

To

http://localhost/Minecraft-User-Info/user/matthijs110

[adam_bray]

No offense, adam_bray, but if you hand out complete code to other people, at least make sure it's good.

 

Sorry, my bad!

 

OP: Here's some slightly better code (untested). I know you might not understand most of what's in it, but Jacques1 will kindly explain it for you if you get confused.

<?php
	/*
	*
	*	DB CONNECTION INFO
	*
	*/
	$_mysql_info = array(
		'user'			=>			'root',
		'password'		=>			'',
		'host'			=>			'localhost',
		'db'			=>			'User_Management',
	);
	
	/*
	*
	*	DEFINITIONS
	*
	*/
	if( !defined( 'MAX_USERNAME_LENGTH' ) )
	{
		define( 'MAX_USERNAME_LENGTH', 15 );	
	}
	
	/*
	*
	*	member.php
	*
	*/
	$mcuser = ( isset( $_GET['user'] ) ) ? $_GET['user'] : false;
	
	if( $mcuser )
	{
		$mcuser = preg_replace("/[^A-Za-z0-9 ]/", '', $mcuser);
		$mcuser = ( strlen( $mcuser ) > MAX_USERNAME_LENGTH ) ?  substr($mcuser, 0, MAX_USERNAME_LENGTH ) : $mcuser;
		
		try {  
			$conn = new PDO('mysql:host='.$_mysql_info['host'].';dbname='.$_mysql_info['db'], $_mysql_info['username'], $_mysql_info['password']);  
			$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			
			$qry = $conn->prepare('
				SELECT Player_Data.Username
				, Player_Data.Rank
				, Player_Data.Tokens 
				FROM Player_Data 
				WHERE Username = :username 
				LIMIT 1;');
				
    		$qry->execute( array(
				'username' 		=>		$mcuser,
			));
			
			$result = $qry->fetchAll();
			
			if( count($result) > 0 )
			{
				foreach( $result as $row )
				{
					echo '<h1>Viewing '.$mcuser.' Profile</h1>';
					
					foreach( $row as $key => $val )
					{
						echo '<strong>'.$key.'</strong>:' . $val;	
					}
				}
			}
			else
			{
				echo 'No rows returned.';
			}
		}  
		catch( PDOException $e )
		{  
			echo 'DB ERROR: ' . $e->getMessage();
		}
	}
	else
	{
		try {  
			$conn = new PDO('mysql:host='.$_mysql_info['host'].';dbname='.$_mysql_info['db'], $_mysql_info['user'], $_mysql_info['password']);  
			$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			
			$qry = $conn->prepare('
				SELECT Player_Data.Username
				, Player_Data.Rank
				, Player_Data.Tokens 
				FROM Player_Data;');
				
    		$qry->execute( array(
				'username' 		=>		$mcuser,
			));
			
			$result = $qry->fetchAll();
			
			if( count($result) > 0 )
			{
				echo '<h1>Select Profile:</h1>';
				
				foreach( $result as $row )
				{
					echo '<a href="member.php?user=' . $row['username'] . '">'.$row['username'].'</a>';
				}
			}
			else
			{
				echo 'No rows returned.';
			}
		}  
		catch( PDOException $e )
		{  
			echo 'DB ERROR: ' . $e->getMessage();
		}
	}
?>

RE your second question, look into mod_rewrite.

[matthijs110]

Sorry, my bad!

 

OP: Here's some slightly better code (untested). I know you might not understand most of what's in it, but Jacques1 will kindly explain it for you if you get confused.

<?php
	/*
	*
	*	DB CONNECTION INFO
	*
	*/
	$_mysql_info = array(
		'user'			=>			'root',
		'password'		=>			'',
		'host'			=>			'localhost',
		'db'			=>			'User_Management',
	);
	
	/*
	*
	*	DEFINITIONS
	*
	*/
	if( !defined( 'MAX_USERNAME_LENGTH' ) )
	{
		define( 'MAX_USERNAME_LENGTH', 15 );	
	}
	
	/*
	*
	*	member.php
	*
	*/
	$mcuser = ( isset( $_GET['user'] ) ) ? $_GET['user'] : false;
	
	if( $mcuser )
	{
		$mcuser = preg_replace("/[^A-Za-z0-9 ]/", '', $mcuser);
		$mcuser = ( strlen( $mcuser ) > MAX_USERNAME_LENGTH ) ?  substr($mcuser, 0, MAX_USERNAME_LENGTH ) : $mcuser;
		
		try {  
			$conn = new PDO('mysql:host='.$_mysql_info['host'].';dbname='.$_mysql_info['db'], $_mysql_info['username'], $_mysql_info['password']);  
			$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			
			$qry = $conn->prepare('
				SELECT Player_Data.Username
				, Player_Data.Rank
				, Player_Data.Tokens 
				FROM Player_Data 
				WHERE Username = :username 
				LIMIT 1;');
				
    		$qry->execute( array(
				'username' 		=>		$mcuser,
			));
			
			$result = $qry->fetchAll();
			
			if( count($result) > 0 )
			{
				foreach( $result as $row )
				{
					echo '<h1>Viewing '.$mcuser.' Profile</h1>';
					
					foreach( $row as $key => $val )
					{
						echo '<strong>'.$key.'</strong>:' . $val;	
					}
				}
			}
			else
			{
				echo 'No rows returned.';
			}
		}  
		catch( PDOException $e )
		{  
			echo 'DB ERROR: ' . $e->getMessage();
		}
	}
	else
	{
		try {  
			$conn = new PDO('mysql:host='.$_mysql_info['host'].';dbname='.$_mysql_info['db'], $_mysql_info['user'], $_mysql_info['password']);  
			$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			
			$qry = $conn->prepare('
				SELECT Player_Data.Username
				, Player_Data.Rank
				, Player_Data.Tokens 
				FROM Player_Data;');
				
    		$qry->execute( array(
				'username' 		=>		$mcuser,
			));
			
			$result = $qry->fetchAll();
			
			if( count($result) > 0 )
			{
				echo '<h1>Select Profile:</h1>';
				
				foreach( $result as $row )
				{
					echo '<a href="member.php?user=' . $row['username'] . '">'.$row['username'].'</a>';
				}
			}
			else
			{
				echo 'No rows returned.';
			}
		}  
		catch( PDOException $e )
		{  
			echo 'DB ERROR: ' . $e->getMessage();
		}
	}
?>

RE your second question, look into mod_rewrite.

I tried multiple staff to convert the arguments to friendly URL's. It doesn't seems to work. Mod_rewrite is enabled in Apache

 

 

<IfModule mod_rewrite.c>

RewriteEngine On
RewriteRule /(.*)/$ player/index.php?user=$1

</ifModule>

I moved some stuff, thats why player and index.php is there. You can see it here:

https://github.com/matthijs110/Minecraft-User-Info