Help here please, Database calls, etc.

Beginners - Learning PHP
#1

I’m trying to create a website that will contain information on all graduates from a school that was founded in 1893.

What I’m looking to do is have a single page for each graduating class and then each page will contain a 3-column 4-row table for each classmate. Column 1 will have the rows merged and will contain thier yearbook photo. Column 2 rows 1-4 will contain labels for the rows in Column 3. Column 3 is my problem. Column 3 will be pulling information from a Mysql database. Cell 1 is a “Name” field, Cell 2 is “Location”, Cell 3 is “Occupation”, and Cell 4 is “email.”

The problem lies in my inability to figure out how to have multiply records looked up in a single page. Up to this point I have ever only looked up one “record” per page and the call for that record (ID) was in the URL. That won’t work this time becuase each table will be looking up a different record. For example, the 30th table on the class of 1995 page will lookup the name, location, occupation, and email of the 30th person in the class. (alphabetical) That record id in the database/table would be 1995030. (1995 for the class and 030 for the classmate.)

Really have a hard timefiguring it out and I know it can’t be that hard. Its driving me nuts!

A example of what I’m trying to do is at http://www.mvaalumniarchives.com/lookup/1995.htm (view the code to see my horrible attempt. :-)

#2

What does the code look like that retrieves the rows from the database, and what does the code look like that spits the data out to the HTML page? It sounds like you know the basics of SQL, but you’re forgetting to loop through the query resource.

#3

I think your right I’m skipping a step. Right now really the only code I have is code from the last page I did which is below. The biggest problem, and I think the only thing keeping me from just modifying this code for the fields that I need, is that this code gets the record(row) ID from the URL. The new site will have multi IDs on each page. The first HTML table will pull information from the MySql table, row ID 1995001. The second HTMl Table on the page will pull row ID 1995002 from the same MySql table and so on. This is where I’m stuck. How to you pull from different records (ids) in the same MySql database/table on the same HTML page?

<?php // listing script // connect to the server mysql_connect( '208.179.***.***, '******', '*********' ) or die( "Error! Could not connect to database: " . mysql_error() ); // select the database mysql_select_db( '******' ) or die( "Error! Could not select the database: " . mysql_error() ); // get the id from the URL request $id = $_REQUEST['id']; // retrieve the row from the database $query = "SELECT * FROM `Classmates` WHERE `id`='$id'"; $result = mysql_query( $query ); // print out the results if( $result && $contact = mysql_fetch_object( $result ) ) { // print out the info $lastname = $contact -> lastname; $firstname = $contact -> firstname; $dob = $contact -> dob; $status = $contact -> status; $address = $contact -> address; $city = $contact -> city; $state = $contact -> state; $zip = $contact -> zip; $work = $contact -> work; $email = $contact -> email; $web1 = $contact -> web1; $web2 = $contact -> web2; $message = $contact -> message; ?> 
  <div align="left">
  <table border="1" cellpadding="3" cellspacing="3" bordercolorlight="#FFE8FF" bordercolordark="#840084" bgcolor="#FFFDFF" width="600" align="left" style="text-align: left">
     <tr>
        <th align="left" width="153">First Name:</th>
        <td align="left"><?php echo($firstname) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Last Name:</th>
        <td align="left"><?php echo($lastname) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Date of Birth:</th>
        <td align="left"><?php echo($dob) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Marital Status:</th>
        <td align="left"><?php echo($status) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Mailing Address:</th>
        <td align="left"><?php echo($address) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">City:</th>
        <td align="left"><?php echo($city) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">State:</th>
        <td align="left"><?php echo($state) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Zipcode:</th>
        <td align="left"><?php echo($zip) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Occupation:</th>
        <td align="left"><?php echo($work) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">E-mail:</th>
        <td align="left"><?php echo($email) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Website 1:</th>
        <td align="left"><?php echo($web1) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Website 2:</th>
        <td align="left"><?php echo($web2) ?></td>
     </tr>
     <tr>
        <th align="left" width="153">Message to the Class:</th>
        <td align="left"><?php echo($message) ?></td>
#4

Think I got it figured out. Or at least I found a way that it works. Whether there is an easier and less space wasting way I don’t know but heres what I got:

<?php // listing script // connect to the server mysql_connect( '208.179.***.***', '******', '********' ) or die( "Error! Could not connect to database: " . mysql_error() ); // select the database mysql_select_db( '******' ) or die( "Error! Could not select the database: " . mysql_error() ); // get the id from the URL request $id = $_REQUEST['id']; // retrieve the row from the database $query = "SELECT * FROM `Classmates` WHERE `id`=1995030"; $result = mysql_query( $query ); // print out the results if( $result && $contact = mysql_fetch_object( $result ) ) { // print out the info $lastname = $contact -> lastname; $address = $contact -> address; $work = $contact -> work; $email = $contact -> email; ?> 
  <div align="left">
  <img border="0" src="../../structure/npa.gif" width="118" height="150" align="left"><table border="1" cellpadding="2" cellspacing="1" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF" bgcolor="#FFF9F9" width="400" align="left" style="text-align: left">
     <tr>
        <th align="left" width="94" height="30" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">
		Name:</th>
        <td align="left" height="30"><font color="#800000"><?php echo($lastname) ?></font></td>
     </tr>
     <tr>
        <th align="left" width="94" height="15" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">&nbsp;</th>
        <td align="left" height="15">&nbsp;</td>
     </tr>
     <tr>
        <th align="left" width="94" height="30" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">
		Location:</th>
        <td align="left" height="30"><font color="#800000"><?php echo($address) ?></font></td>
     </tr>
     <tr>
        <th align="left" width="94" height="30" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">
		Occupation:</th>
        <td align="left" height="30"><font color="#800000"><?php echo($work) ?></font></td>
     </tr>
     <tr>
        <th align="left" width="94" height="30" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">
		E-Mail:</th>
        <td align="left" height="30"><font color="#800000"><?php echo($email) ?></font></td>
     </tr>
             
  </table>
 
  <?php

}
else
{
die( "Error: Could not get contact from database. ".mysql_error() );
}

