Jump to content

Cordion

New Members
  • Content Count

    1
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Cordion

  • Rank
    Newbie
  1. 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
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.