Creating Fully functional mods [tutorial request]

[ɔɔɔɔɔɔuɥoɾ]

yes it does that the second time I refresh, I do that to see if the table was created, and it was, but I'm deleting the entire database every time it fails to do everything correctly

Thanks for the edited code sangwe11, but it still didnt work

[Arantor]

I didn't note whether it was echo or die, either way behaviour isn't really desirable.

In reality you'd almost never create a database from PHP except in the case of an install script. You would even more rarely do it from SMF.

[sangwe11]

yes it does that the second time I refresh, I do that to see if the table was created, and it was, but I'm deleting the entire database every time it fails to do everything correctly

Thanks for the edited code sangwe11, but it still didnt work

what error is it giving ?

[ɔɔɔɔɔɔuɥoɾ]

Well, Im not getting any errors, Im just not getting a table created with the primary key.

Despite its rare use in creating SMF mods, the tutorial on the w3 school says to create that.

I'm stuck on that part, To be honest, I dont really care what script/query I use, So long as I can create a table with a primary key thats that step complete, I can then move on

So Arantor,

How is the typical install script?

[Arantor]

Have a look at the mods I pointed out to you. They are typical install scripts. They create a table with a primary key IIRC.

[ɔɔɔɔɔɔuɥoɾ]

Have a look at the mods I pointed out to you. They are typical install scripts. They create a table with a primary key IIRC.

I did exactly that, I got a parse error, but that was because I just learned you need to ?> then put the query, continue PHP after where necessary.

Anyways, I tried this directly copied from your quota mod, with some minor changes to match the table I'm using

<?php


                    



$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE Persons
(
FirstName varchar(15),
LastName varchar(15),
Age int
)";

// Execute query
mysql_query($sql,$con);

?>
<?php
// Adding the Primary Key 
?>
// create the tables
db_query("
CREATE TABLE `Persons` (
  `ID_GROUP` smallint(5) unsigned NOT NULL,
  `FirstName` int(10) unsigned NOT NULL,
  `exempt` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`ID_GROUP`)
)

// Close connection
mysql_close($con);

[Arantor]

You don't need to create a database. You're already going to be using the SMF database, so just mysql_select_db that instead.

But if you're modding, it's even more done for you. The install script runs when the mod package is installed, as you can see from the code in my quota mod.

[sangwe11]

You don't need to create a database. You're already going to be using the SMF database, so just mysql_select_db that instead.

But if you're modding, it's even more done for you. The install script runs when the mod package is installed, as you can see from the code in my quota mod.

I think he should learn how to create, delete, make tables, write too, update, select from ect. . mysql databases outside of smf first.

Having good knowledge of php helps alot, and even though it takes a while to get you head round how smf works, it will make it all the easier.

But OP, I tell you what.

When I get home from school (yes thats school, 14 ftw ) I will write you a php file that does all of the above queries, and comment it heavily for you.

If you don't hear back from me by 5 - 6pm here (around 10 hours from the time this post was posted) then drop me a PM as I have probably forgotten

[ɔɔɔɔɔɔuɥoɾ]

ok thanks, although in the meantime I'm gonna be trying myself seeing whats wrong with the code

[ɔɔɔɔɔɔuɥoɾ]

This Query works within Navicat and PhpMyAdmin

ALTER TABLE persons
ADD PRIMARY KEY(`firstname`)

but it will not work within the PHP file, this is weird...

$sql = "ALTER TABLE persons
ADD PRIMARY KEY(`firstname`)"

[ɔɔɔɔɔɔuɥoɾ]

$result = mysql_query('ALTER TABLE `persons` ADD PRIMARY KEY ( `firstname` )');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}


YEY!!!
This is the one that finally added the primary key to "firstname"

[sangwe11]

$result = mysql_query('ALTER TABLE `persons` ADD PRIMARY KEY ( `firstname` )');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}


YEY!!!
This is the one that finally added the primary key to "firstname"

Haha, just spotted your mistake

persons is not a SQL command, and so (as you found out) has to be put in ' + '

[ɔɔɔɔɔɔuɥoɾ]

So..

Primary keys, only 1 column can have one?

