[RESOLVED] Export Mysql data to CSV

[avraamG13]

Hello,
Im new to that kind of code so forgive me.

I have 3 diferent tables

players:
id_player
name_player

players_details:
id_player
position
age

players_eval:
id_player
value
wage

You can see that the ‘id_player’ is in all tables. Now, what i want to do is export on csv and if the ‘id_player’ is the same number with the other tables to continew and show the rest of the info i have on the mysql. Also i would like when the 'id_player' to rename to Player ID on the excel file or 'age' to Age etc.

This is the code im using

$table="players"; // this is the tablename that you want to export to csv from mysql.

function exportMysqlToCsv($table);
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";
    $sql_query = "select * from $table";

// Gets the data from the database
$result = mysql_query($sql_query);
$fields_cnt = mysql_num_fields($result);


$schema_insert = '';

for ($i = 0; $i < $fields_cnt; $i++)
{
    $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
        stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
    $schema_insert .= $l;
    $schema_insert .= $csv_separator;
} // end for

$out = trim(substr($schema_insert, 0, -1));
$out .= $csv_terminated;

// Format the data
while ($row = mysql_fetch_array($result))
{
    $schema_insert = '';
    for ($j = 0; $j < $fields_cnt; $j++)
    {
        if ($row[$j] == '0' || $row[$j] != '')
        {

            if ($csv_enclosed == '')
            {
                $schema_insert .= $row[$j];
            } else
            {
                $schema_insert .= $csv_enclosed . 
				str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
            }
        } else
        {
            $schema_insert .= '';
        }

        if ($j < $fields_cnt - 1)
        {
            $schema_insert .= $csv_separator;
        }
    } // end for

    $out .= $schema_insert;
    $out .= $csv_terminated;

} // end while
$file = "testing";
 $filename = $file."_".date("Y-m-d_H-i",time());
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose ;)
    //header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    header("Content-type: application/csv");
    header("Content-disposition: csv" . date("Y-m-d") . ".csv");
    //header("Content-Disposition: attachment; filename=$filename".csv");
    header( "Content-disposition: attachment; filename=".$filename.".csv");
    echo $out;
    exit;
}

I really need your help

Thank you

[grallis]

hi avraamG13

I'm not 100% sure what you want to do, but maybe this will aim you in the right direction:

SELECT
	*  // should specify which fields you want to pull out
FROM
	table1 AS t1 JOIN
	table2 AS t2
ON
	t1.id_player = t2.id_player

This query will only grab the rows when the id_player is in both tables. This becomes problematic if someone's id_player value can be in a table more than once.

Now you have to make sure you have a csv file. I'm going to write it to a file to keep things simpe.

// alias a file to a avriable
$file = "somefile.csv";

// create a file resource
$fh = fopen($file, 'w') or die("Could not open the file");

// create an array of field names
$fieldsArr = array("field1", "field2", "field3", "etc..");

// for dynamic purposes you could also issue a query like this
$colRES = mysql_query("SHOW COLUMNS FROM table1");

while($colROW = mysql_fetch_array($colRES)){
    if($colROW['Field'] != "id_player")
        array_push($fieldsArr, $colROW['Field']);
    else
        array_push($fieldsArr, "Player ID");
}

// write the column headings to the csv
fputcsv($fh, $fieldsArr);

// now loop through and get the data
while($row = mysql_fetch_array($res)){
    fputcsv($fh, $row);   

}

That's basic, but should point you in a better direction. Mind you I havent used fputcsv in a LONG time, i have my own method for that. Look at the code and try and wiggle it in for your uses.

[djjjozsi]

Lets test this code:

<?php

$hostname = '**********'; //// specify host, i.e. 'localhost'
$user = '********'; //// specify username
$pass = '********'; //// specify password
$dbase = '**********'; //// specify database name
$connection = mysql_connect( "$hostname" , "$user" , "$pass" )
or die ( "Can't connect to MySQL" );
$db = mysql_select_db( $dbase , $connection ) or die ( "Can't select database." );

/* you make the correct query with JOINS */
/* Use aliases for your field names, those names will be the field titles in the CSV file*/
$sql = "SELECT aaa.id AS `My first field`, aaa.a AS `Super trouple field name 2`,bbb.bbbb AS `Field name 3` FROM aaa LEFT JOIN bbb ON bbb.id=aaa.b";
$filename = "filename1.csv";
friendly_export( $sql , $filename );

