Jump to content

Archived

This topic is now archived and is closed to further replies.

Orio

Auto_increment

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.

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
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.



Share this post


Link to post
Share on other sites
Wierd that I havent thought about it lol

Thanks alot to all of you!!

Orio.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
*bump*
No one got an idea what wrong?

Orio.

Share this post


Link to post
Share on other sites
up
Anyone? *desperate*

Orio.

Edit- Nvm, problem solved \o/

Share this post


Link to post
Share on other sites

×

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.