$last )
{
$last = $mt;
$csv_name = $dir.$file;
}
}
}
}

closedir( $dh );
echo $csv_name . ‘
‘;
if ( empty( $csv_name ) )
{
echo ‘Failed to find csv file.’;
}
else
{
// Database Constants
define( ‘SERVER’, ‘mysql516int.manage.myhosting.com’ );
define( ‘USER’ , ‘u1146493_kand’ );
define( ‘NAME’ , ‘db1146493_kandpengineering’ );
define( ‘PASS’, ‘AT4qsqhDmvA2Z19u’ );

$conn = new mysqli( SERVER, USER, PASS, NAME );
// Drop previous table.
$sql = “DROP TABLE IF EXISTS searches;”;
if ( !$conn->query( $sql ) )
{
echo ‘Drop table failed: ‘ . $conn->errno . ‘ => ‘ . $conn->error;
exit;
}
//else
// echo ‘Table Dropped.’;

// Open csv file
$file = fopen( $csv_name, ‘r’ );

// Read in first row for column definitions
$data = fgetcsv( $file, 1000,’,’ );
$sql = “CREATE TABLE searches (“;
// Define columns
$cols = ”;
$skip_columns = array();
$i = 0;
foreach ( $data as $col )
{
// Skip empty columns
if ( empty( $col ) )
{
array_push( $skip_columns, $i );
continue;
}

// Remove invalid chars from the col names
$name = str_replace( ‘&’, ‘__’, $col );
$name = str_replace( ‘ ‘, ‘_’, $name );
$name = str_replace( ‘/’, ‘slash’, $name );
$name = str_replace( ‘#’, ‘pound’, $name );
$sql .= mysql_real_escape_string($name) . ‘ VARCHAR(32),’;
$i++;
}

$num_cols = $i – 1;
// Remove the last comma from the above loop.
$sql = substr($sql, 0, -1);

$sql .= “) ENGINE=MyISAM;”;

// Create the table
if ( !$conn->query( $sql ) )
{
echo ‘Create table failed: ‘ . $conn->errno . ‘ => ‘ . $conn->error;
echo ‘

Create Table Command:
‘ . $sql;
fclose( $file );
exit;
}
//else
// echo ‘Table Created.’;

// Load up the table!!
$sql = “INSERT INTO searches VALUES “;
//var_dump($skip_columns);
$i = 1;
while ( ( $data = fgetcsv($file, 1000, ‘,’ ) ) !== FALSE )
{
$sql .= ‘(‘;
$j = 0;
foreach ( $data as $field )
{
if ( in_array( $j, $skip_columns ) )
continue;

$name = “”;
if ( empty( $field ) )
$sql .= “”,”;
else
$sql .= “‘” . mysql_real_escape_string( $field ) . “‘,”;
$j++;
}

for ($j; $j < $num_cols + 1; $j++) $sql .= "'',"; $sql = substr( $sql, 0, -1 ); $sql .= '),'; $i++; } // Remove the last comma. $sql = substr( $sql, 0, -1 ); // Close the CSV file. fclose( $file ); if ( !$conn->query( $sql ) )
{
echo $sql;
echo ‘Table load failed: ‘ . $conn->errno . ‘ => ‘ . $conn->error;
exit;
}
else
{
echo ‘The search table has been updated. The original csv file has been removed.’;

// Delete the csv file from the uploads folder.
unlink( $csv_name );
}
}
?>