PDA

View Full Version : SQL Problems. Please help!



Da 0wner
01-20-2009, 05:51 AM
Does anyone know how to select a column in a row you provide so that you can edit it even if it dosen't matter what's in it, with PHP?

Example:

I have a table with 3 rows in it.

ID Name
1 Whitewater
2 KyleIs1337
3 Darktemplar242

Let's say I want to remove row 2 which contains my name. When I remove it I am left with Whitewater and Darktemplar242 whose ID's are 1 and 3.
I want the ID column to be redone so that when a record is deleted it will loop through the rows and change the ID column of each to the row number. In this case, it would change Darktemplar242's ID to 2.

The way I tried to do this was get the number of rows in the table, perform a loop that many times which, in the loop, set the ID to the number in the loop it is, and in theory, that would be correct. So does anyone know of anyway to do this?

ian.
01-20-2009, 06:03 AM
dunno how to do it in php or w/e, but..

something like

if(ID=erased) then (ID > erased) - 1..

something so that all the ID's above that ID are subtracted by 1 :)

Dumpin
01-20-2009, 06:36 AM
you mean like this?

mysql_query("INSERT INTO example
(name, age) VALUES('Timmy Mellowman', '23' ) ")

Da 0wner
01-20-2009, 06:51 AM
Nope Dumpin. It's a bit more complicated. I just want a way to select a column in a certain row regardless of what's in it.

Something like this, of course it wouldn't look like it though.


mysql_query("INSERT INTO Users (id) VALUES ('1') ROW ('2'));


Of course that wouldn't work because I don't think there is a row parameter but you get what I mean hopefully.

Wizzup?
01-20-2009, 10:45 AM
Use UPDATE.

UPDATE Table SET Value=SomeValue WHERE This>That OR This<That.

Like that. When you delete the row, you could do something like this:

UPDATE Table SET ID=ID - 1 WHERE ID > (RemovedID - 1)

(Not sure if it this will work, but you get the general idea.)

Please note:
Those ID's, why would you want to lower them in the first place?
That's not really the purpose of Databases. Maybe you could tell us what you plan to do, and we could help you out further.

Floor66
01-20-2009, 08:30 PM
I don't see WHY you would want to reset the values..
But, for example, if ID's are: 1, 2, 4; make them into 1, 2, 3:


<?php
include "config.php"; //DB Connection here

$sql = mysql_query("SELECT * FROM `users` ORDER BY `id` ASC");
$i = 0;
while ($row = mysql_fetch_array($sql))
{
if (($row["id"] - $i) > 1)
{
$x = $i + 1;
$up = mysql_query("UPDATE `users` SET `id` = '$x' WHERE `username` = '$row[username]'");
if (!$up)
{
die(mysql_error());
}
}
$i++;
}
?>


Tested. Works.
Took me a while to get the formula though.

Da 0wner
01-21-2009, 02:10 AM
Heh floor didn't test yet but if it works i <3 you...thanks!

Floor66
01-21-2009, 06:38 AM
The only problem is, that you'll have to run this every time a new ID gets created/removed.
Because you can't really alter the column `id`, so when it's 1, 2, 3, 4; and NR/3 gets removed, we have 1, 2, 4 and we use my script to make it 1, 2, 3; the next db entry will be 1, 2, 3, 5; so you'd have to run the script every time.

I'd suggest you do;


<?php
include "config.php"; //DB Connection here

function updateIDs()
{
$sql = mysql_query("SELECT * FROM `users` ORDER BY `id` ASC");
$i = 0;
while ($row = mysql_fetch_array($sql))
{
if (($row["id"] - $i) > 1)
{
$x = $i + 1;
$up = mysql_query("UPDATE `users` SET `id` = '$x' WHERE `username` = '$row[username]'");
if (!$up)
{
die(mysql_error());
}
}
$i++;
}
}

$in = mysql_query("INSERT INTO `users` (username) VALUES ('$value')");
if ($in)
{
updateIDs();
}
else
{
die(mysql_error());
}
?>


So you can call the function every time you need.
I've also tested it with a single ID in the table: 12345.
If I run my script then, it'll change 12345 to 1 !

Wizzup?
01-21-2009, 10:25 AM
Heh floor didn't test yet but if it works i <3 you...thanks!

Did you even try my method?
His method is alot slower... Mine is purely MySQL, rather than lots of PHP. (And just 1 query)

EDIT: And it works fine, too.

Anyway. Consider this:

By removing ID's, you might destroy relationships with other tables. So you'll have to update all of those too.
The AUTO_INCREMENT value also isn't decreased, so you'll have to run this update after EVERY insert, as well. (With either floor's script, or my *fast* query.)


For the above two reasons, what you are trying to do makes no sense. Why use ID's if you don't have relationships for them? Or, if you have relationships for them, are you going to update those tables too? That would be pure SQL hell.


Just my cents.

Floor66
01-21-2009, 02:52 PM
That's true, Wizzup, yours goes faster. I can't disagree :p

Da 0wner
01-22-2009, 02:03 AM
Actually, I don't use auto increment but rather my own type of it so that I can change how it increments and etc.