Welcome to the Spoken Tutorial on the fourth part of My SQL and php tutorials.
In the last tutorial, I used "mysql_query" function to insert some values into our table.
I made a mistake here by putting the date as current date, which isn't my date of birth.
I was able to update here. I was also able to specify where I wanted to update.
Using a unique ID key, I was able to specify exactly where I wanted to update.
So we've already seen the "update" in mysql code.
This is very useful.
This query and code is the one that is mostly used while working with tables or if you're working with mysql in general.
The next thing I show you is how to get data from your table and how to display it really well.
So I will call this "update data" so that we know what that is.
Here, we will say "extract data".
That's a good word to use.
Now, again we will say "extract" and we will create a variable.
This again is "mysql query" and here is some code.
This is slightly more complicated than using these single line queries.
We use single line queries here, but we could have some code after this in order to display properly.
First I will create another record in the table.
So we don't need this "current date" function anymore.
I need this "write" to be shown. Lets create some new value.
I'll say "Kyle Headen" and I'll set a date of birth here. This one is month. So that is the 7th and lets say here, 24th.
So now we got the date of birth.
Now we've got male and then we've got "Kyle Headen" and we are again inserting this into our database.
Lets refresh.
Here I'll create another new value.
I'll say "Emily Headen" and I'll just leave the date of birth as it is for now.
This will be "Female" because I'll extract these records at one point.
Refresh this again.
So we've created 3 records here.
I'll comment this "write". Backup my database.
I'll click on browse in this specific table and you can see that I've got 3 records.
Each one of these is called a "record of data".
We can see this id has also automatically incremented.
We've got the data that we specified and everything that we need.
Ok, so we are extracting data here and I will uncomment this.
Our mysql query is going to start with "select".
This will be either specific records or we can use asterisk (*) to get all the data we need.
Now I'll use an asterisk (*).
What you could do is type "select firstname".
But usually, when you have table, you will need most of the data and it'll take longer to do.
Depending on the source of the table, this won't take very long.
So you already have a couple of records or fields.
But for now I'll say select asterisk (*), which is a star.
We can say select star and then we say FROM.
Again we say, the specified table which is "people".
Here, we can say WHERE and how can you ummm...... filter for the data you want.
So I can say "SELECT star (*) FROM people WHERE firstname= "Alex'".
This query will return only one value because we can see that if we open up here, there is only one record with "Alex".
We can do this by using another really useful function called "mysql numrows" and what I can do is echo this out.
I'll say "echo mysql_num_rows". This is the reason we have given these variables here to be stored in.
Here we can just type "extract".
Our "extract" variable holds our query and our function here tells us how many rows are there in the query that is given out.
Presuming that we gave firstname as "Alex", it'll show when we refresh.
However you get 1.
Lets change this. Lets put something that's common to two people in this database.
That would be the "gender".
So that'll be "Male" or "Female" . Here we can say "WHERE gender = M" and when we refresh, we get two records.
So we can tell how many records we are getting out.
This is really useful for saying how many people in my database are male, for example.
And we can see how many males or females are registered to our website.
So you can store registered information inside here.
What we can also do is order the records.
So I'll say "ORDER BY id" and we can choose descending that is "DESC" and we can choose ascending, which is "ASC".
But for now I'll take this out because we haven't actually echoed out our data yet.
We haven't displayed out our data to the users that has been selected.
So there is no point in using that at the moment.
Now, here I'll say select star (*) from "people" because I want to select all the data from this table here.
So I can manipulate and show it to the user the way I want.
I'll create something here called "numrows"; "numrows =" that.
I'll use a "while" loop. This will use a specific function which is "mysql_fetch_assoc".
It puts this into an associative array.
If you don't know what an associative array is, check out "Arrays" tutorial.
Coming back, "WHILE the row= mysql_fetch_aasoc" or associative is what I will say and this is inside the "extract" query.
We are selecting "row" as array name and we are selecting this as an array for all the selected data.
I'll stop here. In the next tutorial I'll show you how to echo out this data.
I'll probably explain this a bit more in detail.
This is Juanita Jayakar dubbing for the Spoken Tutorial Project.