This last week has been a learning curve of sorts. I spent about 3 hours with my friends husband, Phil, who in some ways managed to confuse me even more, but in most ways helped me work through some of the issues I was up against.
He took me through some of the basics of sql and helped me to understand some simple things relating to 'values', 'names' and 'id's'. I finally got the connection between the allocation of values and names when doing forms, and how they related to the php code when executing a search.
Just prior to meeting with Phil I had a breakthrough with my recipe search function and got the search form working dynamically, so that the drop-down menu pulled the information for the categories and ingredients directly from the database. I was really pleased to be able to work this funcion out on my own. But much of it was trial and error and not a lot of understanding. Phil was able to enlighten me on it and I was then able to work through the actual php coding for the recipe search with him.
We talked about how I had set up the 'submit recipe' function in my design and analysis document and we both determined that by asking the user to submit three connected ingredients to their recipe may make the search function too complicated. So I decided to scrap this idea and make it a little more simple. I wanted to keep the idea of limiting the search to just 16 ingredients, so Phil showed me a way to set-up the $query statement to reflect a direct search for a category and then a 'LIKE' statement to reflect a search for an ingredient. He came up with this:
$query = "SELECT * FROM recipe WHERE recipe_ingredients_list LIKE '%".$_POST['ingredients']."%' and category = $type";
I got home from meeting with him and got stuck into the details of the search form. I had to fiddle around with the Select query a bit to get it to work. I shifted the conditionals around a little bit and finally got it to work really well. At first I got it working, but it was clear that the category conditional was not working as I kept getting recipes displayed which didn't fit in with the chosen category, but after shifting the order of the conditionals around I had success. This is what I ended up with:
$query = "SELECT recipe_title AS Recipe, recipe_ingredients_list AS Ingredients, recipe_method AS Method FROM recipe WHERE category_id = $type AND recipe_ingredients_list LIKE '%".$_POST['ingredients']."%'ORDER BY recipe_title ASC, recipe_ingredients_list ASC, recipe_method ASC";
//This $query uses the LIKE conditional and asks for any recipe within the designated category which contains the designated ingredient, LIKE that described in the search form, through the 'search for recipe' function on each page of the site. It calls for the title, ingredients list and method for each relevant recipe.
} else {
$query = "SELECT recipe_title AS Recipe, recipe_ingredients_list AS Ingredients, recipe_method AS Method FROM recipe WHERE recipe_ingredients_list LIKE '%".$_POST['ingredients']."%'ORDER BY recipe_title ASC, recipe_ingredients_list ASC, recipe_method ASC"; }
//This calls for any recipe within the designated ingredient list and does not require a designated category. Because some users may want to only search for a recipe with a certain ingredient without having to specify a category. Choosing a category is not compulsory. $result = @mysql_query ($query);
I managed to refine the $query even more by adding the ORDER BY conditional, and adding some headings and borders, to create a simple table to display the results in. The result was simple, but effective. Not sure if I will have the time to refine the layout for it, but am happy that I got the dynmics working, which fulfills the requirements of the assignment.
Sunday, August 17, 2008
Sunday, August 10, 2008
Cleaning up the database
While I am waiting to get together with the mysql expert, I have decided to go back to my databsae and see if it needs to be cleaned up and tightened up a bit. I think that if I can get that in order that I might be able see a bit clearer on how to connect the foreign keys to each other when setting up the 'add a recipe' form.
This whole connection between tables and foreign keys has got me completely baffled. It is like the text book, Ullman, skirts around it with every database interface it covers. Then, I can't quite pinpoint what I should be querying when I search on the net for a solution. So I come up with nothing in that avenue too.
A couple of things I have done from this:
1. Ensured that every primary key and foreign key are of the same type - SMALLINT(4) UNSIGNED NOT NULL.
2. I fixed up the recipe table so that all the foreign keys are at the top of the table (below the primary key) instead of spread all over the place.
3. Ensured that every primary key is of the same type as their corresponding foreign key in other tables.
This whole connection between tables and foreign keys has got me completely baffled. It is like the text book, Ullman, skirts around it with every database interface it covers. Then, I can't quite pinpoint what I should be querying when I search on the net for a solution. So I come up with nothing in that avenue too.
A couple of things I have done from this:
1. Ensured that every primary key and foreign key are of the same type - SMALLINT(4) UNSIGNED NOT NULL.
2. I fixed up the recipe table so that all the foreign keys are at the top of the table (below the primary key) instead of spread all over the place.
3. Ensured that every primary key is of the same type as their corresponding foreign key in other tables.
Saturday, August 9, 2008
The Home Stretch
Ok, now that the second assignment is in, it must be all downhill from here. Or maybe it is a steep climb to the end. It was a real stretch to get the second assignment in. I really didn't think that I was going to make it with my login and logout code. But thanks to the help of Peter Martin (another student) I was able to get it over the line just in time, with the login and logout working well.
I faced up to the reality of what I need to accomplish over the next few weeks with this assignment and wondered if I will be able to achieve what I outlined in the Analysis and Design Document. Edwin made comment in my assignment 1 results that it was a good and solid database outline, but that it was going to be tricky and complex (but manageable). This week I started early to tackle what I thought would be the most difficult part of the site - the 'add a recipe' form and its complex coding. This was a dramatic understatement. I have spent all week struggling to understand just how I am going to do it.
My biggest problem has been knowing how to approach the issue of dealing with information which needs to be distributed to several different tables and then reflected back into the recipe table and the associated_ingredients table through Foreign keys. I have read just about every part of Ullman, several times over, trying to get a feel for the correct flow of the coding for it. But there is nothing in there that is clear in how to deal with multiple tables in the way that I need. I think I was spoilt with the first assignment, in that Ullman provided a perfect framework for what I wanted to achieve with the login and logout pages. What gets me is that there are bits here and there of code which I think are relevant, but I just don't know how to put it together to make it work. Now I feel like I am totally on my own and unable to get an idea of how to even approach it. I spent a lot of time on the net searching form something that would get me on the right track. But, again, nothing which resembled anything I need to do with the form. My biggest issue is that I don't know what order to do things in, and how to link so many different fileds to so many different tables.
I linked up with Peter again on Friday for about two hours on Yahoo, but as he has not done anything like this either we really didn't make any headway. I have been thinking that I need to link up with someone locally who has a hand on this whole thing, who can walk me through some of the more obvious aspects of it. By 2am this morning I realised that i was not going to do it on my own.
This morning I rang the husband of a friend who I thought might either know something or someone to help me. I started telling him what the assignment entailed and he jumped in and said that mysql was like talking english for him. So I am going to get together with him sometime this week and nut out how I need to approach the rest of the site design. I feel so much better about this now. By having someone right next to me and being able to answer my questions and offer solutions is going to make this so much easier for me. Yesterday I was considering a fail on this assignment as I couldn't see a way out of this. But today I think I am actually going to get something out of this unit and come away having learnt something.
I faced up to the reality of what I need to accomplish over the next few weeks with this assignment and wondered if I will be able to achieve what I outlined in the Analysis and Design Document. Edwin made comment in my assignment 1 results that it was a good and solid database outline, but that it was going to be tricky and complex (but manageable). This week I started early to tackle what I thought would be the most difficult part of the site - the 'add a recipe' form and its complex coding. This was a dramatic understatement. I have spent all week struggling to understand just how I am going to do it.
My biggest problem has been knowing how to approach the issue of dealing with information which needs to be distributed to several different tables and then reflected back into the recipe table and the associated_ingredients table through Foreign keys. I have read just about every part of Ullman, several times over, trying to get a feel for the correct flow of the coding for it. But there is nothing in there that is clear in how to deal with multiple tables in the way that I need. I think I was spoilt with the first assignment, in that Ullman provided a perfect framework for what I wanted to achieve with the login and logout pages. What gets me is that there are bits here and there of code which I think are relevant, but I just don't know how to put it together to make it work. Now I feel like I am totally on my own and unable to get an idea of how to even approach it. I spent a lot of time on the net searching form something that would get me on the right track. But, again, nothing which resembled anything I need to do with the form. My biggest issue is that I don't know what order to do things in, and how to link so many different fileds to so many different tables.
I linked up with Peter again on Friday for about two hours on Yahoo, but as he has not done anything like this either we really didn't make any headway. I have been thinking that I need to link up with someone locally who has a hand on this whole thing, who can walk me through some of the more obvious aspects of it. By 2am this morning I realised that i was not going to do it on my own.
This morning I rang the husband of a friend who I thought might either know something or someone to help me. I started telling him what the assignment entailed and he jumped in and said that mysql was like talking english for him. So I am going to get together with him sometime this week and nut out how I need to approach the rest of the site design. I feel so much better about this now. By having someone right next to me and being able to answer my questions and offer solutions is going to make this so much easier for me. Yesterday I was considering a fail on this assignment as I couldn't see a way out of this. But today I think I am actually going to get something out of this unit and come away having learnt something.
Thursday, July 31, 2008
References
(N.D.). 5.4.7. Causes of Access denied Errors. MySQL 5.0 Reference Manual, MySQL. http://dev.mysql.com/doc/refman/5.0/en/access-denied.html
(N.D.). 5.4.7. Causes of Access denied Errors. MySQL 5.0 Reference Manual, MySQL. http://dev.mysql.com/doc/refman/5.0/en/access-denied.html
(N.D.). B.1.4.1. How to Reset the Root Password. MYSQL 5.0 Reference Manual, MYSQL. http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
Maurer, T. (2008). Active Field for Registration page crmalibu, Sitepoint.
Maurer, T. (2008). Activation Solution. Petermartin29, Yahoo Messenger.
Maurer, T. (2008). Failure to Activiate. Bianca Todd & Peter Martin, Curtin UniversitybWebCT.
Meloni, J. C. (2007). PHP, MySQL and Apache, Sams Publishing.
Sean. (2007). "Registration Script." Retrieved 29th July 2008, from http://www.tutorialcode.com/php/registration-script/
Student, S. (2008). "Registration activation through email verification ation ation " Retrieved 29th July 2008, from http://codeigniter.com/forums/viewthread/75576
Taylor, A. G. (2003). SQL Wiley Publishing.
Ullman (2005). PHP and MySQL For Dynamic Web Sites, Peachpit Press.
Ullman, L. (2005). Programming with PHP. PHP and Mysql For Dynamic Web Sites, Peachpit: 35-79.
Ullman, L. (2005). Example - User Registration. PHP and MySQL for Dynamic Websites, Peachpit Press: pp. 527-575.
Valade, J. (2008). PHP & MySQL Web Development, Wiley Publishing.
Breakthrough!
Well, I didn't think I would make it, but thanks to Peter Martin's help I got there. I now have a fully functioning registration, login and logout function on my website. It took about 6 hours last night to work through it. In the end it was just a matter of one or two changes to the script. But it was a marathon effort.
Most of what occured is mentioned in my comments within the script, but really it was just a matter of working through each error message as they arose and dealing with them one by one. Most were very simple, such as leaving a comma in where it shouldn't be and knowing the correct field to include when using sessions and how those connected to other pages.
We really got bogged down in it after a while so, since I was pretty much using the same script as Ullman (Ullman, 2005, pp. 527-575), Peter suggested we download the script for the login and logout pages from his book and start again. Two things occurred at that moment:
1. I went into shock to discover that I could have downloaded the script from Ullman's website. I had spent a good part of a week typing up the script for each page thinking that was the only way I could get it into my notebook++. However, I was consoled when I realised that I learnt a heck of a lot of stuff by typing it up myself and figuring out what much of it meant.
2. I was reluctant to download it as I was fairly sure that I had typed it up pretty accurately (since I had gone through it so slowly when I did it) and that I had only made a few changes to the original script. However, I knew I was not infallable (I know, hard to believe isn't it!) so I agreed.
We spent the next several hours going through each line step by step and working out what the problems were when we tested it. It proved to be a really worthwhile exercise as this morning i was able to go through and comment on everything in more detail without much help from the text book.
Please note:
Most of what occured is mentioned in my comments within the script, but really it was just a matter of working through each error message as they arose and dealing with them one by one. Most were very simple, such as leaving a comma in where it shouldn't be and knowing the correct field to include when using sessions and how those connected to other pages.
We really got bogged down in it after a while so, since I was pretty much using the same script as Ullman (Ullman, 2005, pp. 527-575), Peter suggested we download the script for the login and logout pages from his book and start again. Two things occurred at that moment:
1. I went into shock to discover that I could have downloaded the script from Ullman's website. I had spent a good part of a week typing up the script for each page thinking that was the only way I could get it into my notebook++. However, I was consoled when I realised that I learnt a heck of a lot of stuff by typing it up myself and figuring out what much of it meant.
2. I was reluctant to download it as I was fairly sure that I had typed it up pretty accurately (since I had gone through it so slowly when I did it) and that I had only made a few changes to the original script. However, I knew I was not infallable (I know, hard to believe isn't it!) so I agreed.
We spent the next several hours going through each line step by step and working out what the problems were when we tested it. It proved to be a really worthwhile exercise as this morning i was able to go through and comment on everything in more detail without much help from the text book.
Please note:
*The Reset Forgotten Password and the Change Password links and files do not function yet.
* Also, the link to the functioning website through geoffrey_multimedia is situated at the top righthand of this blog, titled The 'Raspberry Whirl' Website
A couple of things need to be addressed, but nothing that effects the register, login and logout functions. I will list them below to remind myself of what I need to look at over the next four weeks to get it functioning as I want.
1. Understand the output buffering and make sure it functions on the pages that it needs.
2. Sort out the issue with the 'Welcome, 'username' not showing up when a user logs in.
3. Understand more about sessions and cookies and make sure they are functioning properly on each page
4. Possibly address the issue of having to move the login form from the top menu bar to a separate page. I didn't have time to research how to make this dynamic while it sits in the header.html template.
There are also some major things that I will need to address, that I don't understand, to get my site working in the way I outlined in the design document.
They are:
1. How I am going to make the search recipe option dynamic, since it sits in the header.html template. Similare to point 4 above.
2. How I am going to write the script for the highly complicated 'Submit Recipe' form (I am really dreading this after my experience with the fairly simple login/logout forms).
Tuesday, July 29, 2008
Activation of Registration
I have decided to make this a separate post as there is so much to this issue.
When I innitially made the attempt to register for the firts time I got as far as the activation link within the email before the error trigger showed up and I was not able to activate the registration. The active field of the database showed that there had been a registration with a email, username, and password, but that it had not been activated. the active column showed the #2 under it. Subsequent tries had the same result - all having the #2 in the active column.
I spent a lot of time cleaning up the script and managed to fix up the Parse errors and script errors, but couldn't get the validation to work.
My thinking was that it had something to do with the database setup, but I wasn't sure. Everything validated within the register.php, and it wasn't until the validate.php page was called up that the error message appeared. This said to me that the problem lay in the database structure.
As I studied the script in the activate.php file I felt that I should have to change the Char from (1) to (32), as the '$y' generated a temporary 32 character string which validates the registration, and then it reverts to NULL when the registration is validated. But changing this didn't solve the validation problem. So I was not sure where to look from there.
I did a bit of research on the internet, but couldn't find anything specific enough to solve the problem (Sean, 2007)(student, 2008). So I decided to ask for help on the WebCT forum. I recieved a couple of repsonses and then an email from Peter Martin suggesting we walk through it together on Yahoo Messenger (Maurer, 2008). He spent over an hour with me going through both the register.php script and the activate.php script. I also posted a question on Sitepoint Forums and had a response, but Peter and I had solved it before I could get right into it (Maurer, 2008).
It all came down to two problems. The first one was discovered by Peter:
1. The setting the variable for activate as '$a' on line 40 of the register.php page. I had not properly set it, but had actually set it as NOW(). Which meant I was issuing a date and time to it and not a variable. So I changed it. However, it still didn't solve the problem with the validation, but, both Peter and I felt that we were not far from the solution and that this was part of it.
2. I then mentioned that we probably should change the active field to Char(32) to allow for the 32 character temporary string. I ended up giving it Char(40). This worked a treat and before I knew it Peter had registered and validated.
Since then I believe I also need to change the NULL column to NULL instead of NOT NULL, as once registration is activated the active column reverts from a 32 character string to NULL. The database works the same with it, but still not sure if this is the right thing to do. Will keep looking into it.
Right now I have another problem on my hands. Validation works, but when I try to login after validation I keep getting the error:
Please enter a valid password
Please enter a valid username
So back to the drawing board....
When I innitially made the attempt to register for the firts time I got as far as the activation link within the email before the error trigger showed up and I was not able to activate the registration. The active field of the database showed that there had been a registration with a email, username, and password, but that it had not been activated. the active column showed the #2 under it. Subsequent tries had the same result - all having the #2 in the active column.
I spent a lot of time cleaning up the script and managed to fix up the Parse errors and script errors, but couldn't get the validation to work.
My thinking was that it had something to do with the database setup, but I wasn't sure. Everything validated within the register.php, and it wasn't until the validate.php page was called up that the error message appeared. This said to me that the problem lay in the database structure.
As I studied the script in the activate.php file I felt that I should have to change the Char from (1) to (32), as the '$y' generated a temporary 32 character string which validates the registration, and then it reverts to NULL when the registration is validated. But changing this didn't solve the validation problem. So I was not sure where to look from there.
I did a bit of research on the internet, but couldn't find anything specific enough to solve the problem (Sean, 2007)(student, 2008). So I decided to ask for help on the WebCT forum. I recieved a couple of repsonses and then an email from Peter Martin suggesting we walk through it together on Yahoo Messenger (Maurer, 2008). He spent over an hour with me going through both the register.php script and the activate.php script. I also posted a question on Sitepoint Forums and had a response, but Peter and I had solved it before I could get right into it (Maurer, 2008).
It all came down to two problems. The first one was discovered by Peter:
1. The setting the variable for activate as '$a' on line 40 of the register.php page. I had not properly set it, but had actually set it as NOW(). Which meant I was issuing a date and time to it and not a variable. So I changed it. However, it still didn't solve the problem with the validation, but, both Peter and I felt that we were not far from the solution and that this was part of it.
2. I then mentioned that we probably should change the active field to Char(32) to allow for the 32 character temporary string. I ended up giving it Char(40). This worked a treat and before I knew it Peter had registered and validated.
Since then I believe I also need to change the NULL column to NULL instead of NOT NULL, as once registration is activated the active column reverts from a 32 character string to NULL. The database works the same with it, but still not sure if this is the right thing to do. Will keep looking into it.
Right now I have another problem on my hands. Validation works, but when I try to login after validation I keep getting the error:
Please enter a valid password
Please enter a valid username
So back to the drawing board....
Monday, July 28, 2008
Tiny steps at a time
I feel like I am inching my way along a precarious wall 100 feet in the air. With every tiny step there is another boulder to hurdle.
So, I got through the password issue, and into the refining of the script. I started getting the error handling messages (which I set up in the config.php file) on the username validation. Obviously there was an issue with how I set it up - so I looked at the coding.
I had taken much of the coding for the registraion page from chapter 13 of Ullman's book and had to adapt it because I was not going to require a first name and second name. So in place of the first_name and second_name I included a user_name and used the same character class :alpha and the same characters as would be used for a first or last name (Ullman, 2005, p. 406). However, I later changed the character class to :alnum, because I wanted the user to be able to use numbers as well as letters. But I forgot to change the characters within the brackets to include a-z0-9 as well. So when I tried registering the username it kept sending me the error handling message. Once I added the extra range of characters it worked a treat and I was welcomed to the Raspberry Whirl website and instructed to go to click on the link which was sent to my email address to activate my account. It was all so exciting...
EXCEPT!!!
I also had a message below the welcome which indicated that the buffering session could not be terminated as there had not been an action to commence it. I straight away checked to see if I had included the start buffering code in the header.html template, which I had. So now I have to deal with this issue.
Update: Can't work out why this is coming up as not being commencing. Have looked at the coding - very simple - but not sure. As I feel that I have some other more important things to deal with for now I have decided to take out 'output buffering' and address it later.
However, I did go to my email and I found the link there in an email for me to click on. I did so, but failed to get back into the site. Something else to look at - I suspect it may be linked to the buffering issue, or the issue below, but not sure.
Then I wondered if the new user information had been recorded in my database, so I went in there to look. I was very excited to see that it had. But the username column had the email address connected to it and the email address column had the username connected to it. So I guess the order in which you submit a query is important. I will now go in and delete my registration and see if I can get the order right so that the information can record correctly.
Update: I just discovered that I had changed the order of the form handling on the register.php page, and the INSERT INTO $query, but didn't change the order of the VALUES (line #40 on register.php). That is why it was registering under the wrong columns. :-)
So, I got through the password issue, and into the refining of the script. I started getting the error handling messages (which I set up in the config.php file) on the username validation. Obviously there was an issue with how I set it up - so I looked at the coding.
I had taken much of the coding for the registraion page from chapter 13 of Ullman's book and had to adapt it because I was not going to require a first name and second name. So in place of the first_name and second_name I included a user_name and used the same character class :alpha and the same characters as would be used for a first or last name (Ullman, 2005, p. 406). However, I later changed the character class to :alnum, because I wanted the user to be able to use numbers as well as letters. But I forgot to change the characters within the brackets to include a-z0-9 as well. So when I tried registering the username it kept sending me the error handling message. Once I added the extra range of characters it worked a treat and I was welcomed to the Raspberry Whirl website and instructed to go to click on the link which was sent to my email address to activate my account. It was all so exciting...
EXCEPT!!!
I also had a message below the welcome which indicated that the buffering session could not be terminated as there had not been an action to commence it. I straight away checked to see if I had included the start buffering code in the header.html template, which I had. So now I have to deal with this issue.
Update: Can't work out why this is coming up as not being commencing. Have looked at the coding - very simple - but not sure. As I feel that I have some other more important things to deal with for now I have decided to take out 'output buffering' and address it later.
However, I did go to my email and I found the link there in an email for me to click on. I did so, but failed to get back into the site. Something else to look at - I suspect it may be linked to the buffering issue, or the issue below, but not sure.
Then I wondered if the new user information had been recorded in my database, so I went in there to look. I was very excited to see that it had. But the username column had the email address connected to it and the email address column had the username connected to it. So I guess the order in which you submit a query is important. I will now go in and delete my registration and see if I can get the order right so that the information can record correctly.
Update: I just discovered that I had changed the order of the form handling on the register.php page, and the INSERT INTO $query, but didn't change the order of the VALUES (line #40 on register.php). That is why it was registering under the wrong columns. :-)
Subscribe to:
Posts (Atom)
