wickning1

phpMyAdmin is a good general purpose frontend for MySQL. [a href=\"http://www.phpmyadmin.net\" target=\"_blank\"]http://www.phpmyadmin.net[/a]

Yeah, looks good to me. It just might get slow when your appointments table is really big. You may want to do the query once a day and store the answer, or something like that. Or go with the second column for just the date part - it's ugly but it should work. I really need to test whether MySQL is smart about this, it seems like a very common thing to GROUP on.

You can use IN instead of a lot of ORs. For instance, this: [code]WHERE product_id IN (3,7,14,18)[/code] is identical to this: [code]WHERE product_id=3 OR product_id=7 OR product_id=14 OR product_id=18[/code]

I think you're ok with two queries here. One to get the product details for all the products you want, another to get all the options for all those products. Then in PHP you go row by row and link them up. Just make sure you ORDER BY product_id and it shouldn't be too hard. The alternative is to use a nested loop, get one product, then query for its options. This will generate a lot of queries, which could eventually slow you down. But it should be easy to write.

Like I said, DATE(datestamp) = date in your tbl_prospects. So in your other table, you can do: SELECT DATE(datestamp) as date ... GROUP BY date I just don't know if it will use your datestamp index, so it may be a slow query.

[a href=\"http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/res...ermissions.html[/a]

The highest number of calls in a day was XX by XX (agent) on dd-mm-yyyy: [code]SELECT MAX(handlername) as agent, COUNT(*) as calls, date FROM tblprospects GROUP BY handlerID, date ORDER BY calls DESC LIMIT 1[/code] The highest number of appointments in a day was XX by XX (agent) on dd-mm-yyyy [code]SELECT MAX(handlername) as agent, COUNT(*) as appts, date FROM tblprospects WHERE appointment=1 GROUP BY handlerID, date ORDER BY calls DESC LIMIT 1[/code] Actually having both the date and datestamp may help MySQL to index these groupings. Grouping on a function like DATE(datestamp) might break indexing. I need to test this though. Make sure you create an index on (handlerID, date) to help optimize these. By that I mean a multiple column index, NOT two indexes! If you already have an index on handlerID, just add date to it.

It's hard to give you specifics, but MySQL has plenty of good date math abilities. For instance, queries that use something like "WHERE mydatestamp < NOW() - INTERVAL 15 MINUTE" are very useful. See here for more: [a href=\"http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.html[/a]

Tutorial. [a href=\"http://www.freewebmasterhelp.com/tutorials/phpmysql\" target=\"_blank\"]http://www.freewebmasterhelp.com/tutorials/phpmysql[/a]

Show me exactly the code you are using.

Using this table: [code]id date place status[/code] [code]...SELECT UNIX_TIMESTAMP(date) AS udate, place, status    FROM events ORDER BY date... while ($row=mysql_fetch_assoc($result)) {    $thismonth = date('F',$row['udate']);    $thisday = date('d',$row['udate']);    if ($month != $thismonth) {       $month = $thismonth;       echo "[$month]<br/>\n";    }    echo '['.$thisday.']['.$row['place'].']['.$row['status'].']'.    "<br/>\n"; }[/code]

DESCRIBE mytablename [a href=\"http://dev.mysql.com/doc/refman/5.0/en/describe.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/describe.html[/a]

Usually when you want to find the highest result(s), you do an ORDER BY and LIMIT. As jworisek pointed out, you'll probably need to combine that with a GROUP BY query in order to get the statistic you're looking for. We can help you out with details if you ask a specific question. Give us the table structure and a query you'd like to do.

This is really something you should find a tutorial for. Here's one: [a href=\"http://www.freewebmasterhelp.com/tutorials/phpmysql\" target=\"_blank\"]http://www.freewebmasterhelp.com/tutorials/phpmysql[/a]

Yes, they all speed up your queries. You can only have one primary key per table (hence "primary"). You can have as many UNIQUEs as you want. With both PRIMARY and UNIQUE, your INSERTs will fail if you try to insert a row that would break the uniqueness of the indexed column. In exchange, you can be confident that your data is consistent and those columns are never duplicated. Normal INDEXes just give you the speed boost without demanding uniqueness. You can have as many as you want (although they eat up space).

All of the empty string stuff will be automatic. The empty string is simply "". If a PHP variable is empty, it doesn't print anything. Many beginners do not realize that there is a difference between NULL and empty(""). An empty string is still a string, it just doesn't have any letters in it. NULL is a special value that is not a string at all! It is there so that if you want to, you can tell the difference between an empty column and a NULL column. For instance, many people use NULL on integer columns, so that they can have 0 be a valid value, and NULL if there is no value. NULL is not the same as zero. Overall you are better off ignoring NULL, making all your columns NOT NULL, and treat 0 and the empty string as if they were blank/false/not there/etc. You'll save yourself a few processor cycles and a lot of headaches.

Basically you just want to GROUP BY on the smallest number of rows that are necessary. Come up with a subquery that produces exactly the information you need, and no more. In your example, there is no need to sum up shipping data for orders you don't care about. Often, when you have something in your main WHERE clause, you'll want to place it inside the subquery as well. Not always, just when it makes sense to do it.

1) Default values will be the empty string, 0, or NULL, depending on your column type. You can specify a value for convenience, wherever it makes sense. 2) I strongly recommend that you do not create columns that support the NULL value. I always create NOT NULL columns. There is a speed advantage, and it usually prevents a lot of confusion. The empty string is not NULL, but serves the same purpose. The default will be NULL automatically if your column supports it, or the empty string (or 0 for numeric columns) if you use NOT NULL. 3) Yes

That should work, what is the problem you are having? The only suggestion I can make right now is to use the MySQL function NOW() instead of doing it in PHP like you are doing with '$now'.

Does the `month` column always match the month in the `date` column?

"Do you think its good practise to make a temporary table for the initial recordset and then run the sql on that after making it indexable.." No, just put all the conditions in the WHERE clause and link them with AND. MySQL is good at optimizing the exact method of retrieval.

No, not really. Usually you set up a cron job (linux) for that, but if you are renting part of a shared server you often don't have that ability. You can set up a special web page and go there yourself, or you can put some code in one of your frequently used pages, and have it check the time to make sure it only runs the update every so often. You might also consider just keeping the data. You can use MySQL's date math to ignore old data during page loads. Just add a "WHERE datecolumn > NOW() - INTERVAL 1 MONTH" to your queries, and put an index on datecolumn. There won't be a big performance hit this way. You may still want to go the delete route if the old data uses up an unusually large amount of space.

Ah, I understand. Yes, you often want to duplicate your WHERE condition inside subqueries to pare down the processing. I'm not able to glean a lot about your database structure, but assuming incoming_data has the order_id column, it would go like this: [code]SELECT sum(received) items_received, order_id, sum(received)-IF(ISNULL(sum(shipped)),0,sum(shipped)) as items_shipped from line_info N INNER JOIN incoming_data ID ON (ID.line_info_id=N.line_info_id) LEFT JOIN (    SELECT sum(s.pieces_shipped) as shipped, s.line_info_id    from shipping_data s    INNER JOIN incoming_data d ON d.line_info_id=s.line_info_id    WHERE d.order_id='$order_id'    GROUP BY s.line_info_id ) as SD2 ON (SD2.line_info_id=N.line_info_id) where order_id='$order_id' group by order_id[/code]

[!--quoteo(post=361298:date=Apr 3 2006, 01:40 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 3 2006, 01:40 PM) [snapback]361298[/snapback][/div][div class=\'quotemain\'][!--quotec--] I always get explode and implode mixed up -- I much prefer Perl's join/split names. [/quote] Yeah, I wrote Perl for about 7 years, PHP for going on 1, I find myself typing join() instead of implode() all the time. It just makes more sense.

If you need to do a lot of translation, you may have to read the CSV in PHP and create an SQL query on the fly. You can use the PHP functions file() and explode() to easily parse a CSV file.. You'll have to export to CSV in Excel. Example: [code]foreach(file('mycsvfile.csv') as $line) {    $row = explode(',', $line);    $datayouwant = $row[5];    $moredatayouwant = $row[7];    ... INSERT ... ($datayouwant, $moredatayouwant) ... }[/code]