Suggestions on best approach on project

[adishardis]

Hi,

I have a database table filled with interesting data. On my webpage I would like to let a user pick two rows in the table and compare the data to see how it differs. Picking what rows to compare would be done by two dropdown lists.

The data in the table is structured so that in a single row: the 10 first fields are descriptive of the rowdata, next ten are "one set of values (totals)" and next ten "another set of values (a breakdown of the totals)" and so on and i would like the user to be able to switch between these "sets" in the rows they already choose in the dropdown.

what I have done so far is constructing a php page with userlogin (thanks to angry-frog.com) designed the overall look of the page and made a semifunctional dropdown that displays the approriate rows from the table depending on who the user is.

I dove into this headfirst trying to code it but bumped into several hurdles and recently started a thread about one of them (http://www.daniweb.com/web-development/php/threads/426093/dropdown-to-output-corresponding-row-values-but-not-all)

but now I'm thinking of taking a step back and getting input on what the best approach is regarding getting this to work. I have limited knowlage in php and mysql (wich i just started learning) but I'm willing to look into any language that would make this a smooth ride :)

Humble for any and all suggestions :)
Adam

[phorce]

his headfirst trying to code it but bumped into several hurdles and recently started a thread abo

Hello,

I'm kinda confused.. So, basically, you have three drop down menues that contain information that is stored inside a table, and, depending on which item in the drop down menu they choose, changes the next one and the one after that?

As someone mentioned, this can be done using Ajax and have the data passed through that way.. This is a good solution to this problem as it doesn't require the page to be reloaded for the data to be loaded.

Let me know, I may be able to give you some pointers, if, this is actually what you want to do.

[adishardis]

Hi again,

Hope everyone had a happy "Midsummer" and is sufficiently recoverd by now :)

I want to have 2 dropdowns so that the user can display and compare data stored in the table. Initially once the user has selected the rows to display, the first 30 fields is displayed and then via four buttons he/she can display what section of fields in the rows choosen ex. 31-60, 61-90 and so on.

As you suggested phorce it would be awsome if, for example, the choosen row in the first dropdown contains "ODB" in the first field, only rows containing "ODB" would be available for comparison in the second dropdown!

I would really appreciate som pointers on using ajax to accomplish this!

Take care!
Adam

[diafol]

So like this? You have a list of records.

<select name="first">
  <option value = "108">text108</option>
  ...
</select>
<select name="second">
  <option value = "108">text108</option>
  ...
</select>

Then when this is sent to the server, you retrieve the records:

$first = intval($_POST['first']);
$second = intval($_POST['second']);
$order = ($first > $second) ? ' DESC' : '';
$r = mysql_query("SELECT * FROM table WHERE id = $first OR id = $second ORDER BY id$order");
if(mysql_num_rows($r) == 2){
    while($d = mysql_fetch_array($r)){
        $myarray[] = $d;
    }
}
//the above gives $myarray[0] (first dropdown id data) and $myarray[1]  (second dropdown id data)

NOW you have the choice - either load all the data into a json object and place it into js to prevent page reload on button press, or leave it to vanilla php or Ajax and run a DB query every time you want the next set of fields. If you need the latter, you'll need to alter the SQL slightly to include only the fields you need in response to a button click.

If this data rarely changes and your users won't have js turned off, you may benefit from the former approach:

BTW - you could just run the encode on the main array itself, but I've split them here:

$firstdata = json_encode($myarray[0]);
$seconddata = json_encode($myarray[1]);

Once you do this you can stuff them into js variables on page load:

var first = <?php echo $firstdata;?>;
var second = <?php echo $seconddata;?>;

If this is on page load, fill the html table with data from php array ($myarray). Otherwise, in reponse to a button click, you'll want to use JS to replace the existing html data with the json data.

It's actually easier than I've made it sound. :)

[adishardis]

Hi again,

I'm so emberassed! Even though you've done so much it's still not getting through all the way to me :-/

Ive done a mockup of what the endresult could look like so we are definitely on the same page! http://adam.synology.me/test.php

In that picture I skipped the buttons and instead maybe I'll have it all visible from the getgo to the right of the "arrow" column, so that column would then show fields 27-42 and then another column to the right showing fields 43-58 and so on.

The table populating the dropdown will be growing so I cant have "prefilled alternatives" in the dropdown but will be getting the appropriate rows to fill the dropdown. Can I use my dropdowns with your solution even though mine are being populated from the table?

Heres the code for my dropdowns:

<?php 
  mysql_connect('xxx', 'xxx', 'xxx');
mysql_select_db('xxx');




$result12 = mysql_query("SELECT foretag FROM users WHERE username ='$session->username'");
$rows = mysql_fetch_array( $result12 );
$test = $rows['foretag'];

    $sql = "SELECT * FROM matningar WHERE foretag = '$test' AND matningstyp ='odb'";  
    $result = mysql_query($sql) or die (mysql_error());  
    while ($row = mysql_fetch_array($result)) 
    { 
            $matningsnr=$row["matningsnr"]; 
            $namn=$row["namn"];  
            $options.="<OPTION VALUE=\"$matningsnr\">".$namn; 
    } 


  ?> 
            <option><? echo $dropdown1 ?>
            <? echo $options ?>
            </option>
        </select>
    </form>
</p>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> 
    <label for="select"><select name="input2" value="Select2" size="1" onChange="this.form.submit()"> 
<p>
    <?php 
  mysql_connect('xxx', 'xxx', 'xxx');
mysql_select_db('xxx');

    $sql2 = "SELECT * FROM matningar WHERE matningstyp ='mci' AND affarsomrade = '$affarsomrade'  ";
    $result22 = mysql_query($sql2) or die (mysql_error());  
    while ($row = mysql_fetch_array($result22)) 
    { 
            $matningsnr2=$row["matningsnr"]; 
            $namn2=$row["namn"];  
            $optionss.="<OPTION VALUE=\"$matningsnr2\">".$namn2; 
    } 


  ?> 
            <option><? echo $dropdown2 ?>
            <? echo $optionss ?>
            </option>
        </select>
    </form>

It's probably a mess but I'm actually able to extract any field from the choosen row in the dropdown. However if I choose something from the other dropdown the first values dissapears.

I can't seem to figure out how to use your code togheter with my dropdowns...

I'm gonna try to get some time tonight after work when the wife is asleep to read up on json_encode. I am much obliged that you have taken time to try and help me!

Peace
Adam

[diafol]

OK, I'd load all the data, something like this:

$output = "Choose two values to compare...";
if(!empty($_POST['first']) && !empty($_POST['second']) && intval($_POST['first']) !== intval($_POST['second'])){
    $first = intval($_POST['first']);
    $second = intval($_POST['second']);
    $ord = ($second <= $first) ? 'DESC' : '';
    $r = mysql_query("SELECT * FROM FROM matningar WHERE id = $first OR id = $second ORDER BY id $ord");
    if(mysql_num_rows($r) == 2){
        while($d = mysql_fetch_array($r)){  
            $arr[] = $d;
        }
        $output = createHTML($arr);
    }else{
        $output = "The values must be different and exist in the list. " . $output; 
    }
}

function createHTML($arr){
    $first = $arr[0];
    $second = $arr[1];
    $fieldnames = array_keys($arr[0]);

    $table1 = "<div id=\"tabcontainer1\"><table id=\"table1\"><thead><tr><th>Nykketal</th><th>{$first['name']}</th><th>{$second['name']}</th><th>Differans</th></tr></thead><tbody>";

    for($x=10;$x<=13;$x++){
        if(abs($first[$x] - $second[$x]) <= 5){
            $img = "no_move";
        }elseif($first[$x] - $second[$x] > 5){
            $img = "up";
        }else{
            $img = "down";
        }
        $table1 .= "<tr><td>$fieldnames[$x]</td><td>{$first[$x]}%</td><td>{$second[$x]}%</td><td><img src=\"images/$img.png\" /></td></tr>";   
    }
    $table1 .= "</tbody></table></div>";

    //do similar for rest of tables
    //then concatenate them:
        $tables = $table1 . $table2 . $table3;

    return $tables;
}

//wherever you need the output to screen
echo $output;

Then I'd use the id of the div tab (e.g. 'tabcontainer1') to act as a hook for hide/show under javascript. E.g. with jQuery and button:

$('#nextButton').click(function(){
   //hide show or TOGGLE tables(s)
});

[adishardis]

Man finally I got it! Thank you! :) There was an extra FROM argument in the code and id needed to be substituted with the name of my field which was matningsnr! WOHOOO :D

Thank you for taking time helping me out!

Sinceraly
Adam

[adishardis]

One quick question more if you dont't mind... :)

In my page i have a container div, leftdiv, contentdiv and rightdiv.

Do I have to change the code if I want to have the dropdowns in the leftdiv and the $output in the contentdiv?

I tried to just move the inputform to the leftdiv but that didn't work?!

must be a simple fix right?

Cheers
Adam

[diafol]

Post your code and tell us again what you'd like where

[adishardis]

You sir are an gentleman and a scholar :)

When I have all the code in an own page (content.php) it works like a charm. But when I try to move it around to have the dropdown in <div id="leftnavs"> and the output in the <div id="contents"> it gets F***ed upp when i hit the submit button(kör). Below you can see my latest try but to no avail.

<div id="containers">
    <div id="leftnavs">
        <?php
            echo "Welcome <b>$session->username</b>, you are logged in. <br><br>";
        ?>
        <?php
