SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Newbie MySQL Query

    Hey I am not good at MySQL but I kind of get a little bit of it, I understand this I think in order of parents,
    DB > Table > Row > Data
    And the goal is to search through Data, Ill try to explain this so it makes sense, it shouldnt be very tricky i dont think, im just inexperienced

    Hey I have a database like this (very small):

    database: test
    HTML Code:
    tables:        category,         files,         reference,         type
                    (These below is where the "info" is stored, I don't know if these are tables/sub-tables or what)
                    > catogeryID,
                    > categoryName,
                    > categoryStatus,
                    > friendlyName,
    For mySQL query:
    Code PHP:
        $query = "SELECT * from category like \"%$trimmed%\"  ;

    I'm guessing the above is looking for only the names of those rows?
    So would I do something like:
    PHP Code:
        $query "SELECT * from categoryID like \"%$trimmed%\"; 
    Is this searching rows? I don't think it is.
    Do I have to define which Table then Row to search?
    Its not getting any results, I think Im doing that wrong

    Does anyone know what Im doing wrong?

  2. #2
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK.

    Firstly,

    The database structure goes like this:

    Database->Tables->Columns/Rows->Items

    To illustrate my point:

    Code:
    DATABASE
    
    -----------------------------------------------------------------------
    | table1  | column1   | column2   |  column3  | ...       | columnN   |
    |----------------------------------------------------------------------
    | row1    | item[1][1]| item      | item      | ...       | item[1][N]|
    | row2    | item      | item      | item      | ...       | item[2][N]|
    | row3    | item      | item      | item      | ...       | item[3][N]|
    | ...     | ...       | ...       | ...       | ...       | ...       |
    | rowN    | item[N][1]| item[N][2]| item[N][3]| ...       | item[N][N]|
    -----------------------------------------------------------------------
    
    -----------------------------------------------------------------------
    | table2  | column1   | column2   | column3   | ...       | columnN   |
    |----------------------------------------------------------------------
    | row1    | item[1][1]| item      | item      | ...       | item[1][N]|
    | row2    | item      | item      | item      | ...       | item[2][N]|
    | row3    | item      | item      | item      | ...       | item[3][N]|
    | ...     | ...       | ...       | ...       | ...       | ...       |
    | rowN    | item[N][1]| item[N][2]| item[N][3]| ...       | item[N][N]|
    -----------------------------------------------------------------------
    
    And more tables...
    To select only few rows from the table, you can run query like this (the condition after WHERE defines what rows will be selected):

    Code sql:
    SELECT * FROM table1 WHERE column1 = value1

    The above query will select those rows from the table called table1 where the item in the column named column1 is "value1". It will select all columns in rows that meet the condition.

    If you want to select only certain columns, run this query:

    Code sql:
    SELECT column1, column2, column3 FROM table1 WHERE column1 = value1

    The above query will select, again, rows where the column1 contains "value1" value. Notice, that this time, the query will select only columns: column1, column2, column3, not all columns.

    One more thing: You must escape a string in order to successfully use it in SQL query.
    Last edited by risoknop; Sep 14, 2008 at 13:51.

  3. #3
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    So you need to use 'WHERE field LIKE ....' instead.

    On top of that, you should enclose your values in single quotes with SQL. Whilst MySQL doesn't complain, others do and it's good practice to keep valid.

    Example:
    PHP Code:
    $query "SELECT * from categories WHERE name like '%{$trimmed}%'"
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have added a scheme of database... it took me like 10 minutes to make it

  5. #5
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow thats a really nice explanation, that makes a lot of sense, its just like an excel sheet, I thought it was only rows, thats cool it does columns too.

    Thanks dude

  6. #6
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also you should use regular expressions to make the search better - if you just search for some word or phrase, there are some problems - if you have % in the phrase, it will return all rows, also it won't search for individual words of your phrase but for the whole phrase.

    Here is a good tutorial: http://www.iamcal.com/publish/articles/php/search/

  7. #7
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That now got VERY confusing, Ill have to take my time VERY sweetly


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
  •