Jump to content

steve_large

New Members
  • Content Count

    7
  • Joined

  • Last visited

Community Reputation

0 Neutral

About steve_large

  • Rank
    Newbie
  1. Hi Barry, Thanks that worked well! Not what I would have thought that I should have used. Nice, now I can use this as a template for any of the other tournament jobs that usually arise three or four times a year. Cheers! Steve
  2. Hi Barry, I was just thinking today what if there were numbers inside the question_id range that I didn't want to include in my list? If you use "BETWEEN" in the query it includes everything between the two numbers and at the end of the PHP code will print everything out. What if I have a range of say 46-51 but I only want to print out 46,47, and 51? Steve
  3. Hi Barand, Wow! Thank you very much for your efforts. Really didn't expect to get such elegant coding back from anyone. Usually when you ask for answers on a lot of sites you are still taking a stab in the dark for the most part. Have spent at least a week over the Christmas holidays working on this one. Your coding is just what I am looking for. I tried your code on the live database and it worked great! I noticed when I cut and pasted that some odd characters came over from the blog window which caused some strange errors. I am using NetBeans IDE as my editor and it says everything looks good now. Just added the connection string and away it went - awesome! Sort of like having a late Christmas present. This really needs to be cited as a good approach for this sort of thing because you would think that it is a common issue for many people and the examples on the Net are very obscure, especially to someone like myself that has a limited knowledge of MySQL and PHP. Please check out the application of your code at this address: http://taekwondobc.com/winterfest/ Will now work on trying to get rid of those annoying extra commas that the plugin keeps appending at the end of the events on every row. All the best and a very Happy New Year! Steve
  4. Hi Barand, Thanks for the quick reply. There are two tables. The answer table and the attendee table. The answer table is quite simple with only a few columns - "registration_id", "question_id", and answer. The attendee table has many columns but the one that binds it to the answer table is "id" as it has the same values as answer.registration_id. Here is the schema for both tables: CREATE TABLE `wp_lvsz_evr_answer` ( `registration_id` int(11) NOT NULL DEFAULT '0', `question_id` int(11) NOT NULL DEFAULT '0', `answer` text NOT NULL, PRIMARY KEY (`registration_id`,`question_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `wp_lvsz_evr_attendee` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `registration_id` varchar(23) DEFAULT '0', `is_primary` tinyint(1) DEFAULT '0', `lname` varchar(45) DEFAULT NULL, `fname` varchar(45) DEFAULT NULL, `address` varchar(45) DEFAULT NULL, `address2` varchar(45) DEFAULT NULL, `city` varchar(45) DEFAULT NULL, `state` varchar(45) DEFAULT NULL, `zip` varchar(45) DEFAULT NULL, `reg_type` varchar(45) DEFAULT NULL, `country_id` varchar(128) DEFAULT NULL, `organization_name` varchar(50) DEFAULT NULL, `vat_number` varchar(20) DEFAULT NULL, `email` varchar(85) DEFAULT NULL, `phone` varchar(45) DEFAULT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `price_option` varchar(100) DEFAULT NULL, `orig_price` decimal(20,2) DEFAULT '0.00', `final_price` decimal(20,2) DEFAULT '0.00', `quantity` varchar(45) DEFAULT NULL, `attendees` mediumtext, `tickets` mediumtext, `total_cost` decimal(20,2) DEFAULT '0.00', `amount_pd` varchar(45) DEFAULT NULL, `coupon_code` varchar(45) DEFAULT NULL, `payment` varchar(45) DEFAULT NULL, `payment_status` varchar(45) DEFAULT NULL, `txn_type` varchar(45) DEFAULT NULL, `txn_id` varchar(45) DEFAULT NULL, `payment_date` varchar(30) DEFAULT NULL, `event_id` varchar(45) DEFAULT NULL, `event_time` varchar(15) DEFAULT NULL, `end_time` varchar(15) DEFAULT NULL, `start_date` varchar(45) DEFAULT NULL, `end_date` varchar(45) DEFAULT NULL, `attendee_session` varchar(250) DEFAULT NULL, `transaction_details` text, `pre_approve` int(11) DEFAULT '1', `checked_in` int(1) DEFAULT '0', `checked_in_quantity` int(11) DEFAULT '0', `hashSalt` varchar(250) DEFAULT NULL, `num_people` varchar(45) DEFAULT NULL, `tax` varchar(45) DEFAULT NULL, `company` varchar(45) DEFAULT NULL, `co_address` varchar(45) DEFAULT NULL, `co_city` varchar(45) DEFAULT NULL, `co_state` varchar(45) DEFAULT NULL, `co_zip` varchar(45) DEFAULT NULL, `token` varchar(32) NOT NULL DEFAULT '0', `coupon` varchar(45) DEFAULT NULL, `waiver_agree` varchar(4) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `registration_id` (`registration_id`), KEY `event_id` (`event_id`) ) ENGINE=InnoDB AUTO_INCREMENT=459 DEFAULT CHARSET=utf8 AUTO_INCREMENT=459 ; Hope this helps to clear things up. Not my table design by the way. A freeware plugin from Wordpress. Finished output should look like -> fullname | belt | events | school . All output for "belt" , "event", and "school" is derived from the "answer" table. "fullname" is derived from the 'attendee' table. Thanks! Steve
  5. Greetings Everyone, This is my first post on this site and I am a bit rusty when it comes to MySQL. Just got a project going for a website where there is a list required for participants once they have registered. The information I require is on two separate tables in the database. The main problem I have is that the information is all in one column and needs to be split into several different columns. Sounds familiar right? Just can't seem to get the code right. When I create the layout in PHP the output is stepped because it handles my aggregate query (created in PHP) as four different queries. I have tested each individual query and they work fine for that particular item. To make a long story short, how can I create a single query that will work with this? Tried a failed attempt at sub queries. Please see below: SELECT (SELECT concat( fname, ' ', left( lname, 1 ) , '.' ) AS fullname FROM wp_lvsz_evr_attendee WHERE event_id =9 ), ( SELECT answer AS belt FROM wp_lvsz_evr_answer, wp_lvsz_evr_attendee WHERE wp_lvsz_evr_attendee.id = wp_lvsz_evr_answer.registration_id AND wp_lvsz_evr_attendee.event_id =9 AND question_id =42 ), ( SELECT answer AS event FROM wp_lvsz_evr_answer, wp_lvsz_evr_attendee WHERE wp_lvsz_evr_attendee.id = wp_lvsz_evr_answer.registration_id AND wp_lvsz_evr_attendee.event_id =9 AND question_id =43 ), ( SELECT answer AS school FROM wp_lvsz_evr_answer, wp_lvsz_evr_attendee WHERE wp_lvsz_evr_attendee.id = wp_lvsz_evr_answer.registration_id AND wp_lvsz_evr_attendee.event_id =9 AND question_id =41 Keeping the data as a reference but as you can see my criteria is based on event_id and question_id. They all work individually but not collectively. Any help you could offer on this would be greatly appreciated as it is the last step in being able to finish my project. Thanks in advance, Steve
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.