PHP: displaying NULL but actually contains data

Question

product table structure is given below

    CREATE TABLE `products` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(6) unsigned NOT NULL,
  `name` varchar(30) NOT NULL,
  `unit_id` int(6) unsigned NOT NULL,
  `brand_id` int(6) unsigned NOT NULL,
  `orignalCost` int(30) NOT NULL,
  `saleprice` int(30) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `fk-to-uom` (`unit_id`),
  KEY `fk-to-brand` (`brand_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `fk-to-brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=230 DEFAULT CHARSET=utf8mb4

Can someone please tell me what mistake i'm making as var_dump() displays the NUll values......is there any mistake in mysqli_fetch_array? it is showing like that "array(1) { [0]=> NULL } "

$selected_items_values = $_POST['product_id']; 
   $prices = [];
   foreach($selected_items_values as $prud)
   {
     if(isset($prud))
     {
      $priceSql = "SELECT saleprice from products where id = ' $prud ' ";
      $price=mysqli_query($db,$priceSql);
      $price = mysqli_fetch_array($price);
      array_push($prices , $price);
      var_dump($prices);

     } 
  }  

Answer 1

EDITED ANSWER

Here is sample table I've created. (I removed foreign keys)

CREATE TABLE `products` (
 `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
 `category_id` int(6) unsigned NOT NULL,
 `name` varchar(30) COLLATE utf8_turkish_ci NOT NULL,
 `unit_id` int(6) unsigned NOT NULL,
 `brand_id` int(6) unsigned NOT NULL,
 `orignalCost` int(30) NOT NULL,
 `saleprice` int(30) NOT NULL,
 `deleted` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`)
) 

Here is sample insert statements

INSERT INTO `products`VALUES (NULL, '1', 'name1', '1', '1', '100', '200', '0');
INSERT INTO `products`VALUES (NULL, '2', 'name2', '2', '2', '100', '200', '0');
INSERT INTO `products`VALUES (NULL, '3', 'name3', '3', '3', '100', '200', '0');
INSERT INTO `products`VALUES (NULL, '4', 'name4', '4', '4', '100', '200', '0');
INSERT INTO `products`VALUES (NULL, '5', 'name5', '5', '5', '100', '200', '0');
INSERT INTO `products`VALUES (NULL, '6', 'name6', '6', '6', '100', '200', '0');

Here is sample data I've entered.

+----+-------------+-------+---------+----------+-------------+-----------+---------+
| id | category_id |  name | unit_id | brand_id | orignalCost | saleprice | deleted |
+----+-------------+-------+---------+----------+-------------+-----------+---------+
|  1 |      1      | name1 |    1    |     1    |     100     |    200    |    0    |
+----+-------------+-------+---------+----------+-------------+-----------+---------+
|  2 |      2      | name2 |    2    |     2    |     100     |    200    |    0    |
+----+-------------+-------+---------+----------+-------------+-----------+---------+
|  3 |      3      | name3 |    3    |     3    |     100     |    200    |    0    |
+----+-------------+-------+---------+----------+-------------+-----------+---------+
|  4 |      4      | name4 |    4    |     4    |     100     |    200    |    0    |
+----+-------------+-------+---------+----------+-------------+-----------+---------+
|  5 |      5      | name5 |    5    |     5    |     100     |    200    |    0    |
+----+-------------+-------+---------+----------+-------------+-----------+---------+
|  6 |      6      | name6 |    6    |     6    |     100     |    200    |    0    |
+----+-------------+-------+---------+----------+-------------+-----------+---------+

Here is db connect get all data via PHP.

$conn = mysqli_connect("$db_host","$db_username","$db_pass","$db_name");
$query = $conn->query("SELECT * FROM `products`;");
var_dump($query);

Here is the result of above. You can see I have 6 rows.

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 8
    [lengths] => 
    [num_rows] => 6
    [type] => 0
)

In order to loop through php object use foreach

foreach ($query as $key => $value) {
  var_dump($value);
}

Foreach result goes like

Array
(
    [id] => 1
    [category_id] => 1
    [name] => name1
    [unit_id] => 1
    [brand_id] => 1
    [orignalCost] => 100
    [saleprice] => 200
    [deleted] => 0
)

Array
(
    [id] => 2
    [category_id] => 2
    [name] => name2
    [unit_id] => 2
    [brand_id] => 2
    [orignalCost] => 100
    [saleprice] => 200
    [deleted] => 0
)

It continues like that...

In order to get only saleprice use it like below.

foreach ($query as $key => $value) {
  var_dump($value['saleprice']);
}

So this is how you can get. Rest of it up to you whatever you want to do with it.

Answer 2

Aside from bugs specific to your implementation, there are two significant issues with this code:

• You're running a query in a loop, multiplying the number of queries you're doing and creating additional database traffic.
• You're concatenating untrusted data into your query, leaving yourself open to SQL Injection

The answer to the first problem is to use SELECT...WHERE...IN..., thus selecting all the required data in one query The second issue is solve by the use of prepared statements. For variable length data they can be fiddly to set up, but the improved security and performance is worthwhile.

For your example we need to get to a query of the form

SELECT `saleprice` FROM `products` WHERE `id` IN (?,?,?);  // could be any number of placeholders here.

    $mysqli = new mysqli('localhost','user','password','schema');
    // create the placeholders
    $paramList = str_repeat("?,", count($_POST['product_id']));
    $query = "select saleprice from products where id in (".trim($paramList, ',').")";
    $stmt = $mysqli->prepare($query);
    // create a string of parameter types
    $types = str_repeat('s', count($_POST['product_id']));  // All $_POST values are strings.
    // Now bind the data to the query
    $stmt->bind_param($types, ...$_POST['product_id']); // Use the ... spread operator to unpack the array
    $stmt->execute();
    $stmt->bind_result($resultData);
    while ($row = $stmt->fetch()) {
        echo $resultData;
        echo "<br>\n";
    }

Credit: Code taken from this blog