1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Simple Table creation with php/mysql

Discussion in 'PHP' started by billybrag, Feb 17, 2006.

  1. #1
    Hello all,

    I wondered if i could pick your brains for a second..

    i have a table called job_jobcategories which contains 30 different jobcategories.

    I have a second table called job_post which has all of a jobs information in it. One of the fields in this table is jobcategory, which is linked to the first table.

    what i am hoping to do is to make a table that pulls each of the job categories from the first table and then counts how many jobs there are for that category and displays this in brackets.

    so it will look something like....

    Advertising (2)
    Automotive (5)
    Bank(4)
    ..
    ..
    etc

    can anyone help with this, in php as I have no idea,

    I think it must be relativly simple,


    thanks alot for any help!!

    Mike
     
    billybrag, Feb 17, 2006 IP
  2. mad4

    mad4 Peon

    Messages:
    6,986
    Likes Received:
    493
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Doing a query within a query is not particularly simple.

    I would add a field to the first table for the category and then do 30 seperate queries to select the jobs in each category with a count function that increases by one for each loop to get the number of matching jobs.

    $query = "select * from job_post where category like \"builder\"";
    $result = mysql_query($query) or die();
    $count=0;  
    while ($row= mysql_fetch_array($result)) {
    $count=$count+1;
    }
    PHP:
     
    mad4, Feb 17, 2006 IP
    billybrag likes this.
  3. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #3
    can you elaborate slightly as im not entirly sure about what you mean.

    Thanks
     
    billybrag, Feb 17, 2006 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    Can you list the fields in each table?
     
    jestep, Feb 17, 2006 IP
    billybrag likes this.
  5. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #5
    I don't know the field names, but this should be about what you are looking for.

    Also, by put in a table, did you mean a table in the database, or an html table for display?

    
    <?PHP
    
    $sql = "SELECT id, job_cat FROM job_jobcategories";
    $sql_res = mysql_query($sql);
    
    while ($sql_array = mysql_fetch_array($sql_res)){
    $jobcatid = $sql_array['id'];
    $jobcat = $sql_array['jobcategory'];
    
    $get_num = "SELECT id FROM job_post WHERE jobcategory = $jobcatid";
    $get_nem_res = mysql_query($get_num);
    $get_rows = mysql_num_rows($get_nem_res);
    
    $display .= "$jobcat ($get_rows)<br>";
    
    }
    echo $display;
    ?>
    
    PHP:
     
    jestep, Feb 17, 2006 IP
  6. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #6
    i meant display in html,

    the field names are:

    The table job_jobcategories has 2 fields

    jobcategoryid
    jobcategory

    and the table job_post has many fields but the field of interest is

    JobCategory
     
    billybrag, Feb 17, 2006 IP
  7. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #7
    Assuming that the job_jobcategories jobcategoryid corresponds to the job_post jobcategory field.
    Something like this should work:
    
    <?PHP
    
    $display .= "<table border=\"0\" cellspacing=\"0\" cellpadding=\"5\">";
    
    $sql = "SELECT jobcategoryid, jobcategory FROM job_jobcategories";
    $sql_res = mysql_query($sql);
    
    while ($sql_array = mysql_fetch_array($sql_res)){
    $jobcatid = $sql_array['jobcategoryid'];
    $jobcat = $sql_array['jobcategory'];
    
    $get_num = "SELECT * FROM job_post WHERE jobcategory = $jobcatid";
    $get_nem_res = mysql_query($get_num);
    $get_rows = mysql_num_rows($get_nem_res);
    
    $display .= "
    <tr>
    <td>$jobcat</td>
    <td>($get_rows)</td>
    <br>
    </tr>
    ";
    
    }
    $display .= "</table>";
    
    echo $display;
    ?>
    
    PHP:
     
    jestep, Feb 17, 2006 IP
  8. Perrow

    Perrow Well-Known Member

    Messages:
    1,306
    Likes Received:
    78
    Best Answers:
    0
    Trophy Points:
    140
    #8
    Your SQL query should be something like this.
    select c.*, count(jobs.id) as nOfJobs 
    from categories as c, jobs 
    where c.id = jobs.categoryid 
    group by c.id
    Code (markup):
    Your resulting record set will have the two fields from categories and a field called "nOfJobs" wich holds the number of jobs in that category.
     
    Perrow, Feb 17, 2006 IP
  9. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #9
    ok, so that is pulling the categories and displaying them how i want, however it isnt performing the count, it is just displaying like

    Advertising()

    thanks for the help so far

    :)
     
    billybrag, Feb 17, 2006 IP
  10. billybrag

    billybrag Peon

    Messages:
    324
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #10
    Sorry Perrow, but i dont understand that, can you explain it for me

    Mike

    one thing i fear may be incorrect

    the matching fields in both tables are jobcategory and JobCategory

    is mysql case sensitive?
     
    billybrag, Feb 17, 2006 IP
  11. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #11
    Try this instead. I may have had your table fields mixed up.

    
    <?PHP
    
    $display .= "<table border=\"0\" cellspacing=\"0\" cellpadding=\"5\">";
    
    $sql = "SELECT jobcategoryid, jobcategory FROM job_jobcategories";
    $sql_res = mysql_query($sql);
    
    while ($sql_array = mysql_fetch_array($sql_res)){
    $jobcat = $sql_array['jobcategory'];
    
    $get_num = "SELECT * FROM job_post WHERE jobcategory = $jobcat";
    $get_nem_res = mysql_query($get_num);
    $get_rows = mysql_num_rows($get_nem_res);
    
    $display .= "
    <tr>
    <td>$jobcat</td>
    <td>($get_rows)</td>
    <br>
    </tr>
    ";
    
    }
    $display .= "</table>";
    
    echo $display;
    ?>
    
    PHP:
     
    jestep, Feb 17, 2006 IP
  12. Lordo

    Lordo Well-Known Member

    Messages:
    2,082
    Likes Received:
    58
    Best Answers:
    0
    Trophy Points:
    190
    #12
    If `jobcategory` of `job_post` is integer, try this:

    SELECT a.*, count(b.id) posts 
    	FROM job_jobcategories a 
    	LEFT JOIN job_post b 
    	ON b.jobcategory = a.jobcategoryid 
    	GROUP BY a.jobcategoryid, a.jobcategeory 
    	ORDER BY a.jobcategory
    Code (markup):
    (replace b.id with b.the_name_of_job_post_id_field)

    The count name is posts. So, you can use it like this: $rs['posts'];
     
    Lordo, Feb 17, 2006 IP