function friendly_export( $sql, $filename )
{
    function str( $var )
    {
        if(empty($var))
    	return '""';
		else
		return '"' . addslashes( $var ) . '"';
    }

$res = mysql_query( $sql );
$numberfields = mysql_num_fields( $res );

for ( $i = 0; $i < $numberfields ; $i++ ) {
    $var[] = str( mysql_field_name( $res, $i ) );
} 

$header = implode( ";", $var ) . "\n\r";

while ( $aaa = mysql_fetch_assoc( $res ) ) {
    $aaa = array_map( "str" , $aaa );
    $content[] = implode( ";", $aaa );
} 
$content = implode( "\n\r", $content );

$h = fopen( $filename, "w" );
fwrite( $h , $header . $content );
} 

?>

[avraamG13]

With yours posts you help me alot so ive manage to make it

Code:

$result = mysql_query("SELECT * FROM players AS t1 JOIN players_details AS t2 JOIN players_eval AS t3 ON t1.id_player = t2.id_player AND t1.id_player = t3.id_player");

$fields = '"Player ID","Player Name", "Age","Position"'."\n";

// Iterate through the rows of data
while ($row = mysql_fetch_assoc($result))
{
	$fields .= '"'.$row['id_player'].'","'.$row['name_player'].'","'.$row['value'].'","'.$row['age'].'","'.$row['position'].'"."\n";
}

// Set our headers
header('Content-type: text/csv');
header("Content-Disposition: attachment; filename=event_registrations.csv");

// Output our data
echo $fields;

Everything works gread the only problem i have is that im looking a way to reaplace for example 'Position' output that shows numbers on the excel from 1-5

1=something
2=something
3=something
4=something
5=something

So if any of this number its saved on the database the ouput of the excel it will be replaced with 'something'

I hope you will understant

Thank you

[grallis]

$fields = '"Player ID","Player Name", "Age","Position"'."\n";

// Iterate through the rows of data
while ($row = mysql_fetch_assoc($result))
{
        switch($row['position']){
                $pos = "";
                case 1:
                        $pos = "Some Position";
                        break;
                case 2:
                        $pos = "Some other position";
                        break;
                default:
                        $pos = "Default position";
                        break;
        }
	$fields .= '"'.$row['id_player'].'","'.$row['name_player'].'","'.$row['value'].'","'.$row['age'].'","'.$pos.'"."\n";
}

The best thing to do would be to put that switch code in a function or method and call it like such:

$pos = $determinePosition($row['position']);

and the function would be something like:

function determinePosition($position){
        $pos = "";
        switch($position){
                case 1:
                        $pos = "Some Position";
                        break;
                case 2:
                        $pos = "Some other position";
                        break;
                default:
                        $pos = "Default position";
                        break;
        }

     return $pos;      
}

Hope this is what you were looking for ...

[djjjozsi]

if you want to change numbers into words,
create a new table and store the numbers in it, and use a JOIN query,
and use ALIASES as field names, and my export function export it
as you whish.

could you explain this number change a little bit ?

[avraamG13]

grallis

Im using the second idea of your code and its not working

Here is the errors
Notice: Undefined variable: determinePosition in ... (line 7)
Fatal error: Function name must be a string in .... (line 7)

 Line 7
$pos = $determinePosition($row['position']); 

Im just copying paste your code and this is the errors

[djjjozsi]

this function is not a variable, so you can use it like this:

$pos = determinePosition($row['position']); 

[avraamG13]

Your a life saver, no something else . The error has been shown insite the excel file

And under position its always shows 'Default position'

<b>Notice</b>:  Undefined variable: row in <b>excel.php</b> on line <b>7</b><br />

Line 7
$pos = determinePosition($row['position']);

[grallis]

Thanks for clearing up my type-o, djjjozsi

avraamG13, could you cut and paste your while loop into a message?

[avraamG13]

Here the whole code

<?php
$pos = determinePosition($row['position']); 

function determinePosition($position){
        $pos = "";
        switch($position){
                case 1:
                        $pos = "Keeper";
                        break;
                case 2:
                        $pos = "Playmaker";
                        break;
                case 3:
                        $pos = "Scorer";
                        break;
                case 4:
                        $pos = "Winger";
                        break;
                case 5:
                        $pos = "Defender";
                        break;
                default:
                        $pos = "Error";
                        break;
        }

     return $pos;      
} 

$result = mysql_query("SELECT * FROM players AS t1 JOIN players_details AS t2 JOIN players_eval AS t3 ON t1.id_player = t2.id_player AND t1.id_player = t3.id_player");

