Using variable in update

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Using variable in update

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Using variable in update

Post 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})";
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Re: Using variable in update

Post 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
}

Code: Select all

<?php echo $TID?> 
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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Using variable in update

Post by requinix »

Use mysql_error() to see what the actual error message is.
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Re: Using variable in update

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Using variable in update

Post 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

Code: Select all

...tournament_number=)
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?
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Re: Using variable in update

Post 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>
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Using variable in update

Post 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?
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Re: Using variable in update

Post 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 :banghead:
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Re: Using variable in update

Post by Alpal »

Have been waiting for over a month for a response. Hope I have not offended anyone?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Using variable in update

Post 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

Code: Select all

if(isset($_POST["submit"])) {
If you echo $querytoexecute what is the value of $TID?
(#10850)
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Re: Using variable in update

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Using variable in update

Post 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
(#10850)
gummy123
Forum Newbie
Posts: 2
Joined: Tue Aug 16, 2016 4:47 am

Re: Using variable in update

Post by gummy123 »

Thank you very much
Post Reply