|
|
Joined: Apr 2003
Posts: 30
User
|
|
User
Joined: Apr 2003
Posts: 30 |
I asked this over at UBBCentral earlier this week, but no one will respond.  I need to pull data from polls with a mysql query. I have played around with some queries, and I can't seem to get what I need. This is what I need: - name of poll in database - title of poll - member # - member display name - name of poll option member selected An example of what I am looking for ... 1062726017GLCOM | Favorite Fruit? | 2121 | Bobbie F | oranges 1062726017GLCOM | Favorite Fruit? | 1025 | smackdown | pears 1062726017GLCOM | Favorite Fruit? | 8547 | kelly | oranges 1062726017GLCOM | Favorite Fruit? | 457 | CindyT | oranges 1062726017GLCOM | Favorite Fruit? | 58 | kickin steve | grapes Basically, I only want the members that have voted in that poll and which option they selected. I seem to get some of the data from different MYSQL queries I try, but I seem to get all options shown for each member. I just want which one they selected if they voted. Thanks.
|
|
|
|
|
Joined: Sep 2003
Posts: 488
Code Monkey
|
|
Code Monkey
Joined: Sep 2003
Posts: 488 |
Hi, try this... SELECT t2.P_Name,t2.P_Title,t3.U_Number,t3.U_Username,t2.P_Option<br /> FROM w3t_PollData AS t1,<br /> w3t_Polls AS t2,<br /> w3t_Users AS t3<br /> WHERE t1.P_Number = t2.P_Number<br /> AND t1.P_Name = t2.P_Name<br /> AND t1.P_IP = t3.U_Number<br /> OR t1.P_IP = t3.U_Username That's for v6.3, but I think it should work in 6.2 also.
|
|
|
|
|
Joined: Apr 2003
Posts: 30
User
|
|
User
Joined: Apr 2003
Posts: 30 |
|
|
|
|
|
Joined: Apr 2003
Posts: 30
User
|
|
User
Joined: Apr 2003
Posts: 30 |
Question ... if I know the name of the poll, how do I edit that command to JUST pull for that poll only?
I have tried to edit with some commands, but I crashed the MYSQL server on one of my sites with an apparant bad command.
|
|
|
|
|
Joined: Sep 2003
Posts: 488
Code Monkey
|
|
Code Monkey
Joined: Sep 2003
Posts: 488 |
Yep, use this... SELECT t2.P_Name,t2.P_Title,t3.U_Number,t3.U_Username,t2.P_Option <br />FROM w3t_PollData AS t1, <br /> w3t_Polls AS t2, <br /> w3t_Users AS t3 <br />WHERE t2.P_Title = 'Poll Title Goes Here' <br />AND t1.P_Number = t2.P_Number <br />AND t1.P_Name = t2.P_Name <br />AND t1.P_IP = t3.U_Username <br />UNION <br />SELECT t2.P_Name,t2.P_Title,t3.U_Number,t3.U_Username,t2.P_Option <br />FROM w3t_PollData AS t1, <br /> w3t_Polls AS t2, <br /> w3t_Users AS t3 <br />WHERE t2.P_Title = 'Poll Title Goes Here' <br />AND t1.P_Number = t2.P_Number <br />AND t1.P_Name = t2.P_Name <br />AND t1.P_IP = t3.U_Number <br />ORDER BY t2.P_Option ASC There are 2 instances where you'll have to include your poll title as can be seen above  -------------------- NOTE: The w3t_PollData table is a li'l funky in that it allows both usernames *and* usernumbers to be inserted into the P_IP field, which puzzles me. I'm not sure what the criteria is for one being chosen over the other. I'll assume that there's gotta be a good reason for doing that, but I have no idea what it is at this point. If anyone knows I'd be interested in hearing.
|
|
|
|
|
Joined: Apr 2003
Posts: 30
User
|
|
User
Joined: Apr 2003
Posts: 30 |
BTW, I have 2 forums, same exact file structures, on 2 different servers. One it works fine using the above (first one). The second it kills the server. I checked with that server admin, and he said the following:
"it locks the table that the other queries are waiting on the table to unlock before they can show the board"
AND
"The problem with your query is you do not have a limit line to it so it takes a long time to send all that data back to the server. You should have a limit line to limit the number of rows returned since this query returns a lot of data"
What can I do to fix this?
|
|
|
|
|
Joined: Sep 2003
Posts: 488
Code Monkey
|
|
Code Monkey
Joined: Sep 2003
Posts: 488 |
Just use the 2nd one I provided (single poll results) If you have a large database for the 1st one, then correct, you'll have to limit the rows because there will be *a lot* of results. PS - I had to slightly modify the 1st one above (multiple poll results), use this query instead if wanting to view multiple polls... SELECT t2.P_Name,t2.P_Title,t3.U_Number,t3.U_Username,t2.P_Option <br />FROM w3t_PollData AS t1, <br /> w3t_Polls AS t2, <br /> w3t_Users AS t3 <br />WHERE t1.P_Number = t2.P_Number <br />AND t1.P_Name = t2.P_Name <br />AND t1.P_IP = t3.U_Username <br />UNION <br />SELECT t2.P_Name,t2.P_Title,t3.U_Number,t3.U_Username,t2.P_Option <br />FROM w3t_PollData AS t1, <br /> w3t_Polls AS t2, <br /> w3t_Users AS t3 <br />WHERE t1.P_Number = t2.P_Number <br />AND t1.P_Name = t2.P_Name <br />AND t1.P_IP = t3.U_Number <br />ORDER BY t2.P_Name ASC <br />LIMIT 0,100 LIMIT 0, is the starting position of the record. 100, the number of rows returned from the sql query. So you can do chunks at a time basically. It definitely shouldn't cause any problems now
|
|
|
|
|
Joined: Sep 2003
Posts: 488
Code Monkey
|
|
Code Monkey
Joined: Sep 2003
Posts: 488 |
Here's a mod I complete for it btw... https://www.ubbdev.com/forum/showflat...SID=#Post119188Whenever you, as an admin, view poll results for any given poll, you'll automatically see the votes made by registered users without having to manually do a query.
|
|
|
|
|
Joined: Apr 2003
Posts: 30
User
|
|
User
Joined: Apr 2003
Posts: 30 |
I modified what you gave me initially to this: SELECT t2.P_Name,t2.P_Title,t3.U_Number,t3.U_Username,t2.P_Option <br />FROM w3t_PollData AS t1, <br />w3t_Polls AS t2, <br />w3t_Users AS t3 <br />WHERE t1.P_Number = t2.P_Number <br />AND t1.P_Name = t2.P_Name <br />AND t2.P_Name LIKE '%1093221487%' <br />AND t1.P_IP = t3.U_Number <br />OR t1.P_IP = t3.U_Username I knew the poll code for what I was looking for (started with 1093221487). The MYSQL kept dieing. After a bit the server admin emailed and asked what the heck I was doing. I showed him this, and he said to do this instead: SELECT t2.P_Name,t2.P_Title,t3.U_Number,t3.U_Username,t2.P_Option <br />FROM w3t_PollData AS t1, <br />w3t_Polls AS t2, <br />w3t_Users AS t3 <br />WHERE t1.P_Number = t2.P_Number <br />AND t1.P_Name = t2.P_Name <br />AND t2.P_Name LIKE '%1093221487%' <br />AND (t1.P_IP = t3.U_Number OR t1.P_IP = t3.U_Username); Which seems to work no problem. He did also mention this: "you can speed this query up by reducing size of the polls and pollsdata table" Being that I think I already peeved him off because I was killing the server, what does that mean and what can I do? Thanks again for the help.
|
|
|
Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.
Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
|
|
|
Posts: 87
Joined: December 2001
|
|
|
Forums63
Topics37,573
Posts293,925
Members13,849
| |
Most Online5,166
Sep 15th, 2019
|
|
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
|
|
|
|
