How to retrieve boolean variable using mysqli in p

2019-02-28 16:54发布

站内问答 / PHP
820 2 5

This is the my table 

CREATE TABLE IF NOT EXISTS `assigned_project_tester` (
  `Assigned_id` int(11) NOT NULL AUTO_INCREMENT,
  `Project_manager` varchar(45) DEFAULT NULL,
  `Project_id` varchar(45) DEFAULT NULL,
  `Tester_name` varchar(45) DEFAULT NULL,
  `Test_creation` tinyint(1) NOT NULL,
  `Test_execution` tinyint(1) NOT NULL,
  PRIMARY KEY (`Assigned_id`)
)

Here are two field named Test_creation and Test_execution which stores boolean values I want to print 'yes' or 'no' on basis of value stored in the database.I dont know how to retrieve boolean values.

                <!DOCTYPE html>
                <!--
                To change this license header, choose License Headers in Project Properties.
                To change this template file, choose Tools | Templates
                and open the template in the editor.
                -->
                <html>
                    <head>
                        <meta charset="UTF-8">
                        <title></title>
                    </head>
                    <body>
                        <?php
                        include 'header.php';
                        include 'footer.php';
                        include 'config.php';





                        $sqluser = "SELECT * FROM user_master where Username='meet.patel'and Role='Tester' ";
                        $result = $conn->query($sqluser);
                        if (!$result) {
                            echo 'Could not run query: ' . mysql_error();
                            exit;
                        } else {

                            $row = mysqli_fetch_assoc($result);
                        }

                        $sql1 = "SELECT Project_id FROM assigned_project_tester  where Tester_name IN (SELECT Username FROM user_master where Username='meet.patel') ";
                        $result1 = $conn->query($sql1);
                        ?>



                        <div id="page-wrapper">
                            <div class="row">
                                <div class="col-lg-12">
                                    <h1 class="page-header">View Profile</h1>
                                </div>
                                <div class="col-lg-6">
                                    <div class="panel panel-default">

                                        <!-- /.panel-heading -->
                                        <div class="panel-body">
                                            <!-- Nav tabs -->
                                            <ul class="nav nav-pills">

                                                <li class=><a href="#profile-pills" data-toggle="tab">Profile</a>
                                                </li>
                                                <li><a href="#project-assigned" data-toggle="tab">Project</a>
                                                </li>
                                                <li><a href="#change-Password" data-toggle="tab">Change Password</a>
                                                </li>
                                            </ul>

                                            <!-- Tab panes -->
                                            <div class="tab-content">

                                                <div class="tab-pane fade" id="profile-pills">
                                                    <div class="panel-body">
                                                        <div class="table-responsive">
                                                            <table class="table">
                                                                <thead>

                                                                </thead>
                                                                <tbody>
                                                                    <tr>
                                                                        <td>Username:</td><td><b> <?php echo $row['Username'] ?></b></td>
                                                                    </tr>
                                                                    <tr><td></td></tr>
                                                                    <tr>
                                                                        <td>Full Name:</td><td><b> <?php echo $row['Full_name'] ?></b></td>
                                                                    </tr>
                                                                    <tr><td></td></tr>
                                                                    <tr>
                                                                        <td>Email: </td><td><b><?php echo $row['Email'] ?></b></td>
                                                                    </tr>
                                                                    <tr><td></td></tr>
                                                                    <tr>
                                                                        <td>Contact no:</td><td><b> <?php echo $row['Contact_no'] ?></b></td>
                                                                    </tr>
                                                                    <tr><td></td></tr>


                                                                    <tr>

                                                                        <td>Role: </td><td><b><?php echo $row['Role'] ?></b></td>

                                                                    </tr>


                                                                </tbody>
                                                            </table>







                                                        </div>
                                                    </div>
                                                </div>



                                                <div class="tab-pane fade" id="project-assigned">


                                                    <div class="panel-body">
                                                        <div class="table-responsive">
                                                            <table class="table">
                                                                <thead>
                                                                    <tr>

                                                                        <th>Working for Project</th>
                                                                        <th  align="center"colspan="2" >Permission</th>
                                                                        <th></th>
                                                                    </tr>
                                                                    <tr>
                                                                        <th></th>
                                                                        <th >Creation of Testcases</th>
                                                                        <th>Execution of Testcases</th>
                                                                    </tr>
                                                                </thead>
                                                                <tbody>

                                                                    <?php
                                                                    $i = 1;

                                                                    while ($store = $result1->fetch_assoc()) {
                                                                        $proid = $store['Project_id'];
                                                                        $sql4 = "select Project_title from project_master where Project_id='" . $proid . "'    ";
                                                                        $resultprot = $conn->query($sql4);
                                                                        $sql5 = "select Test_creation,Test_execution from assigned_project_tester where Project_id =' " . $proid . "'   ";
                                                                          $resultper= $conn->query($sql5);

                                                                        while ($rowse = $resultprot->fetch_assoc()) {
                                                                            echo '<tr><td><b>' . $i . '&nbsp;' . $rowse['Project_title'] . '</b></td></tr>';
                                                                          $i++;
                                                                          if (!$resultper) {
                                                                            echo 'Could not run query: ' . mysql_error();
                                                                            exit;
                                                                        } else {

                                                                            $rowper = mysqli_fetch_assoc($result);
                                                                            $a=$rowper['Test_creation'];
                                                                            $b=$rowper['Test_creation'];
                                                                            if($a==0 && $b=1)
                                                                            {
                                                                                echo 'execution permission assigned';

                                                                            }
                                                                            else if($a==1 && $b=0)
                                                                            {
                                                                                echo 'creation permission assigned';
                                                                            }

                                                                            else if($a=1 && $b=1)
                                                                            {
                                                                                echo 'both permission assigned';
                                                                            }
                                                                           else 
                                                                               {
                                                                               echo 'No permission assiged';
                                                                               }
                                                                        }




                                                                        }




                                                                    }

                                                                    ?>






                                                                </tbody>
                                                            </table>
                                                        </div>
                                                        <!-- /.table-responsive -->
                                                    </div>
                                                    <!-- /.panel-body -->

                                                    <!-- /.panel -->




                                                </div>
                                                <div class="tab-pane fade" id="change-Password">
                                                    <div class="col-lg-12">
                                                        <div class="panel panel-default">
                                                            <div class="panel-heading">

                                                            </div>
                                                            <div class="panel-body">
                                                                <div class="dataTable_wrapper">
                                                                    <form action="changePassword.php" method="POST" role="form">
                                                                        <div class="col-lg-6">
                                                                            <div class="form-group">
                                                                                <label>Current Password</label>
                                                                                <input class="form-control" name="curr" type="password" id="curr" required="required"/>
                                                                            </div>
                                                                            <div class="form-group">
                                                                                <label>New Password</label>
                                                                                <input class="form-control" name="newpass" type="password" id="newpass" pattern=".{6,}" title="Must Contain Six or more characters" required="required"/>
                                                                            </div>
                                                                            <div class="form-group">
                                                                                <label>Confirm Password</label>
                                                                                <input class="form-control" name="confirmpass" type="password" id="confirmpass" pattern=".{6,}" title="Must Contain Six or more characters" required="required"/>
                                                                            </div>

                                                                            <button type="submit" name='submit' class="btn btn-primary" id="submit" >Change Password</button>
                                                                            <button type="reset" class="btn btn-primary">Reset</button>
                                                                        </div>
                                                                    </form>
                                                                    <?php
                                                                    if ($_SERVER["REQUEST_METHOD"] == "POST") {
                                                                        $curr = (filter_input(INPUT_POST, 'curr'));
                                                                        $new = (filter_input(INPUT_POST, 'newpass'));
                                                                        $confirm = (filter_input(INPUT_POST, 'confirmpass'));
                                                                        if ($new == $confirm) {
                                                                            $sql = $conn->prepare("UPDATE login SET password = ? WHERE Username=? ");
                                                                            $sql->bind_param('ss', $new, $_SESSION['username']);
                                                                            if ($sql->execute()) {
                                                                                $sql->close();
                                                                                echo 'Password Updated Sucessfully';
                                                                            } else {
                                                                                $sql->close();
                                                                                echo "Error:<br>" . mysqli_error($conn);
                                                                            }
                                                                        } else {
                                                                            echo "Password doesn't match";
                                                                        }
                                                                    }
                                                                    ?>
                                                                </div>
                                                            </div>

                                                        </div>
                                                    </div>


                                                </div>

                                            </div>
                                        </div>
                                        <!-- /.panel-body -->
                                    </div>
                                    <!-- /.panel -->
                                </div>
                                <!-- /.col-lg-12 -->
                            </div>
                            <div class="row">
                                <div class="col-lg-4">
                                    <div class="panel panel-default">

                                        <div class="panel-body">
                                            <div class="dataTable_wrapper">

                                                <div class="panel-heading">
                                                    <b></b>
                                                </div>
                                                <!-- /.panel-heading -->



                                            </div>

                                        </div>


                                    </div> 
                                </div> 



                            </div>
                        </div>
                        <!-- jQuery -->
                        <!--<script src="../bower_components/jquery/dist/jquery.min.js"></script>-->

                        <!-- Bootstrap Core JavaScript -->
                        <!--<script src="../bower_components/bootstrap/dist/js/bootstrap.min.js"></script>-->

                        <!-- Metis Menu Plugin JavaScript -->
                        <script src="../../bower_components/metisMenu/dist/metisMenu.min.js"></script>

                        <!-- DataTables JavaScript -->
                        <script src="../../bower_components/DataTables/media/js/jquery.dataTables.min.js"></script>
                        <script src="../../bower_components/datatables-plugins/integration/bootstrap/3/dataTables.bootstrap.min.js"></script>

                        <!-- Custom Theme JavaScript -->
                        <script src="../../dist/js/sb-admin-2.js"></script>

                        <!-- Page-Level Demo Scripts - Tables - Use for reference -->
                        <script>
                            $(document).ready(function () {
                                $('#dataTables-example').DataTable({
                                    responsive: true
                                });
                            });
                        </script>
                    </body>
                </html>

标签： php mysqli
举报
2条回答
啃猪蹄的小仙女
2楼-- · 2019-02-28 17:14

In MySQL BOOL and BOOLEAN types are an alias for TINYINT(1) so if you insert true or false in the database you'll get an 1 or 0.

If you want to retreive values as yes or no, you'll need to do it with an if

$myBool = "";
if($result['Test_creation'] == 0){
  $myBool = "no";
} else {
  $myBool = "yes";
}

You can also directly put it into the query with CASE

SELECT CASE WHEN Test_creation <> 0 THEN "yes" ELSE "no" END
查看更多
0人赞 添加讨论(0) 举报
Viruses.
3楼-- · 2019-02-28 17:26

try like this,

<?php  $query = "SELECT Test_creation, Test_execution FROM assigned_project_tester";

if ($result = $mysqli->query($query)) {
    while ($row = $result->fetch_assoc()) {
                if($row['Test_creation'] == 0){
                  $val = "no";
                } else {
                  $val = "yes";
                }
    }
    $result->free();
}
$mysqli->close();?>
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)