Overthinking the INSERT

hcdarkmage · July 9, 2010

Okay . . . how to explain the problem.

I have a dynamic form that doesn't have set field names, so inserting the information into a database is giving me problems. I didn't write the code, but I do need to figure out the solution.

The form itself looks like:

echo "
    <script language=JavaScript>
        var fieldsid = new Array();
var fields = new Array();
var fieldsrequire = new Array();
var fieldstype = new Array();
var fieldstext = new Array();
var fieldscount = 0;
    </script>

    <table border=0 cellpadding=2 width=100% align=center>
        <form name=frmSend action=\"". $GLOBALS['ActivePage']."?homeinclude=$homeinclude\" method=post>
            <input type=hidden name=pageaction value=\"post\">
            <input type=hidden name=sendToEmail value=".$sendToEmail." />
            <b>".$lang['contactform']['contactform']."</b><hr size=1><br />";

            $oContact_Field->data = array("field_id", "fieldname", "fieldtype", "fieldtext", "width", "height","fieldrequire");

            $oContact_Field->order = "sequence asc";
            $result = $oContact_Field->getList();
            while($myrow=mysql_fetch_row($result)){
                echo "
                    <script language=JavaScript>
                        fieldsid[fieldscount]=\"$myrow[0]\";
                        fields[fieldscount]=\"$myrow[1]\";
                        fieldstype[fieldscount]=\"$myrow[2]\";
                        fieldsrequire[fieldscount]=\"$myrow[6]\";
                        fieldstext[fieldscount]=\"$myrow[3]\";
                        fieldscount++;
                    </script>";

                $requiredflag = ($myrow[6]=="yes"?"*":"");
                echo "<tr><td valign=top width=20%>".stripslashes($myrow[3])." $requiredflag</td>";

                switch($myrow[2]){
                    case "textbox":
                        echo "<td valign=top><input type=text style=\"width:{$myrow[4]}px\" name=\"$myrow[0]\" value=\"${$myrow[1]}\"></td>";
                        break;

                    case "drop":
                        echo "<td valign=top><select name=\"$myrow[0]\" style=\"width:{$myrow[4]}px\">";
                        $oContact_FieldOption->data = array("optionvalue");
                        $oContact_FieldOption->where = "field_id=$myrow[0]";
                        $oContact_FieldOption->order = "option_id asc";
                        $resultOption = $oContact_FieldOption->getList();
                        while($myrowOption=mysql_fetch_row($resultOption)){
                            echo "<option value=\"$myrowOption[0]\">$myrowOption[0]</option>";
                        }
                        echo "</select></td>";
                        break;

                    case "checkbox":
                        $oContact_FieldOption->data = array("optionvalue");
                        $oContact_FieldOption->where = "field_id=$myrow[0]";
                        $oContact_FieldOption->order = "option_id asc";
                        $resultOption = $oContact_FieldOption->getList();
                        echo "<td><table border=0 cellpadding=0 cellspacing=0><tr>";
                        while($myrowOption=mysql_fetch_row($resultOption)){
                            $checked= $fieldvalue==$myrowOption[0]?"checked":"";
                            echo "<td style=\"width:{$myrow[4]}px\"><input type=checkbox name=\"$myrow[0]\" value=\"$myrowOption[0]\" $checked>$myrowOption[0]</td>";
                        }
                        echo "</tr></table></td>";
                        break;

                    case "radio":
                        $oContact_FieldOption->data = array("optionvalue");
                        $oContact_FieldOption->where = "field_id=$myrow[0]";
                        $oContact_FieldOption->order = "option_id asc";
                        $resultOption = $oContact_FieldOption->getList();
                        echo "<td><table border=0 cellpadding=0 cellspacing=0><tr>";
                        while($myrowOption=mysql_fetch_row($resultOption)){
                            $checked= ($$myrow[1]==$myrowOption[0]?"checked":"");
                            echo "<td style=\"width:{$myrow[4]}px\"><input type=radio name=\"$myrow[0]\" value=\"$myrowOption[0]\">$myrowOption[0]</td>";
                        }
                        echo "</tr></table></td>";
                        break;

                    case "textarea":
                        echo "<td valign=top><textarea style=\"width:{$myrow[4]}px; height:{$myrow[5]}px\" name=\"$myrow[0]\">${$myrow[1]}</textarea></td>";
                        break;
                }

                echo "</tr>";
            }
            echo "
                <tr><td> </td><td align=left><img id=\"CaptchaImage\" src=\"/shared/administrator/blogger/Captcha.php?NoCache=".rand(0,1073741824)."\">
                <br />
                <input type='BUTTON' onclick='RefreshCaptcha();' value=\"Can't Read\"><br>
                <input type=\"Text\" name=\"CaptchaGuess\" required=\"Yes\" size=\"25\" style=\"width:100px;\"></td></tr>";

But to insert it into the database, I need to take each form field and put it in a different row. I know how to call the information later for emailing, but I am having a problem with the INSERT statement.

