CODEWITHSUNDEEP
phpmysql
Cosmi
Cosmi

Reputation: 67

Select where first character is number

I'm trying to organize my content by the first character. By now, I did something like this:

PHP:

$first_char = $_GET['search'];
$sql = "SELECT id
        FROM table
        WHERE SUBSTR(title,1,1) = '".$first_char."'"
....

HTML:

<a href="?search=a">[A]</a>
<a href="?search=b">[B]</a>
...
<a href="?search=z">[Z]</a>
<a href="?search=nr">[#]</a>

The thing is that when [A] is clicked, it shows titles starting with "A". What I want to do is when [#] is clicked, it should show all the posts starting with a number. So, when $_GET = 'nr' it shows all the titles starting with a number... I tried to do this with array(), but I failed.

Do you have any suggestions?

Upvotes: 6

Views: 2197

Answers (3)

corretge
corretge

Reputation: 1759

The easy way is using regular expressions:

SELECT * FROM yourTable
where yourField REGEXP '^[[:digit:]].*$'

EDIT explain the RegExp

 ^               Start string
 [[:digit:]]     digit one time
 .               everything
  *              zero or more times
 $               End Of String

Read comments to view that 

  .*$

is not necessary :)

Upvotes: 3

Marc B
Marc B

Reputation: 360602

<?php

if ($_GET['search'] == 'nr') {
    $where = "REGEXP '^[[:digit:]]'";
} else {
    $where = "= '" . mysql_real_escape_string($_GET['search']) . "'";
}

$sql = "SELECT .... FROM table WHERE $where";

Upvotes: 0

Bluewind
Bluewind

Reputation: 1064

$sql = "SELECT id
        FROM table
        WHERE SUBSTR(title,1,1) in (1,2,3,4,5,6,7,8,9,0)"

Upvotes: 9

Related Questions