I got a SQL error when trying to apply to all 3 columns, multiple Primary keys defined.

I directed copied the wrong code (example) from w3schools, wonder why it don't work 
oh well, this is working, so I have completed that step and moving on.

Lesson for creating tables from a PHP file, complete code below:

<?php

// Attention - Creating a database, table with fields and primary key

// set up any variables used
mysql_query($sql,$con);

// We need to connect to the MySQL server - use the following code
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Now we can create the database
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "<center>Database created successfully</center>";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// now to create the table
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE persons
(
firstname varchar(25),
lastname varchar(25),
age int
)";

// This will add the primary key to the field chosen

$result = mysql_query('ALTER TABLE `persons` ADD PRIMARY KEY ( `firstname` )');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

// I hope some may find this useful, as I certainly have! :)
?>

[sangwe11]

So..

Primary keys, only 1 column can have one?

I got a SQL error when trying to apply to all 3 columns, multiple Primary keys defined.

I directed copied the wrong code (example) from w3schools, wonder why it don't work 
oh well, this is working, so I have completed that step and moving on.

Lesson for creating tables from a PHP file, complete code below:

<?php

// Attention - Creating a database, table with fields and primary key

// set up any variables used
mysql_query($sql,$con);

// We need to connect to the MySQL server - use the following code
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Now we can create the database
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "<center>Database created successfully</center>";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// now to create the table
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE persons
(
firstname varchar(25),
lastname varchar(25),
age int
)";

// This will add the primary key to the field chosen

$result = mysql_query('ALTER TABLE `persons` ADD PRIMARY KEY ( `firstname` )');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

// I hope some may find this useful, as I certainly have! :)
?>

As far as I know, and as far as I have found out, only one column can have a primary key.

This is because its the field that is "unique" to that table entry.

If the table has more than one, then it just gets confusing for both the database engine and the coder (thats how I was told about the issue, long long ago)

You shouldn't need more than one primary key.

They are only used to get individual records, which is useful when trying to get a individual user or something.

The primary key HAS to be something unique, so setting the PK as first_name wouldn't work, there might be two Bobs

I tend to either call my PK id, or id_whateveriamstoring

So if its a members table, I would use id_members as my PK

That clear up any issues ?

[ɔɔɔɔɔɔuɥoɾ]

yea, I think its to prevent duplicate entries, as when I refreshed the page again, it stopped that from happening with an error about the duplicate entries

I have had success in entering data into the table too (INSERT)

even from a HTML form, I will post that later as I need to go now

[sangwe11]

yea, I think its to prevent duplicate entries, as when I refreshed the page again, it stopped that from happening with an error about the duplicate entries

I have had success in entering data into the table too (INSERT)

even from a HTML form, I will post that later as I need to go now

No need to post your code if its working, only post it if its not.

I know how to do that, so I don't need the code for it

I just saves work for us, saves us reading it.

By all means post it if you think something in it could be simplified, and I will tell you how to

I am a bummer for not checking my code when coding. I will start to code something one night, and it would be really long and complex, and then I will come along the next morning, and half the length and complexity of the code.

It always pays to check, and check back a few hours later, not straight away, or you might still think your way is best

[Arantor]

You can only have one primary key per table, however a primary can contain many columns.

[ɔɔɔɔɔɔuɥoɾ]

yea, the simple codes that work via the w3 tutorial I will not post, but anything that is not working using the method on the w3 tutorials, I certainly help others with by posting my findings

[ɔɔɔɔɔɔuɥoɾ]

OK found and resolved a problem
QUERY Update.

W3 schools says:
mysql_query("UPDATE Persons SET Age = '36'
WHERE FirstName = 'Peter' AND LastName = 'Griffin'");


The working code is:
mysql_query("UPDATE persons SET age='36'
WHERE firstname='Peter' AND lastname='Griffin'")

the problem is here the spaces after and before the " = "

ignor the lowercase letters, they must match though, I purposely made my fields with lowercase so I have to modify the code, thus learn something

[Arantor]

Spaces should be just fine (all my queries have spaces in). If it's failing with spaces, something else is wrong.