ed01 Posted April 25, 2010 Share Posted April 25, 2010 Please help, I've spent more hours than I'd like to admit on this one..... I need to substitute the 89 with a variable like $game_id in the following line; $query = "UPDATE MountainFCgamereports2010 SET approved='Yes' WHERE game_id=89"; I've tried MANY combinations and syntaxes, but nothing will work.... ugh. $query = "UPDATE MountainFCgamereports2010 SET approved='Yes' WHERE game_id={$game_id}"; $query = "UPDATE MountainFCgamereports2010 SET approved='Yes' WHERE game_id={$_GET['game_id']}"; Thanks for any help! Ed Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/ Share on other sites More sharing options...
PFMaBiSmAd Posted April 25, 2010 Share Posted April 25, 2010 Both of the examples you posted are correct, provided that the variables exist and contain a numeric value. What exactly is happening that leads you to believe that "nothing will work...." Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048022 Share on other sites More sharing options...
ed01 Posted April 25, 2010 Author Share Posted April 25, 2010 with using the 89, my table record 'approved' gets populated with "Yes" and the script continuous to run as desired. With using a variable as shown, the $query has game_id= <blank> and stops working. I came across some google references that a $query can't decipher the contents of a variable but didn't understand the solutions. So need some help please. The variables do exist and have a value (not certain that it's defined as numeric) because I use them early in the code to retreive the whole record. Up there I set $game_id = $_GET['game_id']; to capture the record from a URL. Thanks Ed Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048065 Share on other sites More sharing options...
cags Posted April 25, 2010 Share Posted April 25, 2010 As PFMaBiSmAd said your code is correct. I'm not sure what you've read about variables not being deciphered in queries, but that certainly doesn't apply to the code that you have there. What do you get if you echo out the value of $query directly after the line? You seem to be indicating that you get... UPDATE MountainFCgamereports2010 SET approved='Yes' WHERE game_id= If this is the case it indicates that $game_id does not have a value. Placing single quotes around the variable may help (assuming you don't get the above output), but again as PFMaBiSmAd mentioned, they shouldn't be required if the value is numeric. $query = "UPDATE MountainFCgamereports2010 SET approved='Yes' WHERE game_id='{$game_id}'"; Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048068 Share on other sites More sharing options...
PFMaBiSmAd Posted April 25, 2010 Share Posted April 25, 2010 To get the quickest solution to what your code is doing, please just post your code. You likely have an if($game_id = '') (which sets the variable to an empty string) rather than an if($game_id == '') (which tests if the variable is an empty string.) Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048074 Share on other sites More sharing options...
ed01 Posted April 25, 2010 Author Share Posted April 25, 2010 Thanks so much for looking at this!!! I've included my code - sorry it's so primitive, but it's not a complicated thing I'm trying to do. The code calls up the record by it's game_id thru GET in an email URL and includes username and password. It displays that record by echoing to the screen. Then the $query is replaces by an update query, again using the value for game id. The user clicks yes if they want to approve the game report and on submit causes the page to reload. If they clicked yes the UPDATE query is run and the dbase table is updated. These things are not happening that I would like: 1. The approved field is not being updated at all (except if I use 89 instead of the variable desired) 2. upon submit, the screen refreshes but the original echoed data disappears and only displays the radio and submit buttons. 3. upon clicking yes and submit I would like to have the submit button disappear and a simple note show like "successfully approved" Many thanks. Any further help is much appreciated. Ed HERE'S THE CODE: <?php // Turn on output buffering ob_start(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Mountain FC - Game Report 2010</title> <div id="content"> <?php // Connect and select. $dbc = @mysql_connect ('localhost', 'username', 'password') or die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>'); $x = @mysql_select_db ('dbase') or die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); // Turn off error display ini_set ('display_errors', 0); //Now to retreive the URL variable value: $game_id=$_GET['game_id']; $query = "SELECT * FROM MountainFCgamereports2010 WHERE game_id={$_GET['game_id']}"; $result=mysql_query($query); echo "<br />"; while($row=mysql_fetch_assoc($result)){ ?> <table width="99%" border="0" cellpadding="6" cellspacing="1"> <tr> <td style="background-color:#cc0033"><div align="center"><font color="#FFFFFF"><strong><? echo $row['age_level']; ?> GAME REPORTS</strong></font></div></td> </tr> </table> <? // Output echo "<b>",'Mountain FC ', $row['age_level'] , " vs ", $row['opposition']," ","-"," ",$row['game_month']," ", $row['game_day'],"</b>", "<br />"; echo "<b>",'Location : ',"</b>", $row['location'] , "<br />"; echo "<b>",'Weather : ',"</b>", $row['weather'] , "<br />"; echo "<b>",'Mountain FC - ', $row['mountainfcscore'] ," : ",$row['oppositionscore'] ," - ",$row['opposition'],"</b>", "<br />"; echo "<b>",'Half-time score : ', $row['halftimescore'], "</b>"," (mtn:oppos)","<br />"; echo $row['game_details'],"<br />"; if (!empty($row['scorer1'])) { if (!empty($row['scorer1'])) {echo "Goal #1 scored by: ",$row['scorer1']," ";}else {echo "";} if (!empty($row['assist1_1'])) {echo "Assisted by: ", $row['assist1_1']," ";}else {echo "";} if (!empty($row['assist1_2'])) {echo "and assisted by: ", $row['assist1_2']," ";}else {echo "";} echo "<br />"; } if (!empty($row['scorer2'])) { if (!empty($row['scorer2'])) {echo "Goal #2 scored by: ",$row['scorer2']," ";}else {echo "";} if (!empty($row['assist2_1'])) {echo "Assisted by: ", $row['assist2_1']," ";}else {echo "";} if (!empty($row['assist2_2'])) {echo "and assisted by: ", $row['assist2_2']," ";}else {echo "";} echo "<br />"; } if (!empty($row['scorer3'])) { if (!empty($row['scorer3'])) {echo "Goal #3 scored by: ",$row['scorer3']," ";}else {echo "";} if (!empty($row['assist3_1'])) {echo "Assisted by: ", $row['assist3_1']," ";}else {echo "";} if (!empty($row['assist3_2'])) {echo "and assisted by: ", $row['assist3_2']," ";}else {echo "";} echo "<br />"; } if (!empty($row['scorer4'])) { if (!empty($row['scorer4'])) {echo "Goal #4 scored by: ",$row['scorer4']," ";}else {echo "";} if (!empty($row['assist4_1'])) {echo "Assisted by: ", $row['assist4_1']," ";}else {echo "";} if (!empty($row['assist4_2'])) {echo "and assisted by: ", $row['assist4_2']," ";}else {echo "";} echo "<br />"; } if (!empty($row['scorer5'])) { if (!empty($row['scorer5'])) {echo "Goal #5 scored by: ",$row['scorer5']," ";}else {echo "";} if (!empty($row['assist5_1'])) {echo "Assisted by: ", $row['assist5_1']," ";}else {echo "";} if (!empty($row['assist5_2'])) {echo "and assisted by: ", $row['assist5_2']," ";}else {echo "";} echo "<br />"; } if (!empty($row['scorer6'])) { if (!empty($row['scorer6'])) {echo "Goal #6 scored by: ",$row['scorer6']," ";}else {echo "";} if (!empty($row['assist6_1'])) {echo "Assisted by: ", $row['assist6_1']," ";}else {echo "";} if (!empty($row['assist6_2'])) {echo "and assisted by: ", $row['assist6_2']," ";}else {echo "";} echo "<br />"; } if (!empty($row['scorer7'])) { if (!empty($row['scorer7'])) {echo "Goal #7 scored by: ",$row['scorer7']," ";}else {echo "";} if (!empty($row['assist7_1'])) {echo "Assisted by: ", $row['assist7_1']," ";}else {echo "";} if (!empty($row['assist7_2'])) {echo "and assisted by: ", $row['assist7_2']," ";}else {echo "";} echo "<br />"; } if (!empty($row['scorer8'])) { if (!empty($row['scorer8'])) {echo "Goal #8 scored by: ",$row['scorer8']," ";}else {echo "";} if (!empty($row['assist8_1'])) {echo "Assisted by: ", $row['assist8_1']," ";}else {echo "";} if (!empty($row['assist8_2'])) {echo "and assisted by: ", $row['assist8_2']," ";}else {echo "";} echo "<br />"; } if (!empty($row['scorer9'])) { if (!empty($row['scorer9'])) {echo "Goal #9 scored by: ",$row['scorer9']," ";}else {echo "";} if (!empty($row['assist9_1'])) {echo "Assisted by: ", $row['assist9_1']," ";}else {echo "";} if (!empty($row['assist9_2'])) {echo "and assisted by: ", $row['assist9_2']," ";}else {echo "";} echo "<br />"; } echo "<br />","Game id:",$row['game_id'],", Submitted by: ",$row['submittor'], " on ", $row['date_entered'],"<br />"; } //Reset query to nothing $query = ""; // Define an UPDATE query. $query = "UPDATE MountainFCgamereports2010 SET approved='Yes' WHERE game_id={$game_id}"; if ($radioset == "NO") { print "nothing was changed";// for debugging } elseif ($radioset == "YES") { print "radio was set to YES";// for debugging $result=mysql_query($query); } print '<form action="gamereports_APPROVAL.php" method="post">'; print '<input type="radio" name="radioset" value="YES" />Yes<br />'; print '<input type="radio" name="radioset" value="NO" checked="checked" />No<br />'; print '<input type="submit" name="submit" value="Approve" /></form>'; ?> </div> </html> <?php // Turn off output buffering ob_end_flush(); ?> Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048178 Share on other sites More sharing options...
PFMaBiSmAd Posted April 25, 2010 Share Posted April 25, 2010 The form at the bottom of that code is not setting $_GET['game_id'] so there is no value in it when the form is submitted. Web servers are stateless. They don't know or care what happened on any request before the current one or what will happen on any future request. All resources used on any page request are destroyed when the code on that page ends. This includes any php variables. You would need to add ?game_id=<?php echo $game_id; ?> onto the end of the URL in the action="...." attribute in order to cause the $game_id value to be passed when the form is submitted. Also, there is no code setting $radioset from $_POST['radioset']. This implies that register_globals are on. To update your code so that it will work regardless of the register_globals setting (which was turned off by default 8 years ago and have been completely removed in php6), add the following to your code - $radioset = $_POST['radioset']; Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048242 Share on other sites More sharing options...
ed01 Posted April 26, 2010 Author Share Posted April 26, 2010 Thanks! Below I show the portion of code in which I have added those two lines. Unfortunately I didn't seem to have any beneficial effect. What am I doing wrong ? Thanks ! // Define an UPDATE query. $query = "UPDATE MountainFCgamereports2010 SET approved='Yes' WHERE game_id={$game_id}"; $radioset = $_POST['radioset']; if ($radioset == "NO") { print "nothing was changed";// for debugging } elseif ($radioset == "YES") { print "radio was set to YES";// for debugging $result=mysql_query($query); } print '<form action="gamereports_APPROVAL.php?game_id=<?php echo $game_id; ?>" method="post">'; print '<input type="radio" name="radioset" value="YES" />Yes<br />'; print '<input type="radio" name="radioset" value="NO" checked="checked" />No<br />'; print '<input type="submit" name="submit" value="Approve" /></form>'; Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048301 Share on other sites More sharing options...
PFMaBiSmAd Posted April 26, 2010 Share Posted April 26, 2010 Actually, the <form... is part of a php print statement, so it would be necessary to concatenate the $game_id variable, instead of putting it in php tags with an echo of its' own. However, I just tried your full code posted in this thread and there is an $end error, so something is missing. Did you alter the code for the post? Edit: that error was due to the short open tag in your code. Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048304 Share on other sites More sharing options...
PFMaBiSmAd Posted April 26, 2010 Share Posted April 26, 2010 If you browse to that page with a ?game_id=123 on the end of the URL so that the SELECT query retrieves the correct data and click the Yes radio button and submit that form, the expected UPDATE query is formed and executed - UPDATE MountainFCgamereports2010 SET approved='Yes' WHERE game_id=123 Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048306 Share on other sites More sharing options...
ed01 Posted April 26, 2010 Author Share Posted April 26, 2010 It works!!! The concatenating of the print statement was the final piece of the puzzle. print '<form action="gamereports_APPROVAL.php?game_id= ' . $game_id . ' " method="post">'; Thanks soooooo much for all your help! I can now more forward with this slick game report approval method. Cheers - have a great week! Ed Quote Link to comment https://forums.phpfreaks.com/topic/199650-how-to-include-variable-in-sql-query-for-php/#findComment-1048353 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.