[SOLVED] Select statement help

twilitegxa · August 16, 2009

I am trying to select the records from a table in my database where the field "identity" matches the field named also "identity" in another table which is presently being displayed on the same page (table "scouts"). The field has already been set into a variable ("$identity") previously as well, but my statement isn't working. I currently have this statement:

//gather stats and derived values

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

$get_stats_res = mysql_query($get_stats, $conn) or die(mysql_error());

$display_block .= "
<table cellpadding=3 cellspacing=3 border=1>";

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

$display_block .= "
<tr>
<td valign=top>
<table cellspacing=3 cellpadding=3 border=1>
<th colspan=3>Stats And Derived Values</th>
<tr>
<td><b>Body</b></td>
<td>$body</td>
<td> </td>
</tr>
<tr>
<td><b>Mind</b></td>
<td>$mind</td>
<td> </td>
</tr>
<tr>
<td><b>Soul</b></td>
<td>$soul</td>
<td> </td>
</tr>";
}

But I am getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Moon' at line 1

I can't figure out how write the select statement. Here is the full 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($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']);
	$biography = str_replace("\n", "<p>", $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=\"500\" 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><br>
	<p>$biography</p>";
}

//gather stats and derived values

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

$get_stats_res = mysql_query($get_stats, $conn) or die(mysql_error());

$display_block .= "
<table cellpadding=3 cellspacing=3 border=1>";

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

$display_block .= "
<tr>
<td valign=top>
<table cellspacing=3 cellpadding=3 border=1>
<th colspan=3>Stats And Derived Values</th>
<tr>
<td><b>Body</b></td>
<td>$body</td>
<td> </td>
</tr>
<tr>
<td><b>Mind</b></td>
<td>$mind</td>
<td> </td>
</tr>
<tr>
<td><b>Soul</b></td>
<td>$soul</td>
<td> </td>
</tr>";
}

$display_block .= "
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td><b>Health Points</b></td>
<td>$health</td>
<td> </td>
</tr>
<tr>
<td><b>Energy Points</b></td>
<td>$energy</td>
<td> </td>
</tr>
<tr>
<td><b>Attack Combat Value</b></td>
<td>$acv1</td>
<td>$acv2</td>
</tr>
<tr>
<td><b>Defense Combat Value</b></td>
<td>$dcv1</td>
<td>$dcv2</td>
</tr>
<tr>
<td><b>Total Character Points</b></td>
<td>$total_cp</td>
<td> </td>
</tr>
</table><br>";

$display_block .= "

<table cellpadding=3 cellspacing=3 border=1>
<th colspan=2>Character Defects</th>
<tr>
<td>$defect</td>
<td>$level</td>
</tr>
</table></td>";

// gather attributes and sub-attributes

$get_attributes = "select * from scout_attributes";
$get_attributes_res = mysql_query($get_attributes, $conn)
or die(mysql_error());

$display_block .= "
<td valign=top>
<table cellspacing=3 cellpadding=3 border=1>";

//display while statement here

$display_block .= "		
<th colspan=3>Character Attributes And Sub-Attributes</th>
<tr>
<td><b>Attribute/Sub-Attribute</b></td>
<td><b>Level</td>
<td><b>Points</b></td>
</tr>
</table></td></tr></table>";

?>
<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>

A you can see, the first table's records are being display based on the id selected, but I can't use that for the next statement because the id's will not always match. Can anyone tell me how I need to write this statement properly?

twilitegxa · August 16, 2009

Here is the part I want to get the identity from:

//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($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']);
	$biography = str_replace("\n", "<p>", $biography );
	$getMonth = date('F', mktime(0, 0, 0, $birth_month));

Mardoxx · August 16, 2009

shouldn't $stats_info =

be

$stats_info ==

?

twilitegxa · August 16, 2009

I haven't used == on any other of the statements like this:

while ($posts_info = mysql_fetch_array($get_posts_res)) {

These others like this work fine. I think the where part is my problem.

Mardoxx · August 16, 2009

oh yeah ignore me lawl

I was being retarded

pengu · August 16, 2009

Don't know if it's any help but..

With my SQL stuff, I usually put them within ' and '

twilitegxa · August 16, 2009

What do you put in ' '?

twilitegxa · August 17, 2009

I don't think that has any effect on it because my other statement did not need them. I need to know how to get the record in the database based on the identity field that matches the identity field from the table that was just selected.

pengu · August 17, 2009

Like if its an absolute number you can have identity = $id, where $id would equal 0 1 2 3 ect

But if it's got characters.. like a name you'd go identity = '$id'

Does that make sense?

But I'm not sure it even matters

DarkendSoul · August 17, 2009

Clean your variables before you put them through a mysql query! http://ca.php.net/mysql_real_escape_string

twilitegxa · August 23, 2009

I figured out the problem. All I had to do was put $identity in single quotes:

'$identity'

in the statement, an now it works perfectly. Thank everyone!

Sign In

[SOLVED] Select statement help

Recommended Posts

twilitegxa

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

Mardoxx

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

Mardoxx

Link to comment

Share on other sites

pengu

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

pengu

Link to comment

Share on other sites

DarkendSoul

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information