Displaying a roster from SQL table

MichaelCS

Hey Guys,

I'm new to PHP (headdesk) and am slowing learning my way to chop shop style PHP coding (yay me).

My question is this...

I want to display a roster similar to http://www.hadean.org/help/help1.jpg

If you notice it displays entries in my database in alphabetical order and you can click on the name column to be taken to a page that gives a ton more details PLUS images about that specific item.

Think of it as a roster. You see all the names of the people in a group. You can click on a members name and be taken to his profile where it will show some images and whatever other info is stored on the database under his entry.

Does anyone have code for something like this they can share? Say a limit of 15-20 displays per page?

MichaelCS

Wow nobody has done anything like this before?

laserlight

Well... it is kind of routine, so it makes for an excellent opportunity to learn some of the basics. What have you tried?

MichaelCS

Hi'ya,

I've only tried the basics... Any script I have been able to find only displays the entries but I have run in to the following problems...

A) It will display ALL the entries and not break it down to 15/20 per page
😎 It only displays the text of the table rows that I select. It will not make clickable links that lead to individual pages for each listing in the table.

I've been searching all the major PHP sites and this seems to be one of the more active ones with people that actually help noobs like me so that is why I'm posting here.

I am not competent enough in PHP to write scripts from scratch so I look for spinets of code and reverse engineer them to understand how they work or change a few variables and plug it in to my PHP/HTML files etc. Sorta Chop Shop Logic, I confess 🙂

This is all for a hobby site (www.hadean.org) that I am trying to put together.

laserlight

MichaelCS wrote:
A) It will display ALL the entries and not break it down to 15/20 per page

One keyword to search for is pagination.

MichaelCS wrote:
😎 It only displays the text of the table rows that I select. It will not make clickable links that lead to individual pages for each listing in the table.

Adding the clickable links is a piece of cake, or rather, a piece of HTML output via PHP. The real problem here is to create a script that displays the individual data. You can do this by providing say, the id of the data via the query string. With this id you can then select the desired row.

MichaelCS

Pagination eg? Awesome I will start looking when I get home.

As for the second part..... (blink blink blink)

Is that covered under pagination?

Check out http://www.camospecs.com/MiniList.asp

He does it using asp but there has to be a way to do it using php?!?

bradgrafelman

MichaelCS wrote:
there has to be a way to do it using php?!?

Yes, and it's the exact same method that you'd use in ASP (which, incidentally, is the exact same method laserlight described above).

Derokorian

Also personally I recommend building your scripts from scratch when learning so you don't just see the result of something, but understand how to use it.

to a) to determine how much info to display, its order and how many thats all done via the query passed to your database. Lets have a look:

I want to retrieve - certain information - from my members - that are in said group -   organized by field   - and I want to see 15 of them
        SELECT              field_list                      table_name          WHERE conditional       ORDER BY field ASC                           LIMIT 15

to b) to make clickable links you build an html element when you output the data you just selected in A.

You have selected the following data:
ID (int), Name (string), Type (string), Code (int)... 

Lets use this data to create a link from this row to a full profile page, which will be deciphered in profile.php:

<?php
// First create an html table
echo '<table>
   <tr>
      <th>Name</th>
      <th>Type</th>
      <th>Code</th>
   </tr>';
// Create a loop to get one row of the returned data at once:
while( $row = mysql_fetch_assoc($result) ) {

// while continues util the evaluation in () is false
// $row will contain a single row of data say: ID=1,name=Dero,type=BA,code=1234
// each peice of information will be available thru array keys IE $row['id'] will give me the value of 1

// now start printing this information to the browser with a link to the user's profile where their name is
echo '<tr>
   <td><a href="profile.php?id='. $row['id'] .'">'. $row['name'] .'</a></td>
   <td>'. $row['type'] .'</td>
   <td>'. $row['code] .'</td>
</td>';

//clode the loop
}

// end the html table
echo '</table>';

MichaelCS

Very nice guys! Thanks.

Between what was posted here and piecing together the returned errors (and searching google) I was able to figure out how to add the SELECT query...

Here is the code I used...

<?php

//MySQL Database Connect
 include 'connect.php'; 

// First create an html table
echo '<table>
   <tr>
      <th>Name</th>
      <th>Variant</th>
   </tr>';
// Create a loop to get one row of the returned data at once:

$result = mysql_query("SELECT * FROM MiniData");

while( $row = mysql_fetch_assoc($result) ) {

// while continues util the evaluation in () is false
// $row will contain a single row of data say: ID=1,name=Dero,type=BA,code=1234
// each peice of information will be available thru array keys IE $row['id'] will give me the value of 1
// now start printing this information to the browser with a link to the user's profile where their name is
echo '<tr>
   <td><a href="profile.php?id='. $row['id'] .'">'. $row['mech'] .'</a></td>
   <td>'. $row['variant'] .'</td>
</td>';

//clode the loop
}

// end the html table
echo '</table>';
?>

Now it's time to flesh out the table so it doesn't look so spartan!

I appreciate the help guys! That was fast too!

MichaelCS

Oh, that code above I couldn't get the...

WHERE conditional ORDER BY field ASC LIMIT 15

Couldn't get that to work.. gave me errors?!?!

MichaelCS

MichaelCS;10980486 wrote:
Oh, that code above I couldn't get the...
WHERE conditional ORDER BY field ASC LIMIT 15
Couldn't get that to work.. gave me errors?!?!

(head-desk)

Nevermind guys I figured it out. I've got it all working in a nice little table.

Is there an easy way to incorporate pageination in to this?

<?php

//MySQL Database Connect
 include 'connect.php'; 

// First create an html table
echo '<table border="0" cellpadding="1">

   <tr>
      <th>Name</th>
      <th> </th>
      <th>Variant</th>
      <th> </th>
      <th>Description</th>
      <th> </th>
      <th>Artist</th>
   </tr>';
// Create a loop to get one row of the returned data at once:

$result = mysql_query("SELECT * FROM MiniData ORDER BY mech ASC");

while( $row = mysql_fetch_assoc($result) ) {

// while continues util the evaluation in () is false
// $row will contain a single row of data say: ID=1,name=Dero,type=BA,code=1234
// each peice of information will be available thru array keys IE $row['id'] will give me the value of 1
// now start printing this information to the browser with a link to the user's profile where their name is
echo '<tr>
   <td><a href="profile.php?id='. $row['id'] .'">'. $row['mech'] .'</a></td>
   <td width="15"> </td>
   <td>'. $row['variant'] .'</td>
   <td width="15"> </td>
   <td>'. $row['description'] .'</td>
   <td width="15"> </td>
   <td>'. $row['artist'] .'</td>
</td>';

//clode the loop
}

// end the html table
echo '</table>';
?>