Major Database Challenge

Here is the code on pastebin https://pastebin.com/gaqJjPdQ

Is there anyone that can help with this issue? I would be greatly appreciated for your time.

Moving this to the advanced section…

Is the data stored in the same table as WordPress?

I’d suggest you start here: Function Reference/wpdb Class

Yes it is stored on the same database. I have done the $wpdb before for simple sql’s… it is just this is a little complex for me. I need to incorporate the foreach loop that loops out the days of the week and then the query loops out classes for those days. example of the loop….
Monday
class one
class two
Tuesday
class One
class two
Wednesday
No classes
Thursday
class one
class two
Friday
class One
class two
Saturday
No classes
Sunday
class one

Something like so…it also prints out class type and teacher for the first loop in the header for the table. Anyway…as it is it works and works well just need to do it the wordpress way.

no one?

guess I need to ask for help one more time. If someone needs me to explain sql more then please ask. I just need a nudge with this one. I need to change the day of the week for every loop through the query to check if that day has any classes.

Is this still outstanding or have you got some help?

Is it just the SQL that you need as you say you have used $wpdb before? If it is more than the SQL can you also tell me some basics, such as any themes involved, any amendments to standard WP code etc. that might affect a solution – if I can find one that is.

Thanks for the reply. I have it working with the $wpdb adjustment, but not sure I have it in the most streamlined way possible. Here is what I have so far and it works https://wordpress.pastebin.com/76cRLKga
I had to use the $wpdb->prefix for the sql as that seemed to work so I just went with it.
Basically I have a loop inside a loop. The parent loop runs thru an array of the days of the week and passes the days into the sql and then the second loop displays the results. I am checking if there are any classes for each day. I am sure there is a better way to run thru the days of the week with %s like the original sql before the $wpdb , but not sure how to do this with the new version of my sql statement with the $wpdb. Any help with this would be great. I am so close.

Hi,

So far you seem to have done really well, the loop through a loop is OK though others may have different ideas. Can I confirm what happens about dates? You seem to be listing every class on a Tuesday with start and end times so this Tuesday and next Tuesday would be shown – is this right?

Is the wp-prefix in your config file ‘wp_’ or ‘yoga’? The wp_prefix merely sticks the wp-config setting in front of the table names, so I am not sure if wp->prefix.bp_class_schedule is actually wp_bp_class_schedule or yogabp_class_schedule, as the sql suggests, within the databse.

You should still be able to run, or copy, the original SQL, it might just need padding with the wp-query function. By the way you can use an alias on table names, so

yogabp_class_schedule couild be aliased to, for example, ycs by changing line 21 on https://wordpress.pastebin.com/76cRLKga to
yogabp_class_schedule ycs

then line 6. “.$wpdb->prefix.”bp_class_schedule.class_id,
can become ycs.classid as you have aliased the full table name including the wp-prefix. This will compact the code to something like (not tested!):-

$results = $wpdb->get_results(“SELECT ycs.class_id, ycs.group_id, ycs.title, ycs.day, ycs.start_time, ycs.end_time, ycs.member_list, ycs.class_type, ycs.class_description, ycs.teacher, ycs.teacher_link, u.user_url, u.ID, bxd.value
FROM yogabp_class_schedule ycs, users u, bp_xprofile_data bxd

Same alias can be used with the joins statements.

This might give you a more compact sql statement.

It might be possible to do a SELECT within a SELECT – that is run a SELECT to get the day code then run a select on that result to get the information, but it is still two parses of the data per record.

Can you show me what your current output is and an example of what you want? If you want to send me data direct I will let you have my email.

If you have access to the DB could you let me have a copy of the table design? Some data inside would be useful, but *not* private or confidential information, just some test data.

I always find the last 10% of any problem is the hardest to solve, but hang on in there, it will sort out!

Can I confirm what happens about dates? You seem to be listing every class on a Tuesday with start and end times so this Tuesday and next Tuesday would be shown – is this right?

Thanks so much for the response. Great advice on the aliases and I will play with that at some point. I am not sure what you mean by the above quote?
This line here…
?><tr><th><?php echo $Day ?></th><th><?php if ($Day == 'Monday'){echo 'Yoga Class';}?></th><th><?php if ($Day == 'Monday'){echo 'Teacher';}?></th><th></th><th></th><th></th></tr><?php

this line creates the header for the table of results and the results start on Monday looping and ends on Sunday. The header of the first loop is designed to show Monday Yoga Class Teacher across the top of the table with the classes for Mondays showing below. The next loop and for the rest of the loops till Sunday only show the day of the week and does not need to show “Yoga Class” and “Teacher” in the headings for that day as the main table now holds those heading values for the rest of the week. Very clean look and works slick.
I think I will run it as is for awhile before I make any more changes and see if the overall design is what I want. As I am new to all this I wanted to make sure a loop inside a loop like such was ok. As long as it is ok for some then I will leave it and have it as a side project to streamline it as time goes.
As for the prefix…yes it is yoga and I made a mistake on setup and did not add the underscore…still should be fine. And thanks for pointing out line 21 as I missed that wpdb-> prefix.
Thanks for the help…you rock. I bookmarked your website for future whatevers. Thanks again.

One other question?
what is the best way with WP to prevent double form submission…meaning if the user hits submit more then once? not sure if WP already has a hidden function for this. Thanks

Sorry for the delay in replying, out of the office for a while.

My question about dates was based on the assumption that there could be several weeks with different yoga classes. For example Moday 01/01/11 (UK dates) might have an 11.00am class, but Monday 07/01/11 might have a 3.00pm class. Would your listing show a class at 11.00am and 3.00pm for Mondays?

However I think what you are doing is showing what classea are available during a week – Monday to Sunday and they are the same each week. Do you see what I mean?

Loops within loops in this kind of database isn’t really an issue in my view, as there is not a huge amount of data to iterate through, some of the SQL databases I am contracted to manage run into 22 million plus records, and is opened and run several times a second. That is when optimisation becomes an issue!

As to the form buttons, I am not sure what WP does to be honest, it depends on if this is run as a plugin, such as contact7 or your own code on a page. As I understand it doubles can happen if you are using javascript to run the submit, but nothing shows a hidden function exists, as I say it depends on the structure of the form. I have always tried to forward the user onto a success page as soon as the background PHP completes using the ‘header:’ in the code. This way any subsequent clicking on a button is wiped as the page refreshes.

Glad to be of help and let me know if there are any other problems.

Thanks again for the reply. As for right now I will have static times that the user can enter. I will have option boxes for special times such as holidays and such or to announce a schedule change. I am trying to keep the system basic for now, but I am also extracting all the details about the group to show in an lightbox overlay for every class in the loop.
Thanks again for the help and I might tap into your knowledge at some point for tips on including actual dates into the system.
Thanks.
David

Hi all,
I want to provide access to the CMS but prevent users from; updating, move to Trash and/or deleting (pages, posts or comments). Any ideas?

Thanks in advance
David