Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Nov 2005
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Get unique <input> values for multiple SQL entries

    I have code that technically WORKS, but it's not working like I want it to.

    I set up a table into which I drop distinct names out of an SQL table. This works fine.
    Code:
    <table id="box-table-a" summary="PAGE HEADER" style='width:10%'>
    	<tr> 
    			<th>
    				<?php
    include('./db.php');
    $PM = mysqli_query($con, "SELECT DISTINCT PMName FROM report ORDER BY PMName ASC");
    ?> 
    					<b>Sort by PM:</b>
    					<br> 
    					<Select name='PMName' onChange='submit(this.form)'> 
    				<?php
    while ($row = mysqli_fetch_row($PM)) {
        $sel        = ($table === $row[0]) ? "id='sel' selected" : "";
        $selectedPM = array_key_exists('PMname', $_POST) != '' ? ' selected' : '';
        printf(" <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0]);
    }
    ?>  
    					</select> 
    			</th>
    			<th>
    This works perfectly, and is controlled (right now, for testing purposes) by the following SQL query: This works fine.

    Code:
    $result = mysqli_query($con, "SELECT * FROM report WHERE PMName = '$PMSelection' AND REGNSB <> 0.000 ORDER BY RegNSB DESC Limit 5");
    This result then filters into the next HTML table that I have below.

    Code:
    <form action="" method="POST">
    <table id="box-table-a" summary="PM Summary" style='width:90%'>
    <tr>
    <th>Update</th>
    <th style='width:20%'>PM Comments </th>
    <th>PMM Recommendations</th>
    </tr>
    
    <?php
    
    while ($row = mysqli_fetch_array($result)) {
        $PMComments         = $row['PMComments'];
        $PMMRecommendations = $row['PMMRecommendations'];
    ?>
    
    <tr>
    <td><input name="update" type="submit" id="update" value="Update"></td>
    <td><?= $PMComments ?><input name="PMComments" type="text" value="<?= $PMComments ?>"></td>
      <td><?= $PMMRecommendations ?><value="<?= $PMMRecommendations ?>">		
    			<select name="PMMRecommendations[]">
    				<option value="null"></option>
    				<option value="No Action Taken">No Action Taken</option>
    			</select>    
      </td>
    </tr>
    <?php
    }
    ?>
    </table>
    </form>
    So far - I select a name, and the first 5 items (from the Limit 5) owned by this person show up in the table below. Score! There are about 15 other items with data in them (which I didn't list here...) that display fine. The two items I listed in the code above are the ones I'm having a hard time with though. The database is already populated, so $PMComments and $PMMRecommendations are already pre-populated, which is fine. I want the user to see the data that's already there, but I also want to give them the ability to UPDATE these comments and overwrite the value that is already populated in the database.

    Right now, I have 5 rows displaying in my table, and the following code:

    Code:
    <?php
        $PMCommentsValue         = $_POST["PMComments"];
        $PMMRecommendationsValue = $_POST["PMMRecommendations"];
    
    if (isset($_POST['update'])) {
        
        printf("PMComments: %s ", $PMCommentsValue);
        echo "<br>";
        printf("PMRecommendations: %s ", $PMMRecommendationsValue);
    
    }
    ?>
    If I change the value of any of the 5 rows, I get two null results. If I change the 5th row, I get whatever data I put into that row. This tells me that the functionality is working - $_POST["PMComments[]"] and $_POST["PMMRecommendations[]"] are picking SOMETHING up - just not what I want them to pick up. They're only picking up data in the final row of my table, which would be valuable to me if my table only had 1 row in it, but it doesn't.
    It's obvious to me that I need some way to make EACH row of the table unique in its output, but I don't know how to do that.

    My mind imagines that I need to do something with this line:
    Code:
    <td><?= $PMComments ?><input name="PMComments" type="text" value="<?= $PMComments ?>"></td>
    so that each ROW of my table will have a different input name, like PMComments1, PMComments2, PMComments3, PMComments4, PMComments5 - but I don't know how to do this.

    Then, later on, I need a way to get that specific value back from the $_POST, which I also don't know how to do.

    Can someone walk me through how to make this work?

    Here's an example of what it looks like....

    Get unique &lt;input&gt; values for multiple SQL entries-test.png

  • #2
    New Coder
    Join Date
    Nov 2005
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Well, I figured this out. As I've seen a LOT of threads around the interwebs on this exact subject, I wanted to write down how I figured this out in case it helps anyone.


    My cells are populated as such:

    Code:
    <td><?= $PMComments ?><input name="PMComments[]" type="text" value="<?= $PMComments ?>"></td>
    <td><?= $PMMRecommendations ?><value="<?= $PMMRecommendations ?>">		
    			<select name="PMMRecommendations[]">
    				<option value="null"></option>
    				<option value="No Action Taken">No Action Taken</option>
    			</select>    
    </td>
    Then down below I have a for statement that assigns a UNIQUE variable to each instance of "PMComments[]" and "PMMRecommendations[]" (turning them into PMComments1, PMComments2, PMComments3, etc....)
    Code:
    for ($n = 0, $t = count($_POST['PMComments']); $n < $t; $n++) {
        $PMCommentsValue         = $_POST['PMComments'][$n];
        $PMMRecommendationsValue = $_POST['PMMRecommendations'][$n];
        $LineID                  = $_POST['LineID'][$n];
        //echo "POST $n " . $PMCommentsValue . " with LineID " . $LineID . "<br>"; //Uncomment this out to see a visual representation of the update string....
            $sql    = "UPDATE report SET PMComments = '$PMCommentsValue' , PMMRecommendations = '$PMMRecommendationsValue' , Outcome = '$OutcomeValue' WHERE LineID = $LineID ;" ;
        echo $sql . "<br>" ;
        $retval = mysqli_query($con, $sql);
        if (!$retval) {
            die('Could not update data: ' . mysql_error());
        }
        
    }
    The for ($n = 0, $t = count($_POST['PMComments']); $n < $t; $n++) { statement says that $n should equal 0, and $t should equal the physical count of however many items PMComments POSTed out, (if I have 5 lines, it's 5, if I have 500, it's 500.)
    Then, each row in my table is assigned a unique value, (instead of all 500 rows POSTing "PMComments," row 1 POSTs "PMComments1" while row 469 POSTs PMComments469.)

    I now have the ability to write this back into a SQL statement by using $LineID (my unique key for this table).

    I hope this helps someone....

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,085
    Thanks
    80
    Thanked 4,553 Times in 4,517 Posts
    You could *ALSO* do
    Code:
    <?php
    $rownum = 0;
    while( $row = mysql_fetch_assoc( $results ) 
    {
        $PMComments = $row["comments"];
        $PMMRecommendations = $row["recommend"];
        ...
    ?>
    ...
    <td><?= $PMComments ?><input name="PMComments[<?=$rowcnt?>]" type="text" value="<?= $PMComments ?>"></td>
    <td><?= $PMMRecommendations ?><value="<?= $PMMRecommendations ?>">		
    			<select name="PMMRecommendations[<?=$rowcnt?>]">
    				<option value="null"></option>
    				<option value="No Action Taken">No Action Taken</option>
    			</select>    
    </td>
    ...
    <?php
        ++rowcnt;
    }
    ?>
    The advantage of this is that you now know explicitly WHICH row you are getting in your PHP code.

    It probably makes no difference at all in your scenario, but it does make a *HUGE* difference when dealing with radio buttons and checkboxes: If no radio button in a group is checked or if a given checkbox is unchecked, then if you just use name="somename[]" the unchecked name simply *DISAPPEARS* from the $_POST or $_GET! Example, say you had three checkboxes all named name="whatever[] and say that the user only checked the *THIRD* of those checkboxes. Then, in the PHP code, $_POST["whatever"][0] would have a value but [1] and [2] would not! Completely wrong! So keep this in mind for when you use radio buttons and checkboxes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •