/    Sign up×
Community /Pin to ProfileBookmark

How to update id in mySQL

Copy linkTweet thisAlerts:
Jan 09.2004

This is what I’m trying to do:
– Delete the first table row (id=1)
– Rename the new first row (id=2) to id=1
– Rename the new second row (id=3) to id=2
– etc…all the way to id=100

This is how far I’ve got:

$cid = mysql_query(“SELECT count(id) FROM table”);
$r = mysql_fetch_row($cid);
$rowcount = $r[0];

if ($rowcount > 100) {
$cid = mysql_query(“DELETE FROM table WHERE id = 1”);
for ($x=1; $x<=$rowcount; $x++)
{
$newid = $x–;
$update = mysql_query(“UPDATE table SET id=’$newid’ WHERE id=’$x'”);
}

It seems to produce an internal server eror: how do i fix it?

to post a comment
PHP

7 Comments(s) ↴

Copy linkTweet thisAlerts:
@pnajJan 10.2004 — Probably just a typo, but you've missed a '}' at the end, to close the 'if' statement.

The problem was using the $x-- inside the for. It doesn't just return $x - 1, it actually sets $x to $x -1 as well.

Here's my version:
[code=php]
$cid = mysql_query("SELECT count(id) FROM table");
$r = mysql_fetch_row($cid);
$rowcount = $r[0];

if ($rowcount > 100){
mysql_query("DELETE FROM table WHERE id = 1");
for($x = 1; $x <= $rowcount; $x++){
mysql_query("UPDATE table SET id='".($x - 1)."' WHERE id='".$x."'");
}
}
[/code]

A couple of other tips.

For DELETE and UPDATE, you don't [I]need[/i] to return the mysql_query into a $variable.

Don't 'embed' variables in strings.

It's slow and hard to debug.

So, ...

Don't do: "My name is $name";

Do : "My name is ".$name;
Copy linkTweet thisAlerts:
@Tim158authorJan 12.2004 — Thanks I'll try that. I'll try changing my code to reflect your advice too.

?
Copy linkTweet thisAlerts:
@hammerslaneJan 12.2004 — just a question about what you said pnaj, what happens different if you embed a variable in a string?

just interested... thanks
Copy linkTweet thisAlerts:
@pnajJan 12.2004 — Hi Hammerslane,

Two things I know about (may be more) ...

1) It's definitely easier to make typo's if you embed the string.

2) It's less efficient ... PHP takes longer to parse strings that have embedded variables.

Pnaj.
Copy linkTweet thisAlerts:
@pyroJan 12.2004 — I think it's more personal preference. The extra time it would take PHP to parse the code if you use it's variable interpolation features is increadibly minute. As far as easier to debug, I guess it depends. I usually use the variable interpolation with double quotes, and don't seem to notice it being a problem for me. If you are not going to include your variable in your quotes, you'd be best of using single quotes, as this way you won't have problems should use need to use a $ sign in the string, and this way PHP won't even look for variables that it needs to expand.
Copy linkTweet thisAlerts:
@pnajJan 12.2004 — Hi Pyro,

In the end, I agree ... but I notice that a lot of bugs that are posted to the forum stem from embedding variables ... especially for newbies.

The efficiency bit is just to say that it isn't the other way around and you're not [I]gaining[/I] efficiency by embedding.

For the experts, of course, it's up to them.

B.W.

pnaj.
Copy linkTweet thisAlerts:
@pyroJan 12.2004 — I agree. ?
×

Success!

Help @Tim158 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.19,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...