Inserting columns from different tables

PHP
1

Hi,

I have a MySQL table that has columns from other tables, and im trying to populate it using PHP (no luck). The table has three columns from other tables, and a user supplied column.

So far i am able to get the column titles from the database, and the column entries, but i have tried so many ways no luck. The best i have managed so far is to insert just one row, with fixed values, the first contaminant, and the last sample :S. Any hints/help/support will be greatly appreaciated - i’ve been on this for way too long!

Here is the form:

<?php 

include("database.php"); 

//	Error reporting 
//error_reporting (E_ALL ^ E_NOTICE);

$query = "SELECT contaminant, gac_value, sample_name
			FROM gac_ea, sample
			WHERE land_use = 'Allotment'
			AND sample.site_id = 1
			ORDER BY sample.id ";  
$result = mysql_query($query) or die(mysql_error()); 	 

while ($row = mysql_fetch_array($result)) {
extract($row); }

?>

<br><br>
<form name="gqra" id="gqra" action="insert_gqra.php" method="POST"> 
<table class="qra"> 
<tr>
<td class="row2" >&nbsp;</td>
<?php
$query = "SELECT site.id, sample_name
			FROM site, sample, prelim
			WHERE site.id = 1
			AND sample.site_id = site.id
			AND prelim.site_id = site.id
			ORDER BY sample.id ASC LIMIT 5"; 
$result = mysql_query($query) or die(mysql_error()); 		
	
while ($row = mysql_fetch_array($result)) {
extract($row);  
echo "<td class=\\"row2\\" ><label> <span> $sample_name</span> </label></td> " ; } 
echo "</tr>";  //close row 
 ?> 

<tr>
<td class="row3">
     <?php 
	$query3 = "SELECT contaminant, gac_value FROM gac_ea WHERE land_use = 'Allotment' ORDER BY id";
	$result3 = mysql_query($query3) or die(mysql_error());   
	echo "<select name=contaminant value=''></option>";
	echo "<option>Select </option>";
	while($row=mysql_fetch_array($result3)){ 	
	extract($row); 
	echo "<br>".$contaminant;  
		echo "<option value=$row[id]>$row[contaminant]</option>"; } // End contaminant loop 
		echo "</select>"; ?>
</td>

<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
</tr>

</tr> 
<tr>
<td class="row3">
     <?php 
	$query3 = "SELECT contaminant, gac_value FROM gac_ea WHERE land_use = 'Allotment' ORDER BY id";
	$result3 = mysql_query($query3) or die(mysql_error());   
	echo "<select name=contaminant value=''></option>";
	echo "<option>Select </option>";
	while($row=mysql_fetch_array($result3)){ 	
	extract($row); 
	echo "<br>".$contaminant;  
		echo "<option value=$row[id]>$row[contaminant]</option>"; } // End contaminant loop 
		echo "</select>"; ?>
</td>

<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
</tr>

</tr>
<tr>
<td class="row3">
     <?php 
	$query3 = "SELECT contaminant, gac_value FROM gac_ea WHERE land_use = 'Allotment' ORDER BY id";
	$result3 = mysql_query($query3) or die(mysql_error());   
	echo "<select name=contaminant value=''></option>";
	echo "<option>Select </option>";
	while($row=mysql_fetch_array($result3)){ 	
	extract($row); 
	echo "<br>".$contaminant;  
		echo "<option value=$row[id]>$row[contaminant]</option>"; } // End contaminant loop 
		echo "</select>"; ?>
</td>

<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
<td class="row3"><input type="text3" name="ms_conc" id="ms_conc" /></td>
</tr>

</tr>

  
<tr>
<td colspan="6" class="button" >
<input type="reset" value="Clear input"  />
<input type="submit" value="Compare with SGVs" /></td>
</tr>
</table> 

</form>

And the insert script:

<?php 

// $cont_name   = $_POST['cont_name'];
// $sample_name = $_POST['sample_name'];
// $gac_value   = $_POST['gac_value'];
$ms_conc     = $_POST['ms_conc'];

$query = "INSERT INTO qgra (id, contaminant, sample_name, gac_value, ms_conc) 
			VALUES ('','$contaminant', '$sample_name', '$gac_value', '$ms_conc')"; 
			
$result = mysql_query($query) or  die(mysql_error());  

echo "<br><br> Gac saved! "; 

?>

I’m thing the insert script should be something like:

for each sample and selected contaminant, insert sample, contaminant, gac_value from the database and the user inputs (ms_conc). I have tried doing that but it didnt work either.

2

  1. You’re trying to use the same ID and name more than once on each element. That’s not just invalid markup, that’s invalid for the form too.

  2. if all your TD inside a TR are getting the same class, put the class on the TR not the TD. All those classes are the equivalent to the old George Carlin joke about abortion – not every ejaculation deserves a name.

  3. you are echoing out a BR and content OUTSIDE the option but INSIDE the SELECT, that too is invalid markup and going to make the form not work.

Basically, your logic flow is completely broken in terms of the markup it outputs… basically it looks like you don’t know enough HTML to be writing the PHP in question. (It’s why I don’t get the people who say they learned PHP first – since PHP’s primary use is to output markup, how the ???)

It would also help if you bothered formatting the code in a sensible manner to make the logic flow more apparent.

For example, let’s take this:


<td class="row3">
     <?php 
    $query3 = "SELECT contaminant, gac_value FROM gac_ea WHERE land_use = 'Allotment' ORDER BY id";
    $result3 = mysql_query($query3) or die(mysql_error());   
    echo "<select name=contaminant value=''></option>";
    echo "<option>Select </option>";
    while($row=mysql_fetch_array($result3)){     
    extract($row); 
    echo "<br>".$contaminant;  
        echo "<option value=$row[id]>$row[contaminant]</option>"; } // End contaminant loop 
        echo "</select>"; ?>
</td>

From the top for problems, use of double quotes making it execute slower, lack of quotes on html attributes making for invalid markup in modern doctypes, closing an OPTION that hasn’t even been opened yet, break and contents of $containment inside the SELECT but outside and OPTION (only thing that can immediately follow SELECT or /OPTION is OPTION or /SELECT!), invalid php array indexes in php 4.3/newer (probably ignored, will be broken in next generation of php – basically you omitted the single quotes), multiple echo’s attempting to do the job of one echo, unclosed option tag and/or option being mistaken as a comparison…

…and I don’t know why you’d be wasting your time on a extract there.

Let’s assume the returned dataset is 1 => ‘containment1’, 2 => ‘containment2’. The markup that would output SHOULD end up looking like this:


<td class="row3">
<select name=contaminant value=''></option>
<option>Select </option>
<br>containment1
<option value=1>contaminant1</option>
<option value=2>contaminant2</option>
</select>
</td>

Do we SEE a problem here?

What that SHOULD probably be doing is this:


	<tr class="row3">
		<td>
<?php
		$query3="
			SELECT contaminant, gac_value
			FROM gac_ea
			WHERE land_use = 'Allotment'
			ORDER BY id
		";
		if ($result3=mysql_query($query3)) {
			echo '
			<select name="contaminant" value="">
				<option>Select</option>';

			while($row=mysql_fetch_array($result3)) {
				echo '
				<option value="',$row[id],'">',$row[contaminant],'</option>';
			}
			echo '
			</select>';
		} else die(mysql_error());
?>

	</td>

Which would using that same example dataset I listed before output:


<tr class="row3">
	<td>
		<select name="contaminant" value="">
			<option>Select</option>';
			<option value="1">contaminant1</option>
			<option value="2">contaminant2</option>
		</select>
	</td>

See the difference?

3

Thank you deathshadow60,

That really helped.