retrieving data from db table to populate links

Question

Sorry I wasn't sure how to describe a heading for this problem I'm having. :/ I'm in the process of creating a CRUD application and I'm in the update stage. It's updating the in the database wonderfully.

In the process of updating the information in the database, I created links to retrieve the data in the db table. I'm calling the list by the id (which is auto-incremented) I can see all the id numbers in the table, but when it displays on the web page it's only displaying the last id number in the table, but displaying all of the report names correctly. I'm not sure why.

Sorry this is hard to explain, I have attached a username and password along with the site, and snippet of the code, hopefully someone can see something that I can't.

http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/index.php user: Test1 password: test1234 to get to the update page you'll need to click update reports.

this is my update page:

<?php include("includes/session.php"); ?>

<?php include("includes/reports_mysql_connect.php"); ?>
<?php

    $id = $_GET['id'];

    if(!isset($id)){
        $x = mysql_query('SELECT id FROM Reports ORDER BY name') or die(mysql_error());
        while($row = mysql_fetch_array($x)){
            $id = $row['id'];
        }
    }

    //step 3: user submits changes, updates DB to show new information
    if(isset($_POST['submit'])){

        $new_sale = $_POST['sale'];
        $new_questiontwo = $_POST['questiontwo'];
        $new_questionthree = $_POST['questionthree'];
        $new_questionfour = $_POST['questionfour'];
        $new_questionfive = $_POST['questionfive'];
        $new_questionsix = $_POST['questionsix'];
        $new_questionseven = $_POST['questionseven'];
        $new_questioneight = $_POST['questioneight'];

        echo "$new_sale | $new_questiontwo" ; 

        mysql_query("UPDATE Reports SET

            sale = '$new_sale',
            questiontwo = '$new_questiontwo',
            questionthree = '$new_questionthree',
            questionfour = '$new_questionfour',
            questionfive = '$new_questionfive',
            questionsix = '$new_questionsix',
            questionseven = '$new_questionseven',
            questioneight = '$new_questioneight'

            WHERE id = '$id' ") or die(mysql_error());
    }


    //step 1: Retrieve data to create links
    $result = mysql_query("SELECT * FROM Reports") or die(mysql_error());

    while($row = mysql_fetch_array($result)){
        $sale = $row['sale'];
        $questiontwo = $row['questiontwo'];
        $questionthree = $row['questionthree'];
        $questionfour = $row['questionfour'];
        $questionfive = $row['questionfive'];
        $questionsix = $row['questionsix'];
        $questionseven = $row['questionseven'];
        $questioneight = $row['questioneight'];

        //$id = $row['id'];
        //echo $id;

        echo "<div> $id <a href=\"updated-reports.php?id=$id\">$sale</a></div>";
    }

    //Step 2: Retrieve Data for selected report only to prepopulated form.

    $result = mysql_query("SELECT * FROM Reports WHERE id = '$id'") or die(mysql_error());
    while($row = mysql_fetch_array($result)){

        $thissale = $row['sale'];
        $thisquestiontwo = $row['questiontwo'];
        $thisquestionthree = $row['questionthree'];
        $thisquestionfour = $row['questionfour'];
        $thisquestionfive = $row['questionfive'];
        $thisquestionsix = $row['questionsix'];
        $thisquestionseven = $row['questionseven'];
        $thisquestioneight = $row['questioneight'];

        //$thisid = $row['id'];
        //echo $id;
    }

?>



<?php include("../includes/admin-leads-header.php") ?>

<div class="container">
    <div class="sixteen columns">
         <section class="fifteen columns">
            <div class="general">
                <div class="project-leads">

                    <?php

                        if($session->logged_in){
                        if($session->isAdmin()){
                        }

                        echo "<h3>Hello $session->username</h3>";
                        echo "<h5>Please enter your sales leads reports</h5>";
                    ?>

                    <div class="project-leads-reports">
                        <form name="contactform" id="contactform" method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
                            <ul>
                                <li>
                                    <span class="required">*</span>
                                    <label for="name">From: </label>
                                    <input type="text" name="name" id="name" value="<?php echo $session->username?>" />
                                    <span class="error"></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="email">E-mail Address: </label>
                                    <input type="text" name="email" id="email" value="<?php if($form->value("email") == ""){
                                            echo $session->userinfo['email'];
                                        }else{
                                            echo $form->value("email");
                                        }?>" />
                                    <span class="error"></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="boss">Regional Sales Manager E-mail Address:</label>
                                    <input type="text" name="boss" id="boss" value="<?php if($form->value("boss") == ""){
                                            echo $session->userinfo['boss'];
                                        }else{
                                            echo $form->value("boss");
                                        }?>" />
                                    <span class="error"></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="Sales">Sales Lead Subject:</label>
                                    <input type="text" name="sale" id="sale" value="<?php echo trim($thissale);?>" />
                                    <span class="error"></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="questiontwo">When did you followup with the General Contractor?</label>
                                    <textarea name="questiontwo"  id="questiontwo" ><?php echo trim($thisquestiontwo);?></textarea>
                                    <span class="error"><?php echo $strValidationMessage2; ?></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="questionthree">Who is/are the contractors working under the General Contractor?</label>
                                    <textarea name="questionthree" id="questionthree"><?php echo trim($thisquestionthree);?></textarea>
                                    <span class="error"><?php echo $strValidationMessage3; ?></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="questionfour">Did the General Contractor have their own subcontractors, or did we refer our contractor customers to them?</label>
                                    <textarea name="questionfour"  id="questionfour"><?php echo trim($thisquestionfour);?></textarea>
                                    <span class="error"><?php echo $strValidationMessage4; ?></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="questionfive">Can we or did we submit a price?</label>
                                    <textarea name="questionfive" id="questionfive" ><?php echo trim($thisquestionfive);?></textarea>
                                    <span class="error"><?php echo $strValidationMessage5; ?></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="questionsix">Did we win and why did we win or did we lose or why did we lose?</label>
                                    <textarea name="questionsix" id="questionsix" ><?php echo trim($thisquestionsix);?></textarea>
                                    <span class="error"><?php echo $strValidationMessage6; ?></span>
                                </li>
                                <li>
                                    <span class="required">*</span>
                                    <label for="questionseven">What is being done to progress any sales forward with this Project Lead?</label>
                                    <textarea name="questionseven" id="questionseven" ><?php echo trim($thisquestionseven);?></textarea>
                                    <span class="error"><?php echo $strValidationMessage7; ?></span>
                                </li>
                                <li>
                                    <span class="required"></span>
                                    <label for="questioneight">Did you know about this project before it was assigned and or any additional comments?</label>
                                    <textarea name="questioneight"  id="questioneight"><?php echo trim($thisquestioneight);?></textarea>
                                    <span class="error"></span>
                                </li>
                                <li>
                                    <input type="submit" name="submit" id="submit" value="Submit" />
                                </li>
                            </ul>
                        </form>
                    </div><!--end of project lead reports -->   


                <?php }?>



                </div><!--end of project-leads-->
            </div><!--end of general-->
        </section>
    </div> 
</div>


<?php include("../includes/admin_footer.php") ?>

my SQL DB Table:

Valid XHTML http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/68.178.136.47.png. link to image:http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/68.178.136.47.png

image isn't great to look at on here.

Also I have a second question, related to this question. When a user logs in for example "Test1", is there a way to only show Test ones reports from the database?

Below are the table structures for the Reports, and the users, respectively.

Valid XHTML http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/users.png. http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/users.png

Valid XHTML http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/Reports.png. http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/Reports.png

thanks in advance... I've been trying to figure this out since yesterday.

Answer 1

In your while loop that's outputting the reports, $id is equal to where it's set at the top of the page. Try the following in your loop:

echo '<div>' . $row['id'] . '<a href="updated-reports.php?id=' . $row['id'] . '">' . $sale . '</a></div>';

Thanks,

Andrew