冷凝此代码

Question

I have the following code I want to run, but the problem is $this->type is set when the class is created by specifying either petition, proposal, or amendment. As you can see my $sql statement is a UNION of all three, and I want to specify which table (pet,prop,or amend) each row of data comes from.

public function userProposals() {
    $username = User::getUsername();
    $sql = "SELECT * FROM petition WHERE author = '$username'
    UNION SELECT * FROM proposition WHERE author = '$username'
    UNION SELECT * FROM amendment WHERE author = '$username'";
    $query = mysql_query($sql);
    $state = User::userState();

    while ($row = mysql_fetch_assoc($query)) { // $this->type needs to specify pet,prop,amend
        echo "
        <tr>
            <td>$row[id]</td>
            <td><a href='viewproposal.php?type=$this->type&id=$row[id]'>$row[title]</a></td>
            <td>$this->type</td>
            <td>$state</td>
        </tr>";
    }
}

As you can see, $this->type will only say one of the three. To get my function work how i wanted to, I did this (which I feel is too long & there must be a shorter way).

public function userProposals() {
    $username = User::getUsername();
    $state = User::userState();
    $sql = "SELECT * FROM petition WHERE author = '$username'";
    $query = mysql_query($sql);
    while ($row = mysql_fetch_assoc($query)) {
        echo "
        <tr>
            <td>$row[id]</td>
            <td><a href='viewproposal.php?type=petition&id=$row[id]'>$row[title]</a></td>
            <td>Petition</td>
            <td>$state</td>
        </tr>";
    }
    $sql = "SELECT * FROM proposition WHERE author = '$username'";
    $query = mysql_query($sql);
    while ($row = mysql_fetch_assoc($query)) {
        echo "
        <tr>
            <td>$row[id]</td>
            <td><a href='viewproposal.php?type=proposition&id=$row[id]'>$row[title]</a></td>
            <td>Proposition</td>
            <td>$state</td>
        </tr>";
    }
    $sql = "SELECT * FROM amendment WHERE author = '$username'";
    $query = mysql_query($sql);
    while ($row = mysql_fetch_assoc($query)) {
        echo "
        <tr>
            <td>$row[id]</td>
            <td><a href='viewproposal.php?type=amendment&id=$row[id]'>$row[title]</a></td>
            <td>Amendment</td>
            <td>$state</td>
        </tr>";
    }
}

Answer 1

I would normally do something like the following:

public function userProposals() {
    $username = User::getUsername();
    $state = User::userState();
    $tables = array('petition', 'proposition', 'amendment');
    foreach($tables as $table) {
        $label = ucwords($table);
        $sql = "SELECT * FROM $table WHERE author = '" . mysql_real_escape_string($username) . "'";
        $query = mysql_query($sql);
        while ($row = mysql_fetch_assoc($query)) {
            echo "
                <tr>
                <td>$row[id]</td>
                <td><a href='viewproposal.php?type=$table&id=$row[id]'>$row[title]</a></td>
                <td>$label</td>
                <td>$state</td>
                </tr>";
        }
    }
}