Php: Sql And Php

[SpamBurger]

Php: Main - All Your PHP Needs!

In this tutorial, you will be learning how to interact with mysql databases in php, and some basic sql commands. If you're thinking it will be hard, it won't be. Trust me.

First, you should know what SQL stands for. SQL stands for Structured Query Language. It is a query language used to interact with any type of database system, not just MySQL. You should also know the difference between SQL and MySQL. SQL is a language, and MySQL is a database management system. Now that you know what sql is, you can finally start learning about it!
--------------
Php Mysql Functions

This section of the tutorial will teach you how to interact with a mysql database in php. The next section will teach you sql, but we are not there yet. Please note that all these functions can have an optional second parameter where you indetify the connection resource. You can do this by putting the mysql_connect function within a variable, and then adding rhe variable as a parameter.

Before you can even begin using sql, you must first connect to mysql. It only requires one function, so here it is:

mysql_connect(host,username,password);

Simple, isnt it? Now you are probably wondering what host, username, and password are. Host is the host you wish to connect to. Usually, you can just enter 'localhost' for your host, if you are connecting to mysql locally. Second, username is the username of the database you will be soon connecting to. Third, password is the password of the database you will soon be connecting to. Also, you can use the mysql_pconnect() function which is exactly like the mysql_connect function, except it opens up a persistent connection virtually never having too many connections, and must be closed at the end of a document via mysql_close().

Now, you need to select a mysql database to work with. This also requires just one function, so I will give it to you:

mysql_select_db(database);

Again, simple. You simply enter the database you wish to work with. Remember though, that the username and password you entered in the connect function is the username and password of this database, so you can just enter any old database.

Now, you will learn how to query a database. You can query a database with any sql commands you want. To query a database, use this function:

mysql_query(query);

Very, very simple (I told you :p). You are probably wondering what a query is. It is just a set of sql commands sent to the database. You will be learning about these sql commands in the next section.

Two functions I am about to show you are pretty useful in seeing how many rows were affected by a query. The first one is below:

$result=mysql_query(query);
$rows=mysql_num_rows($result);

This function will show you how many rows were returned by a SELECT query. To see how many rows were affected by an INSERT, UPDATE, or DELETE, use this function:

$result=mysql_query(query);
$affrows=mysql_affectced_rows($result);

That function will return an integer telling you how many rows were affected by a query.

Now, one of the most important functions is the one that will actually retrieve data from a database. This function is:

$result=mysql_query(query);
$array=mysql_fetch_array($result);

This will select data from each individual row, and store it in an array. To actually output the data on the page, you could do something like this:

$result=mysql_query(query);
while($array=mysql_fetch_array($result)){
echo $array['field_name'];
}

Please note that there is also a second optional parameter to this function that allows you to specify how you can access data from the array. The second parameter can be MYSQL_NUM, MYSQL_ASSOC, or MYSQL_BOTH. The default is MYSQL_BOTH. MYSQL_ASSOC allows you to retrieve data from the array by specifying the field name, MYSQL_NUM allows you to retrieve data from an array by specifying the spot in the array, and MYSQL_BOTH allows both. If you wish to have MYSQL_ASSOC as the default, simply use the mysql_fetch_assoc() function instead.

Now I will teach you a useful way to prevent attacks such as SQL Injections. The functions is:

mysql_real_escape_string(string);

What this function does is it will escape any special character with a backslash, thus virtually eliminating any SQL Injection.

Since this section of the tutorial is almost over, you will now learn about 3 more functions. The first two are functions used to help you catch errors with sql commands, and the last one is just something very simple.

mysql_error();

This function is pretty straight forward. If there is an error, mysql_error will return the error from the last query executed. Simple.

mysql_errno();

This function is like mysql_error, except that instead of returning the error message, it will return the error number from the last query. If no error is occured, it returns 0.

mysql_close();

A simple function that just simply closes the connection to mysql. Pretty self explanatory.

That is the end of this section of the tutorial! The next section will actually teach you the basics of SQL (On Next Post).

[SpamBurger]

Basic Sql Commands

