Creating links in dynamic tables that allow drill down of data

[PHP_Idiot]

Hi Freaks

 

I'm stuck bigtime on this, and am not sure where to begin!

 

I have a series of tables in my site that show leaderboards for various venues. All the tables are dynamically created using a mysql d'base, and are all functioning nicely.

 

However, the next step in the development it to make the players names in the tables into links so they can drill into themselves (and others) to see where where they have played, where they finished, and so on..

 

I currently have two main tables, one is a finalist table, that updates according to the changing score, and shows the top 40 players across the whole league (http://www.gbpokerclub.co.uk/playerslounge_s3.php) and the other uses a selection box to chose the specific venue to show the leaderboard for just that venue (http://www.gbpokerclub.co.uk/playersloungeLB3.php).

 

In both of these tables I'd like the players name to become links, from each it would take them to a new table listing all the games and venues they had played in and their finishing position, I'd like to also make this linked so that they could click the date to see who else played and where they finished, or click the venue name and see a list of dates the venue had played on (also linked into each game for each date)...and so on.

 

I can write the mysql queries to give me the data I need and present them in a bog standard html table, but how do I use the links to not only move to another page but also to ammend the query (eg if I clik Johns name I want Johns data to be retirieved)

 

Any help would be massively appreciated (but please explain in simple lingo, I'm still very much a eginner at this stuff!!)

 

Cheers

 

PHP_Idiot

[minidak03]

Its hard to really drill down without seeing your source code but I can give you a little something to go by if you can handle doing the queries and a little bit of HTML/PHP yourself.

 

Basically if I understand you correctly your issue isn't so much turning there names into links but understanding more or less how to retrieve there data correctly when that link is clicked.

 

If this is correct here is a general workflow you can use to achieve your goal.

 

1) When you dynamically generate the tables with player names, ect... also pull back the unique id stored in the database (this is usually called 'id' but it can be anything), this is also usually the primary key and it's usually set to auto-increment (making it unique to each user).

 

2) When your displaying this information into the html table, create the link around there name (see example below)

 

<a href="">This is there name</a>

 

Now the location of the link will point to the page where you want to display the users data so something like this

 

<a href="http://blabla.com/show_user_data.php">This is there name</a>

 

Now the last step is to add a query to the end of that url and this query will store the unique database id of this user so something like this

 

<a href="http://blabla.com/show_user_data.php?user_id=543">This is there name</a>

 

Then dynamically insert the unique database id where the number 543 is, from there when the user clicks on the link you can access that user_id paramater using the PHP $_GET or $_REQUEST variables.

 

So from here if a user clicks a link they are brought to the show_user_data.php page and on this page you would have something like the below code

 

<?php 
    $user_id = $_GET['user_id']; 

    // now after this use the value in $user_id var to get this users data from the database
    $query = "SELECT * FROM table WHERE id = '$user_id';

    // and now perform your query and any other php work to display that information
?>

 

I hope this helps you out a bit.

[PHP_Idiot]

Hi Minidak

 

That's really helpful thank you, but I have a couple of questions if that's ok!

 

The first point make sense completely, my tables all have a primary key although not all are autoincrement, but thats because we have a strange membership number layout!

 

The bit I'm a little stuck on is the idea of wrapping a link tag around the name, in the example (which is excellent in explaining what and how the link is constructed) it seems that each link needs manually adding.

 

Would I be right in thinking that I could add this in a loop for each record retrieved from table?

 

I get the bit about putting it into a variable and using Get to pull forward the user_id so I'm going to have a try tomorrow on putting this thing together! (bit late now so will save it for tomorrow)

 

thanks a lot for your really helpful explanation, I'll repost if I get it working

 

 

[minidak03]

The bit I'm a little stuck on is the idea of wrapping a link tag around the name, in the example (which is excellent in explaining what and how the link is constructed) it seems that each link needs manually adding.

 

Would I be right in thinking that I could add this in a loop for each record retrieved from table?

 

That is exactly right you would get the names back from the database or other data source, put them into an array or object, create a loop for each item in the array or object and then this will enable you to generate the urls dynamically (making them unique to each user).

[PHP_Idiot]

Excellent Thanks again for the advice, I'll be trying to put that into action this evening once I finish the day job and get my queries running properly

 

Thanks again Minidak

 

PHP_Idiot

[PHP_Idiot]

Hi Minidak

 

I've been playing around with these links and I've managed to get the links in place and working dynamically

 

but I'm stuggling to get the destination page to GET the information and use it!

 

At the moment the page I link to has a table set to populate based on the selection made from a listbox, but I want to remove the list box and use the information in the link to populate the table. But I just can't get it to work, can you offer any more advice please?

 

Here's my page code at the moment:

// Make a MySQL Connection
mysql_connect("localhost", "XXXX", "XXXX") or die(mysql_error());
mysql_select_db("XXXXX") or die(mysql_error());
$query="SELECT VenueName FROM Venue ORDER BY VenueName";

  
$result = mysql_query ($query); 
echo '<form action="" method="post">';
echo "<select name='VenueName'>";
// printing the list box select command
while($nt=mysql_fetch_array($result))
        {//Array or records stored in $nt
                echo "<option ";

                 if($_POST['VenueName'] == $nt['VenueName']) echo "selected=\"selected\"";

               echo " value=\"$nt[VenueName]\">$nt[VenueName]</option>";
                /* Option values are added by looping through the array */
        }
echo "</select>";// Closing of list box



?>
                  <input type="submit" value="Go" />
                  <a href="../print/VenuesLeaguePrintS3.php" target="_blank"><img src="../images/printicon.jpg" alt="Click for Printer Friendly  Page" width="31" height="31" align="absmiddle" id="Print" /></a><a href="print/<?php echo $_POST['VenueName'] ?>.php" target="_blank"></a></p>
          
<?php 

if (isset($_POST['VenueName']) && !empty($_POST['VenueName'])) {
//mySQL queries

$query = "SELECT Date, VenueName, Results.VenueID, COUNT( * ) 
FROM Results, Venue
WHERE Results.VenueID = Venue.VenueID
AND VenueName = '".$_POST['VenueName']."'
GROUP BY Date DESC  "; 
$result=mysql_query($query)
	or die ("couldn't execute query");


echo <<<html

<table border="1" width="480" cellpadding="1" cellspacing="1">
<tr><td align="center"><strong>Date</strong></td>
		<td align="center"><strong>No. Players</strong></td>
		</tr>
html;

//Now start the loop.

echo  "<h3> League Games </h3>"; 
$pos=0;


while($r = mysql_fetch_array($result)){
//and echo each new row


echo <<<html

<tr><td align="center">{$r['Date']}</td>
	<td align="center">{$r['COUNT( * )']}</td>

</tr>
html;
$pos++;	}


//And close the table.
echo "</table>";
}
?>

 

[PHP_Idiot]

I've sussed it, I had forgotten to put the ?user_id=543 in and just had the 543. Also realised I'd called it a different thing on each page.

 

Any way problem solved, thanks for the help