PDO read BLOB files

[seularts]

I'm trying to find a way to output whatever BLOB files are inserted into a silly database. In my code when I query the CV column I get all sorts of weird symbols. Can you guys please help me fix my code?

    $crud->dataview($query);
        }else{
        $query = "SELECT id, from_unixtime(cheie_wp) as datetime, nume, prenume, email, telefon, oras, denumiri_job, cv FROM jobselection_data_old ORDER BY id DESC";
        $records_per_page=10;
        $newquery = $crud->paging($query,$records_per_page);
        $crud->dataview($newquery);

public function dataview($query)
    {
        $stmt = $this->db->prepare($query);
        $stmt->execute();

        if($stmt->rowCount()>0)
        {
            while($row=$stmt->fetch(PDO::FETCH_ASSOC))
            {

                $date_in = $row['datetime'];
                $date_out = explode(' ', $date_in);

                ?>
                <tr>
                <td><?php echo $date_out[0]; ?></td>
                <td><?php print($row['nume']); ?></td>
                <td><?php print($row['prenume']); ?></td>
                <td><?php print($row['email']); ?></td>
                <td><?php print($row['telefon']); ?></td>
                <td><?php print($row['oras']); ?></td>
                <td><?php print($row['denumiri_job']); ?></td>
                <td><?php print($row['cv']); ?></td>
                <td align="center">
                <a href="edit-data.php?edit_id=<?php print($row['user_id']); ?>"><i class="glyphicon glyphicon-edit"></i></a>
                </td>
                <td align="center">
                <a href="delete.php?delete_id=<?php print($row['user_id']); ?>"><i class="glyphicon glyphicon-remove-circle"></i></a>
                </td>
                </tr>
                <?php
            }
        }else{ ... }
}

[seularts]

EDIT:

I'm trying to find a way to output whatever BLOB files are inserted into a silly database. In my code when I query the CV column I get all sorts of weird symbols. Can you guys please help me fix my code?

    $crud->dataview($query);
        }else{
        $query = "SELECT id, from_unixtime(cheie_wp) as datetime, nume, prenume, email, telefon, oras, denumiri_job, cv FROM jobselection_data_old ORDER BY id DESC";
        $records_per_page=10;
        $newquery = $crud->paging($query,$records_per_page);
        $crud->dataview($newquery);

public function dataview($query)
    {
        $stmt = $this->db->prepare($query);
        $stmt->execute();

        if($stmt->rowCount()>0)
        {
            while($row=$stmt->fetch(PDO::FETCH_ASSOC))
            {

                $date_in = $row['datetime'];
                $date_out = explode(' ', $date_in);

                ?>
                <tr>
                <td><?php echo $date_out[0]; ?></td>
                <td><?php print($row['nume']); ?></td>
                <td><?php print($row['prenume']); ?></td>
                <td><?php print($row['email']); ?></td>
                <td><?php print($row['telefon']); ?></td>
                <td><?php print($row['oras']); ?></td>
                <td><?php print($row['denumiri_job']); ?></td>
                <td align="center"><?php 

            if($row['cv'] != NULL) { ?>

                <a href="view.php?id=<?php print ($row['id']) ?>">view</a>

            <?php }else{

                echo '-';

            }

            ?></td>
                <td align="center">
                <a href="edit-data.php?edit_id=<?php print($row['user_id']); ?>"><i class="glyphicon glyphicon-edit"></i></a>
                </td>
                <td align="center">
                <a href="delete.php?delete_id=<?php print($row['user_id']); ?>"><i class="glyphicon glyphicon-remove-circle"></i></a>
                </td>
                </tr>
                <?php
            }
        }else{ ... }
}

I have modified my code and added view.php with this code:

    if(isset($_GET['id']))
         {

             $stmt = $DB_con->prepare("SELECT * FROM jobselection_data_old WHERE id=:id");
             $stmt->execute(array(":id"=>$_GET['id']));
             $stmt->bindColumn(1, $lob, PDO::PARAM_LOB);
             $stmt->fetch(PDO::FETCH_BOUND);

            header('content-type: image/jpeg');
            fpassthru($lob);

         }
         ?>

The problem is that it does not generate the image file I have uploaded to the database.

You can check it at https://ns.jobselection.ro/db/ user/pass: test

[cereal]

Hi,

in your view.php code, you have this query:

SELECT * FROM jobselection_data_old WHERE id=:id

Then you bind the first column returned by the wildcard * to the variable $lob, which means that if the table structure as an id column as first column, $lob will receive the value of the id. Change the column order by expliciting the column names:

SELECT image_col, id, ...

Then you should be able to get the correct datum. In addition datetime in your query at line 3, is a reserved word, change it or use backticks.

[seularts]

Well I did modify it:

$stmt = $DB_con->prepare("SELECT cv FROM jobselection_data_old WHERE id=:id");
$stmt->execute(array(":id"=>$_GET['id']));
     $stmt->bindColumn(1, $lob, PDO::PARAM_LOB);
     $stmt->fetch(PDO::FETCH_BOUND);

     header('content-type: image/jpeg');
     fpassthru($lob);

"cv" is the column that has the blob value in my table. Still, I can't get it to generate the image. Also, thanks for the datetime correction. I did echo $lob and it does generate the ASCII code, so I know that I got the right column now.

[seularts]

I added a screen capture of my database as well: https://ns.jobselection.ro/db/screen.png . I only added one row for testing purposes!

[cereal]

Great, now change fpassthru($lob); to echo $lob; as fpassthru() is used with file pointers.

[seularts]

I get the exact same result! :(

[seularts]

Wow, this is really weird. I found what the issue was. I had my configuration files in a separate <?php ?> in the same file. Until I placed them both in the same block of code it did not work!

[cereal]

I see why, to be honest I was reading the documentation for PDO and saw why you were using fpassthru():

PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API.

However while testing I got:

PHP Warning:  fpassthru() expects parameter 1 to be resource, string given

And in fact a comment in the same page states the same issue:

• http://php.net/manual/en/pdo.lobs.php#96311