12 Replies - 850 Views - Last Post: 04 October 2012 - 07:29 AM Rate Topic: -----

#1 kiasta  Icon User is offline

  • D.I.C Regular

Reputation: 23
  • View blog
  • Posts: 263
  • Joined: 18-November 07

CREATE TABLE Question

Posted 03 October 2012 - 07:54 AM

I'm learning about PHP and I have a question that I can't seem to find with google or a lot of tutorials, they simply either dismiss the question as not important or just assume you know. My question is this: where would I put a CREATE TABLE function? Let me elaborate a little bit. Say I create a simple script to register an account, for instance:

<form name="register" action="register.php" method="post">
    Username: <input type="text" name="username" />
    Password: <input type="password" name="pass1" />
    Password Again: <input type="password" name="pass2" />
    E-mail: <input type="text" name="email" />
    <input type="submit" value="Register" />
</form>


Ok so we have a basic registration form, now to add the data to a database (using mysql in my case) we would have to create a TABLE. For example:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(15) NOT NULL UNIQUE,
    password VARCHAR(64) NOT NULL,
    email VARCHAR(64) NOT NULL,
    PRIMARY KEY(id)
);


Or something similar... Ok so we create the TABLE, now that's fine but every time a user registers an account this code is there. Is it safe to be executed every time a user registers an account? Is there a better method for doing this, a better place to add the CREATE TABLE function to just run once?

I'll give you the full code I have for a better explanation.

register.php

<form name="register" action="register.php" method="post">
    Username: <input type="text" name="username" />
    Password: <input type="password" name="pass1" />
    Password Again: <input type="password" name="pass2" />
    E-mail: <input type="text" name="email" />
    <input type="submit" value="Register" />
</form>
<?
$username=$_POST['username'];
$pass1=$_POST['pass1'];
$pass2=$_POST['pass2'];
$email=$_POST['email'];
if(strlen($username) > 15)
    header('Location: register.php');
if($pass1 != $pass2)
    header('Location: register.php');
if(strlen($email) > 64)
    header('Location: register.php');

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(15) NOT NULL UNIQUE,
    password VARCHAR(64) NOT NULL,
    email VARCHAR(64) NOT NULL,
    PRIMARY KEY(id))";
mysql_query($query);
mysql_close();
?>


I'm still quite new to php but I am a bit confused about the reasoning and the tutorials are all the same they don't specify or explain why this function is run every time a user registers an account or if it is even safe. Perhaps y'all can enlighten me?

Is This A Good Question/Topic? 0
  • +

Replies To: CREATE TABLE Question

#2 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3099
  • View blog
  • Posts: 10,885
  • Joined: 08-August 08

Re: CREATE TABLE Question

Posted 03 October 2012 - 08:42 AM

You should not be creating tables for each user. Tables should contain information for many users. Do not use any tutorial that:
  • Creates tables for users.
  • Uses deprecated, insecure MySQL functions and not prepared statements.
  • Copies from a super global without doing anything (like validating). Example $x = $_POST['x'] is bad code.
  • Uses header to redirect back to the same server where include or require can cut down on bandwidth consumption.

I could go on and on... there are many versions of bad tutorials out there.
Was This Post Helpful? 1
  • +
  • -

#3 kiasta  Icon User is offline

  • D.I.C Regular

Reputation: 23
  • View blog
  • Posts: 263
  • Joined: 18-November 07

Re: CREATE TABLE Question

Posted 03 October 2012 - 08:51 AM

Hmm, well I guess I should find another tutorial to learn from. Thanks for the advice. I'll just look on here for tutorials instead of google.
Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6107
  • View blog
  • Posts: 23,657
  • Joined: 23-August 08

Re: CREATE TABLE Question

Posted 03 October 2012 - 08:56 AM

What tutorial are you using?
Was This Post Helpful? 0
  • +
  • -

#5 kiasta  Icon User is offline

  • D.I.C Regular

Reputation: 23
  • View blog
  • Posts: 263
  • Joined: 18-November 07

Re: CREATE TABLE Question

Posted 03 October 2012 - 09:23 AM

Well I was primarily using:

http://tinsology.net...-the-right-way/

But w3schools was suggested and they also teach about $_GET and $_POST here: http://w3schools.com/PHP/php_forms.asp

What is a good tutorial than? I don't want to learn anything that can possibly compromise the security of my future website.

*EDIT* Forgot about this one for a primary: http://www.freewebma...ials/phpmysql/2

This post has been edited by kiasta: 03 October 2012 - 09:53 AM

Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3099
  • View blog
  • Posts: 10,885
  • Joined: 08-August 08

