MySQL Join Tables and Looping Through Values

[jbwebdesign]

hello, I am working on a script and i can't seem to get it to work properly. the loops are confusing me.....

I am trying to loop through the "forms" table. the fields in the table are "field_name" and "user_var".

 

The problem comes in where i try to replace the $record['contract_content'] that contains any "user_var" with the value of the field from the users table where id=42

 

 

i have 2 tables

1.)users

 

a.)id

b.)client_first_name

c.)client_last_name

d.)client_address

e.)client_city

f.)client_state

g.)client_zipcode

h.)client_cell_phone

 

2.)forms

 

a.)id

b.)field_name

c.)user_var

 

here is my query: SELECT forms.field_name,forms.user_var, users.client_first_name, users.client_last_name, users.client_address, users.client_city, users.client_state, users.client_zipcode, users.client_cell_phone FROM forms, users WHERE users.id=42

 

 

here is my code:

<?php include("process.php"); ?>
<h2>Generated Contract</h2>
<div id="success"><?php echo $status; ?></div>
<?php 
$contract_id = $_POST['contractID'];
$client_id = $_POST['clientID'];

$sql = "SELECT * FROM " . TABLE_CONTRACTS . " WHERE id=" . $contract_id; 
// feed it the sql directly. store all returned rows in an array 
$rows = $db->fetch_all_array($sql); 

// get customized variables to tie into the contract
$sql2 = "SELECT variable,value FROM " . TABLE_VARIABLES;
$rows2 = $db->query($sql2);

//lets get out customized form fields
$sql4 = "SELECT * FROM " . TABLE_FORMS;
$rows4 = $db->fetch_all_array($sql4);

//lets get out customized form fields
$sql3 = "SELECT * FROM " . TABLE_FORMS;
$sql3 = "SELECT ".TABLE_FORMS.".field_name,".TABLE_FORMS.".user_var";
foreach($rows4 as $record4){
$sql3 .= ", ".TABLE_USERS.".".$record4['field_name']."";
}
$sql3 .=" ".
"FROM ".TABLE_FORMS.", ".TABLE_USERS." ".
"WHERE ".TABLE_USERS.".id=".$_POST['clientID'];
$rows3 = $db->query($sql3);


//lets get the fields from the users table FROM THE SELECTED USER
$sql5 = "SELECT * FROM " . TABLE_USERS . " WHERE id=". $_POST['clientID'];
$rows5 = $db->query($sql5);


// print out array later on when we need the info on the page 
foreach($rows as $record){ 

?>

<div id="Contract">
<form action="modules/my_contracts/filled_contract.php" method="post">
<input type="hidden" name="id" value="<?php echo $_GET['id']; ?>" />
<label for="contract_type">Contract Type:</label><input type="text" id="contract_type" name="contract_type" value="<?php echo $record['contract_type']; ?>">
<label for="contract_name">Contract Name:</label><input type="text" id="contract_name" name="contract_name" value="<?php echo $record['contract_name']; ?>">
<br /><br />
<label for="contract_content">Contract Content:</label>
</div>
<div id="ContractContent">
<textarea class="ckeditor" cols="80" id="contractContent" name="contract_content" rows="10">

<?php 
//echo $sql3;
while($key = $db->fetch_array($rows2)){		
if(stristr($record['contract_content'],$key['variable'])){
	$record['contract_content'] = str_replace($key['variable'],$key['value'],$record['contract_content']);
}	
}


/*
*
* The problem is somewhere around here.....
*
*/

while($key2 = $db->fetch_array($rows3)){	

	if(stristr($record['contract_content'],$key2['user_var'])){
		foreach($rows4 as $record4){
			$record['contract_content'] = str_replace($key2['user_var'],$key2[$record4['field_name']],$record['contract_content']);
		}
}
}
echo $record['contract_content'];
?>

</textarea>
<input type="submit" value="Print Contract!">
</form>
</div>

<?php 
}//end for loop
?>  

 

any ideas on how to do this?

Edited November 16, 2010 by jbwebdesign

[falkencreative]

