Using the Mysql UPDATE Statement in a loop

[ipc1234]

Hi everyone, I've been working on this bit of code for an ungodly amount of time, and I just cant seem to get it working.

 

    while ($i <= 22)
    {

      $query = "UPDATE people SET ".$people[$i]." vs ".$people[$i + 1]." = {$_POST[$people[$i]]} WHERE username = '$username'";
        mysql_query($query) or die('Error, insert query failed at button');     
      $i += 2;
    }

 

Basically I just want to loop 22 times, accessing the table named people, and writing the value of $_POST[person] into the rows "Person One vs Person Two". These Rows have already been created using the same values of the names in the array people[].

 

Thanks so much for your help...I'm fairly sure my problem is incorrect syntax of string/array/variables in that statement.

 

Ian

[JasonLewis]

Your loop won't be running 22 times because your adding 2 to i each time the loop runs through. Which means it is only running 11 times.

[chronister]

UPDATE people SET ".$people[$i]." vs ".$people[$i + 1]." = {$_POST[$people[$i]]} WHERE username = '$username'";

 

That don't look right. The proper syntax is

 

UPDATE table_name SET column_name = 'value' WHERE column_name2 = 'value2'

 

while ($i <= 22)
   {

     $query = "UPDATE people SET ".$people[$i]." vs ".$people[$i + 1]." = {$_POST[$people[$i]]} WHERE username = '$username'";
       #mysql_query($query) or die('Error, insert query failed at button');     
          echo $query.'<br>';
     $i += 2;
   }

 

Try adding the echo line so you can see what the actual query is your trying to run. I think your going to find that your query syntax is wrong. You can also add echo mysql_error() after the echo $query to see if your getting any sql errors.

 

And projectfear is right, your count incrementing is not correct.

[ipc1234]

Sorry about the mistake of saying that the loop will run 22 times, I do realize that the loop will only run 11 times.

 

I also realize that the syntax is:

UPDATE table_name SET column_name = 'value' WHERE column_name2 = 'value2'

 

 

And that's exactly what I'm trying to do in the code:

UPDATE people SET ".$people[$i]." vs ".$people[$i + 1]." = {$_POST[$people[$i]]} WHERE username = '$username'";

 

For instance, in the first run through the loop, let's say people[1] is John Doe, and people[2] is Robert Hill. The column name in the table "people" that I'm trying to submit the value to is "John Doe vs Robert Hill".

 

The value that I'm trying to submit to that column is held in the variable $_POST[$people[1]]...or John Doe (as this is the ID of the form I'm drawing the value from). Initially I had the ID of the form as "$people[1] vs $people[2]", but this proved to further complicate the coding, and I changed the ID of the form to simply $people[1].

 

I took your advice and added the echo $query and received the following results:

 

UPDATE banned SET vs = WHERE username = 'bobby' (11 times)

 

I realize that this means my variables are not being placed properly into the string that is $query, but I can't figure out the syntax to do so.

 

Any further help will be very very very much appreciated....THANKS!

 

Ian

[ipc1234]

After some more tinkering, I was able to get the following code:

 

 
$query = "UPDATE people SET ".$people[$i]." vs ".$people[$i + 1]." = {$_POST[$people[$i]]} WHERE username = '$username'";
#mysql_query($query) or die('Error, insert query failed at button');
echo $query.'<br>';

 

 

To return the following results:

UPDATE people SET John Doe vs Hank Hill = WHERE username = 'bobby'

UPDATE people SET Your Mom vs Bobby Flay = WHERE username = 'bobby'

UPDATE people SET Yes Sir vs Allan Ralph = WHERE username = 'bobby'

etc...

 

 

However, I am still unable to determine the syntax necessary to display the variable $_POST[$people[$i]] in the string that is query.

 

 

Thanks in advance

 

Ian

[chronister]

UPDATE people SET John Doe vs Hank Hill = WHERE username = 'bobby'

 

Again, back to the proper syntax... the above will try to set a column named "John Doe vs Hank Hill" to nothing...  = WHERE

 

It needs to read something like 

 UPDATE people SET matchParticipants = 'John Doe vs Hank Hill' WHERE username = 'bobby'

 

You need to specify the column name to update.

 

And as a tip, you REALLY should not use the raw $_POST data in the query... sanitize it first with either add_slashes() or mysql_real_escape_string()

 

What is the name of your field that the POST data is coming from??

 

$_POST[$people[$i]]   << this denotes that you have a variable field name... $_POST['fieldname'] is more of how it should read...

 

whatever $people[$i] is equal to is the name of the field that your script is looking for. It is probably safe to assume that whatever $people[$i] is equal to is NOT the name of your field.

 

Glad to see your trying though... more than I can say for another thread I am "trying" to help with.

 

Nate

 

 

 

 

[ipc1234]

Again, thanks so much for the help chronister, I really do appreciate it.

 

I think it would help if I posted my entire code, because I'm obviously not doing a good enough job of explaining the intent of my UPDATE loop. It's a little big, but maybe looking at the actual code will be more beneficial to you than me explaining what I'm trying to do.

 

