Getting pictures to show using mySql query

8 posts by 2 authors in: Forums > CMS Builder
Last Post: August 5, 2014   (RSS)

By Mel - August 4, 2014

Hello,

Using XYZ  site both as a live site and as the platform to input master records into the Database set up

I need some help in solving the following problem:

I am able to connect to the main database and do a mysql query to display the records either as a list of records
or have an individual record page

Along the lines of the following for a list of records:

<?php do { ?>
        <div><?php echo $row_bios['name']; ?>&nbsp;&nbsp;<?php echo $row_bios['age']; ?>
        &nbsp;&nbsp;<a href="<?php echo $row_bios['_link'] ?>" target="_self"><img src=" width="<?php echo $row_upload['width'] ?>" height="<?php echo $row_upload['height'] ?>" alt="<?php echo $row_bios['name'] ?>"></a> </div>
        <?php } while ($row_bios = mysql_fetch_assoc($bios)); ?>

But it will not pull up the pictures associated with each record

With the original code via the CMS which works - see below, it will pull up the pictures, so I am guessing that this has something to do with the fact that the CMS deals with display of records in a different way and uses
a different way of coding and linking to the upload directory

 <?php foreach ($biosRecords as $biosRecord): ?>
    <div class="bg-1 bioFrame">
      <div class="name">
           <?php echo $row_bios['name'] ?>&nbsp;&nbsp;
      </div>
        
    <!-- .......... This section deals with picture .......... -->     
     
    <div align="center" class="img_border"><?php foreach ($biosRecord['new_imageNS'] as $upload): ?>
  <?php if ($upload['isImage']): ?>
          <a href="<?php echo $biosRecord['_link'] ?>" target="_self"><img src="<?php echo get_custom_watermark_url($upload['urlPath'], 'watermarkNew.png') ?>" width="<?php echo $upload['width'] ?>" height="<?php echo $upload['height'] ?>" alt="<?php echo $biosRecord['name'] ?>"></a>
          <?php else: ?>     
          <?php endif ?>
    </div>
    <?php endforeach ?>
               
 <!--<div class="bio"><?php echo $biosRecord['bio'] ?></div>-->

     <div align="right"style="padding-top:3px;">
     <a href="<?php echo $biosRecord['_link'] ?>" target="_self">View Details</a>
     </div>
     
    <!-- finish bioFrame--></div>  
   
   <!-- finish master foreach--><?php endforeach ?>


Note:

I would also have to re-think how to do the custom watermark as this is a CMS plug in that generates the custom watermark -  But initially I could do with some advice on how to get the pictures to show
with their associated record - I know they are in the upload file but how do I get them to show?

Thanks Mel





 

By gregThomas - August 4, 2014

Hi Mel,

Upload details for a particular record are stored in a hidden section called uploads inside of the CMS.

The easiest way to retrieve the uploads for a set of records is to retrieve them using the getRecords function, you can use the code generator in the CMS to create the appropriate getRecords function for you:

http://www.interactivetools.com/docs/cmsbuilder/code_generator.html

Just ensure that the getRecords function has showUploads set to true:

  // load records from 'gallery_categories'
  list($gallery_categoriesRecords, $gallery_categoriesMetaData) = getRecords(array(
    'tableName'   => 'gallery_categories',
    'loadUploads' => true,
    'allowSearch' => false,
  ));

Another option is to retrieve the uploads directly from the uploads table, using your example below, you could do that with something like this:

$uploads = mysql_select('uploads',"`tableName` = 'bio table name here' AND fieldName = 'bio upload field name here' AND `recordNum` = '{$row_bios['num']}'"

You'll need to add the appropriate data into the table name and field name strings. The first argument of the mysql_select function is the tablename, and the second argument is a MySQL where statement. 

Let me know if you have any questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By Mel - August 5, 2014

Hello Greg,

Thanks for coming back to me:

  • I am still lost on this, where should I be adding either of the two solutions that you suggested
  • At the present time I can access all records and call all fields with php echo
  • The images table is called cms_uploads and the bios table is cms_bios
  • The particular image field that I am trying to display for the record listings is   new_imageNS 
  • Below is the code that I am using to make the connection and list all the records, I have stripped out the styling to make it more readable

<?php require_once('Connections/Webhost495.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_Webhost495, $Webhost495);
$query_bios = "SELECT * FROM cms_bios";
$bios = mysql_query($query_bios, $Webhost495) or die(mysql_error());
$row_bios = mysql_fetch_assoc($bios);
$totalRows_bios = mysql_num_rows($bios);

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Record display test</title>
</head>

<body>
 
<?php do { ?>
        <div><?php echo $row_bios['name']; ?>&nbsp;&nbsp;<?php echo $row_bios['age']; ?>
        &nbsp;&nbsp;<a href="<?php echo $row_bios['_link'] ?>" target="_self"><img src=" width="<?php echo $row_upload['width'] ?>" height="<?php echo $row_upload['height'] ?>" alt="<?php echo $row_bios['name'] ?>"></a> </div>
        <?php } while ($row_bios = mysql_fetch_assoc($bios)); ?>
        
        
</body>
</html>
<?php
mysql_free_result($bios);
?>


By gregThomas - August 5, 2014

Hi Mel,

At the moment you're accessing the MySQL database directly to retrieve the record you want to display. But CMS Builder comes with some useful functionality that makes retrieving records and all of there linked data (images, drop down selections, details of the user who created the record, etc) much easier. 

In your CMS admin area will be a section called Code Generator, and this will generate the code that allows you to retrieve the records from a particular section. You can read more about it here:

http://www.interactivetools.com/docs/cmsbuilder/code_generator.html

This video also goes over the basic concept of adding records to the front end of your website:

https://www.interactivetools.com/tour/video.php

So for the example you've got below, you'd need to do something like this to display the content:

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
  /* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
  
  // load viewer library
  $libraryPath = 'cmsAdmin/lib/viewer_functions.php';
  $dirsToCheck = array('C:/wamp/www/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

  // load records from 'gallery'
  list($biosRecords, $biosMetaData) = getRecords(array(
    'tableName'   => 'bios',
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1'
  ));

  $bios = $biosRecords[0];

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <title>Record display test</title>
</head>
  <body>
    <div><?php echo $bios['name']; ?>
      &nbsp;&nbsp;<?php echo $bios['age']; ?> 
      &nbsp;&nbsp;
      <?php if(@$bios['new_imageNS'][0]): ?>
        <?php foreach($bios['new_imageNS'] as $image): ?>
          <a href="<?php echo $bios['_link'] ?>" target="_self">
            <img src="<?php ecoh $image['urlPath']; ?>" width="<?php echo $image['width'] ?>" height="<?php echo $image['height'] ?>" alt="<?php echo $bios['name'] ?>">
          </a>
        <?php endforeach; ?>
      <?php endif; ?>
    </div>   
  </body>
</html>

This is just example code, so you might have to make a few changes to get it working (the most likely being the name of the image field).

So I've combined the code that is generated by the code generator and your example code to display the content on the page.

The foreach loop will loop through each image that is uploaded, and display it on the page.

Let me know if you have any other questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By gregThomas - August 5, 2014

Hi Mel,

If your accessing the database from a site that doesn't have a copy of CMS Builder installed, then you'll have to build the query that retrieves the paths to the images manually from the uploads section. 

You'll need to create a MySQL call that connects to the database, and accesses the uploads section (the MySQL table will be called cms_uploads), and have a WHERE statement that filters on the following fields:

  • FieldName - The name of the field (looks to be new_imageNS in this case).
  • tableName - The tablename without the CMS prefix (bios in this case).
  • RecordNum - The record num of the record you're trying to get the uploads for - (This looks to be a single record section, so that would be '1').

So you'r query should probably look something like this:

mysql_select_db($database_Webhost495, $Webhost495);
$query_upload = "SELECT * FROM cms_uploads WHERE `tableName` = 'bios' AND `fieldName` = 'new_imageNS' AND `recordNum` = '1'";
$upload = mysql_query($query_upload, $Webhost495) or die(mysql_error());
$row_upload = mysql_fetch_assoc($upload);
$totalRows_upload = mysql_num_rows($upload);

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By Mel - August 5, 2014 - edited: August 5, 2014

Hi Greg,

With my limited knowledge I think we are heading in the right direction

  • Yes accessing from a site that does not have CMS Builder installed on it - so accessing a central database
  • List page - show a list of one or more records including their thumbnail which is  new_imageNS 
  • Detail page (which is reached by clicking on any of the thumbnails on the List Page to show details about a single record and display the record images the field name in the uploads file for the images being image of which there are approx 6-8 images per individual record

Thanks - Mel

By gregThomas - August 5, 2014

Hi Mel,

Good to hear you're moving in the right direction. Did the upload retrieval code I posted in the previous post work?

If you'd like us to set up the upload, list and detail pages for you, send an e-mail to consulting@interactivetools.com, and we can give you a consulting price to add the system to your site.

Cheers!

Greg

Greg Thomas







PHP Programmer - interactivetools.com