UGN Security Forums
My ProfileMember DirectoryLogin
Search our ForumsView our FAQView our Site Rules
View our CalendarView our Active TopicsGo to our Main Page

UGN Security Store
 

Network Sites UGN Security, The GoNix Initiative, Elite Web Gamers, Back of the Web, EveryDay Helper, VNC Web Design & Development
August
Su M Tu W Th F Sa
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
Sponsored Links
Latest Postings
Amazon Gift Card Generator/KeyGen?te
by Gecko666
08/22/14 09:21 AM
Latest Reviews
Topic Options
Rate This Topic
#18774 - 08/13/05 08:21 AM Getting down and dirty with PHP/MySQL
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
Okay this is a easy MySQL lesson.


=================================================
Reference for this tutorial.
http://us2.php.net/manual/en/ref.mysql.php
http://us2.php.net/manual/en/function.mysql-query.php
http://us2.php.net/manual/en/function.mysql-fetch-array.php
http://us2.php.net/manual/en/function.mysql-close.php
http://us2.php.net/manual/en/function.mysql-connect.php
-----------------------------------------------


This is a very basic MySQL command in PHP

Code:
$dg = mysql_query("SELECT * FROM table1");
lets break that apart shall we?
----------------------------------------------
You will see first "$dg" This is a variable obviously. But why do we need it? Well you could very easily do

Code:
mysql_query("SELECT * FROM table1");
But "$dg" will tell us if out query worked. $dg is a boolean variable. That is it is either true or false. It is set once the query is run. You could, and I do the following.

Code:
$dg = mysql_query("SELECT * FROM table1");
if($dg){
// execute code
}else{
echo "echo out why it failed ".mysql_error();
exit();
}
The above code will run the script just fine unless the query fails. Think of it like this...
Code:
if($dg){
}
This is just like saying...

Code:
if($dg == "true"){
do this code
}else{
do this
}
This is why we have $dg in the code. Lets look again at what we have now.
Code:
$dg = mysql_query("SELECT * FROM table1");
The next thing you see is "= mysql_query();"

This tells PHP we are about to do a mysql_query. It is simply a function in PHP. just like date(U); or php_info(); the "(" and ")" encase the actual MySQL command to execute in MySQL. If you were dialed into the server all you would need to do is what is inside the "(" and ")" So if you follow me so far...

Code:
$dg = mysql_query("");
the above is all PHP. It all the code you see in the code block above is simply to let PHP know it is doing a MySQL query. Now lets look at the MySQL part of the code.

Code:
SELECT * FROM table1
What you see above, basically in human terms says this:

Select everything that is in table one. Think of the * as a wildcard. Same as in MS DOS. You could very easily do

Code:
SELECT colum1 FROM table1
or

Code:
SELECT colum1, colum2, colum3 FROM table1
which if you only need a few columns then you should only select those as it will be faster to do so, plus use less server resources than selecting everything.

So lets look at it all again.

Code:
$dg = mysql_query("SELECT * FROM table1");   //select everything from table1

if($dg){    // if the query worked do everything up to the else section

// execute code

}else{   //if the query failed, tell me why.

echo "echo out why it failed ".mysql_error();    //mysql_error(); is a PHP function that will tell you what the exact error is in your query.

exit();  //stop executing code here, just stop every fucking thing right now!!!!

}
This is lesson one is MySQL, I will post more in the next 24 hrs It is 04:29 here and I am tired. I will probably edit this as I forgot to explain and show how to login to a database.

Oh I forgot. The $dg with the mysql_error(); is most handy in debugging and testing your code. Other things you should know.

Lets say you want to "delete" a row in your database.

You start with something like this

Code:
$dg = mysql_query("");
hey that looks kind of like...
Lets say you want to "update" a bit of data. You would start like this

Code:
$dg = mysql_query("");
Hmmm I see a pattern here...

If you want to "insert" data you would begin with this code

Code:
$dg = mysql_query("");
Did you notice they all start the same? That is because these things are all queries. All you really have to learn once you get the PHP syntax down is the MySQL code. And it is easy.

Delete http://dev.mysql.com/doc/mysql/en/delete.html
Update http://dev.mysql.com/doc/mysql/en/update.html
Insert http://dev.mysql.com/doc/mysql/en/insert.html
Select http://dev.mysql.com/doc/mysql/en/select.html

With the 4 commands above you can do al hell of a lot. I mean a shit load of dynamic content is only limited by your imagination. Next logging in with php. Login out with PHP, how the syntax in the links I just listed for "delete, update, insert, select" work in PHP. Keep reading and do not post comments till I am done the tutorial. I will just delete them when I come back.
_________________________
My New site OpenEyes

Top
Sponsored Links
      
#18775 - 08/15/05 08:14 PM Re: Getting down and dirty with PHP/MySQL
§intå× Offline


*****

Registered: 12/03/02
Posts: 3255
Loc: Maryland
Okay the above explains some basic syntax of coding PHP/MySQL pages. Now lets get started. we are going to make a search engine.

first will will need to log in to our database and create the tables we need. Assuming you have an SSH or telnet client(if not try putty ) open it up. Now set it up to login to your server. I will only say this, if you have SSH as an option to connect you should use it instead of telnet. That is another lesson. Any knowlegeable person feeling like doing this lesson I will put a link here if your do it.

So you are now connected to your server via telnet or SSH. From this point on the command prompt will be "$".


You should now be looking at something like what I have below.
Code:
SunOS 5.7

Last login: Tue Jun 28 14:47:43 from 199.*.*.*
Sun Microsystems Inc.   SunOS 5.7       Generic October 1998
You have mail.
$
Notice this is from a Sun Microsystems OS, you may have windown, Linux, BSD, who knows. I am using Sun Solaris. I said something like this not exactly what I have. It is the command prompt we are worried about.

Code:
$ mysql -uUSER_NAME -p
Above you see my login. Lets take a look. The command is "mysql". Then you use the attribute "-u". Lets look at all atributes shall we.

Code:
mysql  Ver 11.18 Distrib 3.23.52, for sun-solaris2.7 (sparc)
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Usage: mysql [OPTIONS] [database]

  -?, --help            Display this help and exit.
  -A, --no-auto-rehash  No automatic rehashing. One has to use 'rehash' to
                        get table and field completion. This gives a quicker
                        start of mysql and disables rehashing on reconnect.
  -B, --batch           Print results with a tab as separator, each row on
                        a new line. Doesn't use history file.
  --character-sets-dir=...
                        Directory where character sets are located.
  -C, --compress        Use compression in server/client protocol.
  -D, --database=..     Database to use.
  --default-character-set=...
                        Set the default character set.
  -e, --execute=...     Execute command and quit. (Output like with --batch)
  -E, --vertical        Print the output of a query (rows) vertically.
  -f, --force           Continue even if we get an sql error.
  -g, --no-named-commands
                        Named commands are disabled. Use \* form only, or
                        use named commands only in the beginning of a line
                        ending with a semicolon (;) Since version 10.9 the
                        client now starts with this option ENABLED by
                        default! Disable with '-G'. Long format commands
                        still work from the first line.
  -G, --enable-named-commands
                        Named commands are enabled. Opposite to -g.
  -i, --ignore-spaces   Ignore spaces after function names.
  -h, --host=...        Connect to host.
  -H, --html            Produce HTML output.
  --local-infile=[1|0]  Enable/disable LOAD DATA LOCAL INFILE
  -L, --skip-line-numbers
                        Don't write line number for errors.
  --no-pager            Disable pager and print to stdout. See interactive
                        help (\h) also.
  --no-tee              Disable outfile. See interactive help (\h) also.
  -n, --unbuffered      Flush buffer after each query.
  -N, --skip-column-names
                        Don't write column names in results.
  -O, --set-variable var=option
                        Give a variable an value. --help lists variables.
  -o, --one-database    Only update the default database. This is useful
                        for skipping updates to other database in the update
                        log.
  --pager[=...]         Pager to use to display results. If you don't supply
                        an option the default pager is taken from your ENV
                        variable PAGER ().
                        Valid pagers are less, more, cat [> filename], etc.
                        See interactive help (\h) also. This option does
                        not work in batch mode.
  -p[password], --password[=...]
                        Password to use when connecting to server
                        If password is not given it's asked from the tty.

  -P, --port=...        Port number to use for connection.
  -q, --quick           Don't cache result, print it row by row. This may
                        slow down the server if the output is suspended.
                        Doesn't use history file.
  -r, --raw             Write fields without conversion. Used with --batch
  -s, --silent          Be more silent.
  -S  --socket=...      Socket file to use for connection.
  -t, --table           Output in table format.
  -T, --debug-info      Print some debug info at exit.
  --tee=...             Append everything into outfile. See interactive help
                        (\h) also. Does not work in batch mode.
  -u, --user=#          User for login if not current user.
  -U, --safe-updates[=#], --i-am-a-dummy[=#]
As you can see we have a few options here. We are mainly intrested in -u and -p. "-u" to type in your user name. -p is for your password. See example below.

Code:
Last login: Mon Aug 15 14:58:05 from 199.*.*.*
Sun Microsystems Inc.   SunOS 5.7       Generic October 1998
$ mysql -uUSER_NAME -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 230981 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
I have never had luck typing my password after "-p". I hit enter and it will prompt me for the password. Do this step and you should see the MySQL prompt. You should also see the clouds part and hear angles harps. Welcome to heaven. Or as I call it MySQL. Do you feel the power you now have? If not you will. You will...

Once you see the "mysql>" prompt you can start to Create databases, Create tables, edit tables, databases, blah blah blah.... First command you will need is kind of tough. Assuming you are starting from scratch(instalation is also another tutorial). What we want to do is Create a Database.

Code:
mysql> CREATE DATABASE New_database
    -> ;
Query OK, 1 row affected (0.09 sec)

mysql>
Above we see the command. The command is "CREATE DATABASE" see http://dev.mysql.com/doc/mysql/en/create-database.html for more info on this function. You use it like so(well if you have rights to use it).

Code:
mysql> CREATE DATABASE db_name_here
  ^           ^              ^
prompt     command        the name you assign to your new database 
Pretty tough right? Yea sure. Now you can do this in PHP, but that would be just stupid so I am not writing about it. That is a security risk you do not need to take.

Next up we need to create a table. Think of a table like a spead sheet or like excel. You have colums and rows. So we are building a search engine, lets think how we want to lay out our table. Well I will think you will suck up my thinking.

We will need a colum for each of the following.

1.) Page URL
2.) Page description
3.) Page title
4.) keyword 1
5.) keyword 2
6.) keyword 3
7.) keyword 4
8.) keyword 5
9.) id

Now this is a very simple table. The first thing we have to do is figure out our data types http://dev.mysql.com/doc/mysql/en/column-type-overview.html

For now we will focus on a few data types

CHAR,
VARCHAR,
TEXT,
INT,
BLOB

The "char" and "varchar" data types:

Please see the MySQL page on them. http://dev.mysql.com/doc/mysql/en/char.html

It explains what the main differences are. for us either will do.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. (Before MySQL 3.23, the length of CHAR may be from 1 to 255.) When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. The length can be specified as 1 to 255 before MySQL 4.0.2, 0 to 255 as of MySQL 4.0.2, and 0 to 65,535 as of MySQL 5.0.3. (The maximum actual length of a VARCHAR in MySQL 5.0 is determined by the maximum row size and the character set you use. The maxim

The "blob" and "text" data types:

Please see the MySQL page on them
http://dev.mysql.com/doc/mysql/en/blob.html

BLOB columns are treated as binary strings (byte strings). TEXT columns are treated as non-binary strings (character strings). BLOB columns have no character set, and sorting and comparison is based on the numeric values of the bytes in column values. TEXT columns have a character set, and values are sorted and compared based on the collation of the character set assigned to the column as of MySQL 4.1. Before 4.1, TEXT sorting and comparison are based on the collation of the server character set.

That leave "int", if you do not know this one go back to learning PHP basics as you are not ready for MySQL integration yet. "int" is numeric. That is to say, it is for numbers. There are other datatypes for numbers but I am lazy and you are getting a free ride. But to read up more on numeric values check out.

http://dev.mysql.com/doc/mysql/en/numeric-types.html

Now that we have that done we will lay out table


1.) URL VARCHAR(255)
2.) description TEXT
3.) Page title VARCHAR(100)
4.) keyword 1 VARCHAR(25)
5.) keyword 2 VARCHAR(25)
6.) keyword 3 VARCHAR(25)
7.) keyword 4 VARCHAR(25)
8.) keyword 5 VARCHAR(25)
9.) id int(11) AUTO_INCREMENT PRIMARY KEY

What are those numbers? varchar(100) means this colum will haveup to 100 charaters or places for charaters. I will have to finish this later. Time to go home.
_________________________
My New site OpenEyes

Top

Moderator:  §intå×, Gremelin 
Featured Member
Registered: 08/20/13
Posts: 1
Forum Stats
2148 Members
46 Forums
34017 Topics
69184 Posts

Max Online: 1567 @ 04/25/10 02:20 AM
Top Posters
UGN Security 27179
Gremelin 7192
§intå× 3255
SilentRage 1273
Ice 1146
pergesu 1136
Infinite 1041
jonconley 955
Girlie 908
unreal 860
Newest Members
Gecko666, defghi795767, Devo60, ali, lavos
2147 Registered Users
Who's Online
1 registered (Tim050), 278 Guests and 290 Spiders online.
Key: Admin, Global Mod, Mod
Latest News


Donate
  Get Firefox!
Get FireFox!