insert into TBL (column) where x (values)

[MSUK1]

Hello

 

I have created a little note system for an admin to be able to add notes for a specific user.

 

I'll attach each bit of code as it follows, im not getting any errors but at the same time im not getting the desired output?

 

My DB structure is as follows:

 

Columns: memberid | week1 ... week52

 

(im not sure that is the best way to do it but please correct me where neccessery)

 

The Form: (have removed a lot of the options just because they aren't necessary you'll get the picture)

              <a name="form"></a><form action="system/process.php" method="post">                  
                <p>
                    Member to add notes for:
                  <input type="text" disabled="true" id="membername" placeholder="<?php echo $_GET["name"]; ?>" value="<?php echo $_GET["name"]; ?>" name="membername" class="{validate:{required:true, minlength:3}}" />
                  </p>
                  
                  <p>
                   Please select the week to add notes for:
                    <select name="week" id="week" placeholder="week" class="{validate:{required:true}}">
                      <option selected="true">Please select or search for a week</option>
                      <option value="week1">Week 1</option>
                      <option value="week2">Week 2</option>
                      <option value="week3">Week 3</option>
                    </select>
                  </p>

                <p>
                   Please add the notes below:
                  <textarea id="note" name="note" class="{validate:{required:true}}">This is a textarea</textarea>
                </p>
                  
                  <input type="hidden" value="<?php echo $_GET["email"]; ?>" name="id" id="id" />
	  <input type="hidden" name="newnote" value="1" /> <!-- THIS IS USED TO TRIGGER THE FUNCTION -->

            
            <div class="action_bar">
          <input type="submit" class="button blue small" value="Submit" id="submit" name="submit" />
              <a href="members" class="button red small">Close window</a>
            </div>
            </form>

 

process.php (again i have removed functions you do not need to see as they do no interact at all with this function)

      /* Add new Member Note */
      else if(isset($_POST['newnote'])){
         $this->procNewNote();
      }

   /**
    * procNewNote
    **/
   function procNewNote(){
      global $session, $form;
      /* Registration attempt */
      $retval = $session->newnote($_POST['id'], $_POST['week'], $_POST['note']);
      
      /* Registration Successful */
      if($retval == 0){
         $_SESSION['regsuccess'] = true;
         header("Location: ".$session->referrer);
      }
      /* Error found with form */
      else if($retval == 1){
         $_SESSION['value_array'] = $_POST;
         $_SESSION['error_array'] = $form->getErrorArray();
         header("Location: ".$session->referrer);
      }
      /* Registration attempt failed */
      else if($retval == 2){
         header("Location: ".$session->referrer);
      }
   }

 

session.php

   /**
    * register new note - register a new note
    */
   function newnote($subid, $subweek, $subnote){
      global $database, $form, $mailer;  //The database, form and mailer object
      
      /* ID error checking */
      $field = "id";  //Use field name for first name
      if(!$subid || strlen($subid = trim($subid)) == 0){
         $form->setError($field, "* Fatal error occured");
      }
      else{
      }

      /* Week error checking */
      $field = "week";  //Use field name for first name
      if(!$subweek || strlen($subweek = trim($subweek)) == 0){
         $form->setError($field, "* Please select a week");
      }
      else{
      }

      /* Note error checking */
      $field = "note";  //Use field name for first name
      if(!$subnote || strlen($subnote = trim($subnote)) == 0){
         $form->setError($field, "* Please enter your notes");
      }
      else{
      }

      /* Errors exist, have user correct them */
      if($form->num_errors > 0){
         return 1;  //Errors with form
      }
      /* No errors, add the new account to the */
      else{
         if($database->addNewNote($subid, $subweek, $subnote)){
            return 0;  //New user added succesfully
         }else{
            return 2;  //Registration attempt failed
         }
      }
   }

 

database.php

   /**
    * addNewNote - Inserts the given to member note database
    */
   function addNewNote($id, $week, $note){
      $q = "INSERT INTO ".TBL_MEMBER_NOTES." ('$week')
  WHERE memberid = '$id'
  VALUES ('$note')";
      return mysql_query($q, $this->connection);
   }

 

TBL_MEMBER_NOTES is define in constant.php file

define("TBL_MEMBER_NOTES", "member_notes");

 

What i hope to happen

 

im hoping this system would insert into the table a new row for a user, if it doesn't exist and fill in the specified column (weekX) for the week selected in the field

 

if the member note row already exists im hoping for it to insert into the specified column and just update it

 

all help is appreciated

[Pikachu2000]

INSERT queries don't accept WHERE clauses. Are you sure you aren't wanting an UPDATE query?

[MSUK1]

i was just looking at phpMyadmin and trying to see what it runs when i manually update the row.

 

if i use update for this, how would i write a bit of code to decipher if a row exists for that memberid?

 

if memberid exists ->Update OR if it doesnt Insert

 

(im guessing thats the right method?)

[Barand]

if x is a unique key

 

INSERT INTO tablename (x,y,z)

VALUES ($x, $y, $z)

ON DUPLICATE KEY UPDATE y = $y, z = $z

[MSUK1]

would this be the correct string then?

 

   /**
    * addNewNote - Inserts the given to member note database
    */
   function addNewNote($id, $week, $note){
      $q = "INSERT INTO ".TBL_MEMBER_NOTES." (memberid, '$week') VALUES ('$id', '$note') ON DUPLICATE KEY UPDATE memberid = '$id', '$week' = '$note'";
      return mysql_query($q, $this->connection);
   }

 

thank you

[Barand]

column names should not be in single quotes ('$week').

 

Is memberid unique? If it is, it shouldn't be one of the columns you update. If it isn't then no need for the ON DUPLICATE bit.

[MSUK1]

so its not like values where i put '$variable' around a variable?

 

memberid is unique, it is their email address.

 

At first i tried setting this as a primary key but this limited it to 8 chars, so i set it as a unique key

 

ok let me try again

 

   /**
    * addNewNote - Inserts the given to member note database
    */
   function addNewNote($id, $week, $note){
      $q = "INSERT INTO ".TBL_MEMBER_NOTES." ($week) VALUES ('$id', '$note') ON DUPLICATE KEY UPDATE memberid = '$id', '$week' = '$note'";
      return mysql_query($q, $this->connection);
   }
   

 

still not updating :/

[Barand]

Why is the second field name a variable ie $week?

 

if you have 2 VALUES you 2 fields to update.

 

$q = "INSERT INTO ".TBL_MEMBER_NOTES." (memberid, $week) VALUES ('$id', '$note') ON DUPLICATE KEY UPDATE $week = '$note'";

 

[MSUK1]

Why is the second field name a variable ie $week?

 

if you have 2 VALUES you 2 fields to update.

 

$q = "INSERT INTO ".TBL_MEMBER_NOTES." (memberid, $week) VALUES ('$id', '$note') ON DUPLICATE KEY UPDATE $week = '$note'";

 

 

Sorry, i got confused, thank you let me try this

 

really appreciate it

[MSUK1]

this works perfectly! thank you for all your help,