Jump to content

arenaninja

Members
  • Posts

    53
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

arenaninja's Achievements

Member

Member (2/5)

0

Reputation

  1. Oh!! Could this be the effect of MySQL's cache? Because that never occurred to me
  2. Sorry xyph, but it seems that PDO statements do not play well with MySQL when using variables in queries. Any advice?
  3. I swear I looked through the documentations for at least an hour, but I could only think of terms like 'AS command' (including in teh google search ingin), and it never occurred to me that the second portion of that statement could be called an Alias. I suppose it's one of the many issues of being self-taught. The solution you've provided, xyph, works just as you posted -- thank you sir.
  4. Sorry, seems that's the PHP error. Here's the MySQL error: ERROR 1054 (42S22): Unknown column 'Time' in 'field list' The table I have stores information to charge customers -- the relevant ones are: | table | CREATE TABLE `table` ( `tableID` int(10) unsigned NOT NULL AUTO_INCREMENT, `timeStart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `timeEnd` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `rateID` int(10) unsigned NOT NULL, PRIMARY KEY (`pkResID`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 | where rateID is a foreign key. The result I want looks like this (precision notwithstanding, for now): +-------+--------+------------+ | rate | Time | Total | +-------+--------+------------+ | 32.50 | 8.0000 | 260.000000 | | 32.50 | 9.0000 | 292.500000 | | 32.50 | 1.0000 | 32.500000 | | 32.50 | 1.0000 | 32.500000 | | 32.50 | 1.0000 | 32.500000 | However the code to produce this was: SELECT feetable.rate,TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 AS Time,rate*TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 AS Total FROM table LEFT JOIN feetable ON (table.rateID=feetable.pkFee); Note that TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 appears twice on this query -- once to list it AS Time and once to multiply it with the rate. All I'm looking to do is take the value Time and multiply by rate without having to have MySQL recalculate it. In essence, I want to do this: SELECT feetable.rate,TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 AS Time,rate*Time AS Total FROM table LEFT JOIN feetable ON (table.rateID=feetable.pkFee); Which MySQL points out is incorrect because the field 'Time' doesn't exist in either table. How do I reference the column 'Time' that I just created? Edit: note that I don't really have a table named "table". I just renamed it as such when posting this for obvious reasons
  5. Alright, I'm producing accounting reports based on usage, and I'm trying to get the following snippet to work: TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 AS Time, feetable.rate AS `Rate` feetable.rate*Time AS `Charge` I'm using MySQL 5.5. The column `Time` I'm guessing is floating point (usualy 0.5, 1.5, etc.) whereas `Rate` is Decimal. I get a 'syntax error or access violation' on this one, and I have an idea of why (I'm guessing named columns aren't set until you get past the WHERE statement -- i.e.: I could use them to sort, but not to multiply each other?) However I have little recourse as I do not want to overstress the database by re-calculating the TIMESTAMPDIFF. Any suggestions to get this to work?
  6. Alright the weirdness continues. I've managed to set values to the cells inside the workbook but the header content isn't showing (and weird squiggly signs are gone). My guess is that it may have something to do with using LibreOffice to open .xlsx files? I'm gonna test it from my laptop in a few hours.
  7. kicken, This stuff is opening Excel and showing it in the excel window. There's nothing else before or after this code. The user clicks a button on the page that makes a call for this function. Before trying this I have been using this to create an Excel report: $filename = 'recharge-report.xls'; $report = "<html> <body>"; $report.=$this->_reporttable; $report.= "</body> <html>"; header("Content-type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=$filename"); echo $report; It works, but I'm looking to fancify all of this (mostly automate sub-totals to remove burden from MySQL and please the accounting monkeys). I also suspect either php_xml or php_gd2 may not be enabled. My server is a CentOS server running PHP 5.3.10. I've looked all over a page with phpinfo() for php_xml and php_gd2, and I can find this (which relates to gd2): gd GD Support enabled GD Version bundled (2.0.34 compatible) But I can't find anything about php_xml, and I've spent a few minutes in google trying to figure out how to install it but I haven't found anything. I've also perused the php.ini file and have found no reference to either gd2 or php_xml. In short, I still need a ton of help PS: I don't have root access to the server, but the IT Manager is extremely accomodating and he will almost certainly install any missing plugins. However I'd like to know what/how to ask for to mask my ignorance
  8. Hey all. I just installed PHPExcel and I'm trying to do some tests. I'm trying to create my very first workbook with it, I'm doing this: if(file_exists('../sys/lib/phpexcel/PHPExcel.php')) { require_once '../sys/lib/phpexcel/PHPExcel.php'; } else die("PHPExcel not found!"); $phpExcel = new PHPExcel(); // set metadata $phpExcel->getProperties() ->setCreator($_SESSION['firstname']." ".$_SESSION['lastname']) ->setLastModifiedBy($_SESSION['firstname']." ".$_SESSION['lastname']) ->setTitle("Test document") ->setSubject("Some Report") ->setDescription("Testing testing"); // set headers to redirect output to client browser as a file download header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="myfile.xlsx"'); header('Cache-Control: max-age=0'); //-----Create a Writer and output the file to the browser----- $objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007'); $objWriter->save('php://output'); //push out to the client browser $phpExcel->disconnectWorksheets(); unset($phpExcel); Which is in line with just about every example that I've seen. However, what I get instead is that the entire webpage is sent to Excel, along with this gibberish prepended (shown at the very top): PKᄄト?GメDᄇX?[Content_Types].xmlᆳヤMN?ナ??%nY トレvAa ユ(0?ؖg?{&i ノ@?bE??y?d۸l m?ムチ?X﾿(??﾿メ?ᄍ?;@1_ヘ?リᄆ?)jᄁx/%?ナEネ?ᆭ ᄅQĿi!ᆪ?ᄉy3?<チᄃワZ1ᄑ0?Y?%zV c?Ib7?ヌ?a/l٥P1:ᆱq??jᆰ?0Aᆵヨン??"??(フ ヤ?タW?マ?Tj?{ܲ??O??X٭??B�ニ~׭?ツ?ᄂ6?=ᄀoBZᄒヌᄚᄐt?X4?ᄒCg?ナネメQgmrL?ٯc??t?Z?ノヌᄡᄏhPv???u?ユᄂ>ᆪj -' V?ヌ#メ?F^6?n?ᄡ8q"Kノ??>_ What gives? My webpage is utf-8 encoded (as is everything I'm working on)> I've verified that the properties are NOT being set (I have a suspicion that this is what that gibberish is). Any advice is helpful at this point. Thanks.
  9. Nope, not if the data is integers (which it appears to be). You can typecast the data to integers with intval or (int), which will eliminate all non-integer characters. The query will be totally safe this way. You can use array_map like kicken said, but you have to make sure it is a one-dimensional array or you won't get the expected result. Ahhh excellent then. I'll just make sure to typecast (int) when I add them to the CSV list. Many thanks!
  10. I should apologize to you both, I realize that there's something I didn't include. Typically I have this all declared inside a function with the following parameters: public function _loadTableData($id,$isCsv=false) { $sql = "SELECT * FROM someTable WHERE "; if($isCsv==true) $sql.="tablePK IN($id)"; else $sql.="tablePK=:id"; $stmt = $this->db->prepare($sql); if($isCsv==false) $stmt->bindParam(":id",$id,PDO::PARAM_INT); } As you can see, my current solution for CSVs is pretty much equivalent to scootstah's, whereas for non-CSVs I use a prepared statement. But I know that the security from prepared statements comes from the bindParam method, which is skipped entirely when I use CSVs. I'm wondering if this opens my application to vulnerabilities (I'm assuming yes), how badly, and what I can do about it.
  11. Is the text in these inputs ('Hello#') the name for the new field or is this the information that will populate the new field? In any case, unless you want to do a POST (click a 'Submit' button and refresh the page) it looks like you're looking for jQuery, which will allow you to manipulate DOM elements on the fly.
  12. Personally, I always used prepared statements: <?php try { $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass); foreach($dbh->query('SELECT * from FOO') as $row) { print_r($row); } $dbh = null; } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "<br/>"; die(); } // Retrieve values $val1 = htmlentities($_POST['some_value'],ENT_QUOTES); // a string $val2 = (int)$_POST['some_value2']; // an integer $sql = "INSERT INTO `table` (someVal1, someVal2,...) VALUES(:val1,:val2,...)"; try{ $stmt = $dbh->prepare($sql); // Sanitize values into query $stmt->bindParam(":val1",$val1,PDO::PARAM_STR); $stmt->bindParam(":val2",$val2,PDO::PARAM_INT); $stmt->execute(); }catch(Exception $e){ die($e->getMessage()); } ?> I only share because I've read that this is one of the more secure ways to handle data.
  13. I'm trying to figure out the right way to bind this statement. I have a SQL query that takes as input a comma-separated-value (CSV) generated by the server according to user input. I'm paranoid about security so I use prepared statements on everything. The query is more or less: $sql=SELECT * FROM someTable WHERE tablePK IN(3,6,7) However, assuming this can be tampered with I want to do: $sql=SELECT * FROM someTable WHERE tablePK IN(:csv) $stmt=$this->db->prepare($sql) $stmt->bindParam(":csv",$some_var,PDO::PARAM_????) As you can see I have no idea what PARAM I'm supposed to select from the PDO constants -- PARAM_INT is for the SQL Integer type, and PARAM_STRING will return the wrong results [because the SQL string is changed to IN('3,6,7') which seems to always match only the first parameter, in this case 3]. Any help is appreciated. (MySQL 5.5, PHP 5.3)
  14. I'm using MySQL 5.5.21. I have a table, the show create statement as follows: | feeschedule | CREATE TABLE `feeschedule` ( `pkRateID` int(10) unsigned NOT NULL AUTO_INCREMENT, `fkEquipID` int(10) unsigned NOT NULL, `fkInstTypeID` int(10) unsigned NOT NULL, `rate` decimal(6,2) NOT NULL, `dateOfEffect` date NOT NULL, PRIMARY KEY (`pkRateID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 | This table records rates for a particular Equipment (fkEquipID) for different institution types (fkInstTypeID). As these are subject to change, the changes are distinguished by dateOfEffect. However, I'm having issues filtering properly. I would like to select ONLY the data matching the most recent unique fkEquipID and fkInstTypeID entry. I tried: SELECT * FROM feeschedule WHERE dateOfEffect<='2012-07-27' GROUP BY fkEquipID,fkInstTypeID ORDER BY dateOfEffect DESC; but though this does return only one column per matching fkEquipID and fkInstTypeID, it is not the most recent one. Your help is greatly appreciated.
  15. Well I managed to solve it. There are a few subtleties in the solution I'm posting my solution in hopes that someone will one day use it [or if you can help me make it shorter ]. Here's the table, feeschedule: feeschedule | CREATE TABLE `feeschedule` ( `pkRateID` int(10) unsigned NOT NULL AUTO_INCREMENT, `fkEquipID` int(10) unsigned NOT NULL, `fkInstTypeID` int(10) unsigned NOT NULL, `rate` decimal(6,2) NOT NULL, `dateOfEffect` date NOT NULL, `dateOfUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `fkUserID` int(10) unsigned NOT NULL, PRIMARY KEY (`pkFeeSchdID`) ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1 Here's my query (Note that there are 3 IF statements which are exactly the same -- these are automated with PHP): SELECT equip.name_short AS `Abbrev.`,equip.name AS `Full Name`, IF(CHAR_LENGTH(GROUP_CONCAT(IF(feeschedule.fkInstTypeID=1,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)))>0, GROUP_CONCAT(IF(feeschedule.fkInstTypeID=1,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID,NULL)), CONCAT_WS(',',equip.equipid,'1')) AS `Rate1`, IF(CHAR_LENGTH(GROUP_CONCAT(IF(feeschedule.fkInstTypeID=2,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)))>0, GROUP_CONCAT(IF(feeschedule.fkInstTypeID=2,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID,NULL)), CONCAT_WS(',',equip.equipid,'2')) AS `Rate2`, IF(CHAR_LENGTH(GROUP_CONCAT(IF(feeschedule.fkInstTypeID=3,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)))>0, GROUP_CONCAT(IF(feeschedule.fkInstTypeID=3,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)), CONCAT_WS(',',equip.equipid,'3')) AS `Rate3` FROM feeschedule RIGHT JOIN equip ON (equip.equipid=feeschedule.fkEquipID) WHERE dateOfEffect<=:date GROUP BY equip.name Now to break down the IF statement: IF(CHAR_LENGTH(GROUP_CONCAT(IF(feeschedule.fkInstTypeID=1,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)))>0, GROUP_CONCAT(IF(feeschedule.fkInstTypeID=1,CONCAT_WS('i',feeschedule.rate,feeschedule.pkRateID),NULL)), CONCAT_WS(',',equip.equipid,'1')) AS `Rate1` The basic IF() function in MySQL is IF(condition,this is done if true,this is done if false). The GROUP_CONCAT needs the GROUP BY clause to Pivot the results Note that it has a nested IF() statement, if a `rate` isn't defined, the length of this statement is zero. Thus, what happens is the following: if there is a `rate` for this instType and instrument, the crosstab will display the value of the rate field and concatenate it with the rate's PK separated by the character 'i'. If a `rate` doesn't exist for this instType and instrument, then the crosstab will return the instrument's PK and the FK for instType (so that I can make a link with a $_GET string to create one), separated by a comma. The dateOfEffect argument is there because I allow rates to be set for periods of time -- the rate in June may not be the same as that for July, and I need the reports to be backward compatible so this flag is necessary. Note that the RIGHT JOIN ensures that we get a row for an element from table `equip` even if no `rate` is defined for it. Apologies for the convoluted code, if anyone ever needs it I'm sure you can do a minimalist approach and work from there (it's why I supplied the CREATE TABLE code for this table). The truth is there's probably a much simpler way of doing this by using temporary tables. However, the advantage I have using this is that for the most part I can just loop through the query's result with PHP and translate it directly into a table format in HTML.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.