ericsante

I have a huge file with a bunch of data, the delimiters are all messed up, I want to extract the email addresses from the file. this is what I have come up with so far. The problem is that I am not getting the email addresses, I think I messed up something in the fgetcsv. $file_handle = @fopen ("messedupfile.csv"); if ($file_handle) { while (!feof($file_handle)) {$data = fgetcsv($file_handle, 1000, ' ', '"'); for ($j = 0; $j < count(!feof($file_handle)); $j++) if (eregi("^[_A-Za-z0-9-]+(\.[_A-Za-z0-9-]+)*@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)*(\.[A-Za-z]{2,3})$", $data[$j])) $query = "INSERT tblEmail VALUES "; $query .= "('".$data[$j]."'),"; $query2 = substr($query, 0, -1); mysqli_query($link, $query2); } fclose($file_handle); }

thx, I know how to build a drop down list, however I need to build 3 of them from 2 tables, and then submit them to the sql query when the options are changed.

I am trying to write some reporting for my application. I have a table with customers, and a table with date ranges called period (start and end). I want to display a recordset for the customer selected from a dropdown list and the period selected from a second dropdown. so once the data is selected I want to be able to change the selected period for the same customer or change the customer for the same period. I have the mySQL queries working just fine, I have tested my reports where I can pass $_GET's to and it works. I just don't know how to put together the dropdowns from the other tables and the post to get it all working. Any suggestions?

this is my final query that worked for me SELECT TransResult, COUNT(*) AS HowMany, (COUNT(*) / _total ) * 100 AS Percent FROM tbltranslog, (SELECT COUNT(*) AS _total FROM tbltranslog WHERE MONTH(Date) = MOD(MONTH(NOW()),12 + 1)) AS myTotal WHERE MONTH(Date) = MOD(MONTH(NOW()), 12 +1) GROUP BY transresult

I have a table called TransResult and I have 2 data fields in it. I want to get the percentages for the 2 possiable values in TransResult and I want this done by date, so I can go backwards to compare what was done in the past months to what is done in current month. This is what i have come up with so far, however my _total is giving me ALL records in the table not this months records, so my percentages are off, aside from the _total the values it is calculating are correct. Can someone help me work through this. SELECT TransResult, COUNT(*) AS HowMany, (COUNT(*) / _total ) * 100 AS Percent FROM tbltranslog, (SELECT COUNT(*) AS _total FROM tbltranslog) AS myTotal WHERE MONTH(Date) = MONTH(NOW()) +0 GROUP BY transresult thanks.

