3 posts by 2 authors in: Forums > CMS Builder
Last Post: September 18, 2017   (RSS)

I have a dropdown:

<form name="alphasearch" action="ialphalist.php" method="get">
<select style='font-size:26px;height:30px;width:540px;' name='alphasearch' onchange='this.form.submit()'>
   <option value="">Select the First letter of the Business Name</option>
   <option value="REGEXP'^[0-9]'">0-9</option>
   <option value="a%">A</option>
   <option value="b%">B</option>
   <option value="c%">C</option>

snippet from ialphalist.php

list($categoriesRecords, $categoriesMetaData) = getRecords(array(
    'tableName'   => 'categories',
    'perPage'     => '5',
    'loadUploads' => false,
    'allowSearch' => true,
 'where'       => "Bus_name LIKE '$_GET[alphasearch]'",
  'orderBy'    => 'Bus_name',

The letters display results as expected, I am trying to get if the first character is a number to work. I thought that using the "REGEXP" statement was supposed to work, maybe I have it formatted wrong? What do I need to change to get this to work?

Hi dnevels, 

That works, but for security, you need to be sure you escape any user submitted input.  Otherwise, people could submit any random MySQL to be executed by your server.  You can escape your inputs like this:  mysql_escape($var);

Try this: 

 'where'       => "Bus_name REGEXP '^" .mysql_escape($regex). ".*' OR Bus_name LIKE '" .mysql_escape($regex). "'",

Another way to do this is to create another variable that is escaped above: 

$regex = $_GET['alphasearch'];
$regexEscaped = mysql_escape($regex);

And then you can use it inline:

 'where'       => " Bus_name REGEXP '^$regexEscaped.*' OR Bus_name LIKE '$regexEscaped' ",

And in future, if all you need is single character matching try the built in search feature called "fieldname_prefix" here: 

Hope that helps!

Dave Edis - Senior Developer