hey,

i have this query;

$query = "select * from albums where artist like \"$letter%\" order by artist";

which returns results begginging with the letter that is stored in $letter, i need a way of returning all the data that starts with ANY number(not just one number).

thanks in advance

    SELECT ... WHERE SUBSTRING(field, 0, 1) IN (1,2,3,4,5,6,7,8,9,0)

      it doesnt work, tis is what i have.

      thanks

      $query = "select * from albums WHERE SUBSTRING(field, 0, 1) IN (1,2,3,4,5,6,7,8,9,0) order by artist";

      thanks

        SUBSTRING (field, position , count) extracts the portion of field starting at position for count characters.

        So, if fields contains '123ABCD' then SUBSTRING(field,0,1) => '1'

        Having got the first character, we then see if it is a number by checking if is in the set (1,2,3,4,5,6,7,8,9,0)

          Of course it works better when you replace 'field' with the actual fieldname from your database table that you want to test.

            lol, sorry bout that, i was being lazy and didnt read it properly, thanks for your help.

              errr, sorry to be a pain, but there isnt any errors now, but it still doesnt return any results, and i have made sure that there is some in my database that begin with a number.

                I've never tried this, but does this work better

                SUBSTRING(field, 0, 1) IN ('1','2','3','4','5','6','7','8','9','0')

                when we test characters instead of numbers

                  nope still doesnt work,

                  i also tried doing this, SUBSTRING('artist','0','1')
                  heres my code;

                  <?php
 // Get the search variable from URL
   $var = @$_GET['letter'] ;
    $trimmed = trim($var); //trim whitespace from the stored variable

if(!isset($start)) $start = 0;

if ($letter == "123") {

$query = "SELECT * FROM albums WHERE SUBSTRING('artist','0','1') IN ('1','2','3','4','5','6','7','8','9','0') order by artist";
}
else
{

$query = "select * from albums where artist like \"$letter%\" order by artist";
}

//do database connection

@mysql_connect('localhost', '****', '****) or die("ERROR--CAN'T CONNECT TO SERVER");

@mysql_select_db("nabberuk") or die("ERROR--CAN'T CONNECT TO DB");

$result = mysql_query($query); //you should do error checking

$result = mysql_query($query) or die ("Query failed");

//let's get the number of rows in our result so we can use it in a for loop

$numofrows = mysql_num_rows($result);

 $numresults=mysql_query($query);

 $numrows=mysql_num_rows($numresults);

  $trimmed1 = strtoupper($trimmed);



if ($numrows == 0)

  {
   if ($letter == "123"){

   echo "<p>Sorry, there isn't any artists beginning with a number.<p>";

   }
   else {
    echo "<p>Sorry, there isn't any artists beginning with: &quot;" . $trimmed1 . "&quot;<p>";

     }

  }



echo "<table width=90% border=0 cellspacing=0 cellpadding=0>

  <tr>";

 for($i = 0; $i < $numofrows; $i++) {

$row = mysql_fetch_array($result); //get a row from our result set

if($i % 2) { //this means if there is a remainder

    echo "<TR bgcolor=\"lightgrey\">\n";

} else { //if there isn't a remainder we will do the else

    echo "<TR bgcolor=\"white\">\n";

}

echo "<td width=30%>".$row[artist]."</td>\n";

echo "<td width=30%>".$row[title]."</td>\n";

echo "<td width=5%><a href=".$row[link]."><img src=images/sig2dat.gif width=15 height=15 border=0></a></td>\n";


if ($row[buy] == "")	{
	echo "<td width=5%>&nbsp;</td>\n";
} else {
	echo "<td width=5%><a href=" .$row[buy]."target=_blank><img src=images/buy.gif width=15 height=15 border=0></a></td>\n";
}
echo "</TR>\n";
}



echo "</TABLE>\n";

?>

                    MySQL SUBSTRING start its position count at 1 , not 0

                    Use SUBSTRING(artist, 1, 1)

                      Sorry about my misleading info with the substring function.

                        Write a Reply...