« previous next »
Pages: [1]

Author Topic: MYSQL query help please  (Read 871 times)

0 Members and 1 Guest are viewing this topic.

Offline d_barszczakTopic starter

  • Enthusiast
  • Posts: 188
  • Gender: Male
  • Integrated IT Systems Ltd
    • View Profile
    • integrateditsystems.co.uk - Your web solution
MYSQL query help please
« on: December 06, 2007, 08:04:59 AM »
Hi all,

I have a column that stores id numbers for courses called idnumber i.e.

THS-ICT-001
THS-ICT-002
THS-MATHS-001
THS-MATHS-002

I want to use a DISTINCT query to return THS-ICT, THS-MATHS without the final -001.

Is this possible?
Logged
When all else fails - Try reading the manual.

Offline rajivgonsalves

  • Addict
  • Posts: 2,160
  • Gender: Male
  • Whizzkid
    • View Profile
    • My Personal Website
Re: MYSQL query help please
« Reply #1 on: December 06, 2007, 08:14:26 AM »
try this

Code: [Select]
SELECT DISTINCT substring(idnumber,1,LENGTH(idnumber)-4) FROM tablename
Logged
Cheers!
Rajiv

Code: [Select]
// commenting code... nah!!! if it was hard to write, it should be hard to read :P
echo implode('', array_map('chr', explode(',','87,104,105,122,122,107,105,100')));

Offline d_barszczakTopic starter

  • Enthusiast
  • Posts: 188
  • Gender: Male
  • Integrated IT Systems Ltd
    • View Profile
    • integrateditsystems.co.uk - Your web solution
Re: MYSQL query help please
« Reply #2 on: December 06, 2007, 08:35:11 AM »
Cheers,

Are you some sort of mysql god? lol
Logged
When all else fails - Try reading the manual.

Offline fenway

  • MySQL Si-Fu / PHP Resident Alien
  • Global Moderator
  • 'Mind Boggling!'
  • *
  • Posts: 15,443
  • Gender: Male
    • View Profile
Re: MYSQL query help please
« Reply #3 on: December 06, 2007, 10:00:33 AM »
I'll obviously mention that you shouldn't be storing non-atomic values in a single field... but for this to be robust, you should probably be looking for the last "-"... what if the number is longer than 3 characters?
Logged
:anim_rules: Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

Offline d_barszczakTopic starter

  • Enthusiast
  • Posts: 188
  • Gender: Male
  • Integrated IT Systems Ltd
    • View Profile
    • integrateditsystems.co.uk - Your web solution
Re: MYSQL query help please
« Reply #4 on: December 06, 2007, 04:53:52 PM »
Your absolutly right but...

The database is a Moodle Installation that will be transfered to a bigger solution. The id number is just a tag to make it easier to import.

Plus the THS is the initials of the company and MATHS would be the department and the 001 is the unique id and i know there will never be 999 maths courses before we transfer.
Logged
When all else fails - Try reading the manual.

Offline fenway

  • MySQL Si-Fu / PHP Resident Alien
  • Global Moderator
  • 'Mind Boggling!'
  • *
  • Posts: 15,443
  • Gender: Male
    • View Profile
Re: MYSQL query help please
« Reply #5 on: December 06, 2007, 05:37:19 PM »
Quote from: d_barszczak on December 06, 2007, 04:53:52 PM
Your absolutly right but...

The database is a Moodle Installation that will be transfered to a bigger solution. The id number is just a tag to make it easier to import.

Plus the THS is the initials of the company and MATHS would be the department and the 001 is the unique id and i know there will never be 999 maths courses before we transfer.
If it's a temporary hack, then no problem... I just tend to find that "temporary" often lasts a lot longer than expected, and the extra work at the outset is insignificant in the long run.  Just my $0.02.
Logged
:anim_rules: Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

Offline d_barszczakTopic starter

  • Enthusiast
  • Posts: 188
  • Gender: Male
  • Integrated IT Systems Ltd
    • View Profile
    • integrateditsystems.co.uk - Your web solution
Re: MYSQL query help please
« Reply #6 on: December 11, 2007, 03:57:08 AM »
No worries you have helped me a lot in the past and it's always worth listening to your advice.  :)

Your right though i have learned that tempoary hacks can be left longer that you expect them too.


Also i did say it was a moodle installation which means that all input relys on teachers so getting them to populate a vle with 999 letters is hard enough never mind 999 courses.  ;D
Logged
When all else fails - Try reading the manual.

Offline fenway

  • MySQL Si-Fu / PHP Resident Alien
  • Global Moderator
  • 'Mind Boggling!'
  • *
  • Posts: 15,443
  • Gender: Male
    • View Profile
Re: MYSQL query help please
« Reply #7 on: December 11, 2007, 10:11:16 AM »
Quote from: d_barszczak on December 11, 2007, 03:57:08 AM
Also i did say it was a moodle installation which means that all input relys on teachers so getting them to populate a vle with 999 letters is hard enough never mind 999 courses.  ;D
Didn't get that part... ;-) Good luck.
Logged
:anim_rules: Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

Offline d_barszczakTopic starter

  • Enthusiast
  • Posts: 188
  • Gender: Male
  • Integrated IT Systems Ltd
    • View Profile
    • integrateditsystems.co.uk - Your web solution
Re: MYSQL query help please
« Reply #8 on: December 12, 2007, 10:01:34 AM »
Guess you need to work in education to get that one.

Basically teachers want a vle/mle but can any be bothered to supply content for it... nup..
Logged
When all else fails - Try reading the manual.
Pages: [1]
« previous next »