SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member Woodentigger's Avatar
    Join Date
    Mar 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using AND in where section of the query? and shortening Query strings

    The following is on an apache webserver using mySQL and PHP

    The first issue is a query where i want to pick records out of a table where two columns have the same value. I have tried using the AND statement in the WITH section of the query but that don't seem work. The second query i want to use the trim command on a variable that is put into a search string.

    I am unsure of the syntax in both cases and wondered whether anyone could assist

    I have removed some of the columns to shorten the post.

    Query one

    SELECT
    tblBooks.book_id,
    tblBooks.author,
    tblBooks.title,
    tblBooks.short_desc,
    tblBooks.cat_id,
    tblBooks.also,
    tblCat.cat_image,
    tblCat.catTitleImage,
    tblBooks.short_desc,
    FROM tblBooks
    INNER JOIN tblCat ON tblBooks.cat_id = tblCat.cat_id
    WHERE tblBooks.cat_id AND tblBooks.also = URLPARAMETER ORDER BY title ASC

    This is required so that a book can apear in two different categories, the other option is an array or to enter the book twice...

    This query is to search the books table and find all the books with a specific value in both the Cat and also columns to display according to the value passed to it by the website.


    Query two

    SELECT * FROM tblBooks WHERE author left($colname_rsAuthors,5) ORDER BY book_id ASC

    Here i want to do a search for all names that have a specific set of characters in it. I can't use an exact query as there are variations on the manner in which the names are used, some times alone sometimes in conjunction with others, I want to find all variants.

    I am sure it is a simple syntax issue, alternatively I need to look at building an array and doing a multi sort on these two issues.

    Please advise as to which is better and what is the best way to handle these queries.

    I look froward to a response

    Thanks

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2003
    Location
    Mexico City
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On your first query, I don't know if I misunderstood you request, but it would seem you need to use OR, instead of AND.

    You say a book can belong to two categories, and I'd assume they wouldn't be the same?

    There is a normalization issue with your schema. There may be valid reasons for you to take this approach, but in case you haven't, you should analyze if it would be better to have a separate join table, say tblBooks_Cats, where you can store ids of both books and categories.

    For your second query, try this:

    SELECT * FROM tblBooks WHERE author LIKE '%$colname_rsAuthors%' ORDER BY book_id ASC

  3. #3
    SitePoint Member Woodentigger's Avatar
    Join Date
    Mar 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Wow

    Thanks for the quick reply.

    On the first query initially the books would only belong to one category hence inclusion of the category in the books table. However it is now evident that the there are certain books that fal in more than one category and need to show up in both categories that they are linked to hence the AND statement. If OR is uwould it not miss soem records?

    What you suggest makes sense by adding a books category table would make some intersting joins, basically the query would read something like the folowing:
    SELECT * from books table inner join with books _category table to get the books in a specific category and join cat_id with categories table Cat_iD to get the category image based on the URL Parameter>

    Alternatively would it be better to approach it the other way around where the book_category Tables book_id is linked to the tblBooks book_id and the Cat_id is linked to tblCategory cat_id to get category image where = URL parameter, these may be the better way around. What is your opinion?

    In the Second Query the query you propose is the one that is in use, however because the search parameter is based on the full surname and first name of the author EG Edith Blighton it finds all records where Edith Blighton is used alone and ignores records where it is Edith Blighton and Joe Somebody, hence why i want to shorten the search string to widen the results. I had hoped to trim the value sent to the quey string but am unsure of the syntax to do so. Research suggested to use the left trim option. Any thoughts on the syntax?

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2003
    Location
    Mexico City
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have it the other way around with AND/OR

    On the contraire, using AND *would* miss some records. Unless you want to show *only* books that belong to *both* categories, but not to just one, OR is the way to go. In fact, your original query is even more restrictive, in that it only returns books whose cat_id AND also are the same, i.e., books that belong to "Horror" AND "Horror".

    I don't know if I made sense

    If you change your approach (which I suggest, just in case later you want to categorize books in three or more categories), would entail the following join table:

    Code:
    CREATE TABLE tblBooks_Cats
    (
      book_id INT NOT NULL,
      cat_id INT NOT NULL,
    )
    The primary key would be both columns (I don't know if I got the syntax correctly, but that's the idea).

    Then, when you want to search for all the books in a category with a specific id (say 9):

    Code:
    SELECT
      b.book_id,
      b.title,
      etc...
    FROM
      tblBooks b
    LEFT JOIN tblBooks_Cats ON tblBooks_Cats.book_id = b.book_id
    WHERE
      tblBooks_Cats.cat_id = 9
    I hope you get the idea.

    On the second query, you might also want to take the same route (a tblBooks_Authors join table), but under your current scenario, Are you sure you're using the query exactly as I wrote it?

    What that query does is find authors that contain, for example, "Edith Blighton" *anywhere* in the author field. So it doesn't matter if there are other authors in that field.

    If you use a join table as I suggested, you'd search instead the authors table, and join the result with the books that belong to that author (and any other author). It's very similar to the categories issue above.


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
  •