On Aug 6, 2009, at 7:52 PM, "Ron Piggott" <ron.php@xxxxxxxxxxxxxxxxxx>
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
