phpmysqliechomariadbcolumn-defaults

how to echo a retrieved default value from a database column


From a database I can retrieve a column's default value through the query below. That is, I can from phpMyAdmin and dbForge Studio. By means of mysqli one might, but I simply can't get the value (TINYINT) echoed.
Need it to fill (both correctable and hidden) form input boxes with a default value. As a value, $btwl stays empty; the same code used on a single record in that column works. So, I expect there is another type of fetching needed to echo that just one default cypher, but I can't find any documentation how to. Help is appreciated.

    require ("dbconn.inc.php");
    //this should be the desired output retrieved from information_schema
    $Qdefault = "SELECT column_default
          FROM information_schema.columns
          WHERE table_name = 'keuken'
          AND column_name = 'BTW'
          LIMIT 1";
    $resultdefault = $mysqli->query($Qdefault);

      while ($row= $resultdefault->fetch_assoc()){
      $btwl= $row['BTW'];

      echo $row['BTW']. $row['Default']. $btwl . "%  at least 2x the value expected<br><br>";
        }

there is no value coming out of this. The default column value IS filled.
The query is double checked and gives the expected value 6 as a result in phpMyAdmin and dbForge Studio on both remote host and local.

SELECT CREATE TABLE KEUKEN
  CREATE TABLE `keuken` (    
  `ArtNr` int(5) unsigned NOT NULL,  
  `reeks` varchar(16) NOT NULL COMMENT 'binnen                                                         
        reeks op hoofdingredient of gang',                                                       
  `groep` varchar(16) DEFAULT NULL,  
  `omschrijving` varchar(32) NOT NULL, 
  `Prijs_Incl` decimal(8,2) unsigned NOT NULL,                                                    
  `Prijs_Excl` decimal(10,4) unsigned DEFAULT NULL,              
  `variaties` char(1) DEFAULT NULL COMMENT       
              'sterretje in tabel',               
  `aantal` char(6) DEFAULT NULL,   
  `BTW` tinyint(2) NOT NULL DEFAULT '7',     
   PRIMARY KEY (`ArtNr`)    
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

SELECT column_default etc, format by Paul T. in dbForge client

    $Q = "SELECT column_default FROM information_schema.`COLUMNS` where 
    table_name = 'keuken'";
    $result = $mysqli->query($Q);
    while ($row= $result->fetch_assoc()){
    echo $row['column_default'];  

and that worked, result as expected.


Solution

  • For me, the name of the column is actually column_default, and your SELECT query has the same column. So, BTW would not be part of the query results, and neither would Default.

    Can you check that, and then you should only need:

    echo $row['column_default'];
    

    So for my test run:

    SELECT column_default FROM information_schema.`COLUMNS` where table_name = 'tryMe' and column_name = 'date' 
    

    enter image description here

    ...and from the mysql client: enter image description here

    =====

    UPDATE:

    PHP output:

    array(1) { [0]=> array(1) { ["column_default"]=> string(17) "CURRENT_TIMESTAMP" } }