Cheeseslice

Hello again, I have a few minutes and I'm working through the queries. I'm attempting to create a table that will produce 3 columns for choices and a total. So that I can see the most popular combination organised by total users who choose it. I know this is going to sound like a daft question but what process do you use to work out how to do this using mysql? I have been skimming over a few tutorials and on one of them it was suggested that you work backwards when you are dealing with multiple nested queries and joins. Most queries are deal with are very basic but I'm thinking this is a skill I should really learn a bit more about.

Sorry, I worked it out. I'm too tired. I have never done a self join on a table before and I couldn't work out where you were creating/defining the two tables. I think I have it working now.

Hi Adam, is there any chance you could talk me through these queries. I cant get my head around it. Where is usr1.shifts_id and s1.id defined?

Thank you for taking the time to look at this for me. I have a couple of things that I need to get finished before 5, then I'm going to play with my sample data. I'm thinking I need to start revising my knowledge of queries, my idea for solving this problem was so far off. I hope you don't mind but I will probably be coming back to ask you a few more questions.

Hi, I am really appreciating this help so I don't want to come across as ungrateful but I don't think this query will produce the data to create the table that I need. I do want to filter the entire table to only take into account results that have rank <=3 but the rank of the is unimportant other than to get the top 3 choices. I need each count to be based on a permutation of the shifts i.e. total uses who select Cobination | Count shift_id 1 & shift_id 2 shift_id 1 & shift_id 3 shift_id 1 & shift_id 4 shift_id 2 & shift_id 3 shift_id 2 & shift_id 4 shift_id 3 & shift_id 4 With the query that you have given me as an example I can only produce a table showing the total number of users who give a shift_id a particular rank. I have quickly created a small example of how this would look in excel. Thanks again.

Hi Adam, thanks for this. I have just created a table of sample data and have spent the past hour trying to work out why array_rand insists on outputting zero's but that's another story! I have tried your query and what it returns is a count of a particular subject with a particular rank. is that right or am I being totally stupid? so I the output is along the lines of: shiftsId | rank | rankCount 1 | 1 | 30 What I would need though is: shiftsId(Opt1) | shiftsId(Opt2) | Count 1 | 2 | 20 This table shows that 20 people choose the combination of shift pattern 1 and shift pattern 2. I feel that I'm not explaining myself very well.

The rota can basically allow for 3 (sometimes 4) shift patterns to operate at the same time, it depends on the job being done at the time and how long the job is going to last. The users working on the project are given a choice of 19 different patterns and asked to pick 6 but really I am only interested in their top 3 as the intention is to create a rota that accommodates the majority of people. So basically the rank is unimportant at this point, I just want to know which 3 shift patterns that each user wants to work (which I take as their top ranked choices 1,2,3). I actually want to create two tables. The first is the one we have been discussing which shows at a quick glance how popular a pair of patterns would be. The 2nd table I would like to produce will then show the the most popular sets of 3 shit patterns. This would be in the format: option 1 | option 2 | option 3 | total number of users who selected this combination I would really appreciate you giving me an example of how I would produce this table using one query. The only way I can think to do this is to use the header for the column and the header for the row of the cell as a reference. Then run a query for each individual cell to count how many users in usersShiftsRank have selected both of these shifts and ranked them 1, 2 or 3.

thanks for the detailed reply Adam. I write these questions and don't explain myself, I expect people to know what I am thinking. Here are my tables: users id | name ---------------- 1 | Adam 2 | Cheeseslice shifts id | description ---------------- 1 | 2 weeks on 2 weeks off 2 | 4 days on 3 days off 3 | 5 day week, 9 to 5 usersShiftsRank id | users_id | shifts_id | rank ----------------------------------- 1 | 1 | 1 | 1 2 | 1 | 3 | 2 3 | 1 | 2 | 3 4 | 2 | 3 | 1 5 | 2 | 1 | 2 6 | 2 | 2 | 3 This shits table is just example data, it will hold a minimum of 19 to begin with. The user logs in, they select 6 patterns that they would consider, they then give each pattern a number 1-6 to rank it in order of preference. I have this all working and updating to these tables. The purpose of collecting this data is to really discover which sets of data are the most popular so that rotas can be designed to accommodate the majority of the people. So really until the very end when the rota has been created and the set of shits has been agreed I only need to work with the one table, usersShiftsRank. For my table to determine pairs, which is basically going to be 20x20 I am going to have 190 results (if I have done my sums right and excluded duplicates). So I need to count how many users choose shifts 1&2,1&3,1&4 and so on, is it realistic to have a separate query for each of these 190 cells in the table? To estimate the size of the table I would say the pilot will be 100 users each picking 6 shifts with me only interested in ranks 1-3, if the system works then it will be used to survey a far larger set. Once I determine the most popular pairs of shifts I will then need to produce a table and graph showing the most popular combinations, again this will only take into account the shifts ranked 1-3. The easy solution would be to export the data to excel and just use the spreadsheet I made years ago but I really want to learn php so I am trying to move all of my existing systems over to make things more accessible and give me experience solving real problems. Thanks again.

