Getting Started With Mysql - creating tables and insert data into them.

flashy

Apr 2 2008, 03:56 PM

Hi in this tutorial you will learn how to create tables and insert items into them.

First steps are to create the database - go into your cpanel and mysql databases, from there make an account and a database and then attach them together with all priviliges, call the database test and the account admin, with the pw as pass - or any other password.
We need to connect to the database so first in your php file (probably named index.php) - this is how to do it.

CODE

mysql_connect("localhost", "admin", "pass") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

The first line was to connect to the host as LOCALHOST as it is usual the databases locally, the username would be what the username would be (most servers require you to place your CPANEL username as a prefix - like PREFIX_USER, trap17 requires you to do so), and then your password.

The second line was to connect to the database that you created earlier, again you willneed to place in the prefix if you did so for the username.

Now we can start to make a table! We will make a guestbook in this tutorial.

CODE

// connect to the database
mysql_connect("localhost", "admin", "pass") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

//create a table
mysql_query("CREATE TABLE guestbook(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
comment VARCHAR(500))") or die(mysql_error());

The two lines starting from 'id' to '(id)' are the row id's - you'll see what i mean when you go into phpMyAdmin.
VARCHAR means variable characters - so any variable (abc!"£123) can be placed into it, you can use INT for numbers.
the VARCHAR '(30)' means how many characters are stored into this variable.

Now lets go insert some data into the tables

CODE

// connect to the database
mysql_connect("localhost", "admin", "pass") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// insert into tables
mysql_query("INSERT INTO guestbook(name, comment) VALUES('Jim', 'Hi nice site!')") or die(mysql_error());
mysql_query("INSERT INTO guestbook(name, comment) VALUES('Bob', 'Lovely weather')") or die(mysql_error());

That created two lines of data into 'guestbook', 2 names - Jim and bob, and 2 comments - 'Hi nice site' and 'Lovely weather'

Now lets retrieve the variables in the database - but we must store them as an array and read them as an array

CODE

// connect to the database
mysql_connect("localhost", "admin", "pass") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

$get = mysql_query("SELECT * FROM guestbook") or die(mysql_error());

while($array = mysql_fetch_array($get)){
echo "Name: ".$array['name']."<br />";
echo "Comment:<br />".$array['comment']."<br /><br />";
}

The * after SELECT means ALL - in other words SELECT ALL FROM guestbook.

I hoped i helped getting started!

shadowx

Apr 2 2008, 07:10 PM

Nice little tutorial

It would be good if you continued the series to modify table contents and structure with DELETE, UPDATE, SORT etc...

I always found creating tables using PHP versus PMA really difficult to remember the syntax but in truth there isnt much to it!

Absolute Zer0

Apr 2 2008, 08:49 PM

Wow, very well written, I already feel like learning MySQL more!... After I learn PHP...

ADD REPLY / Got an Opinion!	Remove these ADs!	RAPID SEARCH!	Free Web Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE