I can't read data from SQL query?

Question

I want to store data in my database table but the query returns no data. I tried to get data through mysqli_fetch_array but it shows no data..

<?php

    $del = "
DELETE 
  FROM prev_price_karachi
";
    $run = mysqli_query($con, $del);


    $date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');
    $prev_date = date('Y-m-d', strtotime($date .' -1 day'));

//here i am getting data from two tables  
    $sql_pro = "
SELECT product_karachi.id as product_id
     , price_type.mandi_bhao
     , price_type.bhao_bazar
     , price_type.bachat_bazar
     , price_type_grains.retail_price
     , price_type_grains.wholesale_price 
  FROM product_karachi 
  LEFT 
  JOIN price_type 
    ON price_type.pr_id = product_karachi.id     
  LEFT 
  JOIN price_type_grains 
    ON price_type_grains.pr_id = product_karachi.id
";

    $run_pro = mysqli_query($con, $sql_pro);
print_r($run_pro);
//The print_r returns the following output, shows that data is there 187 rows. 
//mysqli_result Object ( [current_field] => 0 [field_count] => 6 [lengths] => [num_rows] => 187 [type] => 0 )
    $i = 0;

// but i could not store the data into variable in below statement, return empty.
    while ($row = mysqli_fetch_array($run_pro)) {

      $id          = $row['pr_id'];
      $price1       = $row['bhao_bazar'];
      $price2       = $row['bachat_bazar'];
      $price3       = $row['mandi_bhao'];
      $price4       = $row['retail_price'];
      $price5       = $row['wholesale_price'];


    $sql_insert = "
INSERT INTO prev_price_karachi 
(pr_id, bhao_bazar
, bachat_bazar
, mandi_bhao
, retail_price
, wholesale_price) VALUES
('$id'
, '$price1'
,'$price2'
,'$price3'
,'$price4'
,'$price5'
,'$prev_date')";
    $sql_run    = mysqli_query($con, $sql_insert);
}

?>

For one thing, you probably wiped out your entire database with the DELETE query you did without using a WHERE clause. — Funk Forty Niner, Apr 29 '20 at 19:36
actually, i have to delete previous day data and replace it with new one on daily basis.it is required. — Muhammad Arif, Apr 29 '20 at 19:41
If you want to wipe out the whole table, `truncate` might be faster and cleaner than a `DELETE` without a where. — aynber, Apr 29 '20 at 19:42
And you can use `INSERT...SELECT` syntax in SQL to carry out the whole operation in one single SQL statement instead of a SELECT and then X number of separate inserts. It will be more efficient, and less code. — ADyson, Apr 29 '20 at 19:43
the problems seem to me is in while ($row = mysqli_fetch_array($run_pro)) — Muhammad Arif, Apr 29 '20 at 19:45
_"but the query returns no data"_ ...you proved this is false yourself, when you did print_r $run_pro and showed there were 187 rows, you said. Did you put anything like a var_dump() command into the loop, to show if it's executing or not? More likely the insert is failing. Have you got mysqli set to throw exceptions when errors occur? And PHP set to log errors? Anyway though like I said above, you don't need to do it in this inefficient way, so it's probably not really an issue worth solving. — ADyson, Apr 29 '20 at 19:45
See about sql injection and the importance of prepared and bound queries — Strawberry, Apr 29 '20 at 19:46
i am new to php, please bear me out, if i raise stupid question..i have the result of print_r($run_pro)..now in don't know why mysqli_fetch_array($run_pro) does not retrieve data in row... — Muhammad Arif, Apr 29 '20 at 20:01
i did var_dump($run_pro) and it returns
object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(6) ["lengths"]=> NULL ["num_rows"]=> int(187) ["type"]=> int(0) } — Muhammad Arif, Apr 29 '20 at 20:19
"in don't know why mysqli_fetch_array($run_pro) does not retrieve data in row" ...read my earlier comment again. Almost certainly this **does** retrieve data, but you just can't see any evidence for it because a) you haven't tried to echo any of the data and b) it seems like that your INSERT query is failing. **BUT**, you don't need to worry about this...just re-write your code using a single INSERT...SELECT query instead (see my first comment, and the link I posted later). This will transfer the data directly from the query result to the target table, without needing PHP to process it. — ADyson, Apr 29 '20 at 23:47
P.S. your INSERT query is almost certainly failing because you are trying to insert the $prev_date value, but you haven't declared a column to insert it into! This would be easy to spot if you had error logging turned on properly. See the following articles to properly configure your system to log PHP and MySQL errors: https://stackify.com/php-error-logs-guide/ (php error logging/reporting) https://stackoverflow.com/a/14578644/5947043 (mysqli exception handling) — ADyson, Apr 29 '20 at 23:49
Anyway you can write this all as one query something like this: — ADyson, Apr 29 '20 at 23:51
`$sql = "INSERT INTO prev_price_karachi (pr_id, bhao_bazar , bachat_bazar , mandi_bhao , retail_price , wholesale_price, date) SELECT product_karachi.id as product_id , price_type.mandi_bhao , price_type.bhao_bazar , price_type.bachat_bazar , price_type_grains.retail_price , price_type_grains.wholesale_price , '".$prev_date."' FROM product_karachi LEFT JOIN price_type ON price_type.pr_id = product_karachi.id LEFT JOIN price_type_grains ON price_type_grains.pr_id = product_karachi.id";` — ADyson, Apr 29 '20 at 23:51
and then you can just execute that, and you don't need your `while` loop. — ADyson, Apr 29 '20 at 23:53
What happens if you do: while ($row = mysqli_fetch_array($run_pro)) { var_dump($row); } ? — bestprogrammerintheworld, Apr 30 '20 at 05:57
@ADyson Thank you so much, it worked, problem solved..i am very grateful to you... — Muhammad Arif, Apr 30 '20 at 15:34

I can't read data from SQL query?

0 Answers0