MyBB Community Forums - How to sort all users then delete them by MySQL

Pages: 1 2

I am currently learning mysql and i need some help to see how to manage this.
I want to order all users by post number to be 0, and newpoints to be 0 and uid has to be less than 200 so it takes only older users.
When sorted I want to delete those with 0 posts, 0 newpoints and uid less than 200

This is my progress so far xD Angel

SELECT `uid`, `username`, `newpoints`, `postnum`,
CASE
WHEN `newpoints` = 0 THEN 'Delete'
WHEN `uid` < 200 THEN 'Delete'
WHEN `postnum` = 0 THEN 'Delete'
ELSE 'Good'
END
FROM `mybb_users`;

Where are you learning it from Confused

https://www.w3schools.com/sql/default.asp

From codecademy Big Grin

Okay I manage to get better example.

SELECT `uid`, `username`, `newpoints`, `postnum` 
FROM `mybb_users` 
WHERE (`uid` <= 200) 
AND (`newpoints` = 0) 
AND (`postnum` = 0)
ORDER BY `uid` ASC;

Now I need to drop those users by this order.

Why are you attempting to order them and then delete them? It makes no difference in which order they're deleted so there's no need to do two queries instead of one. You can run the following query to delete them based on that criteria:

DELETE FROM mybb_users WHERE uid <= 200 AND newpoints = 0 AND postnum = 0;

This will delete all users with the UID of 200 and below, if they have 0 newpoints and a postnum of 0.

So if I want to add also last login date how that would be?

DELETE FROM mybb_users 
WHERE uid <= 200
AND newpoints = 0
AND postnum = 0
AND `lastvisit` < 1533081600;

Also how to add it as php input function so i don't have to query everytime the sql?

(2018-10-23, 03:11 PM)codedude Wrote: [ -> ]Also how to add it as php input function so i don't have to query everytime the sql?

$db->delete_query('users', "uid < 200 AND newpoints=0 AND postnum=0 AND lastvisit < 1533081600");

(2018-10-23, 03:20 PM)Wildcard Wrote: [ -> ]
(2018-10-23, 03:11 PM)codedude Wrote: [ -> ]Also how to add it as php input function so i don't have to query everytime the sql?
$db->delete_query('users', "uid < 200 AND newpoints=0 AND postnum=0 AND lastvisit < 1533081600");

Thanks wildcard, but I want to make uid and lastvisit as html input field instead so i can enter those values manually

Are you asking how to create an HTML form? How to use input from an HTML form in your DELETE query? Or both?

EDIT: Also, ACP or forum-side?

(2018-10-23, 03:21 PM)codedude Wrote: [ -> ]
(2018-10-23, 03:20 PM)Wildcard Wrote: [ -> ]
(2018-10-23, 03:11 PM)codedude Wrote: [ -> ]Also how to add it as php input function so i don't have to query everytime the sql?
$db->delete_query('users', "uid < 200 AND newpoints=0 AND postnum=0 AND lastvisit < 1533081600");
Thanks wildcard, but I want to make uid and lastvisit as html input field instead so i can enter those values manually

Can you paste the code of your custom page so far, where the inputs will be? My input would look something like this:

HTML

<input type="text" name="uids" value="" >
<input type="submit" value="Submit" class="button" name="submit">

PHP

if(isset($mybb->input['submit']) && $mybb->request_method == "post")
{
$uids = (int) $mybb->get_input('uids');
// Code...

$db->delete_query("users", "uid < '".$uids."' AND newpoints=0 AND postnum=0 AND lastvisit < 1533081600");
}

The same can be done for newpoints, postnum and lastvisit. For security purposes I would also suggest adding two hidden inputs to your HTML:

						<input type="hidden" name="my_post_key" value="'.$mybb->post_code.'"></input>
						<input type="hidden" name="uid" value="'.$mybb->user['uid'].'"><br />

The first input is basically a unique identifier that is attached to your account, and the second is the UID of the user submitting the form. You would then use the following within the IF statement above:

verify_post_check($mybb->get_input('my_post_key'));

For the second input, you could then sanitise it and then check it against the mybb_users table to see if the user submitting the form actually exists. The rest is completely up to you.

(2018-10-23, 03:45 PM)Wildcard Wrote: [ -> ]Are you asking how to create an HTML form? How to use input from an HTML form in your DELETE query? Or both?

EDIT: Also, ACP or forum-side?

I was thinking of making it as standard php file in the simplest form via db connect and then protect it via password.

This is as much as I can progress right now with my limited knowledge Big Grin

<?php

/* Now I want to insert connect method to db and normal input form */

define('DB_HOST', 'localhost');
define('DB_NAME', 'database_name');
define('DB_TABLE', 'mybb_users');
define('DB_USER', 'username');
define('DB_PASS', 'password');
?>

  <form method="post">
    <label>
      <p>
      USER ID to query <input type="number"  name="uid" />
      </p>
    <p>
    Last visit query <input type="number" name="lastvisit" />
    </p>
    </label>

    <p>
    <input type="submit" value="Delete" class="confirm"/>
    </p>
  </form>
  
  <script> // Now we query the confirmation via jquery
  $(function() {
    $('.confirm').click(function() {
        return window.confirm("Are you sure?");
    });
});</script>

(2018-10-23, 03:47 PM)Wires Wrote: [ -> ]-snip-

I posted a code example above. Thank you Wires, can you check it?

Pages: 1 2