On Aug 6, 2009, at 7:52 PM, "Ron Piggott" <ron....@actsministries.org> wrote:

Is there a way to optimize this with better mySQL query?

# Select today's Bible verse

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM verse_of_the_day_Bible_verses WHERE `assigned_date` = '0000-00-00' AND seasonal_use = $verse_application ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

# Checking to see if each verse has been used once and
# if so reset the used column and select a fresh Bible verse

if ( $num == 0 ) {

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="UPDATE `verse_of_the_day_Bible_verses` SET `assigned_date` = '0000-00-00' WHERE `assigned_date` <> '0000-00-00' AND `seasonal_use` = $verse_application";
$result=mysql_query($query);
mysql_close();

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM verse_of_the_day_Bible_verses WHERE `assigned_date` = '0000-00-00' AND seasonal_use = $verse_application ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

}

$bible_verse_ref=mysql_result($result,0,"bible_verse_ref");
$bible_verse_text=mysql_result($result,0,"bible_verse_text");
$bible_verse_translation=mysql_result($result, 0,"bible_verse_translation");
$bible_record=mysql_result($result,0,"record");
$bible_store_catalog_reference=mysql_result($result, 0,"store_catalog_reference"); $bible_teaching_devotional_messages_reference=mysql_result($result, 0,"store_teaching_devotional_messages_reference");

# Now make the web page show today's Bible verse
# and mark this Bible verse used so it won't be randomly selected from the database until each verse has been used

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="UPDATE `verse_of_the_day_Bible_verses` SET `assigned_date` = '$todays_date' WHERE `record` LIKE '$bible_record'";
$result=mysql_query($query);
mysql_close();


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Doing the update based in a primary key for the table would be faster that the various bits of the where clause

Bastien

Sent from my iPod

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to