Jump to content

Auto_increment


Orio

Recommended Posts

Hello,

I have a ranking script. What basicly it does is gets the name, rating and comments from the user, and put it with the date and his IP into a table. The rank is automaticly being added in the first column via auto_increment.
But lets say I want to delete the row of the first one to finish. This is where things mess up.
I do the following thing:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']DELETE[/span] [color=green]FROM[/color] [color=orange]`ranks`[/color] [color=green]WHERE[/color] id[color=orange]=[/color][color=red]'1'[/color] [!--sql2--][/div][!--sql3--]
But now, the "first" one in the ranks will be ranked 2, the third one 3 etc'...

Is there a way updating it to always start with 1?

Thanks alot,
Orio.
Link to comment
Share on other sites

[!--quoteo(post=366096:date=Apr 18 2006, 01:45 PM:name=Orio)--][div class=\'quotetop\']QUOTE(Orio @ Apr 18 2006, 01:45 PM) [snapback]366096[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hello,

I have a ranking script. What basicly it does is gets the name, rating and comments from the user, and put it with the date and his IP into a table. The rank is automaticly being added in the first column via auto_increment.
But lets say I want to delete the row of the first one to finish. This is where things mess up.
I do the following thing:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']DELETE[/span] [color=green]FROM[/color] [color=orange]`ranks`[/color] [color=green]WHERE[/color] id[color=orange]=[/color][color=red]'1'[/color] [!--sql2--][/div][!--sql3--]
But now, the "first" one in the ranks will be ranked 2, the third one 3 etc'...

Is there a way updating it to always start with 1?

Thanks alot,
Orio.
[/quote]

DELETE FROM 'ranks';

That will restart it at 1 :)
I'm confused why you want it to restart at 1? Should a scenario ever arise where the id was referenced on other tables (that you forgot to remove) they will now be pointing to invalid data.

You'd always want the number to increment.
Link to comment
Share on other sites

[!--quoteo(post=366108:date=Apr 18 2006, 02:14 PM:name=lead2gold)--][div class=\'quotetop\']QUOTE(lead2gold @ Apr 18 2006, 02:14 PM) [snapback]366108[/snapback][/div][div class=\'quotemain\'][!--quotec--]
DELETE FROM 'ranks';

That will restart it at 1 :)
I'm confused why you want it to restart at 1? Should a scenario ever arise where the id was referenced on other tables (that you forgot to remove) they will now be pointing to invalid data.

You'd always want the number to increment.
[/quote]


I would think you would want to keep your "ID" as is and add a "Rank" field to your table. The "ID" should serve as record number which, as lead2gold pointed out, you can reference from other tables if needed. Having a "Rank" field would allow you to reassign the rankings without interfering with your db structure.

Link to comment
Share on other sites

Thanks for the tip, but now my next question will be- if I'll delete the one that is ranked number two (lets say because number 1 submitted twice by mistake or something), how can I make automaticly number three to be ranked second, the fourth to be third etc'?

Thanks again,
Orio.
Link to comment
Share on other sites

Hi again,
I have another problem.

I added another column in the table called "Ranked" that its value can be either 0 or 1. If the user is in the ranking (defualt) the value of "Ranked" is 1, and when I want to kick someone out I change the value to 0.
But I have a problem with the "kicking out" script.
I got a form page to do that that diplays the table and asking me to name the one I want to kick:
[code]<html><head><title>Kick from ranks</title></head><body>
<center><h3>Everyone in the ranks</h3></center><br>
Choose one to get out of ranks (name):<br><br>
<?php

include("dbinfo.inc.php"); //This page connects to the db, no errors here for sure

$query="SELECT * FROM `ranks` WHERE Ranked='1'";
$result=mysql_query($query) or die (mysql_error());
$rows=mysql_num_rows($result); echo(mysql_error());
?>

<table border="2" cellspacing="2" cellpadding="2">
<tr>
<td><font face="Arial">#</font></td>
<td><font face="Arial">Name</font></td>
<th><font face="Arial">Rating</font></td>
<td><font face="Arial">Date</font></td>
</tr>

<?php
$i=0;
while ($i < $rows) {
$rank= $i+1;
$name=mysql_result($result,$i,"Name");
$rating=mysql_result($result,$i,"Rating");
$date=mysql_result($result,$i,"date");
?>

<tr>
<td><font face="Arial"><?php echo "$rank"; ?></font></td>
<td><font face="Arial"><?php echo "$name"; ?></font></td>
<td><font face="Arial"><?php echo "$rating"; ?></font></td>
<td><font face="Arial"><?php echo "$date"; ?></font></td>
</tr>

<?php
$i++;
}
echo "</table>";
mysql_close();
?>
<br><br><form action="deldis.php" method="POST">
<input type="text" name="del"> <input type="submit">
</form></body></html>[/code]

And this is the deldis.php file (the form's action):
[code]<?php
$name=$_POST['del'];

include("dbinfo.inc.php"); //This page connects to the db, no errors here for sure


$query = "UPDATE `ranks` SET Ranked='0' WHERE Name='$name'";
mysql_query($query); echo(mysql_error());

$query2="SELECT * FROM `ranks`";
$result=mysql_query($query2); echo(mysql_error());
$rows = mysql_num_rows($result); echo(mysql_error());

echo "<b><center>The new table:</center></b><br><br>";
?>
<table border="2" cellspacing="2" cellpadding="2">
<tr>
<td><font face="Arial">#</font></td>
<td><font face="Arial">Name</font></td>
<td><font face="Arial">Rating</font></td>
<td><font face="Arial">Date</font></td>
<td><font face="Arial">Comments</font></td>
<td><font face="Arial">IP</font></td>
</tr>

<?php
$i=0;
$j=1;
while ($i < $rows) {
$rank = $j;
$name=mysql_result($result,$i,"Name");
$rating=mysql_result($result,$i,"Rating");
$date=mysql_result($result,$i,"Date");
$comments=mysql_result($result,$i,"Comments");
$ip=mysql_result($result,$i,"IP");
$isranked=mysql_result($result,$i,"Ranked");
if($isranked==0){$rank="X";} else{$j++;};
?>

<tr>
<td><font face="Arial"><?php echo "$rank"; ?></font></td>
<td><font face="Arial"><?php echo "$name"; ?></font></td>
<td><font face="Arial"><?php echo "$rating"; ?></font></td>
<td><font face="Arial"><?php echo "$date"; ?></font></td>
<td><font face="Arial"><?php echo "$comments"; ?></font></td>
<td><font face="Arial"><?php echo "$ip"; ?></font></td>
</tr>
<?php
$i++;
}
echo "</table>";
mysql_close();
?>[/code]

But the table is not being updated! Whats wrong? I went over the script alot of times, checked if I've got mistakes in tutorials and such... No idea...

Btw, here's the SQL query that I have created the table with:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]CREATE TABLE `ranks` (id int(4) NOT NULL auto_increment,Ranked int(1) NOT NULL,Name varchar(15) NOT NULL,Rating int(3) NOT NULL,Date varchar(7) NOT NULL,Comments varchar(300) NOT NULL,IP varchar(16) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id)) [!--sql2--][/div][!--sql3--]

Thanks again,
Orio.

PS. Sorry for the long post.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.