AnsweredAssumed Answered

Populate Field with Unique Random Number

Question asked by Niall Gowanlock on Apr 21, 2014
I've been trying to write a Logic Hook to generate a unique random number but I seem to be getting a problem where by the problem query below doesn't seem to want to run, I get a white screen after creating/editing a record, tried turning on SET GLOBAL general_log = 'ON';  in mysql and reviewing the log it hadn't run.  Running a different query does work.   If I run the select query from mysql directly it works.

This is my first go at a logic hook so maybe that I've just missed something obvious!  Any pointers appreciated.  Thanks!

<?php

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

function add_code(&$bean, $event, $arguments)
{
  $db =  DBManagerFactory::getInstance();
 
  // PROBLEM QUERY
  $query = "SELECT FLOOR(RAND() * 999999) AS random_num
  WHERE "random_num" NOT IN (SELECT "mycustomfield_c" FROM "mycustomtable_cstm")
  LIMIT 1"; 
  // END PROBLEM QUERY

  $result = $db->query($query);
  $row = $db->fetchByAssoc($result);
  $code = $row"mycustomfield_c"

  $update_query = "UPDATE "mycustomtable_cstm" SET "mycustomfield_c" = '$code'
  WHERE id_c = '{$bean->id}' AND ("mycustomfield_c" = '' or "mycustomfield_c" IS NULL)";

   $db->query($update_query, true);
}
}

?>

Outcomes