• CONTEST ALERT - Experience the power of DDR5 memory with Kingston Click for details

PHPMySQL Looping SELECT, is it possible?

Mpe

Broken In
i extract years from mysql using the array loop like this,

foreach (array(date('Y', strtotime($row['drilling_date']))) as $v) {
//1988, 1989, 1990 and so on..
$current_year = $v;
$next_year = $v+1; // which shows the year nest to $current_year
// problem here
}

Is there any way i could loop the select to query between years with one query
i have this right now but its not working..

foreach (array("SELECT COUNT(result) FROM hptw WHERE drilling_date between '$v-04-01' AND '$next-03-01' and result = 'success'") as $b);

What i am trying to do is that put the COUNT result in array, the first array would contain the result of 1988 and 1989, the second array, 1989-1990 ... and the third array 1990-1991 an continue looping till it fetch all the year present in the database..

e.g (45(between 1988 - 1989), 50(between 1989-1990) and so on.......)

I hope there is another approach to solve this..

oh i also get this result

Resource id #6
 
Last edited:

asingh

Aspiring Novelist
It would probably need to be a stored proc. since you would use T-SQL.

1. You would need to get the max and min years dynamically.
2. There create a dynamic query to pull out counts.
3. Store them in a table.
4. Use the table storage to get it into your PHP table.

CREATE PROC [dbo].[mytestDB]


AS

BEGIN


DECLARE @TABLE_COUNT_MIN int
DECLARE @TABLE_COUNT_MAX int


/*GET THE MAX COUNT'*/
SELECT @TABLE_COUNT_MAX = MAX(YEAR) FROM tbl_tmp_table_info

/*SET THE MIN COUNT*/
SELECT @TABLE_COUNT_MIN = MIN(YEAR) FROM tbl_tmp_table_info

/*LOOP BEGIN*/


WHILE @TABLE_COUNT_MIN !> @TABLE_COUNT_MAX
BEGIN

SELECT @DYN_UPDATE_FIELD = Column_Name FROM tbl_tmp_table_info
WHERE Row_key = CAST(@TABLE_COUNT_MIN AS CHAR)

/*CHANGE THE NULLS */
SET @DYN_UPDATE_QUERY=
'UPDATE tbl_raw_data SET '
+@DYN_UPDATE_FIELD+ ' = ' +@CONST_DUMMY_VAL+
' WHERE '+@DYN_UPDATE_FIELD+ ' IS NULL'

EXEC (@DYN_UPDATE_QUERY)

SET @TABLE_COUNT_MIN = @TABLE_COUNT_MIN + 1

END /*LOOP END*/


END

See how the max and min is first selected and used in a loop. I am doing an update , but you would do an insert to a fresh blank table.
 
Top Bottom