[SOLVED] Display Record Based on Field in Tables

twilitegxa · August 8, 2009

How do I get it to display the record based on one field? What I need to do is display the record based on the field named "identity" when it's the same as the identity from the previous table. Here is my code:

<?php

session_start();

//Access Tracking Snippet

//set up static variables
$page_title = "showprofile.php";
$user_agent = getenv("HTTP_USER_AGENT");
$date_accessed = date("Y-m-d");

//connect to server and select database
$conn = mysql_connect("localhost", "root", "")
or die(mysql_error());
$db = mysql_select_db("smrpg", $conn) or die(mysql_error());

//create and issue query
$sql = "insert into access_tracker values
('', '$page_title', '$user_agent', '$date_accessed')";
mysql_query($sql,$conn);
?>

<?php
//check for required info from the query string
if (!$_GET['id']) {
header("Location: listcharacters.php");
exit;
}

//connect to server and select database
$conn = mysql_connect("localhost", "root", "")
or die(mysql_error());
mysql_select_db("smrpg", $conn) or die(mysql_error());

//verify the topic exists
$verify_topic = "select identity from scouts where
id = $_GET[id]";
$verify_topic_res = mysql_query($verify_topic, $conn)
or die(mysql_error());

if (mysql_num_rows($verify_topic_res) < 1) {
//this character does not exist
$display_block = "<p><em>You have selected an invalid character.
Please <a href=\"listcharacters.php\">try again</a></em></p>";
} else {
//gather rest of profile
$get_posts = "select *, date_format(create_time, '%b %e %Y at %r') as fmt_scout_create_time from scouts where id = $_GET[id]";

$get_posts_res = mysql_query($get_posts, $conn) or die(mysql_error());

//create the display string
$display_block = "
<table cellpadding=3 cellspacing=1 border=1>";

while ($posts_info = mysql_fetch_array($get_posts_res)) {
	$post_id = $posts_info['id'];
	$identity = ucwords(strtolower($posts_info['identity']));
	$character_create_time = $posts_info['fmt_scout_create_time'];
	$username = $posts_info['username'];
	$name = ucwords(strtolower($posts_info['name']));
	$element_of_influence = $posts_info['element_of_influence'];
	$age = $posts_info['age'];
	$birth_month = $posts_info['birth_month'];
	$birth_date = $posts_info['birth_date'];
	$birth_year = $posts_info['birth_year'];
	$height_feet = $posts_info['height_feet'];
	$height_inches = $posts_info['height_inches'];
	$blood_type = strtoupper($posts_info['blood_type']);
	$hobbies = ucwords(strtolower($posts_info['hobbies']));
	$favorite_color = ucwords(strtolower($posts_info['favorite_color']));
	$favorite_gemstone = ucwords(strtolower($posts_info['favorite_gemstone']));
	$favorite_food = ucwords(strtolower($posts_info['favorite_food']));
	$least_favorite_food = ucwords(strtolower($posts_info['least_favorite_food']));
	$favorite_school_subject = ucwords(strtolower($posts_info['favorite_school_subject']));
	$least_favorite_school_subject = ucwords(strtolower($posts_info['least_favorite_school_subject']));
	$strengths = ucwords(strtolower($posts_info['strengths']));
	$weaknesses = ucwords(strtolower($posts_info['weaknesses']));
	$goal = ucfirst($posts_info['goal']);
	$mission = ucfirst($posts_info['mission']);
	$biography = nl2br($posts_info['biography']);
	$getMonth = date('F', mktime(0, 0, 0, $birth_month));

	//add to display
	$display_block .= "
	<tr>
	<td width=24% valign=top><strong>Character Name:</strong></td>
	<td width=55% valign=top>$name</td>
	<td align=center valign=top rowspan=18><img src=\"image.gif\" height=\"480\" width=\"200\"></td>
	</tr>
	<tr>
	<td><strong>Element Of Influence:</strong></td>
	<td>$element_of_influence</td>
	</tr>
	<tr>
	<td><strong>Age:</strong></td>
	<td>$age</td>
	</tr>
	<tr>
	<td><strong>Date Of Birth:</strong></td>
	<td>$getMonth $birth_date, $birth_year</td>
	</tr>
	<tr>
	<td><strong>Height:</strong></td>
	<td>$height_feet feet $height_inches inches</td>
	</tr>
	<tr>
	<td><strong>Blood Type:</strong></td>
	<td>$blood_type</td>
	</tr>
	<tr>
	<td><strong>Hobbies:</strong></td>
	<td>$hobbies</td>
	</tr><tr>
	<td><strong>Favorite Color:</strong></td>
	<td>$favorite_color</td>
	</tr>
	<tr>
	<td><strong>Favorite Gemstone:</strong></td>
	<td>$favorite_gemstone</td>
	</tr>
	<tr>
	<td><strong>Favorite Food:</strong></td>
	<td>$favorite_food</td>
	</tr>
	<tr>
	<td><strong>Least Favorite Food:</strong></td>
	<td>$least_favorite_food</td>
	</tr>
	<tr>
	<td><strong>Favorite School Subject:</strong></td>
	<td>$favorite_school_subject</td>
	</tr>
	<tr>
	<td><strong>Least Favorite School Subject:</strong></td>
	<td>$least_favorite_school_subject</td>
	</tr>
	<tr>
	<td><strong>Strengths:</strong></td>
	<td>$strengths</td>
	</tr>
	<tr>
	<td><strong>Weaknesses:</strong></td>
	<td>$weaknesses</td>
	</tr>
	<tr>
	<td><strong>Goal:</strong></td>
	<td>$goal...</td>
	</tr>
	<tr>
	<td><strong>Mission:</strong></td>
	<td>$mission.</td>
	</tr>
	<td> </td>
	<td> </td>
	</tr>
	<tr>
	<td> </td>
	<td> </td>
	<td valign=left>Created By: $username<br>
	Created On: [$character_create_time]</td>
	</tr>";
	}

	//close up the table
	$display_block .= "</table>";

	//add biograpgy
	$display_block .= "<p>$biography</p>";

//gather stats and derived values from tables

$get_stats = "select * from stats";
$get_stats_res = mysql_query($get_stats, $conn)
or die(mysql_error());

while ($stats_info = mysql_fetch_array($get_stats_res)) {
	$body = $stats_info['body'];
	$mind = $stats_info['mind'];
	$soul = $stats_info['soul'];
	}

	//display defects
	$display_block .= "<table border=1>
	<th colspan=3>Stats And Derived Values</th>
	<tr>
	<td><strong>Body</strong></td>
	<td>$body</td>
	<td> </td>
	</tr>
	<tr>
	<td><strong>Mind</strong></td>
	<td>$mind</td>
	<td> </td>
	</tr>
	<tr>
	<td><strong>Soul</strong></td>
	<td>$soul</td>
	<td> </td>
	</tr>
	<tr>
	<td> </td>
	<td> </td>
	<td> </td>
	</tr>
	<tr>
	<td><strong>Health Points</strong></td>
	<td> </td>
	<td> </td>
	</tr>
	<tr>
	<td><strong>Energy Points</strong></td>
	<td> </td>
	<td> </td>
	</tr>
	<tr>
	<td><strong>Attack Combat Value</strong></td>
	<td> </td>
	<td> </td>
	</tr>
	<tr>
	<td><strong>Defense Combat Value</strong></td>
	<td> </td>
	<td> </td>
	</tr>
	<tr>
	<td><strong>Total Character Points</strong></td>
	<td> </td>
	<td> </td>
	</tr>
	</table>";

	//display attributes
}
?>
<html>
<head>
<title><?php print $identity; ?>'s Profile</title>
<style type="text/css" media="screen">
/*<![CDATA[*/
@import url(global.css); 
/*]]>*/
</style>
</head>
<body>
<!-- HEADER -->
<h1 class="logo">Sailor Moon RPG</h1>
<!-- /HEADER -->
<?php include("topnav.php"); ?>
<div id="main">
<?php include("includes/log.php"); ?>
<?php include("mainnav.php"); ?>
<h1 align="center"><?php print $identity; ?></h1>
<?php print $display_block; ?>
</div>
<?php include("bottomnav.php"); ?><!-- FOOTER -->
<!-- FOOTER -->
<div id="footer_wrapper">
<div id="footer">
<p>Sailor Moon and all characters
are<br /> 
trademarks of Naoko Takeuchi.</p>
<p>Copyright © 2009 Liz Kula. All rights reserved.<br />
A product of <a href="#" target="_blank">Web Designs By Liz</a> systems.</p>
<div id="foot-nav">
<ul>
<li><a href="http://validator.w3.org/check?uri=http://webdesignsbyliz.com/digital/index.php" target="_blank"><img src="http://www.w3.org/Icons/valid-xhtml10-blue" alt="Valid XHTML 1.0 Transitional" height="31" width="88" /></a></li>
<li><a href="http://jigsaw.w3.org/css-validator/validator?uri=http://webdesignsbyliz.com/digital/global.css" target="_blank"><img class="c2" src="http://jigsaw.w3.org/css-validator/images/vcss-blue" alt="Valid CSS!" /></a></li>
</ul>
</div>
</div>
</div>
<!-- /FOOTER -->
</body>
</html>

In the table named "scouts" there is a field named identity and I want the second query to pull the record of the second table (stats) if the two fields are the same (both identity in scouts and stats are the same name). I'm having some trouble. Can anyone help?

trq · August 8, 2009

You should have a read of this tutorial. You can get what you want done using one query.

twilitegxa · August 8, 2009

Well, right now I have this:

//verify the scout exists
$verify_scout = "select identity from stats where
id = $_GET[id]";
$verify_scout_res = mysql_query($verify_scout, $conn)
or die(mysql_error());

if (mysql_num_rows($verify_scout_res) < 1) {
//this character does not exist
$display_block = "<p><em>You have selected an invalid character.
Please <a href=\"listcharacters.php\">try again</a></em></p>";
} else {
//gather rest of profile

$get_stats = "select * from stats where id = $_GET[id]";
$get_stats_res = mysql_query($get_stats, $conn)
or die(mysql_error());

while ($stats_info = mysql_fetch_array($get_stats_res)) {
	$body = $stats_info['body'];
	$mind = $stats_info['mind'];
	$soul = $stats_info['soul'];
	}

And this pulls the information if the id's match, but I want to make sure I don't run into problems (like if the auto incrementing number for the record is different), but I'm having trouble following the tutorial and figuring out how to join the tables.

trq · August 8, 2009

So you read that tutorial in like 25 minutes? Thats pretty good, however its no wonder it didn't sink in.

Why not take another read this time, try the examples.

Thing is, I could do it for you and you will learn nothing. Learn how to do it yourself and you'll never need to ask this question again in the future.

twilitegxa · August 8, 2009

Well, no I haven't read the entire thing, but I have worked with these joins before and I'm not quite understand it. I tried this:

$get_stats = "select * from stats, scouts where stats.identity = scouts.identity";

Following this example:

select house.house_name, pupil.pupil_name from house, pupil where house.house_name = 'jardine' and house.houseid = pupil.houseid order by pupil.pupil_name

But I'm still doing something wrong because it's still only pulling that one record.

twilitegxa · August 8, 2009

I also tried this:

$get_stats = "select stats.body, stats.mind, stats.soul, stats.identity, scouts.identity from stats, scouts where stats.identity = scouts.identity";

But it's not changing anything. What am I doing wrong?I know I must be missing something.

twilitegxa · August 23, 2009

All I did was changed my select statement to add where identity = '$identity' and now it works. I also changed my statement back to the original way I had it, with just:

$get_stats = "select * from stats where identity = '$identity'";

Thanks for all the help!

Sign In

[SOLVED] Display Record Based on Field in Tables

Recommended Posts

twilitegxa

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information