...

View Full Version : Insert unknown key into array



Hayyel
04-30-2009, 03:05 AM
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:


$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:


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.


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

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

timgolding
04-30-2009, 03:52 PM
Using the where clause in this case isnt the way to go I would use the super cool if statement instead.



$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 :D

Hayyel
05-02-2009, 01:09 AM
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.

timgolding
05-02-2009, 02:09 AM
Sorry I'm really confused can you give me a better example or show me exactly how you want the array to look?

Hayyel
05-02-2009, 02:27 AM
Name: Age: Job:
John 21 Cashier
Adam 15 Bagger
Susie 42 Manager
Jill 19 Cashier
Mark 37 Manager
Amanda 70 Cashier


$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:


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

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

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

)

If I do:


$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:


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

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

)

I need to get:


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

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

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

)

timgolding
05-02-2009, 02:42 AM
I am going to have to recreate this database on my machine bare with me

timgolding
05-02-2009, 06:09 AM
[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



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


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:



//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



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



$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($value, array_flip($job_counts)))
$job_counts[$value]=0;
}

print_r($job_counts);


Gives



Array
(
[bagger] => 0
[cashier] => 1
[manager] => 0
)



Both give same just the data is orgainsed more efficiently in second example

Hayyel
05-03-2009, 05:46 AM
Thanks for all your work. I had never seen the 'push' before. I'll have to look it up.

timgolding
05-03-2009, 08:17 PM
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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum