Re: Code optimization

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