6 replies to this topic

[cxn]

Hi all.

I've been writing some lines recently trying to work this issue out. I have a table where I want to store a list of names of films.
I got it to work (inserting the data, outputting also works) but I can't manage to get to delete a row.

Each row consists of another film, with:

- ID (primary key)
- Title
- Director
- Year

Each time a row gets deleted, it seems like the enitre row is gone.

For example if I insert 4 titles, and delete those 4 afterwards, the next film added will be marked as #5, instead of #1. Does that make sense?

Thanks in advance!


Here's the code:

<?php


    $usr = " ";
    $pwd = " ";
    $db = " ";
    $host = " ";

    $cid = mysql_connect($host,$usr,$pwd);
    if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

    if ($task=="del") {

        $SQL = " DELETE FROM overview ";
        $SQL = $SQL . " WHERE id = $id ";
        mysql_db_query($db, $SQL, $cid);

    }
?>




<?php

echo "<table border='0'>
<tr>
<th>#</th>
<th>Name</th>
<th>Director</th>
<th>Year</th>
<th>Delete</th>
</tr>";

    $SQL = " SELECT * FROM overview ";
    $retid = mysql_db_query($db, $SQL, $cid);
    if (!$retid) { echo( mysql_error()); }
    else {
        echo ("<p><table cellpadding=4>\n");
        while ($row = mysql_fetch_array($retid)) {
            $id = $row["id"];

  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['title'] . "</td>";
  echo "<td>" . $row['director'] . "</td>";
  echo "<td>" . $row['year'] . "</td>";
  echo ("<td><a href=\"list_del.php?id=$id&task=del\">x</a></td>");
  echo "</tr>";
  }

        echo ("</table>");
    }






mysql_close($cid);
?> 

[John]

That is how it is suppose to work. If you want to reset the auto-increment value try something along the following lines:

ALTER TABLE tablename AUTO_INCREMENT = 1

[cxn]

Thanks for the reply.

Do I have to do this manually or can I let the php script run it each time a new query gets sent?

[SoN9ne]

The auto increment value is used to determine which ID to assign to the newly inserted row. If you are constantly dropping the data and want your rows to start at id 1 you will need to do this after you delete the data form the database. This will ensure that the new rows will start at id 1 instead of the next id in line. Is there any reason that you are purposely wanting to reset the increment back to 1? If it is just for a count you could just as easily increment $i=0; this way you wont need to worry about your id.

[cxn]

Well it just seems so sloppy, no?
I'm just a newb but I like to have my things sorted out well. What's good about NOT resetting it? Wouldn't it just take more time from the server side (cause it's: 1, 5, 23,34,25,101, ...) ?

All help is welcome :-)

Thanks

[SoN9ne]

That depends heavily on your db structure and the size of the program. To put it simply, for most projects you wouldn't even notice, you are talking milliseconds... If you are using a huge system and the db is fairly large, you may notice a second difference but I'm doubtful. If you want to keep the increment ID in line, that's great.

I found this post that you may find useful. This shows you how to re number the data in the table to keep the ids in line. Not what you are doing but you may find it useful.

[Orjan]

Normally, the server doesn't care about the id number at all. it just stores it, and makes sure it knows the order among them. if its 1,2,3 or 10, 34, 56 isn't important to the computer.