if(!empty($_POST['first']) && !empty($_POST['second']) && intval($_POST['first']) !== intval($_POST['second'])){
    $first = $_POST['first'];
    $second = $_POST['second'];
    $ord = ($second <= $first) ? 'DESC' : '';
    $r = mysql_query("SELECT * FROM matningar WHERE matningsnr = $first OR matningsnr = $second ORDER BY matningsnr $ord");
    if(mysql_num_rows($r) == 2){
        while($d = mysql_fetch_array($r)){  
            $arr[] = $d;
        }
        $output = createHTML($arr);
    }else{
        $output = "The values must be different and exist in the list. " . $output; 
    }
}
function createHTML($arr){
    $first = $arr[0];
    $second = $arr[1];
    $fieldnames = array_keys($arr[0]);
    $nycknamnDIV1[10]="Top";
    $nycknamnDIV1[11]="In";
    $nycknamnDIV1[12]="Pre";
    $nycknamnDIV1[13]="Int";
    $nycknamnDIV1[14]="Kam";
    $nycknamnDIV1[15]="Mål";
    $nycknamnDIV1[16]="Utf";
    $nycknamnDIV1[17]="Taj";
    $nycknamnDIV1[18]="Erb";
    $table1 = "<div id=\"container\"><table id=\"table-3\" cellspacing=\"0\"><thead><tr><th>Kampanjprestation</th><th>Totalt</th><th>Benchmark</th><th>Differans</th></tr></thead><tbody>";
    for($x=10;$x<=13;$x++){
        if(abs($first[$x] - $second[$x]) <= 5){
            $img = "no";
        }elseif($first[$x] - $second[$x] > 5){
            $img = "up";
        }else{
            $img = "down";
        }
        $table1 .= "<tr><td>$nycknamnDIV1[$x]</td><td>{$first[$x]}%</td><td>{$second[$x]}%</td><td><img src=\"images/$img.png\" /></td></tr>";   
    }
    $table1 .= "</tbody></table></div>";
    //do similar for rest of tables
        $table2 = "<div id=\"container\"><table id=\"table-3\" cellspacing=\"0\"><thead><tr><th>DM-nyckeltal</th><th>Totalt</th><th>Benchmark</th><th>Differans</th></tr></thead><tbody>";
    for($x=14;$x<=18;$x++){
        if(abs($first[$x] - $second[$x]) <= 5){
            $img = "no";
        }elseif($first[$x] - $second[$x] > 5){
            $img = "up";
        }else{
            $img = "down";
        }
        $table2 .= "<tr><td>$nycknamnDIV1[$x]</td><td>{$first[$x]}%</td><td>{$second[$x]}%</td><td><img src=\"images/$img.png\" /></td></tr>";   
    }
    $table2 .= "</tbody></table></div>";
    //then concatenate them:
        $tables = $table1 . $table2 . $table3;
    return $tables;
}

?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> 
    <label for="select"><select name="first" value="Select2" size="1"> 
<?php
$result12 = mysql_query("SELECT * FROM users WHERE username ='$session->username'");
$rows = mysql_fetch_array( $result12 );
$test = $rows['foretag'];
$affarsomrade = $rows['affarsomrade'];
    $sql = "SELECT * FROM matningar WHERE foretag = '$test' ";  //AND matningstyp ='mci' AND affarsomrade ='$affarsomrade'
    $result = mysql_query($sql) or die (mysql_error());  
    while ($row = mysql_fetch_array($result)) 
    { 
            $matningsnr=$row["matningsnr"]; 
            $namn=$row["namn"];  
            $options.="<OPTION VALUE=\"$matningsnr\">".$namn; 
    } 
  ?> 
            <option>
            <? echo $options ?>
            </option>
        </select>

    <label for="select"><select name="second" value="Select2" size="1"> 
<p>
    <?php 

    $sql2 = "SELECT * FROM matningar WHERE foretag = '$test' ";// AND affarsomrade = '$affarsomrade' 
    $result22 = mysql_query($sql2) or die (mysql_error());  
    while ($row = mysql_fetch_array($result22)) 
    { 
            $matningsnr2=$row["matningsnr"]; 
            $namn2=$row["namn"];  
            $optionss.="<OPTION VALUE=\"$matningsnr2\">".$namn2; 
    } 
  ?> 
            <option>
            <? echo $optionss ?>
            </option>
        </select><input type="submit" value="kör"/>
    </form>
</div>
<div id="rightnavs">
    </div>
<div id="contents">
    <h2>Analys</h2>
    <?php
        echo $output;
    ?>
    </div>
        </div>   

[adishardis]

Forget about my earlier post, I've got it working now. Went through the code and cleaned it and now it's working!

Thanks for your time and patience!

Sinceraly!
Adam