Using Arrays in Foreach loop for counting rows based on query


#1

//I am new to PHP Please help, the code is given below:
///getting dates for all mondays of curent month
$today = date(“Y-m-d”);
echo $mondates = date(“Y-m-d”, strtotime($day)). "
";
///above echo $mondates gives values below, which are correct as required
/* 2018-08-06
2018-08-13
2018-08-20
2018-08-27 */
///I want to run the below query for each of the four dates in $mondates variable and also want to count and add the number of true queries to a variable as below:
foreach ($mondates as $mondate) {
$selectvalidsplmonmumls = "Select brkmu_no from pin_mls_detail where date_pin = ‘$mondate’ && date_pin <= ‘$today’ && pin_pin = ‘$pinid’ && brkmu_no = ‘M’ || brkmu_no = ‘1’ || brkmu_no = ‘1F’ ";

$run_query = mysql_query($selectvalidsplmonmumls);
$rowsvalidsplmonmumls = mysql_fetch_array($run_query);
if ($rowsvalidsplmonmumls = ‘M’ || ‘1’ || ‘1F’)
{
$validsplmonmumls = 1;
$validsplmonmumls++;
return $validsplmonmumls;
}
echo $validsplmonmumls;
}
///Also want $validsplmonmumls to be accessed outside the loop code. I couldn’t figure out what I am doing wrong. Any help would be appreciated. Thanks


#2

Well, first if you are comparing a value such as if ($rowsvalidsplmonmumls = ‘M’ || ‘1’ || ‘1F’) , you are comparing the first variable equal to ‘M’ and the others are hard values, not any type of compare.
I think it should be more like if ($rowsvalidsplmonmumls == ‘M’ OR $rowsvalidsplmonmumls == ‘1’ OR $rowsvalidsplmonmumls == ‘1F’)
Note that you need to use double-equals for the equal compare in PHP. Also, I just use OR as it is easier to read.

But, you could do this all in one query using the IN() function option in MySQL. If the mondays already exist in an array, you can bypass the foreach and just run one query select where the date_pin is in($mondates).

Next, you do a check after the query which is just like the WHERE in the query and if it matches which is should always since that was the query, you then let a variable equal 1 and then in the next line add one to it. Therefore always setting it to 2. Makes no sense at all.

Lastly, if you need to count values, just use the count() function inside your query. You can use, loosely, something like select count( blah blah blah ) as validsplmonmumis which will create a total count of your query and give you the one result that you need.

Therefore, you would only have one query that would do the entire process for you. I will guess by now you are saying to yourself, what? Let’s explain it just a bit further. You really want to use the query system and not use PHP. Just a waste of time to get a simple total count of something already in the database. You would use the query and just use the count() function. Add in the count(rest of query) as validsplmonmumis and the results will just be that total. Where you have the where date_pin = $mondate, replace that with the where date_pin in( $mondates ). Hope that helps. Try it and post back your code and we can help you get it running.


#3

Thanks ErnieAlex for the pain you have taken to solve the code. As I have first time asked for PHP help online on any platform. I have made below code after your recommendation but it is still giving me the below errors (see last lines), I am pasting the simplified code, please help me to get the number of lines based on the query I am using.

