Thanks for asking! My solution was a bit unorthodox. Let me attempt to explain--
I could not figure out what was causing the update to stop while the TEXT field was set to null(for the purpose of skipping that field during an INSERT). All other fields that were set to null for the same purpose, did in fact allow the update --except the one where I was concatenating --as illustrated in my post earlier.
I came up with a work around; whereas, instead of setting the field to null, I set it to none-null. And during the insert, I created a hidden field in the originating form with the same name as the one in the db. The value of the hidden field has a single character. Since this field in db is only for updating, during the first update the inserted character will be written over.
Now this work-around addressed the issue. It allowed for INSERTING (while ignoring certain fields, including BLOBs) and UPDATING to the same table. The reason for which the initial suggestion did not work, eluded me; though, I spent nearly an entire day debugging.
Thanks, I don't expect the user to enter anything but names and numbers, so I guess no harm with filtering.
But that doesn't seem to work anyway, I still get XSS upon entering a script in the keyword field, tried str_replace() as well, and it doesn't work, this is confusing!
Don't sanitize verything. If the field is EMAIL sanitize it as EMAIL filter. You have to know the type expected! For your case $keyword= filter_var($keyword, FILTER_SANITIZE_SPECIAL_CHARS, FILTER_FLAG_STRIP_LOW); seems to be the right. If you put into my sql don't forget to escape it using mysql_real_escape_string()
There are two inserts table have columns: col1, col2, col3
--Inserts into all columns, this kind of query should have values equal to number of columns
INSERT INTO table_name("Blah", "bla2", "blah3")
--Insert any number, the other columns should have default defined for safety
INSERT INTO table_name(col1, col2) VALUES("Blah", "bla2")
In Login: 1. Check if login info are sent if not redirect back to login page with ambiguous error message 2. If logged in check for credentials with ambiguous error message 3. If logged in redirect to members page
--
-- Table structure for table `Bible`
--
CREATE TABLE IF NOT EXISTS `Bible` (
`Book` int(11) NOT NULL,
`Chapter` int(11) NOT NULL,
`Verse` int(11) NOT NULL,
`Scripture` int(11) NOT NULL,
PRIMARY KEY (`Book`,`Chapter`,`Verse`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Table structure for table `Scriptures`
--
CREATE TABLE IF NOT EXISTS `Scriptures` (
`Scripture` int(11) NOT NULL AUTO_INCREMENT,
`ScriptureText` text CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`Scripture`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
In this schema, Bible.Scripture is a Foreign Key to Scriptures.Scripture. I'm assuming Book, Chapter and Verse all join to other tables in your database already.
The primary key becomes a composite primary key of the columns Book, Chapter and Verse. So it doesn't matter if Chapter and Verse are not unique as long as the three columns combined are unique. This prevents inserting any duplicate records accidentally, which using just an auto-incrementing primary key does not.
Splitting it out into two tables is probably overkill considering you have a hard limit to how much data will actually be contained in the table, but it is an reasonable optimization for MyISAM tables. As per my last post the end result is the bible table can now be sorted in memory instead of on disk. You can read about the difference between fixed with and dynamic row formats here: http://dev.mysql.com/doc/refman/5.0/en/static-format.html http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html
My suggestion was regarding the structure of your table. The three columns Book, Chapter and Verse together make a perfect primary key eliminating the need to create a surrogate primary key (ID in your table).
My other suggestion, would be to split the actual scripture text out into a second table and join it to the Bible table. Reason being, mysql will use a dynamic row format when it encounters columns with a variable length, like TEXT or BLOB, so sort operations have to occur on disk instead of within memory. This is exponentially slower. Since your sort operations would most likely be against book, chapter or verse columns, this would drastically speed up your queries.
thanks MS, so what schema are you suggesting? Remember Book is Unique but Chapters and verses are repeatable!
Just a general observation, feel free to ignore the following.
In your table you're using a surrogate primary key, the auto-incrementing column. When you clearly have a natural and practical composite primary key in (book, chapter, verse) as they are always provided and the combination of three digits may not ever repeat.
The glaring issue I see with using a surrogate key in this scenario is that a record can be mistakenly added with duplicate information.
Apart from what I have said above, there is another way if you if you do not have the ID's in sequence. An autoincrement would usually put the ID's in sequence as long as you have added the data in the proper order.
But if you have not, here is another solution. Create a new column in the table and give it a name say `id_ord`.
You will have to create formula so that the book 1, Chapter 2 and Verse 4 are integrated. e.g. id_ord = Book * 100000 + Chapter * 1000 + Verse Store the above value in the 'id_ord' in the DB. Using this, you can create the values from your PHP code for the from and the to id_orders when a request is given. for e.g. A user asks for scriptures between book 1 chapter 1 verse 1 and book 2 chapter 3 Verse 1.
$idOrd_from = 1*100000+1*1000+1 = 101001 and
$idOrd_to = 2*100000+3*1000+1 = 203001
You should now be able to pull the scriptures between these 2 id_ord
To add this into your DB use the following query UPDATE bible SET id_ord=100000*book+1000*chapter+verse; Hope this helps.
Can you do 1 query to get the IDS (say, Genesis 1:1 = ID 1, Psalms 2:30 = ID 4562), then ask for anything between IDs 1 and 4562? I think that's what ~sudeep was thinking.
As long as the verses were entered in order, it should work.
Otherwise, I think you'll have to do a book-by-book query.
Are your ID's in the order as per the sequence of the Books, chapters and verses in the bible? If so, then you can pull the ID for from where you want to start and the ID till where you want to get the Scripture till and then pull all the scriptures in between the 2 ID's.
sudeep, id is autoincrement and I don't programatically know where new book or chapter begins at as far as ID is concerned. Thanks for replying
Hi, I know this is not PHP issue but we here writes a lot of database SQL querries so I hope for a help. I have a bible script that is supposed to query the scriptures between two intervals. My table is named Bible and have columns: ID (int), Book (int), Chapter(int), Verse (int) and Scripture(text).
Now the books are unique ie 1-66 but chapters keep repeating, and so do verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 and book 2 chapter 3 Verse 1, how do I go about? My knowledge of SQL have taken me to a dead end!
You can check how bible have its chapters and verses arranged at www.biblegateway.com.
A general solution is to use a class library such as Qt4 available from http://qt.nokia.com/. Here you'll find general classes for accessing all major RDBMS's (Oracle, MySQl, SQlite etc...). Qt is great...
Specifically for Oracle, a more difficult, but high performance method is the OCI or OCCI API available from oracles website for free.
The problem is, I have learned great deal of wxWidgets and I don't plan to learn QT for now. I have invested a lot of time in learning wx. May be when only alternative is QT ;)
thanx for all those information.. but I am only into open source technologies. and my programming language is limited in php. So is it impossible to make such a project using PHP??
I would suggest you check desktop application! Though it might be possible, it would we tough task. Right tool for the right job. Some toolkit have mobile version which abstracts you from much of pain
can't you just install any of those packages (the one you prefer) and then reverse-engineer the db design since that is all you want. You should be able to generate a diagram using MySQL workbench: http://dev.mysql.com/downloads/workbench/
I have already tried that long ago and it got me some tables but relationships are lost. Did I miss something?
Hi, I have a complex C++ code with gui. Each major gui division is independent class. Now these classes must 'talk' sometimes in operations like copy from division A and paste in division B. So I need to pass some pointers from one class to another. I was thinking of using friend methods but I see that I have to pass object each time I call the function which complicates stuffs. I thought then of static function but then, my pointers will have to be static and I see it will complicate stuffs. I decided to ask if anyone have met this challenge of inter class communication and how to resolve it! Thanks a lot
thanks again Freddy. AFAIK, Odbc is not part of C++ standard so it is neither in Mingw or Linux GCC. So where do I download headers/sources to compile? Or even ready .a and .h? Thanks
I was finding Database schema for CMS that are already there (Drupal, Joomla et al) but not yet succeeded. I would be happy if anybody points me to it. I want to adopt for my uses, droping this and adding that! Thanks
