views:

136

answers:

3

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>";
    }
}
A: 

Why don't you try the modified SQL:

SELECT 'petition' as typ,title,id FROM petition
  WHERE author = '$username'
UNION SELECT 'proposition' as typ,title,id FROM proposition
  WHERE author = '$username'
UNION SELECT 'amendment' as typ,totle,id FROM amendment
  WHERE author = '$username'"

and then use the typ from each returned row ($row[typ]) instead of $this->type?

The whole thing should be:

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

  while ($row = mysql_fetch_assoc($query)) {
    echo "<tr>
      <td>$row[id]</td>
      <td><a href='viewproposal.php?type=$row[typ]&id=$row[id]'>
        $row[title]
      </a></td>
      <td>$row[typ]</td>
      <td>$state</td>
    </tr>";
  }
}

based on what was in your question.

paxdiablo
I got an error: Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM petition WHERE author = 'test44' UNION SELECT 'proposition' as typ,*' at line 1. From $query = mysql_query($sql);
MrCheetoDust
Apologies, @Farhan, I forgot you could either use * or fields but not both. I've fixed it to use the specific fields (which you might want to do anyway to minimize traffic sent across the wire).
paxdiablo
Strange, you should at least be able to use: SELECT 'petition' AS typ, petition.* FROM petition ...
too much php
+1  A: 

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>";
        }
    }
}
too much php
+2  A: 
nothingmuch
+1 good point! You need to watch out for users entering nasty bits of SQL in their user names (aka, "SQL injection attacks"). @too much php's call to mysql_real_escape_string() makes sure that the malicious SQL is safely put inside a string constant and can't hurt your application.
Jim Ferrans
@nothingmuch, while this is funny, it's not at all helpful in answering the specific question. It may not even be remotely relevant, since you have *no* idea what the User::getUsername() function does; it may always return sanitized values safe for use in queries.
paxdiablo
See http://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks
Jim Ferrans
that is irrelevant, the implied type of the User::getUsername() expression is a string representing a username, that is not an encoded string for consumption by an SQL database.<br/>The code that generates the SQL should be responsible for cleaning up the string, because if someone later refactors User::getUsername without thinking of all the possible usage contexts they might introduce an sql injection attack after the fact.<br/>I felt that others have answered the question but did not address this issue, and that since the poster is looking for general advice, this is relevant.
nothingmuch
wow, the formatting on this website sucks ass.
nothingmuch
paxdiablo