Hello everyone!
I've been on a problem a few hours now and I've managed to finally find the solution however I feel it's very inefficient and am looking for a way to do this in a better way.
I have a textarea where each line is inserted into MySQL database table "lines" and I have the columns "line_number" and "row".
The textarea initial value on page load is simply a list of all lines ordered by line_number in ASC order.
If I modify/add/remove entries from the textarea it will remove data changed and add new data accordingly.
But I am making a lot of queries to do this and I'm not sure if there is a more efficient way than querying each line in the database to find the new value/modified value.
Here's my php script, hopefully this makes sense:
if (isset($_POST['lines'])) {
//get lines posted from textarea
$lines_posted = $_POST['lines'];
//trim all white spaces and remove empty lines as they are not valid for mysql insert
$lines = trim($lines_posted);
$lines = explode("\n", str_replace("\r", "", trim($lines_posted)));
$lines = array_map('trim', $lines_posted);
$lines = array_filter($lines_posted);
$line_count = count($lines);
//output how many valid lines were posted
echo "Posted Lines: $line_count<br>";
//loop through all lines posted, query all rows in database and insert if lines row value does not exist
for ($i = 0; $i < $line_count; $i++) {
if (isset($lines[$i])) {
$find_line_in_db = $db->query("SELECT row FROM lines WHERE row=?", $lines[$i]);
if ($find_line_in_db->numRows() == 0) {
$db->query("INSERT INTO lines (line_number, row) VALUES (?,?)", $i, $lines[$i]);
echo 'Added the line: ', htmlspecialchars($lines[$i]), '<br>';
}
}
}
//query all rows in the database and check if posted lines array value is in the [row] column, delete rows changed/not found accordingly
$rows = "";
$fetch_all_lines = $db->query("SELECT row FROM lines");
while ($all_lines = $fetch_all_lines->fetchRow()) {
if (!in_array($all_lines['row'], $lines)) {
$rows = $all_lines['row'];
$db->query("DELETE FROM lines WHERE row=?", $all_lines['row']);
echo 'Deleted the line: ', htmlspecialchars($lines[$i]), '<br>';
}
}
}
So to visualize what I'm doing imagine two arrays:
$lines = ["line1", "line2", "line3changed"] //textarea lines posted
$db_lines = ["line1", "line2", "line3"]
//$db_lines = the current rows in lines db table
// line_number | row
// 0 | line1
// 1 | line2
// 2 | line3
//outputs
//Added the line: line3changed
//Deleted the line: line3
So basically what I'm asking is, am I doing this the right way?
I would have over 10,000 lines that would be altered every hour in this table and am unsure if I'm doing it the correct way