Sorting results of array a second time before output? 1

[Apollo6]

I have a list of employees with birthdate as one of the fields. This field comes from the database as a string, i.e. 19721003 for 10/03/1972. The initial query pulls all employees. I then have the code working to pull just employees for a specific month. I do this by pulling out the month from the string as I loop through the array of all employees. Then echo out the employee if the month matches. What I want to do in addition is sort the results by day of the month ascending. The problem is that I don't know how to sort a second time after I went through the first array.

<?
	if ($to_ret > 0)
	for ($i=1; $i < $to_ret + 1; $i++)
		{			
			$record_row = odbc_fetch_row($result_A,$i);
			$lastname = odbc_result ($result_A,'lastname');
			$firstname = odbc_result ($result_A,'altaddr1');
			$location = odbc_result ($result_A,'altaddr2');
			$phone = odbc_result ($result_A,'altaddr4');
			$bday_mth = odbc_result ($result_A,'birthdate');
			$bday_mth = preg_replace('/(\d{4})(\d{2})(\d{2})/', '$2', $bday_mth);
			$bday_day = odbc_result ($result_A,'birthdate');
			$bday_day = preg_replace('/(\d{4})(\d{2})(\d{2})/', '$3', $bday_day);
			$email = odbc_result ($result_A,'altcntry');
			if ($bday_mth == $whatMonth) {
?>
echo out employee data here...

Seems like somehow load the results into a second array but also add the $bday_day to the results and then sort the second array by the $bday_day field added, then echo out the second array. If so, I'm not sure how to write that syntax.

Am I way off base here or is there an easier way to go about this. If more information is needed I can provide. Thanks

 

[sleipnir214]

You're not pulling all the data from the table then filtering and ordering in PHP, are you? It would be much more efficient to have your database system do that for you -- all you need do is use the right WHERE and ORDER BY clauses in your SELECT query.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[ID10T16]

I'm not that familiar with odbc, but maybe telling you how I would attempt it in mysql will shed some light...

First, if I knew that I was going to be looking for a specific date, I would incorporate that into the initial database query (ie: SELECT * FROM employees WHERE [some boolean to match for date by month] ORDER BY date ASC)

Something like that would return only the employees for said month, and they would already be in ascending order.

If that doesn't help, maybe you can post what the actual query is that you're using.

I seriously think that processing the array is way more work than you need to do. I believe that the answer to your problem lies in your query.

 

[jpadie]

in mysql it might look something like this. but the OP is better off asking in the forum that supports his particular rdbms

order by month(birthdate) asc, lastname asc, dayofmonth(birthdate) asc

and if you just want to pull out november birthdays add a where clause before the order by and then you could use the order by clause suggested by ID10T16 with the prefixed addition of lastname

where month(birthdate)=11 order by...

 

[Apollo6]

Both... I completely agree with the fact that I should get the SQL statement to get exactly what I want then work with that recordset. The problem is, I have not be able to determine the correct syntax, if possible, to return just the data I want. For example:

$query_A = "SELECT lastname, altaddr1, altaddr2, altaddr4, altcntry, birthdate FROM SOINC.UPEMPL where status <> 3 AND birthdate = '$month'";
	$result_A = odbc_exec($connect_soinc, $query_A);
	$record_row = odbc_fetch_row($result_A);

The problem is that the birthdate is a string, i.e. 19450302, meaning 03/02/1945. I can get $month = 03. I can't seem to figure out how to apply a function to piece out the birthdate in the SQL statement. This drove me just pulling all records and pulling out what I wanted in PHP. This is a PervasiveSQL database but I keep get Invalid Scalar function calls regardless of what I try. I get back all records with "%03%" in them regardless of position if I use wildcards.

Driving me crazy. Will try suggestions and keep trying on the SQL syntax. Thanks!

 

[jpadie]

as I posted above.

$month =   ;//enter month from 1 to 12;
$query_A = "
			SELECT 
				lastname, 
				altaddr1, 
				altaddr2, 
				altaddr4, 
				altcntry, 
				birthdate 
			FROM 
				SOINC.UPEMPL 
			WHERE 
				status != 3 
				AND 
				birthdate = MONTH($month)" 
			ORDER BY 
				lastname asc, 
				DAYOFMONTH(birthdate)";

 

[Apollo6]

jpadie-

"SELECT lastname, altaddr1, altaddr2, altaddr4, altcntry, birthdate FROM TOPLLC.UPEMPL where status <> 3 And month(birthdate) = '$month'";

I get Incompatible Types in Expression syntax error. I would assume this is do to the birthdate field is actually text and I'm applying a date type function. With the direction now moving back to SQL instead of PHP, I'll check a different forum to see if I can find out if there is some specific syntax the PervasiveSQL will handle.

Thanks for the suggestions.

 

[jpadie]

try casting the column type to a date type as part of the query.

 

[Apollo6]

I tried the following and it barked as well, was this what you were thinking?

"SELECT lastname, altaddr1, altaddr2, altaddr4, altcntry, birthdate FROM TOPLLC.UPEMPL where status <> 3 And date(month(birthdate)) = '$month'";

I'm hoping to find some SQL sytax that is PervaseSQL specific and allow the initial recordset to be filtered to just what I need.

 

[jpadie]

date is a function. not a casting directive. however i'm not convinced that pervasivesql will be ok with casting this data as you have not been storing the date in a sql compliant manner (yyyy-mm-dd).

so we are left with a kludge (but it's not that ugly)...

$query_A = "
			SELECT 
				lastname, 
				altaddr1, 
				altaddr2, 
				altaddr4, 
				altcntry, 
				birthdate 
			FROM 
				SOINC.UPEMPL 
			WHERE 
				status != 3 
				AND 
				CAST(SUBSTRING(birthdate,5,2) as INTEGER) = $month 
			ORDER BY 
				lastname asc, 
				CAST(RIGHT(birthdate, 2) as INTEGER)";

the casts in the above are designed to allow you to pass a numeric month in $month and to make sure that the sorting works numerically rather than textually (where you would get 1, 11 rather than 1,2).

i have not used pervasive sql but a brief read of the manual suggests that these basic types of data manipulation are supported.

 

[Apollo6]

The below is the final version of the SQL statement. I actually got the syntax answered in another forum.

"SELECT substring(convert(birthdate, sql_char),7,2), lastname, altaddr1, altaddr2, altaddr4, altcntry, birthdate FROM SOINC.UPEMPL where status <> 3 AND substring(convert(birthdate, sql_char),5,2) = '$whatMonth'
	            UNION
				SELECT substring(convert(birthdate, sql_char),7,2), lastname, altaddr1, altaddr2, altaddr4, altcntry, birthdate FROM TOPLLC.UPEMPL where status <> 3 AND substring(convert(birthdate, sql_char),5,2) = '$whatMonth'";

Thanks for all who commented and a star for jpadie keeping with me even though it turned into a SQL question.