[Resolved] PHP & MySQL

[xhat]

Hello all, and thanks in advance to any who respond. Perhaps a thank you is in order for those who don't respond, as well.

I was conflicted about which forum to use: Newbies or Database, since my question concerns an enumerated field in a MySQL database being displayed as a drop-down list box on my PHP page.

I opted for newbie because there is much I do not know. Ok, on to the problem:

One member of my enumerated set reads thusly: 'Tools, Equipment and Shop Items List'. When I use a for loop to populate the list box (it works fine, almost), <Tools> and <Equipment and Shop Items List> show up as separate, distinct items in the list. Clearly, this is not what I intended.

The [FONT=arial]desc table_name[/FONT] query in mySQL appears to show the set properly. So my question is, do I have to handle this issue on the mySQL side and somehow escape the offending comma, or should it be handled on the PHP side? If a mySQL problem, how do I escape the comma delimiter. If a PHP problem, any suggestions would be appreciated. Below is my code to populate the list box:

<?php
for($i=0; $i < count($ad_section_array); $i++) {
echo "<option value = \" ".
htmlspecialchars($ad_section_array[$i]) .
" \">" . $ad_section_array[$i] . "</option>\n";
}
?>

thanks again for any table scraps of wisdom

[jayant]

do it on php side

use explode

[xhat]

Jayant,

Thanks for the reply. I'm not sure I understand. Isn't [FONT=times new roman]mysql_query[/FONT] the culprit that is populating my array with the key "Tools" and "Equipment and Shop Item List?" I can only imagine that [FONT=times new roman]mysql_query[/FONT] must break on commas, which is why it is chopping up my set. That's the function responsible for getting the values and dropping them into my $result. So, are you saying to wrap [FONT=times new roman]mysql_query[/FONT] inside [FONT=times new roman]explode()[/FONT] ? If no, I have to figure out how to mash two array keys together to make one line. True?

[jayant]

give the exact SQL query being used and the exact records as in the db.

[xhat]

mySQL output of [FONT=times new roman]show columns from advertisement like 'adSection';[/FONT]

| adSection | enum('1961 Tempest Parts List','1962 Tempest/Lemans Parts List','1963 Tempest/Lemans Parts List','Pontiac (Non 61-63 Tempest/Lemans) Parts List','Non-Pontiace Car-Related Parts List','Manuals and Literature List','Memorabilia and Collectibles List','Tools, Equipment and Shop Items List','Members "Stuff for Sale" List') | | | Members "Stuff for Sale" List | |

Italics & bold added by me.

Code for the PHP fetch of the enum set:

function enum_options($table, $field) {
//Puts the values of enumerated fields in the specified tables into an array for later use.

global $default_dbname;

$link_id = db_connect($default_dbname);
$query = "SHOW COLUMNS FROM $table LIKE '$field'";
$result = mysql_query($query, $link_id);
$query_data = mysql_fetch_array($result);

if(eregi("('.*')", $query_data["Type"], $match)) {
	$enum_str = ereg_replace("'", "", $match[1]);
	$enum_options = explode(',', $enum_str);
	return $enum_options;
	}
else return 0;

}

Code for the list box population:

Call to enum_options...
$ad_section_array = enum_options('advertisement','adSection');

then...

<?php
for($i=0; $i < count($ad_section_array); $i++) {
echo "<option value = \" ".
htmlspecialchars($ad_section_array[$i]) .

" \">" . $ad_section_array[$i] . "</option>\n";
}
?>

The final code produces a list box on the page that looks like this:
...select options previous
Tools
Equipment and Shop Items List
more select options...

That's all there is. Thanks for the help.

[xhat]

I've figured out the problem, but not a solution.

In function enum_option, I used explode( ',', $enum_string);

That's causing the break after Tools.

Working on a solution to handle that.

thanks

[xhat]

Jayant,

Thank you for your help. I solved the problem. Probably not an efficient or elegant manner, but here's the new code:

if(eregi("('.*')", $query_data["Type"], $match)) {
//Replace single quote ( ' ) with YY.
$enum_str = ereg_replace("'", "YY", $match[1]);
//Replace commas with XX.
$enum_str = ereg_replace("YY,YY", "XX", $enum_str);
//Lop off the two YY's on the end of the string not covered by preceding line of code.
$enum_str = ereg_replace("YY", "", $enum_str);
//explode on the XX.
$enum_options = explode('XX', $enum_str);
return $enum_options;
}

I had to figure a way to leave the "," intact after Tools while still exploding the string.

Again, thanks for the good lead that got me here

[jayant]

how about using

explode("','", $enum_str);
instead of
explode(',', $enum_str);

this will cause to explode on (',') instead of (,)

in first element of array, remove the first characrter (will be ')
and in last element of array, remove last character (will be ')

simple 🙂

btw always mark the problem as resolved once the solution is reached.