Save image url path in DB columns

Question

i am updating name , email in DB of registered user through php form. its working fine.

class.usr.php

public function update($uname,$email, $tax)
    {
    try {
    $stmt = $this->conn->prepare('UPDATE tbl_users SET userName = ?, userEmail = ? , tax = ?  WHERE userID = ? ');
    $stmt->execute(array($uname,$email, $tax , $_SESSION['userSession']));
    return $stmt->fetch();
    } catch(PDOException $e) {
        echo '<p class="bg-danger">'.$e->getMessage().'</p>';
    }

form

<form action="profile.php" method="POST" enctype="multipart/form-data">

Name : 
<input type="text" name="txtuname" value="<?php echo $row['userName'] ?>" /><br/>
Email :
<input type="text" name="txtemail" value="<?php echo $row['userEmail'] ?>" /><br>
Image
<input type="file" name="photo" id="fileSelect"><br> 

<input type="submit" name="submit" value="Save" />

</form>

form related code to save in db

<?php

$user_home = new USER();

if(!$user_home->is_logged_in())
{
    header("Location: index.php");
die();
}

if (isset($_POST['submit'])) {
// new data
$uname = $_POST['txtuname'];
$email = $_POST['txtemail'];
$tax = trim($_POST['tax']); // image url path

$uid = (isset($_SESSION['userSession']) ? intval($_SESSION['userSession']) : 0);

if ($uid > 0 && $user_home->update($uname,$email, $tax, $uid))
{
    header("Location: profile1.php");
   die(); 
}
}

$stmt = $user_home->runQuery("SELECT * FROM tbl_users WHERE userID=:uid");
$stmt->execute(array(":uid"=>$_SESSION['userSession']));
$row = $stmt->fetch(PDO::FETCH_ASSOC);

?>

after this, now i am uploading an image to folder through same php form successfully with below code.

    <?php 
if(isset($_FILES["photo"]["error"])){ 
if($_FILES["photo"]["error"] > 0){ 
echo "Error: " . $_FILES["photo"]["error"] . "<br>"; 

} else{ 
$allowed = array("jpg" => "image/jpg", "jpeg" => "image/jpeg", "gif" => "image/gif", "png" => "image/png"); 
$filename = $_FILES["photo"]["name"]; 
$filetype = $_FILES["photo"]["type"]; 
$filesize = $_FILES["photo"]["size"]; 

// Verify file extension 
$ext = pathinfo($filename, PATHINFO_EXTENSION); 
if(!array_key_exists($ext, $allowed)) die("Error: Please select a valid file format."); 

// Verify file size - 5MB maximum 
$maxsize = 5 * 1024 * 1024; 
if($filesize > $maxsize) die("Error: File size is larger than the allowed limit."); 

// Verify MYME type of the file 
if(in_array($filetype, $allowed)){ 
// Check whether file exists before uploading it 
if(file_exists("upload/" . $_FILES["photo"]["name"])){ 
echo $_FILES["photo"]["name"] . " is already exists."; 

} else{ 
move_uploaded_file($_FILES["photo"]["tmp_name"], "upload/" . $_FILES["photo"]["name"]); 

echo "Your file was uploaded successfully."; 
} 
} else{ 

echo "Error: There was a problem uploading your file - please try again."; 
} 

} 

} else{ 
echo ""; 
} 

?>

now images are just saving in folders, what i need is i want that image path to save in database and assign that image path to uploaded user in database. so that one registered user can update the existing image, but not upload one more image.

i tried below code , but not working:

<?php
$folder = "upload/"; 
    $file = basename( $_FILES['image']['name']); 
    $full_path = $folder.$file; 
    $tax= $full_path;

    if(in_array($filetype, $allowed)){ 
// Check whether file exists before uploading it 
if(file_exists("upload/" . $_FILES["photo"]["name"])){ 
echo $_FILES["photo"]["name"] . " is already exists."; 

} else{ 
move_uploaded_file($_FILES["photo"]["tmp_name"], "upload/" . $_FILES["photo"]["name"]); 

echo "Your file was uploaded successfully."; 
} 
} else{ 

echo "Error: There was a problem uploading your file - please try again."; 
} 

} 

} else{ 
echo ""; 
} 
?>

db columns : userName, userEmail, tax , photo

with help of google i done all above, i am new to php, so please kindly help me.

You are asking us to write code for you! We fix your attempts at coding we do not write brand new code for you. — RiggsFolly, Oct 25 '16 at 11:41
@RiggsFolly Thanks for your comment, i tried some code as i posted in question, but it did't worked for me.... — , Oct 25 '16 at 11:43
Yes, but I see no attempt at updating the user photo information — RiggsFolly, Oct 25 '16 at 11:43
@RiggsFolly firstly i am trying to save image path in db column, than i will try to display image..... — , Oct 25 '16 at 11:45

