SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 30
  1. #1
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select the latest 2 job vacancies from 5 job categories - Help.

    I would like to construct a query which will return the 2 latest items from each of 5 categories within a table, so that 10 rows are returned. This query will be used to display the 2 latest job vacancies from 5 job categories.

    I was thinking of doing a foreach loop over the 5 categories and then limiting the result for each of these items to the 2 latest additions, but got confused half way through the scripting.

    The table I have has a column for the category the job belongs to along with all the necessary job information.

    If anyone could help me or provide the logic to point me in the right direction, then this would be marvellous.

    Thanks,

  2. #2
    SitePoint Addict amitjoshi's Avatar
    Join Date
    Jul 2005
    Location
    Oklahoma, USA
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    foreach ( $job_categories)
    {

    $sql "SELECT * from tbl_job WHERE job_category_iid='$job_categories' ORDER BY job_post_date DESC LIMIT 2;

    $res = mysql_query($sql);



    I hope this is what you wanted.

  3. #3
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, this is exactly what i'm looking for but I keep retrieving a NULL value for some reason when I dump the last query result set:

    PHP Code:
     require("include/connection.php");
         
    //Get categories
         
    $sql mysql_query("select * from deployment_role");
         while (
    $foo mysql_fetch_array($sql)){
             
    $type[] = $foo['role'];
         }
     
         
    //Query matching categories
         
    foreach($type as $value){
             echo 
    $sql2 "SELECT * FROM deployment_vacancy WHERE role_id = '$value' ORDER BY vac_date_added DESC LIMIT 2";
             echo 
    "<br>";
             
    $res mysql_query($sql2);
         }
     
     
         
    var_export ($res); 

  4. #4
    SitePoint Addict amitjoshi's Avatar
    Join Date
    Jul 2005
    Location
    Oklahoma, USA
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    var_export ($res);
    You are using var_export to dump the result set. That is why you are getting NULL. I think this will return NULl for all your queries not just for the last one.

    try print_r($res);
    Regards,

  5. #5
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I try print_r($res) I just get Resource id#8

  6. #6
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is 100% correct. mysql_query returns a resource id. You must then use one of the mysql_fetch_* functions (in a loop) to read the actual data, just like you did when getting all the categories.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mattih5
    I was thinking of doing a foreach loop over the 5 categories and then limiting the result for each of these items to the 2 latest additions, but got confused half way through the scripting.
    a query inside a loop is almost always a bad idea

    latest 2 jobs in each of 5 designated categories:
    Code:
    select t1.category
         , t1.job
         , t1.postdate
      from jobs as t1
    inner
      join jobs as t2
        on t1.category = t2.category
       and t1.postdate <= t2.postdate
     where t1.category in ( 1,2,3,9,37 )  /* which 5 */   
    group
        by t1.category
         , t1.job
         , t1.postdate
    having count(*) <= 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jees, this looks like a complex query.

    How come a query inside a loop is a bad idea? What is so bad about it?

    Yes, I have looped over with a while loop and see results, i'm not sure if they are correct results though.

    Cheers,

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mattih5
    How come a query inside a loop is a bad idea? What is so bad about it?
    http://www.sitepoint.com/forums/show...9&postcount=13
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats an awesome sql statement, and going to see how I can reuse that syntax in a diary I am working on too... Thanks.

    I think the full thread was far more revealling than the single "lorries to LA posting", nice though it was...

    http://www.sitepoint.com/forums/showthread.php?t=328866

  11. #11
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, yes I understand this now.

    I've attempted to modify your query and mould it into something like what I need.

    This is so far what I have:

    PHP Code:
     echo $sql "
             SELECT
                 t1.id,
                 t1.role_id,
                 t1.vac_date_added
             FROM
                 deployment_vacancy 
             AS 
                 t1
             INNER JOIN
                 deployment_role 
             AS
                 t2
             ON
                 t1.role_id = t2.role
             AND
                 t1.vac_date_added <= t2.postdate
             WHERE
                 t1.role_id in ( 1,2,3,9,37 )  
             GROUP BY
                 t1.role_id,
             HAVING
                 count(*) <= 2
         "

    Some of it I am a little unsure on, like the AND bit with the dates - why is this needed?

    The way I hae the tables set up is like this at the moment (I know it is wrong).

    Table: deployment_role
    Fields: id, role (TEXT with the name of the role: driver, HGV, Fork Lift Truck)

    Table: deployment_vacancy
    Fields: id, rold_id (matches the role field of the above table), vac_date_added (date of addition) etc etc.

    Really the ID's of the deployment role table should go in as the role_id in the deployment_vacancy. But for now, the set up will do.

    Cheers,

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the important part of my query was the self-join

    the "AND bit with the dates" is what allows the query to select the latest two rows, and it is necessary to do this between the two copies of the same table being self-joined

    if you would kindly explain what you're trying to do, i'll see if i can revise your query to get it to work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes no problem.

    I am trying to display the latest 2 job additions for each job type. So their will be a list of the two latest vacancies added to the database for each of:

    * Driving Vacancies
    * Warehouse Vacancies
    * Other Vacancies
    * Professional Vacancies
    * Work for Deployment vacancies

    So, in theory their should be 10 vacancies displayed to the user.

    Hope this helps,

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    could you please associate "latest 2 job additions for each job type" to "deployment_vacancy INNER JOIN deployment_role"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    never mind, i think i figured it out
    Code:
    select d.role
         , t1.id
         , t1.vac_date_added
      from deployment_role as d
    inner
      join deployment_vacancy as t1
        on t1.role_id = d.id
    inner
      join deployment_vacancy as t2
        on t1.role_id = t2.role_id
       and t1.vac_date_added <= t2.vac_date_added
     where d.id in ( 1,2,3,9,37 )  /* which 5 */   
    group
        by d.role
         , t1.id
         , t1.vac_date_added
    having count(*) <= 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    thanks for your revised sql statement. I'm still having difficulty in implementing it. The code I now have in place is:

    PHP Code:
    echo $sql "
     select 
      d.role, 
      t1.id,
      t1.vac_date_added,
     from 
      deployment_role as d
     inner join 
      deployment_vacancy as t1
        on t1.role_id = d.role
     and t1.vac_date_added <= t2.vac_date_added
     where d.id in ( 1,2,3,4,5 )  
     group
        by d.role
      t1.id,
      t1.vac_date_added,
     having count(*) <= 2
     "
    ;
     
    $result mysql_query($sql);
     while(
    $foo mysql_fetch_array($result)){
      echo 
    $foo['id'];
     } 
    And I keep receiving an error like this:

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in.......on line 42
    If this helps I have added 2 screenshots, one of the job categories and one of the table which holds all the jobs. Like I mentioned before, really I should be using the ID as the method of linking, but for the time being the role_id as the role from the role table is fine (for now). The files are attached and are screenshots of the actual data to give an idea of how the the end result will look.

    Cheers
    Attached Images Attached Images

  17. #17
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    the error simply means that something in the query didn't work as expected.
    Put
    PHP Code:
    $result mysql_query($sql) or die(mysql_error()); 
    as the query line to see if it throws up some suggesstions/ hints

    Spike

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you have a "dangling comma" both in the SELECT and the GROUP BY

    you have also removed the self-join again!!! don't do that!!!

    the self-join is where you have t1 joined to t2

    you have removed t2, please put it back
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, i've put it back to how it was, i thought they were mistakes - sorry, i don't have a clue with this query - way over my head.

    Okay, so this is the code I now have:

    PHP Code:
    echo $sql "
    select d.role
         , t1.id
         , t1.vac_date_added
    from deployment_role as d
    inner
    join deployment_vacancy as t1
        on t1.role_id = d.id
    inner
    join deployment_vacancy as t2
        on t1.role_id = t2.role_id
    and t1.vac_date_added <= t2.vac_date_added
    where d.id in ( 1,2,3,4,5 ) /* which 5 */ 
    group
        by d.role
         , t1.id
         , t1.vac_date_added
    having count(*) <= 2
    "
    ;
     
    $result mysql_query($sql) or die(mysql_error());
    while(
    $foo mysql_fetch_array($result)){
    echo 
    $foo['id'];

    No results or errors are being displayed. The table has data in it and the field names in the sql query appear to be correct, does anyone know what's wrong?

  20. #20
    SitePoint Addict lmasi02's Avatar
    Join Date
    Aug 2004
    Location
    Zambia
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this
    PHP Code:
    <?php 
    echo $sql "
            SELECT
                t1.id,
                t1.role_id,
                t1.vac_date_added
            FROM
                (deployment_vacancy 
            AS 
                t1
            INNER JOIN
                deployment_role 
            AS
                t2
            ON
                t1.role_id = t2.role
            AND
                t1.vac_date_added <= t2.postdate)
            LIMIT 0,2
        "
    ;
        
    $qry mysql_query($sql);
        if(
    $row mysql_fetch_array($qry)){
            do{
            
    //echi statements
            
    }
            while(
    $row mysql_fetch_array($qry));
        } else echo 
    "no data";

    ?>
    Power of Knowledge

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    lmasi02, i think you missed the part about the self-join, didn't you

    the self-join is how the query works, and if you don't join the deployment_vacancy to itself then you can't count how many rows have a lower date, which is what the HAVING clause is doing

    holty, make sure you actually do have role_ids 1,2,3,4,5

    i know my query works, i tested it, and so can you:
    Code:
    create table deployment_role
    ( id tinyint not null primary key auto_increment
    , role varchar(37)
    );
    insert into deployment_role (role) values
     ('Driving')
    ,('Warehouse')
    ,('Other')
    ,('Professional')
    ,('Work for Deployment')
    ,('foo')
    ,('bar')
    ;
    create table deployment_vacancy
    ( id tinyint not null primary key auto_increment
    , role_id tinyint
    , vac_date_added date
    );
    insert into deployment_vacancy (role_id,vac_date_added) 
    values
    /* Driving */
     ( 1, '2005-01-13')
    ,( 1, '2005-01-12')
    ,( 1, '2005-01-11')
    /* Warehouse */
    ,( 2, '2005-02-23')
    ,( 2, '2005-02-22')
    ,( 2, '2005-02-21')
    /* Other */
    ,( 3, '2005-03-17')
    ,( 3, '2005-03-16')
    /* Professional */
    ,( 4, '2005-04-04')
    /* Work for Deployment */
    ,( 5, '2005-05-18')
    ,( 5, '2005-05-17')
    ,( 5, '2005-05-19')
    ,( 5, '2005-05-16')
    /* foo */
    ,( 6, '2005-06-16')
    ,( 6, '2005-06-16')
    /* bar */
    ,( 7, '2005-07-07')
    ;
    select d.role                                   
         , t1.id                                    
         , t1.vac_date_added                        
      from deployment_role as d                     
    inner                                           
      join deployment_vacancy as t1                 
        on t1.role_id = d.id                        
    inner                                           
      join deployment_vacancy as t2                 
        on t1.role_id = t2.role_id                  
       and t1.vac_date_added <= t2.vac_date_added   
     where d.id in ( 1,2,3,9,37 )  /* which 5 */    
    group                                           
        by d.role                                   
         , t1.id                                    
         , t1.vac_date_added                        
    having count(*) <= 2     
    ;
    
    role                 id  vac_date_added 
    Driving               1  2005-01-13
    Driving               2  2005-01-12
    Other                 7  2005-03-17
    Other                 8  2005-03-16
    Professional          9  2005-04-04
    Warehouse             4  2005-02-23
    Warehouse             5  2005-02-22
    Work for Deployment  10  2005-05-18
    Work for Deployment  12  2005-05-19
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to all of you that helped, especially r937, this query really is awesome. I managed to get it working, in the end it was due to the fact that I never had the ID's within the deployment_vacancy table. I thought we were matching on what was previously in the field and not the id's.

    Thanks anyway, client will be chuffed with it.

  23. #23
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Do you remember helping me a while back with this query? Only, today around 13 jobs have been entered and the query is only bringing back 2, shouldn't it bring back 10 jobs?

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    oh, yes, i remember, latest 2 in each category

    wish i could see your current query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not quite sure why it is only bringing 2 results.

    PHP Code:
    /**
     * @return void
     * @desc Display the top 10 latest added vacancies
    */
    function latest_vacancies(){

     /*
     * This query is curtorsy of Sitepoint member
     * r937 and is an awesome query!
     */

     $sql = "
      select d.id
       , t1.id
       , t1.vac_date_added
       , t1.vac_reference
       , t1.vac_title
      from deployment_role as d
      inner
       join deployment_vacancy as t1
        on t1.role_id = d.id
      inner
       join deployment_vacancy as t2
        on t1.role_id = t2.role_id
        and t1.vac_date_added <= t2.vac_date_added
      where d.id in ( 1,2,3,4,5 )  /* which 5 */   
      group
          by d.role
       , t1.id
       , t1.vac_date_added
      having count(*) <= 2
     "; 
     $result = mysql_query($sql) or die(mysql_error());
     echo "<table border\"1\" cellpadding=\"2\" cellspacing=\"0\" id=\"top10\" width=\"313px\">";
     echo "<th colspan=\"3\" align=\"left\">";?>
     <object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,29,0" width="310" height="15" standby="Latest 5 Vacancies" title="Flash element: Latest 10 Vacancies">
      <param name="movie" value="animation/latest_10.swf" />
      <param name="quality" value="high">
      <embed src="animation/latest_10.swf" width="310" height="15" autostart="false" quality="high" pluginspage="http://www.macromedia.com/go/getflashplayer" type="application/x-shockwave-flash"></embed></object>
     <? echo "</th>";
     while (
    $foo mysql_fetch_array($result)) {
      
    $id $foo['id'];
      
    $vac_reference $foo['vac_reference'];
      
    $vac_title $foo['vac_title'];
      
    $vac_date_added $foo['vac_date_added'];

      
    /*if(empty($color)){
       $color = "#C5ECF3";
      }elseif($color == "#C5ECF3"){
       $color = "#FCFCFC";
      }elseif($color == "#FCFCFC"){
       $color = "#C5ECF3";
      }*/

      
    if(empty($color)){
       
    $color "#7FCC7F";
      }elseif(
    $color == "#7FCC7F"){
       
    $color "#FCFCFC";
      }elseif(
    $color == "#FCFCFC"){
       
    $color "#7FCC7F";
      }
      
      echo 
    "<tr bgcolor=\"$color\"><td align=\"left\" valign=\"top\"><a href=\"vacancy.php?cmd=top10&&id=$id\" title=\"Internal Link: View this vacancy\">".$vac_reference."</a></td>";
      echo 
    "<td align=\"left\" valign=\"top\">".$vac_title."</td>";
      echo 
    "<td align=\"center\" valign=\"top\">".$vac_date_added."</td>";
      echo 
    "</tr>";
     }  
     echo 
    "</table>";
    }
    Attached is an .sql file of how my table is.
    Attached Files Attached Files


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
  •