How to upload and save a file in db using PHP, MySQL and mysqli_query?

Question

I'm trying to ulpoad a file using PHP and save it in a table, but it returns as failed. The picture does upload, but something is wrong with my mysqli_query or something.

HTML

<form action="server.php" enctype="multipart/form-data" method="post"
id="myForm">
          <select name="type">
            <option value="">Välj typ här</option>
            <option value="photo">Photo</option>
            <option value="video">Video</option>
            <option value="audio">Audio</option>
          </select>
          <input type="text" name="title" placeholder="Titel" class="title">
          <input type="file" name="media" class="btn">
          <input type="submit" value="Spara media" class="btn">
        </form>

Javascript:

$("#myForm").submit(function(e) {
    // Förhindrar att vi skickas iväg till en ny sida (standardhändelsen)
    e.preventDefault();
    // Hämtar formulärsdata (värde från drop-down menyn, samt filen)
    var formData = new FormData(this);

    // Gör ett ajax-anrop
    $.ajax({
        url: $(this).attr("action"), // Till adressen "server.php"
        type: $(this).attr("method"), // Med metoden "post"
        data: formData, // Vår data vi skickar med
        dataType: "JSON", // Hur vi ska tolka den data vi får tillbaka (som JSON)
        cache: false, // Vi tillåter inte att webbläsaren att cacha några resultat
        contentType: false, // Vi vill inte att jQuery ska bestämma hur vårt
        innehåll ska tolkas
        processData: false // Vi tillåter inte att jQuery att processa vår data (som strängar)
    }).done(function (data) {
        // Om vi får ett lyckat svar
        console.log(data);
    }).fail(function(data){
        // Om vi får ett misslyckat svar
        console.log(data);
    });
});

PHP:

$servername = "*hidden*";
$username = "*hidden*";
$password = "*hidden*";
$db = new mysqli($servername, $username, $password);

if (!$db) {
 echo "Error: Unable to connect to MySQL." . PHP_EOL;
 echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
 echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
 exit;
}

$return = new ArrayObject();

if(isset($_FILES['media']['tmp_name'])){
$path = $_POST['type']."/";
$fileName = $path.rand().$_FILES['media']['name'];

if(move_uploaded_file($_FILES['media']['tmp_name'], $fileName)){
 $title = "";
 if(isset($_POST['title'])){
  $title = $_POST['title'];
 }
 if(mysqli_query($db, "INSERT INTO media (title, type, path) VALUES 
 ('".$title."', '".$_POST['type']."', '".$fileName."')")){
  $return['success'] = true;
  $return['path'] = $fileName;
  $return['title'] = $title;
  $return['message'] = "File uploaded and saved in db";
  echo json_encode($return);
 }else{
  $return['success'] = false;
  $return['path'] = $fileName;
  $return['title'] = $title;
  $return['message'] = "File uploaded but not saved in db";
  echo json_encode($return);
 }

 }else{
  $return['success'] = false;
  $return['message'] = "Kunde inte ladda upp filen";
  echo json_encode($return);
 }
  }

if(isset($_GET['action']) and $_GET['action'] == "getMedia"){
if(isset($_GET['type'])){
$res = mysqli_query($db, "SELECT * FROM media WHERE type = 
'".$_GET['type']."' ORDER BY id DESC");
}else{
$res = mysqli_query($db, "SELECT * FROM media ORDER BY id DESC");
}
$media = new ArrayObject();
while($row = mysqli_fetch_assoc($res)){
$m = new ArrayObject();
$m['path'] = $row['path'];
$m['type'] = $row['type'];
$m['title'] = $row['title'];
$m['timestamp'] = $row['timestamp'];
//$m['id'] = $row['id'];
$media['files'][] = $m;
}
echo json_encode($media);
}


?>

Here is the console message received:

Edit: This is where the error message is delivered to the console, found in the php file

}else{
  $return['success'] = false;
  $return['path'] = $fileName;
  $return['title'] = $title;
  $return['message'] = "File uploaded but not saved in db";
  echo json_encode($return);
 }

Second Edit.
I added this line to my else:
echo("Error description: " . mysqli_error($db));
Which produced the following messsage:
"Error description: No database selected{"success":false,"path":"photo/2969cloak.JPG","title":"Hej","message":"File uploaded but not saved in db"}"

**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jan 28 '19 at 01:04
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and ideally should not be used in new code. — tadman, Jan 28 '19 at 01:04
Note: A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so any mistakes made aren’t easily ignored. Many return values cannot be ignored, you must pay attention to each one. Exceptions don’t require individual checking, they can be caught at a higher level in the code. — tadman, Jan 28 '19 at 01:05
@Marika Use `$errors = $mysqli->error_list` in the `else` block to get the errors cause by `insert` statement. Log the value of `$error` using `file_put_contents` or `print_r` to see what caused the issue; — Nishant, Jan 28 '19 at 04:59
@NishantSaini Thank you! By doing a verison of this, I found out that the db was not chosen. — Marika Moreau, Jan 28 '19 at 18:48
Glad it helped. Other than the suggestions in above comments I would also recommend you use try catch in such cases where there is probability of occurrence of exception. — Nishant, Jan 29 '19 at 15:12

score 1 · Answer 1 · answered Jan 28 '19 at 16:21

1

It was as simple as this, actually. I hadn't chosen the database, only the host. The reason for this is that, in my case the username and database is the same since it's a school server. Thank you all!

answered Jan 28 '19 at 16:21

Marika Moreau

51
8

Chad Hamilton Frederick · Answer 2 · 2019-01-28T02:38:30.590

0

I don't see where you select a database. you can use mysqli_select_db ($db, "Database_Name")

I usually separate my query generation from query execution, to help debug errors in the SQL. You can also return the raw query and try to run it directly in MySQL eg:

$query="INSERT INTO media (title, type, path) VALUES ('".$title."', '".$_POST['type']."', '".$fileName."')";

$return['query'] =  $query;

Also, you can catch the error message:

if(mysqli_query($db, $query)){
     //success
     $return['error'] = "success";
     ...

} else {
    $return['error'] = "Error: ". $query ."<br /> \n". mysqli_error($db) ."<br /> \n";
    ...

}

edited Jan 28 '19 at 02:38

answered Jan 28 '19 at 01:27

Chad Hamilton Frederick

414
4
10

It was as simple as this, actually. I hadn't chosen the database, only the host. The reason for this is that, in my case the username and database is the same since it's a school server. Thank you – Marika Moreau Jan 28 '19 at 16:20

How to upload and save a file in db using PHP, MySQL and mysqli_query?

2 Answers2