By panoramical1 on

Hi there

Just made a module which essentially fetches a random image from a gallery and displays it. My PHP is as follows:

// Append trailing slashes on path if not present
  if ($path{count($path)-1} != '/') {
    $path .= '/';
  }

  // Get the 'tid' of the Random Images gallery
  $tid_qu = db_query("SELECT tid FROM term_data WHERE name='$gallery'");
  if ($tid_arr = db_fetch_object($tid_qu)) {
    $tid = $tid_arr->tid;
  }
		
  // Get all image 'nid's in this gallery
  $nid_qu = db_query("SELECT nid FROM term_node WHERE tid='$tid'");
  while ($nid_arr = db_fetch_object($nid_qu)) {
    if (!isset($i)) {
      $i = -1;
    }
	  
    $nids[$i++] = $nid_arr->nid;
  }
	
  // choose the image we want
  $nid = $nids[rand(0,$i)];
  
  // Get the appropriate 'fid' from image
  $fid_qu = db_query("SELECT fid FROM image WHERE nid='$nid' AND image_size='random image'");
  if ($fid_arr = db_fetch_object($fid_qu)) {

  // Get the filepath of the image
    $file_qu = db_query("SELECT filepath FROM files WHERE fid='$fid_arr->fid'");
    if ($file_arr = db_fetch_object($file_qu)) {
      $filepath = 'http://drupal.panoramical.co.uk/'.$file_arr->filepath;
	}	
	// Get the title of the image
	$name_qu = db_query("SELECT title FROM node WHERE nid='$nid'");
	if ($name_arr = db_fetch_object($name_qu)) {
	  $title = $name_arr->title;
	}
  }
	
  $block_content .= '<img src="'.$filepath.'" alt="'.$title.$nid.':'.$fid_arr->fid.'"/>';
  return $block_content;

However, it seems to be very slow. Could anybody suggest why this is so slow (is there a quicker route), or is it just my server being dodgy?

Thanks

⋅ Categories: Drupal 6.x

Comments

panoramical1’s picture

panoramical1 commented

Sorry - my code is all over the place. This is the problem transferring to the drupal coding standards!

nevets’s picture

nevets commented

Not sure why it is slow but your approach to getting a random image may be part of the issue. Personally I would let the views module do the heavy lifting. Here is a version that lets mySql pick the random image and also reduces the number of queries.
Note this is untested so it may contain typos.

// Append trailing slashes on path if not present
  if ($path{count($path)-1} != '/') {
    $path .= '/';
  }

  // Get the 'tid' of the Random Images gallery
  $tid_qu = db_query("SELECT tid FROM {term_data} WHERE name='%s", $gallery);
  if ($tid_arr = db_fetch_object($tid_qu)) {
    $tid = $tid_arr->tid;
  }

  // Get all image 'nid's in this gallery
  $nid_qu = db_query("SELECT nid, RAND() AS _random FROM {term_node} WHERE tid='%d' ORDER BY _random ASC", $tid);
  if ($nid_arr = db_fetch_object($nid_qu)) {
 
    $nid = $nid_arr->nid;

	  // Get the appropriate 'fid' from image
	  $sql = "SELECT title,i.fid,filepath FROM {node} as n JOIN {image} AS i ON (n.nid = i.nid) JOIN {files} f ON (i.fid = f.fid) WHERE n.nid = %d AND image_size='random image'"; 
	  $results = db_query($sql, $nid);
	  if ($data = db_fetch_object($results)) {
		  $block_content .= '<img src="/'.$data->filepath.'" alt="'.$data->title.$nid.':'.$data->fid.'"/>';
		  return $block_content;
		}
	}
panoramical1’s picture

panoramical1 commented

Thanks very much. It appears I need to brush up on SQL syntax. I think the problem is that my server is just being mega slow though. Hopefully will improve soon...