Image to appear from mysql? Help!

[amg182]

Hi guys.

 

Quick question... I have a light box for my images and would like the images to appear  from mysql database photo field(BLOB).

At the moment the scripting works fine if you use a url reference to get images, but i have not idea how to change this so it uses my database "photo" field instead.

Anyone?

 

Here is what I am working with.

<a href="http://myurl/image123.jpg" target="_self" rel="wplightbox[a]~#~~#~"><img src="http://myurl/image123.jpg" width="277" height="207" border="0" alt="" style="position:absolute;left:238px;top:675px;"></a>

 

Thanks in anticipation!

[fugix]

what i like to do with images, since blobs seem to give me trouble, is to store the actual path to the image inside of the database table, then use the table reference as the image src, let me know if this is a feasible option for you.

[amg182]

Hi fugix. I did consider this alternative but would ideally like to have the images come from the database.

Thanks anyway.

[fugix]

you will want to execute something like this then.

$query = mysql_query("SELECT blob from table_name WHERE column_name = column_name");
$row = mysql_fetch_array($query);
header("Content-type: image/jpeg");
$image = $row['column_name'];
$image = imagecreatefromstring($image); 
imagejpeg($image);

[PFMaBiSmAd]

The src="..." attribute is a URL where the browser can fetch the image. If the image data is in a database table, you would need to use the URL of a .php script that when that URL is requested, the correct content-type: header is output, followed by the correct image data.

 

Since you probably have multiple images, you would use a get parameter on the end of the URL that specifies which image data to get from the database and output.

[PFMaBiSmAd]

You wouldn't use GD image functions to do this unless you are manipulating the image in some way, since they impose a huge memory and processing overhead.

[amg182]

Thanks for reply, it seems a little too complicated for someone of my php experience ...think i am just going to have to use the url directory folder.... :-\

 

Oh well, thanks again

[amg182]

I had another go and nearly works, i can get the image to display on the page when the light box opens its displaying the BLOB syntax characters! So if I can just mange to turn the blob syntax into and image I would be sorted.... Any comments would be appreciated. Here is what i am working with.

 

<td class="dr"><a href="index.php?getimage=&field=Photo4" target="_self" rel="wplightbox[a]~#~~#~"><?php echo outimage($recid, "Photo2", "280", "250", "") ?></a></td>

 

It just seems to be this part that the issue is....

<a href="index.php?getimage=&field=Photo4"

 

How can change the blob syntax to an image?

 

 

[amg182]

Anyone please??? :'(

[PFMaBiSmAd]

In reply #4 in this thread, someone mentioned that a "content-type: header is output, followed by the correct image data."

 

The browser doesn't know that the blob data is an image or what kind of image it is, especially since the file extension in the src="" attribute is .php and not that of an actual image like .jpg, .gif, .png,...

 

You either need to store the mime type of the file in your database and output it using a Content-type: header, i.e. a .jpg image would have a mime type of 'image/jpeg' or you if all your images are and will always be only one image type, you could always output the correct content-type header for that image type or you could construct a lookup table (array) that associates the image file extension to the correct mime type, assuming you have stored the original image extension in the database (it would be easier to store the original image mime type in the database in this case.)

 

A typical dynamically produced content-type header statement would look like -

$mime = "image/jpeg"; // determine the mime type for the image
header("Content-type: $mime");

 

 

 

 

[amg182]

Hi.

 

Thanks for reply.

I tried adding the header content but comes up with this error

 

Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\phpgen\index.php:214) in C:\xampp\htdocs\phpgen\index.php on line 500

 

here is the script i am working with.

 

<?php session_start();
  if (isset($_GET["order"])) $order = @$_GET["order"];
  if (isset($_GET["type"])) $ordtype = @$_GET["type"];

  if (isset($_POST["filter"])) $filter = @$_POST["filter"];
  if (isset($_POST["filter_field"])) $filterfield = @$_POST["filter_field"];
  $wholeonly = false;
  if (isset($_POST["wholeonly"])) $wholeonly = @$_POST["wholeonly"];

  if (!isset($order) && isset($_SESSION["order"])) $order = $_SESSION["order"];
  if (!isset($ordtype) && isset($_SESSION["type"])) $ordtype = $_SESSION["type"];
  if (!isset($filter) && isset($_SESSION["filter"])) $filter = $_SESSION["filter"];
  if (!isset($filterfield) && isset($_SESSION["filter_field"])) $filterfield = $_SESSION["filter_field"];

  if (isset($_GET["getimage"]))
  {
    $conn = connect();
    getimage(@$_GET["getimage"], @$_GET["field"]);
    exit;
  }
