MyBB Community Forums

Sorry for such a basic question, but i am developing a very niche plugin with my minimal knowledge. I get many problems, but I usually sort them out after awhile. But for some reason, i just can't get my plugin to create a database table.

function turnorder_install()
{
	global $db;
    $charset = $db->build_create_table_collation();
    if(!$db->table_exists("turnorder")){
		/*
		$a=1;
		$columnNumber="";
		while($a<=10){
			$columnNumber .= "turn".$a." VARCHAR(30),
			";
			$a++;
		}
		rtrim($columnNumber, ",");

		$createTable = "CREATE TABLE mybb_turnorder (
		id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		thread SMALLINT(5) NOT NULL,
		lastPosition TINYINT(2) DEFAULT 1 UNSIGNED,
		".$columnNumber." )";
		*/
		$createTable = "CREATE TABLE mybb_turnorder (
		id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		thread SMALLINT(5) NOT NULL,
		lastPosition TINYINT(2) DEFAULT 1 UNSIGNED,
		turn1 VARCHAR(30),
		turn2 VARCHAR(30),
		turn3 VARCHAR(30),
		turn4 VARCHAR(30),
		turn5 VARCHAR(30),
		turn6 VARCHAR(30),
		turn7 VARCHAR(30),
		turn8 VARCHAR(30),
		turn9 VARCHAR(30),
		turn10 VARCHAR(30))";
		$tableOrder = mysqli_query($db, $createTable);
	}
}

I am thankful for any insight on this. Thank you in Advance.

P.S. the commented out was my original idea for future convenience, if I decide to change the turn field amount.

I can't say I know a whole lot about PHP, but I feel as though I know more around SQL as I've done a couple lessons on it in the past. Regardless, I copied your query into PHPMyAdmin and there were some errors.

I'm unfamiliar with 'UNSIGNED', but whatever it is, was the main issue, and I looked up queries that used UNSIGNED and none used it like you did. I edited the query and this worked fine for me(To insert into PHPMyAdmin)

CREATE TABLE mybb_turnorder(
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  thread SMALLINT(5) NOT NULL,
  lastPosition TINYINT(2) UNSIGNED DEFAULT 1,
  turn1 VARCHAR(30),
  turn2 VARCHAR(30),
  turn3 VARCHAR(30),
  turn4 VARCHAR(30),
  turn5 VARCHAR(30),
  turn6 VARCHAR(30),
  turn7 VARCHAR(30),
  turn8 VARCHAR(30),
  turn9 VARCHAR(30),
  turn10 VARCHAR(30))

Hopefully someone can confirm, but did you want to try use that query instead and see if it works?

Any attributes your are giving to a column must be after the column name and column type. Looking over your proposed table structure though it is not going to be optimal by any means, especially if you need to do a search on it. The less columns that use TEXT or VARCHAR the better you will be for performance. I don't see why you don't just drop all the extra turn columns and put an Index on the column thread. You will have much better performance in this way. You can still sort them by id to display the order of turns.

Thank you,

The UNSIGNED was the problem, I also changed the Varchars to INT after a little change in the process.