PHP and Graphic files

dcjones

Hi all

Is it possible to have a field in a SQL database that holds a .gif or .jpg file.

If so which type of field type would I use

Thank in advance

Dereck

crimaniak

store pictures in filesystem and filenames in dabatase.

ruach

what is bad about storing images directly in the database?

-curious

dcjones

Well is it a good idea or not.

If it can be done what type of field do I use

Thanks

Dereck

archil

the mysql type to use is blob.

take a look at this tut.

http://www.phpbuilder.com/columns/florian19991014.php3

it is, however, a better idea to store the image name in the database rather than the file itself because the file takes up a lot more space, and recovering the file will be slower.

take a look at this tut.

http://www.phpbuilder.com/columns/william19990908.php3?print_mode=1

dcjones

Hi archil
Thank you for your reply. I now see the best way to do what I need,

Thanks

Dereck

dcjones

I have read the tut and understand some but not all.

I have a database which contains a complete list of countries around the world. Each country has a national flag. I have all the flags in .gif format.

If I store the .gif files in a directory on the server and have a field in the database which holds a number for each country, for example

1 = holland
2 = USA
3 = France

and the flags are

holland.gif
USA.gif
france.gif

How do I write the script to show the correct .gif file.

Does this make sence.

Dereck

leatherback

Hi:

$query = "Select * from flagstable where country=$id";

// flagstable and country and ID have to be replaced by the right names for table, flag identieifer colum and identifier value

$result =@ mysql_query($query);
while $row =@ mysql_fetch_array($result)
{
echo <img src = \"row[country]\".gif>;
}

or:

$query = "Select * from flagstable where country=$id";

$result =@ mysql_query($query);
while $row =@ mysql_fetch_array($result)
{
$body .=" <img src = \"row[country]\".gif>";
}

echo $body; // big advantage: you can put all your queries in the beginning, collect output in variables, and call them in your page.

Does this help you?

dcjones

This looks good, I thank you for so much direction.

I will try this out and let you know

Many thanks

Dereck

hundrup

Errr... scuse me for butting in but...

The graphic file takes up the same amount of disk space either as a file or as a blob in mysql

I vote for keeping the image as a file simply because your db will be smaller and therefore
faster

HTH

archil

yeah, should have clarified. It is the space in the database that is used up by storing the blob in the db.

dcjones

Hi all

Hello leatherback

Right, I think I am getting there. I now have 2 tables, Embass & Flags. Flags with 2 fields"id" and "country" the "id" for the "country" is the same as the "id" in the "embass" table. The script runs ansd shows that it is trying to pick up a graphic file for each row but is not showing the actual .gif file. Am I doing this correctly.

This is the code below,

$query = "Select * from flags where country=$id";

$result =@ mysql_query($query);
while ($row =@ mysql_fetch_array($result));
{
$body .=" <img src = \"row[country]\".gif>";
}

$query = "select country, embassy_name from embassies order by country LIMIT $offset,$diff";
$result = mysql_query($query) or die(mysql_error());

// Start a table, with column headers

echo "\n<table width=\"100%\" border=\"0\" cellpadding=\"2\" cellspacing\"2\">" .
"\n\t<td width=\"10%\" bgcolor=\"#CCCCCC\"><font color=\"black\" size=\"2\" ></td>" .
"\n\t<td width=\"20%\" bgcolor=\"#CCCCCC\"><font color=\"black\" size=\"2\" >Country</td>" .
"\n\t<td width=\"80%\" bgcolor=\"#CCCCCC\"><font color=\"black\" size=\"2\" face=\"arial\">Name</td>" .
"\n</tr>";
echo "\n<tr>\n\t<td></td>\n</tr>";
echo "\n<tr>\n\t<td></td>\n</tr>";
echo "\n<tr>\n\t<td></td>\n</tr>";

 // Until there are no rows in the result set,
 // fetch a row into the $row array and ...

while ($row = mysql_fetch_array($result))

 {
    // ... start a TABLE row ...

echo "\n<tr>";

echo "<td>". $body . "</td>";

?>
      <td align="left" width="766"><font color="black\" face="arial\"size="2"> <a href=\"embassies/emb.php?idname=<? echo urlencode($row["country"]).">".$row["country"] ?></a> 
      </td>
      <?

"\n\t";

 echo "<td align=\"left\"><font color=\"black\" face=\"arial\"size=\"2\" >" .
 $row["embassy_name"] . 
 "\n\t";


echo	"</tr>";

    // Finish the row
   echo "\n</tr>";
 }

if($zur!=TRUE)echo'<a href="'.$PHP_SELF.'?id='.($id-($tmp+$step)).'&offset='.($offset-$step) . ' "><font face=arial size=-6>Back </a> ';
for ($i=1;$i<=(int)ceil($anz/$step);$i++) echo '<font face=arial size=-6><a href="'.$PHP_SELF.'?id='.(($i-1)$step).'&offset='.(($i-1)$step). '" > '.$i.'</a> ';

if ($vor != TRUE) echo '<a href="'.$PHP_SELF.'?id='.$id.'&offset='.($offset+$step).'"><font face=arial size=-6>Next</a>';

 // Then, finish the table
 echo "\n</table>\n";

// Close the connection
if (!(mysql_close($connection)))
showerror();

Dereck

leatherback

Hi Dereck

