Jump to content


Photo

Auto_increment


  • Please log in to reply
8 replies to this topic

#1 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 18 April 2006 - 05:45 PM

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] FROM `ranks` WHERE id='1' [!--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.
Think you're smarty?

(Gone until 20 to November)

#2 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 18 April 2006 - 06:14 PM

[!--quoteo(post=366096:date=Apr 18 2006, 01:45 PM:name=Orio)--][div class=\'quotetop\']QUOTE(Orio @ Apr 18 2006, 01:45 PM) View Post[/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] FROM `ranks` WHERE id='1' [!--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.


#3 freakus_maximus

freakus_maximus
  • Members
  • PipPipPip
  • Advanced Member
  • 177 posts

Posted 18 April 2006 - 07:30 PM

[!--quoteo(post=366108:date=Apr 18 2006, 02:14 PM:name=lead2gold)--][div class=\'quotetop\']QUOTE(lead2gold @ Apr 18 2006, 02:14 PM) View Post[/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.



#4 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 18 April 2006 - 08:42 PM

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.
Think you're smarty?

(Gone until 20 to November)

#5 complex05

complex05
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 18 April 2006 - 09:02 PM

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.





#6 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 18 April 2006 - 09:08 PM

Wierd that I havent thought about it lol

Thanks alot to all of you!!

Orio.
Think you're smarty?

(Gone until 20 to November)

#7 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 19 April 2006 - 05:03 PM

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:
<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>

And this is the deldis.php file (the form's action):
<?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();
?>

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.
Think you're smarty?

(Gone until 20 to November)

#8 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 20 April 2006 - 12:34 PM

*bump*
No one got an idea what wrong?

Orio.
Think you're smarty?

(Gone until 20 to November)

#9 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 21 April 2006 - 09:31 AM

up
Anyone? *desperate*

Orio.

Edit- Nvm, problem solved \o/
Think you're smarty?

(Gone until 20 to November)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users