关于MySQL查询输出的决策

Question

I tried to make a database query, which makes a query to decide which query should be executed. I use output of this query to make decision. Please see my PHP file below, which contains all the 3 queries.

<?php
  require_once('dbConnect.php');

  $studentid = $_POST['studentid'];
  $classid = $_POST['classid'];
  $date = $_POST['date'];
  $signature = $_POST['signature'];

  $sql = "SELECT count(case when studentid='$studentid' AND classid='$classid' AND endsig is NULL then 1 end) as p
FROM signature";

 $r = mysqli_query($con,$sql);

 $result = array();

 while($row = mysqli_fetch_array($r)){
    array_push($result,array(
        'p'=>$row['p']
    ));
 }

 if(mysql_result($result, 2)==0){
    $sql = "insert into signature (studentid,classid,start,startsig) values ('$studentid','$classid','$date','$signature')";

    if(mysqli_query($con,$sql)){
     echo 'success';
    }
    else{
     echo 'failure';
    }
 }else{
    $sql = "UPDATE signature SET endsig='$signature' WHERE startdate='$date' AND studentid='$studentid'";

    if(mysqli_query($con,$sql)){
     echo 'success';
    }
    else{
     echo 'failure';
    }
 }

  mysqli_close($con);

I try to get an integer value from the first query with use of count. It should summarize occurences of entries in the given database table that match all the conditions. This number is signed with symbol p. I would like to get back value of p from the output with mysql_result() method.

Answer 1

You've done some unnecessary steps in your code. And mysqli_result is something different (a class) to mysql_result(a method to get strings from a result).

$sql = "SELECT count(case when studentid='$studentid' AND classid='$classid' AND endsig is NULL then 1 end) as p FROM signature";

$r = mysqli_query($con,$sql);

// $result = array(); // not needed

// since we only will get one row anyway, there no need to loop through the results and push those to an array
// simply get the one row as array:
$row = mysqli_fetch_array($r);

// now you can work with that array and your wanted value 'p'
if($row['p']==0) {

// leave the rest as is...