In this section of the tutorial, you will be learning about basic sql commands. You wont be learning every single one, because there are a lot, but just the basic ones such as:

SELECT
UPDATE
DELETE FROM
INSERT INTO
DROP TABLE
TRUNCATE TABLE

and some clauses such as:

WHERE
ORDER BY
LIKE
LIMIT

These are all you need pretty much to create simple scripts. I won't be explaining how to create tables and all that jazz, but if you want something to help you with that, I strongly suggest downloading phpmyadmin. And btw, I will be using a fake table to help show examples. The table is (with a name of userinfo):

First_Name - Last_Name - Age
Kurtis - Dinelle - 13
Bob - Doyle - 30
Jimmy - Wells - 17
Smarter - Child - 6

SELECT

The first command you will be learning is SELECT. But just know that you can't select data from a table unless there is data in it :p. Anyways, the syntax for this command looks like:

SELECT column_name FROM table_name

What this does is selects all rows from the column specified from the table specified. If you wish to select all columns use * instead of column_name. Example below:

SELECT * FROM userinfo

This selects all columns from table userinfo and returns:

Kurtis Dinelle 13
Bob Doyle 30
Jimmy Wells 17
Smarter Child 6

Simple, eh?

UPDATE

The UPDATE command is used tom update existing rows in a table. It's syntax is:

UPDATE table_name SET column1=??, column2=??

This will set all rows in column1 and column2 to some value. You can update more columns simply by separating them by commas. You may be wondering how to update certain rows in columns, and we will get to that when I teach clauses. An example of update is below:

UPDATE userinfo SET First_Name='Stupid'

This updates all rows in the First_Name column in the userinfo table to Stupid, and returns:

Stupid Dinelle 13
Stupid Doyle 30
Stupid Wells 17
Stupid Child 6

DELETE FROM

The DELETE FROM command is used to delete rows from a table. It's syntax is:

DELETE FROM table_name

This will delete all rows from table_name. Again, if you want to only delete some rows, it will be mentioned in the clause section. An example of DELETE FROM is below:

DELETE FROM userinfo

This will delete all rows from userinfo and return nothing because there are no rows :p

INSERT INTO

The INSERT INTO command is used to insert new rows into a table. It can be written two ways:

INSERT INTO table_name VALUES(column1 value,column2 value,column3 value)

or:

INSERT INTO table_name (column1,column3) VALUES(column1 value,column3 value)

The first way just simply inserts data for the columns in asscending order. The second way you can choose which columns to insert into. An example of INSERT INTO is below:

INSERT INTO userinfo VALUES('McConnell','Wade',13)

This inserts McConnell into the First_Name column, Wade into the Last_Name column, and 13 into the Age column all within in the userinfo table, and returns:

Kurtis Dinelle 13
Bob Doyle 30
Jimmy Wells 17
Smarter Child 6
McConnell Wade 13

DROP TABLE

The DROP TABLE command is used to totally get rid of a table. Its syntax is:

DROP TABLE table_name

This would totally delete the table table_name from a database. Example below:

DROP TABLE userinfo

This deletes the table userinfo, and returns nothing because the table userinfo doesn't exist.

TRUNCATE TABLE

The TRUNCATE TABLE command is used to delete all rows from a table. Its syntax is:

TRUNCATE TABLE table_name

This deletes all rows from the table table_name. Example below:

TRUNCATE TABLE userinfo

This deletes all rows from the userinfo table and returns nothing because there aren't any rows.

[SpamBurger]

<<Continued>>

Basic SQL Clauses

This is a subsection to the SQL section of this tutorial. In this section, you will learn basic clauses and operators. Clauses are used to further effect your queries. With them, you can return specific rows, etc. You will recieve an example of each clause within each command I taught you except for the INSERT INTO, DROP TABLE, and TRUNCATE TABLE commands.

WHERE

The WHERE clause can be used to select specific rows from a table where a row within a column is equal to something. Examples:

SELECT * FROM userinfo WHERE Age=13

This would return:

Kurtis Dinelle 13

because we selected all the rows from the userinfo table where each row in the Age column is equal to 13.

UPDATE userinfo SET First_Name='Retarded' WHERE Last_Name='Child'

This would return:

Kurtis Dinelle 13
Bob Doyle 30
Jimmy Wells 17
Retarded Child 6

because we selected the rows where the row in the Last_Name column is equal to Child.

DELETE FROM userinfo WHERE First_Name='Bob'

This would return:

Kurtis Dinelle 13
Jimmy Wells 17
Smarter Child 6

because we delete the row in the userinfo table where every row in the First_Name column is equal to Bob.

ORDER BY

The ORDER BY clause is used to order data selected from a table in a certain way. If you order by a column containing a string, it will be ordered alphabetically. If you order by a column containing an integer, it will be ordered by value. You can even go as far as ordering data in ascending order or descending order by adding either ASC or DESC to the end of the ORDER BY clause (ASC is used by deafault). If you order a string column in ascending, it will be ordered in alphabetic order. If you order a string column is descending, it will be ordered in reverse alphabetic order. Same for integer columns. If you order one by ascending, it will be ordered from least to greatest. If you order it in descending order, it will be ordered from greatest to least. Since ORDER BY is only used with SELECT, you will only recieve examples in SELECT, but you will have 4 examples showing the 4 different ways of ordering.

SELECT * FROM userinfo ORDER BY Age

This would return:

Smarter Child 6
Kurtis Dinelle 13
Jimmy Wells 17
Bob Doyle 30

because we selected all the rows and ordered them by the Age column in ascending order.

SELECT * FROM userinfo ORDER BY Age DESC

This would return:

Bob Doyle 30
Jimmy Wells 17
Kurtis Dinelle 13
Smarter Child 6

because we selected all the rows and ordered them by the Age column in descending order.

SELECT * FROM userinfo ORDER BY First_Name

This would return:

Bob Doyle 30
Jimmy Wells 17
Kurtis Dinelle 13
Smarter Child 6

because we selected all the rows and ordered them by the First_Name column in ascending order.

SELECT * FROM userinfo ORDER BY First_Name DESC

This would return:

Smarter Child 6
Kurtis Dinelle 13
Jimmy Wells 17
Bob Doyle 30

because we selected all the rows and ordered them by the First_Name column in descending order.

LIKE

The LIKE clause is used with the WHERE clause. You can use % signs as wildcards in these "patterns".

SELECT * FROM userinfo WHERE Last_Name LIKE '%ell%'

This would return:

Kurtis Dinelle 13

because we selected the rows where each row in the Last_Name column begins with anything, has ell in it, and ends with anything.

UPDATE userinfo SET First_Name='Dan' WHERE First_Name LIKE 'Jimmy'

This would return:

Kurtis Dinelle 13
Bob Doyle 30
Dan Wells 17
Smarter Child 6

because we updated the rows where the rows in the First_Name column is equal to Jimmy.

DELETE FROM userinfo WHERE Age LIKE '1%'

This would return:

Bob Doyle 30
Smarter Child 6

because we delete all the rows where the rows in the Age column begin with a 1, but can end in anything.

LIMIT

The LIMIT clause is used to return a certain amount of rows from a query. It can be used in two ways, but Im only going to show the first, basic way because I have been writing this tutorial for ages :s

SELECT * FROM userinfo LIMIT 1

This would return:

Kurtis Dinelle 13

because we selected rows from the userinfo table, but restricted it to only select 1 row.

UPDATE userinfo SET First_Name='Dan' LIMIT 2

This would return:

Dan Dinelle 13
Dan Doyle 30
Jimmy Wells 17
Retarded Child 6

because we updated only the first 2 rows.

DELETE FROM userinfo LIMIT 3

This would return:

Smarter Child 6

because we delete the first 3 rows.

Phew! That part was done. Note that you can use any of these clauses together.
--------------
Basic SQL Operators

