Thursday, January 16, 2014

mysql query Sorting certain values to the top

Hay guys you can see the query as same result but execution speed
--------------------------------------------------- 
(
SELECT * FROM cyb_jm_master_countries_m WHERE country_name = "United States"
) UNION (
SELECT * FROM cyb_jm_master_countries_m WHERE country_name != "United States"
)
---------------------------------------------------- 
OR
 
SELECT  * 
FROM cyb_jm_master_countries_m
ORDER  BY  `country_name`  =  'United States' DESC ,  `country_name`  ASC 
LIMIT 0 , 30
 
  


Saturday, January 4, 2014

export large data into excel using php[SOLVED]

how you're exporting to Excel, which could make a difference. If you're simply exporting as a comma or tab separated value file, then trying to open it with Excel, then this is a real problem.

If you're exporting to a real Excel file, then there's a few options.

Excel 2007 (xlsx) supports more than 64k rows in a worksheet, so that might be an option for you; though if you try to open in Excel 2003 (even with the compatibility pack), it will error.

Alternatively, the 64k limit for Excel (xls) files is 54k rows per worksheet. You could split your rows across several worksheets, so (for example) a workbook with 4 worksheets would allow you to export 256k rows.follow the example below
<?php

    //Connection
    $server = "localhost";  //Host name
    $username = "root"; //DB username
    $password = ""; //DB Password
    $dbname = "database"; //DB name
    $tablename = "table"; //table name
    $rows_per_page =5; //Total number of records to present in each page
   
    //DB connection
    $conn =  mysql_connect($server,$username,$password);
    mysql_select_db($dbname,$conn);
   
    //Queries
    $querycount = "SELECT count(*) FROM $tablename";
    $query = "SELECT *  FROM $tablename";
   
    //For page count
    $result = mysql_query($querycount) or trigger_error("SQL", E_USER_ERROR);
    $query_data = mysql_fetch_row($result);
    $numrows = $query_data[0];
    $lastpage      = ceil($numrows/$rows_per_page); //Calculate total number of files to be generated
    $pageno = 1; //intialize page as 1
   
    //Generating files in CSV format
    for($i=1; $i<=$lastpage; $i++) {
       
        //Fixing limit
        $limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
        $filename = "master_data_" . $i . ".csv";
        $fp = fopen($filename, "w");
       
        $res = mysql_query("$query $limit");
       
        // fetch a row and write the column names out to the file
        $row = mysql_fetch_assoc($res);
        $line = "";
        $comma = "";
        foreach($row as $name => $value) {
        $line .= $comma . '"' . str_replace('"', '""', $name) . '"';
        $comma = ",";
        }
        $line .= "\n";
        fputs($fp, $line);
       
        // remove the result pointer back to the start
        mysql_data_seek($res, 0);
       
        // and loop through the actual data
        while($row = mysql_fetch_assoc($res)) {
       
        $line = "";
        $comma = "";
        foreach($row as $value) {
        $line .= $comma . '"' . str_replace('"', '""', $value) . '"';
        $comma = ",";
        }
        $line .= "\n";
        fputs($fp, $line);
        }
        $pageno = $pageno+1; //count page number for next file generation
       
    }
    fclose($fp);

?>