Display records that start with a number
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?
So others may benefit, here is what I ended up with that works:
in the 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="[0-9]">0-9</option>
<option value="a%">A</option>
<option value="b%">B</option>
<option value="c%">C</option>
and changes to viewer:
$regex = $_GET['alphasearch'];
// load records from 'categories'
list($categoriesRecords, $categoriesMetaData) = getRecords(array(
'tableName' => 'categories',
'perPage' => '5',
'loadUploads' => false,
'allowSearch' => true,
'where' => "Bus_name REGEXP '^".$regex.".*' OR Bus_name LIKE '".$regex."'",
'orderBy' => 'Bus_name',
By Dave - September 18, 2017
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:
https://www.interactivetools.com/docs/cmsbuilder/viewer_search.html
Hope that helps!
interactivetools.com