Re: [PHP] Simplifying MySql queries
On 2/12/2011 12:40 PM, Andre Polykanine wrote:
Hi all,
I'm using in my PHP script the following four MySql queries:
$q1=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
`Category`='1' ORDER BY RAND() LIMIT 1");
$q2=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
`Category`='2' ORDER BY RAND() LIMIT 1");
$q3=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
`Category`='3' ORDER BY RAND() LIMIT 1");
$q4=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
`Category`='4' ORDER BY RAND() LIMIT 1");
and here goes the question: is there a way to make these four in one
so strictly one random question is selected from all of the four
categories?
Thanks!
I think you will need something like this:
SELECT *
FROM`CandidateQuestions`
WHERE `Category` IN (1,2,3,4)
GROUP BY `Category`
ORDER BY RAND()
LIMIT 4
Now, what I'm not sure about, nor have I tested, is will mysql do the
ORDER BY RAND() before or after the GROUP BY.
If that doesn't work, then I would try this instead:
SELECT DISTINCT `Category`,
FROM`CandidateQuestions`
WHERE `Category` IN (1,2,3,4)
ORDER BY RAND()
LIMIT 4
This will probably do the distinct after the ORDER BY RAND(), but again,
completely untested. YMMV
Give them a whirl and let us know how it works out.
Jim Lucas
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Simplifying MySql queries
Andre Polykanine wrote: and here goes the question: is there a way to make these four in one so strictly one random question is selected from all of the four categories? "SELECT * FROM `CandidateQuestions` WHERE `Category` IN(1,2,3,4) ORDER BY RAND() LIMIT 4" note the limit 4, you'll be needing that to get back 4 rather than 1 :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Simplifying MySql queries
On 13/02/2011, at 9:40 AM, Andre Polykanine wrote:
> Hi all,
> I'm using in my PHP script the following four MySql queries:
> $q1=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
> `Category`='1' ORDER BY RAND() LIMIT 1");
> $q2=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
> `Category`='2' ORDER BY RAND() LIMIT 1");
> $q3=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
> `Category`='3' ORDER BY RAND() LIMIT 1");
> $q4=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
> `Category`='4' ORDER BY RAND() LIMIT 1");
>
> and here goes the question: is there a way to make these four in one
> so strictly one random question is selected from all of the four
> categories?
> Thanks!
>
> --
> With best regards from Ukraine,
> Andre
> Skype: Francophile
> Twitter: http://twitter.com/m_elensule
> Facebook: http://facebook.com/menelion
mysql_query('SELECT * FROM `CandidateQuestions` WHERE `Category` IN (1,2,3,4)
ORDER BY RAND() LIMIT 1;'); should do it.
Though if there's no other possible values for Category, you could just remove
the WHERE clause.
---
Simon Welsh
Admin of http://simon.geek.nz/
Who said Microsoft never created a bug-free program? The blue screen never,
ever crashes!
http://www.thinkgeek.com/brain/gimme.cgi?wid=81d520e5e
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Simplifying MySql queries
On 02/12/2011 09:40 PM, Andre Polykanine wrote:
> Hi all,
> I'm using in my PHP script the following four MySql queries:
> $q1=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
> `Category`='1' ORDER BY RAND() LIMIT 1");
> $q2=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
> `Category`='2' ORDER BY RAND() LIMIT 1");
> $q3=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
> `Category`='3' ORDER BY RAND() LIMIT 1");
> $q4=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
> `Category`='4' ORDER BY RAND() LIMIT 1");
>
> and here goes the question: is there a way to make these four in one
> so strictly one random question is selected from all of the four
> categories?
> Thanks!
>
You really should ask this on a SQL mailing list. it doesn't have much
to do with PHP.
That said. You could use the IN() statement like this:
SELECT * FROM `CandidateQuestions` WHERE `Category` IN (1, 2, 3) ORDER
BY RAND() LIMIT 1;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Simplifying MySql queries
Hi all,
I'm using in my PHP script the following four MySql queries:
$q1=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
`Category`='1' ORDER BY RAND() LIMIT 1");
$q2=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
`Category`='2' ORDER BY RAND() LIMIT 1");
$q3=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
`Category`='3' ORDER BY RAND() LIMIT 1");
$q4=mysql_query("SELECT *FROM`CandidateQuestions`WHERE
`Category`='4' ORDER BY RAND() LIMIT 1");
and here goes the question: is there a way to make these four in one
so strictly one random question is selected from all of the four
categories?
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: using BOTH GET and POST in the same page.
Ashim Kapoor wrote: Dear All, I am reading "PHP5 and MySQL Bible". Chapter 7 of the book says that PHP can use GET and POST in the SAME page! Also it says that we can use the SAME variables in GET and POST variable sets and that conflict resolution is done by variable_order option in php.ini Can some one write a small program to illustrate the previous ideas? It is not clear to me as to how to implement this. I noticed you've already received one response, so here's some more background info. It's using $_GET and $_POST in the same script, not HTTP GET and HTTP POST. $_GET in PHP correlates to the query string parameters in the URL requested, $_POST in PHP correlates to form data which is POSTed to the server inside a message, with the type application/x-www-form-urlencoded. One could say that $_GET and $_POST are named misleadingly, and that infact what you have is $_PARSED_QUERY_STRING_FROM_URL and $_POST_DATA_MAYBE . The two are quite separate and can both be used at the same time. HTML forms allow a method to be set, GET or POST, if GET then the form is treated like an URL construction template, if POST then it's treated like a message body construction template. It's worth reading up on both HTTP and HTML Forms when using PHP, since PHP is a "Pre Hypertext Processor" and HTTP is the Hypertext transfer protocol, and HTML is the Hypertext markup language :) Best, Nathan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: using BOTH GET and POST in the same page.
Ashim Kapoor wrote: Dear All, I am reading "PHP5 and MySQL Bible". Chapter 7 of the book says that PHP can use GET and POST in the SAME page! Also it says that we can use the SAME variables in GET and POST variable sets and that conflict resolution is done by variable_order option in php.ini Can some one write a small program to illustrate the previous ideas? It is not clear to me as to how to implement this. I noticed you've already received one response, so here's some more background info. It's using $_GET and $_POST in the same script, not HTTP GET and HTTP POST. $_GET in PHP correlates to the query string parameters in the URL requested, $_POST in PHP correlates to form data which is POSTed to the server inside a message, with the type application/x-www-form-urlencoded. One could say that $_GET and $_POST are named misleadingly, and that infact what you have is $_PARSED_QUERY_STRING_FROM_URL and $_POST_DATA_MAYBE . The two are quite separate and can both be used at the same time. HTML forms allow a method to be set, GET or POST, if GET then the form is treated like an URL construction template, if POST then it's treated like a message body construction template. It's worth reading up on both HTTP and HTML Forms when using PHP, since PHP is a "Pre Hypertext Processor" and HTTP is the Hypertext transfer protocol, and HTML is the Hypertext markup language :) Best, Nathan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] using BOTH GET and POST in the same page.
On 2/11/2011 9:23 PM, Ashim Kapoor wrote: Dear All, I am reading "PHP5 and MySQL Bible". Chapter 7 of the book says that PHP can use GET and POST in the SAME page! Also it says that we can use the SAME variables in GET and POST variable sets and that conflict resolution is done by variable_order option in php.ini Can some one write a small program to illustrate the previous ideas? It is not clear to me as to how to implement this. Many thanks, Ashim. But basically, the short of it is this. Name: When submitted with data to this: Will result in this: Array ( [page_id] => 22 [action] => AddUser ) Array ( [action] => DelUser [FullName] => Jim Lucas ) Array ( [page_id] => 22 [action] => DelUser [FullName] => Jim Lucas ) Check out the example that I wrote here http://www.cmsws.com/?page_id=5 Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
