Jump to content


Photo

Selecting columns beginning with a numeric character


  • Please log in to reply
4 replies to this topic

#1 Pmzine

Pmzine
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 24 May 2006 - 04:31 PM

Hey,

Hopefully a quick and easy question, I have a MYSQL database with reviews in it and an A-Z archive. For instance, if you click 'A' link it will show all reviews beginning with the letter 'A'.

I want to be able to grab the reviews beginning with a numeric value though and can't figure out the character to pass to the SQL statement.

Any ideas?

#2 urbandsigns.com

urbandsigns.com
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 24 May 2006 - 04:55 PM

[!--quoteo(post=376726:date=May 24 2006, 11:31 AM:name=Pmzine)--][div class=\'quotetop\']QUOTE(Pmzine @ May 24 2006, 11:31 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Hey,

Hopefully a quick and easy question, I have a MYSQL database with reviews in it and an A-Z archive. For instance, if you click 'A' link it will show all reviews beginning with the letter 'A'.

I want to be able to grab the reviews beginning with a numeric value though and can't figure out the character to pass to the SQL statement.

Any ideas?
[/quote]

Pass in a # sign and then do an If statement for each number.

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 May 2006 - 10:52 PM

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 Pmzine

Pmzine
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 26 May 2006 - 05:42 PM

This is the SQL statement I'm using so far:

$sql = "select * from cdreviews ";

if ($_GET['sort_by']) {
  $letter = $_GET['sort_by'];
  $sql .= "where name like '".$letter."%' ORDER BY name ASC";
} else {
   $sql .= "order by dateadded DESC limit 10";
}

How would I integrate that statement into it?

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 26 May 2006 - 06:06 PM

Something like

$sql = "select * from cdreviews ";

if ($_GET['sort_by']) {
  $letter = $_GET['sort_by'];
  if ($letter=='#') {
      $sql .= "WHERE SUBSTRING(name, 1,1) IN ('0','1','2','3','4','5','6','7','8','9') ORDER BY name ASC";
  } 
  else {
      $sql .= "where name like '$letter%' ORDER BY name ASC";
  }
} else {
   $sql .= "order by dateadded DESC limit 10";
}

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users