So far, though this isn't correct, I have something like this:

$getNum = "SELECT contact_id FROM contact_form2 ORDER BY contact_id DESC LIMIT 1";
$result = mysl_query($getNum) or die(mysql_error());

$userid = $GLOBALS['modSiteInfo']->user_id;
$fields = "SELECT * FROM contact_field WHERE user_id = '$userid'";

$fieldcount = count($fields);

$contact = mysql_result($result);

if($contact != ""){
    $contactid = $contact + 1;
}else{
    $contactid = 1;
}

$datepost = date("Y-m-d h:i:s") 

foreach($_POST as $key => $value) {
    $fieldid = $$key;
    $content = $$value;
}

$f = 0;

while ($f < $fieldcount) {
    $sql = "INSERT INTO contact_form2 (user_id, contact_id, datepost, field_id, content) VALUES ('".$userid."','".$contactid."','".$datepost."','".$fieldid."','".$content."')";
    mysql_query($sql) or die(mysql_error());
    $f++;
}

Could anyone help me figure this out?

tronicsmasta · July 9, 2010

what result are you getting?? blank info in the database, php/database errors???

hcdarkmage · July 9, 2010

I was actually getting nothing. So I thought I would change the insert to look like this:

$getNum = "SELECT contact_id FROM contact_form2 ORDER BY contact_id DESC LIMIT 1";
$result = mysl_query($getNum) or die(mysql_error());

$userid = $GLOBALS['modSiteInfo']->user_id;
$fields = "SELECT * FROM contact_field WHERE user_id = '$userid'";

$fieldcount = count($fields);

$contact = mysql_result($result);

if($contact != ""){
    $contactid = $contact + 1;
}else{
    $contactid = 1;
}

$datepost = date("Y-m-d h:i:s");

while($row = mysql_fetch_array($fields)){

    $fieldid = $row['field_id'];
    $f = 0;

    while ($f < $fieldcount) {
        $sql = "INSERT INTO contact_form2 (user_id, contact_id, datepost, field_id, content) VALUES ('".$userid."','".$contactid."','".$datepost."','".$fieldid."','".$_POST[$fieldid]."')";
        mysql_query($sql) or die(mysql_error());
        $f++;
    }
}

Figuring that would work, I was testing it, but for some reason my submit button stopped working. I went to see what was wrong and I see the issue has something to do with the JS that checks for required fields and makes sure they are filled:

<script language=JavaScript>

function checkRequiredField(){

    for (i=0; i<fieldsid.length; i++){
        var field = document.frmSend[fields[i]];
        if (fieldsrequire[i]=="yes" && (fieldstype[i] == "textbox" || fieldstype[i] == "textarea")){
            if (!field.value){
                alert("'"+ fieldstext[i] +"' <? echo $lang['contactform']['requiredis'] ?>");
                return false;
            }

        }else if (fieldsrequire[i]=="yes" && fieldstype[i] == "radio"){

            var blnChecked = false;
            for(j=0; j<field.length;j++){
                if (field[j].checked){
                    blnChecked = true;
                    break;
                }
            }
            if (blnChecked==false) {
                alert("'"+ fieldstext[i] +"' <? echo $lang['contactform']['requiredis'] ?>");
                return false;
            }
        }
    }

    return true;
}

</script>

hcdarkmage · July 9, 2010

Okay . . . My JS script is now submitting. I put a bunch of "echo" scripts for around in my submit script to see what is being hit. Everything is fine until it hits my while loop:

echo "The form has been submitted!!";  //Testing code
/* Testing for now! */
$getNum = "SELECT contact_id FROM contact_form2 ORDER BY contact_id DESC LIMIT 1";
$result = mysql_query($getNum) or die(mysql_error());

$userid = $GLOBALS['modSiteInfo']->user_id;
$fields = "SELECT * FROM contact_field WHERE user_id = '$userid'";

$fieldcount = count($fields);

echo "<br />".$fieldcount." is the field count.";  //Testing code

$contact = mysql_result($result);

echo "<br />".$contact." is the retrieved ID.";  //Testing code

if($contact != ""){
    $contactid = $contact + 1;
}else{
    $contactid = 1;
}

echo "<br />".$contactid." is the Contact ID.";  //Testing code

$datepost = date("Y-m-d h:i:s");

echo "<br />".$datepost." is the dat posted.";  //Testing code

while($row = mysql_fetch_array($fields)){

    $fieldid = $row['field_id'];
    $fieldname = $row['fieldname'];
    $f = 0;
    echo "<br />Inside the first while loop!";  //Testing code

    while ($f < $fieldcount) {
        $sql = "INSERT INTO contact_form2 (user_id, contact_id, datepost, field_id, content) VALUES  ('".$userid."','".$contactid."','".$datepost."','".$fieldid."','".$_POST[$fieldname]."')";
        mysql_query($sql) or die(mysql_error());
        $f++;
        echo "<br />information inserted.";  //Testing code
    }
}

Can anyone help me figure this out?

