0

I've had a lot of trouble handling an access DB table that contains the field quantità_gc which is a double field and has an accent on the letter à. Using many examples I managed to set up a php code which at the moment no longer gives me errors, but at the same time it doesn't give me a numeric result but rather a string

array(1) {
["quantity_gc"]=>
string(12) "quantity_gc"
}

I ask you where I'm wrong and if it is possible to find a solution. Thanks in advance

Here is my code

<?php

$dsn = "odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:/database.mdb";
$user = '';
$password = '';

try {
    $pdo = new PDO($dsn, $user, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
} catch (PDOException $e) {
    die("Errore di connessione al database: " . $e->getMessage());
}

if (isset($_GET['variabile'])) {
    $variabile = $_GET['variabile'];

    $stmt = $pdo->prepare("SELECT 'quantità_gc' FROM magazzino WHERE id = :variabile");
    $stmt->bindParam(':variabile', $variabile, PDO::PARAM_STR);
    $stmt->execute();
    $quantità_gc = $stmt->fetchColumn();

    if ($quantità_gc !== false) {
        // Applica l'encoding UTF-8 al valore del campo 'quantità_gc'
        $output = ['quantità_gc' => mb_convert_encoding($quantità_gc, "UTF-8", 'auto')];

        var_dump($output);

        // Restituisci i risultati come JSON
        //echo json_encode($output);

    } else {
        // Risultati non trovati o errore nella query
        echo json_encode(['error' => 'Risultati non trovati o errore nella query']);
    }
}
?>

PS: I already have tried many syntax for this query,

SELECT '[quantità_gc]' FROM magazzino WHERE id = :variabile

or

SELECT [quantità_gc] FROM magazzino WHERE id = :variabile

all gives errors

user3783243
  • 5,368
  • 5
  • 22
  • 41
Jayelef
  • 135
  • 12
  • 1
    *"I ask you where I'm wrong"* Don't use accented letters in field and table names. You're looking for trouble. – Olivier Jul 30 '23 at 10:36
  • It's true, I can only agree with you, but the database had already been created, it contains a lot of data and a software that has been using it for years. I can't think of changing the name of several fields which unfortunately are accented. – Jayelef Jul 30 '23 at 10:40
  • `"SELECT [quantità_gc] FROM magazzino WHERE id = :variabile"` should work. Check the final SQL you create (`$stmt`). – Gustav Jul 30 '23 at 10:49
  • Thanks, unfortunately I went through many tests. Here your suggest result Fatal error: Uncaught PDOException: SQLSTATE[07002]: COUNT field incorrect: -3010 [Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 2. (SQLExecute[-3010] at ext\pdo_odbc\odbc_stmt.c:260) in – Jayelef Jul 30 '23 at 10:57
  • 1
    Does `SELECT * FROM` give the same error? Error sounds like parameters dont match placeholders. Error message also should be in the question – user3783243 Jul 30 '23 at 11:57
  • there are many fields in this database, and in no case do they return an error except when using the field that has the accented letter – Jayelef Jul 30 '23 at 14:14
  • Try encoding the query in windows-1252 instead of UTF-8. – Olivier Jul 30 '23 at 14:27
  • @Olivier could you give me an example? I see that the encoding change has other parameters. – Jayelef Jul 30 '23 at 14:41
  • `$query = mb_convert_encoding($query, 'windows-1252', 'UTF-8');` – Olivier Jul 30 '23 at 14:48
  • Thanks, but I think that it isn't risolutive. I'm thinking about to change the fields name in DB. (I'm crying) – Jayelef Jul 30 '23 at 15:11
  • You could also try the ADODB approach described at the end of [this answer](https://stackoverflow.com/a/28341697/12763954). – Olivier Jul 30 '23 at 15:26
  • 1
    In the Access database, create a query based on the table (just `SELECT [quantità_gc] AS YourField FROM magazzino`), and use this query in your PHP code. – Applecore Jul 30 '23 at 15:34
  • @Applecore ok if I understood correctly, I should save the query in the db and use it as a ready-made external query, but I don't know how to call it from the php file, which in this case must be modified – Jayelef Jul 30 '23 at 17:33
  • Not a PHP guy, but shouldn't replacing `$stmt = $pdo->prepare("SELECT 'quantità_gc' FROM magazzino WHERE id = :variabile");` with `$stmt = $pdo->prepare("SELECT YourField FROM YourQuery WHERE id = :variabile");` work? – Applecore Jul 30 '23 at 18:35

0 Answers0