Php: using mysql

thingie-348

Member since: Nov. 27, 2005

Offline.

Member Level 07 Blank Slate

Php: using mysql 2006-08-11 17:07:53

This tutorial is about MySql. MySql is a powerful database language (something like a storage thing) that needs moderate Php to be used.

NOTE: if you dont know Php, put a good comment about this post and dont pass the line

------------------------------------------
------------------------------------------
------------------------------------------
--

Now, lets get started:

Introduction

Mysql is a powerful database language, thet you can download it from here. If you are not sure if you/your server have/has it or not, then just write

<?php
phpinfo() ;
?>

and if exists a table titled MySql, then you/your server have/has it!

One VERYpowerful tool you can use to easily manage MySql is phpMyAdmin (I'm sorry, I dont know where to download it, but if you have a browser-based php-server probably it works). With this tool, you can do lot of things with MySql only will be very difficult.

Now, a simple question: What is a database?
A database is a conjunction of tables. A good example will be like having a forum, where the tables could be:

Users
|-----------------------------------------
---------------------------------|
| Username | Password | Number of posts|
|-----------------------------------------
---------------------------------|
|windymager | men00b345 | 139 |
|-----------------------------------------
---------------------------------|
| afro_ninja | MePwNsAlL | 2487 |
|-----------------------------------------
---------------------------------|

Posts
|-----------------------------------------
------------------------------------------
---------|
| Name | Creator | Text |
|-----------------------------------------
------------------------------------------
---------|
|5000 users were banned | WadeFulp | I didnt liked their face|
|-----------------------------------------
------------------------------------------
---------|
| Php: using mysql | windymager(lol) |blablablablablablabla|
|-----------------------------------------
------------------------------------------
---------|

...And you can take and retreive the data from there. (Note: the password thing isnt true)

Connecting to MySql:

Just put some code like:

<?php
//set variables
$dir = "mydir" ;
$username = "windymager" ;
$password = "lol" ;

//connect to mysql
$worked = mysql_connect ($dir, $username, $password) ;

//Check if mysql_connect returned zero, that means that has done an error
if (!$worked)
{
//mysql_error fucntion says the last mysql error
die ("Could not connect to MySql cause: " . mysql_error()) ;
}

echo "You are now connected to mysql!" ;
?>

Note: the direction, username, and password of mysql are generally given by your host

Creating a database:

NOTE: generally, hosts dont let you ceating a database this way. Theres hight probabilty you cannot do this, if that happens, just use the one your serer provided to you

<?php
$worked = mysql_connect("host","username","password"
);

if (!$worked)
{
die('Could not connect cause: ' . mysql_error());
}
//till here, I hope you know from the last part

$name = "database" ;
$sql = "CREATE DATABASE $name";

//mysql_query command executes the command on the string in mysql languaje, in this case is "CREATE DATABASE" command, witch created a database

$result = mysql_query ($sql) ;

//and returns zero on error
if ($result)
{
echo "Database my_db created";
}
else
{
echo "Error creating database: " . mysql_error();
}
?>

Creating a table:

NOTE: if you have PhpMyAdmin, is very convenient to use that program instead of executing code

To create a table, you must use the CREATE TABLE command inside the mysql_query, with this sintaxis:

CREATE TABLE name
{
column_name1 type extra,
column_name2 type extra,
...
last_column_name type extra
}

types:

INT (number)
BIGINT (long number)
DOUBLE (decimal number)
VARCHAR(size) (string)
TEXT (string with 65536 chars max)
LONGTEXT (string with 4294967295 chars max)
DATE (holds date in format (yyyy-mm-dd))
TIME (holds time in format (hh:mm:ss))

There are more types, but sincerely, forget them, only use these.

extras:
NOT_NULL (if this field has not a value, it will be 0 for ints and "" for strings instead on NULL)
AUTO_INCREMENT (the variable increments each time a row is created)

Also there are more, but these are more important.

Example:

<?php
$con = mysql_connect ("host", "username", "password") ;
//mysql_select_db command must be used to use and create tables
mysql_select_db ("db_name", $con) ;

//create the table
$sql = "CREATE TABLE users
{
username VARCHAR(15) ;
password VARCHAR(15) ;
post_number INT NOT_NULL ;
}"

mysql_query ($sql) ;
?>
...And thats it. Now, you will have a table like this

users
|-----------------------------------------
----------------------------|
| Username | Password | post_number|
|-----------------------------------------
----------------------------|

REST OF THE GUIDE WILL BE PUTTED ON A BIT (I WAS OUT OF SPACE) SO JUST WAIT)

Wade started a flamewar on my post!

His super sweet 5000

Comment to have your name added!

…

authorblues

Member since: Jun. 21, 2005

Offline.

Member Level 12 Blank Slate

Response to Php: using mysql 2006-08-11 17:31:17

At 8/11/06 05:07 PM, windymager wrote: This tutorial is about MySql. MySql is a powerful database language (something like a storage thing) that needs moderate Php to be used.

spamburger already wrote this tutorial
http://www.newground../topic.php?id=527971

…

thingie-348

Member since: Nov. 27, 2005

Offline.

Member Level 07 Blank Slate

Response to Php: using mysql 2006-08-11 17:41:13

Php: Main

note: sorry for the table thing on the last post, it is a disalignement with NG editing and outputing

Adding values to tables:

For adding values to this table, you must use the mysql command INSERT, with sintaxis:

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')

so, if you want to add an user you must put

INSERT INTO users (username, password, post_number) VALUES ('windymager'. 'password'. '0')

now, heres the famous example:

<?php
//..we do all the burocracy...
$con = mysql_connect ("host", "username", "password") ;
mysql_select_db ("database_name") ;`

$username = "windymager" ;
$password = "password" ;

//now we insert the rows
mysql_query ("INSERT INTO users
(username, password)
VALUES
('$username', '$password')") ;

//please note I havent included the post_number column, thats cause its setted to NOT_NULL, is precertain 0

$username = "stamper"
$password = "password" ;

mysql_query ("INSERT INTO users
(username, password)
VALUES
('$username', '$password')") ;
?>

Now the table will be:

Check values of tables

Now heres the problem: I want to see if a user has putted the right password. How do I do?

this is also done with mysql_query function, so lets start:

first thing we want to do is to see where is the username. This is done putting:

$res = mysql_query ("SELECT * FROM users WHERE username = 'windymager'") ;
$var = mysql_fetch_rows ($res) ;

echo $var['password'] ;

now, I think you deserve litle explanation:

the SELECT(what) FROM (where) mysql keyword selects on (where) all the rows from the column (what) (if (what) is set to *, means all the columns) and returns a code. This code is ridden by mysql_fetch_rows, and returns an array with all the parts of the table, or false if there are no more rows
Also, theres a keyword mysql_num_array that returns the number or rows that is on there, so if you want to select ALL data from a table, you could write:

<?php
$con = mysql_connect ("host", "username", "password") ;
mysql_select_db ("database_name", $con) ;

//blah blah blah blah, all this stuff you know it

$res = mysql_query ("SELECT * FROM users") ;

//here you can see the number fields
echo "There are ". mysql_num_rows($res) . " fields<br>" ;

while ($field = mysql_fetch_array)) //if there are no more fields, it returns false and the while finishes
{
echo "The password of " . $field['username'] . " is " . $field['password'] . " and has " . $field['post_number'] . " posts<br>"
}
?>

The WHERE keyword tells to search only where something happens. It can use the operators:

= (is equal, equivalent to php ==)
!= (isnt equal)
> (is grater than)
< (is smaller that)
>= (is grater or equal than)
<= (is smaller or equal than)
AND (equivalent to php &&)
OR (equivalent to php ||)
XOR (exclusive or, so x XOR y will mean in php (x || y) && !(x && y)

so now we could search for the password of someone putting the code:

<?php
$con = mysql_connect ("host", "username", "password") ;
mysql_select_db ("db_name", $con) ;

$username = "windymager" ;

$res = mysql_query ("SELECT * FROM users WHERE usr = '$username'") ;
$res2 = mysql_fetch_array($res) ;

echo "The password of $username is " . $res2['password'] ;
?>

The ORDER BY keyword

Now lets say another problem: you have the list of users, but you want to order it alphabetically, what will you do?

The keyword ORDER BY (into mysql_query) orders the data for some column, so to order alphabetically:

mysql_query ("SELECT * FROM users ORDER BY username") ;

its easy enought to understand. Now the mysql_fetch_array keyword will return it in the alphabetical order or username.

Theres a keyword, DESC, that makes them ordering descendantly, so putting

mysql_query ("SELECT * FROM users OBDER BY username DESC") ;
will order them from z to a.

Conclusion:

MySql, as I said earlier, is a very powerful tool. Infact, most people that use php use it for mysql. It can be used for everything, even newgrounds has his own tables!

Wade started a flamewar on my post!

His super sweet 5000

Comment to have your name added!

…

thingie-348

Member since: Nov. 27, 2005

Offline.

Member Level 07 Blank Slate

Response to Php: using mysql 2006-08-11 17:42:50

At 8/11/06 05:31 PM, authorblues wrote:
At 8/11/06 05:07 PM, windymager wrote: This tutorial is about MySql. MySql is a powerful database language (something like a storage thing) that needs moderate Php to be used.
spamburger already wrote this tutorial
http://www.newground../topic.php?id=527971

So I was all day writing this tutorial for nothing?? crap

Wade started a flamewar on my post!

His super sweet 5000

Comment to have your name added!

…