SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 36 of 36
  1. #26
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the full code. The PHP works with my original SQL so I reckon its ok.

    Code:
    $newsql="select 
    	books.isbn as ISBN, 
    	books.title as Title, 
    	books.authors as Authors, 
    	books.edition as Edition, 
    	(select count(factual.isbn)
        	from factual
            Where isbn = books.isbn ) 
            	as FactualEntryCount, 
    	(select count(grammar.isbn)
             from grammar
             where isbn = books.isbn )
             	as GrammarYesNo, 
    	for_sale.isbn as ForSaleYesNo
      	from books
    	left outerjoin for_sale 
        on books.isbn = for_sale.isbn
     	WHERE 1=1";
    	if ($search_isbn){$newsql=$newsql . " AND books.isbn = '" . $search_isbn ."'";}
    	if ($search_title){$newsql=$newsql . " AND books.title = '" . $search_title ."'";} 
    	if ($search_authors){$newsql=$newsql . " AND books.authors = '" . $search_authors ."'";}
    	if ($search_edition){$newsql=$newsql . " AND books.edition = '" . $search_edition ."'";}
    	if ($search_category){$newsql=$newsql . " AND (books.category1 = '" . $search_category ."'" 
    	. " OR books.category2 = '" . $search_category ."'"
    	. " OR books.category3 = '" . $search_category ."')";}
    	$newsql=$newsql . "order by books.title, books.edition";

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ANDs and ORs may need parenthesizing

    instead of running this query, echo it out to see what it looks like after all the variables have been substituted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select books.isbn as ISBN, books.title as Title, books.authors as Authors, books.edition as Edition,
    (select count(factual.isbn) from factual Where isbn = books.isbn ) as FactualEntryCount,
    (select count(grammar.isbn) from grammar where isbn = books.isbn ) as GrammarYesNo,
    for_sale.isbn as ForSaleYesNo from books
    left outerjoin for_sale on books.isbn = for_sale.isbn
    WHERE 1=1
    AND (books.category1 = 'biology'
    OR books.category2 = 'biology'
    OR books.category3 = 'biology')
    order by books.title, books.edition

    The error I get is
    "Error performing query: You have an error in your SQL syntax near 'select count(factual.isbn) from factual Where factual.isbn = book' at line 6"
    Last edited by Subjective Effec; May 26, 2004 at 17:06.

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    say, you don't happen to be using mysql, by any chance?

    mysql doesn't support subqueries prior to version 4.1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using mySQL, version 3 :P .

    What do I do?

  6. #31
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what do you do? you make sure you mention that early on in any thread asking for sql



    you'll have to run separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #32
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. Sorry, I did not know that mySQL had that issue. You cannot set foreign keys either. Odd that it is so popular - free I guess.

    So separate queries for each section such as book details, then the number of factual, number of grammar, number of for_sale and ave rating?

    I can see I'm going to have some issues matching results up in a table but I can probably do it. I'd rather limit the number of queries though.

    I'll give it a go.

    Thanks again for all your help.

  8. #33
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm reverting back to using the intial joins with DISTINCT in the COUNTS but it doesn;t work - just gives a value of 1 regardless. Taking the distinct out gives the highest nubmer across the tables.

    Can it be fixed? If so, how?

  9. #34
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you'll have to run three separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #35
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this puuely a mySQL problem? If so it is a very poor show. If the only thing stopping me is the lack of support for sub-selects I will not be happy.

    My strategy has changed now. Instead of counting everything I want to join the tables so that I can just know IF there are any corresponding entries in the factual, grammar and for_sale tables. Seems to work so far but it really is sub-standard.

    It's a real shame because I wanted to have the average rating too. . .

    Ack!

  11. #36
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. I'm updating this so that people who come after me (in years to come ) will see how I solved it.

    My final query was:
    Code:
    SELECT books.isbn AS ISBN, books.title AS Title, books.authors AS Authors, books.edition AS Edition,
    	factual.isbn AS FactualEntryCount,
    	grammar.isbn AS GrammarYesNo,
    	for_sale.isbn AS ForSaleBookCount,
    	Avg(ratings.rating) AS Average_Rating	
    	FROM books
    	LEFT JOIN factual
    	ON books.isbn = factual.isbn
    	LEFT JOIN grammar
    	ON books.isbn = grammar.isbn
    	LEFT JOIN for_sale
    	ON books.isbn = for_sale.isbn
    	LEFT JOIN ratings
    	ON books.isbn = ratings.isbn
    	WHERE if ($search_isbn){$newsql_1=$newsql_1 . " AND books.isbn = '" . $search_isbn ."'";}
    	if ($search_title){$newsql_1=$newsql_1 . " AND books.title = '" . $search_title ."'";} 
    	if ($search_authors){$newsql_1=$newsql_1 . " AND books.authors = '" . $search_authors ."'";}
    	if ($search_edition){$newsql_1=$newsql_1 . " AND books.edition = '" . $search_edition ."'";}
    	if ($search_category){$newsql_1=$newsql_1 . " AND (books.category1 = '" . $search_category ."'" 
    	. " OR books.category2 = '" . $search_category ."'"
    	. " OR books.category3 = '" . $search_category ."')";}
    	$newsql_1=$newsql_1 . "GROUP BY books.isbn, books.title, books.authors, books.edition";
    The output is packaged into a table that gives all the "books" table details, whether there are corresponding factual, grammar or for_sale entries in the respective tables and the average rating for each book (or no rating).

    Using if-else users can click through to details of the factual, grammar or for_sale tables as the isbn is wrapped up in the "yes".


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
  •