Solved

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

Posted on 2008-10-06
3
291 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 109

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

786 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