Auto_increment

Orio · April 18, 2006

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.

lead2gold · April 18, 2006

[!--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.

freakus_maximus · April 18, 2006

[!--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.

Orio · April 18, 2006

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.

complex05 · April 18, 2006

You don't need to pull the rank from the database... just do

$sql = "SELECT * FROM table ORDER BY id";
$query = mysql_query($sql);
$rank = 1;
while($data = mysql_fetch_array($query))
{
echo $rank;
rank++;
}

then the id isn't even important.

Orio · April 18, 2006

Wierd that I havent thought about it lol

Thanks alot to all of you!!

Orio.

Orio · April 19, 2006

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.

Orio · April 20, 2006

*bump*
No one got an idea what wrong?

Orio.

Orio · April 21, 2006

up
Anyone? *desperate*

Orio.

Edit- Nvm, problem solved \o/

Sign In

Auto_increment

Recommended Posts

Orio

Link to comment

Share on other sites

lead2gold

Link to comment

Share on other sites

freakus_maximus

Link to comment

Share on other sites

Orio

Link to comment

Share on other sites

complex05

Link to comment

Share on other sites

Orio

Link to comment

Share on other sites

Orio

Link to comment

Share on other sites

Orio

Link to comment

Share on other sites

Orio

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information