Since I don't have access to your database tables to test this... What are the results that you are getting when you run your current code? Any errors?

 

You have a comment in your code "The problem is somewhere around here....." -- does that mean all of the code previously is working properly?

[jbwebdesign]

yes, and i just found my mistake

 

here is my new code :

 

<?php include("process.php"); ?>
<h2>Generated Contract</h2>
<div id="success"><?php echo $status; ?></div>
<?php 
$contract_id = $_POST['contractID'];
$client_id = $_POST['clientID'];

$sql = "SELECT * FROM " . TABLE_CONTRACTS . " WHERE id=" . $contract_id; 
// feed it the sql directly. store all returned rows in an array 
$rows = $db->fetch_all_array($sql); 

// get customized variables to tie into the contract
$sql2 = "SELECT variable,value FROM " . TABLE_VARIABLES;
$rows2 = $db->query($sql2);

//lets get out customized form fields
$sql4 = "SELECT * FROM " . TABLE_FORMS;
$rows4 = $db->fetch_all_array($sql4);

//lets get out customized form fields
//$sql3 = "SELECT * FROM " . TABLE_FORMS;
$sql3 = "SELECT ".TABLE_FORMS.".field_name,".TABLE_FORMS.".user_var";
foreach($rows4 as $record4){
$sql3 .= ", ".TABLE_USERS.".".$record4['field_name']."";
}
$sql3 .=" ".
"FROM ".TABLE_FORMS.", ".TABLE_USERS." ".
"WHERE ".TABLE_USERS.".id=".$_POST['clientID'];
$rows3 = $db->query($sql3);




//lets get the fields from the users table FROM THE SELECTED USER
		$sql5 = "SELECT ";
		foreach($rows4 as $record4){
			$sql5 .= $record4['field_name'] . ',';
		}
		//remove the last comma from the looped values
		$sql5_final = substr($sql5, 0, -1);
		$sql5_final .= " FROM " . TABLE_USERS . " WHERE id=". $_POST['clientID'];



// print out array later on when we need the info on the page 
foreach($rows as $record){ 

?>

<div id="Contract">
<form action="modules/my_contracts/filled_contract.php" method="post">
<input type="hidden" name="id" value="<?php echo $_GET['id']; ?>" />
<label for="contract_type">Contract Type:</label><input type="text" id="contract_type" name="contract_type" value="<?php echo $record['contract_type']; ?>">
<label for="contract_name">Contract Name:</label><input type="text" id="contract_name" name="contract_name" value="<?php echo $record['contract_name']; ?>">
<br /><br />
<label for="contract_content">Contract Content:</label>
</div>
<div id="ContractContent">
<textarea class="ckeditor" cols="80" id="contractContent" name="contract_content" rows="10">

<?php 
//echo $sql3;
while($key = $db->fetch_array($rows2)){		
if(stristr($record['contract_content'],$key['variable'])){
	$record['contract_content'] = str_replace($key['variable'],$key['value'],$record['contract_content']);
}	
}

while($key2 = $db->fetch_array($rows3)){		
if(stristr($record['contract_content'],$key2['user_var'])){
		$record['contract_content'] = str_replace($key2['user_var'],$key2['field_name'],$record['contract_content']);

	//now we have to replace the key2[field_name] with the actual value of it
	$q = mysql_query($sql5_final);

while($my_rows = mysql_fetch_array($q, MYSQL_ASSOC)){
	$record['contract_content'] = str_replace($key2['field_name'],$my_rows[$key2['field_name']],$record['contract_content']);
	}

}
}


echo $record['contract_content'];
?>

</textarea>
<input type="submit" value="Print Contract!">
</form>
</div>

<?php 
}//end for loop
?>  

[jbwebdesign]

The users table fields are created according to the "field_name" in the Forms table.

 

 

so i was getting the value of "field_name" but it wouldn't replace the user_var with the correct value.

 

there for i had to create a new while loop to replace the user_var with the value of the field_name loop for the $_GET['id'] user.

 

 

quite confusing and difficult to explain.