PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Oct 21, 2018 3:11 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
PostPosted: Tue Aug 30, 2011 12:45 am 
Offline
Forum Commoner

Joined: Fri Dec 03, 2010 11:24 pm
Posts: 30
I have some code that takes user input, like 'John 3:16', and puts 'John' to a variable, '3' to a variable and '16' to a variable. It then inserts values from one mysql table to another according to these three variables. If the input isn't spelled correctly, or if the verse doesn't exist in the Bible, or if that verse was already added, then I inform them.

Is there anyway to make this faster?

Syntax: [ Download ] [ Hide ]
$username = mysql_real_escape_string($_SESSION['username']);
if(isset($_POST['reference'])){
       
        $reference = $_POST['reference'];
        $non_letters = array(':', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '-');
        if (substr($reference, 0, 1) == '1' || substr($reference, 0, 1) == '2' || substr($reference, 0, 1) == '3') {$space_book = substr($reference, 0, 1);} else {$space_book = '';}
        $book_name = rtrim($space_book . str_replace($non_letters, '', $reference));
        $chapter_number = substr($reference, strlen($book_name) + 1, strpos($reference, ':') - strlen($book_name) - 1);
        if(strpos($reference, '-') !== false) {
                $first_verse = substr($reference, strpos($reference, ':') + 1, strpos($reference, '-') - strpos($reference, ':') - 1);
                $last_verse = substr($reference, strpos($reference, '-') + 1, strlen($reference) - strpos($reference, '-') - 1);
                for ($verse_number = $first_verse; $verse_number <= $last_verse; $verse_number++){
                        $get_verse = mysql_query("SELECT * FROM esv WHERE book = '$book_name' AND chapter = '$chapter_number' AND verse = '$verse_number'");
                        while($row = mysql_fetch_array($get_verse)){$esv_book = $row['book']; $esv_type = $row['type']; $esv_testament = $row['testament']; $esv_book_index = $row['book_index']; $esv_chapter = $row['chapter']; $esv_verse = $row['verse']; $esv_content = $row['content'];}
                        $duplicate = mysql_query("SELECT * FROM verses_$username WHERE book = '$book_name' AND chapter = '$chapter_number' AND verse = '$verse_number'");
                        if(mysql_num_rows($get_verse) != 0){
                                if(mysql_num_rows($duplicate) != 0){
                                        $added = 1;
                                        }
                                else{
                                        mysql_query("INSERT INTO verses_$username (testament, type, book, book_index, chapter, verse, content, date) VALUES ('$esv_testament', '$esv_type', '$esv_book', '$esv_book_index', '$esv_chapter', '$esv_verse', '$esv_content', now())");
                                        $stat = mysql_query("SELECT * FROM stats");
                                        while ($row = mysql_fetch_array($stat)) {$total_verses = $row['total_verses'] + 1;}
                                        mysql_query("UPDATE stats SET total_verses = $total_verses");
                                        }
                                }
                        else{
                                $not_a_verse = 1;
                                }
                        }
                }
        else{
                $verse_number = substr($reference, strpos($reference, ':') + 1, strlen($reference) - strpos($reference, ':') - 1);
               
                $get_verse = mysql_query("SELECT * FROM esv WHERE book = '$book_name' AND chapter = '$chapter_number' AND verse = '$verse_number'");
                while($row = mysql_fetch_array($get_verse)){$esv_book = $row['book']; $esv_type = $row['type']; $esv_testament = $row['testament']; $esv_book_index = $row['book_index']; $esv_chapter = $row['chapter']; $esv_verse = $row['verse']; $esv_content = $row['content'];}
                $duplicate = mysql_query("SELECT * FROM verses_$username WHERE book = '$book_name' AND chapter = '$chapter_number' AND verse = '$verse_number'");
                if(mysql_num_rows($get_verse) != 0){
                        if(mysql_num_rows($duplicate) != 0){
                                $added = 1;
                                }
                        else{
                                mysql_query("INSERT INTO verses_$username (testament, type, book, book_index, chapter, verse, content, date) VALUES ('$esv_testament', '$esv_type', '$esv_book', '$esv_book_index', '$esv_chapter', '$esv_verse', '$esv_content', now())");
                                $stat = mysql_query("SELECT * FROM stats");
                                while ($row = mysql_fetch_array($stat)) {$total_verses = $row['total_verses'] + 1;}
                                mysql_query("UPDATE stats SET total_verses = $total_verses");
                                }
                        }
                else{
                        $not_a_verse = 1;
                        }
                }
        }
if(isset($_GET['delete_verse'])) {
        $verse_id = $_GET['delete_verse'];
        mysql_query("DELETE FROM verses_$username WHERE id = $verse_id");
        $stat = mysql_query("SELECT * FROM stats");
        while ($row = mysql_fetch_array($stat)) {$total_verses = $row['total_verses'] - 1;}
        mysql_query("UPDATE stats SET total_verses = $total_verses");
        }


Top
 Profile  
 
PostPosted: Tue Aug 30, 2011 4:46 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4313
Location: Sofia, Bulgaria
Sounds like a regexp problem to me.E.g. for 'John 3:16' format you have:

Syntax: [ Download ] [ Hide ]
if (preg_match('#^([a-zA-Z -]+)\s(\d+):(\d+)$#', $reference, $matches))
{
   print_r($matches);
}


Also, I haven't read the code in depth, but it seems you can put most of the data search into the SQL code, not in the PHP code.

_________________
There are 10 types of people in this world, those who understand binary and those who don't


Top
 Profile  
 
PostPosted: Thu Sep 15, 2011 5:28 pm 
Offline
DevNet Resident
User avatar

Joined: Sun Sep 03, 2006 5:19 am
Posts: 1579
Location: Sofia, Bulgaria
Quote:
"SELECT * FROM verses_$username ...


You create a new table for each user?

No. Have one table (say verses) with all the verses for all the users (whatever that means in your application). This means, have a column user_id that references whose 'verse' that is. Create an index on that column, so that queries with " WHERE `user_id`=5" would run faster.

Also, mysql_real_escape_string() will not protect you against SQL injection in cases like this, where you are escaping something else than a value. Check the article in my signature for details if you want, but the situation is best remedied by severely refactoring the code not to use multiple tables like that.

Also, you have things like that:
Syntax: [ Download ] [ Hide ]
        $verse_id = $_GET['delete_verse'];
        mysql_query("DELETE FROM verses_$username WHERE id = $verse_id");


(I'm sure I've already directed you once at reading about SQL injection)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group