CSV Upload Station

CSV Upload Station Page

Commentary and Instructions...

Intro

This "app" isn't designed to be a standalone application in and of itself, as much as it's visualized as a "layer" of code that can be implemented into an already existing tool.

Given the inconvenience caused by renegade naming conventions, a helpful tool is reduced to a frustrating experience - all as a result of something as incremental as column headings that don't match the field names of the target database the CSV file in question is being uploaded to.

This tools seeks to remedy that problem by inviting the user to take a moment and "match" their column headings with the field names of the databased they're preparing to interact with. Being able to save their preferences gives them a sense of ownership and customization while ensuring that the application they're getting ready to use is now far more likely to function correctly.

A Couple of Assumptions...

A couple of assumptions are being made from the start, as far as how this tool is designed:

The application the user is getting ready to interact with necessitates the uploading of a CSV file
The data within that CSV file is being parsed into an application of some kind that will then make several calculations based on that data
The table within that application sets the tone for the incoming CSV file, as far as naming conventions

A List of Tools...

A list of tools is given to the user when they first enter the interface. Those tools are listed in the admin suite and are entered into the administrative database based on the table that the hypothetical CSV file is being uploaded to.

In the image you see to the right, the "twitter" tool is listed with both a "table" and a "secondary table." This may or may not be the issue for every situation, but in this instance, while the "verizon" table defines the expected naming conventions, it's the "twitter_test" table that the data from the incoming CSV file is being uploaded to. Not every tool has to be listed with a secondary table, but should it prove to be logical to catalog a tool with a secondary table, that option exists.

The Real Magic

The id and the session_id...

Once the user has selected the tool they want to use and they've uploaded their CSV file, the code grabs the column headings of their CSV file and displays each field alongside a pulldown that lists every field belonging to the target table of the tool (either the "primary" or the "secondary" table as documented in the administrative suite).

There are two fields that are assumed to be present in either your primary or secondary table: id and session_id. The id is usually a given, but the session_id may or may not be present and that's important. This interface is designed to delete all of the uploaded data from the target table once the user's session is completed. In order to do that and not interfere with other user's data, a session id is needful. So, be certain that your target table has a session_id field as the last field.

The fields that are displayed in the pulldown include every field, save two: the id and the session_id (see callout to the left). The code looks like this:

First of all, here's the code that grabs the column names from the table the CSV file is getting ready to be uploaded to...

class TableData {
function TableInfo($table_name)
{
global $mysqli;
$sql = "show columns from $table_name";
$query = $mysqli->query($sql);
if(!$query)
{
$error = $mysqli->errno.': '.$mysqli->error;
trigger_error($error, E_USER_WARNING);
}
$data_count=mysqli_num_rows($query);
if($data_count==0)
{
trigger_error("you don't have any column names", E_USER_WARNING);
}
while($show_columns=$query->fetch_object())
{
$the_columns[]=$show_columns->Field;
}
return $the_columns;
}
}

Now, here's the code that is displaying those column names in the context of the pulldown menu. Notice line 5. This is signficant because it's the first and last fields in the target table that are omitted so the user isn't distracted by the table id field or the session_id field.

$table_stuff=new TableData;
$table_handle=$table_stuff->TableName();
$columns = $table_stuff->TableInfo($table_handle);
$column_count=count($columns);
unset($columns[$column_count-1], $columns[0]); //this prevent the session_id field from being shown
//echo $column_count;
foreach ($the_header as $value)
{
$body .='
<tr><td class="csv_sample_cell"><input type="text" size="35" value="'.$value.'" name="column_'.$the_count.'"></td>
<td class="csv_sample_cell">
<select name="select_name_'.$the_count.'">
<option></option>';
foreach($columns as $row)
{
$body.='<option>'.$row.'</option>';
};
$body .='</select>
</td>
</tr>';
$the_count=$the_count+1;
}
$real_count=$the_count-1;
$body.='
</table><br><br>
<div class="conclusion"><div style="float:right; padding-top:5px;"><input type="text" size="35"
name="email_preference"></div>If you want to save your preferences so the system will remember your naming conventions
the next time you use this tool, enter your email address in the field to the right.</div><br><br>
<div style="text-align:center;"><input type="hidden" name="csv_name" value="'.$the_csv.'">
<input type="hidden" name="tool_id" value="'.$_POST['tool_id'].'">
<input type="hidden" name="column_count" value="'.$real_count.'">
<input type="hidden" name="number_columns" value="'.$destination_count.'">
<input type="image" src="images/submit_button.jpg" alt="Submit" width="100">
</form>';

Displaying the User's Preferences

The only other pieces of code that are worth mentioning would be the syntax that saves the resulting insert statement a user crafts as a result of the fields they match up with their column headings. This is generated if a user determines that they want to save their preferences and is located on the lines 325-394 on the csvClass.php page which you can view by clicking here.

The other piece of code that's somewhat signficant is the syntax that retrieves the insert statement a user saves as part of their preferences and "re-shapes" it into values that can be displayed as "selected" options in the pulldown menu that lists all of the possible fields in the target table. It's this feature that allows the user to immediately upload their CSV file rather than having to go through each option in the pulldown menu and match things up accordingly.

To get that done, first, you grab the insert statement as it currently exists in the database as a user's preference, eliminate everything from that statement save the first part of the insert syntax that references the target table's fields and convert the resulting string into an array. That happens at line 26-29.

function table_fields($tool_id) {
$statement="";
$stage_one="";
$stage_two="";
$stage_three="";
$array="";
global $mysqli;
$sql="select * from preferences where email ='$_SESSION[email]' and tool_id='$tool_id'";
if(!$query=$mysqli->query($sql))
{
$err = "your login function didn't work because of...";
$err .=$mysqli->errno.': '.$mysqli->error;
trigger_error($err, E_USER_WARNING);
}
$count=mysqli_num_rows($query);
if($count>0)
{
$row=$query->fetch_object();
$statement=$row->tool_fields;
//here's where I'm breaking the insert statement down into a manageable array
$stage_one = strstr($statement, ')', true); //shows everything to the left of the second parenthesis
$stage_two=strstr($stage_one, '('); //gets rid of the "insert into table_name" verbiage
$stage_three = ltrim($stage_two, "("); //trims the "(" from the left end of the string
$array = explode(',', $stage_three); //breaks up the remaining string into an array based on the comma between each value
return $array;
}
}

Once you have that array, now you just reference each element of that array according the number of the fields (count) of the CSV file being uploaded (see line 10). The fact that there isn't a session id in the CSV file eliminates the need for having to accommodate that dynamic, despite the fact that is documented in the saved insert statement. The code looks like this:

$statement=new User;
$statement_array=$statement->table_fields($_POST['tool_id']);
foreach ($the_header as $value)
{
$body .='
<tr><td class="csv_sample_cell"><input type="text" size="35" value="'.$value.'" name="column_'.$the_count.'"></td>
<td class="csv_sample_cell">
<select name="select_name_'.$the_count.'">
<option selected>'.$statement_array[$the_count].'</option>';
foreach($columns as $row)
{
if($row=="session_id")
{
continue;
}
else
{
$body.='<option>'.$row.'</option>';
}
};
$body .='</select>
</td>
</tr>';

Those would be the highights of this tool, as far as the need to have a session_id as the last column of your target table and also the whereabouts and substance of some of the code that was used.