Jump to content

help with sql update script

Symbiot

By Symbiot
in PHP Coding Help

 Share

Recommended Posts

Symbiot Newbie

Symbiot

Posted
Posted

Hi

 

i've got a script to migrate some tables.

 

This is most of it.. just missing the db connection (not needed here)


$link = mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_selectdb($db,$link) or die(mysql_error());
$ag_forums_to_kunena_categories = array();

mysql_query("TRUNCATE bzhv8_kunena_messages");
mysql_query("TRUNCATE bzhv8_kunena_messages_text");
$sql = "
SELECT
 p.id AS messages_id, 
 u.name AS messages_name, 
 a.jos_id AS messages_userid, 
 p.message AS messages_text_message,
 p.posted AS messages_time,
 p.edited AS messages_modified_time,
 p.edited_by AS messages_modified_by,
 p.topic_id AS messages_thread,
 t.forum_id AS messages_catid
 
FROM jos_agora_posts AS p
LEFT JOIN jos_agora_users AS a ON a.username = p.poster
LEFT JOIN jos_agora_topics AS t ON t.id = p.topic_id
LEFT JOIN bzhv8_users AS u ON u.id = a.jos_id
 
ORDER BY p.id ASC
";
 
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_object($res))
{
	echo $row->messages_id;
	//echo "<br />";
	//echo $row->messages_name;
	//echo "<br />";
	//echo $row->messages_userid;
	//echo "<br />";
	//echo $row->messages_text_message;
	//echo "<br />";
	//echo $row->messages_time;
	//echo "<br />";
	//echo $row->messages_modified_time;
	//echo "<br />";
	//echo $row->messages_modified_by;
	//echo "<br />";
	//echo $row->messages_thread;
	//echo "<br />";
	//echo $row->messages_catid;
	//echo "<br />";
	
	//if($row->messages_userid == '')
	//echo "userid er nul";
	//{
	//	$row->messages_userid = 0;
	//}
 
	$cat_id = $ag_forums_to_kunena_categories[$row->messages_catid];
	
	if ($cat_id != 0)
	{
		echo $cat_id;
		echo "kategori id";
		echo "<br />";
		echo "lige før den skal indsætte";
		$sql = "INSERT INTO `bzhv8_kunena_messages` VALUES (".$row->messages_id.", 0, ".$row->messages_thread.", ".$cat_id.", '".$row->messages_name."', ".$row->messages_userid.", '', '', ".$row->messages_time.", NULL, 0, 0, 0, 0, 0, 0, 0, 0, '')";
		echo "her bør den køre sql(1)";
		$sql2 = "INSERT INTO `bzhv8_kunena_messages_text` VALUES (".$row->messages_id.", '".mysql_escape_string($row->messages_text_message)."')";
		echo $sql;
		echo "lige efter anden insert echo sql";
		echo "<br />";
		echo $sql2;
		echo "<br />";
		echo "her skulle den vise anden insert";
		mysql_query($sql) or die(mysql_error());
		mysql_query($sql2) or die(mysql_error());
	}
	else
	{
	
	
    }
}
mysql_close();

But something is not working..

 

first.. ignore the echo's of course.. just me trying to debug.. same with the else statement... 

 

from what I can tell.. and my php knowledge is non-existant.. this part:

 

$cat_id = $ag_forums_to_kunena_categories[$row->messages_catid];

 

it what's causing the problem.. it appears that the $cat_id is not getting any data..

 

if copy the sql line and run it against my database via phpmyadmin then it works as it should..  but well.. something isn't right.. 

there are some other stuff in the script that does the same as the above.. but with different tables and they work just fine..

 

example:

mysql_query("TRUNCATE bzhv8_kunena_topics");
$sql = "
SELECT
 t.id AS topic_id,
 t.poster AS topic_poster,
 t.subject AS topic_subject, 
 t.posted AS topic_first_post_time, 
 t.last_post AS topic_last_post_time, 
 t.last_post_id AS topic_last_post_id, 
 t.last_poster AS topic_last_poster, 
 t.num_views AS topic_hits, 
 t.num_replies AS topic_posts, 
 t.closed AS topic_locked, 
 t.sticky AS topic_hold, 
 t.moved_to AS topic_moved_id, 
 t.forum_id AS topic_category_id, 
 t.question AS topic_poll_id,
 p.id AS topic_first_post_id, 
 p.posted AS topic_first_post_time, 
 a.jos_id AS topic_first_post_userid,
 p.message AS topic_first_post_message
 
FROM jos_agora_topics AS t
LEFT JOIN jos_agora_users AS a ON a.username = t.poster
LEFT JOIN jos_agora_posts AS p ON t.posted = p.posted
ORDER BY t.id ASC
";
 
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_object($res))
{
	echo $row->topic_id;
	echo "<br />";
	if($row->topic_first_post_userid == '')
	{
		$row->topic_first_post_userid = 0;
	}
 
	$cat_id = $ag_forums_to_kunena_categories[$row->topic_category_id];
	echo $cat_id;
	echo "<br />";
	$sql = "INSERT INTO `bzhv8_kunena_topics` VALUES (".$row->topic_id.", ".$cat_id.", '".mysql_escape_string($row->topic_subject)."', 0, ".$row->topic_locked.", ".$row->topic_hold.", 0, ".$row->topic_posts.", ".$row->topic_hits.", 0, 0, 0, ".$row->topic_first_post_id.", ".$row->topic_first_post_time.", ".$row->topic_first_post_userid.", '".mysql_escape_string($row->topic_first_post_message)."', NULL, ".$row->topic_last_post_id.", ".$row->topic_last_post_time.", 0, NULL, NULL, '')";
	echo $sql;
	echo "<br />";
	mysql_query($sql) or die(mysql_error());
}

the above works fine..

 

Can someone take a look and give a hint or 2.. or just point at whatever the solution is :-)

Link to comment
https://forums.phpfreaks.com/topic/292105-help-with-sql-update-script/
Share on other sites
Barand Prolific Member

Barand

Posted
Posted 
$ag_forums_to_kunena_categories = array();
$cat_id = $ag_forums_to_kunena_categories[$row->messages_catid];

$ag_forums_to_kunena_categories is an empty array. I don't see you put anything into it in that code.

Symbiot Newbie

Symbiot

Posted
  • Author
Posted

hi guys

 

thanks for responding... I will have to look into the "depreated" part.. but I am not sure what to do.. 

just to show you the full script and see if it makes mores sense..

 

$ag_forums_to_kunena_categories = array();
$cat_id = $ag_forums_to_kunena_categories[$row->messages_catid];

$ag_forums_to_kunena_categories is an empty array. I don't see you put anything into it in that code.

 

 

.. you mention not putting in any data... 

Can you spot why the rest of the script seems to be working then? 

<?php
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// AGORA 3.0.x to Kunena 2.0.x converter //////////////////////////////////////////////////////////////////////////////////////
// Converter version 0.2.1                 //////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
//enter your database credentials here.
$host = "localhost";
$user = "user";
$pass = "pass";
$db = "testDB";
 
$link = mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_selectdb($db,$link) or die(mysql_error());
$ag_forums_to_kunena_categories = array();
 
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// converting Agora categories to Kunena categories using original category-ID ////////////////////////////////////////////////
mysql_query("TRUNCATE bzhv8_kunena_categories");
$sql = "
SELECT
 c.id AS categories_id,
 c.cat_name AS categories_name,
 c.disp_position AS categories_ordering, 
 c.enable AS categories_published
 
FROM jos_agora_categories AS c
 
ORDER BY c.disp_position ASC
";
 
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_object($res))
{
	echo $row->c.id;
	echo "<br />";
	$sql = "INSERT INTO `bzhv8_kunena_categories` VALUES (".$row->categories_id.", 0, '".mysql_real_escape_string($row->categories_name)."', '', 0, 0, 'joomla.group', 2, 2, 1, 8, 0, ".$row->categories_ordering.", ".$row->categories_published.", NULL, 0, '0000-00-00 00:00:00', 0, 0, 0, 0, '', '', '', 0, 'lastpost', 0, 0, 0, 0, 0, '{}')";
	echo $sql;
	echo "<br />";
	mysql_query($sql) or die(mysql_error());
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// converting Agora Forums to Kunena categories using modified category-ID ////////////////////////////////////////////////////
// first of all count how many agory categories are here. Start catid plus 1 for Forums ///////////////////////////////////////
$sqlnum = "SELECT * FROM jos_agora_categories";
$resnum = mysql_query($sqlnum);
$rows = mysql_num_rows($resnum); 
 
$maxidquery = "SELECT id FROM jos_agora_categories ORDER BY id DESC LIMIT 0,1";
$resmax = mysql_query($maxidquery);
$resmaxid = mysql_fetch_array($resmax);
$maxid = $resmaxid['id'];
 
$cat_mod_id=$maxid+1;
////////////////////////////////////////////////
$sql = "
SELECT
 f.id AS categories_id,
 f.enable AS categories_published,
 f.forum_name AS categories_name,
 f.forum_desc AS categories_description,
 f.num_topics AS categories_numTopics,
 f.num_posts AS categories_numPosts,
 f.last_post AS categories_last_post_time,
 f.last_post_id AS categories_last_post_id,
 f.disp_position AS categories_ordering,
 f.cat_id AS categories_parent_id,
 f.closed AS categories_locked
 
FROM jos_agora_forums AS f
 
ORDER BY f.disp_position ASC
";
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_object($res))
{
	echo $cat_mod_id;
	echo "<br />";
	if($row->categories_last_post_time == '')
	{
		$row->categories_last_post_time = 0;
		$row->categories_last_post_id = 0;
	}
	echo $row->categories_last_post_time;
	echo "<br />";
	$sql = "INSERT INTO `bzhv8_kunena_categories` VALUES (".$cat_mod_id.", ".$row->categories_parent_id.", '".mysql_real_escape_string($row->categories_name)."', '', 0, 0, 'joomla.group', 1, 2, 1, 8, 0, ".$row->categories_ordering.", 1, NULL, 0, '0000-00-00 00:00:00', 0, 0, 0, 0, '".mysql_real_escape_string($row->categories_description)."', '', '', 1, 'lastpost', ".$row->categories_numTopics.", ".$row->categories_numPosts.", 0, ".$row->categories_last_post_id.", ".$row->categories_last_post_time.", '{\"access_post\":[\"6\",\"7\",\"2\",\"3\",\"4\",\"5\",\"8\"],\"access_reply\":[\"6\",\"7\",\"2\",\"3\",\"4\",\"5\",\"8\"]}')";
	echo $sql;
	echo "<br />";
	mysql_query($sql) or die(mysql_error());
	$ag_forums_to_kunena_categories [$row->categories_id] = $cat_mod_id;
	$cat_mod_id++;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
echo "ARRAY:";
echo "<br />";
print_r($ag_forums_to_kunena_categories);
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// convert topics from Agora to Kunena ////////////////////////////////////////////////////////////////////////////////////////
mysql_query("TRUNCATE bzhv8_kunena_topics");
$sql = "
SELECT
 t.id AS topic_id,
 t.poster AS topic_poster,
 t.subject AS topic_subject, 
 t.posted AS topic_first_post_time, 
 t.last_post AS topic_last_post_time, 
 t.last_post_id AS topic_last_post_id, 
 t.last_poster AS topic_last_poster, 
 t.num_views AS topic_hits, 
 t.num_replies AS topic_posts, 
 t.closed AS topic_locked, 
 t.sticky AS topic_hold, 
 t.moved_to AS topic_moved_id, 
 t.forum_id AS topic_category_id, 
 t.question AS topic_poll_id,
 p.id AS topic_first_post_id, 
 p.posted AS topic_first_post_time, 
 a.jos_id AS topic_first_post_userid,
 p.message AS topic_first_post_message
 
FROM jos_agora_topics AS t
LEFT JOIN jos_agora_users AS a ON a.username = t.poster
LEFT JOIN jos_agora_posts AS p ON t.posted = p.posted
ORDER BY t.id ASC
";
 
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_object($res))
{
	echo $row->topic_id;
	echo "<br />";
	if($row->topic_first_post_userid == '')
	{
		$row->topic_first_post_userid = 0;
	}
 
	$cat_id = $ag_forums_to_kunena_categories[$row->topic_category_id];
	echo $cat_id;
	echo "<br />";
	$sql = "INSERT INTO `bzhv8_kunena_topics` VALUES (".$row->topic_id.", ".$cat_id.", '".mysql_escape_string($row->topic_subject)."', 0, ".$row->topic_locked.", ".$row->topic_hold.", 0, ".$row->topic_posts.", ".$row->topic_hits.", 0, 0, 0, ".$row->topic_first_post_id.", ".$row->topic_first_post_time.", ".$row->topic_first_post_userid.", '".mysql_escape_string($row->topic_first_post_message)."', NULL, ".$row->topic_last_post_id.", ".$row->topic_last_post_time.", 0, NULL, NULL, '')";
	echo $sql;
	echo "<br />";
	mysql_query($sql) or die(mysql_error());
}
 
