Treehouse: Grow your CSS skills. Land your dream job.

Multiple Query question!

  • # May 19, 2012 at 1:12 pm

    So what I’m doing exactly is making a search page. I want to be able to search for the wall post’s content, and the name of the user who posted it. Now I could line them up like







    … But why would I do that if I could mix them together so the most accurate will display on the top?
    I’m currently using this query to list information:

    $query = mysql_query("SELECT * FROM wallposts WHERE content LIKE '%$searchq%'");
    while ($row = mysql_ [...]

    … Any way I can use multiple, like WHERE content OR username LIKE … ?

    – Thank’s :) !

    # May 19, 2012 at 10:04 pm
    SELECT * FROM `wallposts` WHERE '%searchterm%' IN( `content`,`username` )

    You might also be interested in MySQL’s FULLTEXT indexing/searching, which is capable of ranking results as well.

    # May 20, 2012 at 6:04 am

    @traq – Thank you! I will look into FULLTEXT, and correct me if I’m wrong but this will be the query?

    mysql_query("SELECT * FROM 'wallposts' WHERE '%$searchq%' IN('content','username')");
    # May 20, 2012 at 12:57 pm

    No – compare:

    // I wrote:
    SELECT * FROM `wallposts` WHERE '%searchterm%' IN( `content`,`username` )

    // you wrote:
    SELECT * FROM 'wallposts' WHERE '%$searchq%' IN('content','username')

    These are different. Identifiers (e.g., table/column names) are delimited with backticks ( ` ); strings are delimited with apostrophes ( ' ).
    Backticks aren’t always necessary (you can safely leave them off in this case), but IMO they’re a good habit to get into. They can prevent problems where a table/column name conflicts with a reserved word:

    SELECT key FROM table WHERE values = 'whatever'
    # causes an SQL error, since KEY, TABLE, and VALUES are all reserved words
    SELECT `key` FROM `table` WHERE `value` = 'whatever'
    # works fine

    Obviously, the best solution to this is to not use reserved words as your table/column names. However, there are quite a lot of them, some are obscure, new ones are added from time to time, and sometimes the word is such a “natural choice” for your data structure that you completely overlook the conflict — so I feel it’s best to be explicit about it.

    (On most US keyboards, the backtick key is next to the 1 key, top left.)

    [ edit ]
    If this is a new project, you might consider using mysqli (or PDO) instead of the mysql extension. ext_mysql is outdated (since MySQL 5) and is scheduled to be deprecated.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.