Hi,
Tried to find query syntax for getting default value from table column, but somehow, not found. Or i'm searching with wrong words....

I can update default values with form like this:
$updsttime = mysqli_query($db,"ALTER TABLE $workhourstable ALTER COLUMN starttime SET DEFAULT '$starttime'");

But. It would be nice, that i could see the current default value first in echoed value. Without opening any phpadmin-consoles, or other actions.
just query this starttime-column default value. Is this rocket-sience or does anyone knows a short and easy way to get the value, as updating it, like above?

    I'd go for Ajax for live updates,

    <script>
		setInterval(function() 
              {      
    
		       $.ajax({    
    
			type: "GET",
			url: "yourSqlQueryHere.php",             
    
			dataType: "html",          
    
			success: function(response){                    
    
				$("#eventData").html(response); 
			}

	   });
	}, 1000 /*set refresh time*/ ); 
</script>

<div id="eventData"> </div>

      My site is free of jquery,bootstrap,ajax,etc. Whole system is working with PHP,MYSQL and CSS just fine. There is no need to add any massive libraries for site that is used for working, not presenting any "cool" nowadays stuffs. It is therefore maybe one of the fastest responsive web-site ;-)

        function call_sql() {
    $('#eventData').load('yourSqlQueryHere.php', function () {
        setTimeout( call_sql, 1000 /* Your time here */ );
    });
}
call_sql();

        JS version.

          I try to avoid events, what are refreshed with intervals=more traffic to site. It is enough, when you open the page, you see the current value.
          So i like to find correct query syntax for that.

              
            
 <?php  
            
$connect=mysqli_connect("YOUR HOST HERE","YOUR USERNAME HERE","YOUR PASSWORD HERE","YOUR DATABASE HERE"); 
if (mysqli_connect_errno()) 
  { 
  echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
  } 
    $sql = "SELECT * FROM YOUR TABLE HERE"; 
    $result = mysqli_query($connect, $sql); 
    if($result == false) { 
        die("Query failed: ".mysqli_error().PHP_EOL.$sql); 
    } 
while($res = mysqli_fetch_assoc($result)); 
{  
            
     echo $res['YOUR TABLE ROW HERE'];  
            
}  

mysqli_close($connect); 
?>

            Standard while loop, just plugin the table rows you want

              Yes, but hat is not what i need.
              If i have created table colum like:
              starttime VARCHAR(45) NOT NULL DEFAULT '08.00'

              I need to get that DEFAULT-value, what is here in sample 08.00

                
SELECT CASE 
    WHEN EXISTS(
        SELECT starttime  
                
        FROM YOUR TABLE HERE 
        WHERE YOUR TABLE ROW HERE = '$starttime'
    )
    THEN (
        SELECT starttime 
        FROM YOUR TABLE HERE 
        WHERE YOUR TABLE ROW HERE = '$starttime''
    ) 
    ELSE 'Default Value' 
END

                  If i open more this case.

                  I update starttime default value sometimes.
                  But instead of fetching current default value somewhere other place or trying to remember what it was, it would be easier to see it in select option value:

                  <select name="starttime" type="text" id="starttime" class="planselect" style="float:left;"
                  <option value="'.$starttime.'">'.$starttime.'</option>';
                  timeselect();
                  echo'</select>

                  fetching it before select, could be displayed as first option.

                    <?php
if ($hours_rule == '08:00')
  {
    ?>
    <select id='hours' name='hours_rule' />
      <option value="'.$starttime.'"><?php echo '.$starttime.' ?></option>';
    </select>
    <?php
   }
else 
  {
    ?>
    <select id='hours' name='hours_rule' />
      <option value="'.$starttime.'"><?php echo '.$starttime.' ?></option>';
    </select>
    <?php
   }

                    as an aside, don't use inline styles, add that to a css file.

                      So you just need to select from information schema to get the current default.

                      SELECT 
    COLUMN_DEFAULT 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = '<name of database goes here>' 
    AND TABLE_NAME = '<name of table goes here>' 
    AND COLUMN_NAME = '<name of column goes here>'

                        Thanks Derokorian, you got the one solution for this.

                        But i already find the way with morning coffee.
                        The trick was using Default in $row syntax! :-D

                        And the working method is like this:
                        $query = mysqli_query($db,"SHOW COLUMNS FROM $workhourstable LIKE 'starttime'");
                        while ($row=mysqli_fetch_array($query)) { $start=$row['Default']; }

                          The advantage of using INFORMATION_SCHEMA are that it is a much more comprehensive view of the data the DBMS has stored about the databases it is managing (it's a metadata database) and doesn't make as many demands on you to remember a whole new bunch of ad-hoc statements that probably aren't supported on other DBMS. I'm not using MySQL, so I don't know if and how you can use SHOW COLUMNS in a query (e.g., joining it to other queries), but I do know that you can with INFORMATION_SCHEMA because it's standard SQL and uses standard SQL semantics.

                          The query Derokorian wrote works just as well on PostgreSQL and SQL Server, for example, but SHOW COLUMNS is meaningless not-SQL on those systems (which, it has to be noted, also have their own interfaces into their own native system catalogues, although in both cases they're still standard SQL syntax).

                            Very good point. I like to see, that my site is compatible with other databases also than mysql.
                            I will update form to use INFORMATION_SCHEMA query instead.

                              Updated, and works like a charm. Thanks! I'm happy.

                                Write a Reply...