<?php

// filename for export
$csv_filename = 'db_export_'.$db_record.'_'.date('Y-m-d').'.csv';
// database variables
$hostname = "localhost";
$user = "klungadmin";
$password = "Ba@&dC0m2Oi6";
$database = "klungadmin_wp";
$port = 3306;
$conn = mysqli_connect($hostname, $user, $password, $database, $port);
if (mysqli_connect_errno()) {
    die("Failed to connect to MySQL: " . mysqli_connect_error());
}
// create empty variable to be filled with export data
$csv_export = '';

$sql = 'SELECT * FROM 
    (SELECT *
        FROM (SELECT       
        @i := @i + 1 as ninjaID,
        post_author AS UserID
        ,(SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_1" ) AS FirstName
		,(SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_37" ) AS LastName
        ,(SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_31" ) AS PhoneNo
        ,(SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_2" ) AS Email
        ,(SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_32" ) AS LineID
        
        ,(SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_34" ) AS PropID
        ,(SELECT post_title FROM new_posts a1 WHERE a1.ID = (SELECT meta_value FROM new_postmeta WHERE post_id = new_posts.ID AND new_postmeta.meta_key = "_field_34" )) AS Prop_Title     
              
              ,(SELECT concat("https://new.klungbaan.com/?post_type=property&p=", PropID)) AS Prop_Url  
              ,(SELECT "") AS imgPath  
        ,(SELECT new_terms.name FROM new_term_relationships
                INNER JOIN new_term_taxonomy on new_term_relationships.term_taxonomy_id = new_term_taxonomy.term_taxonomy_id
                INNER JOIN new_terms on new_term_taxonomy.term_id = new_terms.term_id
                WHERE  new_term_taxonomy.taxonomy = "property_area" and new_term_relationships.object_id = (SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_34" )) as Prop_Area
        ,(SELECT new_terms.name FROM new_term_relationships
                INNER JOIN new_term_taxonomy on new_term_relationships.term_taxonomy_id = new_term_taxonomy.term_taxonomy_id
                INNER JOIN new_terms on new_term_taxonomy.term_id = new_terms.term_id
                WHERE  new_term_taxonomy.taxonomy = "property_city" and new_term_relationships.object_id = (SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_34" )) as Prop_City
              ,(SELECT new_terms.name FROM new_term_relationships
                INNER JOIN new_term_taxonomy on new_term_relationships.term_taxonomy_id = new_term_taxonomy.term_taxonomy_id
                INNER JOIN new_terms on new_term_taxonomy.term_id = new_terms.term_id
                WHERE  new_term_taxonomy.taxonomy = "property_state" and new_term_relationships.object_id = (SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_34" )) as Prop_State
              
              ,(SELECT meta_value FROM new_postmeta WHERE meta_key = "fave_property_price" AND post_id = (SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_34" )) AS PropPrice
              
        ,(SELECT new_terms.name FROM new_term_relationships
                INNER JOIN new_term_taxonomy on new_term_relationships.term_taxonomy_id = new_term_taxonomy.term_taxonomy_id 
                INNER JOIN new_terms on new_term_taxonomy.term_id = new_terms.term_id 
                WHERE new_term_taxonomy.taxonomy = "property_type" and new_term_relationships.object_id = (SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_34" )) as Prop_Type
        
               ,(SELECT main_users.ID FROM new_posts INNER JOIN main_users ON new_posts.post_author = main_users.ID WHERE new_posts.ID = PropID) as AuthorID
              
        ,(SELECT  CONCAT(z1.meta_value, " ", z2.meta_value) FROM new_posts INNER JOIN main_usermeta z1 ON new_posts.post_author = z1.user_id 
          inner join main_usermeta z2 on new_posts.post_author = z2.user_id 
          WHERE new_posts.ID = PropID and z1.meta_key = "first_name" and z2.meta_key = "last_name") as AuthorName
              
               ,(SELECT main_users.user_email FROM new_posts INNER JOIN main_users ON new_posts.post_author = main_users.ID WHERE new_posts.ID = PropID) as AuthorEmail
              
       ,(SELECT meta_value FROM new_postmeta WHERE post_id = ID AND new_postmeta.meta_key = "_field_33" ) AS AllowedInfo
        ,post_date as Created_Date
       
       FROM `new_posts`  
        , (select @i := 0) temp WHERE post_type = "nf_sub" AND post_status = "publish"
       ) temp1 WHERE FirstName IS NOT null AND PropID IS NOT null 
       ORDER BY Created_Date DESC
       ) temp2 

';

// query to get data from database
$query = mysqli_query($conn, $sql);
$field = mysqli_field_count($conn);
// create line with field names
for($i = 0; $i < $field; $i++) {
    $csv_export.= '"'.mysqli_fetch_field_direct($query, $i)->name.'",';
}
// newline (seems to work both on Linux & Windows servers)
$csv_export = rtrim($csv_export,',');
$csv_export.= '
';
// loop through database query and fill export variable
while($row = mysqli_fetch_array($query)) {
    // create line with field values
    for($i = 0; $i < $field; $i++) {
        $csv_export.= '"'.$row[mysqli_fetch_field_direct($query, $i)->name].'",';
    }
    $csv_export = rtrim($csv_export,',');
    $csv_export.= '
';
}
// Export the data and prompt a csv file for download
header('Content-Encoding: UTF-8');
header('Content-type: text/csv; charset=UTF-8');
header("Content-Disposition: attachment; filename=".$csv_filename."");
echo "\xEF\xBB\xBF"; // UTF-8 BOM
echo($csv_export);



?>