Jump to content

Is this possible to do with php and mysql?

imgrooot

By imgrooot
in MySQL Help

 Share
Go to solution Solved by Barand,

Recommended Posts

imgrooot Advanced Member

imgrooot

Posted
Posted

Here's what I am trying to do.

 

Users Table

user_id, sponsor_id, username, filled_positions, position_1, position_2, position_3, position_4, position_5

     1            0               user 1              4                   user 2       user 3       user 4         user 5

     2            1               user 2              2                   user 4       user 5

     3            1               user 3

     4            2               user 4

     5            2               user 5

 

 

Above is a "Users" table.  Here's what I am trying to do.  Insert new users into the table. Say I already have the users table set up with 5 users. I want to add User 6.  I want to loop through the users in the table and find the next empty position and update it with the new user id. 

 

In this scenario diagram above, the next empty position is Row 1 - position_5. The one after that is Row 2 - position_3 and then Row 2 - position_4...etc. It basically loops through rows and checks each position.  So User 6 will be placed under Row 1 - position_5 and User 7 will be placed under Row 2 - position_3. How can one go on about doing that?

imgrooot Advanced Member

imgrooot

Posted
  • Author
Posted (edited)

Following the rule described in the last paragraph, how did user4 and user5 get inserted into two different row-positions?

 

I am creating a 2x3 forced matrix mlm. So in this case, user1 directly sponsored user4 and user5, but since user2 is ALSO under user1, they would go under that user as well.

 

Let me paint the full picture. I was trying to simplify it for the question but I'll share the full details.

 

I have two tables. Table1: users. Table2: matrix

 

Users:

user_id   |   username   |   password   |   email

1                    user1                *****           user1@admin.com

2                    user2                *****           user2@admin.com

3                    user3                *****           user3@admin.com

4                    user4                *****           user4@admin.com

5                    user5                *****           user5@admin.com

6                    user6                *****           user6@admin.com

7                    user7                *****           user7@admin.com

 

matrix:

user_id, sponsor_id, username, filled_positions, position_1, position_2, position_3, position_4, position_5, position_6, position_7, position_8, position_9, position_10, position_11, position_12, position_13, position_14

     1            0               user1              6                  user2         user3          user4         user5         user6          user7

     2            1               user2              2                  user4         user5

     3            1               user3              2                  user6         user7

     4            2               user4

     5            2               user5

     6            3               user6

     7            3               user7

 

I already have these 7 users added manually in the database. It starts with a user8. *note that I am only using usernames under each position for visual purposes. In actuality, I'm using user_id of said usernames.

 

A user signs up through the website. If the user is already being sponsored by another user, then I can make it work. But If the user doesn't have a sponsor, then they have no sponsor id and that's where I have an issue because I can't insert the user in the matrix table without knowing the correct sponsor_id. The correct sponsor_id is from the next empty position in the row. 

 

So in this scenario, user8 would be the next member. User8 will be placed under position_7 of user1. Once this user's all 14 positions are filled, it will go down to the user row below it. So the next user, which would be User16 and will be placed under position_3 of user2. So it'll keep looping through the rows from left to right and find empty positions.

 

So once the new user is placed in the correct position in matrix table, then I can get the sponsor id of said row. I can then use it to insert a new user in the same matrix table.

 

Basically I am going to have 3 querys.

Query 1: Insert the user in users table. This is simply. Don't worry about this.

Query 2: Update a matrix row with a new user in anyone of the positions from 1-14(sequential order) in the matrix table.

Query 3: Insert the same user in the matrix table. This is simple as well. I just need the sponsor_id from Query 2.

 

Here is a 2x3 matrix diagram for visual purposes.

wqplbs.jpg

Edited by imgrooot
imgrooot Advanced Member

imgrooot

Posted
  • Author
Posted

It seems like I haven't explained my situation properly.

 

1. To put it simply, I want to loop through "matrix" table.

2. Find all the rows that have user id.

3. Get all the columns from the row with the lowest user id.

4. Do if else statement to see if each of the 14 positions in said row are empty.  If they are not, insert the new users until they are filled.

5. Move on to the next row with the next highest user id. Do the same thing as above. Rinse and repeat.

 

Here's a query example I have so far.