$sql = "
SELECT
 t.id AS topic_id, 
 t.last_post AS topic_last_post_time, 
 t.last_post_id AS topic_last_post_id, 
 t.last_poster AS topic_last_poster,
 a.jos_id AS topic_last_post_userid,
 p.message AS topic_last_post_message
 
FROM jos_agora_topics AS t
LEFT JOIN jos_agora_users AS a ON a.username = t.last_poster
LEFT JOIN jos_agora_posts AS p ON t.last_post_id = p.id
ORDER BY t.id ASC
";
 
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_object($res))
{
	echo $row->topic_id;
	echo "<br />";
	$topic_last_post_message = mysql_real_escape_string($row->topic_last_post_message);
 
	if($row->topic_last_post_userid == '')
	{
		$row->topic_last_post_userid = 0;
	}
 
	$cat_id = $ag_forums_to_kunena_categories[$row->topic_category_id];
	echo $cat_id;
	echo "<br />";
	$sql = "UPDATE `bzhv8_kunena_topics` SET `last_post_id` = ".$row->topic_last_post_id.",`last_post_time` = ".$row->topic_last_post_time.",`last_post_userid` = ".$row->topic_last_post_userid.",`last_post_message` = '".mysql_escape_string($row->topic_last_post_message)."',`last_post_guest_name` = NULL,`params` = '' WHERE  `bzhv8_kunena_topics`.`id` = ".$row->topic_id."";
	echo $sql;
	echo "<br />";
	mysql_query($sql) or die(mysql_error());
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
 
// convert Posts from Agora tu Kunena /////////////////////////////////////////////////////////////////////////////////////////
 
mysql_query("TRUNCATE bzhv8_kunena_messages");
mysql_query("TRUNCATE bzhv8_kunena_messages_text");
$sql = "
SELECT
 p.id AS messages_id, 
 u.name AS messages_name, 
 a.jos_id AS messages_userid, 
 p.message AS messages_text_message,
 p.posted AS messages_time,
 p.edited AS messages_modified_time,
 p.edited_by AS messages_modified_by,
 p.topic_id AS messages_thread,
 t.forum_id AS messages_catid
 
FROM jos_agora_posts AS p
LEFT JOIN jos_agora_users AS a ON a.username = p.poster
LEFT JOIN jos_agora_topics AS t ON t.id = p.topic_id
LEFT JOIN bzhv8_users AS u ON u.id = a.jos_id
 
ORDER BY p.id ASC
";
 
$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_object($res))
{
	echo $row->messages_id;
	echo "første test fejl";
	echo "<br />";
	if($row->messages_userid == '')
	echo "userid er nul";
	{
		$row->messages_userid = 0;
	}
 
	$cat_id = $ag_forums_to_kunena_categories[$row->messages_catid];
	if ($cat_id != 0)
	{
		echo $cat_id;
		echo "kategori id";
		echo "<br />";
		echo "lige før den skal indsætte";
		$sql = "INSERT INTO `bzhv8_kunena_messages` VALUES (".$row->messages_id.", 0, ".$row->messages_thread.", ".$cat_id.", '".$row->messages_name."', ".$row->messages_userid.", '', '', ".$row->messages_time.", NULL, 0, 0, 0, 0, 0, 0, 0, 0, '')";
		echo "her bør den køre sql(1)";
		$sql2 = "INSERT INTO `bzhv8_kunena_messages_text` VALUES (".$row->messages_id.", '".mysql_escape_string($row->messages_text_message)."')";
		echo $sql;
		echo "lige efter anden insert echo sql";
		echo "<br />";
		echo $sql2;
		echo "<br />";
		echo "her skulle den vise anden insert"
		mysql_query($sql) or die(mysql_error());
		mysql_query($sql2) or die(mysql_error());
	}
	else
	{
	echo $cat_id;
	echo "<br />";
	echo $ag_forums_to_kunena_categories[$row->messages_catid];
	
	}
}
mysql_close();
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.