Welcome to the last learning section of this tutorial. In this section, you will be learning about basic sql operators you can use with the WHERE clause, and two "logical" operators (if you can call them that, I don't know) AND and OR. I won't be giving many examples, just what each of these few operators do.

= - Checks if two values are equal.
!= - Checks is two values are not equal.
> - Checks if value 1 is greater than value 2.
< - Checks if value 1 is less than value 2.
AND - Checks if all of multiple expressions are true.
OR - Checks if one of multiple expressions is true.

Sorry if I missed some ither basic ones, like I said before, Ive been writing this for ages, and can't be arsed to think and/or research.
--------------
Mysql References and Helpful Software

Documentation of mysql functions
w3schools SQL tutorial
phpmyadmin
--------------
Conclusion

I hope you learned something from this massive tutorial that took me ages to write >:(
Since this tutorial is over 2,450 words long, and 14,700 characters long, I don't feel like proof reading it. So please post here any errors or mistakes and enjoy!

[WoogieNoogie]

Good tutorial.

I just want to mention one thing that you didn't...

A great time to use mysql_error(), is when submitting a query. A simple "or" will give you a descriptive error of what happened if the query didn't work. Such as...

$query = mysql_query(SELECT * FROM table WHERE id = '$id') or die(mysql_error());

[DFox]

Excellent, excellent, excellent tutorial.

This is just what we needed!

Fantastic job.

[SpamBurger]

At 7/17/06 08:21 PM, GamesCool wrote: Excellent, excellent, excellent tutorial.

This is just what we needed!

Fantastic job.

Thanks a bunch :)
It couldn't be done without authorblues' kindness to proofread and give me suggestions though :D

[WoogieNoogie]

At 7/17/06 08:21 PM, GamesCool wrote: Excellent, excellent, excellent tutorial.
This is just what we needed!
Fantastic job.

Indeed, my reply made it sound like it was an "okay" tutorial. I really didn't mean that, because this is an excellent tutorial!

[SpamBurger]

Ha ha, it's all good WoogieNoogie :)

[authorblues]

At 7/17/06 08:26 PM, SpamBurger wrote: It couldn't be done without authorblues' kindness to proofread and give me suggestions though :D

<333 luff

i didnt get a chance to proofread your second half. you might want to note the difference between DELETE FROM table_name without any clauses and a TRUNCATE TABLE table_name.

as far as i know, the only real difference is that DELETE FROM doesnt reset the autoindex, which is a nice trick, if you need something like that...

great work :D

[Pilot-Doofy]

At 7/17/06 08:15 PM, SpamBurger wrote: $affrows=mysql_affectced_rows($result);

If you're going to write tutorials and especially write things in bold, you should at least make sure you spell things correctly. I also noticed that you aren't listing the optional arguments for the mysql_* functions. I really think you should, because in some cases you actually need them.

I didn't read the entire thing, but from the impressions I got, it could be better.

[authorblues]

At 7/18/06 12:51 AM, Pilot-Doofy wrote: I didn't read the entire thing, but from the impressions I got, it could be better.

i suggested that he mention the optional resource link parameter with the mysql_* functions, and he did offhandedly, but i think it deserves a better explanation. but give credit where its due. explain what you want clarification on, and we will all try to add it here.

saying "it could be better" is not constructive, doofy.

[Pilot-Doofy]

At 7/18/06 12:55 AM, authorblues wrote: saying "it could be better" is not constructive, doofy.

Okay, double check spelling and grammar first off. Secondly, list the optional parameters and explain them in depth. I ask that the optional parameters be explained because in the case of mysql_close(), because if you call it with no parameters it closes all the mysql connections that are active.

Thirdly, tell how to go about using the functions in real world situations. In most cases, if you're using mysql_num_rows(), you're checking to see if something exists in the database, in other words, if rows exist matching the specified criteria.

If they do not exist, it will raise an error. Not a fatal error, but something ugly nonetheless unless you've already explained the entire concept of error handling in either the .ini settings or with the error_reporting() function, which I don't think has occurred yet.

Say we have this example:

$doesUserExist = mysql_query("SELECT name FROM users WHERE username = 'Pilot-Doofy'");

if ( mysql_num_rows($doesUserExist) < 1 ) {
die('User Pilot-Doofy does not exist!');
}

echo ('User Pilot-Doofy exist.');

Okay, that example works great, unless you have errors on (which most servers do). You should explain that in most situations you are going to want to suppess the errors on mysql_num_rows with the preceeding @ denotion.

Now, I'm not saying mysql_num_rows error handling is all that there is left to explain, it's just an example. Hopefully that will be enough for authorblues to consider it constructive criticism instead of simply taking up space lol.

Once again, not knocking you because you tried, but it seems like it could have had more explanations, examples, and real world situations in it.

[DFox]

At 7/18/06 01:06 AM, Pilot-Doofy wrote: Once again, not knocking you because you tried, but it seems like it could have had more explanations, examples, and real world situations in it.

But you can't include everything. That's what's good about PHP: Main. You have different tutorial authors to contribute to the same subjects.

For instance, you showed the user exists example. That can be a completely separate tutorial in specific projects. PHP: Checking if a user exists with PHP and MySQL.

It's already very long, and if it's any longer it could turn beginners away.

That's just my opinion.

[Zendra]

Very impressive to see this coming from you. You're going the right way. :)
Most area's of MySQL has been covered, which is good. Although I'd loved to see something about joining and such. But it's already pretty much complete.

Great job.

[NinoGrounds]

It is good!

Can you make the second part (stand alone version) and make only advanced SQL syntax?

Like Join, Union etc?

[henke37]

Now to add joins, agregation functions, views, storaged procedures and how to create tables.
Please make a part 2.

[SpamBurger]

Thanks for the comments guys, and thanks Pilot-Doofy for the criticism :)
I probably will make an advanced SQL tutorial, and I could maybe go more in depth and give better explanations of some of the mysql functions like Doofy said. And sorry for that stupid spelling mistake :p. I just couldn't be arsed to re-read the whole thing >.<

