Load data from database according to the number that i clicked

Question

Hello i have a form as you can see in the image below...

enter image description here

... In which form I load the first data from my database table...

enter image description here

what i want is that when i click on a number on the top of the list, to load the appropriate data.

My code is the below (html form code is not included) ...

<?php
                $username = $_SESSION["username"];

                if(isset($_POST['idWork'])){

                    $id = $_POST['idWork'];
                    $job_title = mysql_real_escape_string($_POST["job_title"]);
                    $company = mysql_real_escape_string($_POST["company"]);
                    $website = mysql_real_escape_string($_POST["website"]);
                    $start_date = mysql_real_escape_string($_POST["start_date"]);
                    $end_date = mysql_real_escape_string($_POST["end_date"]);
                    $start_year = mysql_real_escape_string($_POST["start_year"]);
                    $end_year = mysql_real_escape_string($_POST["end_year"]);
                    $work_history = mysql_real_escape_string($_POST["work_history"]);
                    if($start_year > $end_year)
                    {
                        echo '<script>ErrorMessage()</script>';
                        $id=$id-1;
                        $good = false;
                    }
                    else
                    {
                        $good = true;
                    }
                    if($good == true){

                    $query="UPDATE work
                            SET job_title = '$job_title', company = '$company', website = '$website', start_date='$start_date', end_date='$end_date', start_year='$start_year', end_year='$end_year', work_history='$work_history'
                            WHERE id='$id' AND username='$username'";

                        mysql_query($query)or die(mysql_error());
                        if(mysql_affected_rows()>=0){
                            echo "<p>Record Updated<p>";
                            echo "<script type='text/javascript'>;
                                    window.location='addCV.php';
                                  </script>";
                        }else{
                            echo "<p>Error Updating Record<p>";
                            echo "<script type='text/javascript'>;
                                    window.location='addCV.php';
                                  </script>";
                        }
                    }
                }
                else{
                  //first time, initialize as you wish. Probably need to get the first id for this user, using another query
                  $id = 0;
                }

                if($query = mysql_query("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history FROM work WHERE id>'$id' AND username='$username' order by id asc limit 1") or die(mysql_error()))
                {
                    if(mysql_num_rows($query)>=1){
                        while($row = mysql_fetch_array($query)) {
                        $job_title = $row['job_title'];
                        $company = $row['company'];
                        $website = $row['website'];
                        $start_date = $row['start_date'];
                        $end_date = $row['end_date'];
                        $start_year = $row['start_year'];
                        $end_year = $row['end_year'];
                        $work_history = $row['work_history'];
                        }
                    }

                }
            ?>
            <script>
                $(document).ready(function(){
                    $("#updateWork").click(function(){
                        $("#idW").css("display","none");
                        var r = parseInt($("#idW").val(),10)+1;
                        $("#idW").val(r);
                    });
                });
            </script>
            <p><input type="button" value="Work History" id="SlideMeWorkHistoryButton" style="color: #F87F25; background: black; font: bold 22px Tahoma; width: 16em;  height: 2em; border-radius:7px; padding:4px;"/></p>              
            <div id="SlideMeWorkHistoryForm">
                <?php
                $sql = "SELECT id FROM work WHERE username='$username' order by id asc limit 10;";

                $result = mysql_query($sql);
                if ($result != 0) {


                    $num_results = mysql_num_rows($result);
                    for ($i=0;$i<$num_results;$i++) {
                        $row = mysql_fetch_array($result);
                        $id = $row['id'];
                        echo '<a href="' .$id. '">' .$id. '</a>';
                    }

                }

Hi niklakis, do you absolutely have to use `PHP` and `mySQL` for your project? I only ask because you can very easily create what you need using just jquery if you're willing to use parse.com's free services. Check out this demo I made in 1.5 hours that does exactly what you need and uses only jquery to do it: http://dodsoftware.com/sotests/work-history.html (note that I used a "Next" button instead on the numbered links) — Wesley Smith, Mar 12 '15 at 11:47
@DelightedD0D we did something like that but the professor wants to show the numbers which are random according to the number of data we have in our database. He wants to show the numbers of all the data that it might have in the specific table and then when I click on number 2 for example to load the information on the textfields for this specific number. — Waaaaat, Mar 12 '15 at 12:12
That can be added easy enough, do you have to use php and MySQL? — Wesley Smith, Mar 12 '15 at 12:13
@DelightedD0D yes php and MySQL is what we use for this project — Waaaaat, Mar 12 '15 at 12:14
@DelightedD0D do you have to suggest any solution ? Thanks for your time — Waaaaat, Mar 12 '15 at 18:06

Wesley Smith · Accepted Answer · 2015-03-13T02:03:02.930

Try this, changes explained in comments:

<?php
                $username = $_SESSION["username"];

                if(isset($_POST['idWork'])){

                    $id = $_POST['idWork'];
                    $job_title = mysql_real_escape_string($_POST["job_title"]);
                    $company = mysql_real_escape_string($_POST["company"]);
                    $website = mysql_real_escape_string($_POST["website"]);
                    $start_date = mysql_real_escape_string($_POST["start_date"]);
                    $end_date = mysql_real_escape_string($_POST["end_date"]);
                    $start_year = mysql_real_escape_string($_POST["start_year"]);
                    $end_year = mysql_real_escape_string($_POST["end_year"]);
                    $work_history = mysql_real_escape_string($_POST["work_history"]);
                    if($start_year > $end_year)
                    {
                        echo '<script>ErrorMessage()</script>';
                        $id=$id-1;
                        $good = false;
                    }
                    else
                    {
                        $good = true;
                    }
                    if($good == true){

                    $query="UPDATE work
                            SET job_title = '$job_title', company = '$company', website = '$website', start_date='$start_date', end_date='$end_date', start_year='$start_year', end_year='$end_year', work_history='$work_history'
                            WHERE id='$id' AND username='$username'";

                        mysql_query($query)or die(mysql_error());
                        if(mysql_affected_rows()>=0){
                            echo "<p>Record Updated<p>";
                            echo "<script type='text/javascript'>;
                                    window.location='addCV.php';
                                  </script>";
                        }else{
                            echo "<p>Error Updating Record<p>";
                            echo "<script type='text/javascript'>;
                                    window.location='addCV.php';
                                  </script>";
                        }
                    }
                }
                // add the below check 
                else if(isset($_GET['idWork'])){
                  // user clicked on one of the id links to get here
                  // set $id to the value of the GET parameter for key "idWork"
                  $id = $_GET['idWork'];
                }
                else{
                  // first time, initialize as you wish. Probably need to get the first id for this user, using another query
                  // I went ahead and added this for you...
                  if($query = mysql_query("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history FROM work WHERE username='$username' order by id asc limit 1") or die(mysql_error())){
                      if(mysql_num_rows($query)>0){
                        while($row = mysql_fetch_array($query)) {
                             $id = $row['id'];
                        }
                      }
                      else{
                          echo '<script>ErrorMessage()</script>';
                      }
                  }
                }

                if($query = mysql_query("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history FROM work WHERE id>'$id' AND username='$username' order by id asc limit 1") or die(mysql_error()))
                {
                    if(mysql_num_rows($query)>=1){
                        while($row = mysql_fetch_array($query)) {
                        $job_title = $row['job_title'];
                        $company = $row['company'];
                        $website = $row['website'];
                        $start_date = $row['start_date'];
                        $end_date = $row['end_date'];
                        $start_year = $row['start_year'];
                        $end_year = $row['end_year'];
                        $work_history = $row['work_history'];
                        }
                    }

                }
            ?>
            <script>
                $(document).ready(function(){
                    $("#updateWork").click(function(){
                        $("#idW").css("display","none");
                        var r = parseInt($("#idW").val(),10)+1;
                        $("#idW").val(r);
                    });

                    // listen for clicks on the id links
                    $('[data-row-id]').click(function(e){
                            e.preventDefault();
                            var fileName = 'thisFile.php?idWork='; //change "thisFile.php" to the name of this file in your project, the "?" starts the GET parameters, idWork= sets the key for the GET parameter  
                            var id = $(this).data('row-id'); // this gets the id that we stored in the link's data attribute
                            var url = fileName+id;  // then we add that id as the value for the "idWork" key
                            window.location= url; // esentially refresh this page with the id set as a GET parameter and make use of the logic we already have to load the info
                    });
                });
            </script>
            <p><input type="button" value="Work History" id="SlideMeWorkHistoryButton" style="color: #F87F25; background: black; font: bold 22px Tahoma; width: 16em;  height: 2em; border-radius:7px; padding:4px;"/></p>              
            <div id="SlideMeWorkHistoryForm">
                <?php
                $sql = "SELECT id FROM work WHERE username='$username' order by id asc limit 10;";

                $result = mysql_query($sql);
                if ($result != 0) {


                    $num_results = mysql_num_rows($result);
                    for ($i=0;$i<$num_results;$i++) {
                        $row = mysql_fetch_array($result);
                        $id = $row['id'];
                        echo '<a href="#" data-row-id="'.$id.'">' .$id. '</a>';
                    }

                }
                ?>

I get this error Notice: Undefined index: id in E:\xampp\htdocs\CVToolGismo\addCV.php on line 472 And this might be the reason that it doesn't work — Waaaaat, Mar 12 '15 at 19:18
the code in linr 472 is this .... $id = $row['id']; ..... Also i replace the var fileName = 'thisFile.php?idWork='; with this .... var fileName = 'thisFile.php?idWork='; ... As you mentioned in the comment — Waaaaat, Mar 12 '15 at 19:49
Also if I click on a number the error disappear but it doesn't load the data to the form — Waaaaat, Mar 12 '15 at 20:26
I tried your code and I detect that when I click on any number the url on my browser become addCV.php?idWork=undefined , so I can understand that it does not get the id value because if I write the number by myself for example addCV.php?idWork=4 it loads the data — Waaaaat, Mar 12 '15 at 21:53
Right click on one of the links and select "inspect element" , does the data-row-id attribute have a value? — Wesley Smith, Mar 12 '15 at 23:03
no it is not online I am going to check it again and I will inform you :) — Waaaaat, Mar 13 '15 at 08:56
I found 2-3 bugs... 1) is that in the SELECT method --> SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history FROM work WHERE username='$username' order by id asc limit 1 ... if I make the limit 0 the error disappears but i believe that doesnt solve the problem and also when I click on a number the url become like this --> addCV.php?idWork=undefined and if I replace the undefined with a number for exampe addCV.php?idWork=7 then it loads me the data but it loads the data with id=6 — Waaaaat, Mar 13 '15 at 11:54
Right click on one of the links and select "inspect element" , does the data-row-id attribute have a value? — Wesley Smith, Mar 13 '15 at 12:12
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/72924/discussion-between-delightedd0d-and-niklakis). — Wesley Smith, Mar 13 '15 at 12:14
Can you please take a look in the chat room ? We need your help so much :) — Waaaaat, Mar 15 '15 at 12:32
hi my friend can you take a look here please please please (http://stackoverflow.com/questions/29972156/pdo-delete-specified-row-from-table?noredirect=1#comment48065451_29972156) — Waaaaat, Apr 30 '15 at 17:37

score -1 · Answer 2 · answered Mar 08 '15 at 20:00

You can do this with ajax request. Jquery provides us with easy app interface.

There could be endless approaches, but for illustrating purposes, here is how i would do this.

have each anchor element a representative attribute, could be class selector: <a href="#" class="num" change_form_to="1"> 1 </a><a href="#" class="num" change_form_to="3"> 2 </a> ..etc

or you could make jQuery to decide, what information to POST based on element's value.. your preference

see, which number user clicked $('a').on('click',function(){var clicked = $(this).attr('change_form_to')}) so here variable holds this custom attributes value $(this).text() or like this to get text literal jQuery
then make a call to php script $.ajax({type:'POST',url:'dbh.php',data:{idWork:clicked},success: function(res){populate(res)}}) and when success function is called, the response has came for you to call your custom made populate function

All to geather:

$('a').on('click',function(){

   var clicked = $(this).attr('change_form_to');
   $.ajax({
      type:'POST',
      url:'dbh.php',
      data:{idWork:clicked},
      success: function(res){
         console.log(res);
         //populate(res); could look like this if your php script dies with just outputing json_encode on some assoc array 
         var j = JSON.parse(res);
         $('form input.name').value(j.name) ...etc
         }
    })

});

Your question states you want to get data from database but in your sql syntax you have UPDATE logic

lol, I did'n notice yr pasted code area was scrollable. This is the answer, check it! — animaacija, Mar 09 '15 at 11:01
can you please intergrate your code inside my code in order to understand how it work? Thank you for your time — Waaaaat, Mar 11 '15 at 16:19
Just dig in a bit. Once you got basics I gave, you can put it together. — animaacija, Mar 12 '15 at 19:50
You should use data attributes for this. And you dont mention how the OP would need to setup `dbh.php` to return the queried row's information — Wesley Smith, Mar 13 '15 at 02:16

Load data from database according to the number that i clicked

2 Answers2

Linked