Re: mySQL UPDATE

On Thu, 9 Jun 2005, Ron Piggott wrote:

I created a PHP based mailing list that sends out a Bible verse and a quotation each day. Today's verses were: [snip]

Let me show you some code: It selects a Bible verse: SELECT * FROM bible ORDER BY RAND() LIMIT 1

On a side note, this is going to be expensive... there was a long thread on this just recently, but at the very least do: SELECT COUNT(*) FROM bible; $random_record = rand(0, $value_from_query); SELECT * FROM bible LIMIT 1 OFFSET $random_record;

Then it puts the text into variables: $bible_verse_ref=mysql_result($result,$i,"bible_verse_ref"); $bible_verse_text=mysql_result($result,$i,"bible_verse_text"); $bible_verse_translation=mysql_result($result,$i,"bible_verse_translation"); Then immediately following this is the UPDATE that didn't work: UPDATE bible SET current_verse_of_the_day = '1' WHERE bible_verse_ref LIKE '$bible_verse_ref' AND bible_verse_text LIKE '$bible_verse_text' AND bible_verse_translation LIKE '$bible_verse_translation' AND current_verse_of_the_day LIKE '0' LIMIT 1 Where there are no ' or " this piece of code works just fine. I am essentially retrieving a Bible verse from the table and then immediately searching for it to change the 'current_verse_of_the_day' to change from 0 to 1. I have manually made the change for today and the current_verse_of_the_day value was 0. Similar code is used for the quotation --- but there is no need for me to repeat it here. Any ideas how I am able to get the use of ' or " to work on this page?

http://us4.php.net/manual/en/function.mysql-real-escape-string.php Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used. mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.