$fields = '"Player ID","Player Name","TSI","Age","Position","Specialty","Stamina","Playmaking","Winger","Scoring","Keeper","Passing","Defending","Setpieces","Form","Experience","Owner Name","Owner Id","Owner Team Name","Owner Team ID","From"'."\n";


while ($row = mysql_fetch_assoc($result))
{
	$fields .= '"'.$row['id_player'].'","'.$row['name_player'].'","'.$row['value'].'","'.$row['age'].'","'.$pos.'","'.$row['specialty'].'","'.$row['stamina'].'","'.$row['playmaking'].'","'.$row['winger'].'","'.$row['scoring'].'","'.$row['keeper'].'","'.$row['passing'].'","'.$row['defending'].'","'.$row['setpieces'].'","'.$row['form'].'","'.$row['experience'].'","'.$row['owner_name'].'","'.$row['owner_id'].'","'.$row['owner_team'].'","'.$row['owner_team_id'].'","'.$row['owner_country'].'"'."\n";
}

// Set our headers
header('Content-type: text/csv');
// To display data in-browser, change the header below to:
//header("Content-Disposition: inline");
header("Content-Disposition: attachment; filename=event_registrations.csv");

// Output our data
echo $fields;

?>

Let me explain again. On my database under 'Position' row i have numbers from 1-5. From this number you can see on the code that 1 = Keeper etc... So, i want when exporting into excel the numbers to be replaced if 1 = keeper if 2 = Playmaker

[djjjozsi]

lets have a look at the while function, who to apply the determine function on the position value. And you need to escape the quotes before you write the CSV.

<?php

function determinePosition( $position )
{
    $pos = "";
    switch ( $position ) {
        case 1:
            $pos = "Keeper";
            break;
        case 2:
            $pos = "Playmaker";
            break;
        case 3:
            $pos = "Scorer";
            break;
        case 4:
            $pos = "Winger";
            break;
        case 5:
            $pos = "Defender";
            break;
        default:
            $pos = "Error";
            break;
    } 

return $pos;
} 

$result = mysql_query( "SELECT * FROM players AS t1 JOIN players_details AS t2 JOIN players_eval AS t3 ON t1.id_player = t2.id_player AND t1.id_player = t3.id_player" );

$fields = '"Player ID","Player Name","TSI","Age","Position","Specialty","Stamina","Playmaking","Winger","Scoring","Keeper","Passing","Defending","Setpieces","Form","Experience","Owner Name","Owner Id","Owner Team Name","Owner Team ID","From"' . "\n";

while ( $row = mysql_fetch_assoc( $result ) ) {
    $row['position'] = determinePosition( $row['position'] );
    $row = array_map( "addslashes" , $row ); //you need to escape the quotes
    $fields .= '"' . $row['id_player'] . '","' . $row['name_player'] . '","' . $row['value'] . '","' . $row['age'] . '","' . $row['position'] . '","' . $row['specialty'] . '","' . $row['stamina'] . '","' . $row['playmaking'] . '","' . $row['winger'] . '","' . $row['scoring'] . '","' . $row['keeper'] . '","' . $row['passing'] . '","' . $row['defending'] . '","' . $row['setpieces'] . '","' . $row['form'] . '","' . $row['experience'] . '","' . $row['owner_name'] . '","' . $row['owner_id'] . '","' . $row['owner_team'] . '","' . $row['owner_team_id'] . '","' . $row['owner_country'] . '"' . "\n";
} 
// Set our headers
header( 'Content-type: text/csv' );
// To display data in-browser, change the header below to:
// header("Content-Disposition: inline");
header( "Content-Disposition: attachment; filename=event_registrations.csv" );
// Output our data
echo $fields;

?>

[avraamG13]

Thankkkk youuu so much all of you with this. Working 100% 🙂

Again thank you so much

[grallis]

Great news

[djjjozsi]

@ :

I know this is working, but this "category" could store in a table, then you didn't need to hardcode the cases.
if the fields in the right order, using the implode is a faster way to create the comma delimiter (as you can see in my earlier posts).

And this code makes comma separated file, however lots of cases the ; is the delimiter 🙂

@: And thank you for creating this CSV export in another view point

[grallis]

djjjozsi anytime ...

avraamG13 - djjjozsi is giving you good advice about storing that category in another table.

You should have another table that stores the position ID and the position name so like:

positionID | positionName

1 Winger
2 Defender

Then you call a join like we did above and pull out the positionName automatically so you dont need to fuss with hardcoding the values in. That way you can also update the positions from a form. Take care 🙂