Page 1 of 1

Executing a variable number of queries

Posted: Mon Mar 17, 2003 12:54 am
by bluenote
Hello,

to the site admins: please don't be angry if this post actually belongs to "Databases" :wink:

Here's my question: In some part of the Back Office of the PHP / MySQL based cms i'm currently working at, an editor can inscribe names of people which are working at the same project. After choosing how many (1 - xxxx) people he wants to inscribe, a second form opens with the following code:

Code: Select all

<?php

if (($p04=='01') || ($p04=='03')) {

for ($i=0; $i < $auth_num; $i++) {
echo "<B>$i.</B>&nbsp;&nbsp;<INPUT TYPE=text  NAME="auth_last_$i" VALUE="" SIZE=25>&nbsp;&nbsp;<INPUT TYPE=text NAME="auth_first_$i" VALUE="" SIZE=10>&nbsp;&nbsp;<INPUT TYPE=text NAME="auth_ini_$i" VALUE="" SIZE=2>\n";

echo "<BR>";}}

else {}
?>
Therewith, $auth_num rows with 3 input fields (Last name, first name, Initials) in each row are produced, $i is used to specify each row and the input fields within it. $p04 is an internal project number and not relevant for the following actions.

After the editor has created and filled out let's say 5 rows, the form is send to a validation step, in which a MySQL query should extract data corresponding to the names entered before from three different tables in the staff database:

Code: Select all

<?php

require_once('../../include/databases/db_connect.php3');

$dbName = "DBstaff";

MYSQL_CONNECT($hostname,$username,$password) OR DIE("Database connection failed.");

@mysql_select_db("$dbName") or die("Database not found.");

$query = "SELECT staff.s01, staff.s03, staff.s04, staff_departments.dep_s03, staff_departments.dep_s05, staff_departments.dep_s06, staff_groups.grp_s03, staff_groups.grp_s04, staff_groups.grp_s05, staff_groups.grp_s06 from staff LEFT JOIN staff_departments on staff.s03 = staff_departments.dep_s03 LEFT JOIN staff_groups on staff_departments.dep_s03 = staff_groups.grp_s03 WHERE staff.s05 = '$auth_last_xx' AND staff.s06 = '$auth_first_xx' AND staff.s07 = '$auth_ini_xx'";

$erg = MYSQL_QUERY($query);

$numrows = MYSQL_NUM_ROWS($erg);


?>
This query is working fine but: it must be run for each row (Last-First-Initial) produced in the step before, respectively with the exact field names $i produced (in this case: $auth_last_0 [...] $auth_ini_4), so that the data can be separated and processed. If I would know the exact number of rows in advance - no problem. But the number can be 1 as well as 33, and the field numbers are not only important for data separtating and processing but also to flag the most important (the last) and the second most important (the first) person in this set. Any ideas?

Thanx in advance.

Posted: Mon Mar 17, 2003 2:01 am
by McGruff
If I would know the exact number of rows in advance - no problem
In the form code you seem to have a variable for the number of rows: $auth_num.

Add a hidden field with value="$auth_num" to pass this along to the validation script.

RE AW: Executing a variable number of queries

Posted: Mon Mar 17, 2003 2:03 am
by bluenote
Hello McGruff,

thanx for your f a s t reply.

Your suggestion also was one of my first ideas. Now, I'm trying this:

Code: Select all

<?php

$auth_num = $_REQUEST["auth_num"];

require_once('../../include/databases/db_connect.php3');

$dbName = "DBstaff";

MYSQL_CONNECT($hostname,$username,$password) OR DIE("Database connection failed.");

@mysql_select_db("$dbName") or die("Database not found.");

