Page 1 of 1

Checking the value of a dropdowns in sql php

Posted: Sun Sep 11, 2016 9:47 pm
by sarojthapa60
I have two tables in SQL Server database. One table called matrix has all the values to display in a web table and the other table matrix-dropdowns have the values of the cells that have dropdowns. I want to have different background colors for the cell values of dropdowns in the web table, say if q1, then green color, if q2, yellow color. I could not figure out where to create a function to compare the cell values and return background color accordingly. The code snippet is shown below:

Code: Select all

<?php
require_once('include/database.php');

?>
<!DOCTYPE html>
<html>
<head>
</head>
<body>

<section>
    <table id="myTable" class="tablesorter">
    <thead>
    <tr>
     <th>Capacity</th>
     <th>Ownership</th>
     <th>Action</th>
     </tr>
     </thead> 

       <tbody>
        <?php
        $stmt = $conn->prepare("SELECT * FROM MATRIX ORDER BY OBJECTID ASC");
        $stmt ->execute();
        $result = $stmt->fetchAll();
        foreach($result as $row) {
         ?>

          <tr>
          <td><?=$row['Ownership'];?></td>
          <td><?=$row['Capacity'];?></td>
          <td><a href="edit.php?id=<?=$row['OBJECTID'];?>">Edit </a>  </td>

           </tr>
           </tbody>

        <?php
         }
         ?>



Thanks.

Re: Checking the value of a dropdowns in sql php

Posted: Mon Sep 12, 2016 5:51 am
by requinix
Do you want the color to change as the user is changing the values, or based on the initial value it starts with?

Either way, first step is to create the dropdowns without the color-changing part. Can you do that?

Re: Checking the value of a dropdowns in sql php

Posted: Mon Sep 12, 2016 9:17 am
by sarojthapa60
Thanks for your response @requinix. I want the color tostart with the initial value and as the user makes changes, I want the color of the background cell to change accordingly. I have an "edit.php" file where I have created dropdowns . The code snippet is:

Code: Select all

<?php
require_once('include/database.php');

if (isset($_POST['btn_submit'])) {
if (isset($_POST['txt_id'])) {
    $id = $_POST['txt_id'];
} else {
    $id = '';
}


 if (isset($_POST['txt_capacity'])) {                       ////It has dropdowns, namely GREEN, AMBER, RED, BLACK. 
    $capacity = $_POST['txt_capacity'];
} else {
    $capacity = 0;
}

if (isset($_POST['txt_ownership'])) {        
    $ownership = $_POST['txt_ownership'];
} else {
    $ownership = '';
}

 try {
    $stmt = $conn->prepare("UPDATE MATRIX SET  Capacity=:capacity,
                                               Ownership=:ownership
                                               WHERE OBJECTID =:id");


 $stmt->execute(array(':capacity' => $capacity, ':ownership'=>$ownership, ':id' => $id));

 if ($stmt) {


        header('Location:index.php');
        exit();

    }
} catch (PDOException $e) {
    echo $e->getMessage();
}

}

$object_id='';
$capacity = '';
$ownership = '';

if (isset($_GET['id'])) {
        $id = $_GET['id'];
        $stmt = $conn->prepare("SELECT * FROM MATRIX WHERE OBJECTID=:id");
        $stmt->execute(array(':id' => $id));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $object_id = $row['OBJECTID'];
        $capacity = $row['Capacity'];
        $ownership = $row['Ownership'];
 }
 ?>

 <!DOCTYPE html>
 <html>
 <head>
 <title>Edit the Data</title>
 </head>
 <body>
    <h2>Edit the records</h2>

    <form action="" method="post">
        <table border="3px" cellpadding="5px"> 

   <tr>
                    <td>Capacity</td>
                    <td><label>
                            <select name="txt_capacity" class="textfields" id="capacity">
                                <option id="0">Select One</option>
                                <?php
                                require_once('include/database.php');
                                $stmt = $conn->prepare("SELECT * FROM MATRIX_DROPDOWNS");
                                $stmt ->execute();
                                $result = $stmt->fetchAll();
                                foreach($result as $row){
                                    ?>

                                    <option id="<?=$row['OBJECTID'];?>"><?=$row['colors']?></option>
                                <?php } ?>



                            </select>


                        </label>
                    </td>
                </tr>

    <tr>
                    <td>Ownership</td>
                    <td><label>
                            <select name="txt_ownership" class="textfields" id="ownership">
                                <option id="0">Select One</option>
                                <?php
                                require_once('include/database.php');
                                $stmt = $conn->prepare("SELECT * FROM MATRIX_DROPDOWNS");
                                $stmt ->execute();
                                $result = $stmt->fetchAll();
                                foreach($result as $row){
                                    ?>

                                    <option id="<?=$row['OBJECTID'];?>"><?=$row['colors']?></option>
                                <?php } ?>



                            </select>


                        </label>
                    </td>
                </tr>

     <tr>
                <td><label>
                        <input type="hidden" name="txt_id" value="<?= $object_id; ?>">
                    </label>
                </td>
                <td><label><input type="submit" name="btn_submit" value="Submit">
                    </label>
                </td>
            </tr>

        </table>
    </form>

Re: Checking the value of a dropdowns in sql php

Posted: Mon Sep 12, 2016 9:55 am
by requinix
1. For each option, how do you know what the color should be?
2. How do you want to color it? Background of the table cell? Of the label?
3. Are some of the colors dark? How will you deal with black text on a dark background? As an alternative to a background color, how about a small square somewhere close that uses a color? That way it will not conflict with the text.

Before you can use Javascript to change the color, you need to decide what the coloring will look like using HTML and/or CSS. Then you can write Javascript to create the same effect dynamically.
Add the following row to the table:

Code: Select all

    <tr>
                    <td>Demonstration</td>
                    <td><label>
                            <select class="textfields">
                                <option id="0">Select One</option>
				<option id="1" selected>A</option>
				<option id="2">B</option>
                            </select>
                        </label>
                    </td>
                </tr>
Now modify that so that it has the right color (background color, square, whatever) when the page loads. What is the new markup that shows the coloring?

Re: Checking the value of a dropdowns in sql php

Posted: Mon Sep 12, 2016 10:17 am
by sarojthapa60
1. I have a few fields (columns) that have values Q1, Q2, Q3, Q4 for Q rating, F1,F2,F3,F4 for F rating and C1, C2,C3, and C4 for C Rating. If the value is Q1, the color needs to be green.Q2=>amber, Q3=>RED, Q4=>black. I have other fields too whose values are GREEN, RED, AMBER, and BLACK. GREEN=>green.
2.I just want to color the background of the table cell.
3. If the background color is black, we can make the text color white or like you said, a small square should work.

I am going to try the code you provided and see the results and I will let you know. Thanks