twilitegxa Posted August 16, 2009 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
twilitegxa Posted August 16, 2009 Author Share Posted 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)); Quote Link to comment Share on other sites More sharing options...
Mardoxx Posted August 16, 2009 Share Posted August 16, 2009 shouldn't $stats_info = be $stats_info == ? Quote Link to comment Share on other sites More sharing options...
twilitegxa Posted August 16, 2009 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Mardoxx Posted August 16, 2009 Share Posted August 16, 2009 oh yeah ignore me lawl I was being retarded Quote Link to comment Share on other sites More sharing options...
pengu Posted August 16, 2009 Share Posted August 16, 2009 Don't know if it's any help but.. With my SQL stuff, I usually put them within ' and ' Quote Link to comment Share on other sites More sharing options...
twilitegxa Posted August 16, 2009 Author Share Posted August 16, 2009 What do you put in ' '? Quote Link to comment Share on other sites More sharing options...
twilitegxa Posted August 17, 2009 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
pengu Posted August 17, 2009 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
DarkendSoul Posted August 17, 2009 Share Posted August 17, 2009 Clean your variables before you put them through a mysql query! http://ca.php.net/mysql_real_escape_string Quote Link to comment Share on other sites More sharing options...
twilitegxa Posted August 23, 2009 Author Share Posted 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.