Almost! (Sorry for my slow reply: I am trying to get somewhere with my website, which doesn't wnat to work.. I need some advice myself ;-)

You copied a topy from my post (My humbiest apologies!)

Change:

while ($row =@ mysql_fetch_array($result));
{
$body .=" <img src = \"row[country]\".gif>";
}

to:

while ($row =@ mysql_fetch_array($result));
{
$body .=" <img src = \"$row[country].gif\">";
}

and then it should work. (Unles there is other typos in the rest of the code. I didn't check there)

dcjones

Hi, sorry to be a pain,

This is the code I am now working with:

// flags is the flag table with 2 fields, id, flag. $idname is the country appened to the end of
// the URL from the calling page. Example: http://www.travellingtoengland.co.uk/embassies/emb.php?Argentina

$query = "Select * from flags where flag=$idname";

$result1 =@ mysql_query($query);
while ($row =@ mysql_fetch_array($result1));
{
$body .="<img src = \"$row[flag].gif\">";

}

$connection = mysql_connect($server, $user, $password);
mysql_select_db("db66511019", $connection);
$result = mysql_query("SELECT * FROM embassies WHERE country = '$idname'" , $connection);

while ($row = mysql_fetch_array($result))

{

echo " <table width=100% border=0 cellspacing=0 cellpadding=0 background=../../Gif/Background.gif bgproperties=fixed >";
echo "<tr>";
echo "<td width=10%></td>";
echo "<td>".$body."</td>";

When the script is run it picks up the correct record for the embassy but only show a graphic icon for the flag.

Any ideas as to why.

Regards and thanks

Dereck

leatherback

Hi,

Well, can't be too hard.

But eh.. I looked at the page. I am not sure but I think you have to have a look ath the rest of the code too: you somehow get a part of the query in the value of $body.

Furth; When looping through the results, you are in terested in $row[id] instead of $row[flag] if I understand it correctly.
And there is a set of quotes which I don't think you need around $body.

The thing would be something along the lines of:


$query = "Select * from flags where flag=$idname"; 
  $result1 =@ mysql_query($query);  

       while ($row =@ mysql_fetch_array($result1)); 
       { 
       $body .="<img src = \"$row[id].gif\">"; 
       } 
       // I am assuming you are already connected to the database when you do this!!

$connection = mysql_connect($server, $user, $password); 
mysql_select_db("db66511019", $connection); 


$result = mysql_query("SELECT * FROM embassies WHERE country = '$idname'" , $connection); 
    while ($row = mysql_fetch_array($result)) 
    { 
    echo " <table width=100% border=0 cellspacing=0 cellpadding=0 background=../../Gif/Background.gif bgproperties=fixed >"; 
    echo "<tr>"; 
    echo "<td width=10%></td>"; 
    echo "<td>$body</td>"; 
    }

dcjones

Hi, sorry again, we will have to stop meeting like this, people will talk,

Right then.

I have a table called FLAGS.

FLAGS has 2 fields ID and FLAG.

In the table there are a number of records one of which is

record No 1, ID = 1 , Flag = Argentina

The graphic files for the flags are in a sub-dir under the directory which the scripts run from.

I connect to the database using:
$connection = mysql_connect($server, $user, $password);
mysql_select_db("db66511019", $connection);

The query runs: $query = "Select * from flags where flag=$idname";

if I echo the query I get: Select * from flags where flag=Argentina
which is correct, then

$result1 =@ mysql_query($query);
while ($row =@ mysql_fetch_array($result1));

{
$body .="<img src = flags\"$row[id].gif\">";
echo $body;
}

It's here I think it's going wrong. firstly, where does the "$row[id].gif\" come in to it. I can't understand why I am using [id] when the it is unknown to the query. Or am I completely wrong ( I am new'ish to PHP).

This part: echo "<td>".$body."</td>"; if I run the script without the quotes or withour a period either side of $body it produces an error.

I do think that with your help I, we, I should say you, can get this working. I know your very busy with your own problems and I do thank you for your time with mine.

Regards

Dereck

dcjones

Hi All

And thank J for all your help so far,

I still can't get the graphic's to display. do you J or anyone else have any thoughts as to what is wrong

Thanks in advance

Dereck

archil

The quotes problem is coming from this line:

$body .="<img src = flags\"$row[id].gif\">";

Try this:

$body .= "<img src = \"flags/$row[flag].gif\">";

I am assuming that flags is a directory, therefore you need a slash to seporate the directory from the file.

Also, what are the names of your .gif files? If you are naming them something like Argentina.gif, then the above line should work.

Later,

Pete

dcjones

If I run this

<?php
include 'error.inc';
include 'db.inc';

$connection = mysql_connect($server, $user, $password);
mysql_select_db($database, $connection);

$query = "Select * from flags where flag=Argentina";

$result =@ mysql_query($query);
while ($row =@ mysql_fetch_array($result));
{
$body .= "<img src = \"flags/$row[flag].gif\">";

echo $query;
echo $result;
echo $row;
echo $body;
}
?>

The output is below

Select * from flags where flag=Argentina

At the end of the output line is a graphic's symbol so if I look at the source code in the browser I see this

Select * from flags where flag=Argentina<img src = "flags/.gif">

The script is not picking up the graphi'c file and I don't know why.

HELP IS NEED HERE, Please

Dereck

archil

try

$body .= "<img src = \"flags/$row[\"FLAG\"].gif\">";

also, I'm not 100% on mysql queries, but I believe you need to use ' ' in a sql query like so:

$query = "Select * from flags where flag=\'Argentina\' ";

if those don't work, try some debugging.

in your while loop put:

echo $row["ID"];
echo $row["FLAG"];

to see if you are pulling anything from the database. If those don't print anything, there is an issue with the database connection.

use the die() function to determine if the connections are working:

$connection = mysql_connect($server, $user, $password) or die ("Could not connect to server");

mysql_select_db($database, $connection) or die ("Could not connect to database");

Hope that helps,

Pete