?>

<html>
<head>
<title>cars -- cars</title>
<meta name="generator" http-equiv="content-type" content="text/html">
<style type="text/css">
  body {
    background-color: #FFFFFF;
    color: #4169E1;
    font-family: Arial;
    font-size: 20px;
    font-weight: bold;
  }
  .bd {
    background-color: #FFFFFF;
    color: #4169E1;
    font-family: Arial;
    font-size: 20px;
    font-weight: bold;
  }
  .tbl {
    background-color: #FFFFFF;
  }
  a:link { 
    background-color: #FFFFFF01;
    color: #FF00FF;
    font-family: Arial;
    font-size: 14px;
  }
  a:active { 
    background-color: #FFFFFF01;
    color: #0000FF;
    font-family: Arial;
    font-size: 14px;
  }
  a:visited { 
    background-color: #FFFFFF01;
    color: #800080;
    font-family: Arial;
    font-size: 14px;
  }
  .hr {
    background-color: #FFFFFF;
    color: #1E90FF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:link {
    color: #1E90FF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:active {
    color: #1E90FF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:visited {
    color: #1E90FF;
    font-family: Arial;
    font-size: 12px;
  }
  .dr {
    background-color: #FFFFFF;
    color: #000000;
    font-family: Arial;
    font-size: 14px;
  }
</style>
</head>
<body>
<table class="bd" width="100%"><tr><td class="hr"><h2>Car Database</h2></td></tr></table>
<?php



  $conn = connect();
  $showrecs = 10;
  $pagerange = 10;

  $a = @$_GET["a"];
  $recid = @$_GET["recid"];
  $page = @$_GET["page"];
  if (!isset($page)) $page = 1;

  switch ($a) {
    case "view":
      viewrec($recid);
      break;
    default:
      select();
      break;
  }

  if (isset($order)) $_SESSION["order"] = $order;
  if (isset($ordtype)) $_SESSION["type"] = $ordtype;
  if (isset($filter)) $_SESSION["filter"] = $filter;
  if (isset($filterfield)) $_SESSION["filter_field"] = $filterfield;
  if (isset($wholeonly)) $_SESSION["wholeonly"] = $wholeonly;

  mysql_close($conn);
?>
<table class="bd" width="100%"><tr><td class="hr"></td></tr></table>
</body>
</html>

<?php function select()
  {
  global $a;
  global $showrecs;
  global $page;
  global $filter;
  global $filterfield;
  global $wholeonly;
  global $order;
  global $ordtype;


  if ($a == "reset") {
    $filter = "";
    $filterfield = "";
    $wholeonly = "";
    $order = "";
    $ordtype = "";
  }

  $checkstr = "";
  if ($wholeonly) $checkstr = " checked";
  if ($ordtype == "asc") { $ordtypestr = "desc"; } else { $ordtypestr = "asc"; }
  $res = sql_select();
  $count = sql_getrecordcount();
  if ($count % $showrecs != 0) {
    $pagecount = intval($count / $showrecs) + 1;
  }
  else {
    $pagecount = intval($count / $showrecs);
  }
  $startrec = $showrecs * ($page - 1);
  if ($startrec < $count) {mysql_data_seek($res, $startrec);}
  $reccount = min($showrecs * $page, $count);


?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr><td>Car database: cars</td></tr>
<tr><td>Records shown <?php echo $startrec + 1 ?> - <?php echo $reccount ?> of <?php echo $count ?></td></tr>
</table>
<hr size="1" noshade>
<form action="index.php" method="post">
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><b>Search</b> </td>
<td><input type="text" name="filter" value="<?php echo $filter ?>"></td>
<td><select name="filter_field">
<option value="">All Fields</option>
<option value="<?php echo "Make" ?>"<?php if ($filterfield == "Make") { echo "selected"; } ?>><?php echo htmlspecialchars("Make") ?></option>
<option value="<?php echo "Model" ?>"<?php if ($filterfield == "Model") { echo "selected"; } ?>><?php echo htmlspecialchars("Model") ?></option>
<option value="<?php echo "Year" ?>"<?php if ($filterfield == "Year") { echo "selected"; } ?>><?php echo htmlspecialchars("Year") ?></option>
<option value="<?php echo "Price" ?>"<?php if ($filterfield == "Price") { echo "selected"; } ?>><?php echo htmlspecialchars("Price") ?></option>
<option value="<?php echo "Fuel" ?>"<?php if ($filterfield == "Fuel") { echo "selected"; } ?>><?php echo htmlspecialchars("Fuel") ?></option>
<option value="<?php echo "Location" ?>"<?php if ($filterfield == "Location") { echo "selected"; } ?>><?php echo htmlspecialchars("Location") ?></option>
</select></td>
<td><input type="checkbox" name="wholeonly"<?php echo $checkstr ?>>Whole words only</td>
</td></tr>
<tr>
<td> </td>
<td><input type="submit" name="action" value="Apply Filter"></td>
<td><a href="index.php?a=reset">Reset Filter</a></td>
</tr>
</table>
</form>
<hr size="1" noshade>
<?php showpagenav($page, $pagecount); ?>
<br>
<table class="tbl" border="0" cellspacing="4" cellpadding="0"width="100%">
<tr>
<td class="hr"> </td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Make" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Make") ?></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Model" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Model") ?></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Year" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Year") ?></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Price" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Price") ?></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Fuel" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Fuel") ?></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Location" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Location") ?></a></td>
</tr>
<?php
  for ($i = $startrec; $i < $reccount; $i++)
  {
    $row = mysql_fetch_assoc($res);
?>
<tr>
<td class="dr"><a href="index.php?a=view&recid=<?php echo $i ?>">View</a></td>
<td class="dr"><?php echo htmlspecialchars($row["Make"]) ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Model"]) ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Year"]) ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Price"]) ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Fuel"]) ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Location"]) ?></td>
</tr>
<?php
  }
  mysql_free_result($res);
?>
</table>
<br>
<?php showpagenav($page, $pagecount); ?>
<?php } ?>

<?php function showrow($row, $recid)
  {

?>

//here is where the image is displayed, it echoes out the binary of the blob

<table class="tbl" border="1" cellspacing="4" cellpadding="0"width="40%">
<tr>
<td class="dr"><a href="http://localhost/phpgen/table.php?getimage=0&field=Photo" target="_self" rel="wplightbox[a]~#~~#~"><?php echo outimage($recid, "Photo2", "160", "145", "") ?></a></td>

</tr>
</table>

<?php } ?>

<?php function showpagenav($page, $pagecount)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<?php if ($page > 1) { ?>
<td><a href="index.php?page=<?php echo $page - 1 ?>"><< Prev</a> </td>
<?php } ?>
<?php
  global $pagerange;

  if ($pagecount > 1) {

  if ($pagecount % $pagerange != 0) {
    $rangecount = intval($pagecount / $pagerange) + 1;
  }
  else {
    $rangecount = intval($pagecount / $pagerange);
  }
  for ($i = 1; $i < $rangecount + 1; $i++) {
    $startpage = (($i - 1) * $pagerange) + 1;
    $count = min($i * $pagerange, $pagecount);

    if ((($page >= $startpage) && ($page <= ($i * $pagerange)))) {
      for ($j = $startpage; $j < $count + 1; $j++) {
        if ($j == $page) {
?>
<td><b><?php echo $j ?></b></td>
<?php } else { ?>
<td><a href="index.php?page=<?php echo $j ?>"><?php echo $j ?></a></td>
<?php } } } else { ?>
<td><a href="index.php?page=<?php echo $startpage ?>"><?php echo $startpage ."..." .$count ?></a></td>
<?php } } } ?>
<?php if ($page < $pagecount) { ?>
<td> <a href="index.php?page=<?php echo $page + 1 ?>">Next >></a> </td>
<?php } ?>
</tr>
</table>
<?php } ?>

