0

I need some help writing a MySQL query. It needs to look at all records, then sort them by first letter in an alpha-numeric range (Ex. numbers & A-C and then only return in $results the first 15 (in alphanumeric order)

Field to search is 'name'

Simple results showing all (I think 15 limit is right)- how do I modify it?

$results = DB::query("SELECT * FROM Mytable ORDER BY ? DESC LIMIT 15");

For Alpha Range:

$results = DB::query("SELECT substr(theme, 1, 1) as Alphabet
  FROM gallery ORDER BY (CASE theme
  WHEN '1%' THEN 1
  WHEN '2%' THEN 2
  WHEN '3%' THEN 3
  WHEN 'A%' THEN 4
  WHEN 'B%' THEN 5
  ELSE -1
  END) LIMIT 15");

Getting: ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 15'

What am I doing wrong - trying to list 1's-3's, then A-B.

Update2: In concert w/ above corrected query, I have a simple foreach to assign variables, then have variable echoes in content: 

$x = 0
foreach ($results as $row) {
  $x++;
  if ($x == 1) {  // first in query
    $t1 = $row['theme'];
    $d1 = $row['developer'];
    $th1 = $row['thumb'];
  }
  ...
}

Nothing is showing, is there something different I have to do in assigning variables?

Update 3: Do you mean this? Still isn't showing.

$x = 0
while ($row = mysql_fetch_array($results)) {
  $x++;
  if ($x == 1) {  // first in query
    $t1 = $row['theme'];
    $d1 = $row['developer'];
    $th1 = $row['thumb'];
  }
  ...
}

Echo Ex. in body:

<img src="<?php echo($th1); ?>" alt="<?php echo($t1); ?>" />
<span><p class="hname"><?php echo($t1); ?></p>
<p class="hdev"><?php echo($d1); ?></p></span>
parti
  • 205
  • 3
  • 15
  • I forgot to add 'END" to my original query. See update. – Jay Blanchard May 30 '14 at 17:06
  • Please don't put code in the comments, edit your post and add that code there. – Jay Blanchard May 30 '14 at 17:39
  • Ok, moved code into post. – parti May 30 '14 at 18:10
  • Any time that you have a new question based on another post you should make a new question with a link back to that post. Having said that (don't do it now) it should be `while($row = mysql_fetch_array($result)) {...` – Jay Blanchard May 30 '14 at 18:18
  • See Update3 question. Still doesn't show. – parti May 30 '14 at 23:33
  • Placed body echo example in question above – parti May 31 '14 at 21:58
  • Which PHP functions are you using for interacting with the database? I notice that you are connecting to a class method `DB::query`. Does that class have a method for iterating through rows? – Jay Blanchard Jun 02 '14 at 12:28
  • I'm using MeekroDB: http://www.meekro.com/quickstart.php Not sure how to translate to their method. – parti Jun 02 '14 at 16:48
  • We're starting to venture off into new territory that is not related to the original question. Can you create a new question about not being able to see the results (with all of the proper code, of course)? – Jay Blanchard Jun 02 '14 at 17:01
  • Ok, posted here: http://stackoverflow.com/questions/23999897/query-mysql-alphanumeric-w-limit-using-meekrodb – parti Jun 02 '14 at 17:14

1 Answers1

1

Do it like this - 

$results = DB::query("SELECT * FROM Mytable ORDER BY name DESC LIMIT 15");

You probably shouldn't have a column named 'name' though. Those kinds of generic words are often reserved by the systems that you're using.

If you need something more specialized, like limiting to a certain portion of the alphabet, your queries start to become much more complex.

SELECT substr(name, 1, 1) as Alphabet
FROM MyTable
ORDER BY (CASE name
    WHEN 'D%'    THEN 1
    WHEN 'E%'    THEN 2
    WHEN 'F%'    THEN 3
    ELSE -1
    END
)
LIMIT 15

Doing the syntax right off the top of my head, you will have to test for your situation.

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
  • How do I change it if the alpha range is changed to D-F ? – parti May 20 '14 at 19:12
  • 1
    When it starts to become specialized the queries much more complex. I have added an example in my answer. – Jay Blanchard May 20 '14 at 19:18
  • Sorry, Jay, I don't see the additional example in your answer ;) – parti May 20 '14 at 19:23
  • It's there now. What are you trying to accomplish with your queries? – Jay Blanchard May 20 '14 at 19:27
  • The DEF one, is similar to the first, limit queries to field starting w/ those letters & then limit further to first 15 - obviously would have to add the DESC LIMIT 15 to the end of your specialized query - thanks for the help. – parti May 20 '14 at 19:35
  • Quick followup, what if I need to list numbers first, then D-F ? And should I add DESC before LIMIT ? – parti May 21 '14 at 19:14
  • You really don't need DESC here because the order is enforced by the THEN's. You can put it there, it won't hurt. If you want numbers first you can do a couple of things a.) add to the WHEN statements or 2.) re-think your filtering logic. – Jay Blanchard May 21 '14 at 19:16