PHP mysqli create table according to number of rows

PHP
1

I’m retrieving all the users from this table and display them into a table (the usernames separated by commas will be displayed each for each table row; each row of exceptions will be displayed as the tables)

cutoff

| user  | exceptions |
+-------+------------+
| admin | moderator,john,james,mark,luke,thewbmstr,jane,ben,robert,kumar,ford,bert,peter |
+-------+------------+
| admin | harry,joe  |

Get exceptions function

public function get_exceptions($type, $is_arr = false)
{
    global $sqlConnection;
    $curuser = $this->get_cuname();
    $exceptlist = null;
    
    $s = $sqlConnection->prepare("SELECT exceptions FROM cutoff_$type WHERE user = ?");
    $s->bind_param('s', $curuser);
    $s->execute();
    $s->store_result();
    $s->bind_result($exceptions);

    while ($s->fetch()) {
        // Split into arrays
        $split .= ';' . $exceptions;
        $exceptlist = explode(';', $split);
    }
    
    if ($is_arr)
        return $exceptlist;
    else
        return '"' . join('","', $exceptlist) . '"';
}

Load table function

public function load_direct_sales_table($previous = false) {
    // the current user/id
    $cur_user = $this->get_cuname();
    $cur_id = $this->get_cuid();
    $exceptlist = $this->get_exceptions('direct', true);

    // I don't think if this is correct
    foreach ($exceptlist as $excepteach) {
        if (!$previous) {
            $query = "SELECT user_id, username, first_name, last_name, sale_status, first_sale, name_format";
            $query .= " FROM accounts ACC";
            $query .= " INNER JOIN info INF ON ACC.user_id = INF.iuid";
            $query .= " INNER JOIN sales SAL ON ACC.user_id = SAL.suid";
            $query .= " WHERE SAL.mentor = ? AND username NOT IN ($excepteach)";
        } else {
            $query = "SELECT user_id, username, first_name, last_name, sale_status, first_sale, name_format";
            $query .= " FROM accounts ACC";
            $query .= " INNER JOIN info INF ON ACC.user_id = INF.iuid";
            $query .= " INNER JOIN sales SAL ON ACC.user_id = SAL.suid";
            $query .= " WHERE SAL.mentor = ? AND username IN ($excepteach)";
        }
        
        $stmt = $GLOBALS['sqlConnection']->prepare($query);
        $stmt->bind_param('s', $cur_user);
        $stmt->execute();
        $stmt->store_result();

        if ($stmt->num_rows > 0) {
            $stmt->bind_result($user_id, $username, $first_name, $last_name, $sale_status, $first_sale, $name_format);
            $total = number_format(self::INCOME * $stmt->num_rows, 2);
            $income_currency = self::CURRENCY;

            $result = "<table class='income-table'>";
            $result .= "<thead>";
            $result .= "<tr>";
            $result .= "<td>User ID</td>";
            $result .= "<td>Full name</td>";
            $result .= "<td>Username</td>";
            $result .= "<td>Status</td>";
            $result .= "<td>Income</td>";
            $result .= "</tr>";
            $result .= "</thead><tbody>";

            while ($stmt->fetch()) {
                $qualified = '';
                $format_income = number_format(self::INCOME, 2);
                $full_name = $this->format_name($first_name, $last_name, $name_format);

                if ('QUALIFIED' === $sale_status)
                    $qualified = 'class="qualified"';

                $result .= "<tr>";
                $result .= "<td>00{$user_id}</td>";
                $result .= "<td>{$full_name}</td>";
                $result .= "<td>{$username}</td>";
                $result .= "<td {$qualified}>{$sale_status}</td>";
                $result .= "<td>{$income_currency}{$format_income}</td>";
                $result .= "</tr>";
            }

            $result .= "</tbody><tfoot>";
            $result .= "<td></td>";
            $result .= "<td></td>";
            $result .= "<td></td>";
            $result .= "<td></td>";
            $result .= "<td>{$income_currency}{$total}</td>";
            $result .= "</tfoot>";
            $result .= "</table>";

            echo $result;
        } else {
            $result = "<div class=\"sale-no-result\">";
            $result .= "<p>No sales records.</p>";
            $result .= "</div>";

            echo $result;
        }
    }
}

I wanted to achieve:

  • For each row of exceptions, there will be tables (e.g. 2 rows = 2 tables)
  • For each usernames (comma-separated) will be rows of tables (e.g. 5 usernames = 5 rows per table)
2

What’s your question? Try to break it down to small questions each time.

1 Like
3

What I want is to render all rows into tables and the value of each row will be the row of the rendered tables.
For instance,

// 3 rows = 3 tables
| row 1 |         ==>>       <table></table>
+-------+
| row 2 |         ==>>       <table></table>
+-------+
| row 3 |         ==>>       <table></table>

for each row, there will be values separated by commas

// 3 rows = 3 tables && number of values = number of rows
row 1    | john,james,mark,luke |      ==>> <table><tr>{username}</tr></table> // 4 trs
         +----------------------+
row 2    | harry,ford,kumar     |      ==>> <table><tr>{username}</tr></table> // 3 trs
         +----------------------+
row 3    | lance,peter,ben      |      ==>> <table><tr>{username}</tr></table> // 3 trs
         +----------------------+

Thanks for your comprehension.

4

Seeing the comma separated list of names, I have to ask.

Surely you’re not keeping data like that in a single field are you?

5

Well, that’s my decision to put the user’s exception (usernames) in a single row

6

Do you want this output???

<table border="1">
  <tr>
    <td>john</td>
  </tr>
  <tr>
    <td>james</td>
  </tr>
  <tr>
    <td>mark</td>
  </tr>
  <tr>
  <td>luke</td>
  </tr>
  </table>

Next table … etc

If it is this way, you will need to write a function to split up the user names by comma, then loop through them and add the td tags.

7

What do you do when there are a million exceptions - that single row would take years of horizontal scrolling to get to the end.

8

Yes, you got my point. Thanks for that.

9

Well, It would be a hard challenge for me, but after 3 months, all exceptions inside that row (with corresponding user) will automatically be deleted.

10

Not a smart one though.

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.