<?php function showrecnav($a, $recid, $count)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="index.php">Index Page</a></td>
<?php if ($recid > 0) { ?>
<td><a href="index.php?a=<?php echo $a ?>&recid=<?php echo $recid - 1 ?>">Prior Record</a></td>
<?php } if ($recid < $count - 1) { ?>
<td><a href="index.php?a=<?php echo $a ?>&recid=<?php echo $recid + 1 ?>">Next Record</a></td>
<?php } ?>
</tr>
</table>
<hr size="1" noshade>
<?php } ?>


<?php function viewrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("view", $recid, $count);
?>
<br>
<?php showrow($row, $recid) ?>
<?php
  mysql_free_result($res);
} ?>

<?php function connect()
{
  $conn = mysql_connect("localhost", "root", "**");
  mysql_select_db("cars");
  return $conn;
}

function sqlstr($val)
{
  return str_replace("'", "''", $val);
}

function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT ID, `Seller`, `Description`, `Gearbox`, `Engine Size`, `Title`, `Mileage`, `Body Type`, `Make`, `Model`, `Year`, `Price`, `Fuel`, `Location`, `Photo`, `Photo2`, `Photo3`, `Photo4`, `Contact No:`, `Email:` FROM `cars`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Make` like '" .$filterstr ."') or (`Model` like '" .$filterstr ."') or (`Year` like '" .$filterstr ."') or (`Price` like '" .$filterstr ."') or (`Fuel` like '" .$filterstr ."') or (`Location` like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

function sql_getrecordcount()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT COUNT(*) FROM `cars`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Make` like '" .$filterstr ."') or (`Model` like '" .$filterstr ."') or (`Year` like '" .$filterstr ."') or (`Price` like '" .$filterstr ."') or (`Fuel` like '" .$filterstr ."') or (`Location` like '" .$filterstr ."')";
  }
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  reset($row);
  return current($row);
}function get_file_content_as_sql($fieldname)
{
  global $_FILES;
  $sql = "";
  if (!empty($_FILES))
    $sql=mysql_escape_string(file_get_contents(@$_FILES['x_'.$fieldname]['tmp_name']));
  if ($sql == '')
    $sql = 'null';
  else
   $sql = "'".$sql."'";
  return $sql;
}

function get_file_content_as_sql_for_update($fieldname)
{
  global $_POST;
  $sql = "`$fieldname` = `$fieldname`";
  if (isset($_POST["a_".$fieldname]))
  {
    switch ($_POST["a_".$fieldname]) {
    //remove
    case "2":
      $sql = "`$fieldname`= null";
      break;
    //replace
    case "3":
      $sql = "`$fieldname`=" .get_file_content_as_sql($fieldname);
      break;
    }
  }
  return $sql;
}
function outimage($recid, $field, $alt, $width, $height)
{
  $res = "<img src=\"index.php?getimage=$recid&field=$field\" border=0 alt=\"$alt\"";
  if ($width <> '')
    $res = $res." width=\"$width\"";
  if ($height <> '')
    $res = $res." height=\"$height\"";
  $res = $res.">";
  return $res;
}

function getimage($recid, $field)
{
  $res = sql_select();
  mysql_data_seek($res, $recid);;
  $row = mysql_fetch_assoc($res);
  echo $row[$field];
}

?>


<script type="text/javascript" src="wpscripts/jsFlashVer.js"></script>
<script type="text/javascript" src="wpscripts/jquery.js"></script>
<script type="text/javascript" src="wpscripts/jquery.wplightbox.js"></script>
<style type="text/css">
body {margin: 0px; padding: 0px;}
</style>
<script type="text/javascript" src="wpscripts/jspngfix.js"></script>
<script type="text/javascript">
var blankSrc = "wpscripts/blank.gif";
</script>

<script type="text/javascript">
$(document).ready(function() {

$('a[rel*=wplightbox]').wplightbox({ strImgDir: 'wpimages/', strScriptDir: 'wpscripts/', nButtonType: 2, nBorderType: 6 });

})
</script>

 

 

Sorry for my complete ignorance...i am a very new to php and sql

 

[PFMaBiSmAd]

The only thing you can output to the browser before a header is another header and if you are trying to dynamically output an image the only thing you can output is(are) header(s) followed by the image data.

 

See this sticky post, found at the top of the forum section you are posting in - http://www.phpfreaks.com/forums/index.php?topic=37442.0