[SpamBurger]

At 7/18/06 03:05 AM, Zendra wrote: Very impressive to see this coming from you.

Should I take that as an insult or a compliment? Because it was easy to write, just time consuming :p
You're going the right way. :)
Thanks :)

Most area's of MySQL has been covered, which is good. Although I'd loved to see something about joining and such. But it's already pretty much complete.

Yep, I will be writing a second tutorial if no one wants to do it before me, but not anytime soon because I am working on a PM system tutorial :)

Great job.

Thanks

[Pilot-Doofy]

At 7/18/06 10:43 AM, SpamBurger wrote: I just couldn't be arsed to re-read the whole thing >.<

I don't really care about spelling and grammar at all to be honest. I just found it absurd because it was the only appearance (to my knowledge) of that function name. I don't recheck my spellings and grammar most of the time because as you said, it isn't worth it. But I make sure that code-sensitive spellings are spelled correctly.

Don't get me wrong, it was a great tutorial, and as Games said, perhaps I was expecting too much in a single tutorial, but I just felt I could post my input on the matter.

[SpamBurger]

Yea, Im sorry I didn't re-read the code I gave. But any person with an IQ above 70 will know how to change affectced to affected :)

[Pilot-Doofy]

At 7/18/06 12:57 PM, SpamBurger wrote: But any person with an IQ above 70

So basically, anyone except that Squall guy and omega3D? ROFL

[DFox]

At 7/18/06 01:00 PM, Pilot-Doofy wrote: So basically, anyone except that Squall guy and omega3D? ROFL

LOL. You forgot blackvector. But he's gone so I guess he doesn't count.

[TSCom]

When you connect...

ie

$connect = mysql_connect('host','username','passwordR
EVEALED!!');

you give away your PASSWORD to anyone who opens the php file. surely this is a huge security risk!

[DFox]

At 9/2/06 04:18 PM, TSCom wrote: you give away your PASSWORD to anyone who opens the php file. surely this is a huge security risk!

Nope :)

All PHP is executed on the server. PHP never reaches the client, so it can't be seen.

[Rellizate]

All I can say is <3

[Andy-Smithy]

Hey,

Sorry to bump the topic, but I am new with Mysql. Great guide, although I don't know how to set the username and password for my database =/

Can anyone help me?

Regards

Andy