SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    uk
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT only records WHERE a field has a certain string

    Hi,

    My head is going round in circles...

    I simply want to SELECT all the records in the table WHERE the FIELD e_modules CONTAINS the STRING '0015'

    I'm trying this, but it doesn't work for me:

    $sql = "SELECT * FROM $table_name WHERE LOCATE ('0015','e_modules')";

    The FIELD e_modules may conatin a string like this:

    2000,1834,0015,115,23,45332

    Any help gratefully received

  2. #2
    SitePoint Enthusiast shn's Avatar
    Join Date
    Jan 2006
    Location
    Munich DE
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are searching in the string 'e_modules', not the field `e_modules` (note the backticks instead of single quotes; or just e_modules without any quoting).
    Last edited by shn; Feb 8, 2006 at 09:42. Reason: clarifications added
    Patrick

  3. #3
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,228
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)

    strings

    You could perform a STRING COMPARISON with the wildcard "%" in a WHERE something like this
    Code:
    WHERE e_modules LIKE %0015%

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    And if it contains

    Code:
    2000,1834,00015,115,23,45332
    should that be returned? It will be with the proposed solutions.
    The problem is your database design which is not normalized. Instead of having multiple values in a column, those values should be stored as individual records in a separate table with a foreign key to the original table. The current design is hard to maintain and slow to query.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    uk
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guys,

    All options worked but I settled for something like:

    $sql = "SELECT * FROM $eldb_table_name WHERE LOCATE('$mynum',myfield)";

    I am aware of the 00015,0015 possible problem too, thanks.

    Problem solved.

    Thank you!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •