jQuery update html table based on mysql table

Question

I've got a MySQL database with 2 tables in it.

The main files I'm working with are index.php, jquery.js, jfunc.js, and urlmappingDB.php.

My main question pertains to the first 3. In index.php, I'm displaying the data from the data tables made in urlmappingDB.php in html tags. When I click the update button under the table I'ld like to display the updated MySQL table data in the corresponding position in the html table. I get the feeling that my current jquery code isn't going to suffice for updating a table since the variable it passes into the _POST array is pretty much an arbitrary variable in my program.

That said, my employer would prefer I use jquery for this, so I'm sort of stumped on how to implement this. How should I be going about this?

index.php

<?php 
    $con = mysql_connect("localhost", "root", "");

    if (!$con) {
        die("Error: ".mysql_error());
    }

    mysql_select_db("genius", $con);

     $result = mysql_query("SELECT * FROM `urlMappingConsumption`");
?>  

<!DOCTYPE html>
<html>
    <head>
        <script type ="text/javascript" src ="jquery.js"></script>
        <script type ="text/javascript">
        function get() {

            $.post('urlmappingDB.php', {},
            function(output) {

                $('#urlmappingdata').html(output).show();   
            }); 
        }
        </script>

        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
    <body>
        <table id="urlConsumptionTable">
                <thead>
                    <tr>
                        <th>urlConsumptionID</th>
                        <th>customerID</th>
                        <th>activityID</th>
                        <th>numOccurences</th>
                        <th>lastModified</th>
                    </tr>
                </thead>
                <tbody>
                    <?php while($row = mysql_fetch_array($result)) {?>
                        <tr>
                            <td><?=$row['urlMappingConsumptionID']?></td>
                            <td><?=$row['customerID']?></td>
                            <td><?=$row['activityID']?></td>
                            <td><?=$row['numOccurences']?></td>
                            <td><?=$row['lastModified']?></td>
                        </tr>
                    <?php } ?>
                </tbody>
            </table>
        <div id="urlmappingdata"></div>
        <form name="form">
            <input type="button" value="Update" id="update">
        </form>

        <script type="text/javascript" src="jfunc.js"></script>
    </body>
</html>

jfunc.js

$('#update').click( function() {
    var outputMessage;

    outputMessage = "<?php echo POST['outputMessage'];?>";

    $.post('urlmappingDB.php', {outputMessage:outputMessage}, function(data) {
        $('#urlmappingdata').html(data).show();
    });
});

The first MySQL table was created via this PHP file. I'm also updating the MySQL tables here:

urlmappingDB.php:

<?php
$checkVariable = 0;

