CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Wicket W. Warwick
Wicket W. Warwick

Reputation: 33

Storing a path for an image file in MySQL database

I am building an application using Codeigniter and am trying to manually store a path to an image file in my MySQL database. The images are stored in a folder called crests which is at root level of the application.

I am using the following code to insert an image into the database but it is throwing up an "Error 1064" 

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

/*
File:        crests.sql
Description: Used for creating the objects and loading data into the teams schema
Created:     February 11, 2014
Version:     1.0
Advice:      Save this file onto your hardrive and use the following command(no  semicolon) to run it from the 

command prompt
           SOURCE C:\xampp\htdocs\myApplication\sql_scripts\teams.sql  
*/

SELECT '<starting bars script>' AS '';

SELECT '<my_teams_ database>' AS '';

INSERT INTO teams (team_name, team_logo) VALUES('Antrim', '<img src="<?php echo    site_url('../logo/antrim.png'); ?>" 

alt="...">');


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I am getting an error in the MySQL Command Line Client

ERROR 1064 <4200> you have an error in your SQL syntax; check the manual that corresponds to your MySQL server for the right syntax to use near '../logo/antrim.png'); ?>" alt="...">'); at line 1

I am not sure if it is a syntax error or if it has to do with escaping quotation marks using a backslash . I am also not sure if this is the correct way to store the path to an image in Codeigniter. I have searched on here and on other forums but cant find an answer. Any help would be appreciated.

I am using the MVC to store, manipulate and display this data.

Team Model 

<?php  
class Team_model extends CI_Model {  

public function __construct()
{
  parent::__construct();
}

function team() 

    {  
        //Query the team table for every record and row  

        $team_results = array();
        $this->db->select('team_name, team_logo');
        $this->db->from('team');

        $query = $this->db->get();

        if($query->num_rows() > 0) 
        {
         $team_results = $query->result();
        }

        return $team_results;   

    }  

}
/*End of file team_model.php*/
/*Location .application/models/team_model.php*/ 
?>

In the View

<?php

     if (is_array($team_results))
     {
       if( !empty($team_results) ) 
       {

         foreach($team_results as $row) 
         {

          echo '<div class="col-sm-6 col-md-6">';
          echo '<div class="thumbnail">';
          echo '<tr>';
          echo '<h4>';
          echo '<td>'.$row->team_name.'</td>'."</br>";
          echo '<td>'.$row->team_logo'</td>'."</br></br>";
          echo '</h4>';

          echo '</tr>';
          echo '</div>';
          echo '</div>';
         }
       }

      else echo "Not Array";
    }

    ?>


  </div>
</div>

The team_name is returned no problem but the image doesn't display - instead I get a broken image link.

Upvotes: 1

Views: 8524

Answers (4)

wribit
wribit

Reputation: 605

This is how I usually store a picture path name, for example: images/uploads/[picname].[picext].

so it would be: 

insert values ('Antrim', 'images/uploads/example.jpg')

I find it an easier way to deal with saving path names, you can just handle the img tag through html.

Upvotes: 0

brokethebuildagain
brokethebuildagain

Reputation: 2191

Your problem is here:

INSERT INTO team (team_name, team_logo) VALUES('Antrim', '<img src="<?php echo       site_url('../crests/antrim.png'); ?>"

It's not really the best practice to store php or HTML code in a database. Databases are for data, not code. It would be much better to store just the image path in the DB. You should write your INSERT like this: 

INSERT INTO team (team_name, team_logo_url, team_logo_altText) VALUES('Antrim', '../crests/antrim.png', '...')

And handle any PHP or HTML in the code.

I REALLY don't recommend doing this, but if you just want to make it work, you can change your query to:

INSERT INTO team (team_name, team_logo) VALUES('Antrim', '<img src="<?php echo site_url(\'../crests/antrim.png\'); ?>"  alt="...">');

Upvotes: 0

marekful
marekful

Reputation: 15351

There is a problem with quotation marks used in the insert statement on this line:

INSERT INTO team (team_name, team_logo) VALUES('Antrim', '<img src="<?php echo    site_url('../crests/antrim.png'); ?>"

The first value is Antrim, the next is <img src="<?php echo site_url( and the next single quote terminates this value. After this MySQL expects a comma.

Escape the quotes that are inside the intended values.

Upvotes: 0

jeroen
jeroen

Reputation: 91734

You need to escape the single quotes or use double quotes:

INSERT INTO team (team_name, team_logo) VALUES('Antrim',
      '<img src="<?php echo site_url(\'../crests/antrim.png\'); ?>"  alt="...">');

or:

INSERT INTO team (team_name, team_logo) VALUES('Antrim',
      '<img src="<?php echo site_url("../crests/antrim.png"); ?>"  alt="...">');

By the way, I would not store an image path like that if I could avoid it (I don't know how you are going to use it...); you would need to evaluate the string that you get from the database later on to process the php.

Upvotes: 2

Related Questions