[RESOLVED] Displaying database contents on two pages

[gauntletxg]

hello all, this is my first post here and i am in need of some help, so hopefully someone here would be kind enough to point me in the right direction.

i have a basic 'contact us' type web form, and when the user submits it the data is inserted into a mysql database. i currently have a page with a table that will display these records. the code is as follows:

<?
$username="name";
$password="pwd";
$database="db";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contactus ORDER BY name ASC";
$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();

?>

// Table headers on one row (name, email address, etc.)

  <?
$i=0;
while ($i < $num) {
$name=mysql_result($result,$i,"name");
$email=mysql_result($result,$i,"email");
?>  

// New row

<td><? echo $name; ?></td>
<td><? echo $email; ?></td>

// End Row

  <?
$i++;
}

?>

there are actually 9 fields in the database, but i just included name and email in the sake of brevity. the problem i am encountering is that the last field is for comments, and this can take up quite a bit of space in the table i am using to display the database contents, and after awhile the table begins to look incredibly disjointed.

the idea i came up with to fix this is that for each record in this table, the entry under comments will simply be 'click to view', and the user can click this link and a new window will pop up and display the message in full. i've gotten the pop-up window to work with javacript, and i figured the link would have some php code to display the corresponding comments section for each record. i was thinking i would have to set up a variable or something along those lines, but i'm pretty new to php (and programming in general) so i'm not really sure where to go from here. any help would be greatly appreciated.

thanks!

[bpat1434]

One suggestion is to create a link to the page you want, and add a GET variable. Your url would look something like:
http://www.sitename.com/comments.php?comment=##
where the "##" is replaced by the row id number from mySQL.

Then on that next page (comments.php from above) you will retrieve the GET variable and run a query LIMITing by a WHERE clause in mySQL so that you get only the row where the id = ## 😉

A quick example would be;

<?php

$id = (int)$_GET['id'];

$query = "SELECT 'comments' FROM `table` WHERE id='".mysql_real_escape_string($id)."' LIMIT 0,1";
$result = mysql_query($query);
$comments = mysql_result($result, 0, 0);

echo $comments;
?>

Hope that helps.

[EDIT]
[6-26-07] Fixed code issue that was pointed out down below.

[laserlight]

Just a comment on bpat1434's example: if you force a cast of an incoming variable to int, there is no need to escape it with mysql_real_escape_string() since it will definitely be an int value, and thus not have anything to be escaped.

[gauntletxg]

thanks for the quick replies and help guys, but i think i'm confusing myself even more...there's a whole lot of php i don't fully understand yet.

first i want to make sure i understand what is going on conceputally, so either bear with me or just ignore this paragraph. for my browse page, the link under message will point to the id column in the database, which identifies each record. this id is stored in a variable. the results page then retrieves this variable, and displays the entry under message for that specific record. correct?

i think i'm struggling with the grabbing and retrieval of this id...it's probably a really easy solution, but i can't figure it out. for the links (maybe this is what i'm doing wrong), i have this code:

<td><a href="message.php?id=<? echo $id; ?>Click to view</a></td>

where $id is specified as

$id=mysql_result($result,$i,"id");

the code for message.php looks like this:

<?php 

$username="name";
$password="pwd";
$database="db";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$id = (int)$_GET['id']; 

$query = "SELECT 'message' FROM `contactus` WHERE id='".mysql_real_escape_string($id)."' LIMIT 0,1"; 
$result = mysql_query($query); 
$message = mysql_result($result, 0, 1); 

echo $message; 

mysql_close();
?> 

i'm thinking the problem is that i'm not sure of how to set the id from the link in the browse page as a variable so it can be retrieved in the results page. can anybody help me out, or point me in the direction of a resource that explains how the process works in layman's terms?

thanks again guys, i really appreciate it

[bpat1434]

You've got the ideas....

From your first post:

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contactus ORDER BY name ASC";
$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();

?>

// Table headers on one row (name, email address, etc.)

  <?
