dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: AJAX won't add a new record to MySQL table.

  1. #1
    Join Date
    Dec 2009
    Location
    Pretoria, South Africa
    Posts
    7

    Unhappy AJAX won't add a new record to MySQL table.

    Hi there,

    For some reason AJAX won't add a new record to the 5_suppliers table. The correct value's are sent/requested in the ajax, I believe the javascript generated SQL query is correct from syntax to table names and field names and values - no errors on the client side whatsoever. I've checked the code, server, username , password and db are correct, and it retrieves the existing records no problem.

    The page I am working on is local so here is the xml response generator, followed by the js file. The content is retrieved no problem from the database.

    Code:
    <?php
    $q = $_GET["q"];
    if (is_numeric($q)) {
        if ($q > 3 || $q < 0) {
            $q = 0;
        }
    } else {
        $q = 0;
    }
    $sql = "";
    $tsql = "";
    
    function getrecords() {
        $sql = "SELECT * FROM 5_suppliers ORDER BY text_SupplierName ASC;\n";
        $GLOBALS["sql"] .= $sql;
        $result = mysql_query($sql);
        if ($result) {
            while ($row = mysql_fetch_array($result)) {
                echo "    <supplier>\n";
                echo "        <supplierid>" . $row["bigint_SupplierID"] . "</supplierid>\n";
                echo "        <suppliername>" . $row["text_SupplierName"] . "</suppliername>\n";
                echo "        <supplierw3>" . $row["text_SupplierW3"] . "</supplierw3>\n";
                echo "        <contactfname>" . $row["text_ContactFirstName"] . "</contactfname>\n";
                echo "        <contactsname>" . $row["text_ContactSurname"] . "</contactsname>\n";
                echo "        <contactpos>" . $row["text_ContactPosition"] . "</contactpos>\n";
                echo "        <contactemail>" . $row["text_ContactE-mail"] . "</contactemail>\n";
                echo "        <contacttel>" . $row["bigint_ContactTel"] . "</contacttel>\n";
                echo "        <contactcurbal>" . $row["bigint_CurrentBalance"] . "</contactcurbal>\n";
                echo "    </supplier>\n";
            }
        }
    }
    
    $conn = mysql_connect("localhost", "root", "");
    //$conn = mysql_connect("localhost", "*****", "*****");
    if (!$conn) {
        die("Could not connect: " . mysql_error());
    }
    mysql_select_db("ferr3ty0_ferrety", $conn);
    switch ($q) {
        case 1: // add supplier
            $snam = $_POST["text_suppliername"];
            $snam = mysql_real_escape_string($snam);
            $sw3a = $_POST["text_supplierw3address"];
            $sw3a = mysql_real_escape_string($sw3a);
            $cfnm = $_POST["text_contactfirstname"];
            $cfnm = mysql_real_escape_string($cfnm);
            $csnm = $_POST["text_contactsurname"];
            $csnm = mysql_real_escape_string($csnm);
            $cpos = $_POST["text_contactposition"];
            $cpos = mysql_real_escape_string($cpos);
            $ceml = $_POST["text_contactemail"];
            $ceml = mysql_real_escape_string($ceml);
            $ctel = $_POST["text_contacttelephonenumber"];
            $ctel = mysql_real_escape_string($ctel);
            $cbal = $_POST["text_creditbalance"];
            $cbal = mysql_real_escape_string($cbal);
            $tsql = "INSERT INTO 5_suppliers (text_SupplierName,text_SupplierW3,text_ContactFirstName,text_ContactSurname,tex
    t_ContactPosition,text_ContactE-mail,bigint_ContactTel,bigint_CurrentBalance) VALUES (\"".
                    $snam."\",\"".$sw3a."\",\"".$cfnm."\",\"".$csnm."\",\"".$cpos."\",\"".$ceml."\",".$ctel.",".$cbal.");\n";
            $sql .= $tsql;
            $result = mysql_query($tsql);
            break;
        case 2: // modify supplier
            
            break;
        case 3: // remove supplier
            
            break;
        default: // (re)load form
    }
    header("Content-type: text/xml");
    echo "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n";
    echo "<root>\n";
    getrecords();
    echo "    <sql>" . $sql . "</sql>\n";
    echo "</root>";
    mysql_close($conn);
    ?>
    Last edited by Greywacke; 12-18-2009 at 10:17 AM.

  2. #2
    Join Date
    Dec 2009
    Location
    Pretoria, South Africa
    Posts
    7

    Post Javascript: Part 1/2

    Code:
    // Suppliers AJAX & DHTML
    // xml request
    var http_request = false;
    var parents = new Array();
    function makeRequest(method, url, parameters) {
        http_request = false;
        if (window.XMLHttpRequest) { // Mozilla, Safari,...
            http_request = new XMLHttpRequest();
            if (http_request.overrideMimeType) {
                // set type accordingly to anticipated content type
                http_request.overrideMimeType('text/xml');
                //http_request.overrideMimeType('text/html');
            }
        } else if (window.ActiveXObject) { // IE
            try {
                http_request = new ActiveXObject("Msxml2.XMLHTTP");
            } catch (e) {
            try {
                http_request = new ActiveXObject("Microsoft.XMLHTTP");
            } catch (e) {}
            }
        }
        if (!http_request) {
            alert('Cannot create XMLHTTP instance');
            return false;
        }
        http_request.onreadystatechange = alertContents;
        url += (method=="GET")?parameters:"";
        http_request.open(method, url, true);
        if (method == "POST") {
            http_request.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
            http_request.setRequestHeader("Content-length", parameters.length);
            http_request.setRequestHeader("Connection", "close");
        }
        http_request.send((method=="GET")?null:parameters);
    }
    // xml parser
    function alertContents() {
        if (http_request.readyState == 4) {
            if (http_request.status == 200) {
                var xmldoc = http_request.responseXML;
                var rows = xmldoc.getElementsByTagName("supplier");
                for (var r = 0; r < rows.length; r++) {
                    var i = 0;
                    var len = parseInt(rows[r].childNodes.length / 2);
                    var arr = new Array(len);
                    for (var c = 0; c < rows[r].childNodes.length; c++) {
                        var supplier = rows[r].childNodes[c];
                        if (supplier.childNodes.length > 0) {
                            arr[i] = supplier.firstChild.data;
                            i++;
                        }
                    }
                    //alert(arr);
                    addrecord("list_suppliers", arr);
                }
                if (xmldoc.getElementsByTagName("sql")[0]) if (xmldoc.getElementsByTagName("sql")[0].firstChild) var sql = xmldoc.getElementsByTagName("sql")[0].firstChild.data; if (sql != "") alert(sql);
            } else {
                alert('There was a problem with the request.');
            }
            document.getElementById("ajaxbg").style.visibility = "hidden";
        }
    }
    // instantiate ajax requests
    function ajaxRequest(obj) {
        document.getElementById("ajaxbg").style.visibility = "visible";
        switch (obj.value) {
            case "Add":
                if (trim(document.form_suppliers.text_suppliername.value) == "") {
                    alert("Cannot create a blank supplier!");
                    document.getElementById("ajaxbg").style.visibility = "hidden";
                    document.form_suppliers.text_suppliername.focus();
                    return false;
                }
                for (var i = 0; i < document.form_suppliers.list_suppliers.options.length; i++) {
                    if (trim(document.form_suppliers.text_suppliername.value).toLowerCase() == trim(document.form_suppliers.list_suppliers.options[i].text).toLowerCase()) {
                        alert("Cannot create a duplicate supplier!");
                        document.getElementById("ajaxbg").style.visibility = "hidden";
                        document.form_suppliers.list_suppliers.focus();
                        return false;
                    }
                }
                if (trim(document.form_suppliers.text_supplierw3address.value) == "") {
                    if (!confirm("Are you sure you want to leave\nthe supplier's web address blank?")) {
                        document.getElementById("ajaxbg").style.visibility = "hidden";
                        document.form_suppliers.text_supplierw3address.focus();
                        return false;
                    }
                }
                if (trim(document.form_suppliers.text_contactfirstname.value) == "") {
                    alert("The contact's first name must be supplied!");
                    document.getElementById("ajaxbg").style.visibility = "hidden";
                    document.form_suppliers.text_contactfirstname.focus();
                    return false;
                }
                if (trim(document.form_suppliers.text_contactsurname.value) == "") {
                    alert("The contact's surname must be supplied!");
                    document.getElementById("ajaxbg").style.visibility = "hidden";
                    document.form_suppliers.text_contactsurname.focus();
                    return false;
                }
                if (trim(document.form_suppliers.text_contactposition.value) == "") {
                    alert("The contact's position must be supplied!");
                    document.getElementById("ajaxbg").style.visibility = "hidden";
                    document.form_suppliers.text_contactposition.focus();
                    return false;
                }
                if (trim(document.form_suppliers.text_contactemail.value) == "") {
                    if (!confirm("Are you sure you want to leave\nthe contact's e-mail address blank?")) {
                        document.getElementById("ajaxbg").style.visibility = "hidden";
                        document.form_suppliers.text_contactemail.focus();
                        return false;
                    }
                }
    // to be continued
    Last edited by Greywacke; 12-18-2009 at 10:54 AM.

  3. #3
    Join Date
    Dec 2009
    Location
    Pretoria, South Africa
    Posts
    7

    Post Javascript: Part 2/2

    Code:
    // continued from previous reply
                if (trim(document.form_suppliers.text_contacttelephonenumber.value) == "") {
                    if (!confirm("Are you sure you want to leave\nthe contact's phone numberlol blank?")) {
                        document.getElementById("ajaxbg").style.visibility = "hidden";
                        document.form_suppliers.text_contacttelephonenumber.focus();
                        return false;
                    }
                }
                if (parseInt(document.form_suppliers.text_creditbalance.value) < 0) {
                    alert("Can't start a supplier account with a negative PAYG balance!");
                    document.getElementById("ajaxbg").style.visibility = "hidden";
                    document.form_suppliers.text_contactposition.focus();
                    return false;
                }
                var poststr = "text_suppliername=" + encodeURIComponent(document.form_suppliers.text_suppliername.value) +
                              "&text_supplierw3address=" + encodeURIComponent(document.form_suppliers.text_supplierw3address.value) +
                              "&text_contactfirstname=" + encodeURIComponent(document.form_suppliers.text_contactfirstname.value) +
                              "&text_contactsurname=" + encodeURIComponent(document.form_suppliers.text_contactsurname.value) +
                              "&text_contactposition=" + encodeURIComponent(document.form_suppliers.text_contactposition.value) +
                              "&text_contactemail=" + encodeURIComponent(document.form_suppliers.text_contactemail.value) +
                              "&text_contacttelephonenumber=" + encodeURIComponent(parseInt(document.form_suppliers.text_contacttelephonenumber.value)) +
                              "&text_creditbalance=" + encodeURIComponent(parseInt(document.form_suppliers.text_creditbalance.value));
                makeRequest("POST", "scripts/ajax_suppliers.php?q=1", poststr);
                document.form_suppliers.list_suppliers.options.length = 0;
                document.form_suppliers.text_suppliername.value = "";
                document.form_suppliers.text_supplierw3address.value = "";
                document.form_suppliers.text_contactfirstname.value = "";
                document.form_suppliers.text_contactsurname.value = "";
                document.form_suppliers.text_contactposition.value = "";
                document.form_suppliers.text_contactemail.value = "";
                document.form_suppliers.text_contacttelephonenumber.value = "";
                document.form_suppliers.text_creditbalance.value = "";
                break;
            case "Modify":
    
                break;
            case "Remove":
    
                break;
            default:
                makeRequest("GET", "scripts/ajax_suppliers.php?q=0", "");
                document.form_suppliers.list_suppliers.options.length = 0;
                document.form_suppliers.text_suppliername.value = "";
                document.form_suppliers.text_supplierw3address.value = "";
                document.form_suppliers.text_contactfirstname.value = "";
                document.form_suppliers.text_contactsurname.value = "";
                document.form_suppliers.text_contactposition.value = "";
                document.form_suppliers.text_contactemail.value = "";
                document.form_suppliers.text_contacttelephonenumber.value = "";
                document.form_suppliers.text_creditbalance.value = "";
        }
    }
    // string trim functions
    function trim(str, chars) {
        return ltrim(rtrim(str, chars), chars);
    }
    function ltrim(str, chars) {
        if (chars == undefined) var chars = "";
        chars = chars || "\\s";
        return str.replace(new RegExp("^[" + chars + "]+", "g"), "");
    }
    function rtrim(str, chars) {
        if (chars == undefined) var chars = "";
        chars = chars || "\\s";
        return str.replace(new RegExp("[" + chars + "]+$", "g"), "");
    }
    // dynamic updaters
    function addrecord(id, arr) {
        var opt1 = document.createElement('option');
        var opt2 = document.createElement('option');
        //alert(arr[2]);
        opt1.text = arr[1];                // supplier name
        opt1.value = arr[0] +            // supplier id
                     "," + arr[2] +        // supplier w3
                     "," + arr[3] +        // supplier contact first name
                     "," + arr[4] +        // supplier contact surname
                     "," + arr[5] +        // supplier contact position
                     "," + arr[6] +        // supplier contact e-mail
                     "," + arr[7] +        // supplier contact telephone
                     "," + arr[8];        // supplier current balance
        var sel1 = document.getElementById("list_suppliers");
        try {
            sel1.add(opt1, null); // standards compliant; doesn't work in IE
        }
        catch(ex) {
            sel1.add(opt1); // IE only
        }
    }
    // onload event handler
    window.onload = function () {
        return ajaxRequest(document.form_suppliers.button_reset);
    }
    // onselect event handler
    document.form_suppliers.list_suppliers.onchange = function () {
        var val = this.options[this.selectedIndex].value.split(",");
        //alert(val);
        document.form_suppliers.text_suppliername.value = ltrim(this.options[this.selectedIndex].text,"");
        document.form_suppliers.text_supplierw3address.value = val[1];
        document.form_suppliers.text_contactfirstname.value = val[2];
        document.form_suppliers.text_contactsurname.value = val[3];
        document.form_suppliers.text_contactposition.value = val[4];
        document.form_suppliers.text_contactemail.value = val[5];
        document.form_suppliers.text_contacttelephonenumber.value = val[6];
        document.form_suppliers.text_creditbalance.value = val[7];
    }
    // onkeypress event handler
    document.form_suppliers.text_creditbalance.onkeypress = function (e) {
         if (!e) var e = window.event;
         if ([e.keyCode||e.which]==8||[e.keyCode||e.which]==46) //this is to allow backspace or delete
              return true;
         if ([e.keyCode||e.which] < 48 || [e.keyCode||e.which] > 57)
              e.preventDefault? e.preventDefault() : e.returnValue = false;
    }
    Last edited by Greywacke; 12-18-2009 at 10:15 AM.

  4. #4
    Join Date
    Dec 2009
    Location
    Pretoria, South Africa
    Posts
    7

    Query solved

    seem to have found my solution, enclosing any names using a hyphen (-) in back slanting single quotes eg. (`text_ContactE-mail`)
    query solved

Similar Threads

  1. Add related record using a gridview
    By kamur in forum ASP.NET
    Replies: 1
    Last Post: 12-17-2009, 09:46 AM
  2. How to keep history of record updates in MySQL?
    By nataliya in forum Database
    Replies: 5
    Last Post: 04-18-2008, 07:35 AM
  3. Cannot Add Then Cancel Record In VB6 & Access 2003
    By mark1110 in forum VB Classic
    Replies: 5
    Last Post: 05-07-2007, 04:31 PM
  4. Replies: 0
    Last Post: 01-31-2007, 03:43 AM
  5. Saving Record - AJAX
    By Sync in forum AJAX
    Replies: 3
    Last Post: 04-15-2006, 12:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center