Count array key values with array_count_values, count and sizeof PHP functions

sizeof

According to the php.net, sizeof() is an alias of count()

It count all the elements in an array.

One more important thing is:

If your array is “huge”

It is reccomended to set a variable first for this case:

THIS->

$max = sizeof($huge_array);
for($i = 0; $i < $max;$i++)
{
code...
}

IS QUICKER THEN->

for($i = 0; $i < sizeof($huge_array);$i++)
{
code...
}

count

It Count all elements in an array.

Syntax:

int count ( mixed $array_or_countable [, int $mode = COUNT_NORMAL ] )

Parameters:

array_or_countable
An array or Countable object.

mode
If the optional mode parameter is set to COUNT_RECURSIVE (or 1), count() will recursively count the array. This is particularly useful for counting all the elements of a multidimensional array.

Return Values:

It will Returns the number of elements in an array_or_countable. If the parameter is not an array or not an object with implemented Countable interface, 1 will be returned. There is one exception, if array_or_countable is NULL, 0 will be returned.

<?php
$a[0] = 1;
$a[1] = 3;
$a[2] = 5;
$result = count($a);
// $result == 3
echo $result;

$b[0] = 7;
$b[5] = 9;
$b[10] = 11;
$result = count($b);
// $result == 3
echo $result;
$result = count(null);
// $result == 0
echo $result;
$result = count(false);
// $result == 1
echo $result;
$food = array(
              'fruits' => array(
                                'orange',
                                'banana',
                                'apple'
                                ),
              'veggie' => array('carrot',
                                'collard',
                                'pea'
                                )
);
// recursive count
echo count($food, COUNT_RECURSIVE); // output 8
or
echo count($food, 1); // output 8

// normal count
echo count($food); // output 2
// count only multidimensional array value
echo (count($food,COUNT_RECURSIVE)-count($food,0)); //output 6

$MyArray = array (
                  array(1,
                        2,
                        3
                        ),
                   1,
                   'a',
                   array(
                         'a',
                         'b',
                         'c',
                         'd'
                         )
                 );

$result = count($MyArray,0); // output 4
$result = count($MyArray,1); // output 11

// Both level values, but only values
echo(array_sum(array_map('count',$MyArray ))); //output 9 (9 values)
Explaination:
array_map() returns an array containing all the elements of array after applying the callback function to each one.

in our case, callback function is "count" which will count all the values.

print_r(array_map('count',$MyArray ));

Will give below output:

Array
(
[0] => 3
[1] => 1
[2] => 1
[3] => 4
)

And array_sum which calculates the sum of all the values in an array.

So (array_sum(array_map('count',$MyArray ))) wiil sum 3 + 1 + 1 + 4

// Only second level values
echo (count($MyArray ,COUNT_RECURSIVE)-count($MyArray )); //output 7 ((all elements) - (first elements))
?>

There is a simple script with example for counting rows and columns of a two-dimensional array.
<?php
$cars = array
(
"first" => array(
                 "carname" => "Volvo",
                 "carprice" => 22,
                 "caraverage" => 60,
                 "caroil" => 'castol',
                 "caryear" => 2012
),
"second" => array(
                  "carname" => "BMW",
                  "carprice" => 15,
                  "caraverage" => 70,
                  "caroil" => 'hp',
                  "caryear" => 2013
),
"third" => array(
                 "carname" => "Saab",
                 "carprice" => 5,
                 "caraverage" => 80,
                 "caroil" => 'mobil',
                 "caryear" => 2014
),
"fourth" => array(
                  "carname" => "Land Rover",
                  "carprice" => 17,
                  "caraverage" => 90,
                  "caroil" => 'AMSoil',
                  "caryear" => 2015
)
);

$rows = count($cars,0); // output 4
$cols = (count($cars,1)/count($cars,0))-1; // output 24/4 = 6 -1 = 5
print "There are {$rows} rows and {$cols} columns in the table!";
?>

array_count_values

array_count_values — Counts all the values of an array

Syntax:

array array_count_values ( array $array )

Returns an associative array of values from array as keys and their count as value.

<?php
$array = array(1, "hello", 1, "world", "hello");
echo "<pre>";
print_r(array_count_values($array));
echo "</pre>";
?>

