Getting pictures to show using mySql query

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

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 Mel - August 5, 2014

Hi Greg,

Yes I am accessing the database direct as I am connecting via an external site, I am using the CMS as a way of easily inputting records etc into the main database

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