Re: CREATE TABLE Question

Posted 03 October 2012 - 09:56 AM

The first link doesn't create multiple tables. It shows you how to create a table, but it doesn't intend for you to continually create tables. That's why they say:

Quote

Even though we could use usernames to uniquely identify users we will use the id for this instead.

They're identifying each user in the one table by id numbers.

The second link shows a simple HTML form and PHP retrieving information submitted from that form. Note that they do not copy values from $_POST. They use the array. I would say the one weakness of that tutorial is that it mixes PHP and HTML. Those are two languages that don't even get processed on the same computer!

The first link does however make several of the mistakes I mentioned in post #2.
Was This Post Helpful? 1
  • +
  • -

#7 kiasta  Icon User is offline

  • D.I.C Regular

Reputation: 23
  • View blog
  • Posts: 263
  • Joined: 18-November 07

Re: CREATE TABLE Question

Posted 03 October 2012 - 10:00 AM

Oh ok, but what would a good implementation be for creating tables? It doesn't specify where, just how. I've created a first_run.php file that has the create table functions but is that good practice?

This post has been edited by kiasta: 03 October 2012 - 10:03 AM

Was This Post Helpful? 0
  • +
  • -

#8 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6107
  • View blog
  • Posts: 23,657
  • Joined: 23-August 08

Re: CREATE TABLE Question

Posted 03 October 2012 - 10:51 AM

Sort of. The right way to do it is to setup your database before you make your website available for the first time.
Was This Post Helpful? 2
  • +
  • -

#9 kiasta  Icon User is offline

  • D.I.C Regular

Reputation: 23
  • View blog
  • Posts: 263
  • Joined: 18-November 07

Re: CREATE TABLE Question

Posted 04 October 2012 - 12:50 AM

The problem with doing something like that is I don't have direct access to the mysql database with my web hosting. I can use the phpmyadmin that they have setup but 3rd party programs like MySQL GUI or sqlyog are not permitted. It kind of sucks but I didn't know about that until after I already purchased the site from them about 6 months ago. Anyways I'll have to setup the database with php, which is why I'm a little wary of doing so improperly. I'll just look for examples around here when I get some free time. Thanks for all the advise guys I appreciate it!
Was This Post Helpful? 0
  • +
  • -

#10 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6107
  • View blog
  • Posts: 23,657
  • Joined: 23-August 08

Re: CREATE TABLE Question

Posted 04 October 2012 - 04:21 AM

Why wouldn't you use phpmyadmin? That's what it's there for.
Was This Post Helpful? 0
  • +
  • -

#11 kiasta  Icon User is offline

  • D.I.C Regular

Reputation: 23
  • View blog
  • Posts: 263
  • Joined: 18-November 07

Re: CREATE TABLE Question

Posted 04 October 2012 - 06:07 AM

To be completely honest, the interface is awful. I can use it, but uploading .sql scripts is very buggy. I almost always get permission errors or other types of errors when trying to create tables through scripts. For instance I tried to upload a database that would hold every zip code in the united states but every time I tried uploading the script I would get errors, either permission errors or other errors because of the limited functionality of their version of phpmyadmin.

I wouldn't have a problem normally. It worked perfectly fine at the web hosting server at work, I uploaded it with MySQL gui and I received no errors. It created all the tables just fine and I was able to create a price system based on distance for a transport company they were starting up. Then again they have a web hosting that allows root access to their mysql database.
Was This Post Helpful? 0
  • +
  • -

#12 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6107
  • View blog
  • Posts: 23,657
  • Joined: 23-August 08

Re: CREATE TABLE Question

Posted 04 October 2012 - 06:18 AM

Quote

To be completely honest, the interface is awful.


Well I won't disagree with you there. I have little experience with it myself, mainly as a means to test something someone asks here. I don't use a hosting service, I have root shell access to all the systems I've ever used, and use a database versioning and upgrading script system for incremental updates. The initial DB creation is handled via a BASH script which creates the DB and calls out to SQL scripts to create the individual modules once the DB is created.
Was This Post Helpful? 0
  • +
  • -

#13 kiasta  Icon User is offline

  • D.I.C Regular

Reputation: 23
  • View blog
  • Posts: 263
  • Joined: 18-November 07

Re: CREATE TABLE Question

Posted 04 October 2012 - 07:29 AM

Yeah, a dedicated server is my next goal but it's a little out of my price range at the moment. It would be nice to have root shell access, but I am not going to spend more money unless I start getting to the point of actually needing it. For now ftp access and php is good enough just to learn and play around.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1