Member Avatar for João_5

Hello guys, my turn to ask a very simple question.

I already have a Mysql DB with querys working great.

I import data from Active Directory server, and one of the values is State, enabled or disabled, etc.
But this values came in a form of code:

512 = Enabled
514 = Disabled
544 = Enabled, Password Not Required
546 = Disabled, Password Not Required
66048 = Enabled, Password Doesnt Expire
66050 = Disabled, Password Doesn't Expire
66080 = Enabled, Password Doesn't Expire & Not Required

Now what i need is to insert the normal value in a new field

**Rude form:** If value is in row state is 512 insert into row state2 Enabled

Now i have the following code that is nor working:

    <?php
// error_reporting(0);
    // connect to the database
    include('config_users.php');
    $con = mysql_connect("$sqlhost","$sqluser",""); 
    if (!$con) 
    { 
    die('Could not connect: ' . mysql_error()); 
    } 
    $sel = mysql_select_db($database, $con); 
    if (!$sel) 
    { 
    die('Could not select DB: ' . mysql_error()); 
    } 
$sql    = 'SELECT * FROM users WHERE Estado = 66048';
$result = mysql_query($sql, $con);

if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_assoc($result)) {
    mysql_query("INSERT INTO users (State2) values ('Enabled')");
}

mysql_free_result($result);
?>

What am i doing wrong?
The last 3 hours where spent looking for a solution...

  • 2 Contributors
  • 7 Replies
  • 330 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Quick summary: nailed the practical fix by writing updates for each value, and correctly flagged that an UPDATE (not INSERT) is what you needed and that these are bitflags. A cleaner, future‑proof approach is to decode the userAccountControl bits instead of hardcoding every possible combined integer, and to use modern DB access (PDO or mysqli) so the script keeps working on current PHP versions.

Here is a simple, safe PHP pattern (PDO) that reads Estado, builds a human string from the bits, and updates only the State column for each row:

// decode and update with PDO (assumes a PK column named id)
$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8mb4','user','pass',[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stm = $pdo->query('SELECT id, Estado FROM users');
$upd = $pdo->prepare('UPDATE users SET State = :state WHERE id = :id');

while ($r = $stm->fetch(PDO::FETCH_ASSOC)) {
  $e = (int)$r['Estado'];
  $parts = [];
  $parts[] = ($e & 2) ? 'Disabled' : 'Enabled';
  if ($e & 32) $parts[] = 'Password Not Required';
  if ($e & 65536) $parts[] = 'Password does not expire';
  $upd->execute([':state' => implode(', ', $parts), ':id' => $r['id']]);
}

To convert an Active Directory lastLogon/lastLogonTimestamp value (Windows FILETIME) to Unix time:

function adFiletimeToUnix($ft) {
  if (empty($ft)) return null;
  return (int)($ft / 10000000) - 11644473600;
}

Practical tips: keep the raw Estado stored (it’s authoritative), but generate the human string on import or on read. Prefer normalised boolean columns (disabled, pwd_notreqd, pwdnoexp) if you need to filter users quickly. Avoid truncating tables if you want to preserve metadata; use transactions and INSERT ... ON DUPLICATE KEY UPDATE or batch UPDATEs. Finally, stop using `mysql*` functions (they were removed in PHP 7); use PDO/mysqli and prepared statements for safety.

Recommended Answers

All 7 Replies

Member Avatar for João_5

I probably should use something like this:

if ($type == 512){
$type == "Enabled";
} elseif ($type == 514){
$type == "Disabled";
} elseif ($type == 544){
$type == "EnabledPassNotReq";
} elseif ($type == 546){
$type == "DisabledPassNotReq";
} elseif ($type == 66048){
$type == "EnabledPassNoExp";
} elseif ($type == 66050){
$type == "DisabledPassNoExp";
} elseif ($type == 66080){
$type == "EnabledPassNoExpNotReq";
}
Member Avatar for Member #120589

So State2 must = Estado?

This doesn't make much sense to me. The INSERT query is inserting a blank record except for the State2 field. Aren't you thinking of an UPDATE query?

UPDATE users SET State2 = Estado WHERE Estado = 66048

But this values came in a form of code:
512 = Enabled
514 = Disabled
544 = Enabled, Password Not Required
546 = Disabled, Password Not Required
66048 = Enabled, Password Doesnt Expire
66050 = Disabled, Password Doesn't Expire

This is so you can use bitwise operators. Very good :)

Member Avatar for João_5

I have found it finaly, and its working: If Estado is 512 set State as Enabled... and so on... Thanks diafol.

mysql_query("UPDATE users SET State='Enabled' where Estado ='512'");
mysql_query("UPDATE users SET State='Disabled' where Estado ='514'");
mysql_query("UPDATE users SET State='Enabled, Password Not Required' where Estado ='544'");
mysql_query("UPDATE users SET State='Disabled, Password Not Required' where Estado ='546'");
mysql_query("UPDATE users SET State='Enabled, Password Doesnt Expire' where Estado ='66048'");
mysql_query("UPDATE users SET State='Disabled, Password Doesn't Expire' where Estado ='66050'");
mysql_query("UPDATE users SET State='Enabled, Password Doesn't Expire & Not Required' where Estado ='66080'");
Member Avatar for João_5

My active Directory Query is finished, 1 week of work, and it can fetch data from ldap server,save it to mysql database.
In Every refresh it Truncats tables, and updates with new info.
I can export to Excel (CSV), and finaly i can make the state code readable.

Just one thing missing... Convert lastlogon tomestamp, but for now it is not needed.

Member Avatar for Member #120589

I'm impressed :) LDAP is a bitch. I hate it with a vengeance.

Member Avatar for João_5

When someone says it is not possible, or it is very dificoult or even worse, i only get eager to finish the work.

I get this script working in less then a week.

Member Avatar for Member #120589

heh heh, good for you.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.