0

I would like to ask if how can the SQL rearrange the database simultaneously after there is an new input.

Sample data:

Sample Data

I want to follow the Hybrid FCFS + Priority where 1:1 ratio, 1 regular and 1 priority simultaneously.

The identifier of Priority here is when the Priority = 1 of that input.

Here the sample PHP script code but I am stucked here, I don't know if I will modify the code in PHP or it is directly statemented in SQL database

    function save_queueSAMPLE(){
        extract($_POST);

        if($priority == 1){
        }
        else{
            $sql = "INSERT INTO `queue_listSAMPLE` (`customer_name`,`priority`) VALUES('{$customer_name}','{$priority}')";
            $save = $this->query($sql);
    
            if($save) {
                $resp['status'] = 'success';
                $resp['id'] = $this->query("SELECT last_insert_rowid()")->fetchArray()[0];
            } else {
                $resp['status'] = 'failed';
                $resp['msg'] = "An error occured. Error: ".$this->lastErrorMsg();
            }
    
            return json_encode($resp);
        }
    }
ADyson
  • 57,178
  • 14
  • 51
  • 63
Angelo
  • 1
  • 1
  • 1
    Rearrange? Give us before and after versions of sample table data. – jarlh Feb 08 '23 at 15:01
  • `I want to follow the Hybrid FCFS + Priority where 1:1 ratio, 1 regular and 1 priority simultaneously`...you're going to need to properly explain what this means, it doesn't have any obvious relationship to the data you're talking about, and as the comment above notes, it isn't clear what the final result of your code is supposed to be, or what the criteria are, in relation to what's in the table. – ADyson Feb 08 '23 at 15:03
  • 1
    P.S. **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, and some examples writing your queries safely using PHP. **Never** insert unparameterised data directly into SQL. The way your code is now, someone could steal, change, or delete data. As you seem to be using SQLite, see also https://stackoverflow.com/questions/18485026/using-prepared-statements-with-sqlite3-and-php – ADyson Feb 08 '23 at 15:06
  • I will add for example Name: JOHN with priority. Therefore, it will be added to the fifth row and the existing firth will be moved to the sixth. Since it follows the 1-0-1-0 priority. – Angelo Feb 08 '23 at 15:11
  • It's a database, not a spreadsheet. Database tables don't have any inherent ordering. You can, separately, write a query which _displays_ them in the desired order based on the data within the table. That would be a more normal approach in developing an application. The table simply stores the data, the order of the rows is intedeterminate. – ADyson Feb 08 '23 at 15:17

0 Answers0