how to include variable in SQL query for php

ed01 · 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

PFMaBiSmAd · 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...."

ed01 · 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

cags · 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}'";

PFMaBiSmAd · 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.)

ed01 · 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">

<head>

<title>Mountain FC - Game Report 2010</title>

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

?>

<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();

?>

PFMaBiSmAd · 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'];

ed01 · 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>';

PFMaBiSmAd · 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.

PFMaBiSmAd · 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

ed01 · 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

Sign In

how to include variable in SQL query for php

Recommended Posts

ed01

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

ed01

Link to comment

Share on other sites

cags

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

ed01

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

ed01

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

ed01

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information