?>

 

 

 

 

 

<?php // retrieve the row from the database $query = "SELECT * FROM `Classmates` WHERE `id`=1995035"; $result = mysql_query( $query ); // print out the results if( $result && $contact = mysql_fetch_object( $result ) ) { // print out the info $lastname = $contact -> lastname; $address = $contact -> address; $work = $contact -> work; $email = $contact -> email; ?> 
  <div align="left">
  <img border="0" src="../../structure/npa.gif" width="118" height="150" align="left"><table border="1" cellpadding="2" cellspacing="1" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF" bgcolor="#FFF9F9" width="400" align="left" style="text-align: left">
     <tr>
        <th align="left" width="94" height="30" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">
		Name:</th>
        <td align="left" height="30"><font color="#800000"><?php echo($lastname) ?></font></td>
     </tr>
     <tr>
        <th align="left" width="94" height="15" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">&nbsp;</th>
        <td align="left" height="15">&nbsp;</td>
     </tr>
     <tr>
        <th align="left" width="94" height="30" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">
		Location:</th>
        <td align="left" height="30"><font color="#800000"><?php echo($address) ?></font></td>
     </tr>
     <tr>
        <th align="left" width="94" height="30" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">
		Occupation:</th>
        <td align="left" height="30"><font color="#800000"><?php echo($work) ?></font></td>
     </tr>
     <tr>
        <th align="left" width="94" height="30" bgcolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF">
		E-Mail:</th>
        <td align="left" height="30"><font color="#800000"><?php echo($email) ?></font></td>
     </tr>
             
  </table>
 
  <?php

}
else
{
die( "Error: Could not get contact from database. ".mysql_error() );
}

?>

 

 

 

 

 

_____________________________-

Only small problem now is the file size. A page with 128 of these tables was 375KB. Reediting some of the graphics while getting rid of a few others and I got it down to 290KB. Thinking I can trim some more by taking out the // references. Good thing is that most of the graduating classes had about 35-45 students so very few will have 100 or more.

#5

Okay, first of all, use [php] and [/php] tags. Second of all, use loops, they’re your friends. And lastly, learn CSS, it’ll clean your code heaps :)

[php]

<?php // connect to the server mysql_connect( '208.179.***.***', '******', '********' ) or die( "Error! Could not connect to database: " . mysql_error() ); // select the database mysql_select_db( '******' ) or die( "Error! Could not select the database: " . mysql_error() ); // get the id from the URL request $id = intval($_GET['id']); // retrieve the row from the database $query = "SELECT * FROM Classmates WHERE id = " . $id; $result = mysql_query( $query ); // print out the results if ($mysql_num_rows($result) > 0) { while ($contact = mysql_fetch_array($result)) { ?>
Name: <?php echo($contact['lastname']) ?>
   
Location: <?php echo($contact['address']) ?>
Occupation: <?php echo($contact['work']) ?>
E-Mail: <?php echo($contact['email']) ?>

 

 

 

 

 

<?php } } ?> [/php]
#6

would appreciate if you could explain. I thought I was using PHP tags, opening with /?PHP and closing with /?.

I’ve heard of loops but never seen them or know how to use them. CSS I don’t think I’ve ever heard of. What is it and how is it used?

Also, I made the changes in the script you made and the page is now not viewable. I then tried copying and pasting and still, going to the URL gives a blank page?

#7

Well, I fixed the script (a bit), but it’s really not my job to just give you a script that works perfectly (mainly because I can’t, since I don’t know your configurations nor your ultimate goals). If you get a blank page, make sure you have any HTML output at all (by viewing the source of your page). Also, try something like

[php]error_reporting(E_ALL);[/php]

at the top of your PHP page. That’ll show you EVERYTHING that’s wrong with your PHP script (but NOT the HTML, keep that in mind).

Second of all, if you don’t know loops, I (and many PHP devvers with me) would suggest learning about them. The main statements for loops are while() and for(). I’m sure you can look them up on the PHP manual.

CSS is an abbreviation for Cascaded Style Sheets, and comes down to using the tags in HTML. It will significantly clean up your HTML code (because frankly, right now it’s a bunch of deprecated tag attributes scraped together).

Sponsor our Newsletter | Privacy Policy | Terms of Service