Update Parts List Search

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

closedir( $dh );

if ( empty( $csv_name ) )
{
echo ‘Failed to find csv file.’;
}
else
{
// Database Constants
define( ‘SERVER’, ‘10.16.112.122’ );
define( ‘USER’ , ‘u1146493_sa40092’ );
define( ‘NAME’ , ‘db1146493_sa40092_main’ );
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++;
}

// 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);
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++;
}
$sql = substr( $sql, 0, -1 );
$sql .= ‘),’;
}

// Remove the last comma.
$sql = substr( $sql, 0, -1 );

// Close the CSV file.
fclose( $file );

if ( !$conn->query( $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.
chmod( $csv_name, 0777 );
unlink( $csv_name );
}
}
?>

Comments are closed.