// Output

Array
(
[1] => 2
[hello] => 2
[world] => 1
)

What is the difference between crdate and tstamp in TYPO3 database tables ?

Sometimes in many TYPO3 Extension you will find “crdate” and “tstamp” columns.

Today i will tell you, what is the difference between crdate and tstamp in TYPO3 database tables:

crdate = Holds a record’s creation date and time as Unix time stamp value.
tstamp = Holds a record’s last modification date and time as Unix time stamp value.

if you want to get your date in date format for particular record with MySQL query then use below code:

SELECT FROM_UNIXTIME(crdate) AS creation_date FROM tx_table_name WHERE uid = 3

or Even more better
SELECT FROM_UNIXTIME(crdate,’%Y %D %M %h:%i:%s %x’) AS creation_date FROM tx_table_name WHERE uid = 3

How to use array_column to get particular key, value from multidimensional array

if you want to get particular value of particular key from an array then use array_column as per below:

<?php
$records = array(
                 array(
                       'id' => 2135,
                       'first_name' => 'John',
                       'last_name' => 'Doe',
           ),
           array(
                       'id' => 3245,
                       'first_name' => 'Sally',
                       'last_name' => 'Smith',
           ),
           array(
                 'id' => 5342,
                 'first_name' => 'Jane',
                 'last_name' => 'Jones',
           ),
           array(
                 'id' => 5623,
                 'first_name' => 'Peter',
                 'last_name' => 'Doe',
           )
);

$first_names = array_column($records, 'first_name');
echo "<pre>";
print_r($first_names);
echo "</pre>";

$last_names = array_column($records, 'last_name', 'id');
echo "<pre>";
print_r($last_names);
echo "</pre>";
?>

Note: if you do not provide “index_key” then it will take numeric index, otherwise it will take “index_key” as you specified in array_column function.

Array
(
    [0] => John
    [1] => Sally
    [2] => Jane
    [3] => Peter
)
Array
(
    [2135] => Doe
    [3245] => Smith
    [5342] => Jones
    [5623] => Doe
)

How to remove false, null and Zero value from an Array in PHP

There is no need for this kind of operation. Simply use array_filter(), which conveniently handles all this job for you:

Below code will simply remove all the false, null and zero value from an array.

If you want to remove false and null but keeping zero value then you can use the standard php’s ‘strlen’ function as the callback function:

And if you want to remove false and null values by keeping empty string value (”) as it is, you can make your own callback function.

<?php

$entry = array(
               0 => 'foo',
               1 => false,
               2 => -1,
               3 => null,
               4 => '',
               5 => 0
         );

function myFilter($value){
         return (is_numeric($value) || is_string($value) || (empty($value) === false));
}

echo "<pre>";
print_r(array_filter( $entry ));
echo "</pre>";

echo "<pre>";
print_r(array_filter( $entry, 'strlen' ));
echo "</pre>";

echo "<pre>";
print_r(array_filter( $entry, 'myFilter' ));
echo "</pre>";

?>

Output:

Array
(
    [0] => foo
    [2] => -1
)
Array
(
    [0] => foo
    [2] => -1
    [5] => 0
)
Array
(
    [0] => foo
    [2] => -1
    [4] => 
    [5] => 0
)

How to Filter Odd and Even numbers from an Array in PHP with array_filter

Suppose you have array with value of 1 to 12.
And you want to separate odd and even numbers in different array, then use below code.

<?php
function odd($var)
{
   // returns whether the input integer is odd
   return($var & 1);
}

function even($var)
{
   // returns whether the input integer is even
   return(!($var & 1));
}

$array1 = array("a"=>1, "b"=>2, "c"=>3, "d"=>4, "e"=>5);
$array2 = array(6, 7, 8, 9, 10, 11, 12);

echo "Odd :\n";
echo "<pre>";
print_r(array_filter($array1, "odd"));
echo "</pre>";
echo "Even:\n";
echo "<pre>";
print_r(array_filter($array2, "even"));
echo "</pre>";
?>

Output:

Odd :

Array
(
[a] => 1
[c] => 3
[e] => 5
)

Even:

Array
(
[0] => 0
[1] => 6
[3] => 8
[5] => 10
[7] => 12
)

=================================================================================

If you want to use array_filter with a class method as the callback, you can use a psuedo type callback like below, which will print only even numbers from 1 to 10 array.

<?php
class Test {
   public function doFilter($array)
   {
       return array_filter($array, array($this, 'callbackMethodName'));
   }

   protected function callbackMethodName($element)
   {
       return $element % 2 === 0;
   }
}

$example = new Test;
echo "<pre>";
print_r($example->doFilter(range(1, 10)));
echo "</pre>";
?>

Output:

Array
(
    [1] => 2
    [3] => 4
    [5] => 6
    [7] => 8
    [9] => 10
)

And one another example of array_filter within a class to access a protected method from that same class:

<?php

class Bar {
      public function foo()
      {
          $array1 = array("a"=>1, "b"=>2, "c"=>3, "d"=>4, "e"=>5);

          print_r(array_filter($array1, array($this, 'baz')));
      }

      protected function baz($var)
      {
          return($var & 1);
      }
}

$bar = new Bar();

echo "<pre>";
print_r($bar->foo());
echo "</pre>";
?>

Output:

Array
(
 [a] => 1
 [c] => 3
 [e] => 5
)

MySQL query for Getting Second Highest Salary from Employee table in MySQL

“Most Common Question in Interview, how to get 2nd or Nth highest salary from Employee table.”

Now a days i am conducting PHP / Mysql interview for my organisation. And came across many of candidates resumes with 3-4 years of experience in web development. But only couple of developers given me the correct answer. That is what i think, I should share this.

You can write this query in number of ways, but i will explain one of the beset MySQL query which is easy to write and understand. Mysql having advantage of writing query with “Group BY” , “limit” and offset by which you can easily remove dupicate records and handle start and end point.

Lets Start with SQL Schema. Here is the Employee table which have employee_id, employee_name and employee_salary column.
employee

For example, given the above employee table, the second highest salary is 30000. If there is no second highest salary, then the query should return NULL. You can write your SQL query in any of your favorite database specific feature e.g. TOP, LIMIT, OFFSET, GROUP BY, GROUP_CONCAT or ROW_NUMBER, but you must also provide a generic solution which should work on all database. In fact, there are several ways to find second highest salary and you must know couple of them at the time of interview. Once you solve the problem, Interviewer will most likely increase the difficulty level by either moving to Nth salary direction or taking away this buit-in utilities.

Let’s First Start With Without LIMIT Clause.

Second Highest Salary in MySQL without LIMIT

Here is a generic SQL query to find second highest employee_salary, which will also work fine in MySQL. This solution uses subquery to first exclude the maximum employee_salary from the data set and then again finds maximum employee_salary, which is effectively find the second maximum employee_salary from the employee table.

SELECT MAX(employee_salary) FROM employee WHERE employee_salary NOT IN ( SELECT Max(employee_salary) FROM employee);

This will return 30000 in our case.

Here is another solution which uses sub query but instead of IN clause it uses < operator

SELECT MAX(employee_salary) FROM employee WHERE employee_salary NOT IN ( SELECT Max(employee_salary) FROM employee);

You can use this SQL query if Interviewer ask you to get second highest salary in MySQL without using LIMIT.

Second Highest Salary using Correlated SubQuery

Previous SQL query was also using subquery but it was non-correlated, this solution will use correlated subquery. This is also generic solution to find Nth highest salary in Employee table. For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return 2.

SELECT employee_id, employee_salary, employee_name  FROM employee AS e  WHERE 2=(SELECT COUNT(DISTINCT employee_salary) FROM employee AS p  WHERE e.employee_salary<=p.employee_salary);

Second Maximum Salary in MySQL using LIMIT

MySQL has a special keyword called LIMIT which can be used to limit the result set e.g. it will allow you to see first few rows, last few rows or range of rows. You can use this keyword to find the second, third or Nth highest salary. Just use order by clause to sort the result.

One of the most common answer which i found in most of the Junior or Senior Developer while taking Interview is below:

SELECT employee_salary FROM employee ORDER BY employee_salary DESC LIMIT 1,1;

Above query will not work, if we have same salary more than one times.

That’s why we have to use “DISTINCT” keyword as per below:

SELECT DISTINCT(employee_salary) FROM `employee` ORDER BY employee_salary DESC LIMIT 1,1;

Or you can also use GROUP BY keyword for this as below:

SELECT employee_salary FROM `employee` GROUP BY employee_salary ORDER BY employee_salary DESC LIMIT 1,1

Note that: A DISTINCT and GROUP BY generate the same query result, so performance should be the same across both query constructs. GROUP BY should be used if you are using aggregate fnctions (MAX, SUM, GROUP_CONCAT, …, or a HAVING clause) to each group. If all you need is to remove duplicates then use DISTINCT.

And Now what if you want to count only unique records, then use below query:

SELECT count(employee_salary) AS unique_count  FROM (  SELECT employee_salary FROM employee  GROUP BY employee_salary ORDER BY employee_salary DESC ) AS t

And Now what if you want to count only duplicate no. of records, then use below query:

SELECT count(employee_salary) AS duplicate_count
 FROM (
 SELECT employee_salary FROM employee
 GROUP BY employee_salary HAVING COUNT(employee_salary) > 1
 ) AS t

And Now what if you want to count total no. of times each salary repeated (duplicated), then use below query:

SELECT count(employee_salary) AS total_count, employee_salary FROM employee GROUP BY employee_salary

salary_total_count

And now finally the real magic comes here. if you want to count total no. of times each salary repeated (duplicated) with employee_name for that salary, then use below query:

SELECT count(employee_salary) AS total_count, GROUP_CONCAT(employee_name) AS total_employee, employee_salary FROM employee GROUP BY employee_salary ORDER BY employee_salary DESC

final_salary_data

And may be your final interview question would be “count total no. of times each salary repeated (duplicated) with employee_name for that salary for particular salary range, then use below query:

SELECT count(employee_salary) AS total_count, GROUP_CONCAT(employee_name) AS total_employee, employee_salary FROM employee GROUP BY employee_salary HAVING (employee_salary > 10000 AND employee_salary < 30000) ORDER BY employee_salary DESC

salary_range

How to schedule MySQL Query using MySQL event in phpMyAdmin?

To set Event scheduler in Databse, your MySQL server needs to have event scheduler enabled AND have EVENT privileges for the database user.

Go to PhpMyAdmin then run Below SQL query which will tell you about your scheduler status.

SELECT @@event_scheduler;

schedular

To enable event scheduler if it’s disabled try calling SET GLOBAL event_scheduler := 1; You need the SUPER privilege for this to succeed.

Above Query will make your scheduler status ON.

Once you have invoked your scheduler you can see it in the process list. By the following command:

SHOW PROCESSLIST;

schedularlist

Now we can proceed and create our EVENT. Here is how I created Event for the solution of my problem.

I want to delete all records from tx_candle_domain_model_candle table after every Three days whose crdate (created date which is in TIME format in MySQL ) more than Three days than Current day.

Here is how I created Event for the solution of my problem.

CREATE EVENT delete_candle_data

ON SCHEDULE EVERY 3 DAY

DO

DELETE FROM tx_candle_domain_model_candle where crdate < UNIX_TIMESTAMP(timestampadd(day, -3, now())) ORDER BY crdate DESC;

You can refer official documentation of MySQL to create events. There are various parameters that can be used to create an EVENT for your need.

Now you have created an event and if you want to see your events in future just fire a simple command:

SHOW EVENTS;

It will list all the events with details about each event.

event

There is also one Events tab is created in that Database at top. Where you can see your event list and status also.

Eventlist   eventstatus

To update your event MySQl offers you ALTER command. You can update the working of event by manipulating the SQL query, you can also change the schedule or event running time.

In my example I am changing my Event (delete_candle_data) run time. It will run the event once – one hour after the ALTER command is fired.

ALTER EVENT delete_candle_data

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

Further if you want to DROP the created event in future, you can simply use the command:

DROP EVENT delete_candle_data;

I hope you have understood MySQL events and how to work with them. You can also use MySQL events with PHP to schedule things like publishing blog post in  your website etc. I highly recommend you to once go through the official documentation. Share the simple tutorial in order to help others.

Of course you can also create cron job for this purpose. Simply create cron file by putting DELETE query command and put it on server and set time for that url. That will also work.

Have a great Day Ahead 🙂

How to find the Average of the values in your array