function table_exists($tablename, $database = false) {
    if(!$database) {
        $res = mysql_query("SELECT DATABASE()");
        $database = mysql_result($res, 0);
    }

    $res = mysql_query("
        SELECT COUNT(*) AS count 
        FROM information_schema.tables 
        WHERE table_schema = '$database' 
        AND table_name = '$tablename'
    ");

    return mysql_result($res, 0) == 1;
}

//$conn = mysql_connect("localhost", "root", "");
//$db = mysql_select_db('genius', $conn);
try {
    $db = new PDO('mysql:host=localhost; dbname=genius;', 'root', '');
} 
catch (PDOException $e) {
    $error_message = $e->getMessage();
    echo $error_message;
    exit();
}

$query = "SELECT COUNT(*)
            FROM `urlmapping`";
$totalAccountedFor = $db ->query($query);
$allAccounted = 0;
foreach ($totalAccountedFor as $value) {
    $allAccounted = array_sum($value)/2;
}

$query = "SELECT `numOccurences` 
            FROM `urlMappingConsumption`";

$allOccurrences= $db->query($query);
$occurrencesSum = 0;
foreach ($allOccurrences as $row) {
    $occurrencesSum += (array_sum($row)/2)."<br>";

}

$query = "SELECT MIN(`urlMappingID`)
        FROM `urlmapping`
        LIMIT 1"; 
$minURLID = $db->query($query);

$urlMinID = 0;
foreach ($minURLID as $urlid) {
    $urlMinID = array_sum($urlid)/2;
}

$query = "SELECT MAX(`urlMappingID`)
        FROM `urlmapping`
        LIMIT 1";

$maxURLID = $db->query($query);
$urlMaxID = 0;

foreach ($maxURLID as $webid) {
    $urlMaxID = array_sum($webid)/2;
}

$urlIDDifference =$urlMaxID-$urlMinID;

$urlMinID =array_sum($urlid)/2;
$urlConsumption = array();

if($occurrencesSum == ($urlIDDifference + 1)) {
    $checkVariable = 1;

    for($i = 0; $i<($urlIDDifference+1); $i++) {
        $_POST['outputMessage'] = $urlMinID + $i;

        $outputMessage = mysql_real_escape_string($_POST['outputMessage']);
        $querys = "SELECT * 
                    FROM `urlmapping`
                    WHERE urlMappingID = $outputMessage";
        $row = $db->query($querys);

        foreach ($row as $r) {
            $valueCustomer = sprintf("%d",$r['customerID']);
            $valueActivity = sprintf("%d",$r['activityID']);

            if(array_key_exists($valueCustomer, $urlConsumption)) {
                if(is_array($urlConsumption[$valueCustomer])) {
                    if(array_key_exists($valueActivity, $urlConsumption[$valueCustomer])) {
                        $urlConsumption[$valueCustomer][$valueActivity] = $urlConsumption[$valueCustomer][$valueActivity] + 1;
                    } else {
                        $urlConsumption[$valueCustomer][$valueActivity] = 1;
                    }
                } else {
                    $urlConsumption[$valueCustomer] = array($valueActivity => 1);
                }
            } else {
                $urlConsumption[$valueCustomer] = array($valueActivity => 1);
            }
        }
    }
} elseif($occurrencesSum < ($urlIDDifference+1) && $occurrencesSum < $allAccounted ) {
    $otherUrlIDDifference = $urlIDDifference - $occurrencesSum;
    $checkVariable = 2; 

    for($i = 0; $i<($otherUrlIDDifference+1); $i++) {
        $_POST['outputMessage'] = $urlMinID + $occurrencesSum   + $i;
        $outputMessage = mysql_real_escape_string($_POST['outputMessage']);
        $querys = "SELECT * 
                    FROM `urlmapping`
                    WHERE urlMappingID = $outputMessage";
        $row = $db->query($querys);

        foreach($row as $r) {
            $valueCustomer = sprintf("%d",$r['customerID']);
            $valueActivity = sprintf("%d",$r['activityID']);


            if(array_key_exists($valueCustomer, $urlConsumption)) {

                if(is_array($urlConsumption[$valueCustomer])) {
                    if(array_key_exists($valueActivity, $urlConsumption[$valueCustomer])) {
                        $urlConsumption[$valueCustomer][$valueActivity] = $urlConsumption[$valueCustomer][$valueActivity] + 1;
                        // urlConsumption[$valueCustomer][$valueActivity]
                    } else {
                        $urlConsumption[$valueCustomer][$valueActivity] = 1;
                    }
                } else {
                    $urlConsumption[$valueCustomer] = array($valueActivity => 1);
                }
            } else {
                $urlConsumption[$valueCustomer] = array($valueActivity => 1);
            }
        }
    }
}

    $urlConsumptionCustomerKeys = array_keys($urlConsumption);
    $urlConsumptionActivityKeys = array();

    for($i = 0; $i<count($urlConsumptionCustomerKeys); $i++) {
        $urlConsumptionActivityKeys[] = array_keys($urlConsumption[$urlConsumptionCustomerKeys[$i]]);
    }

    $currentIDValue = 1;

    for($i = 0; $i < count($urlConsumptionCustomerKeys); $i++) {
        for ($j=0; $j < count($urlConsumptionActivityKeys[$i]); $j++) {
            $activityID=$urlConsumptionActivityKeys[$i][$j];

            $occurrences = $urlConsumption[$urlConsumptionCustomerKeys[$i]][$urlConsumptionActivityKeys[$i][$j]];

            $customerID= $urlConsumptionCustomerKeys[$i];

            if($checkVariable == 1) {
                $addToTableQuery = "INSERT INTO `urlMappingConsumption`
                                    (`urlMappingConsumptionID`, `customerID`, `activityID`, `numOccurences`, `lastModified`)
                                VALUES
                                    ('$currentIDValue','$customerID', '$activityID','$occurrences', NOW())";
                                $db->exec($addToTableQuery);
                                $currentIDValue = $currentIDValue +1;
                                $occurrencesSum +=1;
            }
            if($checkVariable==2) {
             $checkExistingIDsQuery = $db->query("SELECT `urlMappingConsumptionID`, `numOccurences`
                                                FROM `urlMappingConsumption`
                                                WHERE customerID = $customerID AND activityID = $activityID");
            if(!$checkExistingIDsQuery) {
                die("could not process query");
            } else {
                foreach ($checkExistingIDsQuery as $IDRow) {
                    if($occurrencesSum <$allAccounted) {
                        $newOccurences = $IDRow['numOccurences'] + 1;
                        $querys = "UPDATE urlMappingConsumption
                                    SET numOccurences = $newOccurences
                                    WHERE urlMappingConsumptionID={$IDRow['urlMappingConsumptionID']}";
                        $db->exec($querys);
                        $occurrencesSum +=1;
                    }
                }
            }
        }
    }
?>

The other MySQL table is defined by the 2 following files.

CreateUrlMapping.txt

DROP TABLE IF EXISTS `urlMapping`;
SET @saved_cs_client        = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `urlMapping` (
    `urlMappingID` bigint(20) unsigned NOT NULL auto_increment,
    `customerID` int(11) default '0',
    `activityID` int(11) default '0',
    `contactID` int(11) default '0',
    `fullURL` mediumtext,
    `lastModified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    `developerSandbox` varchar(25) default '',
    PRIMARY KEY (`urlMappingID`),
    KEY `customerActivityID` (`customerID`,`activityID`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

and

urlMapping.csv

INSERT INTO urlMapping (urlMappingID,customerID,activityID,contactID,fullURL,lastModified,developerSandbox)
VALUES
(7549046856,57544,2191972,166419052,'http://www.salesgenius.com/activities/emailOpened.php','2  012-06-1422:53:51','/webapp/webroot/'),
(7549046855,57544,2191972,166419052,'https://www.salesgenius.com/activities/unsubscribe.php','2012-06-1422:53:51','/webapp/webroot/'),
(7549046854,57544,2191972,166419052,'http://security.intuit.com/privacy/marketing-preferences.html','2012-06-1422:53:51','/webapp/webroot/'),
(7549046853,57544,2191972,166419052,'https://privacy.intuit.com/cpi/do/signin','2012-06-1422:53:51','/webapp/webroot/'),
(7549046852,57544,2191972,166419052,'http://ps.genius.com/intuit/pos/index_c.html','2012-06-1422:53:51','/webapp/webroot/'),
etc...

Your code is (was - I have edited it now) an absolute, horrible mess. I hope nobody has to maintain what you've written, I really do. — Bojangles
– Bojangles, Commented Jul 4, 2012 at 11:08

keji · Accepted Answer · 2012-07-04 05:48:02Z

1

Ok to go about this you would use ajax but onclick instead of getting raw data in your php file you put the data in a table then send it to the main page

$.ajax({
        url: "table.php", 
        type: "GET",
        data: data,     
        cache: false,
        success: function (html) {
            alert("HTLM = " + html);
            $('#table').html(html);
            }
 });

#table is the div your table is to be placed in

html is what ajax fetches from the php file = the table

it places the html inside the #table div

if you still need help comment

answered Jul 4, 2012 at 5:48

keji

5,9804 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

jQuery update html table based on mysql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related