krasipenkov · Answer 1 · 2016-10-25T10:43:43.473

Here is another solution:

First of all execute this query manually to add the new column:

ALTER TABLE `tbl_users` ADD `photo` VARCHAR(255) NOT NULL ;

Then this is the php code:

<?php
$dbConn = new Database();
$dbConn->dbConnection();

$user_home = new USER();

function uploadUserPhoto($uid) {
    global $dbConn;
    if(isset($_FILES["photo"]["error"])) {
        if($_FILES["photo"]["error"] > 0) {
            echo "Error: " . $_FILES["photo"]["error"] . "<br>";

        } else {
            $allowed = array("jpg" => "image/jpg", "jpeg" => "image/jpeg", "gif" => "image/gif", "png" => "image/png");
            $filename = $_FILES["photo"]["name"];
            $filetype = $_FILES["photo"]["type"];
            $filesize = $_FILES["photo"]["size"];

            $userDir = $uid;

            // Verify file extension
            $ext = pathinfo($filename, PATHINFO_EXTENSION);
            if(!array_key_exists($ext, $allowed)) die("Error: Please select a valid file format.");

            // Verify file size - 5MB maximum
            $maxsize = 5 * 1024 * 1024;
            if($filesize > $maxsize) die("Error: File size is larger than the allowed limit.");

            // Verify MYME type of the file
            if(in_array($filetype, $allowed)) {
                if(!is_dir('upload/'.$uid)) {
                    mkdir('upload/'.$uid);
                }

                $photoname = time().$uid.'_photo'.'.'.$ext;

                // delete all the files in this directory
                $files = glob('upload/'.$uid.'/*'); // get all file names
                foreach($files as $file){ // iterate files
                    if(is_file($file))
                        unlink($file); // delete file
                }

                // Upload the photo
                move_uploaded_file($_FILES["photo"]["tmp_name"], "upload/" . $uid . '/'. $photoname);

                $updateData = array(':userID' => $uid, ':photo' => $photoname);
                $stmt = $dbConn->conn->prepare("UPDATE tbl_users SET photo=:photo WHERE userID=:uid");
                $stmt->execute($updateData);

                echo "Your file was uploaded successfully.";
            } else {
                echo "Error: There was a problem uploading your file - please try again.";
            }
        }
    } else {
        echo "";
    }
}

if(!$user_home->is_logged_in())
{
    header("Location: index.php");
    die();
}

if (isset($_POST['submit'])) {
    // new data
    $uname = $_POST['txtuname'];
    $email = $_POST['txtemail'];
    $tax = trim($_POST['tax']); // image url path

    $uid = (isset($_SESSION['userSession']) ? intval($_SESSION['userSession']) : 0);

    if ($uid > 0 && $user_home->update($uname,$email, $tax, $uid))
    {
        uploadUserPhoto($uid);
        header("Location: profile1.php");
        die();
    }
}

$stmt = $user_home->runQuery("SELECT * FROM tbl_users WHERE userID=:uid");
$stmt->execute(array(":uid"=>$_SESSION['userSession']));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
?>

There is $dbConnection variable which is the connection to the DB but because I don't know the rest of your code you should replace it with your proper db connection variable.

The photo of the user is saved in photo column in tbl_users and for every user is created sub dir in uploads dir. The subdir is the userID. So for example for user with userID = 1 its upload path will be uploads/1/<filename>.

File name is generated dynamically - this avoids caching of uploaded photo with the same name for example ... and it is better approach.

You have to make a change in code for displaying the photo because now its filename is in the DB and there is subdir in uploads (which is the userID of the user)

can you please tell me what code i need to change for `displaying the photo` — , Oct 25 '16 at 09:37
i need to save the image path in db column, thats also not working for me , can you please join here : http://chat.stackoverflow.com/rooms/126594/discussion-between-abcd-and-krasipenkov — , Oct 25 '16 at 10:24
db file : http://pastebin.com/LKtgBnUF, i followed your answer properly, but still where i am missing ? — , Oct 25 '16 at 10:39
sorry, i updated your code, still image-paths are not displaying under columns, please check these codes : class.usr.php : http://pastebin.com/iLVbMAfN , profile.php : http://pastebin.com/tmVj80jh — , Oct 25 '16 at 10:54
please download complete files : https://ufile.io/58d11 & db file : https://uploadfiles.io/2128 — , Oct 25 '16 at 10:58
Here is the corrected code: https://ufile.io/01f9 I also added db changes directly in dbtest.sql. Code changes are not that much. — krasipenkov, Oct 25 '16 at 20:39
Thanks a ton for your help, i used same files and db that you pasted in above links, but once i browse and select image, its not uploading to folder , also path also not displaying in db. its displaying like this link : http://prnt.sc/cz17ot — , Oct 26 '16 at 05:26
when i inspect element on that, i found that sub-folder created under `upload` folder is showing : http://prnt.sc/cz1c75 , `subfolder:22` is the `user_id` of that user...... — , Oct 26 '16 at 05:41
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/126691/discussion-between-abcd-and-krasipenkov). — , Oct 26 '16 at 06:11
Thanks a lot for your all support, i found solution, need your help like this in upcoming days also .... i am sorry that i wasted your lot of time..... — , Oct 26 '16 at 13:51

