How do I - set a select option after read from MYSQL Table

BLottman

Hello ... I'm hoping someone can help me understand what I'm doing wrong.

I'd like to set a number of select options in a form to preset values that are read from a MySQL table. Example: User's complete a form and the values are stored to the table on submit.

Later, user's need see in an update form their original values previously stored so that they can change and update those values and subsequently store them back to the MySQL table.

Here's what I've done so far:

Before the form displays, all of the values are read from the MySQL table and associated with the variable names for each of the selects.

Then the form is supposed to display the retrieved values but the values read do not appear in the select options of the form.

I use a sql query to read all of the values available for changing and here's how I currently associate values read from the table:

$Zone = row["Zone"];

Here's a segment of code from the form:

<tr><td align=right>
<span class=s1>
Zone
</span>
</td>
<td align=left>
<select name="Zone">
<option name="Zone">NorthCentral</option>
<option name="Zone">NorthEast</option>
<option name="Zone">EastCentral</option>
<option name="Zone">SouthEast</option>
<option name="Zone">SouthCentral</option>
<option name="Zone">SouthWest</option>
<option name="Zone">WestCentral</option>
<option name="Zone">NorthWest</option>
</select>
</td>

How do I associate what's been read from the table with the display on the form?

Thanks in advance.

jeremuck

You have to print them.
HTML won't look into PHP's memory.

Once you've queried the database,

Try doing something like this:

<?php
$selectZoneHTML = "<select name=\"Zone\">\n";

while($zoneresult = mysql_fetch_array($dbResults))
{
  $selectZoneHTML .= "\n<option value=\"" . $zoneresult['Zone'] . "\">" . $zoneresult['Zone'] . "</option>"; 
}

$selectZoneHTML .= "\n</select>";

echo $selectZoneHTML;
?>

It's a little unclear exactly what you want to do and what code you are using. If you have trouble, post more of your code.

BLottman

<?php
include("settings.inc");
mysql_select_db($mydb, $connection);
$sql = "Select ID, ZONE From properties where ID='$dproperty'";
$myresult=@($sql);
$row = mysql_fetch_array($myresult);
$Zone = $row["ZONE"];
?>

<form action="<? $PHP_SELF ?>" method=post>

<-- the following select was the smallest section of code so I'm using it as an example -->

</form>

if ($Submit) {
$sql2 = "UPDATE properties SET ZONE ='$Zone' where ID

= '$dproperty';

$myresult=@($sql);
}

It's fairly lame (i.e., open the table, read the values from the row where the id=dproperty, and use it somehow to display what the user currently has stored in the table. When the user selects something different, I want to rewrite it back to the table.)

Being the neophyte that I am, what does the .= do in your code? Does it concatenate the string to the right with the existing string on the left of the = sign?

$selectZoneHTML .= "\n<option value=\"" . $zoneresult['Zone'] . "\">" . $zoneresult['Zone'] . "</option>";

I think I understand what you are saying. Are you suggesting that I use php (i.e., as in your code example) instead of using the mixture of html and php that I was wanting to do.

jeremuck

You hit the nail on the head with the .=. Very well put.
The dot (.) is the concatenation operator. It will concatenate any two strings. When used with the = like .= it addes it onto whatever was already there.

I think I understand what you are saying. Are you suggesting that I use php (i.e., as in your code example) instead of using the mixture of html and php that I was wanting to do.

You can't do it that way. It wont' work.

You must use PHP to output the values you want.
PHP is executed, HTML is not. PHP outputs HTML. HTML is the end result.
In this case, the end result you want is an HTML snippet that represents a select box of all the values in a DB. PHP is very well suited for this.

Try this:


<?php 
include("settings.inc"); 
mysql_select_db($mydb, $connection); 
$sql = "Select ID, ZONE From properties where ID='$dproperty'"; 
$myresult=@mysql_query($sql); 
// Here, you are getting ALL the results.  mysql_fetch_array gets one whole result 
//(one row from the columns you selected) as an array.
// we must actually loop through ALL the rows to get an array of each row.
// we can do this easily enough in a while loop
while($row = mysql_fetch_array($myresult))
{
	//  This is neat stuff.  An empty [] just addes it on to the end of the array.
	// I almost wet myself when I first discovered this.
	// We'll just use this one for fun.
	$Zones[] = $row["ZONE"]; 

// We'll use this one to actually build the select box.
// We could make it one huge string like the earlier example, or a huge array with each element as an option.
// This time we'll use the array method.
$selectBoxZones[] =  "\n<option value=\"" . $row['ZONE'] . "\">" . $row['ZONE'] . "</option>"; 

} 

?> 


<form action="<? $PHP_SELF ?>" method=post> 

<-- the following select was the smallest section of code so I'm using it as an example --> 

<tr><td align=right> 
<span class=s1> 
Zone 
</span> 
</td> 
<td align=left> 
<!-- Make all this go away
<select name="Zone"> 
<option name="Zone">NorthCentral</option> 
<option name="Zone">NorthEast</option> 
<option name="Zone">EastCentral</option> 
<option name="Zone">SouthEast</option> 
<option name="Zone">SouthCentral</option> 
<option name="Zone">SouthWest</option> 
<option name="Zone">WestCentral</option> 
<option name="Zone">NorthWest</option> 
</select> -->
</td> 
</tr><td>
<!-- Now we will build our Select Box -->
<select name="Zone"> 
<?php
// we are going to use a neat control structure called foreach
foreach($selectBoxZones as $option)
{
	echo $option . "\n";
}
?>
</select>
</td>
</form> 

if ($Submit) { 
$sql2 = "UPDATE properties SET ZONE ='$Zone' where ID 
= '$dproperty'; 

$myresult=@mysql_query($sql); 
} 


?>

BLottman

Thanks so much for giving me the conceptual as well as the technical view on this.

I have a lot of coding to do but will test it with the segment we referenced.

Thanks again. I'll post the results here after I'm done.

Regards,

Bill.🙂

BLottman

Cool! The code for displaying the values in the form as a select work great!

Now for the really stupid question. The problem I'm having now is that it's not writing the $option value displayed on the form as a change to the column for the row in the table.

In the code below, the segment on submit, the $option value doesn't seem to be writing its value to the ZONE column. Am I writing the output to the column correctly? It's not updating the table for that specific row:

<?php
include("settings.inc");
mysql_select_db($mydb, $connection);
$sql = "Select ID,ZONE From properties";
/ where ID='$dproperty'/
$myresult=@($sql);
while($row = mysql_fetch_array($myresult))
{
$Zones[] = $row["ZONE"];
$SelectBoxZones[] = "\n<option value\"".$row['ZONE']."\">".$row['ZONE']."</option>";
}
?>

<form action="<? $PHP_SELF ?>" method=post>
<tr><td align=right>
<span class=s1>
Zone
</span>
</td>
<td align=left>
<select name="Zone">
<?php
foreach($SelectBoxZones as $option)
{
echo $option."\n";
}
?>
</select>
</td>
<tr><td colspan=4> </td></tr> <tr><td
colspan=2><br><br><br></td></tr>
<tr><td align=right><INPUT TYPE=hidden NAME="R621"
VALUE="<? echo $R621;?>"></td></tr>
<tr><td align=right><input type="submit" name="submitlist"

value="Submit List"></td><td align=left><input

type=reset></td></tr>
<tr><td colspan=2><br><br><br></td></tr>
</form>
</table>

<?php
if ($submitlist == "Submit List") {
$sql2 = "UPDATE properties SET ZONE='$option' WHERE ID='$dproperty'";
if (@($sql2)) {
echo("<p> Your property has been Updated.</p>");
}else {
echo("<p>Error Updating your property: " . mysql_error() . "</p>");
exit();
}
} //End of submitsearch
?>

Thanks in advance. I've tried several combinations to get it to write but none have worked.

jeremuck

I'm glad you got it to work and seem to understand it.

The second part is actually recieving the form.
The important thing to remember here, is that the variable name is the same as the name= part of the FORM. It has nothign to do with whatever variables we might have used to help us output the form.
If we perform some tests, maybe that will help us out.

Try this, as the last section of your code:

<?php 
// Here's a gift: two utility functions that check for errors in mysql.
// See the last few lines for usage.
function myError($message = "", &$dbLink)
{
	if($dbLink != "")
	{
		echo "MySQL said: " . mysql_error($dbLink) . "<br>";
	}

die(print( $message));



}

function echeckDB($message = "", &$dbLink)
{
	if(mysql_error($dbLink) != "")
	{
		myError($message, &$dbLink);
	}

}




// I'm very very picky about indentation/coding style. 
if ($submitlist == "Submit List") 
{ 
	// Let's look at the values submitted:
	// Because the form uses the POST method, they will all be stored in the $_POST array.
	// So this will output all values in the $_POST array very nicely.
	// Depending on the version of PHP, you may need to change it to $HTTP_POST_VARS
	echo "<pre>";
	print_r($_POST);
	echo "</pre>";
	// As you can see, in our form, the name of the zones variable is Zone, so we will use that below in our SQL.

// I like to be completely unambiguous about mixing strings and variables, so I always separate them.
// I am always making mistakes like missing a " or a ., so double check this.
// Also, I like to have a name that describes the type of statement it is.
// This is a sql statement that performs an update.
$sqlupdate = "UPDATE properties SET ZONE='" . $Zone . "' WHERE ID='" . $dproperty . "'"; 
// [EDIT:] let's see it!
            echo "<br>SQL statement: " . $sqlupdate . "<br>";
// it's good to do error checking, but I get tired of writing code like you have below.
// so I use two utility functions to do it all the time automatically.
/* get rid of this stuff
	if (@mysql_query($sqlupdate)) 
	{ 
		echo("<p> Your property has been Updated.</p>"); 
	}
	else 
	{ 
	echo("<p>Error Updating your property: " . mysql_error() . "</p>"); 
	exit(); 
	} 
	*/

$result =	mysql_query($sqlupdate);
// It's simple you give your own custom error message, and the connection.
// The function will check the errors for you.
// If there is something wrong, it prints your message, mysql's error message, and then die()'
echeckDB("I could not update the DB with the SQL-statment: " . $sqlupdate . "", &$connection);
// Since we checked, it's safe to proceed as if it was successful.
echo "Hurray, Hurray, we updated!<br>";

// you should be able to get the number of rows affected.
echo "That affected this many rows: ";
echo $result . "<br>";
// I'm not sure how to get the number of rows affected.

$row = mysql_fetch_array($result);
foreach($row as $value)
{
	echo $value . "<br>";
}


} //End of submitsearch 
?>

BLottman

Let me see if I can explain the process that I want to occur:

User enter's data in a form to create a row in the table.
Later the user changes information and resaves the changes to the same row in the table.

The first part is easy. The second step seems perplexing even after trying the code.

Let's say I have only five rows in a table named "properties" and the one row that appears has zone="NorthWest" selected because the Where clause limits the number of select option elements to just that row. If I remove the where clause then the result will be the retrieval of Zones from the properties table for all five rows (i.e., Northwest, Southeast, ....) and some of them will be duplicated (i.e., noted below) in the Select Options list because that's just the way previous users stored the options selected in the properties table:

Northwest
Southeast
Southeast
Northcentral
Eastcentral
....
....

I want to be able to list all Zones, each unique, so that the user sees, not only their currently stored value, but then they can select an option from the list and replace their current stored value with one from the list. If no user ever stores a SouthWest value to their row, it won't show in the selectable list, because the values come from the properties table and some of the selectable values may not have been stored.

I created a separate table named "Zones" that stores each of the Zones so that they are all represented and not duplicated. Then when the update form is displayed, the form is built using all of the separate tables for each of the select boxes on the form. The difficulty that I'm having is:

The user's selection doesn't appear because now I'm not pulling the stored values from the properties table but instead I'm using the Zones table.

How can I display the user's current selection AND make available all of the other unique selections in the same select box?

I have other select boxes to place on the form but for now, the only select box on the form is the Zones select box. This helps with determining the best way to code it.

I hope someone understands what I'm trying to achieve.