📄 sqllecture.html
字号:
<BR><d4J1nX> SELECT contact_name FROM people p,contact c WHERE p.contact_id=c.contact_id
AND forename = 'John'
<BR><d4J1nX> Notice the c and p used for an alias and to remove ambiguity
<BR>*** WishGenie has quit IRC (Quit: i gotta reboot)
<BR>*** cupelmen has quit IRC (Quit: time to sleep...)
<BR><d4J1nX> That statement will pull back the contact names for all
the people called John
<BR><d4J1nX> On an off note..
<BR><d4J1nX> Say we had a drop down/combo box on our webpage
<BR><d4J1nX> And we wanted the full details of a particular person
<BR>*** makzee has quit IRC (Ping timeout: 180 seconds)
<BR>*** SpiderMan has joined #bsrf
<BR>*** ChanServ sets mode: +o SpiderMan
<BR><d4J1nX> In the drop box we would have the details "John Woods"
or "Chris Simms"
<BR>*** bluehaze has quit IRC (Ping timeout: 180 seconds)
<BR><d4J1nX> But the actual value would be the corresponding id in the
table
<BR><Mikkkeee> hail spider
<BR><SpiderMan> hey
<BR><AZTEK> Spidey
<BR>*** ken has quit IRC (Quit: )
<BR><AZTEK> all hail SpiderMan
<BR><SpiderMan> hey AZTEK
<BR>*** Capt_InsaneO has joined #bsrf
<BR><d4J1nX> Depending on the langauge being used we loop through each
record brought back from the query and have a formselectOption for each
<BR>*** Mezzano has quit IRC (Read error: 113 (No route to host))
<BR><d4J1nX> SELECT people_id, forename FROM people;
<BR><d4J1nX> The forename would be in the dropdown
<BR>*** torpor has left #bsrf
<BR><d4J1nX> But behind the scenes the actual value for that selection
is the id
<BR>*** Capt_InsaneO has left #bsrf
<BR>*** mezzano has joined #bsrf
<BR>*** acid519 has joined #bsrf
<BR><Devil_Panther> oh... hi spidy
<BR><d4J1nX> This is moving on to front end languages though and since
php seems to be very popular at the moment, Aztek will take it form here
<BR><d4J1nX> Hey Spider :-)
<BR><SpiderMan> hi
<BR><SpiderMan> sorry to disturb
<BR><d4J1nX> np
<BR><AZTEK> ok i guess its php time
<BR><AZTEK> and we are going to php and not perl
<BR><SpiderMan> so soon?
<BR><AZTEK> lets hit it spidy\
<BR><AZTEK> yea
<BR><AZTEK> ok
<BR><SpiderMan> nothing more of SQL?
<BR><SpiderMan> WHERE clause
<BR><Mikkkeee> php now
<BR><SpiderMan> db normilization
<BR><SpiderMan> ok
<BR><Devil_Panther> AZTEK,,, i guess you preffer php on perl... huh?!
<BR><AZTEK> we did the WHERE
<BR><AZTEK> yea
<BR><d4J1nX> Thats already been done Spider
<BR><SpiderMan> ok
<BR><d4J1nX> Plus normalisation - although not alot
<BR><SpiderMan> ok then I guess it is php time :)
<BR><AZTEK> mysql is the prefered datbase engine of choice by us phpers
<BR><SpiderMan> because it's small, fast, and cheap
<BR><AZTEK> what make us choose mysql over any other
<BR><d4J1nX> You want me to go into normalisation more first Aztek?
<BR><AZTEK> exactly
<BR><AZTEK> cant beat free
<BR><SpiderMan> also it's avaliable on many platforms
<BR><Devil_Panther> ok ok...
<BR><Devil_Panther> LECTURE PLS
<BR><AZTEK> yea any platform apache and php run mysql does
<BR><Devil_Panther> lol
<BR>*** BaGeL has joined #bsrf
<BR><AZTEK> ok
<BR><AZTEK> mysql_connect();
<BR><AZTEK> you have to connect to the mysql host
<BR><d4J1nX> Can php use access databases Az?
<BR><AZTEK> usually localhost
<BR><AZTEK> d4J1nX: yes
<BR><AZTEK> actually i am waiting for spidy to give me the syntax since
i don't have my manual open
<BR><SpiderMan> for mysql_connect()?
<BR><SpiderMan> it's localhost, username, password
<BR><AZTEK> yea
<BR><SpiderMan> in strings
<BR><AZTEK> yea but port
<BR><AZTEK> and stuff
<BR><SpiderMan> if no port is given it uses the default
<BR><AZTEK> int mysql_connect ([string hostname [:port] [:/path/to/socket]
[, string username [, string password]]])
<BR><SpiderMan> you don't have to provide the port unless it's different
then the standard
<BR><AZTEK> yea
<BR><AZTEK> i was wanting the full atribs :)
<BR><SpiderMan> ah, I'll open my docs
<BR><AZTEK> ok mysql_connect() returns a database handle
<BR><AZTEK> that we will use later for other things
<BR><SpiderMan> you should always check for a valid handle
<BR><AZTEK> we use mysql_query();
<BR><AZTEK> te send a query which d4J1nX when over the standard querys
earlier
<BR><SpiderMan> $con= mysql_connect("localhost", "jared", "pass"); if(!$con)
{die("can't connect!");}
<BR>*** Shiva_Shanti has joined #bsrf
<BR><AZTEK> mysql_query("SELECT * FROM people");
<BR><SpiderMan> int mysql_query (string query [, int link_identifier])
<BR>*** d4J1nX has quit IRC (Ping timeout: 180 seconds)
<BR><AZTEK> $result = mysql_query("SELECT * FROM people");
<BR><AZTEK> thats will return what mysql said for the query
<BR><SpiderMan> then you can use functions such as mysql_fetch_array()
to get the results
<BR><AZTEK> yea
<BR><AZTEK> umm thats the reson php is popular for database use its
really easy :)
<BR>*** acid519 has quit IRC (Ping timeout: 180 seconds)
<BR><AZTEK> use mysql_drop_db("database name"); to delete a database
<BR><AZTEK> use mysql_create_db("database name"); to create a databse
<BR><AZTEK> anything else spidy
<BR><AZTEK> like i said its easy
<BR><SpiderMan> int mysql_create_db (string database name [, int link_identifier])
<BR><SpiderMan> let's give an example
<BR><AZTEK> ok
<BR><AZTEK> from the start
<BR><SpiderMan> how about something to pull news out of a db
<BR>*** d4J1nX has joined #bsrf
<BR><AZTEK> ok
<BR><SpiderMan> let's say a database is set up with an id field and
a news field
<BR><SpiderMan> to get the news out we could do this
<BR><SpiderMan> if (!mysql_connect("host", "user", "pass")) {die("error!");}
<BR>*** TCL has joined #bsrf
<BR>*** ChanServ sets mode: +v TCL
<BR><SpiderMan> mysql_select_db("db");
<BR><AZTEK> |ID|NEWS
|
<BR><AZTEK> --------------------------
<BR><AZTEK> |1 |.....................|
<BR><AZTEK> |2 |.....................|
<BR><AZTEK> |3 |.....................|
<BR><AZTEK> |4 |.....................|
<BR><AZTEK> |5 |.....................|
<BR><SpiderMan> that's what the db table would look like
<BR><SpiderMan> $result= mysql_query("SELECT * FROM news ORDER BY id
DESC);
<BR><SpiderMan> we use that to select the news and id out
<BR><SpiderMan> then using a while loop fetch all the data
<BR>*** Shiva_Shanti has quit IRC (Quit: eat, bbl)
<BR><SpiderMan> while($row= mysql_fetch_array($result)) {
<BR><SpiderMan> $news= $row["NEWS"];
<BR><SpiderMan> $id= $row["ID"];
<BR>*** divinator has quit IRC (Ping timeout: 180 seconds)
<BR><SpiderMan> echo "id= $id<br>";
<BR>*** N0_Nick has quit IRC (Ping timeout: 180 seconds)
<BR><SpiderMan> echo "news= $news<br>";
<BR><SpiderMan> }
<BR><SpiderMan> and after that we close the connection to the database
<BR><AZTEK> ok i tought spidy was just pulling this out of one of his
scripts
<BR><AZTEK> never mind
<BR><SpiderMan> heh nope
<BR><SpiderMan> just wrote it up right now
<BR><AZTEK> ok
<BR><AZTEK> using my little impromtu database
<BR><SpiderMan> heh yup
<BR><SpiderMan> question time?
<BR><AZTEK> .mode #bsrf -m
<BR>*** AZTEK sets mode: -m
<BR><freakOVER[away]> whew
<BR>*** N0_Nick has joined #bsrf
<BR><UnHaFox> =)
<BR><freakOVER[away]> i felt so
<BR><freakOVER[away]> muzzled
<BR><SpiderMan> heh
<BR>*** acid519 has joined #bsrf
<BR><UnHaFox> ah got it one the log file;)
<BR><AZTEK> damn laptop
<BR><mezzano> we can talk again ;)
<BR><AZTEK> well its not over
<BR><d4J1nX> Explain each line of code Spider
<BR><AZTEK> d4J1nX
<BR><AZTEK> is about to do perl
<BR><UnHaFox> not over?
<BR><N0_Nick> hey i got disconnected
<BR><SpiderMan> ok
<BR><N0_Nick> man
<BR><freakOVER[away]> oo
<BR><SpiderMan> wait I want to give some tips before we go to perl
<BR><freakOVER[away]> perl
<BR>*** AZTEK sets mode: +m
<BR><SpiderMan> ok so first we connect to the database using mysql_connect()
<BR>*** acid519 is now known as _acid519-
<BR>*** pixel_chomp has joined #bsrf
<BR><SpiderMan> we test to make sure we could connect in the if clause
<BR><AZTEK> if (!mysql_connect("host", "user", "pass")) {die("error!");}
<BR><SpiderMan> if we can't connect we exit the script with the error
message
<BR>*** _ciR_ has joined #bsrf
<BR><SpiderMan> in a real script you would give a better error
<BR><AZTEK> yea like
<BR><AZTEK> "You fucked it up"
<BR><AZTEK> lol
<BR><AZTEK> $result= mysql_query("SELECT * FROM news ORDER BY id DESC);
<BR><SpiderMan> in fact if you want to get the error mysql gives you,
you can use the function mysql_error()
<BR><AZTEK> now we query the database asking for all info in the databse
news and we want it ordered by the id feild decending
<BR><AZTEK> you forgot your "
<BR>*** SySt3mShk has left #bsrf
<BR><AZTEK> $result= mysql_query("SELECT * FROM news ORDER BY id DESC");
<BR>*** SySt3mShk has joined #bsrf
<BR><SpiderMan> heh whoops
<BR><AZTEK> <SpiderMan> while($row= mysql_fetch_array($result)) {
<BR><AZTEK> <SpiderMan> $news= $row["NEWS"];
<BR><AZTEK> <SpiderMan> $id= $row["ID"];
<BR><AZTEK> *** divinator (Oper@box.sk) Quit (Ping timeout: 180 seconds)
<BR><AZTEK> <SpiderMan> echo "id= $id<br>";
<BR><AZTEK> *** N0_Nick (hgfhf@202.179.171.BOX-24341) Quit (Ping timeout:
180 seconds)
<BR><AZTEK> <SpiderMan> echo "news= $news<br>";
<BR><AZTEK> <SpiderMan> }
<BR><SpiderMan> that says while there are rows to fetch, get them
<BR><AZTEK> here we are looping
<BR><AZTEK> yea
<BR>-d4J1nX- I have to go here, going out, I will speak soon
<BR>*** UnHaFox has left #bsrf
<BR><AZTEK> $row becomes a hask array with the column names as identifiers
<BR>-d4J1nX- say bye for meeee
<BR><SpiderMan> hash array*
<BR><AZTEK> yea hash
<BR>*** d4J1nX has quit IRC (Quit: )
<BR><AZTEK> and echo is self explainatory
<BR><SpiderMan> yeah
<BR><SpiderMan> just print it out
<BR>*** UnHaFox has joined #bsrf
<BR><AZTEK> thats about it
<BR><SpiderMan> when the script finishes the connection to the db will
close
<BR><SpiderMan> also just a tip
<BR><AZTEK> now
<BR><SpiderMan> if you are using larger scripts
<BR><SpiderMan> use mysql_pconnect()
<BR>*** AZTEK sets mode: -m
<BR><SpiderMan> it will save memory
<BR><AZTEK> for persestant connection
<BR><UnHaFox> ah=)
<BR><AZTEK> so they wont keep connecting with each user
<BR>*** divinator has joined #bsrf
<BR><reptile> ;)
<BR><UnHaFox> ?
<BR><SpiderMan> so questions?
<BR><freakOVER[away]> lol rep
<BR><_ciR_> oh
<BR><_ciR_> lets kill reptile
<BR><_ciR_> lol
<BR><divinator> yo
<BR><AZTEK> now i guess Mikkkeee will cover hacking sql
<BR><BaGeL> sup div
<BR><_ciR_> he didnt show up
<BR><UnHaFox> yes i have one, but this one doent concern this subject,
so can i ask it?
<BR><Mikkkeee> you guys are done?
<BR><AZTEK> yea
<BR><reptile> ;()
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -