Query

Please enter your selection criteria


0 && $value != "") { $where_clause .= " and "; } if ($key == "type_code" || $key == "title" || $key == "comment") { if ($value != "") { $where_clause .= construct_char($key,$value); $cnt_where++; } } if ($key == "tape_num" || $key == "hours") { if ($value != "") { $where_clause .= construct_numeric($key,$value); $cnt_where++; } } } } if ($cnt_where == 0) { echo "

No Selection Criteria Entered


"; query_mode1($input_array); echo "

\n"; exit; } $where_clause = "where $where_clause"; list($statmt_id,$CNT_FOUND) = cnt_and_query("vhs",$where_clause); if ($CNT_FOUND == 0) { echo "\n"; // display "0 records found" in red color echo "

$CNT_FOUND records(s) selected


\n"; echo "\n"; } else { echo "

$CNT_FOUND records(s) selected

\n"; // CREATE A PIPE-DELIMITED LIST OF SERIAL ID'S while ($row = ifx_fetch_row($statmt_id)) { $pick_list .= $row[serial_id] . "|"; } echo "
\n"; $pick_id = 1; while ($row = ifx_fetch_row($statmt_id,$pick_id)) { echo "\n"; $serial_id = $row[serial_id]; $type_code = chop($row[type_code]); $tape_num = $row[tape_num]; $title = chop($row[title]); $hours = $row[hours]; $comment = chop($row[comment]); echo ""; echo ""; echo ""; if ($title == "") { echo " "; } else { echo ""; } if ($hours == "") { echo ""; } else { echo ""; } if ($comment == "") { echo ""; } else { echo ""; } echo "\n"; $pick_id++; } echo "
Serial ID
Type Code
Tape Number
Title
Hours
Comment
Modify
$serial_id
$type_code
$tape_num
$title $hours $comment"; echo "
"; echo ""; echo ""; echo ""; echo ""; echo ""; echo "
"; echo "

\n"; ifx_free_result ($statmt_id); echo "
\n"; } /************************************************************************************/ echo "

\n"; echo "Criteria:
\n"; echo "$where_clause

\n"; # Display criteria used to get this list echo "

\n"; display_menu1("",0); # Menu: Query, Add-New, Add-Same } /******************************************************************************* * * Function Name: construct_char * Author: Peter R. Schmidt * Description: Construct part of a SQL where-clause for a character type column * * Input Arg 1: Name of column to search on * Input Arg 2: Value to search on * * Return Arg: Where clause * *******************************************************************************/ function construct_char ($column_name,$query_string) { $retval = ""; while (true) { if ($query_string == "=") { $ret_val = "$column_name is null"; // IS NULL break; } if ($query_string == "!=") { $ret_val = "$column_name is not null"; // IS NOT NULL break; } if (preg_match("/\*/",$query_string)) { // MATCHES $ret_val = "$column_name matches \"$query_string\""; break; } if (preg_match("/\|/",$query_string)) { // IN (pipe symbol in criteria) $ret_val = "$column_name in ("; // Start with open paren $cnt=0; $pipe_list = preg_split("/\|/",$query_string); // Create an array of strings while (list($key,$value) = each($pipe_list)) { // for each element in the array... if ( $cnt ) { $ret_val .= ","; // Add a comma to the string } $ret_val .= "\"" . $value . "\""; // append new value to string surrounded by double quotes $cnt++; } $ret_val .= ")"; // Finish with a closed parens break; } $ret_val = "$column_name = \"$query_string\""; // EXACT MATCH break; } return $ret_val; } /******************************************************************************* * * Function Name: construct_numeric * Author: Peter R. Schmidt * Description: Construct part of a SQL where-clause for a numeric type column * * Input Arg 1: Name of column to search on * Input Arg 2: Value to search on * * Return Arg: Where clause * *******************************************************************************/ function construct_numeric ($column_name,$query_string) { $retval = ""; while (true) { if ($query_string == "=") { $ret_val = "$column_name is null"; // IS NULL break; } if ($query_string == "!=") { $ret_val = "$column_name is not null"; // IS NOT NULL break; } if (preg_match("/\*/",$query_string)) { // MATCHES $ret_val = "$column_name matches \"$query_string\""; break; } if (preg_match("/\|/",$query_string)) { // IN (pipe symbol in criteria) $ret_val = "$column_name in ("; // Start with open paren $cnt=0; $pipe_list = preg_split("/\|/",$query_string); // Create an array of strings while (list($key,$value) = each($pipe_list)) { // for each element in the array... if ( $cnt ) { $ret_val .= ","; // Add a comma to the string } $ret_val .= "\"" . $value . "\""; // append new value to string surrounded by double quotes $cnt++; } $ret_val .= ")"; // Finish with a closed parens break; } if (preg_match("/\<|\>/",$query_string)) { // Less then, greater then, etc $ret_val = "$column_name $query_string"; break; } if (preg_match("/\:/",$query_string)) { // Between list ($part1,$part2) = preg_split("/:/",$query_string); $ret_val = "$column_name between $part1 and $part2"; break; } $ret_val = "$column_name = \"$query_string\""; // EXACT MATCH break; } return $ret_val; } /******************************************************************************* * * Function Name: cnt_and_query * Author: Peter R. Schmidt * Description: Query the Informix database, count rows found * * Input Arg 1: table name to search * Input Arg 2: where clause to search on * * Return Arg: rows found * * Note: The manual says you can use ifx_num_rows or ifx_affected_rows to get * the number of rows selected. I found this didn't work. I went back to * the old fashioned way of just counting the rows found. Where performance * is an issue, that may not be the best way do it. * *******************************************************************************/ function cnt_and_query ($table_name,$where_clause) { $num_rows_selected = 0; $statmt_id1 = 0; $statmt_id2 = 0; $connect_id = database_connection(); // ESTABLISH A DATABASE CONNECTION if ($connect_id) { $statmt_txt = "select count(*) count from $table_name $where_clause"; $statmt_id1 = ifx_query($statmt_txt,$connect_id); // GET COUNT if (!$statmt_id1) { echo "Unable to execute Informix sql statement
\n"; echo "$statmt_txt
\n"; chk_ifx_err1($statmt_id1); } $row = ifx_fetch_row($statmt_id1); // FETCH RESULT OF COUNT INTO ARRAY if (!$row) { echo "Unable to fetch Informix result
\n"; echo "$statmt_txt
\n"; chk_ifx_err1($statmt_id1); } $num_rows_selected = sprintf("%d",$row[count]); // FORMAT COUNT INTO VARIABLE ifx_free_result ($statmt_id1); if ($num_rows_selected > 0) { $statmt_txt = "select * from $table_name $where_clause"; $statmt_id2 = ifx_prepare($statmt_txt,$connect_id,IFX_SCROLL); // PREPARE SELECT if (!$statmt_id2) { echo "Unable to prepare Informix sql statement
\n"; echo "$statmt_txt
"; chk_ifx_err1($statmt_id2); } $ret_val = ifx_do($statmt_id2); // EXECUTE PREPARED STATEMENT if (!$ret_val) { echo "Unable to do 'ifx_do' Informix sql statement
\n"; echo "$statmt_txt
"; chk_ifx_err1($statmt_id2); } } } return array ($statmt_id2,$num_rows_selected); } ?>