Databases
StackOverflow2013
Postsdbo
POphp/mysql: get data from one table, convert and insert in another table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POphp/mysql: get data from one table, convert and insert in another table
    primarykey
    data
    text
    <p>Ok i will try to explain, (i am portuguese lol).</p> <p>I have a Database where i have the fallowing MySql:</p> <p>Database: ad Table: notebooks Fields: ID, tag, so, lastlogon, lastlogh</p> <p>My ldap query gets data from ldap server (active directory) and stores it in my mysql database (ad/notebooks). I get tag wich is tag number is unique I get so wich is the OS installed in the notebook I get lastlogh wich is the last logon time stamp, and it is unique too ID field is auto increment and key but i can set the tag filed to be key</p> <p>I have a config_notebooks.php where i set all the variables to connect to ldap and mysql servers.</p> <pre><code>&lt;?php /*------------------------------------------------------------------------*/ //setting your variables /*------------------------------------------------------------------------*/ //ldap host $host = "ldap://HEICPT1VIA01.HEIWAY.NET"; //ldap user $user = "domain\user"; //ldap password $pswd = "pssw"; //ldap base structure $dn = "OU=NotebookM2,OU=WorkstationsM2,OU=PT1,DC=heiway,DC=net";; //attributs to search and get $attrs = array("cn","operatingsystem","lastlogon"); //sql host $sqlhost="localhost"; //sql user $sqluser="root"; //sql password $sqlpswd=""; //sql database $database="ad"; //sql table $table="notebooks"; ?&gt; </code></pre> <p>Now the query script</p> <pre><code>&lt;?php /*------------------------------------------------------------------------*/ //Query script /*------------------------------------------------------------------------*/ include 'config_notebooks.php'; $filter = $_POST['filter']."=".$_POST['keyword']."*"; //connect to db $con = mysql_connect("$sqlhost","$sqluser",""); if (!$con) { die('Could not connect: ' . mysql_error()); } //connect to active directory $ad = ldap_connect($host) or die( "Could not connect!" ); // Set version number ldap_set_option($ad, LDAP_OPT_PROTOCOL_VERSION, 3) or die ("Could not set ldap protocol"); // Binding to ldap server $bd = ldap_bind($ad, $user, $pswd) or die ("Could not bind"); $search = ldap_search($ad, $dn, $filter, $attrs) or die ("ldap search failed"); $entries = ldap_get_entries($ad, $search); $sel = mysql_select_db("ad", $con); if (!$sel) { die('Could not select DB: ' . mysql_error()); } for ($i=0; $i&lt;$entries["count"]; $i++) { $tag = $entries[$i]["cn"][0]; $so = $entries[$i]["operatingsystem"][0]; $lastl = $entries[$i]["lastlogon"][0]; mysql_query(" INSERT INTO $table (tag, so, lastlogh) VALUES ('$tag', '$so', '$lastl') ON DUPLICATE KEY UPDATE tag='$tag', so='$so', lastlogon='$lastl' "); } mysql_close($con); ldap_unbind($ad); if ($entries["count"] &gt; 0) header("Location: notebooks_list.php") ?&gt; </code></pre> <p>In this query the last logon timestamp is coded like 130276262860634000</p> <p>So i can export everything to excel and decode it, but i found a code that does that, so it saves time. I created a new field in the database (lastlogon) and i need to fetch the data from lastlogh field, decode with the new script and store it in the lastlogon field.</p> <p>This is the script that i found:</p> <pre><code>&lt;?php function adConvert ($ad) { $seconds_ad = $ad / (10000000); //86400 -- seconds in 1 day $unix = ((1970-1601) * 365 - 3 + round((1970-1601)/4) ) * 86400; $timestamp = $seconds_ad - $unix; $normalDate = date("F d, Y", $timestamp); return $normalDate; } //example: echo adConvert($ad); ?&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdiasje
    1. USdiasje
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload