SQL Statement request - Easy if you know how! ;)

[alfaowner]

hi, could anyone help me write a sql code to do the following:

Every hour, I wish to ask cron to run my /forum/admincp/includes/cron/postnuke.php Inside theis file I put:

PHP Code:

<?php 
//connect to database 
mysql_connect("localhost", "my db username", "myPW"); 
mysql_select_db("my db name"); 
//empty pn userdb and fill it with fresh userdata 
$query=mysql_query("delete from nuke_users"); 
$query=mysql_query("INSERT INTO nuke_users (uid,uname,email,url,user_sig,pass,user_icq) select userid,username,email,homepage,signature,password,icq from user"); 
$query=mysql_query("update nuke_users set umode='nested', theme='ExtraLite',timezone_offset='14.0'"); 

mysql_close(); 
?>

It connects every hour and updates my post nuke with all the current users and edited passwords etc... However, i cannot quite get it to work with VB3, what if anythink do i need to change? ANy ideas welcomed!

It connects to the DB and deletes all my post nuke users, but nothing else.

Thanks again!

[Andreas]

Hmm ... wouldn't it be easier to modify PostNuke to verify accounts against your existing vB 3 user table?

[alfaowner]

Originally Posted by KirbyDE

Hmm ... wouldn't it be easier to modify PostNuke to verify accounts against your existing vB 3 user table?

Hey, would it? I suppose going by your post, yes.... But is that hard, do you know how to?

Ive been playing with this for 5 hours now Would you help me with some guidance? or could anyone else commment or advise this? i seache dthis forum but to no avail

[Modin]

I agree with KirbyDE... once your member list gets large that'll become one intensive script.

It's hard to say how difficult it would be to make postnuke use your vb database, but it would be the better way to go.

[alfaowner]

well, maximum 10,000 ever members i suppose, so not too bad

[Andreas]

I'll take a look into it within the next few days and keep you posted.

[alfaowner]

Would this work?

<FONT size=2>

PHP Code:

<?php 
//connect to database 
mysql_connect("localhost", "DBUSENAME", "DBPASS"); 
mysql_select_db("DBNAME");
 
mysql_query("DROP TABLE IF EXISTS ".$user_prefix."_users"); 
echo "<p>Table ".$user_prefix."_users deleted!</p><br>"; 
mysql_query("CREATE TABLE ".$user_prefix."_users ( uid int(11) NOT NULL auto_increment, name varchar(60) NOT NULL default '', uname varchar(25) NOT NULL default '', email varchar(255) NOT NULL default '', femail varchar(255) NOT NULL default '', url varchar(255) NOT NULL default '', user_avatar varchar(30) default NULL, user_regdate varchar(20) NOT NULL default '', user_icq varchar(15) default NULL, user_occ varchar(100) default NULL, user_from varchar(100) default NULL, user_intrest varchar(150) default NULL, user_sig varchar(255) default NULL, user_viewemail tinyint(2) default NULL, user_theme int(3) default NULL, user_aim varchar(18) default NULL, user_yim varchar(25) default NULL, user_msnm varchar(25) default NULL, pass varchar(40) NOT NULL default '', storynum tinyint(4) NOT NULL default '10', umode varchar(10) NOT NULL default '', uorder tinyint(1) NOT NULL default '0', thold tinyint(1) NOT NULL default '0', noscore tinyint(1) NOT NULL default '0', bio tinytext NOT NULL, ublockon tinyint(1) NOT NULL default '0', ublock tinytext NOT NULL, theme varchar(255) NOT NULL default '', commentmax int(11) NOT NULL default '4096', counter int(11) NOT NULL default '0', newsletter int(1) NOT NULL default '0', user_posts int(10) NOT NULL default '0', user_attachsig int(2) NOT NULL default '0', user_rank int(10) NOT NULL default '0', user_level int(10) NOT NULL default '1', PRIMARY KEY (uid) )");
 
echo "<p>Table ".$user_prefix."_users created!</p><br>"; 
$result = mysql_query("SELECT * FROM user userid"); 
$all = mysql_num_rows($result); 
echo "<p>You have total $all users in your vb user table.<br>Now, we'll transfer them into nuke's $user_prefix_users table</p><br>";
 
$query=mysql_query("INSERT INTO ".$user_prefix."_users (uid,uname,email,url,pass,user_icq,user_aim,user_yim) select userid,username,email,homepage,password,icq,aim,yahoo from user");
 
?>

[Modin]

I don't believe you can do an insert like that... I believe you'd have to insert each member separately.

replacing

$query=mysql_query("INSERT INTO ".$user_prefix."_users (uid,uname,email,url,pass,user_icq,user_aim,user_yim) select userid,username,email,homepage,password,icq,aim,yahoo from user");

with

while($user = mysql_fetch_array($result))
{
  $query=mysql_query("INSERT INTO ".$user_prefix."_users (uid,uname,email,url,pass,user_icq,user_aim,user_yim) VALUES ('$user[userid]', '$user[username]', '$user[email]', '$user[url]', '$user[password]', '$user[icq]', '$user[aim]', '$user[yahoo]'");
}

but with 10k users, that equals 10k queries every time this runs...

[alfaowner]

hmmm, well, i so love the hacked postnuke ive made, but i cannot bear to have it and require two seperate logins, its madness.

[Modin]

it shouldn't be too difficult to make postnuke use your vbulletin login. I haven't had time to try it yet... will soon