What's the best way to output each record on its own url?

[soccer_star]

I am very new to PHP/MySQL and am wondering if the following is possible.

I am planning a dynamic soccer site with a database table containing things like:

name, team, position, goals, assists

So typical rows might be:

Wayne Rooney, Manchester Utd, Attacker, 12, 3
Steven Gerrard, Liverpool, Midfielder, 8, 1
David James, Portsmouth, Goalkeeper, 0, 0

...and so on.

What I want is for Wayne Rooney to have his own page though, such as www.mysite.com/wayne-rooney and to have all his info on that page. Then another separate page for Steven Gerrard (www.mysite.com/steven-gerrard), another for David James etc.

I know I can create wayne-rooney.php and use:

"SELECT * FROM mytable WHERE name='Wayne Rooney'"

But that would mean creating a seperate php page for each of my soccer players. I plan on having hundreds!

So my question is, is there another more efficient way of achieving this? Ideally by using just one script to automatically generate html pages for each player. I guess it would be similar in structure to something like imdb.com where each actor has their own page.

This noob would be most grateful for any help/advice. :)

[Matthew1980]

Hi there soccer_star,

I suppose as one way to do it would be by way of a jump to style drop down selection menu. Then from that you can just query the DB from the name chosen, and as long as the DB query references are on the page (a single .php file formatted to be a template) The info can be displayed from the DB.

The tricky bit would be to populate the drop down menu from the names held in the DB, so that each time you add one, the drop down would reflect the addition.

At least that's the first option that springs to mind.

Cheers,
MRb

[rocknbil]

First thing:

So typical rows might be:
Wayne Rooney, Manchester Utd, Attacker, 12, 3
Steven Gerrard, Liverpool, Midfielder, 8, 1
David James, Portsmouth, Goalkeeper, 0, 0

If you haven't, you need to integrate a unique primary key here. While it's normally in the first column, you can do

alter table players add plr_id int(11) primary key auto_increment;

Two reasons, when you look up by record id integers are always faster, and two, making your job easier, it's simpler to select where plr_id=1234 than fiddling about with like or regex operators. Generally, I like to create two of these fields, one auto increment for internal use and one for public display - won't derail by getting into why.

What I want is for Wayne Rooney to have his own page though, such as www.mysite.com/wayne-rooney

The solution lies in .htaccess. :-)

One good way to do this is is to add

alter table players add player_url varchar(255) not null;

(Create an index on it too)

Then look into mod_rewrite. Basically, you are rewriting the request for a URL back to your script.

You will see a lot of samples where they check "if it's not a file and not a directory, rewrite to script" but this is server intensive, it has to check the entire file system. For this reason, I'd prepend the user URL with a directory, even if it doesn't exist, it will make it faster and more efficient.

www.mysite.com/players/wayne-rooney

