Function to pull MySQL enum fields for insert into HTML form

[tycoonbob]

Hi everyone.  I have another question, which will hopefully be the last piece to get my application going.  I have a MySQL table with an enum field, with a few values (Value1, Value2, Value3, Value4).  I have a HTML form that is pulling over fields from the same table, but those other fields are varchar fields.  I'm wanting to create a drop down box which is dynamically populated with those enum values, and that defaults to the currently selected field.

 

I have found a few examples of this, but they all seem to be deprecated mysql_* code, whereas I'm using mysqli_* throughout.  I'm fairly new to PHP, and I have never written a function before.  I figure something like this would be out there somewhere, but I haven't been able to find an example here at PHPFreaks, nor on various other forums.  Here are some examples of what I have found using mysql_*:

http://www.barattalo.it/2010/01/19/php-to-get-enum-set-values-from-mysql-field/

http://stackoverflow.com/questions/3715864/displaying-mysql-enum-values-in-php

http://www.larryullman.com/forums/index.php?/topic/916-use-data-type-enum-for-form-drop-down-options/

http://www.pcserviceselectronics.co.uk/php-tips/enum.php

 

I just don't know where to start with creating this function.  I need to use this 3 times, or 2 different fields, which is why I assumed a function would be the best way to go.

 

Thanks for any help!

[Ch0cu3r]

You just need to replace mysql_* with mysqli_ and pass in the mysqli object where needed.

 

The function posted in the first link I have updated to mysqli

function set_and_enum_values( &$conn, $table , $field )
{
    $query = "SHOW COLUMNS FROM `$table` LIKE '$field'";
    $result = mysqli_query( $conn, $query ) or die( 'Error getting Enum/Set field ' . mysqli_error() );
    $row = mysqli_fetch_row($result);

    if(stripos($row[1], 'enum') !== false || stripos($row[1], 'set') !== false)
    {
        $values = str_ireplace(array('enum(', 'set('), '', trim($row[1], ')'));
        $values = explode(',', $values);
        $values = array_map(function($str) { return trim($str, '\'"'); }, $values);
    }

    return $values;
}

$fields = set_and_enum_values($mysqli, 'table', 'field');

printf('<pre>%s</pre>', print_r($fields, true));

[tycoonbob]

Alright, so I added that to my function.inc.php file, but on each page I now get "Error getting Enum/Set field", while no other data is loading and without calling that function on the page.  

 

index.php has:

require_once('includes/functions.inc.php')

 

but this function won't be used on that page.

 

Also, I'm confused by the variables in the funciton.  Is the '&' intentional?  I have a config.inc.php file that has my db connection stored in $con, so should I replace $conn with $con?

[Ch0cu3r]

The code to call the function is this (make sure you have not put it in function.inc.php)

$fields = set_and_enum_values($mysqli, 'table', 'field');

Replace $mysqli with your mysqli object.

Replace table with your table name

Replace field with the name of the field you want to get the enum/set values from.

 

 

Also, I'm confused by the variables in the funciton.  Is the '&' intentional?

Yes, because the mysqli object is being passed by reference

[tycoonbob]

The code to call the function is this (make sure you have not put it in function.inc.php)

$fields = set_and_enum_values($mysqli, 'table', 'field');

Replace $mysqli with your mysqli object.

Replace table with your table name

Replace field with the name of the field you want to get the enum/set values from.

 

Yes, because the mysqli object is being passed by reference

I really appreciate your help.  Unfortunately, I'm still not doing something right.  Here is what I have in my table:

<tr>
<td class="headings"><b>Owner:</b></td>
<td><select name="owner"><option value="<?php $fields = set_and_enum_values($con, 'inventory', 'owner'); ?>" /></select></td>
</tr>

I get a drop down list, but it's empty.  Here is config.inc.php, which is where $con is set:

<?php
$con=mysqli_connect("server","username","password","db_name");
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  ?>

[Ch0cu3r]

You need to loop over $fields to generate the <options>

<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
?>
    <option><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>

[tycoonbob]

 

You need to loop over $fields to generate the <options>

<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
?>
    <option><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>

Thanks for explaining, and it now makes sense.  One final question on this topic; This works, however, where I'm using this there is already data in selected in this field (when editing).  How can I set the SELECTED parameter to what the field currently has?

[Ch0cu3r]

Maybe try

<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
     $selected = (isset($_POST['owner']) && $_POST['owner'] == $option) ? ' selected="selected" : '';
?>
    <option<?php echo $selected; ?>><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>

[tycoonbob]

 

Maybe try

<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
     $selected = (isset($_POST['owner']) && $_POST['owner'] == $option) ? ' selected="selected" : '';
?>
    <option<?php echo $selected; ?>><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>

 

I believe there is a syntax error, but I'm not sure where it is.  I know there is a missing '>' in '<option>', which I fixed, but I think the single quotes are off balanced in Line 5?  With the code as is, and with the '>' added on <option>, it just loads a white page -- which I believe is considered a fatal error.

 

EDIT: Also, I think $_POST might not be the right thing I need either.  Earlier in this script I have this:

$inven_id = $_GET['id'];
$query = "SELECT
            * 
          FROM 
            inventory 
          INNER JOIN 
            products 
          ON 
            inventory.sku=products.sku 
          WHERE 
            inventory.id = '$inven_id'"; 


$result = mysqli_query($con,$query) or die(mysqli_error($con));
$row = mysqli_fetch_array($result);

And my other table fields are getting the default value by using:

<?php echo $row['owner']; ?>

For example.

Edited December 31, 2013 by tycoonbob

[Ch0cu3r]

Oops. yea I left a single quote off

<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
     $selected = (isset($_POST['owner']) && $_POST['owner'] == $option) ? ' selected="selected"' : '';
//                                                                                             ^ I left this quote off
?>
    <option<?php echo $selected; ?>><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>

Edited December 31, 2013 by Ch0cu3r

[tycoonbob]

<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
     $selected = (isset($_POST['owner']) && $_POST['owner'] == $option) ? ' selected="selected"' : '';
?>
    <option><?php echo $selected; ?><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>

Didn't seem to work.  It didn't break anything, and the drop downs are still working, but they are not defaulting to what data is already in that field.

 

I know I've seen a mysql_* example today that had that ability; going to see if I can find it.  It's not one of the 4 I listed in the OP.

[tycoonbob]

Oh wait, this is doing something:

<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
     $selected = (isset($row['owner']) && $row['owner'] == $option) ? ' selected="selected"' : '';
?>
    <option><?php echo $selected; ?><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>

 

The value that is selected in the database is showing up as:
select="selected"PersonsName

 

So it's able to mark which one is which.  I'm guessing the HTML didn't execute properly?

Edited December 31, 2013 by tycoonbob

[Ch0cu3r]

You have changed the formatting of the option tag (maybe you was thinking it was wrong) it should be like this

 

<option<?php echo $selected; ?>><?php echo $option ?></option>

 

The text in green is PHP code. The brown text is HTML code. There is nothing syntactically wrong with this code.

Edited December 31, 2013 by Ch0cu3r

[tycoonbob]

Ha, I feel like such a newbie.  My text editor was telling me there was a syntax error, so I did move the > for option.  Moving it back, and using the code you originally provided, and everything works like expected!

 

I can't thank you enough!

[tycoonbob]

This is my final code that worked.  The only modification from yours above is replacing the `$_POST` with `$row`, since I am extracting that data via SQL query instead of POST.

<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
     $selected = (isset($row['owner']) && $row['owner'] == $option) ? ' selected="selected"' : '';
?>
    <option<?php echo $selected; ?>><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>

 

 

Thanks again!