First, one note regarding style/conventions: the variable you call $aSQL is referred to in documentation [ http://www.php.net/manual/en/function.mysql-query.php ] as the "result identifier" or "result resource." In addition, "columns" are usually referred to as "fields," because you never actually deal with entire columns, only with rows ("records"), split into individual fields.
So you might want to rename the variables $aResult and $aSQL so that they are consistent with that convention, just to save yourself some name-juggling when you're looking at documentation down the road:
$aResult -> $aOutput $aSQL -> $aResult $column_name -> $field_name
In addition, in the line which reads:
while ($this_column = mysql_fetch_array ($aSQL))
We're not actually fetching columns here, we're fetching rows. So I'd change $this_column to $this_row.
Now on to the code.
This is what I think you're trying to do with this function, in English:
- Get all distinct values from one field ("column") in a table
- Create a popup menu containing all these values as options
- If there was previously a value selected in the popup menu for this field, reselect it
In the following I'll assume this to be a correct assessment of your goal.
Firstly, you don't need to use separate variables to use in the if() comparison and the printing of the <option>s:
$aMother = $this_column[0];
$aID = $this_column[0];
You're assigning the same value to two different variables, when you can (and should) only use one, as in the following: (Note that I've also replaced $this_column with $this_row, and renamed other variables, as mentioned earlier.)
<?
while ($this_row = mysql_fetch_array ($aResult))
{
$aValue = $this_row[0];
if ($aValue == $aCurSel)
{
$aOutput .= "<option value=\"$aValue\" selected>$aValue</option>";
}
else
{
$aOutput .= "<option value=\"$aValue\">$aValue</option>";
}
}
return $aOutput;
?>This is exactly equivalent to your code, but is more concise: generally, the more variables you introduce unnecessarily, the more potential you have for introducing bugs.
Now, to explain how the whole thing works, I'll use some example values. Let's call the function once, passing the values "Foo", "Bar", and "ccc".
<?= GetGenOpts("Foo","Bar","ccc"); ?>
Stepping through the function now, assuming values from the this function call:
<?
function GetGenOpts ($aTableName, $field_name, $aCurSel = "")
{
// $aTableName will be "Foo"
// $field_name will be "Bar"
// $aCurSel will be "ccc"/* Connecting, selecting database */
$link = mysql_connect("localhost", "root", "jewcrew") or die("Could not connect");
print "Connected successfully";
mysql_select_db("oysterqtl", $link) or die("Could not select database");
/* Performing SQL query */
$aOutput = "";
// Text of actual query passed to MySQL:
// "select distinct Bar from Foo"
$aResult = mysql_query("select distinct $field_name from $aTableName", $link);
?>
Let's stop here and test this query, to see what results it returns. To do this, we'll issue the same query at the mysql command shell. It should look something like this (first line is a unix shell command, run on the server itself):
------
$ mysql -h localhost -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 to server version: 3.23.41
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use oysterqtl Database changed mysql> select distinct Bar from Foo; +-----+ | Bar | +-----+ | aaa | | bbb | | ccc | | ddd | +-----+ 4 rows in set (0.11 sec)
mysql> exit
------
The result we get has only one field ("Bar"), which is what we expected, and has as many rows (or "records") as there are distinct values for that field.
So if we make a single call to mysql_fetch_array(), it should return an array that, if we constructed an identical one manually, would look like this:
<? $row = array( 0 => "aaa", // Numerical index "Bar" => "aaa" ); // Associative index ?>
Note that I included both the numerical index and the associative index because mysql_fetch_array() returns an associative array with BOTH. Likewise, if we used mysql_fetch_row(), it would return an array with ONLY numerical indices, and if we used mysql_fetch_assoc(), it would return an array with ONLY associative indices. Also notice that the associative index is equal to the name of the field ("Bar").
Continuing on, if we step through the first iteration of the while() loop:
<?
while ($this_row = mysql_fetch_array ($aResult))
{
// $this_row is now an associative array equivalent to:
// $this_row = array( 0 => "aaa", "Bar" => "aaa");$aValue = $this_row[0]; // $aValue now contains "aaa"
if ($aValue == $aCurSel)
// Will test false. $aValue (which contains "aaa") != $aCurSel (which contains "ccc")
// Therefore, will skip to else
{
$aOutput .= "<option value=\"$aValue\" selected>$aValue</option>\n";
}
else
{
$aOutput .= "<option value=\"$aValue\">$aValue</option>\n";
// $aOutput now contains:
// '<option value="aaa">aaa</option>\n'
}
}
?>
In subsequent iterations, $aValue will equal to "bbb", "ccc", "ddd", and then the loop will end. The second and fourth iterations will be exactly the same as the first, but in the third, since $aCurSel == ccc, and $aValue == ccc, the if() will test true, and $aOutput will get the selected <option> added to it.
And finally, after the while() loop is done, we return $aOutput and end the function:
<? return $aOutput; } ?>
The actual HTML returned to the client from the function call:
<?= GetGenOpts("Foo","Bar","ccc"); ?>
...would be:
<option value="aaa">aaa</option> <option value="bbb">bbb</option> <option value="ccc" selected>ccc</option> <option value="ddd">ddd</option>
Hope I've helped clear some things up for you! If you have any further questions, please don't hesitate to ask! :-)
Cheers,
Morgan
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