date_default_timezone_set(“Asia/Kolkata”);
$today=date(“Y-m-d”);
$month = date(‘m’);
$year = date(‘Y’);
$days = cal_days_in_month(CAL_GREGORIAN, $month,$year);
for($i = 1; $i<= $days; $i++){
$day = date(‘Y-m-’.$i);
$result = date(“l”, strtotime($day));
if($result == “Monday”){
//echo date(“Y-m-d”, strtotime($day)). " “.$result.”
";
$mondates = date(“Y-m-d”, strtotime($day)). "
";
echo $mondates;
$mondatesarr = array($mondates); //////////THIS WAS NOT DECLARED IN PREVIOUS CODE SENT TO YOU
print_r($mondatesarr);

///////////TILL THIS LINE, ABOVE CODE IS OK. IT GIVES $mondatesarr values in array/////////

$pinid = 1;

$ttlvalidsplmonmumls = 0;

foreach ($mondatesarr as $mondate) {

$selectvalidsplmonmumls = “Select count(*) from pin_mls_detail where (brkmu_no = ‘M’ OR brkmu_no = ‘1’ OR brkmu_no = ‘1F’ ) AND pin_pin = ‘$pinid’ AND date_pin IN($mondatesarr)”; /////////////////LINE 194

print_r($selectvalidsplmonmumls);

$runquery = mysql_query($selectvalidsplmonmumls);

$rowsvalidsplmonmumls = mysql_fetch_array($runquery); ////////////////LINE 201

print_r($rowsvalidsplmonmumls);

$ttlvalidsplmonmumls = mysql_num_rows($runquery); ////////////////LINE 205
}
echo $ttlvalidsplmonmumls;

//////////I am getting below errors

Notice : Array to string conversion in E:\xampp\htdocs\macc\include\splmls.php on line 194
Select count(*) from pin_mls_detail where (brkmu_no = ‘M’ OR brkmu_no = ‘1’ OR brkmu_no = ‘1F’ ) AND pin_pin = ‘1’ AND date_pin IN(Array)
Warning : mysql_fetch_array() expects parameter 1 to be resource, boolean given in E:\xampp\htdocs\macc\include\splmls.php on line 201

Warning : mysql_num_rows() expects parameter 1 to be resource, boolean given in E:\xampp\htdocs\macc\include\splmls.php on line 205


#4

Starting from the end and working back toward the top, there’s too much that needs to be done differently in this code.

Some recommendations -

  1. The php mysql_ extension is obsolete and has been removed from the latest php versions. If you are learning php, you should be learning how to use the modern php PDO extension, not the obsolete mysql_ extension.

  2. The last two php errors you are getting are ‘follow on’ errors. They are due to the sql query failing with an error and your code doesn’t have any error handling to detect and handle a query error. The easiest way of adding error handling for all the database statements is to use exceptions, and in most cases let php catch the exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. When you switch to the php PDO extension, you enable exceptions by setting the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION.

  3. The reason your sql query is most likely failing is because the IN(…) comparison takes a comma separated list of values. It doesn’t take an array and using a php array directly in this context causes it to be treated as the string ‘Array’, which will cause the sql query to try to use a column named Array.

  4. To produce a comma separated list of dates for the IN(…) comparison, you would implode() the array of dates, which also requires single-quotes around each date. However, you aren’t actually producing an array of dates. You are creating a new array for each different date. Your code should first loop to produce the array of dates and nothing else. You are also concatenating a new-line onto the end of each date you are producing. This won’t match your actual data (and if you are doing this because your stored data has new-lines as part of it, you need to fix the stored data so that they are only the dates and they should be stored in a DATE type column.)

  5. You need to use under scores _ in your variable names so that they are readable.

  6. When dealing with the result from a SELECT query, only the final data needs to be stored in a uniquely named variable. The other variables leading up to the result should be simple names like $sql, $stmt, $row, …

  7. External/dynamic data that could contain unknown values (sql syntax, i.e. sql injection) should not be put directly into the sql query statement. You should use a prepared query, with place-holders in the sql statement for each value, then supply the value when the query gets executed.

  8. As ErnieAlex has stated, the sql query can do all the counting work for you. The only loop you should have is the one producing the array of dates. The rest of the code should only produce the sql query statement, execute it, then fetch the count value.

I’m busy doing something else right now. If I/others have time, they will post an example showing what the sql query and the php PDO code should be.


#5

This creates an array with one date in it. You should create the empty array before the for() is processed like this: $mondatesarr = array(); This will create the array with no data inside it. Then, instead of creating the one array entry ( $mondatesarr = array($mondates); ) replace it with adding one date to the array like this: array_push($mondatearr, $mondates); This command pushes a new value at the end of the array. Here is a page that explains it. Array_Push Info

Now, that should create an array for you that holds all your Mondays. I did note in the code you posted, you did not end your FOR() loop. A FOR loop is like for($i = 1; $i<= $days; $i++){ some code } I did not see your closing brace which should be around where the ///// comment was above.

Next, to fix the query issues…
First, MySQL is outdated and seldom used at all now. The newer version MySQLi is needed. A lot of schools teach the old outdated versions as they still have textbooks with that version. You should update your MySQL code to the MySQLi versions. The “i” stands for improved. It is a much more secure version and actually is faster too. But do it after you get this working. Here is a link listing each MySQLi commands and they show how to set each up. W3Schools MySQLi commands
Comparing dates inside a query sometimes is tricky. You are creating an array of dates formatted as Y-m-d as they are held in a MySQL database. If the field you are using is set up as a “DATE” type of field or even if it is a “DATETIME” type of field, you can use DATE(fieldname) in the query to format the field for just the date part. Then, you can use the IN() function for your array and it should work well. You would need to change the query something like this:

SELECT * FROM calendar WHERE DATE(startTime) = '2010-04-29'

As you see, you can pull out the “Y-m-d” format inside of a query using DATE() function. So, using that way, you would change your query to using the IN() function differently. I thought you would look at the MySQL function IN() and learn it. But, to explain here, the IN() needs a list of dates such as IN(‘2018-08-24’, ‘2018-08-28’) etc… To use a PHP array inside the MySQL query, you need to format the data inside the array into a series of strings. To do this with your code, change your query like this:

$selectvalidsplmonmumls = “Select count(*) from pin_mls_detail where ( brkmu_no = ‘M’ OR brkmu_no = ‘1’ OR brkmu_no = ‘1F’ ) AND pin_pin = ‘$pinid’ AND date_pin IN(" . implode( ‘,’, $mondatesarr) . ")”;

As you see, using the PHP function IMPLODE() we create an output that is the list of dates. Then we place this inside your query inside the IN() function. The query then has the list of dates inside the query where they should be. Hope that makes sense to you.

Also, this code will get all the Mondays of the current month:

$date = new DateTime('first Monday of this month');
$thisMonth = $date->format('m');
while ($date->format('m') === $thisMonth) {
    array_push($mondatesarr, $date->format('Y-m-d'));
    $date->modify('next Monday');
}

Date time was Gregorian, so you are in Russia. Nice! Always wanted to visit there!


#6

An example showing how to use the php PDO extension to do this -

<?php
date_default_timezone_set("Asia/Kolkata");

$days = cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'));

$dates = []; // define/initialize array to hold dates - the Mondays in the current month
for($i = 1; $i <= $days; $i++)
{
	$day = date('Y-m-'.$i);
	$dow = date("l", strtotime($day));
	if($dow == "Monday")
	{
		// add day to the array of dates
		$dates[] = "'$day'"; // literal dates need to be enclosed by single-quotes
	}
}

// treat the pin_id as external/unknown data to show how to use it in a prepared query
$pin_id = 1;

// produce the sql query statement
$sql = "Select count(*) as cnt
 from pin_mls_detail
 where brkmu_no IN('M','1','1F') AND pin_pin = ? AND date_pin IN(".implode(',',$dates).")";

// prepare, execute, and fetch the data using the php PDO extension - the connection is in $pdo
$stmt = $pdo->prepare($sql);
$stmt->execute([$pin_id]);
$row = $stmt->fetch();

$ttl_valid_spl_mon_mumls = $row['cnt']; // the number of rows that the query matched

#7

BUT, Phdr, he is not using PDO at all. He is just learning MySQL and I am getting up to MySQLi level.
He has no $pdo connection set up and he is not ready for object orientated PDO… Not yet, I think…

But, perhaps maybe…


#8

WOW…Your code works well Mr Phdr. Thanks a lot. But used it using MySQL instead of PDO or MySQLi. For PDO I have to amend my whole website for this and this is a lot of work. Can you recommend me easy way of converting my website from PHP 5.6 to latest PHP 7. What precautions and list of things needed to be altered(If you say I can make and ask in another post for this). My below code worked well as per your suggestions and help (hope so will work for other weekdays too, not only Monday):

////Below is the code:

date_default_timezone_set(“Asia/Kolkata”);
$today=date(“Y-m-d”);
$month = date(‘m’);
$year = date(‘Y’);
$days = cal_days_in_month(CAL_GREGORIAN, $month,$year);

for($i = 1; $i<= $days; $i++){
$day = date(‘Y-m-’.$i);
$dow = date(“l”, strtotime($day));
if($dow == “Monday”){
$mondates[] = “’$day’”;
print_r($mondates);
}

$pinid = 1;
$ttl_valid_spl_mon_mumls = 0;

foreach ($mondates as $mondate) {

$query = “Select count(*) as cnt from pin_mls_detail where brkmu_no IN(‘M’,‘1’,‘1F’) AND pin_pin = ‘$pinid’ AND date_pin IN(”.implode(’,’,$mondates).")";

print_r($query);
$sql = mysql_query($query);
$rows = mysql_fetch_array($sql);
print_r($rows);
}
echo $ttl_valid_spl_mon_mumls = $rows[‘cnt’];


#9

Thanks ErnieAlex, you explained a lot but arrays still confuse me a lot. Many things to take care of in Arrays, which is a headache for me.
I am running my website on PHP 5.6 and I got it developed few years ago from not a much experienced PHP developer. I have myself gained little knowledge by doing a Udemy onlin course in complete PHP website developer which included PHP, intro to Javascript, PDO, jQuery, Mysql, Bootstrap, but I am not perfect still.
As you recommended me to use MySQLi, should I change all the queries in my website to MySQLi and PDO (which will be a cumbersome work for me, hufffff). I am also planning to learn PHP OOPS and CakePHP (May be). Can I still continue running my website on PHP 5.6. Is it safe?


#10

You need to stop using loops where they are not needed. As stated and as shown in the example code I posted, the only loop you need is the one building the array of dates. Once you have that array and implode it inside the sql query statement, you have one single query to execute and fetch the data from. No addition loop is needed (you are not even using the $mondate variable inside of this added loop.)

The code will work for any specific weekday value. However, if what you are eventually doing is producing a report that spans a range of dates or a range of days of the week, you would not run separate queries for each value. You would instead use ONE query that gets all the data you want. You would need to post an example of the data you are trying to query for, but in general you would add a GROUP BY … term to the existing query to get it to operate on data per date or per day of the week.

If you have question(s) about converting old code, you should start a new thread. However, the biggest issues are security and simplicity. A feature that provided (some) protection against sql special characters in external data from breaking the sql query syntax (which is how sql injection is accomplished) was removed in php 5.4. The simplest and surest way of adding back this protection is to use prepared queries and use the php PDO extension. It is much simpler and more consistent than the php mysqli extension, especially concerning prepared queries. So, if you must convert old code, it will take the least amount of new code if you forget about the msyqli extension and instead just move directly to the PDO extension.


#11

HEARTILY THANKFUL, Mr Phdr :sunglasses:


#12

I know $mondate is not used anywhere but it somehow solved my purpose.

I don’t know more about how GROUP BY command works. So I have done it through writing foreach loop because otherwise doing with group I will get confused.I used this code for each day of the week.It became little lengthy but had to do in that way as I am not a professional in Php.
Thanks


#13

////Below is the Final Corrected code WITHOUT FOREACH LOOP:

date_default_timezone_set(“Asia/Kolkata”);
$today=date(“Y-m-d”);
$month = date(‘m’);
$year = date(‘Y’);
$days = cal_days_in_month(CAL_GREGORIAN, $month,$year);

for($i = 1; $i<= $days; $i++){
$day = date(‘Y-m-’.$i);
$dow = date(“l”, strtotime($day));
if($dow == “Monday”){
$mondates[] = “’$day’”;
print_r($mondates);
}

$pinid = 1;
$ttl_valid_spl_monbrk_mumls = 0;
$query = “Select count(*) as cnt from pin_mls_detail where brkmu_no IN(‘M’,‘1’,‘1F’) AND pin_pin = ‘$pinid’ AND date_pin IN(”.implode(’,’,$mondates).")";

//print_r($query);

$sql = mysql_query($query);

$rows = mysql_fetch_array($sql);

echo $ttl_valid_spl_monbrk_mumls = $rows[‘cnt’];