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]
Make your team a DevOps team with BuildMaster. Pairing an easy-to-use web UI with a free base platform, BuildMaster gets you started in minutes. See how
Allrecipes.com and others use BuildMaster to automate their software delivery.