userid = "0" on all posts

[Cybernetec]

I have situation where for all posts in database userid is "0" (table post). Don't ask how...

username column is OK.

Is there a query which will fix this?

[Danny.VBT]

Do all posts in the table have the userid field set to "0"?

The userid is set to 0 in some specific situations, for example for Guests.

[r007]

Is there a query which will fix this?

Hey Cybernetec!

Sorry to hear about your problem :( I wrote a little PHP script that should update everything for you.

I ran it on my test forum and it worked just fine... so hopefully it will work for you too!

Make sure you backup your database before running this... Just incase something with my script goes wrong.

<?php

// MAKE SURE YOU BACKUP YOUR FORUM BEFORE RUNNING THIS!!!

// Enter your info here!
$mysql_username = "username";
$mysql_password = "password";
$mysql_dbname = "vb";
$table_prefix = "";

$link = mysql_connect('localhost', $mysql_username, $mysql_password);
if (!$link)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db($mysql_dbname);

// Find all the posts without a username
$postsql = "SELECT username FROM `" . $table_prefix . "post` WHERE userid = '0'";

$postresult = mysql_query($postsql) or die(mysql_error());

echo "We need to update: " . mysql_num_rows($postresult) . " posts<br />";

while($postrow = mysql_fetch_array($postresult))
{
// Find the correct userid
$usersql = "SELECT userid FROM `" . $table_prefix . "user` WHERE username = '" . $postrow['username'] . "'";

$userresult = mysql_query($usersql) or die(mysql_error());

// If we found a userid for that username...
if(mysql_num_rows($userresult) == 1)
{
$userrow = mysql_fetch_array($userresult);
echo "'" . $postrow['username'] . "' is really ID #" . $userrow['userid'] . "<br />";

// Update the post database with the new ID.
$updatesql = "UPDATE `" . $table_prefix . "post` SET userid = '" . $userrow['userid'] . "' WHERE username = '" . $postrow['username'] . "'";

$updateresult = mysql_query($updatesql) or die(mysql_error());
}
else
{
echo "Could not find user '" . $postrow['username'] . "'!<br />";
}

}

mysql_free_result($postresult);

mysql_close($link);
?>
All you have to do is upload it to your website and run it once. Make sure you delete it after you are done.

Hope this helps,
-Robert

[Lats]

Is there a query which will fix this?
Yep...
UPDATE
post
LEFT JOIN
user
ON
post.username = user.username
SET
post.userid = user.userid

[Cybernetec]

Thank you very much guys!

I tried with Lats's query first, because it's easier to do, and it worked. Also saved r007's great script to my PC (might need it sometime).

Thanks again,

Cyb