for ($i=0; $i < $auth_num; $i++) {
	
$qq = "auth_query".$i;
	
$qe = "auth_erg".$i;
	
$qn = "auth_numrows".$i;
	
$auth_last = "auth_last_".$i;
	
$auth_first = "auth_first_".$i;
	
$auth_ini = "auth_ini_".$i;
	
${$qq} = "SELECT staff.s01, staff.s03, staff.s04, staff_departments.dep_s03, staff_departments.dep_s05, staff_departments.dep_s06, staff_groups.grp_s03, staff_groups.grp_s04, staff_groups.grp_s05, staff_groups.grp_s06 from staff LEFT JOIN staff_departments on staff.s03 = staff_departments.dep_s03 LEFT JOIN staff_groups on staff_departments.dep_s03 = staff_groups.grp_s03 WHERE staff.s05 = '${$auth_last}' AND staff.s06 = '${$auth_first}' AND staff.s07 = '${$auth_ini}'";
	
${$qe} = MYSQL_QUERY(${$qq});
	
${$qn} = MYSQL_NUM_ROWS(${$qe});
	
if (${$qn} > '0') {
		
		$auth = "auth_".$i;
		
		${$auth} = 0;
		
		$as = "auth_status".$i;
		
		$sIDS = "auth_dep_sIDS".$i;
		
		$PIN = "auth_PIN".$i;
		
		${$as} = mysql_result(${$qe},${$auth},"s01");
		${$sIDS} = mysql_result(${$qe},${$auth},"s03");
		${$PIN} = mysql_result(${$qe},${$auth},"s04");
		
		$dIDS = "auth_dep_dIDS".$i;
		
		$den_name = "auth_dep_enNAME".$i;
		
		$dge_name = "auth_dep_geNAME".$i;
		
		${$dIDS} = mysql_result(${$qe},${$auth},"dep_s03");
		${$den_name} = mysql_result(${$qe},${$auth},"dep_s05");
		${$dge_name} = mysql_result(${$qe},${$auth},"dep_s06");
		
		$gdIDS = "auth_dep_gIDS".$i;
		
		$ggIDS = "auth_grp_gIDS".$i;
		
		$gen_name = "auth_grp_enNAME".$i;
		
		$gge_name = "auth_grp_geNAME".$i;
		
		${$gdIDS} = mysql_result(${$qe},${$auth},"grp_s03");
		${$ggIDS} = mysql_result(${$qe},${$auth},"grp_s04");
		${$gen_name} = mysql_result(${$qe},${$auth},"grp_s05");
		${$gge_name} = mysql_result(${$qe},${$auth},"grp_s06");}
		
		else {}}

?>
Any Comments are welcome.

P.S.: My Enviroment is - as far as it is important - Solaris 7 Server, PHP 4.2.2 & MySQL 3.23.52.

RE AW AW: Executing a variable number of queries

Posted: Thu Mar 20, 2003 3:05 am
by bluenote
Hi again 8) ,

by splitting the thing above into 5 single querys (here is one of them)

Code: Select all

<?php
$creator_num = $_REQUEST["creator_num"];

require_once('../../include/databases/db_connect.php3');

$dbName = "DBstaff";

$userstable = "staff";

MYSQL_CONNECT($hostname,$username,$password) OR DIE("Database connection failed.");

@mysql_select_db("$dbName") or die("Database not found.");

for ($i=0; $i < $creator_num; $i++) {
	
	$qq = "pcreator_query".$i;
	
	$qe = "pcreator_erg".$i;
	
	$qn = "pcreator_numrows".$i;
	
	$creatornfamily = $_REQUEST["creatornfamily_$i"];
	
	$creatorngiven = $_REQUEST["creatorngiven_$i"];
	
	$creatorini = $_REQUEST["creatorini_$i"];
	
	${$qq} = "SELECT s04 FROM $userstable WHERE s05 = '$creatornfamily' AND s06 = '$creatorngiven' AND s07 = '$creatorini'";
	
	${$qe} = MYSQL_QUERY(${$qq});
	
	${$qn} = MYSQL_NUM_ROWS(${$qe});
	
	if (${$qn} > '0') {
		
		$pcreator = "pcreator_".$i;
		
		${$pcreator} = 0;
		
		$PIN = "creator_PIN".$i;
		
		${$PIN} = mysql_result(${$qe},${$pcreator},"s04");
		
		$subpin = ${$PIN};
		
		$p10 =  "$subpin,";}
		
		else {}}

?>
i've made it working. But then a new problem came up: one of the single querys produces a string of numbers like "02,02,03,03,04,04,04,05"; those numbers are identification numbers for single work groups. This is OK for the beginning, but I need each occuring number one time only. So how can I extract the doubles and "kill" 'em? strcomp() / strncomp() isn't very useful, because I have nothing to compare with. Is explode() / implode() a suitable way to do it?

T.i.A., bluenote