php-mysql question about getting multiple verses

Question

ANSWER FOUND: I am reading all and deciding, THANK YOU SOO MUCH hahaha :D I <3 stackoverflow

I apologize for the vagueness of this question and the lack of research, but I really had no idea how to google this question.

I am passing a bible verse and bible chapter into a page that takes it and pulls the verse from the bible db likes this

?book=Genesis&chapter_number=1&verse_number=1

But what I want to be able to do is send in multiple verses separated by a "-"

like this:

?book=Genesis&chapter_number=1&verse_number=1-2

I don't really know how this would work. Any ideas?

Does it go any further than that? Like `number=1-2,5,8-10`? – Pekka Dec 06 '10 at 22:56 — Pekka, Dec 06 '10 at 22:56

GolezTrol · Accepted Answer · 2010-12-07T14:53:30.833

2

This works. It allows you to give a range of verses like 1,3,4-10. It'll return the whole chapter if no specific verses are given.

// Get the book and chapter
$book = isset($_GET['book'])?(string)$_GET['book']:'';
$chapter = isset($_GET['chapter'])?(string)$_GET['chapter']:'';

// Make sure you escape the string to prevent SQL injection
$book = mysql_real_escape_string($book);
$chapter = mysql_real_escape_string($chapter);

// Get the verses as a string. 
$verses = isset($_GET['verses'])?trim($_GET['verses']):'';

if ($verses ==='') {
    // If no verses are given, TRUE will trick the database 
    // into returning all verses for the given book.
    $verseWhere = 'TRUE';
} else {
    // Split it on the commas
    $verseRanges = explode(',', $verses);
    $verseConditions = array();

    // Split each value on '-', if any
    foreach($verseRanges as $verseRange) {
        $verseRange = explode('-', $verseRange);
        // Build a condition
        if (count($verseRange) === 1) {
            $verseConditions[] = "verse = ".(int)$verseRange[0];
        } else {
            $verseConditions[] = "verse BETWEEN ".(int)$verseRange[0]." AND ".(int)$verseRange[1];
        }
    }

    // Implode the array to build a list of conditions
    $verseWhere = "(".implode(' OR ', $verseConditions).")";
}

// Create the SQL statement
$query = "
    SELECT 
        * 
    FROM 
        Bible
    WHERE
        book = '$book' AND
        chapter = '$chapter' AND
        $verseWhere";

[edit] Made some minor changes, removed the typo's and actually run the script to test it. :)

edited Dec 07 '10 at 14:53

answered Dec 06 '10 at 23:13

GolezTrol

114,394
18
182
210

Type-hint much? Seriously, PHP has weak typing for a reason. Don't fight it! – Stephen Dec 06 '10 at 23:15
If I don't use type hinting, I would need to escape the values. Ints are always ints. Not much to escape there. :) Maybe I could loose the (strings) but I like strong typing as much as possible, I even used the === operator. ;) – GolezTrol Dec 06 '10 at 23:17
Read your documentation. According to the spec, `$_GET` variables are passed through `urldecode()`. `urldecode()` returns a string. So `$_GET` will *always* contain a string, already. (unless you muck with it.) http://php.net/manual/en/reserved.variables.get.php – Stephen Dec 06 '10 at 23:20
Unless you mock with it, indeed. Not much space to mock with in such a small example, but when writing large websites with a group of developers, you can never be sure. ;) It won't hurt, and it will hardly make your code less readable. When you read it, you can tell directly that you expect that value to be an int. That also means you can pass the values as an int to the MySQL query, which may be more efficient that passing strings. – GolezTrol Dec 07 '10 at 06:42
I fixed my example to remove the typos and include the book in the query. The first attempt was just a quick attempt to give a general idea and I wasn't able to fix and test it earlier (have done now). Idea is the same, though. Still think int casting is better than escaping if the actual value is (should be) an integer. All to do with semantics. Escaping values other than strings is actually a work-around. – GolezTrol Dec 07 '10 at 14:51

Ish · Answer 2 · 2010-12-06T23:20:06.023

0

Send request as

?book=Genesis&verse_number[]=1&verse_number[]=2&verse_number[]=3=10

Server will receive this as

$_GET = array(
    'book' => 'Genesis',
    'verse_number' => array(
          '1',
          '2',
          '3-10'
     )
)

$verses = array();
foreach($_GET['verse_number'] as $item) {
    $item = explode('-', $item);
    if(isset($item[1])) {
         for($i=(int)$item[0];$i<=(int)$item[1];$i++)
             $verses[] =  $item[$i];
    } else {
         $verses[] =  $item[0];
    }
}

edited Dec 06 '10 at 23:20

answered Dec 06 '10 at 23:12

Ish

28,486
11
60
77

This would look ugly, and hugely cumbersome when you have a lot of verses (e.g. 1-50) – Ben G Dec 06 '10 at 23:13

Stephen · Answer 3 · 2010-12-07T12:51:48.973

0

My better (imho) version of GolezTrol's script. Defaults to Genesis, chapter 1, verse 1 if no information is provided in the URL. Also fixed some syntax errors/misspellings in his script. Also, he forgot the book. ;) Finally, removed excess type casting, and used escaping where necessary:

$book        = empty($_GET['book']) ? 'Genesis' : $_GET['book'];
$chapter     = empty($_GET['chapter']) ? '1' : $_GET['chapter'];
$verses      = empty($_GET['verses']) ? '1' : $_GET['verses'];
$book        = mysql_real_escape_string($book);
$chapter     = mysql_real_escape_string($chapter);
$verseRanges = explode(',', $verses);
$vC          = array();

foreach($verseRanges as $verseRange) {
    $vR = explode('-', $verseRange);
    foreach ($vR as &$value) {
        $value = mysql_real_escape_string($value);
    }
    $vC[] = count($vR) === 1
          ? 'verse = ' . $vR[0]
          : 'verse BETWEEN ' . implode(' AND ', $vR);
}
$query  = "SELECT * FROM Bible WHERE chapter = '" . $chapter . "' ";
$query .= "AND book = '" . $book . "' ";
$query .= 'AND (' . implode(' OR ', $vC) . ')';

Using type casting in lieu of escaping makes your code less readable/understandable. Always code for readability in the future. Also, $_GET values are already strings.

edited Dec 07 '10 at 12:51

answered Dec 06 '10 at 23:43

Stephen

18,827
9
60
98

I did use escaping where necessary. Excessive type hinting is a matter of taste, and passing strings instead of integers is less efficient. Not so much better, iMho. :p – GolezTrol Dec 07 '10 at 06:45
Relying on an artifact or side effect of *one* feature (type casting) in order to avoid using a feature that was designed for the task (escaping) is gimmicky at best, and error prone at worst. Help me understand *why* you don't want to use escaping? – Stephen Dec 07 '10 at 12:48
Here's an example of type casting gone wrong. Imagine if the OP was taught to type cast user input, and then tried to code something with floats: http://stackoverflow.com/q/3385685/262056 – Stephen Dec 07 '10 at 13:04
That is totally not related to this example. – GolezTrol Dec 07 '10 at 14:50
Yes, but you are teaching the wrong-tool-for-the-job approach. That could *lead* to the above example. I'm done arguing, you don't have to agree with reason. – Stephen Dec 07 '10 at 15:22

php-mysql question about getting multiple verses

3 Answers3