then in .htaccess,

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule ^players\/*.* /your-script.php [R=301,L]
</IfModule>

In your script, you split up the incoming query string on /,

$path_array = explode('/',$_SERVER{'REQUEST_URI'});

the last one in the array will be the player's URL. If it's empty, generate an index page, otherwise,

select * from players where player_url='$plr_url';

And you have your page. If there's no results, output a message, "player disabled," whatever. Don't just return to the index, this is confusing ("I keep clicking Wayne Rooney and it keeps bouncing me back to the players index. . . ")

[soccer_star]

Thanks for the replies Matthew1980 and rocknbil.

I know next to nothing about mod_rewrite but I will read up on it and try your suggestion - thanks for the pointer. :)

[g1smd]

It all starts with a link on the page. That link 'defines' the URL. You'll create those links from a list in your database when your index page is called.

When the link is clicked, a URL request arrives at your server. The script will then translate that request into a request to the database and fetch some content.

If there's no content the script will send a 404 header and an error message. If there is content, the script will build the HTML page and send it to the browser.

[soccer_star]

Ok, I think I'm getting it.

So I create index.php as my home page and on this page have the links for the player pages with something like this:

<?php do { ?>
<p><a href="http://www.mysite.com/<?php echo $row_rsData['player_url']; ?>"><?php echo $row_rsData['name']; ?></a></p>
<?php } while ($row_rsData = mysql_fetch_assoc($rsData)); ?>

When one of these links is clicked, my .htaccess code handles it (assuming I use rocknbil's mod_rewrite code as follows):

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule ^players\/*.* /your-script.php [R=301,L]
</IfModule>

Now the link request is sent to your-script.php by the .htaccess file.

This is where I'm still confused - would your-script.php be my player template file, but with some extra code in it to output the HTML page? Is that what this line does:

$path_array = explode('/',$_SERVER{'REQUEST_URI'});

[rocknbil]

This is where I'm still confused - would your-script.php be my player template file, but with some extra code in it to output the HTML page?

Depends on what you mean by player template file. Returning to the original statement,

Then another separate page for Steven Gerrard (www.mysite.com/steven-gerrard

If you'e used to calling up that player's page like this,

your-script.php?player_id=1234

The task is to take steven-gerrard and convert it to 1234. So if the "player page" is your player template file, yes.

Is that what this line does:

$path_array = explode('/',$_SERVER{'REQUEST_URI'});

No, this is just a start. :-) "Teach a man to fish . . . "

Let's say this explode starts with
$_SERVER{'REQUEST_URI'} = /players/steven-gerrard

so you have two items in the array
[0] = 'players'
[1] = 'steven-gerrard'

So you'd do something like this:

$path_array = explode('/',$_SERVER{'REQUEST_URI'});
// make sure the first one is not blank. If it is,
// shift it off the array.
if (empty($path_array[0])) { $dump_it = array_shift($path_array; }

// We know index 0 is "players"
$player_url = $path_array[1];

$query = "select player_id from players where player_url='$player_url';

//Execute that query, if found,
$_GET['player_url'] = $row[0];

Note how I set the result to $_GET. This is so if, for whatever reason, there are existing requests by query string, it will still work.

One thing to be careful of, make sure if there's no result from the above, set up a default. After doing the above,

if (! isset($_GET['player_url']) or (isset($_GET['player_url']) and ! is_numeric($_GET['player_url']) and ! ($_GET['player_url'] > 0)) {
// do some default here
}

[soccer_star]

I am making slow but steady progress - I appreciate your help rocknbil :)

I'm putting the theory into practice. It's a brand new site that I'm using MAMP to test. I now have the following up and running on my local server:

index.php is the home page and has links to the players generated by this:

<?php do { ?>
 <p><a href="http://localhost:8888/players/<?php echo $row_urls['player_url']; ?>"><?php echo $row_urls['firstname']; ?> <?php echo $row_urls['lastname']; ?></a></p>
 <?php } while ($row_urls = mysql_fetch_assoc($urls)); ?>

This produces links like [localhost:8888...]

I set up my .htaccess file so that when a link is clicked it sends the request to your-script.php as follows:

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule ^players\/*.* /your-script.php [R=301,L]
</IfModule>

This works fine.

So your-script.php is my template file. It is mostly HTML using lots of echos to output certain data from my database table (called player) that differs between the players, like their stats, teams, a bio etc. At the moment the opening code is:

<?php require_once('Connections/connPHP.php'); ?>
<?php
mysql_select_db($database_connPHP, $connPHP);
$query_rsData = "SELECT * FROM player";
$rsData = mysql_query($query_rsData, $connPHP) or die(mysql_error());
$row_rsData = mysql_fetch_assoc($rsData);
$totalRows_rsData = mysql_num_rows($rsData);
?>

The final line of code after </html> is:

<?php mysql_free_result($rsData); ?>

When I click a link, it sends it to your-script.php which outputs a page using data for the player with unique primary key 1. So far so good.

Where I'm having difficulty though is implementing the code you gave me rocknbil into your-script.php so that it outputs a page corresponding to the link clicked. I'm not sure how to combine it with the existing php code in the page. I tried a number of things but always just got a blank screen.

This is the code I've added to your-script.php:

$path_array = explode('/',$_SERVER{'REQUEST_URI'});
// make sure the first one is not blank. If it is,
// shift it off the array.
if (empty($path_array[0])) { $dump_it = array_shift($path_array; }

// We know index 0 is "players"
$player_url = $path_array[1];

$query = "Select player_id FROM players WHERE player_url='$player_url'";

//Execute that query, if found,
$_GET['player_url'] = $row[0]; 
if (! isset($_GET['player_url']) or (isset($_GET['player_url']) and ! is_numeric($_GET['player_url']) and ! ($_GET['player_url'] > 0)) {
// do some default here
} 

But where exactly does it go, and what (if anything) does it replace from my existing php code?!

(Thanks for reading all of that by the way! :))

[rocknbil]

Blank screen may mean I have errors, or it may mean nothing to output. The code samples I post are rarely working code, all typed on the fly.

Start outputting echo's to see what you have (or use print_r() . . .) Remember, the idea is to convert the URL to the unique id so eventually you can do

select * from players where id=1234

to get their data.

header("content-type:text/html");

$path_array = explode('/',$_SERVER{'REQUEST_URI'});
echo "URI is " . $_SERVER{'REQUEST_URI'} . " and path_array is " . print_r($path_array) . " <br>\n";

// make sure the first one is not blank. If it is,
// shift it off the array.
if (empty($path_array[0])) { $dump_it = array_shift($path_array); } // NOTE I had an error here

// We know index 0 is "players"
$player_url = $path_array[1];

echo "player url is $player_url <br>\n";

A diversion: I put these into functions, which is why I am setting the get variable to the record id (and as mentioned, the script will still work with a regular query string.) So if your script is executing inline, top to bottom, you could **just as easily** do a select * from table here and get the row data.

$query = "Select player_id FROM players WHERE player_url='$player_url'";

echo "database query is is $query <br>\n";

Note I did have a confusing bit here, it shouldn't be "player_url" as we're storing the RECORD ID here. But you get the idea, I hope.

//Execute that query, if found,
$_GET['player_id'] = $row[0];

echo "the final record id is " . $_GET['player_id'] . " <br>\n";

Review all your echo's, see if it's what you expect.

Then you go on to select the player's records by id and output, unless you do a select * as mentioned.

[soccer_star]

I outputted echos as you suggested and the first one confused me. I used this code:

$path_array = explode('/',$_SERVER['REQUEST_URI']);
echo "URI is " . $_SERVER['REQUEST_URI'] . " and path_array is " . print_r($path_array) . " <br>\n";

// make sure the first one is not blank. If it is,
// shift it off the array.
if (empty($path_array[0])) { $dump_it = array_shift($path_array); } // NOTE I had an error here 

No matter which link I clicked I got the message:
Array ( [0] => [1] => your-script.php ) URI is /your-script.php and path_array is 1

So I got that from the link:
[localhost:8888...]

Shouldn't that have produced something like this:
Array ( [0] => [1] => wayne_rooney.htm ) URI is /wayne_rooney.htm and path_array is 1

Or am I doing something wrong? All other echos I tried contained no data, I assume because it seems to pick up the current url (your-script.php) instead of the requested url.

Any ideas will be gratefully received?

[rocknbil]

Good catch here, $_SERVER['REQUEST_URI'], $_SERVER{'REQUEST_URI'} should also work but this particular affliction comes from coding both Perl and PHP daily, it's easy to mix them up . . .

Shouldn't that have produced something like this:

Yes, it should have . . . well actually, it should have players in 0, not null, like

Array ( [0] =>players [1] => wayne_rooney.htm ) URI is /wayne_rooney.htm and path_array is 1

and don't know why It's not. Maybe it's because it's executing on localhost? I've no idea why that would be the case.

The only other thing I can think of (shouldn't matter) is you're executing the script from the players directory. Try uploading to a live server? I use this all the time, it's never failed so far . . .

[g1smd]

While you're thinking about all of this, how are you going to cope if there are two different players with the same name?

[soccer_star]

I will try as you suggested rocknbil and if all else fails will upload it to a live server.

While you're thinking about all of this, how are you going to cope if there are two different players with the same name?

A good point that might come into play once my database gets into the hundreds of players. One of the duplicates will have to have their middle names included maybe!

[rocknbil]

Of course, when adding new players you need to look up if it exists first. Even middle names can conflict. What you do if found really depends on what you have patience for. :-)

A down and dirty, though not real user friendly, is if it exists, loop through 1 to 100 times, appending the player name with the number and re-look it up, making sure it's not taken.

while (($exists==1) and $tries <= 100)) {
// keep looking
$tries++;
}

You want to make sure to limit it to avoid an infinite loop.

Notify the user, let them make some sort of decision. Maybe they can come up with a unique one . . . filtered, of course, against a list of offensive words . . .

[soccer_star]

Well, I tried it on a live server but I'm still getting the current URL instead of the requested URL using this code:

<?php
$path_array = explode('/',$_SERVER['REQUEST_URI']);
echo "URI is " . $_SERVER['REQUEST_URI'] . " and path_array is " . print_r($path_array) . " <br>\n";
?>

The htaccess redirect is working, sending requests from index.php to your-script.php, but I still get:
Array ( [0] => [1] => your-script.php ) URI is /your-script.php and path_array is 1

... when I should be getting:
Array ( [0] => players [1] => wayne_rooney.htm ) URI is /wayne_rooney.htm and path_array is 1

All the files are in my root dir called public_html (.htaccess, your-script.php and index.php) if that's at all relevant.

I've done loads of reading on request URI but can't seem to find the answer. Anybody else encountered this problem when using a mod_rewrite? I'm pretty close to giving up and creating each player page by hand!

[rocknbil]

I just checked a live site again. hee are the only things I'm doing differently:

list($category_id,$product_id) = get_ids_from_uri($_SERVER{'REQUEST_URI'});

then do all of the above in a function, returning two values (which may or may not be null.)

get_ids_from_uri($request) {
// etc.
return Array($c,$p);
}

The only thing I can think of is your server is somehow doing a redirect, not a rewrite. You can try

$blah = $_SERVER{'REQUEST_URI'};

then explode $blah, though that should make exactly zero difference.