$i=0;
while ($i < $num) {
/**********************************/
$id = mysql_result($result, $i, 'id');  /* This is where the ID is set to a variable */
/**********************************/
$name=mysql_result($result,$i,"name");
$email=mysql_result($result,$i,"email");
?>
// New row

<td><a href="message.php?id=<? echo $id; ?>"><? echo $name; ?></a></td>
<td><? echo $email; ?></td>

// End Row

  <?
$i++;
}

?>   

You'll notice my large comment area is where the ID variable is set, and then down in the table area, I created the link on the name. Hope that helps.

[gauntletxg]

ok, i actually did understand that beforehand, i was thinking that the id had to be added as a variable on the results page and with the get variable on the browse page, when it's the other way around. brain fart on my end.

i tried running your first example and i get an unable to jump to row 0 on idex 2 error on the line

$message = mysql_result($result, 0, 1); 

google isn't much help - when i search for the error message i just get websites that are displaying similar errors.

also - are there two different id's involved here? i was thinking the id referred to the auto-incrementing id field in the database, but it almost seems like the id refers to something going on exclusively for this script. i noticed the id variable you added is very similar to the one i've been tinkering with (post above), except yours has single-quotes and mine has double qoutes

[bpat1434]

Single quoted strings aren't parsed by PHP.
Double quoted strings are parsed by PHP so that interpolated variables are replaced with their values.

The error just means that the field id (1) isn't in the array. That's my mess-up. It should be:

$comments = mysql_result($result, 0, 0);

Sorry.

[gauntletxg]

ack. my apologies, but i can't figure this out. i don't think i even care about this feature anymore, it's just annoying me so much that i have to finish it. i made the change you suggested and i don't get any error messages anymore. however, instead of displaying the contents of the message field on id (whatever), it just displays the word message.

here's the browse page:

<?
$username="name";
$password="pwd";
$database="db";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contactus ORDER BY name ASC";
$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();

?>

// Table header row

  <?
$i=0;
while ($i < $num) {
$id = mysql_result($result, $i, 'id');
$name=mysql_result($result,$i,"name");
$title=mysql_result($result,$i,"title");
$school=mysql_result($result,$i,"school");
$email=mysql_result($result,$i,"email");
$address=mysql_result($result,$i,"address");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
$phone=mysql_result($result,$i,"phone");
$fax=mysql_result($result,$i,"fax");
$message=mysql_result($result,$i,"message");
?>  

<tr>
  <td><? echo $name; ?></td>
  <td><? echo $title; ?></td>
  <td><? echo $school; ?></td>
  <td><? echo $email; ?></td>
  <td><? echo $address; ?></td>
  <td><? echo $city; ?></td>
  <td><? echo $state; ?></td>
  <td><? echo $zip; ?></td>
  <td><? echo $phone; ?></td>
  <td><? echo $fax; ?></td>
  <td><a href="message.php?id=<? echo $id; ?>">Click to view</a></td>
</tr>

  <?
$i++;
}

?>

this seems to be working ok as far as i can tell. the id in each link corresponds to the respective record. this is the results page (message.php):

<?php 

$username="name";
$password="pwd";
$database="db";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$id = (int)$_GET['id']; 

$query = "SELECT 'message' FROM `contactus` WHERE id='".mysql_real_escape_string($id)."' LIMIT 0,1"; 
$result = mysql_query($query); 
$message = mysql_result($result, 0, 0); 

echo $message; 

mysql_close();
?> 

when i click the click to view link the page it takes me to just displays the word message, regardless of which record i'm attempting to view. i'm sure it's something remarkably simple, but i can't see it. 🙁

[bpat1434]

remove the single quotes from around the 'message' in your query in message.php. Single quotes in queries denote a string, back ticks (`) denote a column name 😉

[gauntletxg]

those bloody quotes shakes fist

thanks so much for your help, i really appreciate it!

[bpat1434]

no problem. If this is resolved, please mark it as such.