Newbie MySQL Query

[JREAM]

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

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:

    $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:

    $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?

[risoknop]

OK.

Firstly,

The database structure goes like this:

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

To illustrate my point:

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):

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:

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.

[Jake Arkinstall]

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:

$query = "SELECT * from categories WHERE name like '%{$trimmed}%'"; 

[risoknop]

I have added a scheme of database... it took me like 10 minutes to make it

[JREAM]

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

[risoknop]

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/

[JREAM]

That now got VERY confusing, Ill have to take my time VERY sweetly