Reynard submitted the following through the Visual Studios plug-in. He writes that he was charged with making the Customer Values page go faster. Depending on the client, there could be hundreds or thousands of Values that can be attached to a user (Client). The user manages them through a standard checkbox-laden page.

He quickly came to learn that you'd only be able to pry CSV from the lead developer's cold, dead code.

He tries to walk us through it.

"The user's values are cached in the session in a single CSV value. The text description of the values is saved. Not the IDs. The CSV are split into a list for data manipulation."

// Comma seperated list of values current user has selected, plus their Free Text value. Split into a list.

$curItemArr = split($_SESSION["CustomerValues"], ",");

// The text description (but not ID) of possible values, from the database
$oRecSet = DB.Execute("SELECT ValueName FROM Values WHERE ValueType='Customer' ORDER BY ValueName ");

$curItemSQLNoExt = "";

"After pulling up the entire data table from the DB, the system loops through each record, then inner loops through all the user's selected values to find matches. It puts them into, of course, a CSV."

// For each value in the session
while (! $oRecSet.eof)
{

// For each database item (backwards)

for ($i = $curItemArr.length; $i >= 0; $i++)
{

// If the current split item equals the current database item
if ($curItemArr($i) = $oRecSet["ValueName"])
{

// put it into a comma seperated array for SQL
// Be sure to escape, because we wouldn't want this code to suck or anything
$curItemSQLNoExt += "'" + replace($curItemArr[x], "'", "''") & "',"
} // end if

} // next i

$oRecSet.movenext();

// next database item

} //endwhile

"As mentioned, only text values are in session, not IDs. So the system goes back and selects all the IDs for values the user has. It stores those IDs in a CSV"

// at least they didn't just re-execute the sql.
$oRecSet.movefrist();

idStr = "";

// this one was-- umm-- if the session contained a language that was the same language as the databse
if ($curItemSQLNoExt <> "")
{
// Get the ID of the languages that the user has in their session.
sSQL = "select ValueID from Values where ValueType='Customer' AND ValueName in (" + mid($curItemSQLNoExt, 1, len($curItemSQLNoExt)-1) + ") "

$oRecSet2 = DB.Execute(sSQL);



while (! $oRecSet2.eof)
{
// Back into a csv list!
idStr = idStr & $oRecSet2("ValueID") + " "
$oRecSet2.movenext();
}

// TOO MANY SPACES!
if (idStr <> "")
{

idStr = mid(idStr, 1,len(idStr) - 1);

}
}

"The CSV is split back into an array for obvious reasons below."

// Split it back into an array.
if (idStr <> "")
{
idArr = idStr.split(" ");
}

"Finally,checkboxes are made using the CSV."

// Finally, UI!
while (! $oRecSet.EOF)
{
if (instr(1, idStr, trim($oRecSet["ValueID"].value)))
{
Response.Write( "<input type='checkbox' name='UserValue' value='" + $oRecSet["ValueName"] + "' checked />" + $$oRecSet["ValueName"]);

}

else

{

Response.Write("<input type='checkbox' name='UserValue' value='" + $oRecSet["ValueID"] + "' />" + $$oRecSet["ValueName"]);

}

$oRecSet.movenext();

}

// And textbox
if (FreeTextValue = "")
{
Response.Write("<textarea name='FreeText'>" + FreeTextValue + "</textarea>");
}

Reynard thought about educating the lead programmer on normalized data. He was able to replace all the CSV with a bit of XML, and all was right in the world.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!