#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2

    Category & Post ( show category only once in loop? )


    Hi ! I'm trying to work on a query with fetches a category for a user.. and shows the questions that fall under that category.

    Problem is my Sql get's the name every time..
    I just want

    CATEGORY NAME
    ----------------
    post 1
    post 2
    ...

    (thanks! )

  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    Hi,

    sorry, but I don't understand a word. In your description, you talk about questions and categories. In your query, you have projects and tasks. What's the relation?

    And if you don't want projects.name(?) to show up, um, why do you select it then?
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    this requirement (to show a column value only once for multiple occurrences of a related column value) should be handled by your application program, not with sql
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Originally Posted by r937
    this requirement (to show a column value only once for multiple occurrences of a related column value) should be handled by your application program, not with sql
    Thanks for the respons. I'm using PDO to fetch everything but how do you fetch the name only once.. and outside the loop.?

    Now it outputs all descriptions of the tasks.. but the tasks are related to a project but i don't know how to output the project name once. thanks!

    Code:
    $sth = $db->prepare('SELECT projects.name, tasks.description FROM tasks INNER JOIN projects ON projects.id = tasks.project_id INNER JOIN users ON users.project_id = projects.id WHERE users.id = :id'); $sth->execute(array('id' => $_SESSION['userid']));  while($project = $sth->fetch()){ 	echo $project['description']; }
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    Like r937 already said, this is a matter of the application (the PHP code in your case).

    What you want is two separate queries: First, fetch the project names and IDs. Loop over them, and for each project, display the name and fetch the corresponding tasks using the ID.
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Originally Posted by Jacques1
    Like r937 already said, this is a matter of the application (the PHP code in your case).

    What you want is two separate queries: First, fetch the project names and IDs. Loop over them, and for each project, display the name and fetch the corresponding tasks using the ID.
    Allright so 2 queries? I'll try that! thanks..
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by Jacques1
    What you want is two separate queries: First, fetch the project names and IDs. Loop over them, and for each project, display the name and fetch the corresponding tasks using the ID.
    no, no, no, no, NO

    sorry, but a query inside a loop is an insidiously inefficient solution

    one query, sorted results, loop over these results and check for current/previous control values

    basic, elementary programming

    i'd even write the code, but i don't do php
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    Originally Posted by r937
    no, no, no, no, NO
    Are you alright?

    Have you even looked at his example? Do you know the overall performance of PHP? That user he selected has 2 (in words: two) projects. Maybe others have 10. While that's the case, it might be a bit, um, early to do "optimizations".

    But in the last thread, you considered stuffing the first name and some assignment ID into the same column to save one query, so I understand where you're coming from.
    Last edited by Jacques1; January 25th, 2013 at 12:19 AM.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by Jacques1
    But in the last thread, you considered stuffing the first name and some assignment ID into the same column to save one query, so I understand where you're coming from.
    in that thread you referenced, i "considered" it only long enough to reject it

    please stop being so annoyingly adversarial

    du hast jetzt zum zweiten mal eine ohrfeige verdient

    edit: forgot to add smiley, sorry
    Last edited by r937; January 25th, 2013 at 07:33 AM.
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo