Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2012
    Posts
    16

    Question Unanswered: Using a php-generated drop-down with mysql insert

    I'm trying to run a mysql insert query with a php dropdown menu that I generated as the input. The menu is functioning well. In the form I also have a hidden field that holds the primary key, a submit button, and the hidden field with the insert function. Here's the code:

    Code:
    <form id="Strength" name="Strength" method="post" action="">
                                    <div align="center">
                                        <input name="Character_ID" type="hidden" id="Character_ID" value="<?php echo $row_rs_Name['Character_ID']; ?>" />
                                        <?php
                                          echo "<select name='Strength_Pts2' id='Strength_Pts2'>";
                                            if ($row_rs_AttributeAssignment['CasteStrength'] == 'Yes' OR $row_rs_AttributeAssignment['ProfStrength'] == 'Yes') {
                                              foreach($PotRange as $AttributesAvailable) {
                                                echo "<option value='$AttributesAvialabe'>$AttributesAvailable</option>";
                                              }
                                            } else {
                                              echo "<option value='0'>0</option>";
                                            }
                                          echo "</select>";
                                        ?>
                                        <input type="submit" name="button6" id="button6" value="update" />
                                        <input type="hidden" name="MM_insert" value="Strength" />
    Here's the code for the insert behavior:

    Code:
    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Strength")) {
      $insertSQL = sprintf("INSERT INTO GA_AttributePoints (Character_ID, Strength_Points) VALUES (%s, %s)",
                           GetSQLValueString($_POST['Character_ID'], "int"),
                           GetSQLValueString($_POST['Strength_Pts2'], "int"));
    
      mysql_select_db($database_iotSMF, $iotSMF);
      $Result1 = mysql_query($insertSQL, $iotSMF) or die(mysql_error());
    }
    When I select a value from the menu and hit the button to run the query, the page returns:

    Column 'Strength_Points' cannot be null

    Anybody have any ideas about what I'm doing wrong? I've been messing around with this all day and I'm stumped.

    Thanks in advance!

    -CB

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    $s not %s?

    Also, where is this set?
    Can you echo out your insert statement and share the result with us please?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2012
    Posts
    16
    $s instead of %s generates a MySQL syntax error. I had to custom-build the dropdown menu I'm using here and altered the insert query that dreamweaver auto-generated for another insert to generate the code. A dangerous game, perhaps.

    Not sure how to answer your question about where the set is, do you mean where is the database?

    Also, not sure what you mean by echo out the insert statement; sorry for not knowing what the heck I'm doing.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're right about the %s; I missed that it was a replace style function being used.

    After $insertSQL is assigned a value, use echo to display it on your webpage
    Code:
      $insertSQL = sprintf("INSERT INTO GA_AttributePoints (Character_ID, Strength_Points) VALUES (%s, %s)",
                           GetSQLValueString($_POST['Character_ID'], "int"),
                           GetSQLValueString($_POST['Strength_Pts2'], "int"));
    
    echo $insertSQL;
    We are interested in finding out exactly what SQL command is being run here.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2012
    Posts
    16
    Great idea about having that print. Here's what it came back with:

    Code:
    INSERT INTO GA_AttributePoints (Character_ID, Strength_Points) VALUES (14, NULL)Column 'Strength_Points' cannot be null
    That means for whatever reason its not picking up the value from the menu I created. thoughts?

    Thanks so far!

    -CB

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not neccesarily...
    the error message is saying you have not allowed NULL as a value for the column Strength_Points
    so the problem is either you need to enable NULLS for Strength_Points OR work out why you tried to set a value of NULL
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2012
    Posts
    16
    The column is set to default to 0 in the database if a field is left blank upon insert (that's actually a pretty important function in this case). I'm trying to insert the value from the dropdown menu into that column, but it is picking up NULL (which obviously isn't an option in the menu), so that led me to believe the value from the dropdown wasn't being picked up.

    It is strange that it isn't just converting the NULL to 0; I guess I'm just totally confused

    Thanks for the help so far!

    -CB

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you have set a default value of 0 AND NOT NULL then you either need to send a numeric value OR not send a value for that column at all.

    Personally I'd suggest you put some validation code between the form and the script that sets the values. you should always check user values, especially if this a web site where you do not know and you cannot trust the motives of users.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2012
    Posts
    16
    Validation code is a good idea. The menu I made should be providing a numeric value. I ran a test and put some hard-coded numbers in the menu and the insert query ran perfectly. Any ideas on how to fix this?

    -CB

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    use validation code that ensures what is about to hit the db is sane.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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