1

I have a query that checks if the value exists in my table. then it stores the result in an array and outputs it in JSON. But my response is always : [{"SELECT EXISTS(SELECT * FROM wp_woocommerce_order_items WHERE order_id = $sdata)":"1"}; I just want to have it output the result (0 or 1 ) here is my code:

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


$query = "SELECT EXISTS(SELECT * FROM wp_woocommerce_order_items WHERE order_id = $sdata)";
if ($result = mysqli_query($conn, $query)) {

    $newArr = array();
    /* fetch associative array */
    while ($db_field = mysqli_fetch_assoc($result)) {
      $newArr[] = $db_field;
   }

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

$conn->close();
?>
getNordic
  • 57
  • 1
  • 7

1 Answers1

2

First of all, I would recommend you to use prepared statements.

Second, you get your query as an array key cause result has no alias, so that's why you see it in your array.

Third, you don't really need EXISTS here.

Forth, you don't need a loop here also:

$query = "SELECT COUNT(*) FROM wp_woocommerce_order_items as item_exists WHERE order_id = $sdata";
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.    
}

Using prepared statements:

$stmt = $mysqli->prepare('SELECT COUNT(*) as item_exists FROM wp_woocommerce_order_items WHERE order_id = ?');
$stmt->bind_param("i", $sdata);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
$stmt->close();

echo json_encode((bool) $row['item_exists']);
freeek
  • 985
  • 7
  • 22
  • with the 1st one I get always false as response no matter if the value exists in the table or not – getNordic Sep 29 '19 at 11:30
  • @getNordic as I don't see other parts of code - just debug the result, check your `$sdata` and give us some hints. – freeek Sep 29 '19 at 11:54