NarKampenTake2/ReadQuestionsToDbFromCsvFile.php

<?php

$HOST = 'nordh.xyz';
$USERNAME = 'narKampen';
$PASSWORD = '9Bq.6[AcTc2ADwN-';
$DATABASE = 'narKampen';
$PORT = '3306';

if (!isset($argv[1]) || !str_contains($argv[1], '.csv')) {
    echo 'Needs to supply a .csv with questions and answers';
    die();
} else {
    $file = $argv[1];
    $category = substr($argv[1],strripos($argv[1], DIRECTORY_SEPARATOR)+1,-4);
    $category = str_replace("_", " ", $category);
}

try {
    $dbh = new PDO(
        'mysql:host=' . $HOST . ';dbname=' . $DATABASE,
        $USERNAME,
        $PASSWORD
    );
} catch (PDOException $e) {
    echo '<h1>An error har occured. </h1><pre>', $e->getMessage(), '</pre>';
    die();
}


$sth = $dbh->query("SELECT id FROM Category WHERE name = '$category'");
$sth->setFetchMode(PDO::FETCH_ASSOC);

$result = $sth->fetchAll();

if (count($result) > 0) {
    foreach ($result as $r) {
        echo "Category Id: " . $r['id'] . "\n";
        $categoryId = $r['id'];
    }
} else {
// Insert new category??
    echo "Category not found $category" . PHP_EOL;
    die();  
}

$csv = fopen($file,'r');

while (!feof($csv)) {
    $csvArray[] = fgetcsv($csv,1000,',');
}

fclose($csv);

foreach ($csvArray as $value) {
    $data = ['question' => $value[1],
            'answer' => $value[0]];
    $sth = $dbh->prepare('INSERT INTO Questions (question, answer) VALUES (:question, :answer)');
    try {

        $sth->execute($data);
        $questionId = $dbh->lastInsertId();

        $sth2 = $dbh->prepare('INSERT INTO QuestionToCategory (questionId, categoryId) VALUES (:questionId, :categoryId)');
        $questionLinkData = ['questionId' => $questionId, 'categoryId' => $categoryId];
        $sth2->execute($questionLinkData);

    } catch (Exception $e) {
        echo '<h1>An error has ocurred.</h1><pre>', $e->getMessage(), '</pre>';
    }
}

?>