PHP PDO and Mysql [closed]

by

Here’s a rough answer which should resolve the injection issue, the non-executing query issue, and the incorrect insert syntax. I don’t have your DB so I can’t confirm this is fully functional but it should be closer..

<?php
// include to get database connection
include_once 'config/db.php';
try{
    $a_id = "SELECT a.id as aid FROM aluno a, utilizador u WHERE a.utilizador_id = u.id
    AND u.nome = ?";
    $stmt = $con->prepare($query);
    $stmt->execute(array($_POST['nome']));
    while ($row = $stmt->fetch_assoc()) {
        $aids[] = $row['aid'];
        $a_id = $row['aid'];
    }
    // what are you doing if there are more than one record?
    // current execution will only have the last id as $a_id
    $prof = 1; 
    $query = "INSERT INTO classificacao(nota, semestre, aluno_id, utilizador_id) 
                VALUES (?, ?, ?, ?)";
    $stmt = $con->prepare($query);
    // execute the query
    if($stmt->execute(array($_POST['nota'], $_POST['semestre'], $a_id, $prof))){
        echo "Product was created.";
    }else{
        echo "Unable to create product.";
    }
}
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>

Notice the insert syntax used here. At the start you define the columns

INSERT INTO classificacao(nota, semestre, aluno_id, utilizador_id)

Then you pass the values in after the values. Each value is separated by a comma.

VALUES (?, ?, ?, ?)

You don’t pass the columns in there. The ? are placeholders for the values going to the DB.

Links for further reading:

How can I prevent SQL injection in PHP?
http://dev.mysql.com/doc/refman/5.6/en/insert.html
https://dev.mysql.com/doc/refman/5.0/en/update.html
http://php.net/manual/en/pdo.prepared-statements.php

More Related Contents:

  1. PHP / PDO echo data from database into a table
  2. PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)
  3. PDOException SQLSTATE[HY000] [2002] No such file or directory
  4. How to prevent duplicate usernames when people register?
  5. How can I use PDO to fetch a results array in PHP?
  6. Error when preparing a multiple insert query
  7. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  8. MySQL check if a table exists without throwing an exception
  9. pdo prepared statements with wildcards
  10. Insert multiple rows with PDO prepared statements
  11. PDO + MySQL and broken UTF-8 encoding
  12. Do SQL connections opened with PDO in PHP have to be closed
  13. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  14. php code to test pdo is available?
  15. PDO fetchAll group key-value pairs into assoc array
  16. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  17. What is the advantage of using try {} catch {} versus if {} else {}
  18. Alternative for mysql_num_rows using PDO
  19. ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
  20. Setting a connect timeout with PDO
  21. Unknown database error in PHP but it exists in PHPMyAdmin
  22. How do detect that transaction has already been started?
  23. Binding parameters for WHERE IN clause with PDO [duplicate]
  24. return one value from database with mysql php pdo
  25. Trying to access array offset on value of type bool
  26. return multiple response data in one response
  27. How to get a query error from prepare() in PDO PHP?
  28. How can I pass an array of PDO parameters yet still specify their types?
  29. PHP PDO how to run a multiple query request?
  30. PHP: mysql v mysqli v pdo [closed]

Leave a Comment