Hello

I've been searching around to see if there is away in my sql query to get only the last 100,000 or so results

I'm making a db backup/ftp script and I have several tables that get quite large for example spider tracking table.

So for that table I would only be interested in the last 100000 entries for my emergency backup sql dump.

So I have $TableCleanArr which contains a list of tables that I'd like to keep the last 100000 entries or so.

So I'm not sure if it's possible to have something like $Limit get only the last entries.

$TableCleanArr = array('BannersClicks','BannersShows','ProfilesTrack','SpiderTracks','Votes','VotesPhotos');

function backup_table_content($table)
{
    GLOBAL $TableCleanArr;
        IF ( in_array($table, $TableCleanArr ) ){
             $Limit = " ";
        }
        $res = db_res("SELECT * FROM `$table` $Limit ");
        while($row = mysql_fetch_row($res))
        {
                $insert = "INSERT INTO `$table` VALUES (";

            for($j=0; $j<mysql_num_fields($res);$j++)
            {
                    if(!isset($row[$j]))
                            $insert .= "NULL,";
                    else if($row[$j] != "")
                            $insert .= "'".addslashes($row[$j])."',";
                    else $insert .= "'',";
            }

            $insert = ereg_replace(",$","",$insert);
            $insert .= ");\n";
            output_backup_data( $insert );
    }
}

Any help would be great🙂

    Ok maybe I need to stop reading and start thinking...lol

    I'm not sure if this will work yet but and I don't know if it's the best approach, if you have a better approach please feel free to let me know

    
function backup_table_content($table)
{
    GLOBAL $TableCleanArr;

    $res1 = db_res("SELECT * FROM `$table`");
    IF ( in_array($table, $TableCleanArr ) ){
         $Rows = mysql_num_rows ($res);
         IF ( $Rows >= 100000 )
              $Limit = "LIMIT $Rows-100000, $Rows";

    }
    $res = db_res("SELECT * FROM `$table` $Limit");
    while($row = mysql_fetch_row($res))
    {
            $insert = "INSERT INTO `$table` VALUES (";

            for($j=0; $j<mysql_num_fields($res);$j++)
            {
                    if(!isset($row[$j]))
                            $insert .= "NULL,";
                    else if($row[$j] != "")
                            $insert .= "'".addslashes($row[$j])."',";
                    else $insert .= "'',";
            }

            $insert = ereg_replace(",$","",$insert);
            $insert .= ");\n";
            output_backup_data( $insert );
    }
     mysql_free_result($res );
     mysql_free_result($res1 );
}

      You do realize that without an order by the result order is pretty much random. it just so happens that myisam tables return tuples in the order they were originally inserted, whether they were updated or not.

      Change that to innodb and woops, it all goes out the window.

      You really need to use an order by SOME FIELD that identifies the order you want.

      Then, you can do ORDER BY FIELDNAME DESC limit 100,000 and voila, you're done. and it can use an index (depedning on your db / planner / table handler etc...)

        Write a Reply...