I have several tables that contains info for my customer. The tblCustomer table contains keys for the data from other tables. I need to perform calculations based on the values stored in the other tables here is the database structure and some of the data. Any assistance would be most helpful. ??? there are 3 calculations, I will be performing a search by date range to get my dataset from tbltranslog: 1. based on the SubID in tbltranslog I need calculate the "Fee" based on the value that is set in tblCustomer, I need to do this for each transaction that is in tbltranslog 2. calculate the check fee for each item that is tbltranslog that is a check, again the rate for the check is set in the tblCustomer. 3. calculate the credit card fee for each item that is tbltranslog that is a credit card, again the rate for the check is set in tblCustomer CREATE TABLE `tblCustomer` ( `Customer_ID` int(5) NOT NULL auto_increment, `AcctID` varchar(8) NOT NULL, `SubID` varchar(8) NOT NULL, `Merchant` varchar(32) NOT NULL, `Status` varchar(8) NOT NULL, `Address1` varchar(32) NOT NULL, `Address2` varchar(32) NOT NULL, `City` varchar(32) NOT NULL, `State` varchar(20) NOT NULL, `Zip` varchar(10) NOT NULL, `Phone` varchar(12) NOT NULL, `Fax` varchar(12) NOT NULL, `Email` varchar(32) NOT NULL, `Contact` varchar(32) NOT NULL, `BankID` varchar(20) NOT NULL, `AuthNet` varchar(20) NOT NULL, `MID` varchar(20) NOT NULL, `TID` varchar(20) NOT NULL, `TermKey_ID` varchar(32) NOT NULL, `CCFees_ID` varchar(4) NOT NULL, `CKFees_ID` varchar(4) NOT NULL, `Fee` varchar(20) NOT NULL, `MCSAcct` varchar(19) NOT NULL, `DebitAcct` varchar(50) NOT NULL, `PaymentType` varchar(16) NOT NULL, `AVS` varchar(4) NOT NULL, `CVV2` varchar(3) NOT NULL, `Reserve` varchar(4) NOT NULL, `Payout` varchar(4) NOT NULL, `Pricing_ID` varchar(4) NOT NULL, PRIMARY KEY (`Customer_ID`) ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ; -- -- Dumping data for table `tblCustomer` -- INSERT INTO `tblCustomer` (`Customer_ID`, `AcctID`, `SubID`, `Merchant`, `Status`, `Address1`, `Address2`, `City`, `State`, `Zip`, `Phone`, `Fax`, `Email`, `Contact`, `BankID`, `AuthNet`, `MID`, `TID`, `TermKey_ID`, `CCFees_ID`, `CKFees_ID`, `Reseller`, `MCSAcct`, `DebitAcct`, `PaymentType`, `AVS`, `CVV2`, `Reserve`, `Payout`, `Pricing_ID`) VALUES (12, 'ABCD1', '10001', 'Eric''s Test Acco', '1000', '123 some street', '', 'Some Town', 'Some State', '33137', '212-555-1212', '212-555-1212', 'one@metallica.com', 'Eric', 'My Bank', '1006', '12345', '12345', '1234-12', '1009', '1001', 'No', '', '12345', 'ACH', 'Yes', 'Yes', '1000', '1002', ''); CREATE TABLE `tblCCFees` ( `CCFees_ID` varchar(4) NOT NULL, `Rate` varchar(5) NOT NULL, PRIMARY KEY (`CCFees_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `tblCCFees` -- INSERT INTO `tblCCFees` (`CCFees_ID`, `Rate`) VALUES ('1000', '.25'), ('1001', '.30'), ('1002', '.35'), ('1003', '.40'), ('1004', '.45'), ('1005', '.50'), ('1006', '.55'), ('1008', '1.00'), ('1007', '.75'), ('1009', '1.25'), ('1010', '1.35'), ('1012', '1.45'), ('1013', '1.50'); INSERT INTO `tblCKFees` (`CKFees_ID`, `Rate`) VALUES ('1000', '1.00'), ('1001', '.75'), ('1002', '1.25'), ('1003', '1.50'); INSERT INTO `tblReserveRate` (`ReserveRate_ID`, `Reserve`) VALUES (1000, '10.00'), (1010, '9.0'), (1011, '9.25'), (1012, '9.5'), (1013, '9.75'), (1014, '8.0'), (1015, '8.25'), (1016, '8.5'), (1017, '8.75'); CREATE TABLE `tbltranslog` ( `TransLog_ID` int(10) NOT NULL auto_increment, `SubID` int(7) NOT NULL, `Date` char(21) NOT NULL, `Amount` varchar(9) NOT NULL, `TransType` varchar(15) NOT NULL, `TransResult` varchar(8) NOT NULL, `OrderNo` varchar(10) NOT NULL, `TransKey` varchar(10) NOT NULL, PRIMARY KEY (`TransLog_ID`), UNIQUE KEY `OrderNo` (`OrderNo`) ) ENGINE=MyISAM AUTO_INCREMENT=10479 DEFAULT CHARSET=latin1 AUTO_INCREMENT=10479 ; -- -- Dumping data for table `tbltranslog` -- INSERT INTO `tbltranslog` (`TransLog_ID`, `SubID`, `Date`, `Amount`, `TransType`, `TransResult`, `OrderNo`, `TransKey`) VALUES (10095, 10001, '12/05/2006 20:48:43', '25.00', 'Sale', 'Approved', '53807312', '68295959'); INSERT INTO `tbltranslog` (`TransLog_ID`, `SubID`, `Date`, `Amount`, `TransType`, `TransResult`, `OrderNo`, `TransKey`) VALUES (10094, 10001, '12/05/2006 18:56:04', '29.99', 'Check Pre-Auth', 'Approved', '53805218', '68293514'); INSERT INTO `tbltranslog` (`TransLog_ID`, `SubID`, `Date`, `Amount`, `TransType`, `TransResult`, `OrderNo`, `TransKey`) VALUES (10093, 10001, '12/05/2006 18:50:06', '29.99', 'Sale', 'Approved', '53805092', '68293359');

I have the following table CREATE TABLE `tbltranslog` (   `TransLog_ID` int(10) NOT NULL auto_increment,   `SubID` int(5) NOT NULL,   `Date` char(21) NOT NULL,   `Amount` varchar(9) NOT NULL,   `TransType` varchar(32) NOT NULL,   `TransResult` varchar(10) NOT NULL,   `AuthCode` varchar(15) NOT NULL,   PRIMARY KEY  (`TransLog_ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1; and my CSV file contains the following fields: "Sub ID","Transaction Date/Time","Amount","Consumer Name","Account Name","Transaction Type","Transaction Result","Authorization Code","Routing Number","Account Number","Account Type Description","Credit Card Number","Credit Card Expiration Date","Recurring Description","Company Name","Billing Address1","Billing Address2","Billing City","Billing State","Billing Zip","Billing Country","Shipping Address1","Shipping Address2","Shipping City","Shipping State","Shipping Zip","Shipping Country","Phone","E-Mail","Memo","Drivers License Number","Social Security Number","IP Address","Merchant Referrer Information","Merchant Order Number","Order Number","Comments","Transaction History Key","Referring Transaction History Key","Merchant Order Type","User ID","Authnet Auth Response","Authnet Auth Definition" As you can see the CSV has more data then I want to keep, so the question is how do I ignore the fields that I do not want.

what kind of reports?  Business reports? or web stats?

I have several tables to store different options, in my client table I store the tblname_ID in the tblclient.  When I want to display the client table in a list I can do this however I get the tblname_ID's and not the desctriptions, does someone have a function already that will read the values into an multi-dimentional array and display the description and not the tblname_ID. Thanks

TABLE `winners` ( `id` int(11) NOT NULL auto_increment, `auction` int(32) NOT NULL default '0', `seller` int(32) NOT NULL default '0', `winner` int(32) NOT NULL default '0', `bid` double NOT NULL default '0', `closingdate` timestamp(14) NOT NULL, `fee` double NOT NULL default '0', KEY `id` (`id`) ) TYPE=MyISAM; TABLE `auctions` ( `id` int(32) NOT NULL auto_increment, `user` int(32) default NULL, `title` tinytext, `starts` varchar(14) default NULL, `description` text, `pict_url` tinytext, `category` int(11) default NULL, `minimum_bid` double(16,4) default NULL, `reserve_price` double(16,4) default NULL, `buy_now` double(16,4) default NULL, `auction_type` char(1) default NULL, `duration` varchar(7) default NULL, `increment` double(8,4) NOT NULL default '0.0000', `location` varchar(30) default NULL, `location_zip` varchar(10) default NULL, `shipping` char(1) default NULL, `payment` tinytext, `international` char(1) default NULL, `ends` varchar(14) default NULL, `current_bid` double(16,4) default NULL, `closed` char(2) default NULL, `photo_uploaded` char(1) default NULL, `quantity` int(11) default NULL, `suspended` int(1) default '0', `private` enum('y','n') NOT NULL default 'n', `relist` int(11) NOT NULL default '0', `relisted` int(11) NOT NULL default '0', `num_bids` int(11) NOT NULL default '0', `sold` enum('y','n','s') NOT NULL default 'n', `shipping_terms` tinytext NOT NULL, `bn_only` enum('y','n') NOT NULL default 'n', `adultonly` enum('y','n') NOT NULL default 'n', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=MyISAM ; TABLE `checkout_order` ( `auction` varchar(6) NOT NULL default '', `user` int(32) NOT NULL default '0', `firstname` varchar(40) NOT NULL default '', `lastname` varchar(40) NOT NULL default '', `address` varchar(40) NOT NULL default '', `city` varchar(40) NOT NULL default '', `state` varchar(40) NOT NULL default '', `zip` varchar(40) NOT NULL default '', `country` varchar(40) NOT NULL default '', `phone` varchar(40) NOT NULL default '', `email` varchar(40) NOT NULL default '', `shipping_firstname` varchar(40) NOT NULL default '', `shipping_lastname` varchar(40) NOT NULL default '', `shipping_address` varchar(40) NOT NULL default '', `shipping_city` varchar(40) NOT NULL default '', `shipping_state` varchar(40) NOT NULL default '', `shipping_zip` varchar(40) NOT NULL default '', `shipping_country` varchar(40) NOT NULL default '', `amount` varchar(10) NOT NULL default '', `credits` varchar(10) NOT NULL default '', `ipaddr` varchar(15) NOT NULL default '', `processed` char(1) NOT NULL default '0', PRIMARY KEY (`auction`) ) TYPE=MyISAM; The query I have been using is SELECT a.auction, a.winner, b.id, b.title, b.current_bid, c.auction FROM winners a, auctions b, checkout_order c WHERE a.auction = b.id AND ( b.closed =1 OR b.closed = -1 ) AND b.suspended =0 AND a.winner =17609 AND a.auction != c.auction LIMIT 0 , 30 ---- in the auctions table I have the following records: id | title | current_bid 1000 | something | 1.00 1001 | something | 1.00 1002 | something | 1.00 1003 | something | 1.00 in the winners I have the following records: auction | winner| bid 1000 | 17609 | 1.00 1001 | 17609 | 1.00 1002 | 17609 | 1.00 1003 | 17610 | 1.00 in the checkout_orders I have the following records: auction | user 1002 | 17609 1003 | 17610 so I would expect to see only the records 1000, 1001. However I get 1000,1001,1000,1000

I am building an auction administration site, I want to check the auction site for all the closed auctions for the user and compare this against the paid auction table and then display the items that need to be paid for. I have the SQL query to find the closed auctions, however I am having problems doing the compare against the paid auctions table. Thanks in advance.

I have 2 databases where I have a UserID field in a table. I want to compare each row between the two tables in their respective database and display the differences (all the fields on each row) on a web page. I have tried it a few different ways and have been unsuccessful. Any input would be greatly appreciated. Thanks.