If you want to find the AVERAGE of the values in your array, use the sum and count functions together.  For example, let’s say your array is $foo and you want the average…

<?php
$foo = array(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);
$average_of_foo_round = floor(array_sum($foo) / count($foo));
$average_of_foo = (array_sum($foo) / count($foo));

echo "Average value Round fractions is ".$average_of_foo_round."<br/>";
echo "Average value is ".$average_of_foo."<br/>";
?>
// Output
Average value Round fractions is 10
Average value is 10.5

Array Intersect and Array union in PHP

Array Intersect

<?php

$array1 = array(2, 4, 6, 8, 10, 12);
$array2 = array(1, 2, 3, 4, 5, 6);

echo "<pre>";
print_r(array_intersect($array1, $array2));
echo "</pre>";
echo "<pre>";
print_r(array_intersect($array2, $array1));
echo "</pre>";

?>

// output

Array
(
    [0] => 2
    [1] => 4
    [2] => 6
)
Array
(
    [1] => 2
    [3] => 4
    [5] => 6
)

Array Union

<?php
$a = array(1,2,3,4);
$b = array(2,4,5,6);

//  $a = 1 2 3 4
$union =                            //  $b =   2   4 5 6
        array_merge(
          array_intersect($a, $b),    //         2   4
          array_diff($a, $b),         //       1   3
          array_diff($b, $a)          //               5 6
        );                              //  $u = 1 2 3 4 5 6
?>

// Output

Array
(
    [0] => 2
    [1] => 4
    [2] => 1
    [3] => 3
    [4] => 5
    [5] => 6
)

How To make 1 to 20 Number Array with for loop

[A]

If you want to create simple 1 to 20 number array with key value then use below code:

<?php
$array = array();
for ($x = 1; $x <= 20; $x++)
{
$array[] = $x;
}
echo "<pre>";
print_r($array);
echo "</pre>";
?>

Or Even More Better…. 🙂

$array = range(1, 20, 1);
echo "<pre>";
print_r($array );
echo "</pre>";

You can also use below Single line code:

$array = array_combine(range(0,19),range(1,20));

echo "<pre>";
print_r($array);
echo "</pre>";

And of course, If you want your key and value both have 1 to 20 value then use below one:

$array = array_combine(range(1,20),range(1,20));

// Output A

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 4
    [4] => 5
    [5] => 6
    [6] => 7
    [7] => 8
    [8] => 9
    [9] => 10
    [10] => 11
    [11] => 12
    [12] => 13
    [13] => 14
    [14] => 15
    [15] => 16
    [16] => 17
    [17] => 18
    [18] => 19
    [19] => 20
)

[B]

And Also, there is a much simpler way of creating a range of even numbers is by starting with an even number:

$evenarray = range(2, 10, 2);
 echo "<pre>";
 print_r($evenarray);
 echo "</pre>";
// Output B

Array
(
 [0] => 2
 [1] => 4
 [2] => 6
 [3] => 8
 [4] => 10
)

Also you can create your Own function to make your array which start, Ends and increase nth steps as below:

function myRange($start, $limit, $step)
{
     $myArr = array();
     foreach((array) range($start, $limit,$step) as $k => $v)
     {
        $myArr[$k+1] = $v;
     }
     return $myArr;
}
echo "<pre>";
print_r(myRange(0, 100, 10));
echo "</pre>";

// Output B

Array
(
 [1] => 0
 [2] => 10
 [3] => 20
 [4] => 30
 [5] => 40
 [6] => 50
 [7] => 60
 [8] => 70
 [9] => 80
 [10] => 90
 [11] => 100
)

OR use Below Code to start your key with “Zero” Not “one”

function my_range( $start, $end, $step = 1) {

     $range = array();

     foreach ((array)range( $start, $end ) as $index) {
          if (! (($index - $start) % $step) ) {
               $range[] = $index;
          }
     }
     return $range;
}
echo "<pre>";
print_r(my_range(0, 100, 10));
echo "</pre>";
Array
(
    [0] => 0
    [1] => 10
    [2] => 20
    [3] => 30
    [4] => 40
    [5] => 50
    [6] => 60
    [7] => 70
    [8] => 80
    [9] => 90
    [10] => 100
)