PHP JavaScript Help

[reverend boltron]

Question: How would I use JS and PHP together to dynamically repopulate my select boxes?

Background info:
I'm still a major newbie using PHP and JavaScript and pretty much all scripting languages. I have a semi strong C++ background and I know BASIC as well, so everything isn't absolutely foreign to me.

Right now, I have my website built and performing pretty decent. I want to switch over from HTML to PHP. I've recently discovered how cool this language is, and I want to start using it to it's potential.

I have select boxes that are chained, but they're chained using JavaScript, compliments of a nice JS library that I found. Originally I had a list of bible verses that I have highlighted in my bible display when you select the verse. The boxes automatically repopulate based on what book you're in, and then from what chapter you're in.

What I want to do is add functionality for different users. If I have several different people give me a list of their verses, I want to be able to select a user, and have the books, chapters, and verses repopulate from there.

What I want to cut down on though, is the amount of initial loading time. I have over 1,300 different verses in my life, and I'm sure that the other people will have over 500 as well.

The page uses PHP and MySQL to fetch the verses from a database and display the result on a page (thanks W3Schools!), so that doesn't have an impact on the original loading time.

Downloading a huge JS file can take a long time, so I wanted to use JS and PHP together to repopulate my select boxes. If someone chooses a user, then a JS function will send that off to the server, which goes through a PHP switch statement, and sends back the HTML/JS for that specific user for the box population.

I set it up one way using MySQL but it would take about 30 seconds to fetch the info and send it back, that isn't an acceptable amount of time. I would expect at most two to two and a half seconds.

Here is the link to the JS file that works with the PHP file getverse.js

Here is my code thus far...

 

[reverend boltron]

Man, that looks awful (the code).

Here are the links, I renamed the extensions so they would display..

user page
optionlist.php

 

[reverend boltron]

I am thinking of using AJAX with an XML file to load the options of the select boxes for population. Does anybody know if this route is faster and more efficient route than MySQL? I'm using W3Schools as my backbone for this operation. If anybody has any suggestions, I'm listening.

 

[Atheus]

OK I'm not sure I fully understand you - could you link me to the actual site so I can see what you mean?

I set it up one way using MySQL but it would take about 30 seconds to fetch the info and send it back, that isn't an acceptable amount of time. I would expect at most two to two and a half seconds.

It should *not* take that long to fetch from a database, something is going badly wrong, but since I'm not sure what you're trying to do I can't really help... are you making hundreds of requests at once or something?

I am thinking of using AJAX with an XML file to load the options of the select boxes for population. Does anybody know if this route is faster and more efficient route than MySQL? I'm using W3Schools as my backbone for this operation. If anybody has any suggestions, I'm listening.

AJAX is no faster or slower than form submission, it's just different, and SQL is more efficient than XML files for large amounts of data. You're storing the whole bible?

 

[reverend boltron]

Hey, thanks for the reply.

The website is voxverum and the page that I'm currently using is page in question.

That page isn't set up with the user option box, but here is what I want it to look like.. experimental

I know that it is stripped down, and that the boxes don't populate. But what I want to happen is when you click on a user, it will populate the other boxes accordingly, by fetching the info for the select boxes.. I don't know if that helps at all. When i have more time, I will go in depth more.

 

[Atheus]

Woah man, did you write that yourself? All those VerseSelect.forText statements? That must have taken absolutely *forever* ....

Am I right in assuming you don't have the whole bible anywhere on the server? You just manually put in the verses you want? I can't really tell...

Anyway here's what I'd do:

First get the whole bible into an SQL database. This undoubtably exists out there somewhere, most likely for free, but I'm not a Christian so I wouldn't know where.

I'd then remove all the VerseSelect.forText stuff and use AJAX + SQL to populate the boxes one at a time. For example, if I select Genesis, an AJAX request would be made to the server with arguements like ?function=selectChapter&book=Genesis. This would tell the backend PHP to use a function 'selectChapter', which would make a request to the SQL database like this - SELECT ChapterNumber FROM Chapter WHERE ParentBook='Genesis' - and put it into an XML string for return to the client.

That would give you all the chapter numbers from Genesis, however many there are, and you would use the responseXML variable in your callback function to populate the select box. Same thing would happen for selecting a chapter and getting the verse numbers, and same again for selecting a verse number and getting the text - SELECT Text FROM Verse WHERE VerseNum=x AND ParentChapter=y AND etc etc, however the database is layed out.

Now you want to limit the verses shown for a particular user right? So you make a new table called UserVerse (or something) with 2 coulumns - User and Verse. Then you just modify the earlier select statements to take account of that table, something like - SELECT V.Text FROM Verse V, UserVerse UV WHERE V.VerseNum=x AND UV.Verse=x AND UV.User='bob'...

That was way longer than I anticipated when I started writing it...

...I hope it was helpful!

 

[reverend boltron]

Hey, thanks for your input. I don't have a lot to update about right now. I'm still actually trying to get the whole bible in the MySQL database. I'm finding out that the web host I have isn't the greatest... it is dot5hosting. I signed up with them for dirt cheap though, $23.90 for two years. So I can't complain, but I'll just have to switch hosts when my contract is up. Anyway, phpMyAdmin is giving me troubles putting the data in, so I wrote a php file to do it for me. I'm running into troubles with the single quotes that are in the verses. I'm googling it now to see how to get around that problem, since I doubt it is unique to me.

Anywho, I didn't write out all of the VerseSelect.forText statements, I actually had to do very little actual work. I set up a nice little Excel spreadsheet to do all of the work for me. Originally I had the verses point to the appropriate URL on biblegateway.com, and I had used Excel to do that for me as well. All I had to do was go through my bible and harvest all of the underlined verses. Then I used a windows macro program (AutoHotKey is awesome by the way) to do all of the work to get the actual verse for me.

I created a new table called 'bible', which will store all of the verses for the bible. The table has two fields, 'code' and 'verse'. Code is a 8 digit number, the first two digits are for the book, the next three are for the chapter, and the three after that are for the verse. The reason is there are 66 books in the bible, the max number of chapters any book has is 150, and the max number of verses in a book is 176.

Then I created a table called 'users', which has a name field and a verselist field.

I will probably add three more fields in the users table. I am thinking about adding booknum, chapternum, and versenum. Then I'll just split up the 8 digit number for the user, and concatenate it back (book+chap+verse) for the search in the database.

I am not too great at xml, xpath, xquery, etc, so I will read up on that a bit more if I'm going to use XML. The verses are actually formatted in HTML with CSS styles already, so I don't know if that is why you were suggesting XML or if I am missing something about how it relates to AJAX.

Thanks a lot for your help so far!

 

[Atheus]

Originally posted by: reverend boltron
I created a new table called 'bible', which will store all of the verses for the bible. The table has two fields, 'code' and 'verse'. Code is a 8 digit number, the first two digits are for the book, the next three are for the chapter, and the three after that are for the verse. The reason is there are 66 books in the bible, the max number of chapters any book has is 150, and the max number of verses in a book is 176.

I don't think that's the best format for your application - you want to be able to easily select all the chapter numbers in a book to populate the select boxes right? So in this case you'd have to use a LIKE statement (slow) to pick the right bits out of the code, something like SELECT Code FROM Bible WHERE Code LIKE '01%%%%%%'; to select all the verses in Genesis, then parse through all the hundreds of those to find the chapter numbers.

You'll have to have a seperate table that tells you how many chapters in any book, how many verses in any chapter, etc, to populate the boxes, and *then* select from the main verse table to get the actual text.

I am not too great at xml, xpath, xquery, etc, so I will read up on that a bit more if I'm going to use XML. The verses are actually formatted in HTML with CSS styles already, so I don't know if that is why you were suggesting XML or if I am missing something about how it relates to AJAX.

Well with an 'AJAX' request you have two options for your return data; responseText or responseXML. With responseText you would format the data server-side using comma seperated values and quotes and stuff, then manaully sort through them on the client. With responseXML you can parse it with existing functions.

 

[reverend boltron]

Okay, well, I finally got the whole bible in the MySQL database the way I wanted it. I thought I was going to have trouble because of the single quotes, but it ended up working out fine after I found out about stripslashes.

The way my database is setup for the bible table is, it has two fields, the 8 digit reference for the unique verse and the field for the html of the verse.
Then I have another table called users. The users field has seven fields. The first field for the user name, then a field for the book number, the chapter number and the verse number, then I have three more fields, one for option select value for the book, chapter, and verse..

so it would look something along the lines of

name, book, chapter, verse, bookh, chapterh, verseh

(*bookh = HTML for book, chapterh = HTML for chapter, verseh= HTML for verse)

Titus, 01, 004, 007, < option value ="01">Genesis</option>, <option value="004">4</option>, <option value="01004007">7</option>

That is for genesis 4:7.
That way I have all of the information included in the values of the options to get the next field. And option value has the unique 8 digit number to look up in the bible table. That cuts off the "like" statement. I think I can get rid of the verse field though, because thinking it through, I don't think I will really need it. Though I might keep it in there for statistical use, as in counting the verses that people have highlighted that are in common with each other.

So if I wanted to get the books for the user Titus I was thinking of doing something along the lines of: "select distinct bookh from users where name='Titus';" I don't know if that is the best way to get all of the books, but if there is a better, faster way, please let me know.

So, I don't know if I need the autopopulating select boxes anymore. They were good while they lasted. They are very fast, and maybe there is a way I can still use them and the php as well. If I can integrate them both at the same time, I think I can cut down the amount of requests sent to the MySQL database.

I'm going to look into seeing if I can write out the data to a file on the client side and flagging the requests for the book, chapter, and verse select boxes. Maybe that is a nice compromise. Maybe I should just focus on getting this part to work, and then expand once I have this done.

Anyway, I'll let you know how this goes. I really appreciate your help so far though, you've definitely given me a lot of great advice and let me discover what I needed to discover on my own, which is one of the best ways of learning. Much appreciated 😉

 

[reverend boltron]

Okay, it's all done. Now all I need is to get a set of verses from other people.

Here is the new one: New

Compared to the old one: old

The old one is faster, and it doesn't run into box population errors, but it is limited to only one user. I might redo the database and still use the drop down library that I was using before. Then I would just load in the data that I had from before, i.e. VerseSelect.forText.... but do that per user. Maybe that will take just as long... I'm still going to look into the other options that I have though. The more I look around the more I'll know what I can do.

Thanks for all of your help Atheus, seriously.