<?php
  session_start();
  if (!isset($_SESSION['db_is_logged_in']) || $_SESSION['db_is_logged_in'] !== true)  //is the one accessing this page logged in or not?
  {
   header('Location: login.php');                                                   //not logged in, go back to login page
   exit;
  }
?>

<html>
<head>
  <title>You are a god</title>
  <script language="javascript">
    function ShowSubs(value, fight)                                    // the script for hiding and showing the submission type form
    {

      if (value ==                                                   // the value 8 corresponds with selecting the submission option
      {
        document.getElementById(fight).style.display = 'block';        // block means visible
      }
      else if (value != 
      {
        document.getElementById(fight).style.display = 'none';         // hides the form again if submission is unselected
      }

    }
  </script>
</head>
<body>
<?php
  $username = $_SESSION['Userid'];
  $fighters = array ("","Fedor Emelianenko","Tim Sylvia","Andrei Arlovski","Ben Rothwell","Josh Barnett","Pedro Rizzo","Matt Lindland","Fabio Negao","Renato Sobral",
                     "Mike Whitehead","Aleksander Emelianenko","Paul Buentello","Antonio Nogueira","Edwin Dewees","Vitor Belfort","Terry Martin","Ray Lizama",
                     "Justin Levens","Mike Pyle","JJ Ambrose","Savant Young","Mark Hominick");

  if(isset($_POST['bannedsubmit']))
  {
    include 'includes/config.php';
    include 'includes/connect.php';

    $i = 1;
    
    $query = "INSERT INTO banned (username, complete) VALUES ('$username', 1)";
    mysql_query($query) or die('Error, insert query failed at username');    	



    while ($i <= 22)
    {
      $temp = $_POST[".$fighters[$i]." vs ".$fighters[($i - 1)]"];
      $query = "UPDATE banned SET ".$fighters[$i]." vs ".$fighters[$i + 1]." = '$temp' WHERE username = '$username'";
        #mysql_query($query) or die('Error, insert query failed at button');
      echo $query.'<br>';
    }


  }

?>
  Time to make your picks <?php echo $_SESSION['Userid'];?>
  <br><br><br>
  <FORM METHOD = "post" NAME = "bannedpicks" ID = "bannedpicks">  <!--this is the form for all the selections for this event-->
<?php
  $i = 1;  //$i is our counter


  while ($i <= 22)  // the value 22 is how many fighters are on the card
  {
    ?><b> <?php print $fighters[$i];?></b><INPUT TYPE="radio"<?php                      // This discusting begining of the while loop prints out two fighters at a time
    if ($i & 1)                                                                         // and places a radio button next to each one. Because you can only choose one
    {                                                                                   // fighter to win one fight, each pair of radio buttons has the same "NAME"
      ?>NAME="<?php print $fighters[$i];?> vs <?php print $fighters[($i + 1)];?>"<?php  // this means that while each of the radio buttons has a independant "VALUE",
    }                                                                                   // each pair of buttons is independant of the other pairs, and you may only 
    else                                                                                // select one of the two. The "VALUE" of each button is the individual fighter's
    {                                                                                   // name (eg. 'Fedor Emelianenko', or 'Don Frye'). The "NAME" for each pair of 
      ?>NAME="<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?>"<?php  // radio buttons is 'Fighter1 vs Fighter2' (eg. 'Don Frye vs Vernon White').
    }
      ?>VALUE="<?php print $fighters[$i];?>"><br><?php 

    if (!($i & 1))          // if our counter is NOT an even number...this allows us to print our forms for each pair of fighter 
    {
      ?>
      Method of victory: 
      <sELECT NAME = '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> MOV' ID = '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> MOV' SIZE = "1" onChange = "ShowSubs(document.getElementById('<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> MOV').value, '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> Sub')">
      <OPTION VALUE = '0' SELECTED>Select a Method of Victory</OPTION>
      <OPTION VALUE = '1'>Disqualification</OPTION>
      <OPTION VALUE = '2'>Draw</OPTION>
      <OPTION VALUE = '3'>Knock Out</OPTION>
      <OPTION VALUE = '4'>Majority Decision</OPTION>
      <OPTION VALUE = '5'>Majority Draw</OPTION>
      <OPTION VALUE = '6'>No Contest</OPTION>
      <OPTION VALUE = '7'>Split Decision</OPTION>
      <OPTION VALUE = '8'>Submission</OPTION>
      <OPTION VALUE = '9'>Technical Knock Out</OPTION>
      <OPTION VALUE = '0'>Unanimous Decision</OPTION>
      </SELECT><br>

      <DIV ID ='<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> Sub' STYLE = "display: none;">
        Submission Type: <SELECT ID = '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> Sub MOV' ID = '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> Sub MOV' SIZE = "1">
                         <OPTION VALUE="0" SELECTED>Select a Submission Type</OPTION>
                         <OPTION VALUE "1">Arm Lock</OPTION>
                         <OPTION VALUE "2">Leg Lock</OPTION>
                         <OPTION VALUE "3">Choke</OPTION>
                         <OPTION VALUE "4">Other</OPTION>
                         </SELECT><br>
      </DIV>

      Winning Round: <SELECT NAME = '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> Round' ID = '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> Round' SIZE = "1">
                     <OPTION VALUE="0" SELECTED>Select a Round</OPTION>
                     <OPTION VALUE="1">Round 1</OPTION>
                     <OPTION VALUE="2">Round 2</OPTION>
                     <OPTION VALUE="3">Round 3</OPTION>
                     </SELECT><br>

      Winning Minute: <SELECT NAME = '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> Minute' ID = '<?php print $fighters[($i - 1)];?> vs <?php print $fighters[$i];?> Minute' SIZE = "1">
                      <OPTION VALUE="-1">Select a Minute</OPTION>
                      <OPTION VALUE="0">0 (0:00 - 0:59)</OPTION>
                      <OPTION VALUE="1">1 (1:00 - 1:59)</OPTION>
                      <OPTION VALUE="2">2 (2:00 - 2:59)</OPTION>	  
                      <OPTION VALUE="3">3 (3:00 - 3:59)</OPTION>
                      <OPTION VALUE="4">4 (4:00 - 4:59)</OPTION>
                      <OPTION VALUE="5">5 (5:00)</OPTION>
                      </SELECT><br><br><br><?php
    }

  $i += 1;
  }
?>
  <INPUT TYPE = "submit" ID = "bannedsubmit" NAME = "bannedsubmit" Value = "Sumbit">
  </FORM>
<a href="logout.php">Logout</a>

</body>
</html>

 

So that's it in its entirety...As you can see, that code only tries to submit the value from the radio buttons, as I'll be able to submit the rest of the form data once I have the correct syntax for the radio buttons.

 

 

Thanks,

 

Ian

[chronister]

I took your code down to the minimum and fixed some syntax errors.

 

<html>
<head>
  <title>You are a god</title>
</head>
<body>
<?php
  $fighters = array ("","Fedor Emelianenko","Tim Sylvia","Andrei Arlovski","Ben Rothwell","Josh Barnett","Pedro Rizzo","Matt Lindland","Fabio Negao","Renato Sobral",
                     "Mike Whitehead","Aleksander Emelianenko","Paul Buentello","Antonio Nogueira","Edwin Dewees","Vitor Belfort","Terry Martin","Ray Lizama",
                     "Justin Levens","Mike Pyle","JJ Ambrose","Savant Young","Mark Hominick");

    while ($i <= 22)
    {
      $temp = $_POST[$fighters[$i].' vs '.$fighters[($i - 1)]];
      $query = "UPDATE banned SET ".$fighters[$i]." vs ".$fighters[$i + 1]." = '$temp' WHERE username = '$username'";
        #mysql_query($query) or die('Error, insert query failed at button');
      echo $query.'<br>';
  $i++;
    }
?>

</body>
</html>

 

When I run this, I get the following as one of the lines....

 

UPDATE banned SET Antonio Nogueira vs Edwin Dewees = '' WHERE username = ''

 

 

This is saying update column named Antonio Nogueira vs Edwin Dewees to nothing WHERE username = '

 

Is it safe to assume that you DO NOT have a column named  Antonio Nogueira vs Edwin Dewees??

 

To truly see what is going wrong, run your code on your side of things and then take the results of the query echo and plug that into PhpMyadmin. Then post the error you get from that.

 

Nate

[ipc1234]

I do have a column named Antonio Nogueira vs Edwin Dewees in my table...However I don't have PHPMyadmin....

 

 

The reason it's trying to set the column to nothing is because the syntax for this statement:

 

    while ($i <= 22)
    {
      $temp = $_POST[$fighters[$i].' vs '.$fighters[($i - 1)]];
      $query = "UPDATE banned SET ".$fighters[$i]." vs ".$fighters[$i + 1]." = '$temp' WHERE username = '$username'";
        #mysql_query($query) or die('Error, insert query failed at button');
      echo $query.'<br>';
  $i+=2;
    }

 

Is incorrect...and I'm not sure why

 

It SHOULD be outputing "UPDATE banned set Antonio Nogueira vs Edwin Dewees = 'Antonio Nogueria' WHERE username = 'ipc1234';

 

That is what I'm trying to achieve....Where "Antonio Nogueria vs Edwin Dewees" is the name of the column and "Antonio Nogueria" is the posted result of the form named "Antonio Nogueria vs Edwin Dewees".

 

Ian

[chronister]

Oh... ok... that is a weird column name

 

Seems like the $temp var is not being populated correctly.

 

echo $temp and post what you get.

 

You got some strange coding going on there It was throwing me off big time.

 

Seems like your trying to set your column to be equal to $temp, and temp is equal to $_POSTdata vs fighter.

 

So I think it may be in the $temp var as to where it is going wrong.