PHP export products from database into csv

[zazu]

Hey guys,

 

I have to export all my products from database into a csv file when executin the following script but i can't manage to put together the function that returns all my products from database.

<?php
require_once("config/db-connect.php");

function toate_produsele_active() {
	$array_produse = mysqli_query($mysqli, "SELECT product_id FROM mb95_products");
	return $array_produse;
}

$f = fopen('php://output', 'wb');
if($f) {
    foreach(toate_produsele_active() as $produs) {
        $coloane = array(
            $produs['product_id'],
            $produs['product_name'],
            $produs['category_url'],
            $produs['product_short_desc'],
            $produs['ron'],
            $produs['product_price'],
            $produs['cantitate'],
            implode('[,]', str_replace('[,]', '[%2C]', $produs['product_photo'])),
        );
        fputcsv($f, $coloane, ';', '"');
    }
    fclose($f);
}
?>

[benanamen]

Your query is only selecting one column. You need to name the rest of the columns you want in the query.

[zazu]

is not working with all columns, i miss something else?

[zazu]

<?php
require_once("config/db-connect.php");
function toate_produsele_active() {

$array_produse = mysqli_query($mysqli, "SELECT product_id, product_name, category_url, product_short_desc, product_price, product_photo FROM mb95_products");

while ($row = mysqli_fetch_assoc($array_produse)) {
    print_r($row);
}
}

$f = fopen('php://output', 'wb');
if($f) {
    foreach(toate_produsele_active() as $produs) {
        $coloane = array(
            $produs['product_id'],
            $produs['product_name'],
            $produs['category_url'],
            $produs['product_short_desc'],
            $produs['product_price'],
            implode('[,]', str_replace('[,]', '[%2C]', $produs['product_photo'])),
        );
        fputcsv($f, $coloane, ';', '"');
    }
    fclose($f);
}
?>

latest code, not working...the final result needs to be:

 

1;Product name;product price; etc etc

[ginerjm]

Where is $mysqli defined? IN your config.php code? You need to pass it into the function.

 

Things like this would show up on your screen if you turned on php error checking during the development phase. Be sure to turn it off before putting it into production.

Edited March 23, 2017 by ginerjm

[zazu]

<?php

function toate_produsele_active() {

$db_username = 'root';
$db_password = '';
$db_name = 'mb95-romania';
$db_host = 'localhost';
$mysqli = mysqli_connect($db_host, $db_username, $db_password,$db_name);

$array_produse = mysqli_query($mysqli, "SELECT product_id, product_name, category_url, product_short_desc, product_price, product_photo FROM mb95_products");
return array($array_produse);
}

$f = fopen('php://output', 'wb');
if($f) {
    foreach(toate_produsele_active() as $produs) {
        $coloane = array(
            $produs['product_id'],
            $produs['product_name'],
            $produs['category_url'],
            $produs['product_short_desc'],
            $produs['product_price'],
            implode('[,]', str_replace('[,]', '[%2C]', $produs['product_photo'])),
        );
        fputcsv($f, $coloane, ';', '"');
    }
    fclose($f);
}
?>

Ok i've put config.php code into the function now i have the Fatal error: Cannot use object of type mysqli_result as array in D:\EasyPHP-DevServer-14.1VC9\data\localweb\projects\mb95-romania\export-products.php on line 19

[ginerjm]

You don't try and convert the results of the query to array. It is a resource variable now and must either be fetched row by row or fetchall'ed which would then create an array.

[ginerjm]

Do the query.

If it succeeds:

open your output file handle.

begin a while loop to fetch each row as an array. Use the PDO::FETCH:NUM option to get a sequential array, not an associative one.

with each row use the fputcsv function to create a csv string out of the array (look it up in the manual) and write that to your file handle.

 

close the handle when loop is completed.

 

The manual has a good example of how easy this is.

[zazu]

function toate_produsele_active()
{   
    $push = array();
    $result = mysqli_query($mysqli, "SELECT product_id, product_name, category_url, product_short_desc, product_price, product_photo FROM mb95_products");
    while($row = mysqli_fetch_array($result, MYSQLI_NUM))
    {
        $row = array_map('stripslashes', $row);
        $push[] = $row;
    }
    return $push;
}

$f = fopen('php://output', 'wb');
if($f) {
    foreach(toate_produsele_active() as $produs) {
        $coloane = array(
            $produs['product_id'],
            $produs['product_name'],
            $produs['category_url'],
            $produs['product_short_desc'],
            $produs['product_price'],
            implode('[,]', str_replace('[,]', '[%2C]', $produs['product_photo'])),
        );
        fputcsv($f, $coloane, ';', '"');
    }
    fclose($f);
}

This is what i've achived but the script doesn't return anything. what i'm doing wrong?

[Jacques1]

what i'm doing wrong?

 

Several things:

• The function doesn't have access to any $mysqli variable. You need to either pass the connection to the function as an argument (the recommended way) or import it as a global variable (the confusing way).
• Your error reporting is misconfigured, because it should have told you that you're using an undefined variable. Turn it all the way up. Yes, even “notices” matter.
• The tool you use for programming sucks, because it also should have pointed out the error. Switch to a proper IDE like Netbeans.
• You need to learn how to perform basic debugging steps (with var_dump(), for example). Don't just surrender when a problem occurs. Try to find out what happens. You're actually in a much better situation than we are, because you're sitting right in front of the screen.
• stripslashes? Really? Unless you're using some truly prehistoric PHP version (which I hope you don't), this function is completely useless and actually harmful.

[zazu]

Ok i've used var_dumb and the select is returning NULL but i cant understand why is that cuz the select query seems to be ok.

[Jacques1]

Have you read the whole reply? Have you fixed the connection problem?

[zazu]

<?php
session_start();
$db_username = 'rpre0623_mb95';
$db_password = '0a%Iu(tG6,!@';
$db_name = 'rpre0623_mb95';
$db_host = 'localhost';
$mysqli = new mysqli($db_host, $db_username, $db_password, $db_name);


function toate_produsele_active($mysqli)
{   
    $push = array();
    $result = mysqli_query($mysqli, "SELECT product_id, product_name, category_url, product_short_desc, product_price, product_photo FROM mb95_products") or trigger_error(mysqli_error());
    var_dump($result);

    while($row = mysqli_fetch_array($result, MYSQLI_NUM))
    {
        $push[] = $row;
    }
    return $push;
}

$f = fopen('php://output', 'wb');
if($f) {
    foreach(toate_produsele_active() as $produs) {
        $coloane = array(
            $produs['product_id'],
            $produs['product_name'],
            $produs['category_url'],
            $produs['product_short_desc'],
            $produs['product_price'],
            implode('[,]', str_replace('[,]', '[%2C]', $produs['product_photo'])),
        );
        fputcsv($f, $coloane, ';', '"');
    }
    fclose($f);
}
?>

Yes. This is my entire code.

[Jacques1]

You're not passing the connection to the function, you have not fixed your error reporting, and now you need a new database password.

 

I have listed those steps for a reason. Go through them one by one, fix the problems, then come back. Your mysqli error reporting is also broken. You need to either enable mysqli exceptions or manually check every single mysqli function call.

[ginerjm]

It's been almost two weeks and you haven't listened or learned and you don't even write your posts well. Perhaps this isn't your game?

 

The very first post I made told you to include your db connection in the function. It also suggested that you turn on error checking. Did you do either yet?

 

And when you respond to us please take the time to write the correct words so that you and we know that you know what you are doing.