Re: ORDER BY RAND() not working
> $id = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL ORDER
> BY RAND() LIMIT 3",$link) or die(mysql_error());
PHP code:
$result = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL
ORDER BY RAND() LIMIT 3") or die(mysql_error());
The error message is as follows:
You have an error in your SQL syntax near 'RAND() LIMIT 3' at line 1
Jamie
Jamie Tibbetts.[EMAIL PROTECTED]
Epigroove .http://www.epigroove.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ORDER BY RAND() not working
I'm running PHP 4.1.2 and MySQL 3.23.39. I have a simple query that works if I telnet into MySQL and run the query manually. If I try and use it in a PHP page, I get the "Supplied argument is not a valid MySQL result resource" error. However, if I take out the ORDER BY RAND() part from the query, it works in PHP. Doesn't work: SELECT * FROM products WHERE sale_price IS NOT NULL ORDER BY RAND() LIMIT 3 Works: SELECT * FROM products WHERE sale_price IS NOT NULL LIMIT 3 Does anyone have any idea why this is happening? Thanks, Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help with a complex query
>>> Try - and I mean *try* :-) >>> >>> SELECT Customers.* FROM Customers, Subscriptions >>> LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) >>> LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && >>> (Ordered_Items.productid = '23')) >>> WHERE Customers.id=Subscriptions.customerid >>> AND Subscriptions.listid='2' >>> AND Ordered_Items.orderid IS NULL >>> AND Orders.customerid IS NULL >>> AND NOT Customers.bad_email; >> >> Well, MySQL has been chugging on this one for about 15 minutes now, and >> it's >> still going. So I'm not sure if it is a problem with the query or the >> optimization of my MySQL databases. There are 30,000+ records in the >> Customers database, but the query should have finished by now, right? >> > Well, my best guess is that you should consider indexing your tables... > If you haven't done so already, indexing will cause dramatic effect on > the speed of the queries... > > Peter Normann Okay. I've indexed all fields involved, and I've gotten the query time down to under 3 seconds! However, I've discovered a flaw in the query. A customer can order multiple times. The above query still includes customers that have ordered product #23, but have also ordered other products on separate orders. Example: Customers: Subscriptions: +--+---+ +++ | id | bad_email | | customerid | listid | +--+---+ +++ | 1001 | 0 | | 1001 | 2 | +--+---+ +++ Orders: Ordered_Items: +++ +-+---+ | id | customerid | | orderid | productid | +++ +-+---+ | 1 | 1001 | | 1 | 23| | 2 | 1001 | | 2 | 98| +++ +-+---+ Customer #1001 would be included in the found set of the query above. He would be removed because he ordered product #23 on order #1, but he would also be included because he didn't order product #23 on order #2. Any ideas on how to exclude these multi-order customers? Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help with a complex query
> Try - and I mean *try* :-) > > SELECT Customers.* FROM Customers, Subscriptions > LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) > LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && > (Ordered_Items.productid = '23')) > WHERE Customers.id=Subscriptions.customerid > AND Subscriptions.listid='2' > AND Ordered_Items.orderid IS NULL > AND Orders.customerid IS NULL > AND NOT Customers.bad_email; Well, MySQL has been chugging on this one for about 15 minutes now, and it's still going. So I'm not sure if it is a problem with the query or the optimization of my MySQL databases. There are 30,000+ records in the Customers database, but the query should have finished by now, right? Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need help with a complex query
I am currently a web designer who's taught himself the necessary basics of PHP and MySQL. I've reached a point where I can't figure out a complex query. I have a mailing list that customers can sign up for if they want to receive "Special Offer" mailings when products go on sale. So when it's time to send out a mailing on a sale product, I need to select all customers who have signed up for list #2. However, (and here's the big problem) I need to filter out the customers that have bad email addresses marked in their customer record, *and* I need to filter out the customers who have already purchased the sale product. Customers: Subscriptions: +--+---+ +++ | id | bad_email | | customerid | listid | +--+---+ +++ | 1001 | 1 | | 1001 | 2 | | 1002 | 0 | | 1002 | 1 | | 1003 | 0 | | 1003 | 2 | | 1004 | 0 | | 1004 | 2 | | 1005 | 0 | | 1005 | 2 | +--+---+ +++ Orders: Ordered_Items: +++ +-+---+ | id | customerid | | orderid | productid | +++ +-+---+ | 1 | 1001 | | 1 | 24| | 2 | 1002 | | 2 | 98| | 3 | 1003 | | 3 | 23| | 4 | 1004 | | 4 | 56| +++ +-+---+ For example: I want to send out a mailing on product #23 A) Customer 1001 has a bad email address, so he shouldn't be selected B) Customer 1002 has a good email address, but he's not signed up for list #2, so he shouldn't be selected C) Customer 1003 has a good email address, he's signed up for list #2, but he's ordered product #23 before, so he shouldn't be selected D) Customer 1004 has a good email address, he's signed up for list #2, and he's ordered a product that isn't #23, so he *should* be selected E) Customer 1005 has a good email address, he's signed up for list #2, and he's never ordered, so he *should* be selected Summary: > Not Selected: 1001,1002,1003 > Selected: 1004,1005 Can anyone create a query that will return the results I need. Keep in mind that I am far from being a MySQL expert, so if you start using too much technical specifics, my head might explode. ;) Thanks in advance! Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ Success .http://www.success.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