Oluwakore · July 9, 2010

Hello everyone, I wish to build an intranet portal/site accessible only to people within our local network, Please I need your help concerning the following problems

1. whats the best way to store multimedia files and how do you reference a file in a database

2. how to create an IM chat on a php intranet portal

3. how to run host a CMS portal an intranet network

4. How to include a download option on a php page 4 users to

5. Pros & Cons of HTTP Authentication Method

6. how to compile an html file to turn it into e-book

hcdarkmage · July 9, 2010

Hello everyone, I wish to build an intranet portal/site accessible only to people within our local network, Please I need your help concerning the following problems

1. whats the best way to store multimedia files and how do you reference a file in a database

2. how to create an IM chat on a php intranet portal

3. how to run host a CMS portal an intranet network

4. How to include a download option on a php page 4 users to

5. Pros & Cons of HTTP Authentication Method

6. how to compile an html file to turn it into e-book

What does this have to do with the original problem at hand? Not cool!

hcdarkmage · July 12, 2010

Bump . . .

hcdarkmage · July 12, 2010

Okay . . . new tactic. I got it to go into my first WHILE loop, but I'm not getting it to go into the INSERT query. Can anyone help point out where I'm going wrong?

echo "<!-- The form has been submitted!! -->";  //Testing code
/* Testing for now! */
$getNum = "SELECT contact_id FROM contact_form2 ORDER BY contact_id DESC LIMIT 1";
$result = mysql_query($getNum) or die(mysql_error());

$userid = $GLOBALS['modSiteInfo']->user_id;

$getCount = "SELECT COUNT(*) FROM contact_field WHERE user_id= '$userid'";
$fcount = mysql_query($getCount) or die(mysql_error());
$fieldcount = mysql_result($fcount);

echo "<!-- ".$fieldcount." is the field count. -->";  //Testing code

$contact = mysql_result($result);

echo "<!-- ".$contact." is the retrieved ID. -->";  //Testing code

if($contact != ""){
    $contactid = $contact + 1;
}else{
    $contactid = 1;
}

echo "<!-- ".$contactid." is the Contact ID. -->";  //Testing code

$datepost = date("Y-m-d h:i:s");

echo "<!-- ".$datepost." is the date posted. -->";  //Testing code

$getFields = "SELECT * FROM contact_field WHERE user_id = '$userid'";
$fields = mysql_query($getFields) or die(mysql_error());

while($row = mysql_fetch_array($fields)){

    $fieldid = $row['field_id'];
    $fieldname = $row['fieldname'];
    $f = 0;
    echo "<!-- Inside the first while loop! -->";  //Testing code
    echo "<!-- ".$fieldid." ".$fieldname." ".$_POST[$fieldname]." Something!!! -->";  //Testing code

    while ($f < $fieldcount) {
        $sql = "INSERT INTO contact_form2 (user_id, contact_id, datepost, field_id, content) VALUES ('".$userid."','".$contactid."','".$datepost."','".$fieldid."','".$_POST[$fieldname]."')";
        mysql_query($sql) or die(mysql_error());
        $f++;
        echo "<!-- information inserted. -->";  //Testing code
    }
}

hcdarkmage · July 12, 2010

Never mind . . . like the subject line said, I was over thinking the whole thing.

$userid = $GLOBALS['modSiteInfo']->user_id;

$getNum = "SELECT contact_id FROM contact_form2 ORDER BY contact_id DESC LIMIT 1";
$result = mysql_query($getNum) or die(mysql_error());
$contact = mysql_result($result);

if($contact != ""){
    $contactid = $contact + 1;
}else{
    $contactid = 1;
}

$datepost = date("Y-m-d h:i:s");

$getFields = "SELECT * FROM contact_field WHERE user_id = '$userid'";
$fields = mysql_query($getFields) or die(mysql_error());

while($row = mysql_fetch_array($fields)){

    $fieldid = $row['field_id'];
    $fieldname = $row['fieldname'];

    $sql = "INSERT INTO contact_form2 (user_id, contact_id, datepost, field_id, content) VALUES ('".$userid."','".$contactid."','".$datepost."','".$fieldid."','".$_POST[$fieldname]."')";
    mysql_query($sql) or die(mysql_error());

}

Pikachu2000 · July 12, 2010

Sort of a shot in the dark, but have you echoed $fieldcount before the: while ($f < $fieldcount) to make sure it's value is greater than $f (zero)?

That's twice today I've been typing something out while the topic was being marked solved! >:(

Sign In

Overthinking the INSERT

Recommended Posts

hcdarkmage

Link to comment

Share on other sites

tronicsmasta

Link to comment

Share on other sites

hcdarkmage

Link to comment

Share on other sites

hcdarkmage

Link to comment

Share on other sites

Oluwakore

Link to comment

Share on other sites

hcdarkmage

Link to comment

Share on other sites

hcdarkmage

Link to comment

Share on other sites

hcdarkmage

Link to comment

Share on other sites

hcdarkmage

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information