$find_sponsor = $db->prepare("SELECT * FROM matrix WHERE user_id > :user_id");
$find_sponsor->bindValue(':user_id',0);
$find_sponsor->execute();
$result_sponsor = $find_sponsor->fetchAll(PDO::FETCH_ASSOC);
if(count($result_sponsor) > 0) {
  foreach($result_sponsor as $row) {
    $sponsor_id             = $row['sponsor_id'];
    $user_id                = $row['user_id'];
    $filled_positions          = $row['filled_positions'];
    $position_1                 = $row['position_1'];
    $position_2                 = $row['position_2'];
    $position_3                 = $row['position_3'];
    $position_4                 = $row['position_4'];
    $position_5                 = $row['position_5'];
    $position_6                 = $row['position_6'];
    $position_7                 = $row['position_7'];
    $position_8                 = $row['position_8'];
    $position_9                 = $row['position_9'];
    $position_10                = $row['position_10'];
    $position_11                = $row['position_11'];
    $position_12                = $row['position_12'];
    $position_13                = $row['position_13'];
    $position_14                = $row['position_14'];
  }

  if(empty($position_1)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_1 = :position_1, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_1', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_2)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_2 = :position_2, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_2', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_3)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_3 = :position_3, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_3', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_4)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_4 = :position_4, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_4', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_5)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_5 = :position_5, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_5', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_6)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_6 = :position_6, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_6', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_7)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_7 = :position_7, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_7', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_8 = :position_8, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_8', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_9)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_9 = :position_9, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_9', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_10)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_10 = :position_10, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_10', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_11)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_11 = :position_11, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_11', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_12)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_12 = :position_12, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_12', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_13)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_13 = :position_13, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_13', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else if(empty($position_14)) {
 
    $update_sponsor = $db->prepare("UPDATE matrix SET position_14 = :position_14, filled_positions = :filled_positions + 1 WHERE user_id = :user_id");
    $update_sponsor->bindParam(':position_14', $new_user_id);
    $update_sponsor->bindParam(':filled_positions', $filled_positions);
    $update_sponsor->bindParam(':user_id', $matrix_user_id);
    if($update_sponsor->execute()) {}
 
  } else {}
}
  • Solution
Barand Prolific Member

Barand

Posted
  • Solution
Posted

My 0.02 worth
 
Tables

CREATE TABLE `users` (
  `user_id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `password` varchar(150) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `matrix` (
  `matrix_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `sponsor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`matrix_id`),
  KEY `idx_matrix_user_id` (`user_id`),
  KEY `idx_matrix_sponsor_id` (`sponsor_id`)
);

-- seed matrix with at least 1 record

INSERT INTO matrix(user_id, sponsor_id) VALUES (null,1);

This sample form adds a new user then inserts the user into the matrix table. If a sponsor is specified then that sponsors id is inserted, otherwise it is added to the first available sponsor (ie with less than 14 users)

 

Just curious - why 14?

<?php

// Your PDO connection code goes here

if ($_SERVER['REQUEST_METHOD']=='POST') {
    // NOTE : validation omitted for brevity
    
    $sql = "INSERT INTO users (username,password,email)
            VALUES (:user,:pass,:email)";
    $stmt = $db->prepare($sql);
    $stmt->execute( [
            'user' => $_POST['name'],
            'pass' => password_hash($_POST['pwd'], PASSWORD_DEFAULT),
            'email' => $_POST['email']
        ]);
        
    // get the id of the newly added user
    $userid = $db->lastInsertId();
    
    // add new user into the matrix
    $sql = "INSERT INTO matrix (user_id, sponsor_id)
            SELECT @user as user
                , CASE 
                    WHEN @sponsor=0 THEN m.sponsor_id
                    ELSE @sponsor
                  END as sponsor
            FROM (
                    SELECT sponsor_id
                      , COUNT(user_id) as tot
                    FROM matrix
                    WHERE sponsor_id IS NOT NULL
                    GROUP BY sponsor_id
                    HAVING tot < 14
                    ORDER BY sponsor_id
                    LIMIT 1
                ) m
                JOIN (SELECT @user := :user, @sponsor := :sponsor) init";
    $stmt = $db->prepare($sql);
    $stmt->execute([
                ':user' => $userid,
                ':sponsor' => $_POST['sponsor']
            ]);
}

function userOptions($db, $current=0)
{
    $sql = "SELECT 
                user_id
              , username
            FROM users
            ORDER BY username";
    $res = $db->query($sql);
    $opts = '<option value="0">--No sponsor specified--</option>';
    foreach ($res as $r) {
        $opts .= "<option value='$r[user_id]'>$r[username]</option>\n";
    }
    return $opts;
}

function currentUsers($db)
{
    $sql = "SELECT u1.user_id
              , u1.username
              , GROUP_CONCAT(u2.user_id ORDER BY u2.user_id SEPARATOR ' | ') as users
              FROM matrix m
                INNER JOIN users u1 ON m.sponsor_id = u1.user_id
                INNER JOIN users u2 ON m.user_id = u2.user_id
              GROUP BY u1.user_id
              ORDER BY u1.user_id";
    $res = $db->query($sql);
    $usrs = '';
    foreach ($res as $r) {
        $usrs .= "<tr><td>$r[user_id]</td><td>$r[username]</td><td>$r[users]</td></tr>\n";
    }
    return $usrs;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>codeword_entry</title>
<meta name="author" content="Barry Andrew">
<style type="text/css">
body {
    font-family: sans-serif;
    font-size: 10pt;
}
fieldset {
    padding: 15pt;
    background-color: #ccc;
}
legend {
    background-color: black;
    color: white;
    padding: 3px;
}
table {
    font-family: sans-serif;
    font-size: 10pt;
    border-spacing: 1px;
    min-width: 700px;
} 
th {
    background-color: black;
    color: white;
    padding: 5px 3px;
}
td {
    padding: 3px;
}
</style>
</head>
<body>
<h1>Add New User</h1>
<form action="" method='POST'>
    <fieldset>
    <legend>User details</legend>
        User name <input type="text" name="name" size="40"><br>
        Password  <input type="password" name="pwd" size="40"><br>
        Email <input type="text" name="email" size="50">
    </fieldset>
    <fieldset>
    <legend>Sponsor</legend>
        Sponsor <select name="sponsor"><?=userOptions($db)?></select>
    </fieldset>
    <input type="submit" name="btnSub" value="Submit">
</form>
<hr>
<h2>Current Users</h2>
<table>
    <tr><th>ID</th><th>User name</th><th>Sponsored Users</th></tr>
    <?=currentUsers($db)?>
</table>
</body>
</html>
  • Like 1
imgrooot Advanced Member

imgrooot

Posted
  • Author
Posted

 

My 0.02 worth

 

Tables

CREATE TABLE `users` (
  `user_id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `password` varchar(150) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `matrix` (
  `matrix_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `sponsor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`matrix_id`),
  KEY `idx_matrix_user_id` (`user_id`),
  KEY `idx_matrix_sponsor_id` (`sponsor_id`)
);

-- seed matrix with at least 1 record

INSERT INTO matrix(user_id, sponsor_id) VALUES (null,1);

This sample form adds a new user then inserts the user into the matrix table. If a sponsor is specified then that sponsors id is inserted, otherwise it is added to the first available sponsor (ie with less than 14 users)

 

Just curious - why 14?

<?php

// Your PDO connection code goes here

if ($_SERVER['REQUEST_METHOD']=='POST') {
    // NOTE : validation omitted for brevity
    
    $sql = "INSERT INTO users (username,password,email)
            VALUES (:user,:pass,:email)";
    $stmt = $db->prepare($sql);
    $stmt->execute( [
            'user' => $_POST['name'],
            'pass' => password_hash($_POST['pwd'], PASSWORD_DEFAULT),
            'email' => $_POST['email']
        ]);
        
    // get the id of the newly added user
    $userid = $db->lastInsertId();
    
    // add new user into the matrix
    $sql = "INSERT INTO matrix (user_id, sponsor_id)
            SELECT @user as user
                , CASE 
                    WHEN @sponsor=0 THEN m.sponsor_id
                    ELSE @sponsor
                  END as sponsor
            FROM (
                    SELECT sponsor_id
                      , COUNT(user_id) as tot
                    FROM matrix
                    WHERE sponsor_id IS NOT NULL
                    GROUP BY sponsor_id
                    HAVING tot < 14
                    ORDER BY sponsor_id
                    LIMIT 1
                ) m
                JOIN (SELECT @user := :user, @sponsor := :sponsor) init";
    $stmt = $db->prepare($sql);
    $stmt->execute([
                ':user' => $userid,
                ':sponsor' => $_POST['sponsor']
            ]);
}

function userOptions($db, $current=0)
{
    $sql = "SELECT 
                user_id
              , username
            FROM users
            ORDER BY username";
    $res = $db->query($sql);
    $opts = '<option value="0">--No sponsor specified--</option>';
    foreach ($res as $r) {
        $opts .= "<option value='$r[user_id]'>$r[username]</option>\n";
    }
    return $opts;
}

function currentUsers($db)
{
    $sql = "SELECT u1.user_id
              , u1.username
              , GROUP_CONCAT(u2.user_id ORDER BY u2.user_id SEPARATOR ' | ') as users
              FROM matrix m
                INNER JOIN users u1 ON m.sponsor_id = u1.user_id
                INNER JOIN users u2 ON m.user_id = u2.user_id
              GROUP BY u1.user_id
              ORDER BY u1.user_id";
    $res = $db->query($sql);
    $usrs = '';
    foreach ($res as $r) {
        $usrs .= "<tr><td>$r[user_id]</td><td>$r[username]</td><td>$r[users]</td></tr>\n";
    }
    return $usrs;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>codeword_entry</title>
<meta name="author" content="Barry Andrew">
<style type="text/css">
body {
    font-family: sans-serif;
    font-size: 10pt;
}
fieldset {
    padding: 15pt;
    background-color: #ccc;
}
legend {
    background-color: black;
    color: white;
    padding: 3px;
}
table {
    font-family: sans-serif;
    font-size: 10pt;
    border-spacing: 1px;
    min-width: 700px;
} 
th {
    background-color: black;
    color: white;
    padding: 5px 3px;
}
td {
    padding: 3px;
}
</style>
</head>
<body>
<h1>Add New User</h1>
<form action="" method='POST'>
    <fieldset>
    <legend>User details</legend>
        User name <input type="text" name="name" size="40"><br>
        Password  <input type="password" name="pwd" size="40"><br>
        Email <input type="text" name="email" size="50">
    </fieldset>
    <fieldset>
    <legend>Sponsor</legend>
        Sponsor <select name="sponsor"><?=userOptions($db)?></select>
    </fieldset>
    <input type="submit" name="btnSub" value="Submit">
</form>
<hr>
<h2>Current Users</h2>
<table>
    <tr><th>ID</th><th>User name</th><th>Sponsored Users</th></tr>
    <?=currentUsers($db)?>
</table>
</body>
</html>

 

Awesome. I will give it shot.  Thanks.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.