wickning1

It's possible that you forgot to add the correct user privileges in MySQL. See [a href=\"http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/use...management.html[/a]

I'd just like to add: Kneel before Zod!

This should do the trick. I'm not clear on whether the keyword search should be a substring search or not, so I used LIKE. This query would go even faster if you could change it to = instead of LIKE. Here's the query: [code]SELECT customer_nr FROM customer c LEFT JOIN (     SELECT customer_nr FROM customer_category_keyword     INNER JOIN (         SELECT keyword_nr FROM category_keyword         WHERE keyword_word LIKE "%searchStr%"     ) cats USING(keyword_nr)     GROUP BY customer_nr ) keys USING (customer_nr) WHERE c.customer_name LIKE "%searchStr%" OR keys.customer_nr IS NOT NULL ORDER BY c.customer_name[/code] Please note that this is still going to be slow when you have a lot of customers. Maintaining your own custom index would be the best solution. Edit: I just corrected an error. Edit: Made another improvement.

I'll work on it, check back in a bit.

Keep in mind that, due to the use of LIKE, this query will always be forced to scan through a great number of rows, and will be very slow as the database grows. You may want to look into substring indexing strategies. You can also do it in three steps... find all the keywords that match first, then all the customer names that match, then synthesize. That will be quite a bit faster than the single query JOIN.

Unless magic quotes is turned on, you probably have a ' in the text that is screwing up the query. Use echo get_magic_quotes_gpc() to find out if magic quotes is turned on for your script.

For question 2 - You want a list of all parts, and how many have been sold. SalesOrderLine is the table that lists parts and quantities, so you want to join the parts table with that one. Since you want all parts regardless of whether any have been sold, that means you want a LEFT JOIN. You want to join parts with line items having the same part number, that goes after ON. So far we have: [code]SELECT * FROM part p LEFT JOIN SalesOrderLine l ON l.part_no=p.part_number[/code] Now, you want to have MySQL sum up the quantities and values for each part number, so you want to use GROUP BY and some group functions like SUM: [code]SELECT p.*, SUM(l.Qty_Ordered) as cnt, SUM(l.Qty_Ordered * p.PricePerUnit) as valu FROM part p LEFT JOIN SalesOrderLine l ON l.part_no=p.part_number GROUP BY p.part_number[/code] Finally, you are told to return 20 characters of the description and the part number and sort the list, so we'll clean up our return values a bit more and add the sort command: [code]SELECT p.part_number, MID(p.description, 0, 20) as descr, SUM(l.Qty_Ordered) as cnt, SUM(l.Qty_Ordered * p.PricePerUnit) as valu FROM part p LEFT JOIN SalesOrderLine l ON l.part_no=p.part_number GROUP BY p.part_number ORDER BY descr[/code] That should do it.

I gave you a function, but you have to call it for it to work. //send the mail if (checkEmail($_POST['email'])) mail($recipient, $subject, $msg, $mailheaders); else echo "Haha you're an attacker, no goodies for you!"; //redirect page

Validate $_POST['email'] to make sure it is a single, valid email address (instead of an injection attack, probably containing thousands of email addresses). This isn't the greatest around but it will work for your purpose. You can look around for a better solution. [code]function  checkEmail($email) { if (!preg_match("/^( [a-zA-Z0-9] )+( [a-zA-Z0-9\._-] )*@( [a-zA-Z0-9_-] )+( [a-zA-Z0-9\._-] +)+$/" , $email)) {   return false; } return true; } [/code]

Always nice to help someone who's stumped but at least has a clue. So many posters just want you to teach them programming 101, especially in the PHP Help forum. The site looks pretty nice, good luck with it.

It looks like you're selecting b.* twice, which is unnecessary, but other than that it looks good.

What you're looking at doing actually involves a few GROUPings, so it should be subquery based. Also, you're going to need independent dates for both clicks and votes, or else one of them is going to be counted back forever. If you're doing what I think you're doing, it should end up looking something like this. [code]SELECT a.*, b.avg_score, b.total_votes, c.clicks, d.* FROM illustrators_table a LEFT JOIN users d ON a.user_id = d.user_id LEFT JOIN (     SELECT user_id, AVG(score) as avg_score, COUNT(*) as total_votes FROM score_table WHERE score_date > curdate() - INTERVAL 1 MONTH GROUP BY user_id ) b ON a.user_id=b.user_id LEFT JOIN (     SELECT user_id, COUNT(*) as clicks FROM external_url  WHERE click_date > curdate() - INTERVAL 1 MONTH GROUP BY user_id ) c ON a.user_id=c.user_id WHERE visible="t" AND s_verified="t" ORDER BY a.user_id DESC[/code] fenway - the CAST is only done once. DATE_SUB(curdate(), INTERVAL 1 MONTH) is a constant. Once it gets calculated, it can be used to do an index lookup.

I notice that you named it str_date, are you storing it as a string type (varchar, tinytext, etc), or a date type (DATETIME, TIMESTAMP, etc)? If you're storing it as a string, the query as written will not be able to use the index on str_date. It will want to convert str_date to a date type and then compare it with the DATE_SUB. To restore indexing, you need to force it to stringify the DATE_SUB into a format that matches your str_date format. My example assumes your format is YYYYMMDD. Also, the CONCAT functions appear to be superfluous and could slow things down a little. I removed them. [code]SELECT a.*, round(AVG(b.score),1) AS avg, count(distinct b.judge) AS total_votes, count(distinct c.ip_address) AS clicks, d.* FROM illustrators_table a LEFT JOIN score_table b ON a.user_id = b.user_id LEFT JOIN external_url c ON a.user_id = c.user_id LEFT JOIN users d ON a.user_id = d.user_id WHERE visible ="t" && s_verified ="t" && str_date > CAST(DATE_SUB(curdate(), INTERVAL 1 MONTH )+0 as CHAR) GROUP BY c.user_id ORDER BY a.user_id DESC[/code]

You shouldn't have any problems storing HTML in the database, although storing PHP code is slightly more involved (if you want it to be executed when it comes out). One of the nice things about storing articles in MySQL is you can use COMPRESS() and UNCOMPRESS() to transparently save space. Some MySQL installations may not have support for it though.

The comma is just another way to write an INNER JOIN. I find that LEFT JOIN helps me detect referential integrity problems in a lot of cases, because I get rows with obviously bad data, instead of the rows just disappearing into the abyss. But yeah it's not a good long term solution to simply cope with bad integrity. I just use LEFT JOIN any time I'm writing a query that I expect to return all the applicable rows from the left table (subject to the WHERE clause).

It's hard to reply without knowing a little more about your setup, but it sounds like a fairly simple join to do in one query. This will give you the equivalent of select * from orders but limited to orders with outstanding line items: [code]SELECT DISTINCT o.* FROM orders o, outstandinglineitems i WHERE o.orderno=i.orderno[/code]

The join version should work fine on that older MySQL. [code]SELECT DISTINCT r1.rRR FROM RRInventory r1, RRInventory r2 WHERE r1.rSite=r2.rSite AND r2.rIndex=720[/code]

[!--quoteo(post=347866:date=Feb 21 2006, 12:02 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 21 2006, 12:02 AM) [snapback]347866[/snapback][/div][div class=\'quotemain\'][!--quotec--] Change the word LEFT to INNER, and you'll have what you desired -- LEFT matches those rows which are found in the "left" table, and not the "right". You want to have a match in both. [/quote] Actually that's not quite correct, LEFT JOIN is just like an inner join except that it prints each row from the left table AT LEAST ONCE. For instance, given these tables: Reviews memID|review|gameID 1|blah|2 3|blah2|1 Users userID|memName 1|admin 2|akabugeyes [code]SELECT r.review, r.gameID, r.memID, u.memName, u.userID FROM reviews r LEFT JOIN users u ON r.memID=u.userID[/code] would return: blah|2|1|admin|1 blah2|1|3|NULL|NULL It's a good way to guarantee that you're seeing all the reviews even with referential integrity problems (or when the info in the right table is optional). BTW akabugeyes, that query I wrote will work for you. It will also work if you replace LEFT JOIN with INNER JOIN. All the other queries in this thread are incorrect or inconsistent.

[code]<?php // open a mysql database connection $result = mysql_query("SELECT * FROM myTable ORDER BY DATE DESC LIMIT 50", $dblink); while ($row = mysql_fetch_array($result)) {    $date[] = $row['date'];    $open[] = $row['open'];    $high[] = $row['high'];    $low[] = $row['low'];    $close[] = $row['close']; } mysql_free_result($result); ?>[/code]

I've written scripts that performed well over 3 million updates without any kind of pause. MySQL had no problem with them, and was serving up web pages to the normal users too. But I think the best way to solve your problem is with a stored procedure (if you're using MySQL 5): [code]CREATE PROCEDURE updateRatings BEGIN    DECLARE done, currid INT DEFAULT 0;    DECLARE rankCount INT DEFAULT 1;    DECLARE myRatings CURSOR FOR SELECT id FROM table ORDER BY rating;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;    OPEN myRatings;    REPEAT       FETCH myRatings INTO currid;       IF currid IS NOT NULL THEN          UPDATE table SET rank=rankCount WHERE id=currid;          rankCount = rankCount + 1;       END IF;    UNTIL done END REPEAT;    CLOSE myRatings; END[/code] If you're still on MySQL 4, just do the while loop on your end, it won't bring the server down.

That's not normal behavior even if you're overloaded. These are the repairs I would attempt, in order: Try dumping all the data into a backup file with mysqldump and then use it to create fresh tables with all the same data. Try dropping the whole database and recreating it from backup. Try reinstalling mysql. Try another hard drive. Try another server.

Are you familiar with the concept of session handling? If no, that's what you need to investigate. If yes, what about logging in has you hung up?

A well-written application should never take more than a second to run, unless you are doing some serious data crunching. Correct me if I'm wrong but it sounds like you are not indexing your database at all. If that's the case, you might want to do a little reading.

I don't know about that database package, but if you use mysqli you can use mysqli_multi_query() to send many statements in the same communication. I built support for it into my personal database package, and my tests do show a thin speed increase on a 100Mbps LAN. The best thing you can do to make your application scale to many users is to make sure NONE of your queries are forced to do a table scan. Not only do table scan queries eat up processor/drive resources, but they also lock the tables involved, so even if the machine has cycles to spare, your users are all locked up waiting on that table scan. Of course, use EXPLAIN to get information about how your query is executed by MySQL.

UPDATE products SET type="" WHERE id=1