Solved

MySql - LEFT - Need to match 1,2,3 + characters

Posted on 2008-10-06
3
290 Views
Last Modified: 2013-12-12
I have a simple form on my website where people can lookup a country code.  The country codes are either 1, 2, or 3 digits in length.  The problem is that my results are not showing all possible matches.

Example:

The code 55 is Brazil.  55629 is Brazilian cell phones.

When I search for 55 I want to see anything that stats with the user entered valued, 55.

Just like if I entered 5 the result would be

5 - Country X
55 - Brazil
55629 - Brazilian Cell Phones

Here is what I have so far but clearly does not work.


PHP PAGE

-------------
 

$dbconn = mysql_connect($hostname, $username, $password) or die("EPIC FAIL: Unable to connect to MySQL DB");
 

$database = mysql_select_db("te_support", $dbconn);
 

$query  = "SELECT Area, Rate FROM OWF_RATES WHERE Code = LEFT('" . $_POST["cCode"] . "',1)";

$result = mysql_query($query) or die(mysql_error());
 

$rows = array();

while($row  = mysql_fetch_array($result))

{

	$rows[] = $row;

}

mysql_free_result($result);

mysql_close($dbconn);

	
 

$w->setvar("rows", $rows);
 

$w->fdisplay("rates.tpl", true);
 
 
 
 

------------------------------ HTML PAGE --------------
 

<table>

		<th>

		  <div align="center">{$LANG_country_area_region_name}</div>

		</th>

		<th>

		  <div align="center">{$LANG_rate_per_minute}  ( US$ )</div>

		</th>

          {foreach from=$rows item=row}

            <tr>            </tr>

          <tr>

            <td>{$row.Area}</td>

            <td>

              <div align="center">{$row.Rate}</div>

            </td>

          </tr>

          {/foreach}

        </table>

Open in new window

0
Comment
Question by:joedunn
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22650891
this should work better:
$query  = "SELECT Area, Rate FROM OWF_RATES WHERE Code LIKE '" . $_POST["cCode"] . "%' ORDER BY LEN(Code) DESC ";

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 22654447
Might want that "order by" to be "LEN(Code) ASC" so the short codes come out first?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22654494
>Might want that "order by" to be "LEN(Code) ASC" so the short codes come out first?
correct. I am use to use DESC in similar situations to get the longest match first.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now