SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2009
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Database not updated with query in PHP

    Howdy! I know I'm missing something rather simple here but I just can't seem to find it. I need another set of eyes. The data is being passed from the form to the php page that captures the data into variables. I can echo out the variables with the data but my query will not update the data table, even though my code indicates that the database was updated.
    Here's the form.
    Code:
          <fieldset>
                <legend>Enter New Term</legend>
    <form name  ="input" action="insert.php" method="post">
        <label for="newterm" class="label">New Term</label>
        <input type="text" name="newterm" id="newterm" value=""/>
        <label for="definition" class="label">Definition 1</label>
        <input type="text" name="definition1" id="definition1" value=""/>
        <label for="definition2" class="label">Definition 2</label>
        <input type="text" name="definition2" id="definition2" value=""/>
        <label for="reference1" class="label">Reference Key 1</label>
        <input type="text" name="reference1" id="reference1" value=""/>
        <label for="reference2" class="label">Reference 2</label>
        <input type="text" name="reference2" id="reference2" value=""/>
        <label for="ilink1" class="label">Reference Link 1</label>
        <input type="text" name="ilink1" id="ilink1" value=""/>
        <label for="ilink2" class="label">Reference Link 2</label>
        <input type="text" name="ilink2" id="ilink2" value=""/>
        <label for="comment" class="label">Comment</label>
        <input type="text" name="comment" id="comment" value=""/>
        <input type="submit" name="submit" id="submit" value="Add Term" />
    </form>
    </fieldset>
    Here's the update file that captures the form data and sends to the database.
    Code:
    include 'conn.php';
    $new_term = $_POST['newterm'];
    $def1 = $_POST['definition1'];
    $def2 = $_POST['definition2'];
    $ref1 = $_POST['reference1'];
    $ref2 = $_POST['reference2'];
    $link1 = $_POST['ilink1'];
    $link2 = $_POST['ilink2'];
    $cmmnt = $_POST['comment'];
    echo $new_term;
    echo $def1;
    echo $def2;
    echo $ref1;
    echo $ref2;
    echo $link1;
    echo $link2;
    echo $cmmnt;
    
    $query = "INSERT INTO terms (id, keyword, def1, def2, kref1, kref2, iref1, iref2, comment, entrydate)
    values(".$new_term."','".$def1."','".$def2."','".$ref1."','".$ref2."','".$link1."','".$link2."','".$cmmnt."')";
    
    if(!$query) {
        die('This data is not valid and will not be saved.');
    }
    echo 'Your data has been validated and has been committed to the database';
    Here is the structure of the table
    Code:
    CREATE TABLE terms(
              id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
              keyword VARCHAR(35)NOT NULL UNIQUE,
    def1 VARCHAR(255) NOT NULL,
    def2 VARCHAR(255) NULL,
    kref1 VARCHAR(55) NULL,
    kref2 VARCHAR(55) NULL,
    iref1 VARCHAR(255) NULL,
    iref2 VARCHAR(255) NULL,
    comment VARCHAR(255) NULL,
    entrydate DATETIME
    )Engine = InnoDB DEFAULT CHARSET=utf8
    Thanks in advance.

  2. #2
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try putting quotes around the fields: e.g. terms('id', 'keyword')... etc.
    Have a good day.

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2009
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Darren but that's a no go as well. I've tested the db connect and it's good. Very odd.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    The quoting of the values is incorrect

    "INSERT INTO terms (id, keyword, def1, def2, kref1, kref2, iref1, iref2, comment, entrydate)

    values(

    '".$new_term."',
    '".$def1."',
    '".$def2."',
    '".$ref1."',
    '".$ref2."',
    '".$link1."',
    '".$link2."',
    '".$cmmnt."')";

    The single quote before ".$new_term (shown in red) is not in your code, but it should be.

    Also, take a look at mysql_real_escape_string() to prevent SQL Injection.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2009
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I removed the quotes from the values and added the single quote but there is still not data being committed to the database. No errors.
    Thanks for the links. I'll be looking at those issues once I make sure my database is gathering the data.

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2009
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I use this query in PHPadmin a record is stored in the database.

    Code:
    INSERT INTO terms (keyword, def1, def2, kref1, kref2, iref1, iref2, comment)
    values('PHP','Scripting Language','Loosely typed scripting language','Web apps','Dynamic','http://www.php.net','http://www.php.net','This is a comment')
    The entrydate field is NULL however. I wonder if there is an issue that I don't see there. Does the date have to be formatted?

  7. #7
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Make sure you have not misspelled your fields either, and what type is the entrydate field?
    Have a good day.

  8. #8
    SitePoint Zealot
    Join Date
    Sep 2009
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All the field spelling is good. The entry data is a DATETIME field. I think that may be the issue. It's not set to NULL or NOT NULL or formatted in any way.
    thanks

  9. #9
    SitePoint Zealot
    Join Date
    Sep 2009
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just as I suspected. A simple fix it was.
    I forgot the mysql_query before the insert statement. WOW. I feel relieved and like and idiot at the same time.
    Thanks for the input.
    FIX:
    Code:
    $query = mysql_query("INSERT INTO terms (keyword, def1, def2, kref1, kref2, iref1, iref2, comment)values('".$new_term."','".$def1."','".$def2."','".$ref1."','".$ref2."','".$link1."','".$link2."','".$cmmnt."')");

  10. #10
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dont worry it happens to all of us
    Have a good day.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •