SupaMonkey

Hey guys, First, my schema (just the parts pertinent to the question): CREATE TABLE `credit_usages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `client_id` int(10) unsigned DEFAULT NULL, `client_user_id` int(10) unsigned DEFAULT NULL, `credits_used` int(11) DEFAULT NULL, `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `usage_value` decimal(9,2) DEFAULT NULL, `used` datetime DEFAULT NULL, `invoiced` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), KEY `client_id` (`client_id`), KEY `client_user_id` (`client_user_id`) ); CREATE TABLE `invoice_products` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `invoice_id` int(10) unsigned DEFAULT NULL, `invoice_product` varchar(255) DEFAULT NULL, `sell_price` decimal(10,2) DEFAULT NULL, `cost_price` decimal(10,2) DEFAULT NULL, `taxed` tinyint(1) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `invoices` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `client_id` int(10) unsigned DEFAULT NULL, `client` varchar(255) DEFAULT NULL, `invoiced` date DEFAULT NULL, `postal_address` text, `invoice` int(10) unsigned DEFAULT NULL, `emailed` tinyint(1) DEFAULT NULL, `tax_rate` decimal(10,2) DEFAULT NULL, `tax` decimal(10,2) DEFAULT NULL, `subtotal` decimal(10,2) DEFAULT NULL, `total` decimal(10,2) DEFAULT NULL, `notes` text, PRIMARY KEY (`id`) ); So heres my scenario, I have system where every time someone views something on my site, they get charged a credit. The system inserts a row in the 'credit_usages' table with all the relevant details of that credit usage with its 'invoiced' field set to 0 by default. Once a month I plan to run a cron that takes all the uninvoiced "credit usages" and charge them to a client on a single invoice. Problem comes in that clients are constantly adding items to the 'credit_usages' table so I dont know how to handle this without for example setting credit_usages invoiced='1' for something that hasnt been invoiced. Let me elaborate: 1) my cron runs 2) select all credit_usages for all clients where invoiced = 0 'grouped by client' 3) inserts invoice and invoice_product with relevant details from (2) 4) updates credit_usages to invoiced=1 where invoiced=0 Problem: Inbetween (2) and (4) clients 'consume' more credits and so (2) doesnt have the new credits, yet on (4) these will be updated to '1'... Your thoughts? My first impulse is to make 'invoiced' a tinyint instead of boolean and before (2) update invoiced to '2' for example where invoiced='0', then i only invoice what is set to '2' and only set invoiced=1 where invoiced =2. Dont know if this is the best way forward - only had 2 hours of sleep last night - so some non-zombie thought patterns would be appreciated

Something so simple yet I cant get an easy way around this one. I have two tables: CREATE TABLE `doctor_tests` ( `id` int(10) unsigned NOT NULL auto_increment, `doctor_id` int(10) unsigned default NULL, `test_id` int(10) unsigned default NULL, `answer_1` char(1) default NULL, `answer_2` char(1) default NULL, `answer_3` char(1) default NULL, `answer_4` char(1) default NULL, `answer_5` char(1) default NULL, `answer_6` char(1) default NULL, `answer_7` char(1) default NULL, `answer_8` char(1) default NULL, `answer_9` char(1) default NULL, `answer_10` char(1) default NULL, `answer_11` char(1) default NULL, `answer_12` char(1) default NULL, `answer_13` char(1) default NULL, `answer_14` char(1) default NULL, `answer_15` char(1) default NULL, `answer_16` char(1) default NULL, `answer_17` char(1) default NULL, `answer_18` char(1) default NULL, `answer_19` char(1) default NULL, `answer_20` char(1) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tests` ( `id` int(10) unsigned NOT NULL auto_increment, `test` varchar(64) default NULL, `released` date default NULL, `expires` date default NULL, `type` varchar(3) default NULL, `question_1` text, `answer_1` char(1) default NULL, `question_2` text, `answer_2` char(1) default NULL, `question_3` text, `answer_3` char(1) default NULL, `question_4` text, `answer_4` char(1) default NULL, `question_5` text, `answer_5` char(1) default NULL, `question_6` text, `answer_6` char(1) default NULL, `question_7` text, `answer_7` char(1) default NULL, `question_8` text, `answer_8` char(1) default NULL, `question_9` text, `answer_9` char(1) default NULL, `question_10` text, `answer_10` char(1) default NULL, `question_11` text, `answer_11` char(1) default NULL, `question_12` text, `answer_12` char(1) default NULL, `question_13` text, `answer_13` char(1) default NULL, `question_14` text, `answer_14` char(1) default NULL, `question_15` text, `answer_15` char(1) default NULL, `question_16` text, `answer_16` char(1) default NULL, `question_17` text, `answer_17` char(1) default NULL, `question_18` text, `answer_18` char(1) default NULL, `question_19` text, `answer_19` char(1) default NULL, `question_20` text, `answer_20` char(1) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; I want to compare doctor_tests to tests to see if a doctor got 80% or more for a test - whats the easiest/cleanest way using mysql or php & mysql? Can it be done with a single SQL statement ?

Unknown column 'outstanding' (because its a column alias)...

Hey Guys, So here's the thing, I need to draw up an outstanding amount based on 5 tables information: credit_notes: credit data - credit_note_products invoices: debit data - invoice_products payments: credit data So this is what I have: SELECT (SELECT ROUND(SUM(((sell_price*quantity)-((sell_price*quantity)*(discount/100)))*1.14),2) FROM invoice_products INNER JOIN invoices ON (invoices.id=invoice_products.invoice_id) WHERE invoice_id=invoices.id and customer_id=127) AS outstanding, (SELECT SUM(credit_amount*quantity) FROM credit_note_products INNER JOIN credit_notes ON (credit_notes.id=credit_note_products.credit_note_id) WHERE customer_id=127) AS credit_notes, (SELECT amount FROM customer_payments WHERE customer_id=127) AS payments, outstanding-credit_notes-payments AS balance_brought_forward Query works fine if I leave out the last line above. As soon as I try and subtract the fields from each other- thats where the problem lies...

Thanks fenway, Will do it like that from now on

Thanks fenway Whats the difference/implications/etc?

Thanks Whats improper about how I've done it? Please correct me Here is the single update SQL statement thats updates the 'last_occurrence' to have the 'next_occurrence' value if anyones interested: UPDATE recurring_invoice_products SET last_occurrence = CASE WHEN recurrence_type=1 THEN (last_occurrence + INTERVAL recurrence DAY) WHEN recurrence_type=2 THEN (last_occurrence + INTERVAL recurrence MONTH) ELSE (last_occurrence + INTERVAL recurrence YEAR) END WHERE end > NOW() AND (CASE WHEN recurrence_type=1 THEN (last_occurrence + INTERVAL recurrence DAY) WHEN recurrence_type=2 THEN (last_occurrence + INTERVAL recurrence MONTH) ELSE (last_occurrence + INTERVAL recurrence YEAR) END) <= CURDATE()

This is the solution I came up with; thanks for leading me in the right direction SELECT start, end, recurring_invoice_products.id as recurring_invoice_product_id, product,last_occurrence, CASE WHEN recurrence_type=1 THEN (last_occurrence + INTERVAL recurrence DAY) WHEN recurrence_type=2 THEN (last_occurrence + INTERVAL recurrence MONTH) ELSE (last_occurrence + INTERVAL recurrence YEAR) END as next_occurrence FROM recurring_invoice_products,products,product_types WHERE product_types.id=products.product_type_id AND recurring_invoice_products.product_id=products.id AND end > NOW() HAVING next_occurrence <= CURDATE() ORDER BY product

In a single query I basically would like to return all the rows in 'recurring_invoice_products' which their last_occurrence value + the value of recurrence (based on the recurrence type) = today. For example, lets say I have two records in recurring_invoice_products: +------------+------------+----------------+------------+-----------------+ | start | end | last_occurence | recurrence | recurrence_type | +------------+------------+----------------+------------+-----------------+ | 2007-01-03 | 2999-01-01 | 2007-10-01 | 1 | 1 | | 2007-01-04 | 2999-01-01 | 2007-10-01 | 1 | 2 | +------------+------------+----------------+------------+-----------------+ If you look at the first record, it has a recurrence of 1 and a recurrence type of 1 - this means each day this item should get charged. So basically, in pseudo code, the SQL statement would be: SELECT * FROM recurring_invoices WHERE last_occurrence + INTERVAL recurrence (as value: 1,2,3 - etc) recurrence_type (as type, 1=day,2=month,3=year) = CURDATE()

Hi Guys, Was hoping I could get some help with a query I am struggling with. Basically I have a table that stores recurring services which clients have. [b]Table structure:[/b] CREATE TABLE `recurring_invoice_products` ( `id` int(10) unsigned NOT NULL auto_increment, `product_id` int(10) unsigned default NULL, `sell_price` decimal(9,2) default NULL, `quantity` int(10) unsigned default NULL, `details` text, `username` varchar(64) default NULL, `client_id` int(11) default NULL, `recurrence` smallint(6) default NULL, `recurrence_type` tinyint(4) default NULL, `last_occurrence` date default NULL, `start` date default NULL, `end` date default NULL, PRIMARY KEY (`id`) ); Now for the key explanations: - start : start date of the service - end : end date of the service - recurrence_type: type of recurrence, 1 = days, 2 = months, 3 = years - recurrence: this is the value of how many units of recurrence_type will pass, eg: 15 recurrence with a recurrence_type of 1 is 15 days - last_occurrence: last time that this service 'occurred' (ie: was charged) I already have a PHP function which can find out if it time to charge the client again or not, but I was wondering if could take it one step further and not even have MySQL return unnecessary rows. [b]PHP Function:[/b] function next_occurrence($recurrence,$recurrence_type,$from_date) { if ($recurrence_type == 1) return date('Y-m-d',mktime(0,0,0,date('m',strtotime($from_date)),date('d',strtotime($from_date))+$recurrence,date('Y',strtotime($from_date)))); elseif ($recurrence_type == 2) return date('Y-m-d',mktime(0,0,0,date('m',strtotime($from_date))+$recurrence,date('d',strtotime($from_date)),date('Y',strtotime($from_date)))); elseif ($recurrence_type == 3) return date('Y-m-d',mktime(0,0,0,date('m',strtotime($from_date)),date('d',strtotime($from_date)),date('Y',strtotime($from_date))+$recurrence)); else return false; } eg code of above function: if (date('Y-m-d') = next_occurrence($row_data['recurrence'],$row_data['recurrence_type'],$row_data['last_occurrence'])) charge_client; You guys know of a way? Dont know if something like this is possible?

Couldnt I just retrieve the CSV column then explode it, then loop through it and get each id's data with a query?

Ok, so I am gonna jack up my product catalogue script a bit as it is getting slightly outdated. One of the new features is "Other related products" Now, my question is, which would be better: 1) Adding a column to a products "related_products" and just put csv ID's of other products in there and just explode,etc. whenever i need them 2) Create a new table and create a new record for every related product for each product 3) Something else you guys may think of? My worthless-based-on-nothing impressions are: 1) Would be faster, more efficient 2) Would be easier to make initially, but slower/less efficient Any takes on this or any suggestions?

So I have a script that goes to a page and rips it of all the numbers and then goes onto the next page, etc etc. Basically the script works 100% and I can do up to about 90 pages at a time; as soon as I try and do any more than that - I get an "Internal Server Error" after a while. I tried the whole ob_start / flush theory which is supposedly supposed to work and it hasnt. Any idea's? - PHP is installed as CGI - Running Apache - Script is run off a local webserver and connects to remote page Here is my script: [code]$none_found = ''; $found_num = ''; $found_at = ''; set_time_limit(9000); ob_start(); for ($i = 1; $i <= 90; $i++) {     $fh         = fopen("http://www.someplace.com/index.php?id=". $i,'r');     $result = '';     while(!feof($fh))      $result .= fgets($fh);     fclose($fh);     $message = strip_tags($result);     if (preg_match("|\d{3} \d{3} \d{4}|", $message, $numbers_found))     {         $found_at  .= $i .",";         $found_num .= $numbers_found[0] .'<br>';         mysql_query('insert into zzz_numbers (cellphone) values (\''. $numbers_found[0] .'\')');     }     else         $none_found .= $i .',';          if (substr($i,strlen($i)-1) == '0')     {       echo ".";              flush();     ob_flush();     }     sleep(1); } echo "<br><br>None found at:<br>". $none_found; echo "<br><br>Found at:<br>". $found_at; echo "<br><br>Numbers found :<br>". $found_num; [/code]

Just in case someone comes across the same problem, I think this solves it: [code] SELECT DISTINCT id,reference_number FROM dresses   WHERE NOT EXISTS (SELECT * FROM bookings                     WHERE (bookings.dress_id = dresses.id) AND               (booked_from BETWEEN('2006-01-10') AND ('2006-01-14')) AND               (booked_to BETWEEN('2006-01-10') AND ('2006-01-14'))            ); [/code]

Contents of "Dresses": [code] mysql> select id,reference_number from dresses; +----+------------------+ | id | reference_number | +----+------------------+ |  9 | 1                | |  8 | ABC34263250      | | 10 | ABC0000          | | 11 | ABC00001         | | 12 | ABC00001         | | 13 | ABC00001         | | 14 | ABC00001         | | 15 | ABC00001         | | 16 | ABC00001         | | 17 | ABC00001         | | 18 | ABC00001         | | 19 | ABC00001         | | 20 | ABC00001         | | 21 | ABC00001         | +----+------------------+ [/code] Contents of "Bookings": [code] mysql> select * from bookings; +----+----------+-------------+------------+--------------+ | id | dress_id | booked_from | booked_to  | reason       | +----+----------+-------------+------------+--------------+ |  1 |        8 | 2006-01-13  | 2006-01-13 | Dry Cleaners | |  2 |        9 | 2006-02-27  | 2006-02-28 | Hired        | +----+----------+-------------+------------+--------------+ [/code] Obsidians Query & Result: [code] mysql> SELECT     ->  dresses.id, reference_number,bookings.*     -> FROM     ->  dresses, bookings     -> WHERE     ->  dresses.id = dress_id AND dresses.id NOT IN     ->  (SELECT dress_id FROM bookings WHERE (booked_from BETWEEN '2006-01-10' AND '2006-01-14') OR (booked_to BETWEEN '2006-01-10' AND '2006-01-14')); +----+------------------+----+----------+-------------+------------+--------+ | id | reference_number | id | dress_id | booked_from | booked_to  | reason | +----+------------------+----+----------+-------------+------------+--------+ |  9 | 1                |  2 |        9 | 2006-02-27  | 2006-02-28 | Hired  | +----+------------------+----+----------+-------------+------------+--------+ [/code] What I actually want to get: [code] mysql> "magical MySQL query"; +----+------------------+ | id | reference_number | +----+------------------+ |  9 | 1                | | 10 | ABC0000          | | 11 | ABC00001         | | 12 | ABC00001         | | 13 | ABC00001         | | 14 | ABC00001         | | 15 | ABC00001         | | 16 | ABC00001         | | 17 | ABC00001         | | 18 | ABC00001         | | 19 | ABC00001         | | 20 | ABC00001         | | 21 | ABC00001         | +----+------------------+ [/code] ( Notice how: "Dress ID 8" isnt listed because it would be booked out for the desired timeframe. "Dress IDs 10-21" still show even though they have not been booked for anything. ) Basically I want to get ALL dresses that arent going to be booked out within the time frame. What obsidians query does is returns all booked items that arent within that timeframe. Two different results :) Its sick, I know. At the end of the day, all I can think of doing is two seperate queries one which selects the dresses (in a loop) and then one which checks if the dress is in the bookings table for that date. I would however much prefer having it all done in one query.