PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Fri Sep 22, 2017 1:16 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 14 posts ] 
Author Message
 Post subject: Using variable in update
PostPosted: Tue Jun 07, 2016 9:35 pm 
Offline
Forum Commoner

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

Syntax: [ Download ] [ Hide ]
// in the head of the page get the tournament ID

$TID = "-1";
if (isset($_GET['tournID'])) {
  $TID = $_GET['tournID'];
}
 
[
The problem is here
Syntax: [ Download ] [ Hide ]
// 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


Top
 Profile  
 
PostPosted: Tue Jun 07, 2016 9:43 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6551
Location: WA, USA
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.

Syntax: [ Download ] [ Hide ]
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
Syntax: [ Download ] [ Hide ]
$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})"
;


Top
 Profile  
 
PostPosted: Wed Jun 08, 2016 1:58 am 
Offline
Forum Commoner

Joined: Mon Jul 26, 2010 4:08 am
Posts: 39
Thank you but no success, am getting the error when I run the page

Syntax: [ Download ] [ Hide ]
$result=mysql_query($querytoexecute, $con) or die("<br><br><font face='arial' color='green'>Error tourn_id not set</font>");


Modified page

Syntax: [ Download ] [ Hide ]
if (isset($_GET['tournID']) && ctype_digit($_GET['tournID'])) {
  $TID = (int)$_GET['tournID'];
} else {
  // error and don't update
}



Syntax: [ Download ] [ Hide ]
<?php echo $TID?>

Echos the variable ok

Modified

Syntax: [ Download ] [ Hide ]
$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


Top
 Profile  
 
PostPosted: Wed Jun 08, 2016 2:12 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6551
Location: WA, USA
Use mysql_error() to see what the actual error message is.


Top
 Profile  
 
PostPosted: Wed Jun 08, 2016 10:42 am 
Offline
Forum Commoner

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

Syntax: [ Download ] [ Hide ]
// 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


Top
 Profile  
 
PostPosted: Wed Jun 08, 2016 5:19 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6551
Location: WA, USA
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:
...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?


Top
 Profile  
 
PostPosted: Wed Jun 08, 2016 9:07 pm 
Offline
Forum Commoner

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

Syntax: [ Download ] [ Hide ]
<?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>


Top
 Profile  
 
PostPosted: Thu Jun 09, 2016 12:14 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6551
Location: WA, USA
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?


Top
 Profile  
 
PostPosted: Thu Jun 09, 2016 9:54 am 
Offline
Forum Commoner

Joined: Mon Jul 26, 2010 4:08 am
Posts: 39
$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:


Top
 Profile  
 
PostPosted: Thu Aug 11, 2016 7:31 am 
Offline
Forum Commoner

Joined: Mon Jul 26, 2010 4:08 am
Posts: 39
Have been waiting for over a month for a response. Hope I have not offended anyone?


Top
 Profile  
 
PostPosted: Thu Aug 11, 2016 3:32 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13427
Location: New York, NY, US
I'm confused about this code that is first checking for a GET variable:
Syntax: [ Download ] [ Hide ]
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
Syntax: [ Download ] [ Hide ]
if(isset($_POST["submit"])) {

If you echo $querytoexecute what is the value of $TID?

_________________
(#10850)


Top
 Profile  
 
PostPosted: Sun Aug 14, 2016 1:28 am 
Offline
Forum Commoner

Joined: Mon Jul 26, 2010 4:08 am
Posts: 39
After much trial and error
Used the KISS principle (Keep it simple stupid!)

Syntax: [ Download ] [ Hide ]
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


Top
 Profile  
 
PostPosted: Sun Aug 14, 2016 10:29 am 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13427
Location: New York, NY, US
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.
Syntax: [ Download ] [ Hide ]
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)


Top
 Profile  
 
PostPosted: Tue Aug 16, 2016 4:55 am 
Offline
Forum Newbie

Joined: Tue Aug 16, 2016 4:47 am
Posts: 2
Thank you very much


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 14 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: Exabot [Bot] and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group