Subtitles of the Movie

So far our script has successfully connected to the database and selected the particular database that we want to use but we haven't got any output from our query apart from the name of the table, which is not very useful. So let’s take a look at ways to output some data from the database. What we use now that we've got our result of our query here is a function called my_sql fetch row and what that does is it returns our numerically indexed array which we can use to access the data. So for a numerically indexed array, if you remember we could use ‘loop’ to go through and access all the elements within the array. However there are a number of different rows in the, as the result of the query. Each row represents one row within the database. So the way we loop through all the different rows to act on those is we can simply use a ‘while’ loop and we'll set a variable, let’s say ‘record’ to the value of the result of this function my_sql fetch row and we’re going to do that from the result of the query, so that if this executes successfully that means the data has been returned and has been assigned to ‘record’. If it doesn't work that means that no data has been assigned to ‘record’ and this ‘while’ loop will stop. So now we’ve got a record variable containing a row from the results and we’re going to have to loop through that to check every element within the row, so every column within the row essentially. So lets use a 'for' loop similar to the ones we have used before and that's going to go through the length of a number of elements within the result array and every time it finds one, it’s going to echo this element on it’s own to the browser and let’s put in another line break over here, this is so that the different records will be split up from each other and finally we finish our 'while' loop with another curly bracket. Let’s save that and see what our my_sql fetch row has done for this php script. As we can see, the data is been output now, we have successfully used our php script to access the data within our database and its being output in a rather random way, well it’s not random but it’s not very structured in terms of how the user is being presented with the data. There are a lot of empty spaces and although we’ve got our data here we don't know what any of these are. Maybe a little common sense could tell us that this is an email but one of these two figures is the phone and one of these is the fax and we’re not being told, so that's not very useful. If we go back to our php script, there is another more useful way to do this, which involves associative arrays. Now a change that we need to make here is to use a different php function called ‘my_sql fetch assoc’ for associative array. Now this, of course we won’t be able to use this ‘for’ loop because as we found out in the last chapter, ‘for’ loop will only work with numerically indexed arrays, what we’ll use is another ‘while’ loop and we’re going to use that list function that we learned about in the last chapter, the ‘list’ and ‘each’ functions to find the key and the value of each element within the associative array. So we’re going to say field name could be the name of the key and field value can be the name of the value and we’re going to act on the record associative array. This works very similarly to the example in the last chapter. We’re going to output to the browser the field name and then concatenation symbol and a quote there and then we’re going to put in simply a colon and a space, separate them and then the field value and we shouldn't forget to put in another html tag within double quotes as always and we’ll close that in a ‘while’ loop. So where before we had a ‘for’ loop within a ‘while’ loop, here we have 2 ‘while’ loops but it’s doing pretty much the same thing. The outer one loops through all the different rows within the result of the query that we specified while this inner loop here loops through all the elements within the array that we fetched for each row. Let’s take a look at how that's working. If we refresh our browser, we'll see that this time it’s included the keys, each of the keys and where there is a blank field with nothing in it, the key is still specified so we can see what is missing from there, phone and fax, obvious which ones which and the data is generally presented in a much more useful way. Once again I suggest you take the opportunity to have a play around with these new ideas, try out some queries of your own on the sample database or on your own database.