MySQL/PHP else if statement with affected rows

Question

I am trying to let to check the a table if there exists a value and if there exists a value it should output as JSON "one" and if it doesn't exist it should output "two". My thought is that With my SELECT EXIST Statement it should only return a row if there exists the value in the table but for some reason it always outputs a row.

Here is the code:

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
if ($result = mysqli_query($conn, $query)) {

    $newArr = array();
    $value = mysqli_fetch_object($result);
    $newArr[] = (bool) $value->item_exists;

    echo json_encode($newArr); // get all products in json format.    
}
*/


/* part where output is just 1 or 0*/
$query = "SELECT EXISTS(SELECT * FROM wp_woocommerce_order_items WHERE order_id = $sdata)";
$myArray = array();
if ($result = mysqli_query($conn, $query)) {
    while($row = $result->fetch_row()) {
            $myArray[] = $row;
    }
    // 2nd operation checkif order available
 if ($conn->affected_rows == 1) {
    echo json_encode($one);
 } else {
    //Success and return new id
    echo json_encode($two);
 }    
    //end 2nd operation
    echo json_encode($myArray);
}$result->close();
    echo json_encode($newArr); // get all products in json format.    
}
    $conn->close();
?>

`select exists( select... )` will return either 0 or 1 so your php will always have a row — Professor Abronsius, Sep 30 '19 at 12:49
https://stackoverflow.com/questions/9953157/mysql-php-check-if-row-exists may give some ideas. — Nigel Ren, Sep 30 '19 at 12:51

Professor Abronsius · Answer 1 · 2019-09-30T13:01:53.963

Could you not simply try something like the following? Assign an alias to the result of the query so you can access it like you would for a regular column name, run the query and extract that pseudo-columnname from the recordset. This does, of course, ignore the sql vulnerability ( or potential vulnerability ) in the sql statement - a prepared statement would be a safer method to employ.

$query = "select exists( select * from `wp_woocommerce_order_items` where `order_id` = $sdata ) as `exists`";

$exists = false;

if( $result = mysqli_query( $conn, $query ) ) {
    $row = $result->fetch_assoc();
    $exists = intval( $row['exists'] );
}

$myArray = array( $exists ? 1 : 2 );
echo json_encode($myArray);

To do similar using a prepared statement you might consider something like this:

$exists = false;

$sql='select exists( select * from `wp_woocommerce_order_items` where `order_id` = ? ) as `exists`';
$stmt=$conn->prepare($sql);
if( $stmt ){
    $stmt->bind_param( 'i', $sdata );
    $res=$stmt->execute();

    if( $res ){
        $stmt->store_result();
        $stmt->bind_result( $exists );
        $stmt->fetch();
        $stmt->free_result();
        $stmt->close();
    }
}


$myArray = array( $exists ? 1 : 2 );
echo json_encode( $myArray );

score 1 · Answer 2 · edited Sep 30 '19 at 13:11

1

Please always use PDO or Prepared MySQLI to avoid SQL injections.
Someone could drop your tables or do other dangerous things that will ruin your data.

Refer to this example:

<?php

    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare("SELECT 1 FROM `wp_woocommerce_order_items WHERE order_id = :sdata");
    $stmt->execute(['sdata' => $sdata]);
    $result = $stmt->fetchColumn();

    $exists = $result ? "one" : "two";

    print_r($exists);

edited Sep 30 '19 at 13:11

Your Common Sense

156,878
40
214
345

answered Sep 30 '19 at 12:58

Thrallix

699
5
20

it is good that you recommend prepared statements but it seems you need a bit more practice with them – Your Common Sense Sep 30 '19 at 13:12
@YourCommonSense There's never enough practise lad. Thanks for the correction. – Thrallix Sep 30 '19 at 13:13

MySQL/PHP else if statement with affected rows

2 Answers2