gpuk360 gpuk360 - 5 months ago 12
SQL Question

How to get current Content from HTML Table with PHP

I am trying to edit data by using HTML and PHP. In my HTML file there is a table that contains the data from a SQLite database (SELECT statement works fine). The PHP file contains the UPDATE statement with some concat.

sshTunnel.sqlite --> tunnelthomas

CREATE TABLE "tunnelthomas" ("ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "SSH_USER" VARCHAR, "SSH_IP" VARCHAR, "SSH_PORT" VARCHAR, "SSH_PW" VARCHAR, "SSH_KEYFILE" VARCHAR, "TUNNEL_LOCAL_INTERFACE" VARCHAR, "TUNNEL_LOCAL_PORT" VARCHAR, "TUNNEL_REMOTE_IP" VARCHAR, "TUNNEL_REMOTE_PORT" VARCHAR, "DESCRIPTION" VARCHAR)


This is the "HTML" part:

while ($row = $results->fetchArray(SQLITE3_ASSOC))
{
$output .= '<tr>
<td class="ID" data-id1="' . $row['ID'] . '">' . $row['ID'] . '</td>
<td class="SSH_USER" data-id2="' . $row['ID'] . '" contenteditable="true">' . $row['SSH_USER'] . '</td>
<td class="SSH_IP" data-id3="' . $row['ID'] . '" contenteditable="true">' . $row['SSH_IP'] . '</td>
<td class="SSH_PORT" data-id4="' . $row['ID'] . '" contenteditable="true">' . $row['SSH_PORT'] . '</td>
<td class="SSH_PW" data-id5="' . $row['ID'] . '" contenteditable="true">' . $row['SSH_PW'] . '</td>
<td class="SSH_KEYFILE" data-id6="' . $row['ID'] . '" contenteditable="true">' . $row['SSH_KEYFILE'] . '</td>
<td class="TUNNEL_LOCAL_INTERFACE" data-id7="' . $row['ID'] . '" contenteditable="true">' . $row['TUNNEL_LOCAL_INTERFACE'] . '</td>
<td class="TUNNEL_LOCAL_PORT" data-id8="' . $row['ID'] . '" contenteditable="true">' . $row['TUNNEL_LOCAL_PORT'] . '</td>
<td class="TUNNEL_REMOTE_IP" data-id9="' . $row['ID'] . '" contenteditable="true">' . $row['TUNNEL_REMOTE_IP'] . '</td>
<td class="TUNNEL_REMOTE_PORT" data-id10="' . $row['ID'] . '" contenteditable="true">' . $row['TUNNEL_REMOTE_PORT'] . '</td>
<td class="DESCRIPTION" data-id11="' . $row['ID'] . '" contenteditable="true">' . $row['DESCRIPTION'] . '</td>
<td><button type="button" name="btn_edit" data-id12="' . $row['ID'] . '" class="btn btn-xs btn-warning btn-block btn_edit">Editieren</button></td>
<td><button type="button" name="btn_delete" data-id13="' . $row['ID'] . '" class="btn btn-xs btn-danger btn-block btn_delete">Löschen</button></td>
</tr>';
}


This is the script part:

$(document).on('click', '.btn_edit', function()
{
var ID = $(this).data("id12");
if(confirm("Are you sure you want to edit this?"))
{
$.ajax({
type: "POST",
url: "edit.php",
data: {ID:ID},
dataType: "text",
success: function(data)
{
alert('ajax success');
fetch_data();
}
});
}
});


Finally, this is the PHP part:

<?php
$db = new SQLite3('sshTunnel.sqlite');
$db->exec('UPDATE tunnelthomas SET SSH_USER="' . $_POST['SSH_USER'] . '",
SSH_IP="' . $_POST['SSH_IP'] . '",
SSH_PORT="' . $_POST['SSH_PORT'] . '",
SSH_PW="' . $_POST['SSH_PW'] . '",
SSH_KEYFILE="' . $_POST['SSH_KEYFILE'] . '",
TUNNEL_LOCAL_INTERFACE="' . $_POST['TUNNEL_LOCAL_INTERFACE'] . '",
TUNNEL_LOCAL_PORT="' . $_POST['TUNNEL_LOCAL_PORT'] . '",
TUNNEL_REMOTE_IP="' . $_POST['TUNNEL_REMOTE_IP'] . '",
TUNNEL_REMOTE_PORT="' . $_POST['TUNNEL_REMOTE_PORT'] . '",
DESCRIPTION="' . $_POST['DESCRIPTION'] . '"
WHERE ID="' . $_POST['ID'] . '"');
$db->close();
?>


It seems, that there is no content buffered. Instead of updating the database correctly, the scripts "delete" all the row content. It seems, that they update all columns with NULL. Does anyone know why?

Answer

In your ajax call you set data: {ID: ID} that is all which will be send. Only $_POST['ID'] will be set.

You have to send the data of the elements.

For you current HTML Code it could be the following code.

var ID = $(this).data("id12");
var data = {ID: ID};
$(this).closest('tr').find('td[class]').each(function() {
    data[$(this).attr('class')] = $(this).text();
});
console.log(data); //check if the data you send is complete

Then use data: data in the ajax call to send the data.

But you should change the structure of you HTML. Use a data-name or something similar to set the name not a class.

Comments