Cannot get data

Question

I'm trying to get a file of my database, which is formated as csv (excel file), but it doesn't even get the data to the browser.

I haven't tried something yet, because i'm afraid that i will do it worse than it is...

My PHP Code (I have already connected to the database, but i don't want to show my info in public):

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create connection
$db = new mysqli($dbhost, $dbusername, $dbpassword, $dbname);

$allData = "";
$query = 'SELECT * FROM members';
  $retval = mysqli_query( $db,$query );
   if(! $retval ) {
      die('Could not get data.');
   }
   while($row = mysqli_fetch_array($retval)) {
  $allData .= $row['Id'] . ',' . $row['Email'] . ',' . $row['Condition'] .     ',' . $row['isEmailConfirmed'] . ',' . $row['Name'] . ',' .         $row['LastName'] . ',' . $row['Token'] . ',' .$row['Type'] . ',' .         $row['extra_privileges'] . ',' . $row['access-to-ftp'] . ',' .$row['access-    to-panel'] . ',' . $row['Birthdate'] . ',' . $row['Password'] . ',' .     $row['Description'] . "\n";
  //$retval = "data:text/csv;charset=utf-    8,ID,EMAIL,CONDITION,ISEMAILCONFIRMED,NAME,LASTNAME,TOKEN,TYPE,EXTRA_PRIVIL    EGES,ACCESS-TO-FTP,ACCESS-TO-PANEL,BIRTHDATE,PASSWORD,DESCRIPTION\n";
  $retval .= $allData;

  echo $retval;
  }
?>

It just tells me Could not get data, which i have as a die solution.

Die is not a good way of debugging. Enable exception mode and php error reporting. [How to enable MySQLi exception mode?](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments/22662582#22662582) — Dharman, Jun 06 '19 at 21:26
@Dharman Notice: Undefined variable: db in /var/www/vhosts/photoshare.ga/database.photoshare.ga/backup/confirmed/index.php on line 15 Warning: mysqli_query() expects parameter 1 to be mysqli, null given in /var/www/vhosts/photoshare.ga/database.photoshare.ga/backup/confirmed/index.php on line 15 Could not get data. — , Jun 06 '19 at 21:28
That is because you have named your variable with MySQLi connection `$conn`, but then you try to use `$db`. It is a typo. — Dharman, Jun 06 '19 at 21:29
Notice: Undefined index: extra_pivileges in /var/www/vhosts/photoshare.ga/database.photoshare.ga/backup/confirmed/index.php on line 20 Warning: Use of undefined constant allData - assumed 'allData' (this will throw an Error in a future version of PHP) in /var/www/vhosts/photoshare.ga/database.photoshare.ga/backup/confirmed/index.php on line 22 Notice: Undefined variable: response in /var/www/vhosts/photoshare.ga/database.photoshare.ga/backup/confirmed/index.php on line 24 — , Jun 06 '19 at 21:31
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, string given in /var/www/vhosts/photoshare.ga/database.photoshare.ga/backup/confirmed/index.php on line 19 @Dharman — , Jun 06 '19 at 21:32
Inside of the loop you are re-using variable `$retval`. You overwrite you DB results with some string you are creating. Call your variables proper names and you will make such typos less often. — Dharman, Jun 06 '19 at 21:33
But, these are the default names of the columns...(I just started learning PHP, please don't judge me @Dharman ..) — , Jun 06 '19 at 21:36
I am not talking about column names. Take a look at `$allData` and `$retval`. What are they? What do these names mean? You already used `$retval` and then you recycled it inside of the loop. — Dharman, Jun 06 '19 at 21:38
I am not judging. Typos happen to everyone. I am kindly suggesting to use better, longer and more descriptive variable names. e.g. `$result_of_DB_call` — Dharman, Jun 06 '19 at 21:39
I have one last error @Dharman : Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, string given in /var/www/vhosts/photoshare.ga/database.photoshare.ga/backup/confirmed/index.php on line 19 — , Jun 06 '19 at 21:41
At this point I need to ask you to update the question with the code you currently have. It looks like you are still reusing `$retval` inside the loop, but I can't tell you where. — Dharman, Jun 06 '19 at 21:43
Either name the result of `mysqli_query` a different name, or do not assign to it inside of `while` loop: `$retval = "data:text/csv`... Your problem is that inside of the loop you are discarding your DB results and recycling the variable for a CSV string. Use different names for these variables. — Dharman, Jun 06 '19 at 21:49
@Dharman If i remove the $retval = "data:text/csv... it will output this (i updated my code to check this) : Recoverable fatal error: Object of class mysqli_result could not be converted to string in /var/www/vhosts/photoshare.ga/database.photoshare.ga/backup/confirmed/index.php on line 22 — , Jun 06 '19 at 21:54

score 1 · Accepted Answer · answered Jun 06 '19 at 22:02

Your problem is that you are re-using the same variable and you are losing your DB results.

$sql = 'SELECT * FROM members';
$retval = mysqli_query($db, $sql);
// ^^^^ This PHP variable holds your DB results
if (!$retval) {
    die('Could not get data.');
}
//  Each iteration of the loop you reach for a single row from the result $retval
while ($row = mysqli_fetch_array($retval)) {
    $allData .= $row['Id'] . ',' . $row['Email'] . ',' . $row['Condition']     . ',' . $row['isEmailConfirmed'] . ',' . $row['Name'] . ',' .     $row['LastName'] . ',' . $row['Token'] . ',' .$row['Type'] . ',' .     $row['extra_pivileges'] . ',' . $row['access-to-ftp'] . ',' .$row['access-to    panel'] . ',' . $row['Birthdate'] . ',' . $row['Password'] . ',' .     $row['Description'] . "\n";
    $retval = "data:text/csv;charset=utf-8,ID,EMAIL,CONDITION,ISEMAILCONFIRMED,NAME,LASTNAME,TOKEN,TYPE,EXTRA_PRIVILEG    ES,ACCESS-TO-FTP,ACCESS-TO-PANEL,BIRTHDATE,PASSWORD,DESCRIPTION\n";
    $retval .= $allData;
    // ^^^ but here you are using the same variable, thus losing its previous contents. 

    echo $retval;
}

Change the name of your PHP result and do not overwrite it. For example:

$sql = 'SELECT * FROM members';
$result_of_DB_select = mysqli_query($db, $sql);
// ^^^^ This PHP variable holds your DB results
if (!$result_of_DB_select) {
    die('Could not get data.');
}

// CSV header before the loop:
$CSV_response = "data:text/csv;charset=utf-8,ID,EMAIL,CONDITION,ISEMAILCONFIRMED,NAME,LASTNAME,TOKEN,TYPE,EXTRA_PRIVILEG    ES,ACCESS-TO-FTP,ACCESS-TO-PANEL,BIRTHDATE,PASSWORD,DESCRIPTION\n";

//  Each iteration of the loop you reach for a single row from the result variable called $result_of_DB_select 
while ($row = mysqli_fetch_array($result_of_DB_select)) {
    $CSV_response .= $row['Id'] . ',' . $row['Email'] . ',' . $row['Condition']     . ',' . $row['isEmailConfirmed'] . ',' . $row['Name'] . ',' .     $row['LastName'] . ',' . $row['Token'] . ',' .$row['Type'] . ',' .     $row['extra_pivileges'] . ',' . $row['access-to-ftp'] . ',' .$row['access-to    panel'] . ',' . $row['Birthdate'] . ',' . $row['Password'] . ',' .     $row['Description'] . "\n";
    // ^^^ different variable holds your CSV response now and you will not lose contents of $result_of_DB_select. 
}
echo $CSV_response;

If you wanted to keep the CSV header inside the loop and return it for each row, then you can put it inside the loop. I assumed you want it only once at the top and then all the rows after that. — Dharman, Jun 06 '19 at 22:08
Ok, now i understand...Thank you so much for your help @Dharman ! — , Jun 06 '19 at 22:09

Cannot get data

1 Answers1