Jim R

I think I was editing while you were responding. Sorry about that. The form is set up purely in HTML (.php page) and posts Action to a .php page. I've had the form for about five years. Only now and going forward am I wanting to put the results from Users into a database. This year I'm wanting to put the User's choices in a data table.

Do I need to re-write the form in PHP? Here is a sample drop down with the choices. The "name" is section number I need to insert. I figured I could duplicate that by counting instances in my foreach loop. But sticking to the question at hand, will I need to wrap all 64 of my drop downs in PHP? <select name="1"> <option selected="selected">____select team</option> <option>1. Hammond Morton, 7-13</option> <option>2. Highland, 10-11</option> <option>3. Gary West, 11-10*</option> <option>4. Munster, 20-0</option> <option>5. Lake Central, 12-8</option> <option>6. Lowell, 12-8</option> <option>7. East Chicago Central, 13-7</option> </select>

Should I make all the form items have the same name? (Then use a counter to simulate the section number?)

I've had an online form that would be processed in this way: Insert the User's information - name, email, school - into a database. Then it would email me that information as well as the responses to 64 questions. I'd like to now put those answers in a data table. Here is how those responses would be sent to me, with their form (drop down items) item name: $message .= $_POST['1'] . "\n"; $message .= $_POST['2'] . "\n"; $message .= $_POST['3'] . "\n"; $message .= $_POST['4'] . "\n"; .... ......... all the way down to 64 .... $message .= $_POST['64'] . "\n\n"; I have a data table with the following columns: (user_info) ID nameFirst nameLast email school I have another table with the following columns: (user_answer) --> this is the table I need help on ID uID section --> that corresponds with the form item name answer --> actual answer chosen For each uID, I'll have 64 ID's, so it will be for example: 1 1 1 Munster 2 1 2 Valparaiso ...... 64 1 64 Tecumseh 65 2 1 Lake Central 66 2 2 Merrillville The question I have (it may be a simple one) is how do I get those 64 answers from a User into an Array that I can start building a Loop to Insert?

It wasn't just about the variable but the location of the IF loop. $subscriptionLevels = array( 's2member_level3' => 'Yearly', 's2member_level2' => 'Semi-annual', 's2member_level1' => 'Monthly' ); $query = 'SELECT um1.meta_value as custom, um2.meta_value as level, u.ID,u.user_login, u.user_email FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 ON um1.user_id = um2.user_id JOIN wp_users u ON um1.user_id = u.ID WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%" GROUP BY um1.user_id'; $result = mysql_query($query); //Process results into temp array $regionData = array(); while($row = mysql_fetch_assoc($result)) { //var_dump($row); $custom = unserialize($row['custom']); $user_region = $custom['county']; $level = unserialize($row['level']); $level_desc = key($level); //E.g. s2member_level3 if ($level_desc != "s2member_level4") { $user_level = $subscriptionLevels[$level_desc]; } if(!isset($regionData[$user_region])) { $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0); } $regionData[$user_region][$user_level]++; } //Output the results foreach ($regionData as $region => $data) {if ($region != 0) { echo "<br>Region {$region}<br>\n"; foreach($data as $subscription_type => $member_count) { var_dump($subscription_type); echo "{$subscription_type}: {$member_count} members<br>\n"; } } }

I put it in. It didn't tell me anything. Here is what is produced: string(0) "" : 1 members

That's not going to tell me what variable to use. That's just going to confirm what I already know. It doesn't work. As I said, that was just the last variable I tried before posting the code.

