Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Oct 2010
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Delete rows with smallest id value

    Okay,

    How would I go about deleting say 10 rows from mysql db where that have the least id value, or the highest id value...or just the first 10 or last 10 rows...thanks in advance

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,502
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    What is the name of your MySQL table and some column names?

  • #3
    New Coder
    Join Date
    Oct 2010
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Table Name = urls

    Columns would be id, url, comment_submitted, used

  • #4
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,502
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    Untested script ... not sure if this will work correctly or not, but it might be enough to get you started ...

    PHP Code:
    <?php

    //choices
    // 1= the least 10
    // 2= the most 10
    // 3= 10 from the top and 10 from the bottom
    // pick the choice.
    $choice=1;

    // connect to your database here

    if($choice==1){
    $query="SELECT * FROM urls ORDER BY id DESC LIMIT 0,10";
    }
    if(
    $choice==2){
    $query="SELECT * FROM urls ORDER BY id ASC LIMIT 0,10";
    }
    if(
    $choice==3){
    $query="SELECT * FROM urls ORDER BY id DESC LIMIT 0,10 UNION SELECT * FROM urls ORDER BY id ASC LIMIT 0,10 UNION ORDER BY id ASC";
    }
     
    // display results of query here

    ?>

  • #5
    New Coder
    Join Date
    Oct 2010
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts
    appreciate the help, but all of them choice 1/2 delete the last 10 and choice 3 doesnt do anything.

  • #6
    New Coder
    Join Date
    Oct 2010
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Okay, This is what it echos: Resource id #2, meanwhile the smallest id in the table is 40392


    PHP Code:
    <?php
    $con 
    mysql_connect("localhost","*****","*****");
    mysql_select_db("dubstep3_project1");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }


    //choices 
    // 1= the least 10 
    // 2= the most 10 
    // 3= 10 from the top and 10 from the bottom 
    // pick the choice. 
    $choice=1

    // connect to your database here 

    if($choice==1){ 
    $querymysql_query("SELECT * FROM urls ORDER BY id DESC LIMIT 0,10"); 

    if(
    $choice==2){ 
    $querymysql_query("SELECT * FROM urls ORDER BY id ASC LIMIT 0,10"); 

    if(
    $choice==3){ 
    $querymysql_query("SELECT * FROM urls ORDER BY id DESC LIMIT 0,10 UNION SELECT * FROM urls ORDER BY id ASC LIMIT 0,10 UNION ORDER BY id ASC"); 

      
    // display results of query here 
    echo $query;   







    ?>

  • #7
    New Coder
    Join Date
    Oct 2010
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I changed it again and now it echos: ArrayArrayArrayArrayArrayArrayArrayArrayArrayArray

    PHP Code:
    <?php
    $con 
    mysql_connect("localhost","******","******");
    mysql_select_db("dubstep3_project1");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }


    //choices 
    // 1= the least 10 
    // 2= the most 10 
    // 3= 10 from the top and 10 from the bottom 
    // pick the choice. 
    $choice=1

    // connect to your database here 

    if($choice==1){ 
    $query1mysql_query("SELECT * FROM urls ORDER BY id DESC LIMIT 0,10"); 

    if(
    $choice==2){ 
    $query2mysql_query("SELECT * FROM urls ORDER BY id ASC LIMIT 0,10"); 

    if(
    $choice==3){ 
    $query3mysql_query("SELECT * FROM urls ORDER BY id DESC LIMIT 0,10 UNION SELECT * FROM urls ORDER BY id ASC LIMIT 0,10 UNION ORDER BY id ASC"); 

    while (
    $res mysql_fetch_array($query1))

      {
    // display results of query here 
    echo $res;   

    }





    ?>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •