Quantcast
Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
    1. Welcome to GTAForums!

    1. GTANet.com

    1. GTA Online

      1. The Cayo Perico Heist
      2. Find Lobbies & Players
      3. Guides & Strategies
      4. Vehicles
      5. Content Creator
      6. Help & Support
    2. Red Dead Online

      1. Frontier Pursuits
      2. Find Lobbies & Outlaws
      3. Help & Support
    3. Crews

    1. Red Dead Redemption 2

      1. PC
      2. Help & Support
    2. Red Dead Redemption

    1. Grand Theft Auto Series

      1. St. Andrews Cathedral
    2. GTA VI

    3. GTA V

      1. Guides & Strategies
      2. Help & Support
    4. GTA IV

      1. The Lost and Damned
      2. The Ballad of Gay Tony
      3. Guides & Strategies
      4. Help & Support
    5. GTA San Andreas

      1. Guides & Strategies
      2. Help & Support
    6. GTA Vice City

      1. Guides & Strategies
      2. Help & Support
    7. GTA III

      1. Guides & Strategies
      2. Help & Support
    8. Portable Games

      1. GTA Chinatown Wars
      2. GTA Vice City Stories
      3. GTA Liberty City Stories
    9. Top-Down Games

      1. GTA Advance
      2. GTA 2
      3. GTA
    1. GTA Mods

      1. GTA V
      2. GTA IV
      3. GTA III, VC & SA
      4. Tutorials
    2. Red Dead Mods

      1. Documentation
    3. Mod Showroom

      1. Scripts & Plugins
      2. Maps
      3. Total Conversions
      4. Vehicles
      5. Textures
      6. Characters
      7. Tools
      8. Other
      9. Workshop
    4. Featured Mods

      1. Design Your Own Mission
      2. OpenIV
      3. GTA: Underground
      4. GTA: Liberty City
      5. GTA: State of Liberty
    1. Rockstar Games

    2. Rockstar Collectors

    1. Off-Topic

      1. General Chat
      2. Gaming
      3. Technology
      4. Movies & TV
      5. Music
      6. Sports
      7. Vehicles
    2. Expression

      1. Graphics / Visual Arts
      2. GFX Requests & Tutorials
      3. Writers' Discussion
      4. Debates & Discussion
    1. Announcements

      1. GTANet 20th Anniversary
    2. Support

      1. Court House
    3. Suggestions

[MySQL] A few problems


andibomb

Recommended Posts

Well, since the post wasn't added first time, I'll add it now.

 

The first problem is that on my site, I have an interactive Library of all of my songs, albums etc. I have a php script which allows you to view each artist, grouped by the first letter in their name. The query I use at the moment makes viewing bands with "The" before their name difficult as I would like bands like "The Chemical Brothers" to come under "C". Is there something I can add to my current query to make this word exempt from the LIKE operator?

 

Current query where $search is the letter the user has specified:

 

 

SELECT DISTINCT Artist FROM playlist WHERE Artist LIKE "$search%"

 

 

I would also like to be able to group band names that start with an integer. What would $search need to be in order for the script to list band names such as "311"? To clarify, I don't want to display names that start with 1-9 indivdually, but together.

 

Thanks for your help.

Link to post
Share on other sites

I don't particularly want to go through all the Bands changing the position of "The". This would be a last resort.

 

The second option is a good idea, yet bands with "The" in them will still appear in the T category.

Link to post
Share on other sites

You could just run a script to make the changes...

 

If that's really not an option you'll have to use a regexp, but that seems a little excessive.

Link to post
Share on other sites

Yeah, I don't think I am going to go that far. I was just looking for an easier alternative. I think I'll just edit the entries as you said. Thanks for your help. smile.gif

 

Any idea's about displaying bands beginning with numbers?

Link to post
Share on other sites

 

If that's really not an option you'll have to use a regexp, but that seems a little excessive.

I lie. A little bit messy but:

 

 

$WHERE = ($search=='t') ? "(LIKE 'T%' AND NOT LIKE 'The %') OR LIKE 'The T%'" : "LIKE '$search%' OR LIKE 'The $search%'";

 

 

 

SELECT Artist FROM playlist WHERE $WHERE

 

 

Something like that ought to do the trick.

 

 

Not sure about how you'd do the numbers.. (other than using 10 OR LIKE '2%'s).

Link to post
Share on other sites

Thanks a lot fred, that worked great. Although I had to put Artist before each LIKE command.

 

Turned out like this:

 

 

$where = ($search == 'T') ? "Artist LIKE 'T%' AND Artist NOT LIKE 'The %' OR Artist LIKE 'The T%'" : "Artist LIKE '$search%' OR Artist LIKE 'The $search%'";

 

 

Is there a shorthand way to cut down on all this? Just out of curiosity.

Edited by andibomb
Link to post
Share on other sites
$where = ($search == 'T') ? "Artist LIKE 'T%' AND Artist NOT LIKE 'The %' OR Artist LIKE 'The T%'" : "Artist LIKE '$search%' OR Artist LIKE 'The $search%'";

 

 

Is there a shorthand way to cut down on all this? Just out of curiosity.

It's not really that long but you could trim it down to:

 

$where = "Artist LIKE '$search%' " . (($search=='T') ? "AND Artist NOT LIKE 'The %' " : "") . "OR Artist LIKE 'The $search%'";

 

Link to post
Share on other sites

Segosa, that's a good suggestion but I would prefer to keep the band names intact.

 

EDIT:

@fred, I think I'll keep it as it is, it's more readable for me that way.

Edited by andibomb
Link to post
Share on other sites
Wouldn't it be easier to str_replace "the " and "the " with "" before inserting it into the query?

What is the difference between "the " and "the "?

 

 

$search = str_replace('the ', '', $string);

 

 

str_replace() is your friend.

Link to post
Share on other sites

@Svip, I think I'd still prefer to keep the prefix intact and freds query let's me do that.

 

Any ideas on the number problem yet?

Link to post
Share on other sites

Not exactly a great way to do it but:

 

 

WHERE ASCII(Artist) BETWEEN 48 AND 57

 

 

ASCII apparently gives you the ASCII value for the first character in the string. Between 48 and 57 should give you all the values for 0 through 9.

Link to post
Share on other sites
Sounds good fred, I'll be sure to try that one out, although there must be a more simple, understandable way to do it.
Link to post
Share on other sites
Wouldn't it be easier to str_replace "the " and "the " with "" before inserting it into the query?

What is the difference between "the " and "the "?

 

 

$search = str_replace('the ', '', $string);

 

 

str_replace() is your friend.

I meant "the " and " the".

Link to post
Share on other sites
Sounds good fred, I'll be sure to try that one out, although there must be a more simple, understandable way to do it.

There's no "is_first_character_a_number" function, unfortuantely. I think your best option would be to actually add another column to the database to store where each record goes (eg, 0 for bands starting with a number, and then A, B, C, etc) - yeh, it'd take up an extra byte of space for every row you have but it'd also make your queries a lot nicer. You'd just need to use a simple WHERE BeginsWith = X instead of all the LIKEs...

Link to post
Share on other sites

I could do that. It wouldn't be hard to make another column and use a script to populate each row with the first letter.

 

Thanks again for your input inlove.gif .

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • 1 User Currently Viewing
    0 members, 0 Anonymous, 1 Guest

×
×
  • Create New...

Important Information

By using GTAForums.com, you agree to our Terms of Use and Privacy Policy.