Re: [PHP-DB] Number of Lines in Table.
See : http://stackoverflow.com/questions/14682448/how-to-get-the-total-row-count-with-mysqli Matt On Sep 2, 2014 7:38 PM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: Dear List - This works: mysql describe Purchases; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | indx | smallint(6) | NO | PRI | NULL| auto_increment | | manf | varchar(20) | YES | | NULL|| | itm | varchar(50) | YES | | NULL|| | prc | float | YES | | NULL|| +---+-+--+-+-++ 4 rows in set (0.00 sec) SELECT SQL_CALC_FOUND_ROWS itm FROM Purchases LIMIT 500 OFFSET 0; +--+ | itm | +--+ | BT-300. Host interface: USB | | Oregano | | Fancy Paprika| | Fancy Paprika| | snip | Oregano | | Oregano | | Oregano | | Oregano | | Basil| +--+ 453 rows in set (0.00 sec) mysql SELECT FOUND_ROWS(); +--+ | FOUND_ROWS() | +--+ | 453 | +--+ 1 row in set (0.00 sec) This does not: $sql26 = SELECT SQL_CALC_FOUND_ROWS itm FROM Purchases LIMIT 50 OFFSET 0; if (!mysqli_query($cxn, $sql26)) printf(Errormessage: %s\n, mysqli_error($cxn)); $result26 = mysqli_query($cxn, $sql26); $sql27 = SELECT FOUND_ROWS(); if (!mysqli_query($cxn, $sql27)) printf(Errormessage: %s\n, mysqli_error($cxn)); $result27 = mysqli_query($cxn, $sql27); $row27 = mysqli_fetch_row($result27); print_r($row27); //output Array ( [0] = 1 ) What is my error? TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Number of Lines in Table.
Karl, This works for small datasets, but when you have a large amount of data (either in terms of storage or row count) it is no longer practical. This is why people typically use the SQL row count instead of transferring all of the data to php and doing the work there. It is much more efficient. (You may wish to read about buffered vs unbuffered queries) Matt On Sep 2, 2014 9:57 PM, Karl DeSaulniers k...@designdrumm.com wrote: On Sep 2, 2014, at 9:37 PM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: Dear List - This works: mysql describe Purchases; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | indx | smallint(6) | NO | PRI | NULL| auto_increment | | manf | varchar(20) | YES | | NULL|| | itm | varchar(50) | YES | | NULL|| | prc | float | YES | | NULL|| +---+-+--+-+-++ 4 rows in set (0.00 sec) What is my error? TIA Ethan Hi Ethan, Is it terribly important for you to get the count from MySQL? Php does a nice job of this very easily. $sql = SELECT itm FROM Purchases; $result = mysqli_query($cxn, $sql); if (!mysqli_query($cxn, $sql27)) printf(Errormessage: %s\n, mysqli_error($cxn)); if(!$result || (mysql_numrows($result) 1)){ return NULL; } /* Return result array */ $rowarray = mysqli_fetch_array($result); $numrows = count($rowarray); //return $rowarray; print_r('Number of rows: '.$numrows); print_r('Results { '.$row.' }'); Haven't tested this, but I believe it should work out the box. May need to tweek to taste. HTH, Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Writing Problems
/***/ $fp = fopen('filename.txt', 'a+'); // The correct thing to do is: if (! $fp) throw new Exception( 'Something went wrong opening the file' ); // Otherwise, you can also check with: if (! is_resource($fp)) throw new Exception( 'Something went wrong opening the file' ); fprintf( $fp, whatever ); fclose($fp); /***/ If that fails and you aren't seeing any errors, I would try this prior to the fopen() call: if (is_writeable('/full/path/to/filename.txt')) echo File is writeable. . PHP_EOL; If is_writeable() returns false you need to look more closely at your permissions. If is_writeable() returns true and you still can't write to it, you would then be in a very strange situation. One other possible thing to try is to put the output of fprintf() into a variable and echo that variable, just to make sure everything is working fine there, but at a glance that all looks ok. -Matt On Tue, Aug 26, 2014 at 8:12 PM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: On 08/26/2014 09:19 PM, Matt Pelmear wrote: Do you check whether $fh2 is a resource after you fopen()? snip Do you check whether $fh2 is a resource after you fopen()? How? TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: www-data file
On 08/26/2014 06:21 AM, Jim Giner wrote: On 8/26/2014 12:20 AM, Ethan Rosenberg wrote: Dear list - When I use fopen, the file owner and group are both www-data. How can I ensure that the owner and group will be ethan? TIA Ethan Why should ownership be a concern when you are simply opening a file? AFAIK permissions are set at the time the file is placed there and will affect the access to them from then on. If you are able to fopen the file, why do the permissions matter? If you can't then you have an entirely different problem to discuss. fopen() can create files if they don't exist. So if you open a file for writing, it will by default receive the user/group of the web server. If you then need to access the file as some other user, you run into this situation. (I assume this is what Ethan is referring to here.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Writing Problems
Do you check whether $fh2 is a resource after you fopen()? btw, a better way than: $ chown ethan:ethan filename.txt Might be: $ chown ethan:www-data filename.txt $ chmod 664 filename.txt This way you own the file, the server can write to it (assuming your server has group www-data), and it is not world-writeable. (you almost never want things to be world-writeable) Matt On Aug 26, 2014 5:45 PM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: Dear List - I can't figure this one out. 1] Straighten out ownership ethan@meow:/var/www$ rm receipt.txt ethan@meow:/var/www$ touch receipt.txt ethan@meow:/var/www$ ls -l receipt.txt -rw-r--r-- 1 ethan ethan 0 Aug 26 19:31 receipt.txt ethan@meow:/var/www$ chmod 766 receipt.txt ethan@meow:/var/www$ ls -l receipt.txt -rwxrw-rw- 1 ethan ethan 0 Aug 26 19:31 receipt.txt This is what we want 2] Now program code ini_set('display_startup_errors', 'on'); error_reporting(E_ALL | E_NOTICE); ini_set('display_errors','1'); error_reporting(1); $fh2 = fopen(/var/www/receipt.txt, a+); want this file to be like a cash register receipt. I can truncate this file for each execution of the program; ie, when all the purchases are finished. $sql3 = select quant, orderpt, ordrpt_flag, manuf, item, stock, price, tax_flag from Inventory where UPC = $upc; $result3 = mysqli_query($cxn, $sql3); $row3 = mysqli_fetch_row($result3); print_r($row3 ); //gives correct results. $numbyt=fprintf($fh2, %s %s %.2f %s\n,$row3[3] , $row3[4], $row3[6], $row3[7]); echo br /numbyt fh2 $numbytbr /; //result is 0 No errors. What is wrong?? TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] www-data file
Another, safer, thing to consider would be setting a sticky bit on whatever directory the files will be in: http://computernetworkingnotes.com/managing-file-system-security/sticky-bit.html I've used sticky bits in a number of situations with multiple groups and users co-habiting environments quite nicely in the past. You'll find that simply chowning from inside php won't always work, depending on how your groups are setup. If it will be just one file (rather than an unlimited number of them created by the server), simply chown it to ethan manually, once. -matt On Aug 25, 2014 9:43 PM, Aziz Saleh azizsa...@gmail.com wrote: On Tue, Aug 26, 2014 at 12:20 AM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: Dear list - When I use fopen, the file owner and group are both www-data. How can I ensure that the owner and group will be ethan? TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Use chown/chgrp after the fact: http://php.net/manual/en/function.chown.php http://php.net/manual/en/function.chgrp.php If you want it to be ethan by default, something which I would never do or recommend to do for obvious security reasons you will need to modify your Apache environment variables (find where the configs are set by using grep, for example: grep www- /etc/apache2/apache2.conf).
Re: [PHP-DB] Popular website search engines
Besides the usual suspects that are 3rd party, a common solution I've seen is Solr/Lucene: https://lucene.apache.org/solr/ -Matt On 03/13/2014 11:44 AM PT, Olivier Austina wrote: Hi, I am new to website search engine area.I would like to do a survey of popular search engine used for a website. I found some search engine like FreeFind. http://www.freefind.com/ I know also that Google is used as search engine for some websites. Any suggestion on how website search engine works for traditional, e-commerce, forum, social media etc will be appreciate. Thank you. Regards Olivier -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Calculating Past Dates In A Different Time Zone
Ron, You could use the same technique here if you want to do the work in PHP: === php code === assert( convertToGMT('2013-11-01 00:00:00') == '2013-11-01 04:00:00' ); // EST offset by four hours assert( convertToGMT('2013-11-07 23:59:59') == '2013-11-08 04:59:59' ); // EDT offset by five hours function convertToGMT( $local_date ) { $date = new DateTime( $local_date, new DateTimeZone('America/New_York') ); $date-setTimezone( new DateTimeZone('GMT') ); $gmt_date = $date-format('Y-m-d H:i:s'); return $gmt_date; } === php code === For date intervals: === php code === $date = new DateTime( '2013-11-01 00:00:00', new DateTimeZone('America/New_York') ); $date-add( new DateInterval('P7D') ); // 7 days $date-setTimezone( new DateTimeZone('GMT') ); assert( $date-format('Y-m-d H:i:s') == '2013-11-08 05:00:00' ); === php code === Just be careful with mutable vs. immutable DateTime objects ;) I wouldn't mess with 23:59:59. Instead, use specific comparisons to make it work: SELECT * FROM `journal_entry` WHERE `occurrence_date` = 2013-01-01 00:00:00 AND `occurrence_date` 2013-11-08 00:00:00; ...if you're really that concerned about that one second. Alternatively you could use DateTime::sub() to subtract a single second and still use BETWEEN. I would argue that one second doesn't matter for almost any application, but I obsess over such details myself, so I can't argue that point too strongly ;) For reports on a given month or range of months, you can use different DateInterval values (P1M, etc.), or get the number of days in any given month from PHP's date() command. btw, if you were considering doing all of the work in SQL (MySQL), you could do: === sql query === SELECT * FROM `journal_entry` WHERE `occurrence_date` BETWEEN CONVERT_TZ( DATE_SUB(:end_date_in_local_time, INTERVAL 7 DAY), :local_tz, GMT ) AND CONVERT_TZ(:end_date_in_local_time, :local_tz, GMT); === sql query === For the specific problems you called out: 1) Calculating what time it is in GMT when it is midnight in the user's time zone X days ago: You just need to use DateTime::sub() to subtract X days (DateInterval('P'.$X_days.'D')) from midnight today (date('Y-m-d 00:00:00')), then convert the result to GMT. Note that this is midnight this morning from PHP's perspective if you use date()... my example below takes into account the user's timezone. 2) Calculating midnight on November 1st 2013 in the user's time zone: $date = new DateTime( '2013-11-01 00:00:00', new DateTimeZone($user_tz_str) ); I'll finish with one very specific example for one of the problems you mentioned. 3) Building a query for the last 3 months. === php code === $user_tz_str = 'America/New_York'; $tz_user = new DateTimeZone($user_tz_str); $tz_gmt = new DateTimezone('GMT'); // or UTC, or whatever... // I wasn't sure which way you meant here, so I did a few. // I think you'll be able to figure out what you want to do based on one of these or some variation on them. $starting_point = 'this morning'; // 'this morning' or 'now' or 'ending before this month' if( $starting_point == 'this morning' ) { // do 3 months back from midnight this morning. $day_date = new DateTime(); $day_date-setTimezone( $tz_user ); // $day_date-format('Y-m-d').'00:00:00' is midnight this morning from the perspective of the user's current time $end_date = new DateTime( $day_date-format('Y-m-d').'00:00:00', $tz_user ); } else if( $starting_point == 'ending before this month' ) { // do three months prior to when this month started. $day_date = new DateTime(); $day_date-setTimezone( $tz_user ); $end_date = new DateTime( $day_date-format('Y-m').'-01 00:00:00', $tz_user ); } else { // use now. User timezone doesn't even matter. $end_date = new DateTime(); } $start_date = clone $end_date; // clone the object or you'll make a mess of things. $start_date-sub( new DateInterval('P3M') ); // subtract 3 months. You could use whatever DateInterval you want here. // make sure you do timezone conversion AFTER the DateInterval is subtracted, if you care about daylight savings time. $start_date-setTimezone( $tz_gmt ); $end_date-setTimezone( $tz_gmt ); /* * At this point: * $start_dt-format('Y-m-d H:i:s') == the beginning of our interval in GMT * $end_dt-format('Y-m-d H:i:s') == the end of our interval in GMT */ // We'll use a PDO prepared statement as an example here. Assume $dbh comes from somewhere above... $sth = $dbh-prepare( 'SELECT * FROM `journal_entry` WHERE `occurrence_date` BETWEEN :start_dt_gmt AND :end_dt_gmt' ); $sth-bindParam(':start_dt_gmt', $start_dt-format('Y-m-d H:i:s'), PDO::PARAM_STR); $sth-bindParam(':end_dt_gmt', $end_dt-format('Y-m-d H:i:s'), PDO::PARAM_STR); $sth-execute(); === php code === Hope this helps, -Matt On 11/10/2013 08:26 AM PT, Ron Piggott wrote: Hi Everyone I need help knowing how to calculate midnight “X” days ago in
Re: [PHP-DB] Calculating Past Dates In A Different Time Zone
Typically, yes. It is possible you don't have the time zone tables populated. America/Bahia works for me, so I suspect that is the case. The relevant manual page to load this data (assuming your server is running in a unix environment) is here: http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html If it is running in a Windows environment: http://dev.mysql.com/downloads/timezones.html -Matt On 11/10/2013 04:22 PM PT, Ron Piggott wrote: A suggestion I was given is to use the mySQL CONVERT_TZ command with the PHP time zone names. But when I do: SELECT CONVERT_TZ( `journal_entry`.`occurance_date` , 'GMT', 'America/Bahia' ) FROM `journal_entry` I am receiving NULL as the resulting date. Does mySQL accept PHP time zone names? Ron
[PHP-DB] Subject Matter
Hello all, I am subscribed to this list because of my interest in PHP's database integration. At this point only a small percentage of the messages are related to that. I am not sure who runs the list, whether they care about off-topic posts, or whether anyone else cares about it. I, however, do. Therefore, I would like to ask the group whether anyone else cares about this, or whether I should simply unsubscribe to reduce clutter in my inbox. One way or the other I would like to receive only messages of interest to me (i.e., those pertaining to the subject matter of the list I subscribed to). Thoughts appreciated. -Matt
Re: [PHP-DB] Subject Matter
On 08/23/2013 04:36 PM, Lester Caine wrote: Matt Pelmear wrote: I am not sure who runs the list, whether they care about off-topic posts, or whether anyone else cares about it. The php lists are only loosely moderated, but comments like yours usually bring things under control. I'd refer you to my recent post thought as to why the current threads are not that far off topic ;) Indeed, that thread is one that is on topic... but I think the signal-to-noise ratio on this list is rather poor ;) If I'm the only one bothered by it, it's no big deal for me to unsubscribe... I just thought I'd check the general opinion first. I don't run into these problems on the internals list... :-) -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] chmod
Emiliano, This question is totally out of scope for this mailing list. It has nothing to do with databases at all. Also, you should _almost never_ use 0777 for permissions. You probably want 0644. (Owner can read/write, all others can read.) Regarding your question in general, it sounds like you are running this on a Windows environment. I can't personally speak to whether chmod() works well in that environment or not (I would never run a windows server or dev environment for that matter), but I suspect it may not be working as expected based on what you are describing, especially if you are using an older version of PHP. I would be willing to further help you diagnose the problem (others may be as well), but I really think this does not belong on this mailing list and, as such, those discussions should be held off-list. FYI, I think the appropriate forum for this question would be either the General user list or, if you are indeed using Windows, the Windows PHP users list: http://php.net/mailing-lists.php -Matt On 08/20/2013 08:39 AM, Emiliano Boragina wrote: Hello everyone, I try to use chmod 0777, but the JPG file in my localhost I cant view. I must enter in the windows properties of the JPG file, add the user with full permission, and them i can view it. I use this: move_uploaded_file($file_tmp,$desired_dir/.$file_name); chmod($desired_dir/.$file_name,0777); What's wrong? Thanks. Emiliano
Re: [PHP-DB] Sorting a PHP array
I would use ORDER BY in your query and let the database do the work, assuming you're using a standard relational database (like MySQL or Postgresql). Otherwise, if you MUST do it in PHP, you could look at usort(), or uasort() using your own custom sorting function. I would, however, strongly recommend keeping the work in the database. (If you're using a bleeding-edge version of PHP (I think this is in the 5.5 release candidates), you could also use array_column() along with usort() or uasort() to make your life easier.) -Matt On 05/15/2013 09:32 AM, Charlie Lewis wrote: I have a bookseller database list read into a PHP array with dimensions [row][field]. There are 32 fields in each record/row and up to 500 records. What is the neatest way to sort the array by any of the fields, such as author or title? I know I should just search for a manual on this, but could anybody give me a quick start? Thanks, Charlie -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Placing a form on a page
On 05/05/2013 10:00 PM, Ethan Rosenberg, PhD wrote: On 05/06/2013 12:48 AM, tamouse mailing lists wrote: What you're asking about has nothing to do with PHP. You want help with HTML and CSS, and front-end design. You are absolutely correct. I meant to label the post as HTML - Placing a form on a page. My logic was that a PHP programmer will know HTML. Lest we forget, the name of the list is php-db, meaning it should be about database related topics in the php language. I don't know whose job it is to manage things like that (because the list seems to generally accept questions of any type), but it would be great if we tried to stay on-topic. I actually subscribed initially because I had some very specific questions about PDO and mysql's interaction. It seems I should probably unsubscribe since there are rarely topics of any interest to me, but I keep hoping things will turn around... -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] pdo ?
http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post Every pro has this feature (magic_quotes_gpc) turned off. If you understand SQL Injection vulnerabilities, and properly bind things into your queries, I would recommend disabling it. -Matt On 01/24/2013 08:55 AM, Jim Giner wrote: ok - new to using pdo functions, but I thought I had a handle on it. I'm writing out to my page an input tag with the following value in it: 49'ers I can confirm it by using my browser's view source to see that is exactly how it exists in the page. When I hit a submit button and my script retrieves the 'post' vars my debugging steps are showing that the var $_POST['team'] contains the above value with a backslash (\) already inserted. This is causing me a problem when I then try to use pdo-quote to safely encode it for updating my sql database. My question is - why does the POST var show the \ char before I execute the 'quote' function? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] pdo ?
On 01/24/2013 09:23 AM, Jim Giner wrote: On 1/24/2013 12:05 PM, Matt Pelmear wrote: http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post Every pro has this feature (magic_quotes_gpc) turned off. If you understand SQL Injection vulnerabilities, and properly bind things into your queries, I would recommend disabling it. -Matt On 01/24/2013 08:55 AM, Jim Giner wrote: ok - new to using pdo functions, but I thought I had a handle on it. I'm writing out to my page an input tag with the following value in it: 49'ers I can confirm it by using my browser's view source to see that is exactly how it exists in the page. When I hit a submit button and my script retrieves the 'post' vars my debugging steps are showing that the var $_POST['team'] contains the above value with a backslash (\) already inserted. This is causing me a problem when I then try to use pdo-quote to safely encode it for updating my sql database. My question is - why does the POST var show the \ char before I execute the 'quote' function? You're right! But I must not understand something. My root folder has a php.ini file with the magic quotes set off. Doesn't that carry on down to folders beneath it? I would check phpinfo() to see if it is being overridden. -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] pdo ?
On 01/24/2013 12:00 PM, Jim Giner wrote: On 1/24/2013 1:41 PM, Richard Quadling wrote: On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote: On 01/24/2013 09:23 AM, Jim Giner wrote: On 1/24/2013 12:05 PM, Matt Pelmear wrote: http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post Every pro has this feature (magic_quotes_gpc) turned off. If you understand SQL Injection vulnerabilities, and properly bind things into your queries, I would recommend disabling it. -Matt On 01/24/2013 08:55 AM, Jim Giner wrote: ok - new to using pdo functions, but I thought I had a handle on it. I'm writing out to my page an input tag with the following value in it: 49'ers I can confirm it by using my browser's view source to see that is exactly how it exists in the page. When I hit a submit button and my script retrieves the 'post' vars my debugging steps are showing that the var $_POST['team'] contains the above value with a backslash (\) already inserted. This is causing me a problem when I then try to use pdo-quote to safely encode it for updating my sql database. My question is - why does the POST var show the \ char before I execute the 'quote' function? You're right! But I must not understand something. My root folder has a php.ini file with the magic quotes set off. Doesn't that carry on down to folders beneath it? I would check phpinfo() to see if it is being overridden. -Matt Create an info.php file containing ... ?php phpinfo(); Save that in the directory containing PHP and one other directory. Load them via your browser. See the settings for the magic_ and see where the php.ini configuration file is being loaded. It may be that your ini file is completely ignored! Matt Rich, I have a small php.ini in my domain's 'php' folder as well as in my webroot folder. I was under the impression that the overrides would be applied to all folders below my webroot, but apparently it is not happening. How do 'pros' replicate their .ini settings thru all of the application folder? Not thru settings within the scripts I hope - I thought I read that the was not a very efficient way to do it and that a php.ini file was the best since it would be merged with the master one installed by my hoster. Jim, Personally I rarely have the need to override the php.ini settings for a particular host on a server. (Granted I never work in shared servers) Assuming you are using Apache and the standard module configuration, you can use .htaccess files to override many settings from php.ini Official reference pages: http://php.net/manual/en/configuration.changes.php (you might want to read through the comments here, too) http://httpd.apache.org/docs/current/howto/htaccess.html Example and some discussion here as well: http://davidwalsh.name/php-values-htaccess If you are using another web server or running PHP as FastCGI you may need to consider other options (changing the setting globally or doing a require_once() of your config changes?, or see the FastCGI example here: http://www.askapache.com/php/php-htaccess-tips-tricks.html) -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] pdo ?
On 01/24/2013 01:37 PM, Karl DeSaulniers wrote: On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote: On 01/24/2013 12:00 PM, Jim Giner wrote: On 1/24/2013 1:41 PM, Richard Quadling wrote: On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote: On 01/24/2013 09:23 AM, Jim Giner wrote: On 1/24/2013 12:05 PM, Matt Pelmear wrote: http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post Every pro has this feature (magic_quotes_gpc) turned off. If you understand SQL Injection vulnerabilities, and properly bind things into your queries, I would recommend disabling it. -Matt On 01/24/2013 08:55 AM, Jim Giner wrote: ok - new to using pdo functions, but I thought I had a handle on it. I'm writing out to my page an input tag with the following value in it: 49'ers I can confirm it by using my browser's view source to see that is exactly how it exists in the page. When I hit a submit button and my script retrieves the 'post' vars my debugging steps are showing that the var $_POST['team'] contains the above value with a backslash (\) already inserted. This is causing me a problem when I then try to use pdo-quote to safely encode it for updating my sql database. My question is - why does the POST var show the \ char before I execute the 'quote' function? You're right! But I must not understand something. My root folder has a php.ini file with the magic quotes set off. Doesn't that carry on down to folders beneath it? I would check phpinfo() to see if it is being overridden. -Matt Create an info.php file containing ... ?php phpinfo(); Save that in the directory containing PHP and one other directory. Load them via your browser. See the settings for the magic_ and see where the php.ini configuration file is being loaded. It may be that your ini file is completely ignored! Matt Rich, I have a small php.ini in my domain's 'php' folder as well as in my webroot folder. I was under the impression that the overrides would be applied to all folders below my webroot, but apparently it is not happening. How do 'pros' replicate their .ini settings thru all of the application folder? Not thru settings within the scripts I hope - I thought I read that the was not a very efficient way to do it and that a php.ini file was the best since it would be merged with the master one installed by my hoster. Jim, Personally I rarely have the need to override the php.ini settings for a particular host on a server. (Granted I never work in shared servers) Assuming you are using Apache and the standard module configuration, you can use .htaccess files to override many settings from php.ini Official reference pages: http://php.net/manual/en/configuration.changes.php (you might want to read through the comments here, too) http://httpd.apache.org/docs/current/howto/htaccess.html Example and some discussion here as well: http://davidwalsh.name/php-values-htaccess If you are using another web server or running PHP as FastCGI you may need to consider other options (changing the setting globally or doing a require_once() of your config changes?, or see the FastCGI example here: http://www.askapache.com/php/php-htaccess-tips-tricks.html) -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php You could just check for it with php and add or strip accordingly. adding slashes if magic_quotes is disabled: if (!get_magic_quotes_gpc()) { $var = addslashes($var); } stripping slashes if magic_quotes is enabled and your planning on sanitizing yourself. if (get_magic_quotes_gpc()) { $var = stripslashes($var); //do your own sanitizing } I wouldn't suggest the last one if your not going to sanitize yourself as it will make you vulnerable. But all-in-all very simple to implement. Best, Karl DeSaulniers Design Drumm http://designdrumm.com You shouldn't be sanitizing using addslashes() regardless, or using magic_quotes_gpc at all really, if you can help it. See: http://php.net/manual/en/security.magicquotes.whynot.php (magic_quotes was deprecated because it is bad.) -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] pdo ?
On 01/24/2013 01:34 PM, Jim Giner wrote: If you are using another web server or running PHP as FastCGI you may need to consider other options (changing the setting globally or doing a require_once() of your config changes?, or see the FastCGI example here: http://www.askapache.com/php/php-htaccess-tips-tricks.html) -Matt It sounds like I'll have to modify multiple htaccess files - which is about the same as this php.ini problem - having multiples perhaps. I've been working fine with a php.ini that merges onto the full ini file. Suddenly this new appl is not seeing the overrides. Any info you can point me to about how php.ini files work? The manual doesn't get very specific about it. You would only have to modify multiple .htaccess files if you have multiple document roots that need this change applied to them. Otherwise just create/edit the .htaccess file in the document root for the site you want the change applied to. .htacess isn't always read by Apache. Some configurations tell it to read or not read that file, so you may need to check that as well. phpinfo() will tell you which php.ini file is being used-- if you edit the one it says is being used you should see a change unless something else is overriding the setting (like an .htaccess file or your code). -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] pdo ?
On 01/24/2013 04:02 PM, Jim Giner wrote: I took my 6 line override php.ini file and replicated (with a script) into all of my possible folders under my web root. NOt exactly an elegant solution, but with the script, easy to maintain. Honestly, you would be better off just putting those 6 overrides into your common include file. I'm curious why would you do that as opposed to just putting it in the webroot with a single .htaccess file (or changing the global php.ini)? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
On 01/06/2013 07:09 PM, tamouse mailing lists wrote: Actually, you might find netbeans just all that for writing the HTML, CSS and JS stuff, too. I used Dreamweaver when it first came out and compared to the other sorts of things available at the time, it was pretty spiffy. But time has marched on, and creating HTML isn't something I want a visual editor for. +1 for Netbeans. I've used all the major IDEs that support PHP over the years. I keep coming back to Netbeans. (And ended up using it for Java and C/C++ as well, for small projects anyway.) As much as it is terrible, it is still that much better than the alternatives. I don't even have that many complaints about the most recent versions. I suggest it for newcomers to multiple teams I work with. I did really like NuSphere's PhpED, but unfortunately that was only an option for me when I still dual-booted into Windows. (They say it runs fine in Wine, but major things like the code completion popup are either difficult or impossible to get working. Anyone from NuSphere listening? I want to pay you for a native Linux version like you used to have...) -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running - Mystery SOLVED
This is a *very* common problem, so don't feel too bad :-) I personally *never* set the error reporting level in the code, and during code reviews I reject code that does so. It is much cleaner to manage this in php.ini. Glad you solved your problem. -Matt On 01/07/2013 08:49 AM, Ethan Rosenberg, PhD wrote: Dear list - Boy, Do I make STUPID MISTAKES! The show_errors was turned off in the program in question!! When I ran another program, that displayed errors, is threw the correct error message. My apologies for running everyone around numerous trees. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
On 01/04/2013 01:40 PM, tamouse mailing lists wrote: One should be able to solve parsing problems without resorting to using a web server. Every time I save a php file, I have my editor set to run it through php -l. Saves lots of angst when I get to the web testing stage. This is a good idea in general. It's also a good trick to check for this problem from the command line when suspecting a parse problem. Most IDEs will make parse errors very obvious while you are editing the file, too. Unfortunately not all members of any given development team can be relied upon to test things in this way before committing ;) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
On 01/04/2013 06:00 PM, tamouse mailing lists wrote: On Fri, Jan 4, 2013 at 6:09 PM, Matt Pelmear mjpelm...@gmail.com wrote: On 01/04/2013 01:40 PM, tamouse mailing lists wrote: One should be able to solve parsing problems without resorting to using a web server. Every time I save a php file, I have my editor set to run it through php -l. Saves lots of angst when I get to the web testing stage. This is a good idea in general. It's also a good trick to check for this problem from the command line when suspecting a parse problem. Most IDEs will make parse errors very obvious while you are editing the file, too. Unfortunately not all members of any given development team can be relied upon to test things in this way before committing ;) Committing a syntax error which breaks integration is a HUGE HUGE NO NO. Such a person would likely be cast from a dev team, or relegated to some more benign task. Yup. Again though, not everyone is so lucky as to only work with true professionals at all times. I've personally managed dev teams that are anywhere from largely green developers to largely seasoned professionals. Sometimes you have to work with what you have. But we digress... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
In addition to Jim's comments, Have you checked to see whether the permissions on all files are appropriate on the environment where this doesn't work? Are you running this in a web environment or CLI? If web, are you 100% sure the web server configuration is correct? -Matt On 01/03/2013 04:35 PM, Ethan Rosenberg, PhD wrote: Dear List - I am running sid on my Lenovo desktop and squeeze on my Dell laptop. The php.ini files are the same. The programs are the same except foro the locatioln of the password file and of a file to be read for parameters. The programs run beautifully on the desktop, but refuse to run on the laptop. Error_reporting is set to -1. I receive no errors. Advice and help, please. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
Ethan, I'm not sure this conversation is appropriate for this mailing list. This question has probably been answered 1000 times on the general php list. In my experience, the problem you are experiencing is caused by one of two issues: 1) A major parsing problem (sometimes no error is displayed-- you just get no output.) 2) An out-of-memory error (which can sometimes cause scripts to bail out even when error reporting is enabled) I would recommend: - Setting the error reporting level in php.ini rather than with error_reporting() while you debug this issue - Systematically comment out your entire script (in which case you should be able to run it and do something simple like display a string), then slowly add things in until you no longer receive output. ex: ?php error_reporting(-1); echo this is my page!; this_will_still_die(;) ? becomes: ?php echo test; /* error_reporting(-1); echo this is my page!; this_will_still_die(;) */ ? then: ?php echo test; error_reporting(-1); /* echo this is my page!; this_will_still_die(;) */ ? Call me crazy, but I swear I've encountered problems where even comments weren't enough- I've had to actually remove the code from the file and paste it in bit by bit. PHP can be extremely annoying sometimes. -Matt On 01/03/2013 08:54 PM, Ethan Rosenberg, PhD wrote: = Jim and Matt - Did all the suggested debugging prior to sending the eml. Note - error_reporting(-1). I hope that should catch anything. If it helps, the programs with just HTML code run OK. the ones with HTML/PHP do not. In fact, one of the HTML/PHP programs does not give any output at all, even w/ a character string; eg, dtgfsvc, at the beginning f the code. Are you running this in a web environment or CLI? If web, are you 100% sure the web server configuration is correct? I can't answer because I do not understand. ?php phpinfo(); ? gives the correct output. Hopefully, all the above should give us some hints at how to proceed. Ethan
Re: [PHP-DB] CSV storage InnoDB?
Karl, Typically I would do this as multiple rows rather than comma-separated data in one field. If you go this route, you may consider adding an unsigned int primary key (with auto increment) on the discounts table so you consume less storage space and can do joins more quickly. `discounts`: id discount_code product_id_or_whatever 1sfeijfkjx 34 2kbgrlijf 36 `user_discounts`: user_id discount_id 1231 1232 4563 If you really needed to SELECT the data with comma separation, you could use something like GROUP_CONCAT (http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat), but I would try to avoid that. This is a very basic design principle for relational databases. You may want to do some more reading about database architecture in general. That said, you could use a TEXT field to store a comma separated list, I just wouldn't recommend doing it that way. -Matt On 11/26/2012 06:01 PM, Karl DeSaulniers wrote: On Nov 26, 2012, at 7:29 PM, Bastien wrote: Bastien Koert On 2012-11-26, at 7:43 PM, Karl DeSaulniers k...@designdrumm.com wrote: Hello all, Quick question. What is the best way to store a comma separated list of discount codes in a InnoDB? Text, varchar, blob, ? I want to be able to store discount codes offered to users in their profile, in their cart, etc. So I am thinking of storing the codes as a comma separated list that I can then refer to, add to or remove codes from. The codes will change every once and a while and they list of codes may be rather large after say a year, so I am shying away from an enum() or anything like that. Any help/pointers is greatly appreciated. Best, Karl -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl. Is there a way to group these codes? Can profiles be the same for different users? If so another table for the code and a join table for the users to codes might be simpler to manage Bastien Hi Bastien, Thanks for your reply. Sorry, I should have elaborated. There is a table that holds all the discount codes. Keeps them grouped with a product id or if they are to be applied cart wide. So the field I am asking for will hold codes that are being used by either a product or by a user or if it is a cart discount. Each have their own tables, the products, the users and the cart. In these tables is where I need to insert this field. An example of the discount table would be like: Discount table: | discount code | product id | discount perc. | discount type | +-+-+--+---+ |8lKzv_=== | 22 | 20 | item | +-+-+--+---+ | NpLK23 | 0 | 50 | cart | +-+-+--+---+ but in the user table for instance: User table: | user_discounts | +---+ |8lKzv_===, YjMlio_===, NpLK23, hDflp0_=| +---+ Just trying to figure out the best way to store multiple variables if you will in one field like so. Or if this is even the best way to do such a thing. I am open to alternatives. I will need to read this new filed out of the database as an array that I can then utilize with an in_array or something of the sort. There may be a best practices on this that I am not aware of with my somewhat limited PHP exp. Hope that makes more sense. TIA. Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] CSV storage InnoDB?
BLOB and TEXT are basically the same thing, except that BLOB can be used for storing binary data (like an image). You would only need one additional table to do what I was describing, but if you're dead-set on using a comma separated list then TEXT or LONGTEXT is probably what you want, depending on how large you anticipate those lists becoming. If you care about efficiency you may want to look at this blog post: http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/ -Matt On 11/26/2012 09:55 PM, Karl DeSaulniers wrote: Hi guys, Thanks for your responses. I do have a auto incremented id for the codes. Yes multiple product can have the same code. Table is as follows. CREATE TABLE `discounts` ( `d_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `p_id` int(10) unsigned NOT NULL DEFAULT '0', `d_discode` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '', `d_type` enum('item','cart') NOT NULL DEFAULT 'item', `d_discamt` float(5,2) unsigned NOT NULL DEFAULT '0.00', `d_discper` int(3) unsigned NOT NULL DEFAULT '0', `d_qty` int(10) unsigned NOT NULL DEFAULT '0', `d_description` varchar(25) CHARACTER SET utf8 NOT NULL DEFAULT '', `d_expire` datetime NOT NULL DEFAULT '-00-00 00:00:00', `d_user` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '', `d_userip` varchar(15) CHARACTER SET utf8 NOT NULL DEFAULT '', `d_date` datetime NOT NULL DEFAULT '-00-00 00:00:00', PRIMARY KEY (`d_id`), KEY `d_discode` (`d_discode`), KEY `d_expire` (`d_expire`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; This is where the discounts and their codes and any info are stored including who set the discount (this user does not apply to our situation, FYI). Then in say the user table I set the codes that user gets to use. ... `u_discodes` (???This is my question. What to use???) CHARACTER SET utf8 NOT NULL DEFAULT '', ... I just need to store the separate codes a user has available to them here. So what would be the best suited? I am thinking a blob or text, because like tamouse said, I can explode the results and or check an in_array on the results. It seems the logical route to me for what I am doing. u_discodes just is a reference to check against. I then apply the amounts or percentages according to what is in the discounts table for that code. Am I moling this? Missing something? I'd like ( I guess ) to stay away from creating separate tables that hold the references for users, products, carts, etc and their codes. To me the blob seems so much simpler. But alas, I may have answered my own question. : \ Best, Karl Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Formatting
FYI, this really is not the appropriate mailing list for these types of questions. Are you positive they are empty lines? i.e., are they in the html code of the page, or only appear as empty space in the rendered page? If they are just in the rendered page, in addition to losing the /div in the middle of your table (as someone has suggested), you might also want to check your stylesheets. In particular, this looks like it could cause some strange things to happen depending on what you're doing with it: td class=first-col Try loading up firebug and looking at the table element and the elements around it to see if there is some strange padding. Or, try disabling any stylesheets you are loading with the page to see if it still renders in an undesirable way. Good luck, -Matt On 11/25/2012 07:05 PM, Jim Giner wrote: On 11/25/2012 5:39 PM, Ethan Rosenberg, PhD wrote: List - Any more ideas. = Keep on debugging jg Jim - I've been at this for a few weeks, and am stuck. Thanks for all your help. Ethan + On Nov 25, 2012, at 1:59 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: On 11/25/2012 12:56 PM, Jim Giner wrote: On 11/25/2012 12:46 PM, Ethan Rosenberg, PhD wrote: Dear list - When I run the following code, the results are preceded by at least one screen full of blank lines. I am showing you a large code block since I do not know where the error is: if(isset($_REQUEST['Sex']) trim($_POST['Sex']) != '' ) { if ($_REQUEST['Sex'] === 0) { $sex = 'Male'; } else { $sex = 'Female'; } } $allowed_fields = array ('Site' = 's', 'MedRec' = 'i', 'Fname' = 's', 'Lname' = 's','Phone'= 's', 'Height' = 'i', 'Sex' = 's', 'Hx' = 's','Bday' = 's', 'Age' = 'i' ); if(empty($allowed_fields)) { echo ouch; } // Configure the query and the acceptable params to put into the WHERE clause $sql12 = 'SELECT * FROM Intake3 WHERE 1'; // Magically put everything together $types = ''; $args = array(); foreach( $allowed_fields as $k = $type ) { if( !array_key_exists( $k, $allowed_fields ) ) continue; else { if( ($_POST[$k]) != '') { $args[] = $_POST[$k]; // Note the addition of the ampersand here $types .= $type; $sql12 .= AND ($k = ?); } } } $stmt = mysqli_stmt_init($cxn); mysqli_stmt_prepare( $stmt, $sql12 ); if( !$stmt ) throw new Exception( 'Error preparing statement' ); // Put the statement and types variables at the front of the params to pass to mysqli_stmt_bind_param() array_unshift( $args, $stmt, $types ); // Note that I've moved this call. Apparently it doesn't pass back the result. I guess sometimes I just forget these things. // mysqli_stmt_bind_param() if( !call_user_func_array( 'mysqli_stmt_bind_param', $args ) ) throw new Exception( 'Failed calling mysqli_stmt_bind_param' ); if( !mysqli_stmt_execute( $stmt ) ) throw new Exception( 'Error while executing statement' ); mysqli_stmt_bind_result( $stmt, $Site, $MedRec, $Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age); if(count($errors_array) == 0) { ? centerbSearch Results/b/centerbr / center !-- This is the block that prints about one screen full down bellow the Search Results header -- table border=4 cellpadding=5 cellspacing=55 rules=all frame=box style=table-layout: fixed; tr class=heading thSite/th thMedical Record/th thFirst Name/th thLast Name/th thPhone/th thHeight/th thSex/th thHistory/th thBirthday/th thAge/th /tr /div ?php $i = 0; do { { $vara2 = array(array($Site, $MedRec, $Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age)); $vara2[$i][0]= $Site; $vara2[$i][1]= $MedRec; $vara2[$i][2]= $Fname; $vara2[$i][3]= $Lname; $vara2[$i][4]= $Phone; $vara2[$i][5]= $Height; $vara2[$i][6]= $Sex; $vara2[$i][7]= $Hx; $vara2[$i][8]= $Bday; $vara2[$i][9]= $Age; echo tr\n; $_SESSION['exe'] = 2; ? td ?php echo $vara2[$i][0]? /tdbr / td ?php echo
Re: [PHP-DB] Prepared Statements - Select - Bind Parameters w/ correction
$stmt = mysqli_stmt_prepare( $cxn, $sql12 );// line 507 //Warning: mysqli_stmt_prepare() expects parameter 1 to be mysqli_stmt, object given in /var/www/x5.php on line 507 $cxn is not a mysqli_stmt. Are you perhaps passing the mysqli database resource instead of the statement? See this section of my example: $stmt = mysqli_prepare( $dbh, $q ); if( !$stmt ) throw new Exception( 'Error preparing statement' ); where $dbh is the result of mysqli_connect() and $q is a string containing your unbound query. -Matt On 09/28/2012 09:46 AM, Ethan Rosenberg, PhD wrote: Matt - Thanks. Here is what I used, and it still generates an error: $allowed_fields = array ( $_POST['Site'] = 's', $_POST['MedRec'] = 'i', $_POST['Fname'] = 's', $_POST['Lname'] = 's', $_POST['Phone'] = 's', $_POST['Height'] = 'i', $_POST['Sex'] = 's', $_POST['Hx'] = 's', $_POST['Bday'] = 's', $_POST['Age'] = 'i' ); if(empty($allowed_fields)) { echo ouch; } // Magically put everything together $types = ''; $args = array(); foreach( $allowed_fields as $k = $type ) { if( !array_key_exists( $k, $_POST ) ) continue; $args[] = $_POST[$k]; // Note the addition of the ampersand here $types .= $type; $sql12 .= AND ($k = ?); } echo new query $sql12; // For debugging and demonstration echo 'Query: ' . $sql12 . PHP_EOL; echo 'Bind types: ' . $types . PHP_EOL; echo 'Arguments:' . PHP_EOL; print_r($args); $stmt = mysqli_stmt_prepare( $cxn, $sql12 );// line 507 //Warning: mysqli_stmt_prepare() expects parameter 1 to be mysqli_stmt, object given in /var/www/x5.php on line 507 if( !$stmt ) throw new Exception( 'Error preparing statement' ); // line 509 //Fatal error: Uncaught exception 'Exception' with message 'Error preparing statement' in /var/www/x5.php on line 509 //Exception: Error preparing statement in /var/www/x5.php on line 509 // Put the statement and types variables at the front of the params to pass to mysqli_stmt_bind_param() array_unshift( $args, $stmt, $types ); // Note that I've moved this call. Apparently it doesn't pass back the result. I guess sometimes I just forget these things. // mysqli_stmt_bind_param() if( !call_user_func_array( 'mysqli_stmt_bind_param', $args ) ) throw new Exception( 'Failed calling mysqli_stmt_bind_param' ); if( !mysqli_stmt_execute( $stmt ) ) throw new Exception( 'Error while executing statement' ); mysqli_stmt_bind_result( $stmt, $id, $data ); while( mysqli_stmt_fetch($stmt) ) printf( %d %d\n, $id, $data ); mysqli_stmt_close( $stmt ); // mysqli_close( $cxm ); What did I do wrong??? Ethan
Re: [PHP-DB] Prepared Statements - Select - Bind Parameters w/ correction
Ethan, Please accept my apologies for the minor errors in the untested code I previously provided for your edification. Consider the following tested code: ?php /* CREATE TABLE test (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, data INT UNSIGNED); INSERT INTO test (data) VALUES (123),(124),(125); */ // Faking a POST for testing $_POST = array( 'id' = 1, 'data' = 123 ); // Don't forget to put your mysql configuration here $mysql_host = 'localhost'; $mysql_user = 'user'; $mysql_password = 'password'; $mysql_database = 'test'; $dbh = mysqli_connect( $mysql_host, $mysql_user, $mysql_password, $database ); if( !$dbh ) die( 'Connect failed: ' . mysqli_connect_error() . PHP_EOL ); // Configure the query and the acceptable params to put into the WHERE clause $q = 'SELECT * FROM test WHERE 1'; $allowed_fields = array( 'data' = 'i', 'id' = 'i' ); // Magically put everything together $types = ''; $args = array(); foreach( $allowed_fields as $k = $type ) { if( !array_key_exists( $k, $_POST ) ) continue; $args[] = $_POST[$k]; // Note the addition of the ampersand here $types .= $type; $q .= AND ($k = ?); } // For debugging and demonstration echo 'Query: ' . $q . PHP_EOL; echo 'Bind types: ' . $types . PHP_EOL; echo 'Arguments:' . PHP_EOL; print_r($args); $stmt = mysqli_prepare( $dbh, $q ); if( !$stmt ) throw new Exception( 'Error preparing statement' ); // Put the statement and types variables at the front of the params to pass to mysqli_stmt_bind_param() array_unshift( $args, $stmt, $types ); // Note that I've moved this call. Apparently it doesn't pass back the result. I guess sometimes I just forget these things. // mysqli_stmt_bind_param() if( !call_user_func_array( 'mysqli_stmt_bind_param', $args ) ) throw new Exception( 'Failed calling mysqli_stmt_bind_param' ); if( !mysqli_stmt_execute( $stmt ) ) throw new Exception( 'Error while executing statement' ); mysqli_stmt_bind_result( $stmt, $id, $data ); while( mysqli_stmt_fetch($stmt) ) printf( %d %d\n, $id, $data ); mysqli_stmt_close( $stmt ); mysqli_close( $dbh ); /// end code snippet I would recommend you consider Jim Giner's remarks as well. PHP's error message was giving you exactly what you needed to solve the problem with the code I gave you. There is even a note about using call_user_func_array() in the documentation about mysqli_stmt_bind_param(). In fact, the first example in the comments on the mysql_stmt_bind_param() page shows one way of solving the issue you are having. (http://php.net/manual/en/mysqli-stmt.bind-param.php) I think you will find people a lot more willing to help if you can show that you've done basic research like looking at the documentation for the function you are trying to use :-) If you don't understand references, I would recommend reading about them: http://php.net/manual/en/language.references.php -Matt On 09/27/2012 09:40 AM, Ethan Rosenberg, PhD wrote: Dear list - SEE CORRECTION IN $_POST VARIABLE BELOW. Thanks to all for your help. I hope [??] that this question will solve all the remaining problems. So that we are on the same page, here is what was previously stated. mysqli_stmt_bind_param expects three variables, in this order mysqli_stmt_bind_param($stmt, num, $a, $b, $c) Where stmt is the query w/ the ?? that is SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age FROM Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?) and num is the number and type of variables is the query, in this case 'sis' $a $b and $c are the variables to be inserted, in this case: $a = $_POST['Site']; $b = $_POST['MedRec']; $c = $_POST['Sex']; As I seem to have found, the variables cannot be a string or components of an imploded array. This is a search function that will take patient supplied data and search the Intake database to determine the Medical Record Number. There are nine variables in the database, and I never know which variables the patient will give. Based on the database, it is easy to set up the correspondence. The database is searched in the order of the correspondence and the letters can be immediately determined... $a = $_POST['Site'] $b = $_POST['MedRec'] $c = $_POST['Fname'] $d = $_POST['Lname'] $e = $_POST['Phone'] $f = $_POST[Height'] $g = $_POST['Sex'] $h = $_POST['Hx'] $i = $_POST['Bday'] $i = $_POST['Age'] - Corrected The challenge is to be able to dynamically select the variables that will go intomysqli_stmt_bind_param. Advice and help, please Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements - Select
Ethan, I believe the root of your problem is that you are passing $bind3 as a string to mysqli_stmt_bind_param() where the function was expecting multiple arguments. Let's say $binder = array( 'one', 'two', 'three' ); // I'll call this $arguments in my example below and $typer = array( 's', 'i', 's' ); Right now you are effectively doing this: // doesn't work mysqli_stmt_bind_param( $stmt, 'sis', one, two, three ); Consider using the call_user_func_array() method: // Pass elements in $arguments array to the mysqli_stmt_bind_param() method as separate arguments. call_user_func_array( 'mysqli_stmt_bind_param', array_unshift($arguments, $stmt, $types) ); In this case, this call_user_func_array() call would effectively be the same as: mysqli_stmt_bind_param( $stmt, sis, 'one', 'two', 'three' ); // assuming you imploded $typer into $types Also note that your $binder array seems to have commas as elements. It would need to have just the actual parameters (so, count($arguments) would be 3). It looks like you've been making good progress with your script. It's a little tough to tell everything that you're doing here, but take a look at this and see if something like this helps simplify things at all: / Begin untested code snippet / $sql1 = 'SELECT whatever FROM table WHERE 1 ; $allowed_fields = array( 'Site' = 's', // fieldname is key, bind type is value 'MedRec' = 'i', ... ); $types = ''; $args = array(); foreach( $allowed_fields as $k = $type ) { if( !array_key_exists( $k, $_POST ) ) continue; $args[] = $_POST[$k]; $types .= $type; $sql1 .= AND ($key = ?) ; } $stmt = mysqli_prepare( $mysql_resource, $sql1 ); if( !call_user_func_array( 'mysqli_stmt_bind_param', array_unshift($args, $stmt, $types) ) ) throw new Exception( 'Error while binding parameters' ); / End untested code snippet / As indicated, I didn't test that script. It's just to illustrate an idea. Hope this helps, Matt On 09/19/2012 05:59 PM, Ethan Rosenberg, PhD wrote: Dear List - Thanks to all for your responses. Here is another one I wish to accomplish the following mysqli_stmt_bind_param($stmt, 'sis', $_POST['Site'], $_POST['MedRec'], $_POST['Sex']); This statemnt was hand coded. I wish to be able to generalize it. Therefore - $sql11 = SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age FROM Intake3 where 1 ; $allowed_fields = array ( 'Site' =$_POST['Site'], 'MedRec' = $_POST['MedRec'], 'Fname' = $_POST['Fname'], 'Lname' = $_POST['Lname'] , 'Phone' = $_POST['Phone'] , 'Height' = $_POST['Height'], 'Sex' = $_POST['Sex'], 'Hx' = $_POST['Hx'], 'Bday' = $_POST['Bday'], 'Age' = $_POST['Age'] ); $z0='$_POST'; $z0 .=['Site']; $z1='$_POST'; $z1 .=['MedRec']; $z2='$_POST'; $z2 .=['Fname']; . . . $indeces = array( 0 = array ( 'tpe'= 's', val = $z0 ), 1 = array ( tpe = i, val= $z1 ), . . $binder = array(); //array to hold variables $typer = array(); //array to hold variable type $position = -1; foreach ( $allowed_fields as $key = $val ) { $position = $position + 1; if ($val != '') { array_push($binder, $indeces[$position]['val']); array_push($binder, , ); array_push($typer, $indeces[$position]['tpe']); $sql11 .= AND ($key = ?) ; } } array_pop($binder); The above generates the following query: SELECT Site, MedRec, Fname, Lname, Phone, Height, Sex, Hx, Bday, Age FROM Intake3 where 1 AND (Site = ?) AND (MedRec = ?) AND (Sex = ?) //Construct the strings for the mysqli_stmt_bind_param statement $typ2 = implode($typer); $typ3 = '; $typ3 .=$typ2; $typ3 .= '; $bind3 = implode($binder); //This statement works perfectly. mysqli_stmt_bind_param($stmt, 'sis', $_POST['Site'], $_POST['MedRec'], $_POST['Sex']); //This one fails mysqli_stmt_bind_param($stmt, $typ3, $bind3); With the following error message: Warning: mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables echo br /$typ3;'sis' echo br / $bind3; $_POST['Site'], $_POST['MedRec'], $_POST['Sex'] Help and Advice, please Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
I'm a little confused here. You have a 15x3x3 set of data to display in a 3 column, 15 row datagrid? Are you displaying a single value in each cell of the datagrid, or all 3 values for the cell? How many variables are needed to uniquely select a piece of data from that 3-dimensional space? Can you provide a little more detail about the data? -Matt On 09/19/2012 11:44 AM, Bruno Sandivilli wrote: Anyone? Please. 2012/9/18 Bruno Sandivilli bruno.sandivi...@gmail.com Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
Bruno, This schema layout seems a little odd to me. Maybe I'm just misunderstanding the use. Correct me if I'm wrong here: Each row in the bill table is a customer? (or some other lookup value for which you're generating the data grid?) I assume the 15 fields from your original message are the 20 fields you are describing here? The values inside the Bill table are 100% unique and point to the Value table? You're trying to generate output that looks like this? CategoryRegisteredBilledTotal electricity_use 120,00 150,12102,36 electricity_demand130,00 150,12102,36 ... for a particular row in the Bill table where ID=something ? If this is true, I would consider normalizing this a bit more. If you break the columns in your bill table out into their own table, expand your values table a bit, you could end up with something like this: categories table: idcategory_name 1 electricity_use 2 electricity_demand ...... (where id is the primary key) bill_data table: ID_from_original_bill_tablecategory_idregistered billedtotal 1 1 120,00 150,12102,36 1 2 120,00 150,12102,36 ... (where (ID_from_original_bill_table, category_id) is the primary key) And do queries like this: SELECT c.category_name, bd.registered, bd.billed, bd.total FROM categories AS c JOIN bill_data AS bd ON c.id=bd.category_id WHERE bill_data.ID_from_original_bill_table=whatever; Or am I still not understanding correctly what you're trying to do? If you actually need to do a pivot you might want to look here: http://www.artfulsoftware.com/infotree/queries.php#78 -Matt On 09/19/2012 01:33 PM, Bruno Sandivilli wrote: Sorry for the lack of explanation. Again: I have to model a bill table. The bill have 20 fields. But for each field we have: *registered* value, *billed *value and* total value.* So i've created an *bill* table with *20 *fields (representing the bill fields, like: consumption, demand, etc ), and a *bill_values* table (with *id*,*registered*,*billed *and *total*). I'll try to ilustrate: Bill Table *ID | eletricity_use | eletricity_demand_ | eletricity_traffic_use | eletricity_penalties *| etc 1 101102103 104 *AND* Value Table *ID | Registered | Billed | Total* 101 120,00 150,12102,36 102 130,00 150,12102,36 103 150,00 150,12102,36 104 110,00 140,12102,36 better? Thanks! 2012/9/19 Jim Giner jim.gi...@albanyhandball.com On 9/19/2012 3:12 PM, Jim Giner wrote: On 9/18/2012 8:52 AM, Bruno Sandivilli wrote: Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! I don't get it either. You have 15 rows with 3 cols each. So? Display them. Query them. Big deal. What is the real difficulty here? I'm not seeing it. (I don't read it as 15x3x3 - at least that's not what he said since he said 'for each row he has 3 values') Continuing on - why is your visual structure at this point NOT the same as your physical structure? I don't think you're telling us what your real problem is here. We need more information. You have '15 row with 3 cols each', ie, 'each row has 3 values'. So - your table has 15 records in it, each with 3 columns. Add a key field to give each row an identity and that's it. No? Sorry - but again - this post is not showing me a clear problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Adding entry to /dev
Ethan, I think you should be a little more specific about what your end goal is. -Matt On 09/10/2012 07:41 PM, Ethan Rosenberg, PhD wrote: Dear list - How do I add a new entry to /dev; eg, /dev/sdb? Thanks, Ethan Rosenberg -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Problems w/ insert
Ethan, I am curious why you are using mysqli_stmt_bind_result() on a statement that is an INSERT query? I don't use mysqli, but as I read the documentation it seems to me that this method is intended to bind results from a SELECT query into variables so that you can simply call mysqli_stmt_fetch() in a loop and have the variables magically contain the data from the next row. Perhaps mysqli is confused because you are both mapping data to be bound (with mysqli_stmt_bind_param()) and to be returned (with mysqli_stmt_bind_result()) on the same INSERT query? If you are just trying to insert, you should only need mysqli_stmt_bind_param() (which, by my count, has one parameter too many right now in your code) and mysqli_stmt_execute() (and probably not mysql_stmt_bind_param() and mysql_stmt_fetch()). See Example #2 here: http://us.php.net/manual/en/mysqli-stmt.execute.php -Matt On 09/10/2012 08:29 PM, Karl DeSaulniers wrote: On Sep 10, 2012, at 7:06 PM, Ethan Rosenberg, PhD wrote: Dear list - Here is my code: $sql3 = select max(Indx) from Visit3; $result7 = mysqli_query($cxn, $sql3); $row7 = mysqli_fetch_array($result7, MYSQLI_BOTH); $Indx = $row7[0]; $sql2 = INSERT INTO Visit3(Indx, Site, MedRec, Notes, Weight, BMI, Date) VALUES(?, ?, ?, ?, ?, ?, ? ); mysqli_stmt_prepare( $stmt, $sql2 ); $_POST['Indx'] = $Indx; $_POST['Date'] = $Date; mysqli_stmt_bind_param($stmt, 'isisiis', $_POST['Indx'], $_POST['Site'], $_POST['MedRec'], $_POST['Notes'], $_POST['Weight'], $_POST['BMI'], $_POST['Date']); mysqli_execute($stmt); mysqli_stmt_bind_result($stmt, $_POST['Indx'], $_POST['Site'], $_POST['MedRec'], $_POST['Notes'], $_POST['Weight'], $_POST['BMI'], $_POST['Date']); *//The error is in this statement* mysqli_stmt_fetch($stmt); mysqli_stmt_close($stmt); *Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement* * * Help and advice, please. Ethan Rosenberg -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Maybe right here there needs to be something? mysqli_stmt_bind_result($stmt, ?, $_POST['Indx'], ... in the statement above you have. mysqli_stmt_bind_param($stmt, 'isisiis', $_POST['Indx'], ... what ever 'isisiis' is, you need to put somehting in your ...bind_result() I think. maybe.. mysqli_stmt_bind_result($stmt, '', $_POST['Indx'], or mysqli_stmt_bind_result($stmt, 'isisiis', $_POST['Indx'], sorry for not a more concrete answer. I am guessing a little. :) HTH, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysqli_connect ??
Jim, You likely have to install the mysqli package for your distribution. Try a search for something likeubuntu install php mysqli or centos install php mysqli I can tell you for ubuntu you probably would need to do something like this: sudo apt-get install php5-mysql If you're in a hosted environment, you might be out of luck unless they'll install it for you :-) But I would also be surprised if a hosted environment was missing this package. -Matt On 09/07/2012 01:36 PM, Jim Giner wrote: No - they are not. :( On 9/7/2012 1:32 PM, Bastien Koert wrote: On Fri, Sep 7, 2012 at 10:54 AM, Jim Giner jim.gi...@albanyhandball.com wrote: Experimenting with prepared statements - trying out for the first time. I keep getting a fatal error on this statement: mysqli_connect(.) Message is: Fatal error: Call to undefined function mysqli_connect() in /home/albany/php/sqli_db_connect_select.php on line 6 My host is running php 5.3.9 Can anyone clue me in? I copied my usage directly from the manual! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Check to see if mysqli_functions are enabled with phpinfo() -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PDO user question
Brandon: RAM is cheap, but not always expendable depending on what you're doing. Using count() on results from PDOStatement::fetchAll() is fine on small result sets. Keep in mind that this will certainly blow up on very large result sets, especially depending on your memory_limit for php. Avoiding this problem is the primary reason I use prepared statements for retrieving data. If you're working with small result sets you could avoid using prepared statements altogether, which would greatly simplify doing something like getting a row count. If you don't absolutely need your code to be portable across multiple database servers, I would encourage you to experiment with the rowCount() method and see if it works for your setup. (I ran a quick test in my mysql environment and it worked fine for a SELECT statement. Sqlite doesn't seem to support it because the actual sqlite library doesn't support it. Haven't looked into postgres or others.) Another thing to be aware of (at least with mysql), and speaking to Lester's point, is that you can use SQL_CALC_FOUND_ROWS (with mysql, anyway) to get a total count when you are using LIMITs on your result set (which would be advisable if you're returning these results in a web interface). -Matt On 09/09/2012 02:40 PM, brandon wrote: That's how I'd do it. Extend the PDO interface on your abstract class to include a num_rows() method that utilizes that higher level count($this-result). It might be a little more overhead... but RAM is cheap... and there's always forking/extending the library in C/C++... -Brandon On 2012-09-09 11:49, Michael Stowe wrote: How are you using the number? Probably the easiest way is to utilize PDOStatement::FetchAll() and then do a count() on that result set. - Mike Sent from my iPhone On Sep 9, 2012, at 11:42 AM, Stefan Wixfort stefan.wixf...@gmx.de wrote: Hi Jim I've had some success with querying using pdo and prepared statements as well. One thing that I'm curious about is How does one handle the need to get the number of rows returned by a Select? The documentation is very clear that PDO doesn't return that value for a Select statement (depending upon driver?) and there were a couple of solutions that made no sense to me. There was even one that did a completely separate query just to get the row count which makes even less sense. I believe you are referring to SELECT COUNT(*)... Because I couldn't find a different way I use that. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Wow, this is weird
David, Try using $_FILES['image']['name'] (like you do elsewhere in the script) instead of $_POST['image'] ? Also, if you're not just doing a simple test script, you should probably use mysql_real_escape_string() on your parameters in the query. You may want to also look at the alternative mysql drivers, as the API you're using is planned for deprecation at some point. http://php.net/manual/en/mysql.php http://www.php.net/manual/en/mysqlinfo.api.choosing.php (this page seems to leave out the mysqlnd driver) -Matt On 08/27/2012 03:04 PM, David McGlone wrote: Hi Everyone I have written some code that works almost 100% like I expect, but for the life of me I cannot figure out why the image name is not being inserted into the database. I've posted the code on pastebin, can anyone spot something amis? http://pastebin.com/mGBFrxwP David M. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Wow, this is weird
You are probably having problems with Array[name] because of the way you are inserting variables into the query. You're doing this: this is my string with '$_POST[image][name]'. Instead, try this: This is my string with '{$_POST['image']['name']}' in it. or this: This is my string with ' . $_POST['image']['name'] , ' in it, using the concatenate operator. -Matt On 08/27/2012 04:43 PM, David McGlone wrote: On Monday, August 27, 2012 03:36:41 PM Matt Pelmear wrote: David, Try using $_FILES['image']['name'] (like you do elsewhere in the script) instead of $_POST['image'] ? I just your tried suggestion and now it does insert something into the database, but it's inserting Array[name] instead of the intended image name. So I believe my first mistake was, I should have realized the line $_FILES['image']['name'] is an array, so of course the image name isn't going to be there until I extract it from the array. Correct? Also, if you're not just doing a simple test script, you should probably use mysql_real_escape_string() on your parameters in the query. I started with a simple form and I'm working my way up the ladder, from posting stuff, sticking stuff in a db using stuff like getimagesize(), and then next I'm going to work some more with sanitization of forms and inserted data in the db. :-) By the time I'm done with this, I'm hoping to have a much greater understanding of FILTER_SANITIZE_STRING, getimagesize, and whatever else I would need to know about forms and inserting into db's. :-) You may want to also look at the alternative mysql drivers, as the API you're using is planned for deprecation at some point. http://php.net/manual/en/mysql.php http://www.php.net/manual/en/mysqlinfo.api.choosing.php (this page seems to leave out the mysqlnd driver) Thank you for the heads up, I'll most certainly take a look at this. David M. -Matt On 08/27/2012 03:04 PM, David McGlone wrote: Hi Everyone I have written some code that works almost 100% like I expect, but for the life of me I cannot figure out why the image name is not being inserted into the database. I've posted the code on pastebin, can anyone spot something amis? http://pastebin.com/mGBFrxwP David M. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: PDO Mysql data truncation missing error
Amit, Thanks for the suggestion. Unfortunately, even when I explicitly enable E_WARNING or even E_ALL error reporting in php I still do not receive any indication that data was truncated on insert. -Matt On 08/21/2012 07:09 AM, Amit Tandon wrote: Dear Matt, Even with MySQL, u get the warning as the show warnings is enabled. With php bound scripts, you have to check warnings to see the warnings. However, php is quite flexible and you an use error_reporting(E_WARNING) for non-fatal error reporting regds amit On Aug 21, 2012 10:41 AM, Matt Pelmear mjpelm...@gmail.com mailto:mjpelm...@gmail.com wrote: On 08/21/2012 01:08 AM, David Robley wrote: Matt Pelmear wrote: Hello, I'm trying to detect data truncation on insert to MySQL using PDO. As far as I can tell, this gets reported at least in some cases (ex: http://drupal.org/node/1528628), but I have been unable to see this myself. The test table I'm using has a column that is VARCHAR(5): mysql describe test; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(10) unsigned | YES | | NULL| | | data | varchar(5) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) My test script inserts a ten character string into the 5 character column: PHP TEST CODE $pdo = new PDO( 'mysql:host=localhost;dbname=test', 'username', 'password' ); $pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $retval = $pdo-query( 'INSERT INTO `test` (data) VALUES (1234567890)' ); print_r( $retval ); END TEST CODE This results in a new row in the `test` table, truncated after the 5th character as expected, but the truncation is not reported. (running in php 5.3.2 and 5.3.4) Running the same query directly in the mysql command line shows a warning as expected. Am I missing something simple here? Thanks, Matt Caveat: I don't use PDO but maybe the PDO::ERRMODE_WARNING attribute may do what you want? Cheers Interesting idea. I gave it a try, but got the same result! I think PDO::ERRMODE_WARNING tells PDO to give PHP warnings versus throw exceptions when there are problems (if I'm understanding it properly). -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PDO Mysql data truncation missing error
Hello, I'm trying to detect data truncation on insert to MySQL using PDO. As far as I can tell, this gets reported at least in some cases (ex: http://drupal.org/node/1528628), but I have been unable to see this myself. The test table I'm using has a column that is VARCHAR(5): mysql describe test; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(10) unsigned | YES | | NULL| | | data | varchar(5) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) My test script inserts a ten character string into the 5 character column: PHP TEST CODE $pdo = new PDO( 'mysql:host=localhost;dbname=test', 'username', 'password' ); $pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $retval = $pdo-query( 'INSERT INTO `test` (data) VALUES (1234567890)' ); print_r( $retval ); END TEST CODE This results in a new row in the `test` table, truncated after the 5th character as expected, but the truncation is not reported. (running in php 5.3.2 and 5.3.4) Running the same query directly in the mysql command line shows a warning as expected. Am I missing something simple here? Thanks, Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: PDO Mysql data truncation missing error
On 08/21/2012 01:08 AM, David Robley wrote: Matt Pelmear wrote: Hello, I'm trying to detect data truncation on insert to MySQL using PDO. As far as I can tell, this gets reported at least in some cases (ex: http://drupal.org/node/1528628), but I have been unable to see this myself. The test table I'm using has a column that is VARCHAR(5): mysql describe test; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(10) unsigned | YES | | NULL| | | data | varchar(5) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) My test script inserts a ten character string into the 5 character column: PHP TEST CODE $pdo = new PDO( 'mysql:host=localhost;dbname=test', 'username', 'password' ); $pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $retval = $pdo-query( 'INSERT INTO `test` (data) VALUES (1234567890)' ); print_r( $retval ); END TEST CODE This results in a new row in the `test` table, truncated after the 5th character as expected, but the truncation is not reported. (running in php 5.3.2 and 5.3.4) Running the same query directly in the mysql command line shows a warning as expected. Am I missing something simple here? Thanks, Matt Caveat: I don't use PDO but maybe the PDO::ERRMODE_WARNING attribute may do what you want? Cheers Interesting idea. I gave it a try, but got the same result! I think PDO::ERRMODE_WARNING tells PDO to give PHP warnings versus throw exceptions when there are problems (if I'm understanding it properly). -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php