Thanks, I will have a look at how to use these to graph the data. Im going to have to spend a bit of time on this table I need to produce first. I don't even know what the proper name for this style of table is to look up how others would code it. In excel I would just offset references to both column and row headers but I'm new to php.

Hi, I have created a survey, a basic form split into two sections. The user selects 6 options from a set of 20, they then rank these 6 in order by entering a number in a text field associated with their choice. At the minute I am only interested in their top 3 so I have created a table that has userid,option1,option2,option3. What i need to do is count the number of times each paired combination occurs in this table and present it in the format a distance table would be in, so that the most popular combinations can be determined. This is to do with shift patterns and rotas. A simplified example of 4 shifts. I have given each shift an id number 1,2,3,4,5 User1 selects 1,2,3 so has combinations 1&2,1&3,2&3 User2 selects 1,2,4 so has combinations 1&2, 1&4, 2&4 So from this I want to have a table with id as column and row names, with combination totals in the cells 1 2 3 4 1 0 2. 1. 1. 2 2 0. 1. 1. 3 1 1. 0. 0 4 1 1. 0. 0 This table is just the beginning as I will need to provide information for each shift in relation to the others but I think once I get my head around this I should manage. I can produce this table in excel but I don't know where to start with php and mysql. Should I be creating a table to store the combinations somehow, or do I run a query for each cell, which is fine for a table like this with 6 but it would be very heavy going once I was calculating my full table with a lot of users. Is it easy to display data in graphs with php? If anyone could give me some advice I would appreciate it.

Thanks for the reply. Originally (many years ago) I did my degree in applications development, java, vb, coldfusion, oracle and a few other things I can't remember. Unfortunately I ended up going down the hardware support route and only dabbled in web development when someone asked me for a favour. I will be the first to admit that I don't really remember anything from that long ago but I think with a bit of effort I should be able to start picking up things again quite quickly. I have been doing a bit of research this past few weeks, looking at the sort of jobs I might be interested in, and from what I can see the main contenders for well paid work are php, javascript, c#, asp.net and maybe some python. Most positions that I saw involving php do ask for experience developing in a framework environment which is why I would consider doing this project in something like cakephp to learn how it all works. I suppose my real issue is learning how to code fast, generally I can work out a problem if I can't find a similar piece of code using google I end up spending a few hours wading through the php manual only to discover there is a function that already does what I need. Would you recommend learning different frameworks from the start?

Hi, I've made the decision to learn php (and javascript) in the hope of expanding my skillset and changing career paths a bit. I have been asked to create an application that will support a few hundred users, the general idea behind it is that each user will have their own work area containing a number of different forms. These forms are based on templates created by a supervisor and as each form is finished it will be printed as a pdf. A supervisor is responsible for around 50 users, they need to be able to see the progress of each user that they are responsible for. In addition to this I need to allow for this application to expand to different locations with their own supervisors, users and forms. I think I can get my head around the database and planning the relationships between each table but I don't know how you go about starting to plan for an application to be developed in php. I have looked at quite a few jobs over the past number of weeks and all of them want experience in using frameworks. Is it best to start from scratch or do most companies stick to using the likes of cakephp to speed up developement? Once I have created a map of the pages I require and the tables that I need do I start with the security aspect of the application or do I just design each page and worry about how the pages are secured at the end. I know I'm a bit vague so what I'm really asking is for examples of how someone would tackle a project like this. Or if anyone could provide me with links that would cover the design process I would be very grateful. Many thanks

As a matter of interest. Is it more efficient to run one loop? <?php $array = range(1,26); // fill an array with values 1-26 $max=count($array); $i=0; while($i<=$max){ $c1html .= $array[$i] . " "; $i++; $c2html .= $array[$i] . " "; $i++; $c3html .= $array[$i] . " "; $i++; $c4html .= $array[$i] . " "; $i++; } ?> <div class="c1"><?php echo $c1html;?></div> <div class="c2"><?php echo $c2html;?></div> <div class="c3"><?php echo $c3html;?></div> <div class="c4"><?php echo $c4html;?></div>

Hey, this is my first post so be gentle. I have actually just decided to start learning PHP today and this seemed to be the forum where everyone hangs out, I saw your post and thought it would be something that I would probably use quite often. This code can probably be reduced to a couple of lines (I was using the php manual) but you can achieve what you want by running 4 loops. Would anyone be kind enough to point me in the right direction for newbie tutorials. Many thanks. <?php $array = range(1,26); // fill an array with values 1-26 $i = 1; $max=count($array); echo $max; $i=0; echo "<div class=\"c1\">"; while($i<=$max){ echo $array[$i] . " "; $i+=4; } echo "</div>"; $i=1; echo "<div class=\"c2\">"; while($i<=$max){ echo $array[$i] . " "; $i+=4; } echo "</div>"; $i=2; echo "<div class=\"c3\">"; while($i<=$max){ echo $array[$i] . " "; $i+=4; } echo "</div>"; $i=3; echo "<div class=\"c4\">"; while($i<=$max){ echo $array[$i] . " "; $i+=4; } echo "</div>"; ?>