If I knew the answer to that, I wouldn't have tried it. Is there a chance instead of telling me what doesn't work, which I already know doesn't work, could you show me what does work? I've tried several of the variables dealing with level, and I've tried that IF in a couple of places. I simply left it as I last tried it and posted the code here so it could be seen as reference. it's reflecting those with s2member_level4 with that line, and there would be others showing if Region 0 (those who hadn't picked a County) was still showing. I took out the s2member_level4 from the first array because it's not going to be used.

Here is the code with comments where I added IF loops: $subscriptionLevels = array( 's2member_level3' => 'Yearly', 's2member_level2' => 'Semi-annual', 's2member_level1' => 'Monthly' ); $query = 'SELECT um1.meta_value as custom, um2.meta_value as level, u.ID,u.user_login, u.user_email FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 ON um1.user_id = um2.user_id JOIN wp_users u ON um1.user_id = u.ID WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%" GROUP BY um1.user_id'; $result = mysql_query($query); //Process results into temp array $regionData = array(); while($row = mysql_fetch_assoc($result)) { //var_dump($row); $custom = unserialize($row['custom']); $user_region = $custom['county']; $level = unserialize($row['level']); $level_desc = key($level); //E.g. s2member_level3 $user_level = $subscriptionLevels[$level_desc]; if(!isset($regionData[$user_region])) { $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0); } $regionData[$user_region][$user_level]++; } //Output the results foreach ($regionData as $region => $data) {if ($region != 0) { // This worked, eliminating Region 0 echo "<br>Region {$region}<br>\n"; foreach($data as $subscription_type => $member_count) {if ($subscription_type != "s2member_level4") { // This did not eliminate s2member_level4 row echo "{$subscription_type}: {$member_count} members<br>\n"; } } } } I'm trying to get rid of: Region 4 Yearly: 0 members Semi-annual: 0 members Monthly: 0 members : 1 members <---------- that row there

I got rid of Region 0, but I can't get rid of the s2member_level4 row. I've tried a few variables. $subscriptionLevels = array( 's2member_level3' => 'Yearly', 's2member_level2' => 'Semi-annual', 's2member_level1' => 'Monthly' ); $query = 'SELECT um1.meta_value as custom, um2.meta_value as level, u.ID,u.user_login, u.user_email FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 ON um1.user_id = um2.user_id JOIN wp_users u ON um1.user_id = u.ID WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%" GROUP BY um1.user_id'; $result = mysql_query($query); //Process results into temp array $regionData = array(); while($row = mysql_fetch_assoc($result)) { //var_dump($row); $custom = unserialize($row['custom']); $user_region = $custom['county']; $level = unserialize($row['level']); $level_desc = key($level); //E.g. s2member_level3 $user_level = $subscriptionLevels[$level_desc]; if(!isset($regionData[$user_region])) { $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0); } $regionData[$user_region][$user_level]++; } //Output the results foreach ($regionData as $region => $data) {if ($region != 0) { echo "<br>Region {$region}<br>\n"; foreach($data as $subscription_type => $member_count) {if ($subscription_type != "s2member_level4") { echo "{$subscription_type}: {$member_count} members<br>\n"; } } } }

I come here as my last resort, and I put the problem out there while I work on it. Sometimes I get it by talking through it. Sometimes I don't. I would think a "where" in the foreach loop would make the most sense, but I'm not sure that works. I started to search for it as I sat down then saw the email notification.

I think we're good on the data now. It was the first query you provided, but I added the Group By back. There are more than one record for some of the Users. Not sure why, but I think it could have been due to a change in the plugin. At first, I think it Inserted new rows with each change, where now it updates, because suddenly my last four Users just have one record that matches. Here is what I have now, with your tweaks and initial query. $subscriptionLevels = array( 's2member_level3' => 'Yearly', 's2member_level2' => 'Semi-annual', 's2member_level1' => 'Monthly' ); $query = 'SELECT um1.meta_value as custom, um2.meta_value as level, u.ID,u.user_login, u.user_email FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 ON um1.user_id = um2.user_id JOIN wp_users u ON um1.user_id = u.ID WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%" GROUP BY um1.user_id'; $result = mysql_query($query); //Process results into temp array $regionData = array(); while($row = mysql_fetch_assoc($result)) { //var_dump($row); $custom = unserialize($row['custom']); $user_region = $custom['county']; $level = unserialize($row['level']); $level_desc = key($level); //E.g. s2member_level3 $user_level = $subscriptionLevels[$level_desc]; if(!isset($regionData[$user_region])) { $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0); } $regionData[$user_region][$user_level]++; } //Output the results foreach ($regionData as $region => $data) { echo "<br>Region {$region}<br>\n"; foreach($data as $subscription_type => $member_count) { echo "{$subscription_type}: {$member_count} members<br>\n"; } } Here is what it's producing, which is really close to my needs: I need for Region 0 to not show up at all. Also, the rows with just a count (one in Region 4, the other is in Region 0), I need for those not to show. Those represent s2member_level4.

I know what those odd lines are now. They Users with s2member_level4, which shouldn't be part of the print out. That's a different part of the site. I didn't think about that.

You need to explain to me what you mean by data requested if what I've posted isn't good enough. I posted the data in the post that started this topic, and I noted that couple of posts up. THAT's the data the Inner Join needed to get. It's just a sample of the data, but there are over 7,000 rows in that table. I figured just a few might help. As for the other table, I've noted a few times I just need it for user_login and user_email. Each User has anywhere from 20-30 rows associated to their user_id. To keep it simple, just know, that line links the user_id on both sides of the Inner Join, and that is necessary to get the correct count. I've noted that before. What we have now is really close, minus a couple of weird lines.

I added a line break. It has a couple of odd lines. One in Region 0. Another at the end.

Why did you change the query? It worked. I tried your last one without even looking at the query. I just looked at how you were creating the array. Getting rid of um1.user_id=um2.user_id screwed up the count.

I get that, but that aspect of it was still working. I tested it. I put in the aliases as you helped me with the rest of my query. It produced the same results as your changes did. I definitely see the logic of your point though, and I appreciate your help. I'm stepping out for lunch right now and will work on that code when I get back. Thank you!

I probably can't do the subscription levels the same way as I did the Regions, can I? I'd have to associate the s2member_level1 to Monthly, s2member_level2 to Semi, and s2member_level3 to Yearly, then count each set, then echo in the usernames.

Yes, I tried the code, and it works. I noted that it was progress. You didn't change any of the Joins from what I could tell. I had never set up aliases in the Select part. I posted the data on the first post. The Join you don't like links the two rows or data to a single ID. Not everyone who has Subscribed listed their County. Not everyone who has put in their County is a Subscriber. I don't want it to pull people who listed their County or people who have Subscribed. I only want it to pull those who have both. -- ON um1.user_id=um2.user_id -- links the ID's on each side of the Join. Otherwise, it pulls 97 rows, AND it just shows them all as having the same subscription (not sure why that would be the case). With that line, 18 rows, correct associations. You changed it around a little, but here is the query I had before, altering the way the aliases are set up: SELECT um1.meta_value AS custom,um2.meta_value AS level,u.ID,u.user_login,u.user_email FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 JOIN wp_users u ON um1.user_id=um2.user_id WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%" AND um1.user_id = u.ID GROUP BY um1.user_id It gave me the same results. I get what you're saying, but that query was formed on here in another, helped in part by PMF. I do see, however, the logic in how you reformed the query and will stick with it. Now, my last issue (I think) is something I posted earlier. How do I get the output to look like this: (I struggle with printing hierarchies) Region 1: Yearly: ## members Semi-annual: ## members Monthly: ## members Region 2: Yearly: ## members Semi-annual: ## members Monthly: ## members etc...through Region 5.

Without it, it gives me 97 rows, instead of 18. It matches the two criteria to similar user_id's. Otherwise, it would just pull every instance. I just need the ones that are associated with the same User. Just like without the Group By, I get 42 rows instead of 18. I need Users who have set up their County of residence and are Subscribers, and for reasons of listing them, I want their User name and email address. There is some progress. The var_dump shows the County fields, so that's good. I appreciate you putting the code up. I've never seen aliases set up that way. For sanity's sake, I changed $meta_value to $county. So that's looking good. I assume if I want to unserialize the s2member_levels, I just duplicate it like this: $level = unserialize($line['level']);

I've never had any issues with the table query not working in a PHP query until now. I've worked with aliases before but not with Inner Joins, which is why this has been troublesome. Can you, looking at my code, just tell me what it should look like instead of what it isn't? Because what you showed me doesn't appear to apply to Inner Joins where table 1 and table 2 are the same.

How does that help me with the Inner Join? Table1 would be the same as table2. I added the AS in my query, and it didn't change the results. As I understand it, these are aliases: FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 JOIN wp_users u

If um1.meta_value isn't the alias name, then I don't know what that is. I clearly thought that was the alias name, as I changed it to that after you said to. And I do have var_dump($line) inside the While as you said in reply #3.

@Psycho, in my database viewer, Sequel. @PFM, I asked two different people that, and they said it didn't matter. Since it showed up in my query in Sequel I never posed the question. It makes perfect sense to me, but it doesn't appear to have changed anything. I'm getting the Selected columns in my database viewer but not the var_dump. Well, I'll post the code and var_dump below: $custom = 'SELECT um1.meta_value,um2.meta_value,u.ID,u.user_login,u.user_email FROM wp_usermeta um1 INNER JOIN wp_usermeta um2 JOIN wp_users u ON um1.user_id=um2.user_id WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%" AND um1.user_id = u.ID GROUP BY um1.user_id'; $c_results = mysql_query($custom); $region = array(); while($line = mysql_fetch_assoc($c_results)) { $meta_value = unserialize($line['um1.meta_value']); $region[$meta_value['county']]++; var_dump($line); } foreach ($region as $key => $value) { echo "Region $key: $value members<br>"; } Here is the var_dump:

I'm looking at the database results, and the query to get those results were figured out here in another topic. So the question is why is it showing up in the database query but not the var_dump.