Show uploaded files from MySQL based on file extension and number of files with hyperlinks

Question

I'm trying to load previously uploaded files into the MySQL database. Files are stored on folder while submitting form where preview page will contain hyperlinks to those files (something like attachments)

Problem that I'm getting is:

if I select only 1 or more files with specific file extension to be uploaded, then I will get listed correct number of files and hyperlinks will work.
if I select 2 files with 2 different file extensions (PDF and JPG), then I will get 4 hyperlinks shown.
if I select 4 files with 2 different file extensions (PDF and JPG), them I will get 8 hyperlinks shown

Probably error is with foreach command in my code.

Please check attached picture on how it looks after loading files back.

preview of results

index.php

<?php
include('dbconfig.php');

date_default_timezone_set('Europe/Oslo');
$loaddate1 = date('Y-m-d');
$loadtime1 = date('H-i-s');
?>

<form name="myForm" id="myForm" class="workshop add" action="" method="post" enctype="multipart/form-data">
<input id="" type="text" name="toolsn" value="">
<input id="" type="text" name="actioninsertdate" value="<?php echo $loaddate1;?>" readonly> <br/>
<input id="" type="text" name="actioninserttime" value="<?php echo $loadtime1;?>" readonly><br/>
<input type="file" name="files[]" multiple/><br/>
<button type="submit" name="submit">Submit</button></div>
</form>



<?php


if(isset($_POST['submit']))
{
$path = 'images/';
$toolsn = $_POST['toolsn'];
$actioninsertdate = $_POST['actioninsertdate'];
$actioninserttime = $_POST['actioninserttime'];
$errors= array();
foreach($_FILES['files']['tmp_name'] as $key => $tmp_name ){
    $file_name = $key.$_FILES['files']['name'][$key];
    $file_basename = substr($file_name, 0, strripos($file_name, '.')); // get file extension
    $file_ext = substr($file_name, strripos($file_name, '.')); // get file name
    $file_size =$_FILES['files']['size'][$key];
    $file_tmp =$_FILES['files']['tmp_name'][$key];
    $file_type=$_FILES['files']['type'][$key];
    $newfilename = $toolsn.'-'.$loaddate1.'_'.$loadtime1.'_'.rand(1,20).$file_ext;
    if($file_size > 2097152){
        $errors[]='File size must be less than 2 MB';
    }

    $query="INSERT workshop1 SET toolsn='$toolsn',file='$newfilename',type='$file_type',size='$file_size',actioninsertdate='$actioninsertdate',actioninserttime = '$actioninserttime'"
    or die(mysqli_error ($connection));
    if(empty($errors)==true || empty($toolsn)==false){
        if(is_dir($path.$toolsn)==false){
            mkdir("images/$toolsn", 0700);      // Create directory if it does not exist
        }
        if(is_dir("images/$toolsn/".$file_name)==false){
            //$newfilename = $toolsn.'-'.$loaddate1.'_'.$loadtime1.'_'.rand(1,4).$file_ext;
            move_uploaded_file($file_tmp,"images/$toolsn/".$newfilename);
        }else{                                  // rename the file if another one exist
            //$newfilename = $toolsn.'-'.$loaddate1.'_'.$loadtime1.'_'.rand(1,4);
            $new_dir="images/$toolsn/".time().$newfilename;
             rename($file_tmp,$new_dir) ;
        }
     mysqli_query($connection, $query);
    }else{
            print_r($errors);
    }
}

}
?>

read.php

<?php
include('dbconfig.php');
$sqls=mysqli_query($connection, "
SELECT
GROUP_CONCAT(DISTINCT toolsn SEPARATOR '<br />') as toolsn,
GROUP_CONCAT(DISTINCT type SEPARATOR '<br />') as type,
GROUP_CONCAT(DISTINCT file ORDER BY type SEPARATOR '<br />') as file,
GROUP_CONCAT(DISTINCT actioninsertdate SEPARATOR '<br />') as actioninsertdate,
GROUP_CONCAT(DISTINCT actioninserttime SEPARATOR '<br />') as actioninserttime
from workshop1
group by actioninserttime");

//get feedback why database not working
if (!$sqls) {
printf("Error: %s\n", mysqli_error($connection));
exit();
}
?>

<table id="table" class="table table-hover table-responsive">
<thead class="thead-default">
    <tr>
    <th>Toolsn</th>
    <th>Date added</th>
    <th>Time added</th>
    <th>Attachment</th>
    </tr>
</thead>
<?php
echo '<tbody id="tbody"><tr>';
while ($row = mysqli_fetch_array($sqls)) {
   echo '<td>'.$row['toolsn'].'</td>';
   echo '<td>'.$row['actioninsertdate'].'</td>';
   echo '<td>'.$row['actioninserttime'].'</td>';

echo '<td>';
$eachtoolsn=explode('<br />',$row['toolsn']);
$eachfile=explode('<br />',$row['file']);
$eachtype=explode('<br />',$row['type']);

foreach($eachfile as $listfile) {
//echo $listfile;

    foreach($eachtoolsn as $key => $listoolsn) {
    //echo [$key];
    }

    foreach($eachtype as $listtype) {
        if ($listtype === 'image/jpeg'){
            echo '<a href="images/'.$row['toolsn'].'/'.$listfile.'" target="_blank"><img src="images/'.$row['toolsn'].'/'.$listfile.'" width="48" height="48"></a>';
        } elseif ($listtype === 'application/pdf'){
            echo '<a href="images/'.$row['toolsn'].'/'.$listfile.'" target="_blank"><img src="images/icon-pdf.png" width="48" height="48"></a>';
        }
    }

echo '</td>';
echo '</tr>';}
echo '</tbody></table>';
?>

You code is **[SQL Injection vulnerable](https://stackoverflow.com/questions/601300/what-is-sql-injection)**, don't use it in production and learn how to protect yourself. — Blag, Oct 08 '17 at 11:21

score 0 · Answer 1 · answered Oct 08 '17 at 11:46

0

Your logic is not good :

<?php
foreach($eachfile as $listfile) {
    foreach($eachtype as $listtype) { // <= your bug is here
        if ($listtype === 'image/jpeg'){
            echo '<a href="images/'.$row['toolsn'].'/'.$listfile.'" target="_blank"><img src="images/'.$row['toolsn'].'/'.$listfile.'" width="48" height="48"></a>';
        } elseif ($listtype === 'application/pdf'){
            echo '<a href="images/'.$row['toolsn'].'/'.$listfile.'" target="_blank"><img src="images/icon-pdf.png" width="48" height="48"></a>';
        }
    }
}

Here is the new version :

<?php
foreach($eachfile as $key => $listfile) {
    if ($eachtype[$key] === 'image/jpeg')
        echo '<a href="images/'.$row['toolsn'].'/'.$listfile
        .'" target="_blank"><img src="images/'.$row['toolsn']
        .'/'.$listfile.'" width="48" height="48"></a>';
    elseif ($eachtype[$key] === 'application/pdf')
        echo '<a href="images/'.$row['toolsn'].'/'.$listfile
        .'" target="_blank"><img src="images/icon-pdf.png" '
        .'width="48" height="48"></a>';
}

The $key is the index of your line in the array you make with explode()

$eachfile = array(
    0=>'file1',
    1=>'file2',
)

$eachtype = array(
    0=>'PDF',
    1=>'JPG',
)

So you just use the key of you file to get the good line of the type.

answered Oct 08 '17 at 11:46

Blag

5,394
2
18
43

By using your code I'm getting an error: Notice: Undefined offset: 1, or Notice: Undefined offset: 2? Do you know why? – stvlada Oct 08 '17 at 14:37
@stvlada what do you have in $eachfile and $eachtype ? (use var_dump) – Blag Oct 08 '17 at 14:39
for first line from picture above where I have two images stored in mysql, my var_dump looks like array: (size=2) 0 => string 'ASM5000-006-2017-10-08_11-05-42_18.jpg' (length=38) 1 => string 'ASM5000-006-2017-10-08_11-05-42_16.jpg' (length=38) – stvlada Oct 08 '17 at 14:47
@stvlada ok for the var_dump of $eachfile, what about the one of $eachtype ? – Blag Oct 08 '17 at 14:54
depends on the line if I have one or two file extensions uploaded: array (size=1) 0 => string 'image/jpeg' (length=10) OR array (size=2) 0 => string 'application/pdf' (length=15) 1 => string 'image/jpeg' (length=10) – stvlada Oct 08 '17 at 15:02
@stvlada if you just switch your foreach for mine, you still have the error ? – Blag Oct 08 '17 at 15:06
Yes, if I replace my code that you have quoted with yours I'm start getting error about Undefined offset. If I continue using my code, no such error, but I'm getting problems like on above image attached. – stvlada Oct 08 '17 at 15:09

score 0 · Answer 2 · answered Oct 08 '17 at 20:52

I have managed to get distinguished file previews if only one extension is uploaded. If I upload multiple files with 2 different extensions then I can't make code to work where uploaded PDF files will get PDF icon on preview. Either all files can have the same icon (different code) or this shown on image. For some reason this is happening when GROUP_CONCAT is used to gather one or more file grouped by "actioninsertime".

Image preview:

code used:

$eachfile=explode('<br />',$row['file']);
$eachtype=explode('<br />',$row['type']);
$eachextension=explode('<br />',$row['extension']);

foreach ($eachtype as $listtype){}
foreach ($eachextension as $listextension){}


foreach ($eachfile as $listfile){
if ($listextension === 'pdf') {
echo '<a href="images/'.$row['toolsn'].'/'.$listfile.'" target="_blank"><img src="images/icon-pdf.png" width="48" height="48"></a>';}
}

foreach ($eachfile as $listfile){
if ($listextension === 'jpg') {
echo '<a href="images/'.$row['toolsn'].'/'.$listfile.'" target="_blank"><img src="images/'.$row['toolsn'].'/'.$listfile.'" width="48" height="48"></a>';}
}

Show uploaded files from MySQL based on file extension and number of files with hyperlinks

index.php

read.php

2 Answers2