Re: [PHP] WHERE problem
Mike Shanley wrote: I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); ARRAY??? // Ready to ship... Referring to it via an index... could be the problem $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
2007. 02. 20, kedd keltezéssel 08.17-kor Jim Lucas ezt írta: Mike Shanley wrote: I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); ARRAY??? what's wrong with that? http://hu.php.net/manual/en/function.mysql-fetch-array.php and then you can of course refer to it with indexes, both numeric and associative I don't see anything problematic with that... greets Zoltán Németh // Ready to ship... Referring to it via an index... could be the problem $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
Németh Zoltán wrote: 2007. 02. 20, kedd keltezéssel 08.17-kor Jim Lucas ezt írta: Mike Shanley wrote: I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); ARRAY??? what's wrong with that? http://hu.php.net/manual/en/function.mysql-fetch-array.php and then you can of course refer to it with indexes, both numeric and associative I don't see anything problematic with that... greets Zoltán Németh // Ready to ship... Referring to it via an index... could be the problem $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush nothing is wrong with it, just confusing -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
Németh Zoltán wrote: 2007. 02. 20, kedd keltezéssel 08.17-kor Jim Lucas ezt írta: Mike Shanley wrote: I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); ARRAY??? what's wrong with that? http://hu.php.net/manual/en/function.mysql-fetch-array.php and then you can of course refer to it with indexes, both numeric and associative I don't see anything problematic with that... greets Zoltán Németh // Ready to ship... Referring to it via an index... could be the problem $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush I would suggest using either assoc or row this way there is no confusion. Plus it doesn't take as much resources. :) -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
2007. 02. 20, kedd keltezéssel 08.28-kor Jim Lucas ezt írta: Németh Zoltán wrote: 2007. 02. 20, kedd keltezéssel 08.17-kor Jim Lucas ezt írta: Mike Shanley wrote: I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); ARRAY??? what's wrong with that? http://hu.php.net/manual/en/function.mysql-fetch-array.php and then you can of course refer to it with indexes, both numeric and associative I don't see anything problematic with that... greets Zoltán Németh // Ready to ship... Referring to it via an index... could be the problem $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush I would suggest using either assoc or row this way there is no confusion. Plus it doesn't take as much resources. :) ok, it's probably better to decide which to use in advance and then stick to that one... it's always better to plan carefully before coding, and use the optimal tools needed for the job. however, I think sometimes everyone starts coding without detailed plans ;) greets Zoltán Németh -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
Different strokes for different folks... Might I toss a new recommendation into the mix? SELECT text FROM fortunes ORDER BY RAND() LIMIT 1; = = = Original message = = = N~~meth Zolt~~n wrote: 2007. 02. 20, kedd keltez~~ssel 08.17-kor Jim Lucas ezt ~~rta: Mike Shanley wrote: I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); ARRAY??? what's wrong with that? http://hu.php.net/manual/en/function.mysql-fetch-array.php and then you can of course refer to it with indexes, both numeric and associative I don't see anything problematic with that... greets Zolt~~n N~~meth // Ready to ship... Referring to it via an index... could be the problem $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush I would suggest using either assoc or row this way there is no confusion. Plus it doesn't take as much resources. :) -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] WHERE problem
[snip] Different strokes for different folks... Might I toss a new recommendation into the mix? SELECT text FROM fortunes ORDER BY RAND() LIMIT 1; [/snip] I suggested that yesterday. :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
2007. 02. 20, kedd keltezéssel 11.39-kor [EMAIL PROTECTED] ezt írta: Different strokes for different folks... Might I toss a new recommendation into the mix? SELECT text FROM fortunes ORDER BY RAND() LIMIT 1; that's not new :) a couple of people recommended it earlier today/yesterday this is perfect, but only if the table is not very large. see http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ greets Zoltán Németh = = = Original message = = = N~~meth Zolt~~n wrote: 2007. 02. 20, kedd keltez~~ssel 08.17-kor Jim Lucas ezt ~~rta: Mike Shanley wrote: I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); ARRAY??? what's wrong with that? http://hu.php.net/manual/en/function.mysql-fetch-array.php and then you can of course refer to it with indexes, both numeric and associative I don't see anything problematic with that... greets Zolt~~n N~~meth // Ready to ship... Referring to it via an index... could be the problem $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush I would suggest using either assoc or row this way there is no confusion. Plus it doesn't take as much resources. :) -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] WHERE problem
Ah.. sorry Jay. I had like 8,000 emails today and must have missed some of the original responses. Or maybe I'm just trying to look smart by riding on your coat-tails. Either way, apologies for the repeated information. -TG = = = Original message = = = [snip] Different strokes for different folks... Might I toss a new recommendation into the mix? SELECT text FROM fortunes ORDER BY RAND() LIMIT 1; [/snip] I suggested that yesterday. :) ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
The reason I didn't go with the suggestions for this approach is that I want to accept new submissions to the database whose default id = 0. Thus, I can moderate them on the way in by giving them non-0 numbers. (I've changed the database row count call to fit the implementation since my posting of the code.) Thanks to everyone! [EMAIL PROTECTED] wrote: Different strokes for different folks... Might I toss a new recommendation into the mix? SELECT text FROM fortunes ORDER BY RAND() LIMIT 1; -- Mike Shanley ~you are almost there~ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
On Mon, February 19, 2007 2:32 pm, Bruce Cowin wrote: Mike Shanley [EMAIL PROTECTED] 20/02/2007 9:23:08 a.m. I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); Are you certain that your 'index' field runs from 1 to $max-1 and you will never DELETE a fortune leaving a hole in your 'index' values?... Unless you really really really need the quality of the Merseinne Twister random generator, you could just do: SELECT text FROM fortunes ORDER BY rand() LIMIT 1 (Or is it random() in MySQL? I always confuse mysql/pg random function name...) Also, 'text' is a field type in SQL, so you may need: SELECT `text` to make it not be reserved word. Ditto for index - `index` perhaps. // Ready to ship... $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); As a matter of Code Style, you MAY want to consider doing: $chosen = mysql_result($choose, 0, 0); instead of creating a 1-element array and then accessing element 0 of that array. Some developers prefer to always do their PHP/mysql the same Others prefer to make it clear when they are getting a singleton database result, by using a different pattern of code. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
On Tue, February 20, 2007 10:46 am, Németh Zoltán wrote: 2007. 02. 20, kedd keltezéssel 11.39-kor [EMAIL PROTECTED] ezt Ãrta: Different strokes for different folks... Might I toss a new recommendation into the mix? SELECT text FROM fortunes ORDER BY RAND() LIMIT 1; that's not new :) a couple of people recommended it earlier today/yesterday this is perfect, but only if the table is not very large. see http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ Another option for extremely large tables, is to ADD a field, say, 'random_cache' of type float, and index that field. You can then: SELECT id FROM whatever ORDER BY random_cache LIMIT $limit; Gather your id's together, and then: UPDATE whatever SET random_cache = random() WHERE id in ($ids) You'd have an index on id as well, of course. So, in essence, as you use up random rows, you re-assign those rows with a new random number, and toss them back in the pile It *does* re-shape the index on the random index, so if that gets too lop-sided and you are selecting a large $limit, the DB takesa beating, but this is still pretty efficient for what most people are trying to do most of the time. It puts the heavy lifting into a DB index, which is about as efficient as you're going to get. It is possible for two users to get the same random selection, if their queries inter-twine. You could wrap the whole thing in a transaction, possibly, if that's undesirable. I use this for a playlist of ~30 songs every day out of ~6 rows, and it works well. But I only do the queries once a day, and store the result, so maybe it won't scale well for heavily-trafficed site. I'd be interested in hearing anybody who benchmarks this compared to other methods, but confess I'm not in enough of a performance bind to feel the need to benchmark for myself. Though I know for sure it beat the ORDER BY random() on my usage, as that's what I had and it was killing me. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
WHERE id 0 would take care of that. Or, better yet, SEPARETE your 'id' field from your 'approved' field and don't try to be cheap with a one-byte (or even one-bit in some DBs) field. Over-loading the data field definition with dual meaning almost always turns into a problem down the line, in my experience. [Apologies if this has been said -- I'm having email threading issues at the moment...] On Tue, February 20, 2007 3:59 pm, Mike Shanley wrote: The reason I didn't go with the suggestions for this approach is that I want to accept new submissions to the database whose default id = 0. Thus, I can moderate them on the way in by giving them non-0 numbers. (I've changed the database row count call to fit the implementation since my posting of the code.) Thanks to everyone! [EMAIL PROTECTED] wrote: Different strokes for different folks... Might I toss a new recommendation into the mix? SELECT text FROM fortunes ORDER BY RAND() LIMIT 1; -- Mike Shanley ~you are almost there~ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
Are you getting an error or just nothing returned? The first thing I'd check is if index is a numeric field and if it is, remove the single quotes from around $randi in the where clause. Regards, Bruce Mike Shanley [EMAIL PROTECTED] 20/02/2007 9:23:08 a.m. I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); // Ready to ship... $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Mike Shanley ~you are almost there~ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
Without the single-quotes, I still get nothing returned. Bruce Cowin wrote: Are you getting an error or just nothing returned? The first thing I'd check is if index is a numeric field and if it is, remove the single quotes from around $randi in the where clause. Regards, Bruce Mike Shanley [EMAIL PROTECTED] 20/02/2007 9:23:08 a.m. I'd like to think I understood code a little better than this, but I've got a problem with my WHERE... I know it's the WHERE because I get a good result when I leave it out. And the random function is also working... I honestly can't figure it out. Thanks in advance for help with this laughable prob. --- // How many are there? $result = mysql_query(SELECT count(*) FROM fortunes); $max = mysql_result($result, 0); // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); // Ready to ship... $fortune = 'span class=quotecycquot;' . $chosen1[0] . 'quot;br/-Omniversalism.com/span'; mysql_close(); -- Mike Shanley ~you are almost there~ A new eye opens on March 5. -Omniversalism.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] WHERE problem
-Original Message- From: Mike Shanley [mailto:[EMAIL PROTECTED] Sent: Monday, February 19, 2007 3:50 PM Cc: php-general@lists.php.net Subject: Re: [PHP] WHERE problem Without the single-quotes, I still get nothing returned. Bruce Cowin wrote: Are you getting an error or just nothing returned? The first thing I'd check is if index is a numeric field and if it is, remove the single quotes from around $randi in the where clause. Two things that come to mind... 1) If there are 100 records in there, is the value of the 'index' column exactly 1-100? It won't do any good to give it a random value of 1-100 if your records are numbered 101-200 :P 2) INDEX is a mysql keyword. Try putting backticks around it. ... WHERE `index` = $randi If all else fails, remove the part of your code that generates the random 'index' and just use ORDER BY RAND() LIMIT 1 in your query. HTH, Brad -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] WHERE problem
[snip] // Get randomized!... the moderated way... $randi = mt_rand(1, $max-1); $q = SELECT text FROM fortunes WHERE index = '$randi'; $choose = mysql_query($q); $chosen1 = mysql_fetch_array($choose); [/snip] Put the random statement in the query SELECT foo FROM bar ORDER BY RAND() LIMIT 1 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] WHERE problem
This was the problem. Thanks very much! Brad Fuller wrote: 2) INDEX is a mysql keyword. Try putting backticks around it. ... WHERE `index` = $randi -- Mike Shanley ~you are almost there~ A new eye opens on March 5. -Omniversalism.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
