Showing MySQL rows in a PHP file

[KingPuyol]

How can I show MySQL rows in a PHP file?

Like if I have a table called "coders" and in it I have "coderid", "codername", and "codercountry"

and the values are:
1 - John - USA
2 - Mary - England
3 - Jordi - Spain
etc etc.

I want to show ALL THE ROWS in the following format:

<a href="coder.php?show="'$coderid'">'.$codername.' ('.$codercountry.')</a>
Or whatever way you can write it.

So I want the HTML output to be:

<a href="coder.php?show=1">John (USA)</a>
<a href="coder.php?show=2">Mary (England)</a>
<a href="coder.php?show=3">Jordi (Spain)</a>

So who can this be done, I want to show ALL THE ROWS.

[Dismounted]

I'm presuming you're using the vBulletin backend in the following example.

PHP Code:

// cache data
$tabledata = $vbulletin->db->query_read("SELECT * FROM `coders`");
$tabledata = $vbulletin->db->fetch_array($tabledata);

// display rows
foreach ($tabledata AS $row)
{
echo '<a href="coder.php?show=' . $row['coderid'] . '">' . $row['codername'] . '  (' . $row['codercountry'] . ')</a>';
} 


[KingPuyol]

Thanks but can you show me something without the vBulletin backend, I understood how to do it but if you can show me another example without the vBulletin backend then it will be much easier.

[firstrebel]

<?php

// change hostname, username and password, dbname

if(!isset($query) || empty($query))
	($query= "select coderid,codername,codercountry from coders order by coderid asc");	
	$query = stripslashes($query);
	$db_link = mysql_connect('hostname', 'username', 'password')
  		or die("Could not connect to database. Please try again later.");
	mysql_select_db('dbname') or 
		die("could not connect to database");
	$result = mysql_query($query) or	
		die( mysql_error() );
	$number_cols = mysql_num_fields($result);
	
	echo "<table width=740  border=1 align=center cellspacing=0 cellpadding=5>\n";
	echo "<tr align=center>\n"; 
	echo "<font face=arial>";
	echo "<font size=-1>";	
	for ($i=0; $i<$number_cols; $i++)
	{
		echo "<th>" .mysql_fieldname($result, $i). "</th>\n"; 
	}
	echo "</tr>\n";
	
	while ($row = mysql_fetch_row($result))
	{
		echo "<tr align=left>\n";

		for ($i=0; $i<$number_cols; $i++)
		{
			echo "<td>";
			echo "<font face=verdana>";
			echo "<font size=-1>";		
			if (!isset($row[$i]))
				{echo "n.a.";}
			else 
				{echo $row[$i];}
			echo "</td>\n"; 
		}
		echo "</tr>\n";
	}
	echo "</table>";
	echo "</font>";
      mysql_close($db_link);
?>

[KingPuyol]

Thanks and can you please explain what is $i and can you also tell me:
1- How can I limit the number of rows to show, for example: showing the last 20 rows.
2- Paginating rows, for example, showing first 20 rows and then the next 20 in the other. etc..
3- If one of the fields that I have is empty, for example let's say the table is called table and the column which is going to be empty is called game. How can I select the empty one:
Here's the table:

id----  player      ---- game
1 ----  Andrew    ---- 5
2 ----  Someone  ---- 3
3 ----  King          ---- NULL*
4 ----  MOnkey    ---- NULL

I meant by NULL that it's EMPTY, nothing is in it, so if I wanted to arrange all this in Ascending Order by ID and I want it to select the FIRST row where the game field is NULL.

[firstrebel]

To limit the return you would use LIMIT is the SELECT statement

$query= "SELECT coderid,codername,codercountry from coders order by coderid asc LIMIT 20"

This would return the last 20 in an ascending order. To get the last 20 in descending order change asc LIMIT 20 to desc LIMIT 20.

To get a specific range you would use asc LIMIT 1,20 which would give rows 1-20, or asc LIMIT 20,40 would give rows 20-40. Play around until you get what you want by changing asc and desc and the LIMIT parameters.

I don't have a pagination script in working order at the moment, it is something I need to get working on one of my dbs that has around 2200 rows. I have a script but not integrated into the rest of the script.

In the SELECT statement you specify all the fields you want called, whether they have data in them or not. Any that are empty will just have an empty cell in the row.

Try this as your select statement from your example where the table fields are id, player, game:

($query= "select id, player, game from tablename order by id asc");

Change tablename to whatever it is in the db.

Bob

[KingPuyol]

Thanks, I really appreciate it.