PassTeT · Accepted Answer · 2016-10-26T12:31:36.637

Add new function for saving files and use global php var $_FILES

1 Add new column to your DB to store file path, let's name it photo

2 Add new functions for your user class:

<?php
class User {
...
  const PATH_PHOTOS = '/path/to/photo/folder/';
  const BASE_URL = 'http://YOUR_DOMAIN_NAME:YOUR_PORT/YOUR_PATH/';

  public function add_photo($file)
  {
    $ext = pathinfo($file['name'], PATHINFO_EXTENSION);
    $file['new_name'] = uniqid(rand(), true) . ".$ext";
    if (!$this->_upload_file($file))
      return false;
    return $this->_remove_previous_photo()->_add_file_to_db(self::PATH_PHOTOS .     basename($file['new_name']));
  }

  protected function _remove_previous_photo()
  {
    $photo = $this->get_photo();
    if ($photo)
      unlink($photo);
    return $this;
  }

  public function get_photo()
  {
    global $_SESSION;
    $stmt = $this->conn->prepare('SELECT photo FROM tbl_users WHERE userID = ?     ');
    $stmt->execute(array($_SESSION['userSession']));
    $result = $stmt->fetch();
    return reset($result);
  }

  public function get_photo_url()
  {
    $pathInfo = pathinfo($this->get_photo());
    $last_dir = end(explode(DIRECTORY_SEPARATOR, $pathInfo['dirname']));
    return self::BASE_URL . "$last_dir/" . basename($this->get_photo());
  }

  protected function _upload_file($file)
  {
    $uploadfile = self::PATH_PHOTOS . $file['new_name'];
    return move_uploaded_file($file['tmp_name'], $uploadfile);
  }

  protected function _add_file_to_db($file_path)
  {
    try {
      $stmt = $this->conn->prepare('UPDATE tbl_users SET photo = ? WHERE userID = ? ');
      return $stmt->execute(array($file_path, $_SESSION['userSession']));
    } catch (PDOException $e) {
      echo '<p class="bg-danger">' . $e->getMessage() . '</p>';
    }
  }
...
}
?>

3 The main file should look like this:

<?php

$user_home = new USER();

if(!$user_home->is_logged_in())
{
    header("Location: index.php");
die();
}

if (isset($_POST['submit'])) {
// new data
$uname = $_POST['txtuname'];
$email = $_POST['txtemail'];
$tax = trim($_POST['tax']); // image url path

$uid = (isset($_SESSION['userSession']) ? intval($_SESSION['userSession']) : 0);

if ($uid > 0 && $user_home->update($uname,$email, $tax, $uid) && $user_home->add_photo($_FILES['photo']))
{
    header("Location: profile1.php");
   die(); 
}
}

$stmt = $user_home->runQuery("SELECT * FROM tbl_users WHERE userID=:uid");
$stmt->execute(array(":uid"=>$_SESSION['userSession']));
$row = $stmt->fetch(PDO::FETCH_ASSOC);

?>

Hope this helps

sorry, its not working for me , i gave path as [link](http://sbdev2.kidsdial.com:81/php/site3/upload/) — , Oct 25 '16 at 06:43
still not working, looks like i am missing somewhere, can you please check these codes : class.user.php : http://pastebin.com/Zkue3Tyn , main file : http://pasted.co/2e0a4c65 — , Oct 25 '16 at 07:01
I have checked the file you gave me. The constant PATH_PHOTOS - is not a URL it should be a pass on a server — PassTeT, Oct 25 '16 at 08:23
I am not sure where the files are located on your server, but there definatelly should be a directory where theese files should be stored(the permissions should include read/write for images, but no execute for security reasons). When you setup the directory on your server make sure it's accessible by your apache(for linux systems open terminal and type ```su -www -s /sh/bin``` go to the folder you created and try to get any file). After change the PATH_PHOTOS to absolute path on your server — PassTeT, Oct 25 '16 at 09:51
the images are uploading fine in folders, but the image paths are not saving in database..... — , Oct 25 '16 at 09:59
okay, i changed the path to `/var/www/html/sbdev2/php/site3/upload` , here only all uploaded images are storing, but the file path is not saving in database column `photo` — , Oct 25 '16 at 10:02
I've updated the code, i suppose the $_SESSION var was not taken as it was in global scope. Try it now — PassTeT, Oct 25 '16 at 10:10

Save image url path in DB columns

2 Answers2

Linked