0

I know this is common to ask but I'm newbie in PHP, The problem I've encountered is that the multiple Insert queries do not work in my current code, it works if I've removed at least one query Insert but what if I have multiple queries and CRUD it to another table, I just want to combine query when inserting data to different table, Any help can do

function signUp($table, $id_no, $middlename, $lastname)
{
    $id_no = $this->prepareData($id_no);
    $middlename = $this->prepareData($middlename);
    $lastname = $this->prepareData($lastname);
    $table2 = "users";
    $userrole = 0;
    $username = "test_username";
    $password = "test_password";

    $this->sql =
        "INSERT INTO " . $table . " (id_no, middle_name, last_name) VALUES ('" . $id_no . "','" . $middlename . "','" . $lastname . "')" ; //IF I removed this first query it will work to second table
        "INSERT INTO " . $table2 . " (user_role, username, password) VALUES ('" . $userrole . "','" . $username . "','" . $password . "')";
    if (mysqli_query($this->connect, $this->sql)) {
        return true;
    } else return false;
}

Updated

    function userDetailsTable($table, $firstname,$middlename, $lastname, $sex, $id_no,  $email, $contactno, $address)
{
    $firstname = $this->prepareData($firstname);
    $middlename = $this->prepareData($middlename);
    $lastname = $this->prepareData($lastname);
    $sex = $this->prepareData($sex);
    $id_no = $this->prepareData($id_no);
    $email = $this->prepareData($email);
    $contactno = $this->prepareData($contactno);
    $address = $this->prepareData($address);

    $result =sql2("SELECT MAX(id) FROM users");  // returns error
    
    $this->sql =
        "INSERT INTO " . $table . " (user_id,first_name, middle_name, last_name, sex, id_no, email, contact_no, address) VALUES ('" . $result . "','" . $firstname . "','" . $middlename . "','" . $lastname . "','" . $sex . "','" . $id_no . "','" . $email . "','" . $contactno . "','" . $address . "')" ;
    if (mysqli_query($this->connect, $this->sql)) {
        return true;
    } else return false;
}
CarlJade
  • 91
  • 1
  • 7
  • Yes, multiple query doesn't work. So you have to run two queries. As simple as that – Your Common Sense May 18 '22 at 16:08
  • 1) Just call mysqli_query twice - once for each insert. Wrap them in a transaction if you want to be sure they both succeed or both fail. 2) BUT... you should not be building queries in this insecure way anyway. Use prepared statements and parameters, always (and just execute two statements, of course :-) ) – ADyson May 18 '22 at 16:08
  • 1
    **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, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. – ADyson May 18 '22 at 16:10
  • https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. – ADyson May 18 '22 at 16:10
  • 2
    Also, please don't store passwords in plain text - that is another security risk. Learn about [password hashing](https://www.php.net/manual/en/faq.passwords.php) instead. See also [How to use PHP's password_hash to hash and verify passwords](https://stackoverflow.com/questions/30279321/how-to-use-phps-password-hash-to-hash-and-verify-passwords) – ADyson May 18 '22 at 16:10
  • @YourCommonSense Yes I tried that but it doesn't work please see my updated code above on how did I implement it, is there something wrong? – CarlJade May 18 '22 at 22:14
  • @ADyson I have my updated code above I tried creating two sql but still it didn't work, and those are just dummy data – CarlJade May 18 '22 at 22:16
  • Well...you're still only executing one query there. Your code never does anything with $this->sql2! – ADyson May 18 '22 at 22:17
  • And it's still horrendously insecure. Follow the advice above and start using prepared statements and parameters, urgently. – ADyson May 18 '22 at 22:18
  • @ADyson I just read the link above but I didn't understand, should I make two functions separately? – CarlJade May 18 '22 at 22:20
  • `should I make two functions separately`...yes, that's what we said in the very first comments. Start by making it run mysqli_query twice - once for each SQL statement. Then move onto reimplementing your queries safely as per the simple examples at https://phpdelusions.net/mysqli#prepare – ADyson May 18 '22 at 22:25
  • @ADyson but what if I have two queries, one is `retrieving data` which use **select** and then getting the max id of the data then insert it, I have update code above need help – CarlJade May 19 '22 at 00:01
  • If you have two queries to run, then you prepare and execute two statements. It's as simple as that. I have no idea what your updated code is doing - what is the `sql2()` function? – ADyson May 19 '22 at 00:07
  • Please do by this example, https://stackoverflow.com/a/62369599/285587 – Your Common Sense May 19 '22 at 07:16

0 Answers0