Data Management with php
By Red Squirrel
Mysql is a type of database like many others, such as Oracle and MsSQL. Most databases use the same commands such as INSERT, UPDATE, SELECT, DROP etc… these are the key commands to manipulating the database. These commands also have various parameters. Commands, or queries, which is the right name in terms of databases, are the key to maintaining a database. A forum script is full of various queries to read/write to the database.
Using php, this would be a generic program that would do a query on a database:
On line 2, we simply open a connection to the database. This is sort of the equivalent of fopen() for the file database. After that, we select the database, and use the handle ($sqlinfo in this case) as the second parameter. On line 4 we do the query. At this point, you can do as many queries as you wish. If there's a problem with the query, you will get an error given by mysql itself. On line 5 we close the database.
This is perfect for queries that require writing, but if you need to read, there's a second step needed in order to retrieve the data. After the query, you would need to determine how many rows will be returned, and get the data. This is usually done in a while loop so you can display the data as you are reading each row, just like we did with the file database.
In this case, the query on line 4 would be SELECT, which will be covered after. The function on line 5 returns the number of corresponding rows into $count.
Line 6 is optional, it checks to see if there's at least one row that corresponds to the query, and if there's none, it displays an error.
Line 7 retrieves all the data from the row. So if you want to get the user ID, you would use $info[id] and so on, where ID is the name of a column in the database table.
This is where adding a new field would be made easy, because you simply need to add it using your mysql manager (usually phpmyadmin) and then voila, no need to modify the rest of the database to fit, as it will fit already, but old rows will simply have null as the value to the newly added column, unlike a file database, where you would need to add a return or N/A to each entry to avoid a mess up, and you would need to rewrite the reading handling.
To read multiple rows, the easiest way to do it is to have a while loop that checks if $count is bigger then 0, while decreasing it each time it executes. In the loop you would then do what you have to do with the row data, such as displaying it.
Managing your databases
Using your mysql manager that came with your web host/mysql installation, you can create and delete databases, and also add tables. A database is basically that simple: various tables. Each table has various columns that each one has a name, and then the rows, which are the entries.
If you want to practice the following samples, you can use phpmyadmin as it has a query box, which is made specifically for modifying a database manually. To make the examples easier to follow, create a database called test and a table called zerowing that has these current columns and rows in it. The word column can be set as TEXT and number can be set as BIGINT, but this part does not matter as much, but it does, it depends on what you plan to store in those columns. (large numbers, smaller numbers, text etc) Also remember that when making a php script, these queries would all go in the mysql_query(); function.
Putting data in the database
The INSERT query is used when putting data in a database. So if we want to add another entry, we need to give it a name. So if we want word to be equal to "bases" and we want number to be 254, we simply do this:
INSERT INTO zerowing (word,number) VALUES ( 'bases', '1254' );
That would do it!
Now we realized that we made a mistake, no need to worry, you can easily change it. You can modify rows using UPDATE. To change bases to base and 1254 to 254, you would go like this:
UPDATE zerowing set word='base', number='254' where word='bases' and number='1254' LIMIT 1;
First, we set the columns to what we want, then we have a clause so we only set desired tables, and then we can limit the query to one row. If there would happen to be more then one row with the clause matched, this would limit it to affecting only 1 row. However, in applications such as forums, it's good to have unique Ids for everything, such as members, forum topics etc. LIMIT can also be used when displaying rows, it will limit mysql to returning only a certain number of rows.
Now we want to delete values. We want to delete everything in which number is lower then 200. So we go like this:
DELETE from zerowing where number<200;
In any query, you can use < > != and = clauses, DELETE is one of them. If we only wanted to delete one entry, we would specify more details, like this: Something interesting to note is that you only need to use = and not == for "equal to". This is usually not the case when programming.
DELETE from zerowing where number='2102' and word='AD' LIMIT 1;
It is good to use LIMIT when you only want one row to get deleted, just in case there happens to be more then one with the same values.
As you can see mysql is pretty simple but yet powerful in php. It's almost as if you are talking to the database and telling it exactly what you want to do.
Now we want to read our table, and in a specified order! Think it's complicated? Not at all. When reading, there's more to it then just the query, so here's a script that would read this table and display the values.
As you can see, it's the usual stuff like shown a while ago, but now we have a while loop.
In the query, we add order by number, which means it will order it by the column number, it's pretty much self explanatory I hope! Then the while loop simply keeps going until $count is smaller then 0, and we are decreasing it as we go. The echo() inside the loop is simply displaying the word with a space. Notice that we don't even use number, because we don't have to! Number in this case is simply used to keep some order in things. You can also order by word, if you want, which would alphabetically display it! If you wanted to start at Z, you would simply add "desc" after the query, and same if you wanted to start at the biggest number.
So our program input would be all your base are belong to us.
There is much more info I could give you about mysql, but this is a php article, so this is only to show you the basic commands and parameters, which you would use in php. But these can easly be used anywhere else such as in the ssh console, phpmy admin's query box etc. That's what's fun about it, it's pretty much universal. With these concepts, you should be able to easly make a script that can store data and retrive it, such as a guest book or maybe even a mini forum.
I hope this article helped give you a general understanding of data storage using php with file databases and as well as mysql databases. Happy coding!
This site best viewed in a W3C standard browser at 800*600 or higher
Site design by Red Squirrel | Contact
© Copyright 2018 Ryan Auclair/IceTeks, All rights reserved