Page 1 of 1
Using variable in update
Posted: Tue Jun 07, 2016 9:35 pm
by Alpal
Have a page that runs several queries and creates a temp table, copies some information to another table, then drops the temp table.
Everything works until I try and use a variable in an update statement. So ....
Variable is stored in a session
Code: Select all
// in the head of the page get the tournament ID
$TID = "-1";
if (isset($_GET['tournID'])) {
$TID = $_GET['tournID'];
}
[
The problem is here
Code: Select all
// Add the tourn id to the appropriate players
$querytoexecute = "UPDATE `mydatabase`.`tourn_entry_set_rank_temp`
SET `tourn_id` = (SELECT tourn_entry.tournament_number
FROM tourn_entry
WHERE tourn_entry.tourn_memb_id=tourn_entry_set_rank_temp.memb_id AND tourn_entry.tournament_number=62)";
$result=mysql_query($querytoexecute, $con) or die("<br><br><font face='arial' color='green'>Error tourn_id not set</font>");
if (isset($result)) echo "<br><br><font face='arial' color='green'>Successfully set the tourn_id for players as per the passed variable </font>";
Need to include the variable here
AND tourn_entry.tournament_number=62)
so it would look something like this
AND tourn_entry.tournament_number='$TID')
Any assistance will be greatly appreciated
Re: Using variable in update
Posted: Tue Jun 07, 2016 9:43 pm
by requinix
It's a number so you should use it like a number. That means no quotes and no strings. You also need to validate that it's a number before using it.
And speaking of using, don't use -1 as a default value because it doesn't make sense to use a default value here: either you do the update or you don't do the update.
Code: Select all
if (isset($_GET['tournID']) && ctype_digit($_GET['tournID'])) {
$TID = (int)$_GET['tournID'];
} else {
// error and don't update
}
Then it's a simple matter of
Code: Select all
$querytoexecute = "UPDATE `mydatabase`.`tourn_entry_set_rank_temp`
SET `tourn_id` = (SELECT tourn_entry.tournament_number
FROM tourn_entry
WHERE tourn_entry.tourn_memb_id=tourn_entry_set_rank_temp.memb_id AND tourn_entry.tournament_number={$TID})";
Re: Using variable in update
Posted: Wed Jun 08, 2016 1:58 am
by Alpal
Thank you but no success, am getting the error when I run the page
Code: Select all
$result=mysql_query($querytoexecute, $con) or die("<br><br><font face='arial' color='green'>Error tourn_id not set</font>");
Modified page
Code: Select all
if (isset($_GET['tournID']) && ctype_digit($_GET['tournID'])) {
$TID = (int)$_GET['tournID'];
} else {
// error and don't update
}
Echos the variable ok
Modified
Code: Select all
$querytoexecute = "UPDATE `mydatabase`.`tourn_entry_set_rank_temp`
SET `tourn_id` = (SELECT tourn_entry.tournament_number
FROM tourn_entry
WHERE tourn_entry.tourn_memb_id=tourn_entry_set_rank_temp.memb_id AND tourn_entry.tournament_number={$TID})";
unsure as what to do next. The page runs correctly if I set {$TID} as 62
Re: Using variable in update
Posted: Wed Jun 08, 2016 2:12 am
by requinix
Use mysql_error() to see what the actual error message is.
Re: Using variable in update
Posted: Wed Jun 08, 2016 10:42 am
by Alpal
Thank you
the error message
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 ')' at line 4
Code: Select all
// Add the tourn id to the appropriate players
$querytoexecute = "UPDATE `mydatabase`.`tourn_entry_set_rank_temp`
SET `tourn_id` = (SELECT tourn_entry.tournament_number
FROM tourn_entry
WHERE tourn_entry.tourn_memb_id=tourn_entry_set_rank_temp.memb_id AND tourn_entry.tournament_number={$TID})";
$result=mysql_query($querytoexecute, $con) or die(mysql_error());
if (isset($result)) echo "<br><br><font face='arial' color='green'>Successfully set the tourn_id for players as per the passed variable </font>";
Cannot see where the syntax error is. If I run the query with - AND tourn_entry.tournament_number=62) it runs correctly
Re: Using variable in update
Posted: Wed Jun 08, 2016 5:19 pm
by requinix
The only ) is at the end of the query. The only way for that part to be invalid would be if $TID is empty and the query says
Obviously you have code that sets
a $TID somewhere. Where is that located? Does it execute before the query is run? Does it execute in the same scope as the code that runs the query?
What is all the code involved, in one piece?
Re: Using variable in update
Posted: Wed Jun 08, 2016 9:07 pm
by Alpal
Here is the entire page. There are more queries to go into the page when i can get past this query, they will use the same variable $TID. No point in including them until i can get past this step.
Have stripped the page out to its most basic form. Tested it and it still breaks down at the same point.
The page title UpdateSeed.php
Code: Select all
<?php require_once('../Connections/conn.php'); ?>
<?php
if (!isset($_SESSION)) {
session_start();
}
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
if (isset($_GET['tournID']) && ctype_digit($_GET['tournID'])) {
$TID = (int)$_GET['tournID'];
} else {
// error and don't update
}
?>
<!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>
</head>
<body>
<center>
<?php require_once('../Connections/conn.php'); ?>
<?php
if(isset($_POST["submit"]))
{
mysql_select_db($database_conn, $conn);
//The object of this page is to seed a tournament using the current Tournament Rankings
//It creates a temporary table tourn_entry_set_rank_temp from tourn_RP_co_curr the table is then altered so only entries that
//have been entered into the "Tournament ID" are included, their seeding is set, then copied back to the tourn_entries table
//On completion the temporary table is dropped
// create a temporary table tourn_entry_set_rank_temp from tourn_RP_co_curr
$querytoexecute = "CREATE TABLE `mydatabase`.`tourn_entry_set_rank_temp` SELECT * FROM `mydatabase`.`tourn_RP_co_curr`";
$result=mysql_query($querytoexecute, $conn) or die("<br><br><font face='arial' color='green'>Error temp table not created</font>");
if (isset($result)) echo "<br><br><font face='arial' color='green'>tourn_entry_set_rank_temp table was created and data copied successfully from the tourn_RP_co_curr table</font>";
// Alter copied table by dropping unwanted columns
$querytoexecute = "ALTER TABLE `mydatabase`.`tourn_entry_set_rank_temp`
DROP COLUMN total_curr_rp,
DROP COLUMN last_update;";
$result=mysql_query($querytoexecute, $conn) or die("<br><br><font face='arial' color='green'>Error unwanted columns not dropped </font>");
if (isset($result)) echo "<br><br><font face='arial' color='green'>Succcessfully dropped unwanted columns total_rp_curr and last_update</font>";
// Add the tourn id column
$querytoexecute = "ALTER TABLE `tourn_entry_set_rank_temp` ADD `tourn_id` INT( 10 ) NOT NULL DEFAULT '0';";
$result=mysql_query($querytoexecute, $conn) or die("<br><br><font face='arial' color='green'>Error tourn_id column not added</font>");
if (isset($result)) echo "<br><br><font face='arial' color='green'>Successfully added tourn_id column </font>";
// Add the tourn id to the appropriate players
// This is where the error occurs
//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 ')' at line 4
$querytoexecute = "UPDATE `mydatabase`.`tourn_entry_set_rank_temp`
SET `tourn_id` = (SELECT tourn_entry.tournament_number
FROM tourn_entry
WHERE tourn_entry.tourn_memb_id=tourn_entry_set_rank_temp.memb_id AND tourn_entry.tournament_number={$TID})";
$result=mysql_query($querytoexecute, $conn) or die(mysql_error());
if (isset($result)) echo "<br><br><font face='arial' color='green'>Successfully set the tourn_id for players as per the passed variable </font>";
mysql_close ($conn);
}
else
{
echo "<form id='form1' name='form1' method='post' action='UpdateSeed.php?submit=1'>";
echo "<input type='submit' id='submit' name='submit'>";
echo "</form>";
}
?>
</center>
</body>
</html>
Re: Using variable in update
Posted: Thu Jun 09, 2016 12:14 am
by requinix
Okay, well, your code is trying to do the update regardless of whether TID can be set properly. Fix that by making it not update if the second half of that one if block executes. Do that and you'll probably see that the TID value being passed to the script is bad.
What is the code for the form or link that has the TID and goes to this script?
Re: Using variable in update
Posted: Thu Jun 09, 2016 9:54 am
by Alpal
$TID is passed from the previous page via
<a href="../UpdateSeed.php?tournID=<?php echo $row_players1['tournament_number']; ?>">
Have checked that the variable is reaching the page correctly
<?php echo $TID?> = 62 tested with various other tournament ID numbers and they all echo correctly
Not sure what you meant by "Fix that by making it not update" ?
Obviously I am no genius with mysql or php, building this page is about as difficult as it gets for me.
Apologies for the time it takes for me to reply, I am in Victoria, Australia, so the time zone is almost opposite yours.
Appreciate your assistance, did not think this would be that difficult. Have searched without success for many hours before I made this post

Re: Using variable in update
Posted: Thu Aug 11, 2016 7:31 am
by Alpal
Have been waiting for over a month for a response. Hope I have not offended anyone?
Re: Using variable in update
Posted: Thu Aug 11, 2016 3:32 pm
by Christopher
I'm confused about this code that is first checking for a GET variable:
Code: Select all
if (isset($_GET['tournID']) && ctype_digit($_GET['tournID'])) {
$TID = (int)$_GET['tournID'];
} else {
// error and don't update
}
And then this code that is checking for a form submission and a POST variable
If you echo $querytoexecute what is the value of $TID?
Re: Using variable in update
Posted: Sun Aug 14, 2016 1:28 am
by Alpal
After much trial and error
Used the KISS principle (Keep it simple stupid!)
Code: Select all
if (isset($_GET['tournID'])) {
$TID = $_GET['tournID'];
}
$querytoexecute = "UPDATE `tourn_entry_set_rank_temp`
SET `tourn_id` = (SELECT tourn_entry.tournament_number FROM tourn_entry
WHERE tourn_entry.tourn_memb_id=tourn_entry_set_rank_temp.memb_id AND tourn_entry.tournament_number='$TID')";
$result=mysql_query($querytoexecute, $conn or die(mysql_error());
The page is now working.
Can only assume that I had the variable ($TID) in the head of the page.
When the page was submitted it was showing the query as executed but was not updating the table.
Once I brought the variable inside the if statement everything works fine?
Thank you all for your assistance.
Please mark this thread as solved
Re: Using variable in update
Posted: Sun Aug 14, 2016 10:29 am
by Christopher
Instead of just die(), I would recommend a little structured programming to deal with errors and let user know what went wrong in a nicer way. You always want to display a page -- even if there is an error. Don't just print some die() text back to the browser.
Code: Select all
if (isset($_GET['tournID'])) {
$TID = (int)$_GET['tournID']; // convert to int to avoid SQL injection
$querytoexecute = "UPDATE `tourn_entry_set_rank_temp`
SET `tourn_id` = (SELECT tourn_entry.tournament_number FROM tourn_entry
WHERE tourn_entry.tourn_memb_id=tourn_entry_set_rank_temp.memb_id AND tourn_entry.tournament_number='$TID')";
$result=mysql_query($querytoexecute, $conn);
if (!mysql_error()) {
// generate page content
} else {
// deal with error or tell the user what when wrong
}
} else {
// deal with error or tell the user what when wrong
}
// display page with content or error message
Re: Using variable in update
Posted: Tue Aug 16, 2016 4:55 am
by gummy123
Thank you very much