CODEWITHSUNDEEP
javascriptphpjqueryhtml-table
Slayer0248
Slayer0248

Reputation: 1261

jQuery update html table based on mysql table

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...

Upvotes: 0

Views: 1867

Answers (1)

keji
keji

Reputation: 5990

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

Upvotes: 1

Related Questions