EDIT 1:
<?php
// Import db file.
require_once 'dbDetails.php';
// Upload folder.
$upload_path = 'uploads/';
// Get the server ip.
$server_ip = gethostbyname(gethostname());
// Create upload url.
$upload_url = 'http://' . $server_ip . '/userapp/' . $upload_path;
//response array
$response = array();
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
// Validate request parameters.
if (isset($_POST['name']) and isset($_FILES['image']['name'])) {
// Request values.
$name = isset($_POST['name']) ? $_POST['name'] : '';
//************************************************************************
// NB: CHANGE IT TO A VALUE FROM THE FORM, OR DELETE IT FROM HERE AND SQL.
// IF YOU DON'T INSERT THIS, THEN MAKE SURE THAT THE FIELD "campo_correo"
// ACCEPTS NULL VALUES, OR GIVE IT A DEFAULT VALUE IN DB (LIKE THIS: '').
$correo = isset($_POST['correo']) ? $_POST['correo'] : '';
//************************************************************************
$nombre = isset($_POST['nombre']) ? $_POST['nombre'] : '';
$apellido = isset($_POST['apellido']) ? $_POST['apellido'] : '';
$telefono = isset($_POST['telefono']) ? $_POST['telefono'] : '';
$categoria = isset($_POST['categoria']) ? $_POST['categoria'] : '';
$titulo = isset($_POST['titulo']) ? $_POST['titulo'] : '';
$descripcion = isset($_POST['descripcion']) ? $_POST['descripcion'] : '';
$publico = isset($_POST['publico']) ? $_POST['publico'] : '0';
$terminos = isset($_POST['terminos']) ? $_POST['terminos'] : '0';
$latitud = isset($_POST['latitud']) ? $_POST['latitud'] : '0';
$longitud = isset($_POST['longitud']) ? $_POST['longitud'] : '0';
// Get file info from request.
$fileinfo = pathinfo($_FILES['image']['name']);
// Get file extension.
$extension = $fileinfo['extension'];
// Build file url to insert in database.
$file_url = $upload_url . getFileName() . '.' . $extension;
// Build file path to upload to server.
$file_path = $upload_path . getFileName() . '.' . $extension;
try {
// Save the file in the directory.
$movedUploadedFile = move_uploaded_file($_FILES['image']['tmp_name'], $file_path);
if (!$movedUploadedFile) {
throw new Exception('The file could not be moved.');
}
// Connect to database.
$con = mysqli_connect(HOST, USER, PASS, DB);
if (!$con) {
throw new Exception('Could not connect to database');
}
// Insert record into table.
$sql = "INSERT INTO `userapp`.`reportes2` (`url`, `name`, `campo_correo`, `campo_nombre`, `campo_apellido`, `campo_telefono`, `campo_categoria`, `campo_titulo`, `campo_descripcion`, `campo_publico`, `campo_terminos`, `campo_latitud`, `campo_longitud`) VALUES ('$file_url', '$name', '$correo', '$nombre', '$apellido', '$telefono', '$categoria', '$titulo', '$descripcion', '$publico', '$terminos', '$latitud', '$longitud')";
$inserted = mysqli_query($con, $sql);
if (!$inserted) {
throw new Exception('The insert statement could not be executed!');
}
// Close the database connection.
$closed = mysqli_close($con);
if (!$closed) {
throw new Exception('The database connection can not be closed!');
}
// Fill response array with values.
$response['error'] = false;
$response['url'] = $file_url;
$response['name'] = $name;
} catch (Exception $e) {
$response['error'] = true;
$response['message'] = $e->getMessage();
}
} else {
$response['error'] = true;
$response['message'] = 'Please choose a file.';
}
// Display response.
echo json_encode($response);
} else {
$response['error'] = true;
$response['message'] = 'No post method used!';
// Display response.
echo json_encode($response);
}
/**
* We are generating the file name, so this method will
* return a file name for the image to be upload.
*
* @return int
* @throws Exception
*/
function getFileName() {
try {
// Connect to database.
$con = mysqli_connect(HOST, USER, PASS, DB);
if (!$con) {
throw new Exception('Could not connect to database!');
}
// Select max id.
$sql = "SELECT max(id) as id FROM reportes2";
$mysqliResult = mysqli_query($con, $sql);
if (!$mysqliResult) {
throw new Exception('The select statement failed!');
}
// Fetch max id.
$result = mysqli_fetch_array($mysqliResult);
// Close database connection.
$closed = mysqli_close($con);
if (!$closed) {
throw new Exception('The database connection can not be closed!');
}
// Validate results.
if ($result['id'] == null) {
return 1;
} else {
return ++$result['id'];
}
} catch (Exception $e) {
echo $e->getMessage();
exit();
}
}
EDIT 2 - How to prepare and run queries using mysqli library:
Option 1: Using mysqli_stmt_get_result() + mysqli_fetch_array():
<?php
/*
* Run prepared db queries.
*
* Uses:
* - mysqli_prepare()
* - mysqli_stmt_bind_param()
* - mysqli_stmt_execute()
* - mysqli_stmt_get_result()
* - mysqli_fetch_array()
*/
try {
$username = 'Hello';
$password = 'World';
//---------------------------------------------------------
// Connect to db.
//---------------------------------------------------------
$conn = mysqli_connect('<host>', '<user>', '<pass>', '<db>');
if (!$conn) {
throw new Exception('Connect error: ' . mysqli_connect_errno() . ' - ' . mysqli_connect_error());
}
//---------------------------------------------------------
// Sql statement.
//---------------------------------------------------------
$query = "SELECT * FROM users WHERE username = ? AND password = ?";
//---------------------------------------------------------
// Prepare sql statement.
//---------------------------------------------------------
$stmt = mysqli_prepare($conn, $query);
if (!$stmt) {
throw new Exception('The sql statement can not be prepared!');
}
//---------------------------------------------------------
// Bind variables to the prepared statement as parameters.
//---------------------------------------------------------
$bound = mysqli_stmt_bind_param($stmt, 'ss', $username, $password);
if (!$bound) {
throw new Exception('The variables could not be bound to the prepared statement!');
}
//---------------------------------------------------------
// Execute the prepared statement.
//---------------------------------------------------------
$executed = mysqli_stmt_execute($stmt);
if (!$executed) {
throw new Exception('The prepared statement could not be executed!');
}
//---------------------------------------------------------
// Get the result set from the prepared statement.
//---------------------------------------------------------
$result = mysqli_stmt_get_result($stmt);
if (!$result) {
throw new Exception(mysqli_error($conn));
}
//---------------------------------------------------------
// Get the number of rows in statements result set.
//---------------------------------------------------------
$rows = mysqli_num_rows($result);
if ($rows > 0) {
//---------------------------------------------------------
// Read the result set.
//---------------------------------------------------------
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
if (!isset($row)) {
echo 'No records returned!';
exit();
}
echo 'Login successful: ' . $row['username'] . '/' . $row['password'];
} else {
echo 'Invalid username/password. Please check and retry login.';
}
//-----------------------------------------------------------
// Frees stored result memory for the given statement handle.
//-----------------------------------------------------------
mysqli_stmt_free_result($stmt);
//---------------------------------------------------------
// Close db connection.
//---------------------------------------------------------
$closed = mysqli_close($conn);
if (!$closed) {
throw new Exception('The database connection can not be closed!');
}
} catch (Exception $exception) {
echo '<pre>' . print_r($exception, true) . '</pre>';
exit();
}
Option 2: Using mysqli_stmt_store_result() + mysqli_stmt_bind_result() + mysqli_stmt_fetch():
<?php
/*
* Run prepared db queries.
*
* Uses:
* - mysqli_prepare()
* - mysqli_stmt_bind_param()
* - mysqli_stmt_execute()
* - mysqli_stmt_store_result()
* - mysqli_stmt_bind_result()
* - mysqli_stmt_fetch()
*/
try {
$username = 'Hello';
$password = 'World';
//---------------------------------------------------------
// Connect to db.
//---------------------------------------------------------
$conn = mysqli_connect('<host>', '<user>', '<pass>', '<db>');
if (!$conn) {
throw new Exception('Connect error: ' . mysqli_connect_errno() . ' - ' . mysqli_connect_error());
}
//---------------------------------------------------------
// Sql statement.
//---------------------------------------------------------
$query = "SELECT * FROM users WHERE username = ? AND password = ?";
//---------------------------------------------------------
// Prepare sql statement.
//---------------------------------------------------------
$stmt = mysqli_prepare($conn, $query);
if (!$stmt) {
throw new Exception('The sql statement can not be prepared!');
}
//---------------------------------------------------------
// Bind variables to the prepared statement as parameters.
//---------------------------------------------------------
$bound = mysqli_stmt_bind_param($stmt, 'ss', $username, $password);
if (!$bound) {
throw new Exception('The variables could not be bound to the prepared statement!');
}
//---------------------------------------------------------
// Execute the prepared statement.
//---------------------------------------------------------
$executed = mysqli_stmt_execute($stmt);
if (!$executed) {
throw new Exception('The prepared statement could not be executed!');
}
//---------------------------------------------------------
// Transfer the result set from the prepared statement.
//---------------------------------------------------------
$stored = mysqli_stmt_store_result($stmt);
if (!$stored) {
throw new Exception('The result set from the prepared statement could not be transfered!');
}
//---------------------------------------------------------
// Get the number of rows in statements' result set.
//---------------------------------------------------------
$rows = mysqli_stmt_num_rows($stmt);
if ($rows > 0) {
//---------------------------------------------------------
// Bind result set columns to corresponding variables.
//---------------------------------------------------------
$bound = mysqli_stmt_bind_result($stmt, $resId, $resUsername, $resPassword);
if (!$bound) {
throw new Exception('The result set columns could not be bound to the variables');
}
//--------------------------------------------------------------------
// Fetch results from the prepared statement into the bound variables.
//--------------------------------------------------------------------
while (mysqli_stmt_fetch($stmt)) {
echo 'Successfully returned data:<br/><br/>';
echo 'ID: ' . $resId . '<br/>';
echo 'Username: ' . $resUsername . '<br/>';
echo 'Password: ' . $resPassword . '<br/>';
}
} else {
echo 'Invalid username/password. Please check and retry login!';
}
//-----------------------------------------------------------
// Free stored result memory for the given statement handle.
//-----------------------------------------------------------
mysqli_stmt_free_result($stmt);
//---------------------------------------------------------
// Close db connection.
//---------------------------------------------------------
$closed = mysqli_close($conn);
if (!$closed) {
throw new Exception('The database connection can not be closed!');
}
} catch (Exception $exception) {
echo '<pre>' . print_r($exception, true) . '</pre>';
exit();
}
Nota bene:
Trying to use mysqli_stmt_store_result() together with mysqli_stmt_get_result() will lead to errors.
