用于更新具有相同 session_id 的表的 PHP 查询 [英] PHP query for updating table with same session_id

问题描述

我使用 session_id 作为我的数据库中的 ID 列.还有一个名为 Sections 的列，其中添加了整个 xml.xml 包含部分名称和用户在那里花费的时间.我想更新相同 session_ids 的 Sections 列.我怎样才能做到这一点?现在它为每条记录添加一个新行.这是我的php代码

I used session_id as the ID column in my db.There is one more column named Sections where added the whole xml. The xml contains section names and the amount of time user spent there. I want to update the Sections column for same session_ids. How can I do that? Right now it adds a new row for every record. Here is my php code

$id=$_SESSION["id"];
$totaltime=$_POST['total'];
$HomeTime=$_POST['home'];
$ProductTime=$_POST['products'];
$ProcessTime=$_POST['process'];
$DevTime=$_POST['dev'];
$ContactTime=$_POST['contact'];


$xmlObject = <<<XML
<?xml version="1.0" encoding="UTF-8"?> 
<item>
    <section>
        <name>Home</name>
        <time>$HomeTime</time>
    </section>  
    <section>
        <name>Product</name>
        <time>$ProductTime</time>
    </section>
    <section>
        <name>Process</name>
        <time>$ProcessTime</time>
    </section>  
    <section>
        <name>Development</name>
        <time>$DevTime</time>
    </section>
    <section>
        <name>Contact</name>
        <time>$ContactTime</time>
    </section>
    </item>
XML;



    $sql = "INSERT INTO user_time (ID, Sections) VALUES ('$id', '$xmlObject')";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

推荐答案

首先使用数据库存在的会话 ID 从数据库中获取现有的 XML 数据，并将其存储到变量 $xmlFromDB 并继续执行以下步骤.否则插入数据库.

First fetch existing XML data from db using the session id it exists, and store to variable $xmlFromDB and proceed with following steps. Otherwise insert to db.

if(session id exisits in db){
    //Fetch the table row or corresponding session id.
    //Extract times from XML using this code.

    $arr = [];
    $times = new SimpleXMLElement($xmlFromDB);
    foreach($times->section as $sec){
        $arr[$sec->name.""] = $sec->time;
    }
    extract($arr);

    //This will add previous value to new value.
    $HomeTime += $Home;
    $ProductTime += $Product;
    $ProcessTime += $Process;
    $DevTime += $Development;
    $ContactTime += $Contact;
}

//Now generate xml using your method.

$xmlObject = <<<XML
    <?xml version="1.0" encoding="UTF-8"?> 
    <item>
        <section>
            <name>Home</name>
            <time>$HomeTime</time>
        </section>  
        <section>
            <name>Product</name>
            <time>$ProductTime</time>
        </section>
        <section>
            <name>Process</name>
            <time>$ProcessTime</time>
        </section>  
        <section>
            <name>Development</name>
            <time>$DevTime</time>
        </section>
        <section>
            <name>Contact</name>
            <time>$ContactTime</time>
        </section>
        </item>
XML;

if(session id exists){
    //Then update the same row using the UPDATE  query.
    $sql = "UPDATE user_time SET Sections = '$xmlObject' where = '$id'";
}else{
    $sql = "INSERT INTO user_time (ID, Sections) VALUES ('$id', '$xmlObject')";
}

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
} 

这篇关于用于更新具有相同 session_id 的表的 PHP 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！