Posted in capacity as Module Leader Group Software Development, Department of Computing Sheffield Hallam University A Morgue One hundred students, 10 clients, 5 labs of University PCs, overzealous Facilities department and the Sheffield weather. What could possibly go wrong? Last week was ‘Scrum Week’. Nineteen groups of students working flat out 9-5, Monday to Friday…
Read MoreQuick tip post. If you have set your data in MySQL to be utf-8 and have special characters such as umlauts (ö) and accents (é) then ensure you add the following: This could be added to your connection information as follows: This sets the character set used in the msqli method to utf8. If this isn’t…
Read MoreWhen you have a database make it work for you. This example shows how to use a database to create a simple HTML drop down list. Assume we have a table such as: Film Name Film Certificate Tropic Thunder 15 TRON: Legacy PG Inception 12 The Tourist 12 The Shawshank Redemption 15 The Usual Suspects…
Read MoreInserting multiple values with a prepare statement can be a little tedious. This solution uses PDO rather than mysqli as you can loop around the bindValue statement. The code also assumes a table structure where the first field is an auto-increment thus the loop is pushed on one so as to ignore this field. You…
Read MoreOften with date based data, such as events listings, you may need to extract all the records that match a particular calendar month. Here all you need from the user is a month and year. Then you can let SQL do the rest, for example: The explanation bit: the magic here is done by DATE_ADD…
Read MoreTo accompany the mysqli cheatsheet here is a PDO cheatsheet. Give me one record Give me the whole lot One row from user input using prepare Multiple rows from user input using prepare INSERT Get Last Insert ID UPDATE DELETE
Read MoreTo debug a mysqli prepare statement we could place the prepare statement within an if as follows: The above can help but it often you want to see the SQL that was created by the prepare statement. Unfortunately there is no easy way to do this. However, the function below might help. You can feed…
Read MoreMany web applications require the developer to add data, if none is there, but amend data if some exists. Take the example of an online survey. The user answers a question, and in our SQL we use INSERT to add the data to MySQL. The first time a record is added we could use ‘auto-increment’…
Read MoreConcatenation – such a wonderful word to say. Also a real get out of jail card when manipulating data in your MySQL database. Take a scenario where you want to create a ‘fullname’ field from a ‘firstname’ and ‘surname’ field. This can be achieved with the CONCAT command. The syntax is A SQL SELECT statement…
Read MoreProject after project connecting to databases. What I need is a cheatsheet for the PHP MySQLi extension to cover the most common querying scenarios. Here we go: Give me one record Give me the whole lot One row from user input using prepare When you have variables coming via $_POST or $_GET ($_GET in this…
Read More