MYSQLi PHP Basics without prepared statements

MySQLi and PDO are object oriented and support Prepared Statements (also support Transactions, Stored Procedures and more). Prepared Statements are very important for web application security, as they protect from SQL injection. Using Prepared Statements you do not have to escape strings before insert them in Database. Moreover, PDO offers support for many databases (not only MySQL).

Establish a database connection

$DBServer = 'localhost';  // or an IP address

$DBUser   = 'root';

$DBPass   = 'your password';

$DBName   = 'dbname';

 $conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);

// check connection

if ($conn->connect_error) {

trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);

}

//select rows

$sql='SELECT * FROM users';

$rs=$conn->query($sql);

  $rows_returned = $rs->num_rows;

echo $rows_returned;  //returns number of rows

//loop through the records

$rs->data_seek(0);

while($row = $rs->fetch_assoc()){

    echo $row['id'] . '<br>';

}

//insert

$sql="INSERT INTO users VALUES ($id)";

if($conn->query($sql) === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $last_inserted_id = $conn->insert_id;

  $affected_rows = $conn->affected_rows;

}

//update 

$sql="UPDATE tbl SET col1_varchar=$v1, col2_number=1 WHERE id>10";

if($conn->query($sql) === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $affected_rows = $conn->affected_rows;

}

//delete

$sql="DELETE FROM tbl WHERE id>10";

if($conn->query($sql) === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $affected_rows = $conn->affected_rows;

}


Remember to escape_string when inserting since you are not using prepared sql statements.

$conn->real_escape_string('col1_value')