View Full Version : Turning Table Data into a Page View

05-20-2004, 08:44 AM
Can this be done?

I have a table that returns some of the data from each record the MySql database (Name, Address & Telephone).

What I would like to know is if it is possible to generate a page showing all the information held for that record by clicking the relevent line in the table, for example row 6 or row 99.

In MS Access it would be like switching from table to form view.

I have very limited knowledge of PHP so any pointers would be appreciated

05-20-2004, 08:51 AM
The most common way is just to display one of the fields (like the name) as a link to a second page, with the records primary key value in the querystring. On this second page, you then read the querysrtring value and use it inside your selectquery. Like

$sql="select ... from ... where userID=" . $_GET['uID'] ;

where userID is the PK of that table, and uID is the variable in the querystring that was set like
echo ('<a href="secondpage.php?uID=' . $row['userID'] . ' title...>' . $row['name'] . '</a>');

05-20-2004, 09:20 AM
Ok - so it can be done.

Is it possible to "Dumb Down" your answer a little!!

I'm using the following piece of code to return the data into a table:-
While($row = mysql_fetch_row($result)) {
echo "<tr>";
for ($i=0; $i < mysql_num_fields($result); $i++) {
echo "<td align=center>$row[$i]</td>";
echo "</tr>\n";

How would I amend this to get a link into the first (or one of the other)fields?

The rest of your answer may drop into place if I can sort out the first bit. At the moment I don't understand the code

05-20-2004, 09:51 AM
OK. Assume that your select would look like

select userID, name, adress, phone from table

and that you display them in a table where the first column is the name, the second column is the adress and third column is the phonenumber.
to display the name as a link with the userID in the querystring, your code would look like

While($row = mysql_fetch_row($result)) {
echo '<tr>';
echo ('<a href="secondpage.php?uID=' . $row[0] . '" title="See the userdetails for user '. $row[1] .'">' . $row[1] . '</a>');
for ($i=2; $i < mysql_num_fields($result); $i++) {
echo '<td align="center">' . $row[$i] . '</td>';
echo "</tr>\n";

$row[0] contains the userID value,
$row[1] contains the users name

i assume you've copyed some older code from somewhere. It's recommended to use mysql_fetch_assoc() instead of mysql_fetch_row(), because you then get an associative array so you can use the more meaningfull $row[userID] instead of $row[0].
by using mysql_fetch_array() you both get an associated and nyumerical array --> for instance to do this for() loop in your code.
also look at the quetes i used --> single quotes around string that don't need to be parsed (all except the "\n") and concatenating the variables instead of including then inside double quoted strings.
This way it's also easier to output valid html code (double quoted html-attribute values like the "center")

05-20-2004, 10:42 AM
Making some headway. My Select is:-
Select name, town, county, telephone, fax, email from TABLE where county Like Somerset order by town. They are then displayed:-
Name, Town, County, Telephone, Fax, Email

I have amended my While statemnt to include the code you provided. Two problems have come up. First the Links appear outside the table and is showing the town not the name. So 10 rows push the table 10 lines down the page. Second the table is now incorrect with the name column showing the County, the town column showing the telephone no, the county showing the fax no etc. The name and town have disappered

I don't understand the use of .php?uID= or $row[0]. I am also unclear as to the for loop for ($i=2; $i < mysql_num_fields($result); Why has $i changed from $i=0 to $i=2. Can you educate a poor old man a little further. I'll try out row_assoc later if thats ok.

05-20-2004, 11:14 AM
When i say 'assuming', then this of course doesn't mean that your actual query is like that :rolleyes: I need to assume your making some sort of select to illustrate the code and because i have insufficient psychic powers to know your actual select.

if your select is

Select name, town, county, telephone, fax, email from TABLE where county Like Somerset order by town

then your code needs to be

While($row = mysql_fetch_row($result)) {
echo '<tr>';
echo ('<td><a href="secondpage.php?name=' . $row[0] . '" title="See the userdetails for user '. $row[0] .'">' . $row[0] . '</a></td>');
for ($i=1; $i < mysql_num_fields($result); $i++) {
echo '<td align="center">' . $row[$i] . '</td>';
echo "</tr>\n";

about the 3 questions:
--> the names outside the table was my bad. I forgot the columntags. Added them in the code above
--> the shown data : for ($i=0; $i < mysql_num_fields($result); $i++) will print each field inside a column. Now, if you want to have a column for each field, then your for loop needs to start at '0' because the $row is a zero-based array. So $row[0] = the first field (=name).
But ... we now have printed the name as a link before the for() loop, so we only need the second and folowing fields, so we let the for-loop start at the second field by setting the initial value for $i to 1.
--> the use of .php?uID= or $row[0] : All the links point to the same page. So inside that page, you need to be able to see which link (which name) was clicked. This is done wy adding a querystring to the fileadress. Like if you look at the adressbar at the top of this page, you'll see ?p=202874.
If you hoover over the links and look at the statusbar (bottom of your browser window), then you'll see that if you hoover over a different name, that only the querystringvalue will change.
Now, normally, you'll have a primary key inside each table. A primary key is an autonum column with a unique value for each name. There are several reasons to always include a PK inside each table. For this example for intance : the PK value will be numerical, which makes it
- easier to use inside a querystrin because you don't need to wory about weird characters that need encoding/decoding
- easier to check against sql-injections --> because all non-numercical values are altered an invalid
- faster selected --> the PK field is indexed and the required record will be returned quicker

If you don't have a PK, then you need to append another value to the querystring. In the code above, i added name=the name

so your select on the second page will look like

$sql="select your variables from TABLE where name='". $_GET['name'] . "'";

05-20-2004, 12:15 PM
Was playing around before your post arrived. Got the tables back in the right order. I'll try and understand your explanations in a minute. Still had the first column outside the table. I missed the column tags as well. That bit is now sorted.
As an aside this bit of could this bit of code be used to provide a link\mailto by changing the href to the relevent $row number?

I look at the secondpage coding now and get back as soon as I get somewhere. You have been a real gent - thanks very much

05-20-2004, 01:07 PM
Ok I'm back. I can get the query to run on the second page.

$result=MySQL_QUERY("Select * from table where name='".$_GET['name']. "'");

I get Resource id #2 if I use print or echo $result
I get the name if I use print or echo $name

If if try any other field names ($telephone) I get nothing.

If I use the original table output code all the data is returned in the table. Am I missing something simple here?

05-20-2004, 01:52 PM
I get Resource id #2 if I use print or echo $result

$result will contain the "resrource identifier". It's just a handle to get to the actual resultset and it's values.

You should never use a * inside embedded sql : always specify the columnnames.

assume that your query would look like

$sql="select var1, var2, var3 from table where name=" . $_GET['name'];

then you can get the values for var1, var2 and var3 like

$sql="select var1, var2, var3 from table where name=" . $_GET['name'];
$result=mysql_query($sql) or die ('Querysproblem when selecting userdetails.');
if (mysql_num_rows($result) >= 1){
while ($row=mysql_fetch_assoc($result)){
echo ('<tr>
<td>' . $row['var1'] . '</td>
<td>' . $row['var2'] . '</td>
<td>' . $row['var3'] . '</td>
} else {
echo 'No records foud foor user with name ' . $_GET['name'];

05-21-2004, 09:11 AM
Mmm - Resource Identifiers and never use a *. Thanks for the tip.

I'll try your code later on.

Got a fudge working using the table output and splitting the results. No very elegant but it works. Yours is considerably tidyier.

You were dead on about special characters. Anything with a '&' in the Name does not display. Any hints?

Thanks for all the help

05-21-2004, 10:09 AM
Any hints?
Yes. Simply add a new column to the table and make it the primary key and autonum column. And then also select that column on your first page, and use that value inside the querystring --> just like in my initiale code.

You'll need to use that setup in almost any db-driven pages so you might as well start using it now ...