Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post

    Insert unknown key into array

    Hello again,

    If I have an array obtained by a mysql query with a WHERE clause how do I get it to return 0 where there is no match?

    For example:
    Table contains:

    Name: Age: Job:
    John 21 Cashier
    Adam 15 Bagger
    Susie 42 Manager
    Jill 19 Cashier
    Mark 36 Manager

    My Query is this:

    PHP Code:
    $query "SELECT job, COUNT(*) FROM table WHERE age <= 36 GROUP by job ";
    $result mysql_query($query) or die(mysql_error());
    $i=0;
    while (
    $array=mysql_fetch_assoc($result)) {
        
    $datay[$i]=$array;
    $i++;
    }; 
    Result:

    PHP Code:
    Array
    (
        [
    0] => Array
            (
                [
    job] => Bagger
                
    [COUNT(*)] => 1
            
    )

        [
    1] => Array
            (
                [
    job] => Cashier
                
    [COUNT(*)] => 2
            
    )

    I need the result to include the jobs that do not fit the WHERE and give them a count of zero.

    PHP Code:
    Array
    (
        [
    0] => Array
            (
                [
    job] => Bagger
                
    [COUNT(*)] => 1
            
    )

        [
    1] => Array
            (
                [
    job] => Cashier
                
    [COUNT(*)] => 2
            
    )
        [
    2] => Array
            (
                [
    job] => Manager
                
    [COUNT(*)] => 0


  • #2
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Using the where clause in this case isnt the way to go I would use the super cool if statement instead.

    PHP Code:
    $query "SELECT job ,
    IF ( age> 36, 0, COUNT( * )) AS COUNT
    FROM table
    GROUP by job"
    ;
    $result mysql_query($query) or die(mysql_error());
    $i=0;
    while (
    $array=mysql_fetch_assoc($result)) {
        
    $datay[$i]=$array;
    $i++;
    }; 
    I say its supercool because i just learned it 2 minutes ago
    You can not say you know how to do something, until you can teach it to someone else.

  • #3
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    This solution gets it half way there. Trouble is that if any of the jobs meet the criteria it counts all of them just not the ones that meet the criteria.

    So if one manager is older then 40 it counts all of the managers.. not just the one.

  • #4
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Sorry I'm really confused can you give me a better example or show me exactly how you want the array to look?
    You can not say you know how to do something, until you can teach it to someone else.

  • #5
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    Name: Age: Job:
    John 21 Cashier
    Adam 15 Bagger
    Susie 42 Manager
    Jill 19 Cashier
    Mark 37 Manager
    Amanda 70 Cashier

    PHP Code:
    $query "SELECT job, COUNT(*) FROM table WHERE age <= 36 GROUP by job "
    $result mysql_query($query) or die(mysql_error()); 
    $i=0
    while (
    $array=mysql_fetch_assoc($result)) { 
        
    $datay[$i]=$array
    $i++; 
    }; 
    Gives:

    PHP Code:
    Array
    (
        [
    0] => Array
            (
                [
    job] => Bagger
                
    [COUNT(*)] => 1
            
    )

        [
    1] => Array
            (
                [
    job] => Cashier
                
    [COUNT(*)] => 2
            
    )

        [
    2] => Array
            (
                [
    job] => Manager
                
    [COUNT(*)] => 1
            
    )


    If I do:

    PHP Code:
    $query "SELECT job, COUNT(*) FROM table WHERE age <= 19 GROUP by job "
    $result mysql_query($query) or die(mysql_error()); 
    $i=0
    while (
    $array=mysql_fetch_assoc($result)) { 
        
    $datay[$i]=$array
    $i++; 
    }; 
    I get:

    PHP Code:
    Array
    (
        [
    0] => Array
            (
                [
    job] => Bagger
                
    [COUNT(*)] => 1
            
    )

        [
    1] => Array
            (
                [
    job] => Cashier
                
    [COUNT(*)] => 1
            
    )


    I need to get:

    PHP Code:
    Array
    (
        [
    0] => Array
            (
                [
    job] => Bagger
                
    [COUNT(*)] => 1
            
    )

        [
    1] => Array
            (
                [
    job] => Cashier
                
    [COUNT(*)] => 1
            
    )
        
        [
    2] => Array
            (
               [
    job] => Manager
               
    [COUNT(*)] => 0
            
    )



  • #6
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    I am going to have to recreate this database on my machine bare with me
    You can not say you know how to do something, until you can teach it to someone else.

  • #7
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    [B]I tired to keep it to one query but its taking ages and I can't think of a way at the moment. So This is what I would do for a quick solution. Firstly for the sake of normalization you should have two tables one for staff and one for jobs. It is a one to many relationship so the secondary key for the many should be the primary key for the one

    Code:
    CREATE TABLE `staff` (
     `staff_id` int(10) NOT NULL auto_increment,
      `name` varchar(255) NOT NULL,
      `age` int(3) NOT NULL,
      `job_id` int(10) NOT NULL
       PRIMARY KEY  (`staff_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- 
    -- Dumping data for table `staff`
    -- 
    
    INSERT INTO `staff` (`staff_id`, `name`, `age`, `job_id`) VALUES ('1', 'John', 21, 3),
    ('2','Adam', 15, 2),
    ('3','Susie', 42, 1),
    ('4','Jill', 19, 3),
    ('5','Mark', 37, 1),
    ('6','Amanda ', 70, 3);
    
    CREATE TABLE `jobs` (
      `job_id` int(10) NOT NULL auto_increment,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY  (`job_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
    
    -- 
    -- Dumping data for table `jobs`
    -- 
    
    INSERT INTO `jobs` (`job_id`, `name`) VALUES (1, 'manager'),
    (2, 'bagger'),
    (3, 'cashier');
    You can run that entire code as a query or just look at this
    PHP Code:
    staff_id  name   age |job_id  
    1         
    John   21  |  3
    2         
    Adam   15  |  2
    3         
    Susie  42  |  1
    4         
    Jill   19  |  3
    5         
    Mark   37  |  1
    6         
    Amanda 70  |  3

    job_id   
    name
    1        
    manager
    2        
    bagger
    3        
    cashier 
    Then this would be my code for now:

    PHP Code:
    //After connection
    $query "SELECT * FROM jobs";
    $result mysql_query($query) or die(mysql_error());  

    $job_titles=array();
    while (
    $row=mysql_fetch_assoc($result)) { 
        
    $job_titles[$row['job_id']]=$row['name'];
    };

    $query "SELECT job_id, COUNT(*) AS count FROM staff WHERE age <= 19 GROUP by job_id"
    $result mysql_query($query) or die(mysql_error()); 
    $i=0
    $jobs=array();
    while (
    $row=mysql_fetch_assoc($result)) {      
         
    $row['job']=$job_titles[$row['job_id']];
         unset(
    $row['job_id']);
         
    array_push($jobs$row['job']);
         
    $datay[$i]=$row;
         
    $i++; 
    }

    foreach(
    $job_titles as $value)
    {
        if(!
    in_array($value$jobs))
        
    array_push($datay, array('count' => 0'job' => $value));
    }

    print_r($datay); 
    ouputs

    PHP Code:
    Array
    (
        [
    0] => Array
            (
                [
    count] => 1
                
    [job] => bagger
            
    )

        [
    1] => Array
            (
                [
    count] => 1
                
    [job] => cashier
            
    )

        [
    2] => Array
            (
                [
    count] => 0
                
    [job] => manager
            
    )


    I know it's less efficient then a single query and I guess would be nice in a single but at least it works. I'll think some more tomorrow. I'm sorry I haven't done much advanced stuff with grouping functions need to read up on it i guess

    But could be made a bit more efficient if you organised the results a bit differently rather than using that multidimensional array.

    Something like

    PHP Code:
    $query "SELECT * FROM jobs";
    $result mysql_query($query) or die(mysql_error());  

    $jobs=array();
    while (
    $row=mysql_fetch_assoc($result)) { 
        
    $jobs[$row['job_id']]=$row['name'];
    };

    $query "SELECT job_id, COUNT(*) AS count FROM staff WHERE age <= 19 GROUP by job_id"
    $result mysql_query($query) or die(mysql_error()); 
    $i=0
    $job_counts=array();
    while (
    $row=mysql_fetch_assoc($result)) {      
        
    $job_counts[$jobs[$row['job_id']]]= $row['count'];
    $i++; 
    }

    foreach(
    $jobs as $key => $value)
    {
        if(!
    in_array($valuearray_flip($job_counts)))
           
    $job_counts[$value]=0;
    }

    print_r($job_counts); 
    Gives

    PHP Code:
    Array
    (
        [
    bagger] => 0
        
    [cashier] => 1
        
    [manager] => 0

    Both give same just the data is orgainsed more efficiently in second example
    Last edited by timgolding; 05-02-2009 at 07:16 AM.
    You can not say you know how to do something, until you can teach it to someone else.

  • Users who have thanked timgolding for this post:

    Hayyel (05-03-2009)

  • #8
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    Thanks for all your work. I had never seen the 'push' before. I'll have to look it up.

  • #9
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    do you mean array_push. The names originates from machine code where you could push and pop to and from a stack. If you want a que then its shift and unshift. Looking at the output from my second code it looks wrong. If you plan to use that one I can modify it and correct the bug. If not I won't bother?
    Last edited by timgolding; 05-03-2009 at 09:19 PM.
    You can not say you know how to do something, until you can teach it to someone else.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •