Re: [PHP-DB] Re: Getting Results
On 21 January 2011 12:39, Karl DeSaulniers k...@designdrumm.com wrote: Very informative, Thank you Richard. I will have to flag this one and come back to it. My database structure may require something of this measure down the line with the auditing ability I plan on building into the admin section. Never heard of USE AdventureWorks. Is that a SQL system function? Or just a table in the examples database? USE [1] is a Transact SQL statement (MS call their SQL language T-SQL). AdventureWorks is one of the demo DBs MS supply to help people learn about using their server. Regards, Richard. [1] http://msdn.microsoft.com/en-us/library/ms188366.aspx -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Getting Results
Thanks Robbyka, The username would be what your referring to with the .idvip So.. select username, userlevel,email, timestamp from .USERS., .VIP. where .VIP..username=.USERS..username; yes? Karl On Jan 20, 2011, at 7:24 PM, Robbyka Gheo wrote: i think its wrong, try this script? select username, userlevel,email, timestamp from users, viptable where viptable.idvip=users.idvip; i asumsion the viptable.idvip is relation with users.idvip; On Fri, Jan 21, 2011 at 9:01 AM, Karl DeSaulniers k...@designdrumm.com wrote: Hello, Can anyone tell me if I am doing this right? Or how to do this right? Not sure on what to search for. What the search term would be for this type of call to the database. $q = SELECT username,userlevel,email,timestamp FROM .USERS. ORDER BY username FROM .VIP. ; I am basically trying to get a list of user info from the user table based on the usernames that are in the vip table. TIA, Karl DeSaulniers Design Drumm http://designdrumm.com Karl DeSaulniers Design Drumm http://designdrumm.com
Re: [PHP-DB] Getting Results
Hi Robbyka, .USERS. and .VIP. are constant variables used to hide the actual names of the tables for security reasons. Those variables will have content. Not sure what you mean by semi locon like = from .users; The user names are set up to be unique. No two user names can be identical and the users in the VIP table come from the USERS table to begin with. I am simply trying to get the VIP users info without having to fill the VIP table up with duplicate info that is already in the users table. The only thing going into the VIP table is the username, and a time stamp. Thanks for your help though, I think that is what I needed. Still need to test. Best, Karl On Jan 20, 2011, at 7:50 PM, Robbyka Gheo wrote: how many your tables? why you use semi locon like = from .users; i think if you use that the query look .users as variabel and you must have content it that variabel.. and better use unique id both of vip and users table. On Fri, Jan 21, 2011 at 9:39 AM, Karl DeSaulniers k...@designdrumm.com wrote: Thanks Robbyka, The username would be what your referring to with the .idvip So.. select username, userlevel,email, timestamp from .USERS., .VIP. where .VIP..username=.USERS..username; yes? Karl On Jan 20, 2011, at 7:24 PM, Robbyka Gheo wrote: i think its wrong, try this script? select username, userlevel,email, timestamp from users, viptable where viptable.idvip=users.idvip; i asumsion the viptable.idvip is relation with users.idvip; On Fri, Jan 21, 2011 at 9:01 AM, Karl DeSaulniers k...@designdrumm.com wrote: Hello, Can anyone tell me if I am doing this right? Or how to do this right? Not sure on what to search for. What the search term would be for this type of call to the database. $q = SELECT username,userlevel,email,timestamp FROM .USERS. ORDER BY username FROM .VIP. ; I am basically trying to get a list of user info from the user table based on the usernames that are in the vip table. TIA, Karl DeSaulniers Design Drumm http://designdrumm.com Karl DeSaulniers Design Drumm http://designdrumm.com Karl DeSaulniers Design Drumm http://designdrumm.com
Re: [PHP-DB] Configuring PHP with GD and JPEG support
Hey Mike, A google search for PHP is unable to call the GD function reveiled some interesting results. Have a look see. Best, Karl Sent from my iPhone On Jan 16, 2011, at 1:35 AM, mike dorian united.ea...@hotmail.com wrote: Just tried it with the following but still fails. All I'm getting is a blank page. php? function create_image() { //Let's generate a totally random string using md5 $md5_hash = md5(rand(0,999)); //We don't need a 32 character long string so we trim it down to 5 $security_code = substr($md5_hash, 15, 5); //Set the session to store the security code $_SESSION[security_code] = $security_code; //Set the image width and height $width = 100; $height = 20; //Create the image resource $image = ImageCreate($width, $height); //We are making three colors, white, black and gray $white = ImageColorAllocate($image, 255, 255, 255); $black = ImageColorAllocate($image, 0, 0, 0); $grey = ImageColorAllocate($image, 204, 204, 204); //Make the background black ImageFill($image, 0, 0, $black); //Add randomly generated string in white to the image ImageString($image, 3, 30, 3, $security_code, $white); //Throw in some lines to make it a little bit harder for any bots to break ImageRectangle($image,0,0,$width-1,$height-1,$grey); imageline($image, 0, $height/2, $width, $height/2, $grey); imageline($image, $width/2, 0, $width/2, $height, $grey); //Tell the browser what kind of file is come in header(Content-Type: image/jpeg); //Output the newly created image in jpeg format ImageJpeg($image); //Free up resources ImageDestroy($image); } create_image(); ? From: k...@designdrumm.com Date: Mon, 10 Jan 2011 17:10:35 -0600 To: php-db@lists.php.net Subject: Re: [PHP-DB] Configuring PHP with GD and JPEG support On Jan 10, 2011, at 9:35 AM, mike dorian wrote: Hello, Questions with regards to compiling PHP to support GD with JPEG on 64-bit CentOS 5.5. 1) Specifying folder for jpeg library When I execute the following command, am I saying the full path to libdir is at /usr/source/lib64? ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with-gd -- with-jpeg-dir=/usr/source --with-libdir=lib64 Should I have done the following instead? ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with-gd -- with-jpeg-dir=/usr/source/lib64 2) After copying the compiled JPEG libraries to the the lib64 directory, PHP is still unable to call the GD functions (ie ImageCreateFromJpeg) properly. Once I've done the following. ./configure --enable-shared make make install I have the following files installed. Meanwhile the /usr/local/ lib64 directory doesn't have these files. Despite copying the whole set to /usr/local/lib64, the above configure command still doesn't work. /usr/local/lib/libjpeg.a /usr/local/lib/libjpeg.la /usr/local/lib/libjpeg.so - libjpeg.so.8.0.2 /usr/local/lib/libjpeg.so.8 - libjpeg.so.8.0.2 /usr/local/lib/libjpeg.so.8.0.2 The following displays a blank page on my Firefox. ?php header('Content-Type: image/jpeg'); $image_file_path = '/usr/local/apache2/htdocs/image.jpg'; $image_file_path; $im = @ImageCreateFromJpeg($image_file_path); echo $im; imagejpeg($im); echo End: imagedestroy($im); ? Hi Mike, Dont know about your server config, but maybe try using this create image script and see. function create_image() { //Let's generate a totally random string using md5 $md5_hash = md5(rand(0,999)); //We don't need a 32 character long string so we trim it down to 5 $security_code = substr($md5_hash, 15, 5); //Set the session to store the security code $_SESSION[security_code] = $security_code; //Set the image width and height $width = 100; $height = 20; //Create the image resource $image = ImageCreate($width, $height); //We are making three colors, white, black and gray $white = ImageColorAllocate($image, 255, 255, 255); $black = ImageColorAllocate($image, 0, 0, 0); $grey = ImageColorAllocate($image, 204, 204, 204); //Make the background black ImageFill($image, 0, 0, $black); //Add randomly generated string in white to the image ImageString($image, 3, 30, 3, $security_code, $white); //Throw in some lines to make it a little bit harder for any bots to break ImageRectangle($image,0,0,$width-1,$height-1,$grey); imageline($image, 0, $height/2, $width, $height/2, $grey); imageline($image, $width/2, 0, $width/2, $height, $grey); //Tell the browser what kind of file is come in header(Content-Type: image/jpeg); //Output the newly created image in jpeg format ImageJpeg($image); //Free up resources ImageDestroy($image); } Was taken from the WebCheatSheet.com - Create CAPTCHA Protection. Namely to see how they are using the ImageJpeg, not the whole code. But, at least you will have a code you know produces an image if anything and you
RE: [PHP-DB] Connecting to MySQL
Resolved itself, just had to reboot. Works fine now. Thanks anyway :) From: cristiano_di_bu...@hotmail.com To: phps...@gmail.com Subject: RE: [PHP-DB] Connecting to MySQL Date: Fri, 14 Jan 2011 21:58:33 +0100 It outputs: PDO PDO support enabled PDO drivers mysql, odbc, sqlite From: phps...@gmail.com Date: Fri, 14 Jan 2011 15:52:55 -0500 To: cristiano_di_bu...@hotmail.com CC: php-db@lists.php.net Subject: Re: [PHP-DB] Connecting to MySQL On Fri, Jan 14, 2011 at 2:15 PM, Cristiano Di Buduo wrote: I just downloaded both PHP and MySQL, both newest stable versions, both are working well, but when i try{} to : $dbh = new PDO('mysql:host=localhost;dbname=test', user, passwd); i catch{} a could not find driver error. Any help with this? I think the proper modules were set up in the php.ini by default. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Check the php.ini or run to see if the PDO is enabled -- Bastien Cat, the other other white meat -- 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] Connecting to MySQL
note that you'll have to restart apache after any changes to httpd.conf/php.ini in your case httpd.conf was changed when u installed PDO Kranthi. http://goo.gl/e6t3 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Configuring PHP with GD and JPEG support
Just tried it with the following but still fails. All I'm getting is a blank page. php? function create_image() { //Let's generate a totally random string using md5 $md5_hash = md5(rand(0,999)); //We don't need a 32 character long string so we trim it down to 5 $security_code = substr($md5_hash, 15, 5); //Set the session to store the security code $_SESSION[security_code] = $security_code; //Set the image width and height $width = 100; $height = 20; //Create the image resource $image = ImageCreate($width, $height); //We are making three colors, white, black and gray $white = ImageColorAllocate($image, 255, 255, 255); $black = ImageColorAllocate($image, 0, 0, 0); $grey = ImageColorAllocate($image, 204, 204, 204); //Make the background black ImageFill($image, 0, 0, $black); //Add randomly generated string in white to the image ImageString($image, 3, 30, 3, $security_code, $white); //Throw in some lines to make it a little bit harder for any bots to break ImageRectangle($image,0,0,$width-1,$height-1,$grey); imageline($image, 0, $height/2, $width, $height/2, $grey); imageline($image, $width/2, 0, $width/2, $height, $grey); //Tell the browser what kind of file is come in header(Content-Type: image/jpeg); //Output the newly created image in jpeg format ImageJpeg($image); //Free up resources ImageDestroy($image); } create_image(); ? From: k...@designdrumm.com Date: Mon, 10 Jan 2011 17:10:35 -0600 To: php-db@lists.php.net Subject: Re: [PHP-DB] Configuring PHP with GD and JPEG support On Jan 10, 2011, at 9:35 AM, mike dorian wrote: Hello, Questions with regards to compiling PHP to support GD with JPEG on 64-bit CentOS 5.5. 1) Specifying folder for jpeg library When I execute the following command, am I saying the full path to libdir is at /usr/source/lib64? ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with-gd -- with-jpeg-dir=/usr/source --with-libdir=lib64 Should I have done the following instead? ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with-gd -- with-jpeg-dir=/usr/source/lib64 2) After copying the compiled JPEG libraries to the the lib64 directory, PHP is still unable to call the GD functions (ie ImageCreateFromJpeg) properly. Once I've done the following. ./configure --enable-shared make make install I have the following files installed. Meanwhile the /usr/local/ lib64 directory doesn't have these files. Despite copying the whole set to /usr/local/lib64, the above configure command still doesn't work. /usr/local/lib/libjpeg.a /usr/local/lib/libjpeg.la /usr/local/lib/libjpeg.so - libjpeg.so.8.0.2 /usr/local/lib/libjpeg.so.8 - libjpeg.so.8.0.2 /usr/local/lib/libjpeg.so.8.0.2 The following displays a blank page on my Firefox. ?php header('Content-Type: image/jpeg'); $image_file_path = '/usr/local/apache2/htdocs/image.jpg'; $image_file_path; $im = @ImageCreateFromJpeg($image_file_path); echo $im; imagejpeg($im); echo End: imagedestroy($im); ? Hi Mike, Dont know about your server config, but maybe try using this create image script and see. function create_image() { //Let's generate a totally random string using md5 $md5_hash = md5(rand(0,999)); //We don't need a 32 character long string so we trim it down to 5 $security_code = substr($md5_hash, 15, 5); //Set the session to store the security code $_SESSION[security_code] = $security_code; //Set the image width and height $width = 100; $height = 20; //Create the image resource $image = ImageCreate($width, $height); //We are making three colors, white, black and gray $white = ImageColorAllocate($image, 255, 255, 255); $black = ImageColorAllocate($image, 0, 0, 0); $grey = ImageColorAllocate($image, 204, 204, 204); //Make the background black ImageFill($image, 0, 0, $black); //Add randomly generated string in white to the image ImageString($image, 3, 30, 3, $security_code, $white); //Throw in some lines to make it a little bit harder for any bots to break ImageRectangle($image,0,0,$width-1,$height-1,$grey); imageline($image, 0, $height/2, $width, $height/2, $grey); imageline($image, $width/2, 0, $width/2, $height, $grey); //Tell the browser what kind of file is come in header(Content-Type: image/jpeg); //Output the newly created image in jpeg format ImageJpeg($image); //Free up resources ImageDestroy($image); } Was taken from the WebCheatSheet.com - Create CAPTCHA Protection. Namely to see how they are using the ImageJpeg, not the whole code. But, at least you will have a code you know produces an image
Re: [PHP-DB] Connecting to MySQL
On Fri, Jan 14, 2011 at 2:15 PM, Cristiano Di Buduo cristiano_di_bu...@hotmail.com wrote: I just downloaded both PHP and MySQL, both newest stable versions, both are working well, but when i try{} to : $dbh = new PDO('mysql:host=localhost;dbname=test', user, passwd); i catch{} a could not find driver error. Any help with this? I think the proper modules were set up in the php.ini by default. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Check the php.ini or run ?php phpinfo(); ? to see if the PDO is enabled -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Oracle PDO UTF-8 problem
Glad you resolved it. Overall, I have to recommend the OCI8 extension over the unmaintained (like much of PDO) PDO_OCI extension. Chris On 01/13/2011 11:36 PM, Karsten Lehmann wrote: I found out that the error depends on the wrong character set of the Oracle Database. In my case this was a Western Europ instance of the Database. The same data on a UTF8 instance was displayed as excpected. Am 13.01.2011 10:42, schrieb Karsten Lehmann: Doesn't work. I got the following error after i set the parameter in the PDO construct Fatal error: Undefined class constant 'MYSQL_ATTR_MAX_BUFFER_SIZE' IMHO this is only a MYSQL parameter but i worked with oracle. Am 13.01.2011 10:27, schrieb kesavan trichy rengarajan: Try increasing this: PDO::MYSQL_ATTR_MAX_BUFFER_SIZE More info here: http://php.net/manual/en/ref.pdo-mysql.php On Thu, Jan 13, 2011 at 7:23 PM, Karsten Lehmannlehm...@maqsima.de wrote: Hello I try to read a 2000 character long string out of database. The database is an oracle 10g Express Edition with UTF-8 character set. To read the string we use the PDO abstraction layer with oci-driver. If the 2000 character contains one or more UTF-8 characters we get the problem that PDO truncate the result of the database, with the following message: Warning: PDOStatement::fetchAll() [pdostatement.fetchall]: column 5 data was too large for buffer and was truncated to fit it in C:\wamp\www\wp\bp.php on line 115 The definition of the database column is TESTSTRING VARCHAR2(2000 CHAR) CHAR means that we can store up to 2000 characters independent from the encoding of the character (e.g. UTF8). I also init the PDO with UTF8 $dbh = new PDO('oci:dbname=' . $db_name . ';charset=UTF8', $db_user, $db_pw); If i read the same string with java and JDBC-driver it works fine, so i can exclude a database error. How i have to configre PHP/PDO to be aware of the truncation of the result string? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Email: christopher.jo...@oracle.com Tel: +1 650 506 8630 Blog: http://blogs.oracle.com/opal/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Oracle PDO UTF-8 problem
Try increasing this: PDO::MYSQL_ATTR_MAX_BUFFER_SIZE More info here: http://php.net/manual/en/ref.pdo-mysql.php On Thu, Jan 13, 2011 at 7:23 PM, Karsten Lehmann lehm...@maqsima.de wrote: Hello I try to read a 2000 character long string out of database. The database is an oracle 10g Express Edition with UTF-8 character set. To read the string we use the PDO abstraction layer with oci-driver. If the 2000 character contains one or more UTF-8 characters we get the problem that PDO truncate the result of the database, with the following message: Warning: PDOStatement::fetchAll() [pdostatement.fetchall]: column 5 data was too large for buffer and was truncated to fit it in C:\wamp\www\wp\bp.php on line 115 The definition of the database column is TESTSTRING VARCHAR2(2000 CHAR) CHAR means that we can store up to 2000 characters independent from the encoding of the character (e.g. UTF8). I also init the PDO with UTF8 $dbh = new PDO('oci:dbname=' . $db_name . ';charset=UTF8', $db_user, $db_pw); If i read the same string with java and JDBC-driver it works fine, so i can exclude a database error. How i have to configre PHP/PDO to be aware of the truncation of the result string? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Oracle PDO UTF-8 problem
Doesn't work. I got the following error after i set the parameter in the PDO construct Fatal error: Undefined class constant 'MYSQL_ATTR_MAX_BUFFER_SIZE' IMHO this is only a MYSQL parameter but i worked with oracle. Am 13.01.2011 10:27, schrieb kesavan trichy rengarajan: Try increasing this: PDO::MYSQL_ATTR_MAX_BUFFER_SIZE More info here: http://php.net/manual/en/ref.pdo-mysql.php On Thu, Jan 13, 2011 at 7:23 PM, Karsten Lehmannlehm...@maqsima.de wrote: Hello I try to read a 2000 character long string out of database. The database is an oracle 10g Express Edition with UTF-8 character set. To read the string we use the PDO abstraction layer with oci-driver. If the 2000 character contains one or more UTF-8 characters we get the problem that PDO truncate the result of the database, with the following message: Warning: PDOStatement::fetchAll() [pdostatement.fetchall]: column 5 data was too large for buffer and was truncated to fit it in C:\wamp\www\wp\bp.php on line 115 The definition of the database column is TESTSTRING VARCHAR2(2000 CHAR) CHAR means that we can store up to 2000 characters independent from the encoding of the character (e.g. UTF8). I also init the PDO with UTF8 $dbh = new PDO('oci:dbname=' . $db_name . ';charset=UTF8', $db_user, $db_pw); If i read the same string with java and JDBC-driver it works fine, so i can exclude a database error. How i have to configre PHP/PDO to be aware of the truncation of the result string? -- 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] Query syntax error?
you have renamed places_data table to 'a' 2011/1/13 Harvey har...@harveyk.com: Hello, I have a query on a page that used to work fine, but is now generating an error. I assume that the version of php or mysql was updated on the webhost server or something like that? Here is the query: select count(places_data.place_id) as areacount, boroughs.borough_name as boroname, area.area_name as areaname, area.area_id as areaid from places_data a inner join boroughs b on a.area_fid = b.area_id inner join area c on c.borough_fid = b.borough_id where places_data.on_off_fid = 2 . $cat_query . group by c.area_name order by b.borough_name ASC, c.area_name ASC And here is the error message: Unknown column 'places_data.place_id' in 'field list' But place_id is a field in places_data table. Any ideas? Thanks! Harvey -- 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] Query syntax error?
Thanks, I got it working now, had to use the a/b/c thing a few times -Original Message- From: jose [mailto:jojap...@gmail.com] Sent: Thursday, January 13, 2011 9:52 AM Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Query syntax error? you have renamed places_data table to 'a' 2011/1/13 Harvey har...@harveyk.com: Hello, I have a query on a page that used to work fine, but is now generating an error. I assume that the version of php or mysql was updated on the webhost server or something like that? Here is the query: select count(places_data.place_id) as areacount, boroughs.borough_name as boroname, area.area_name as areaname, area.area_id as areaid from places_data a inner join boroughs b on a.area_fid = b.area_id inner join area c on c.borough_fid = b.borough_id where places_data.on_off_fid = 2 . $cat_query . group by c.area_name order by b.borough_name ASC, c.area_name ASC And here is the error message: Unknown column 'places_data.place_id' in 'field list' But place_id is a field in places_data table. Any ideas? Thanks! Harvey -- 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Oracle PDO UTF-8 problem
I found out that the error depends on the wrong character set of the Oracle Database. In my case this was a Western Europ instance of the Database. The same data on a UTF8 instance was displayed as excpected. Am 13.01.2011 10:42, schrieb Karsten Lehmann: Doesn't work. I got the following error after i set the parameter in the PDO construct Fatal error: Undefined class constant 'MYSQL_ATTR_MAX_BUFFER_SIZE' IMHO this is only a MYSQL parameter but i worked with oracle. Am 13.01.2011 10:27, schrieb kesavan trichy rengarajan: Try increasing this: PDO::MYSQL_ATTR_MAX_BUFFER_SIZE More info here: http://php.net/manual/en/ref.pdo-mysql.php On Thu, Jan 13, 2011 at 7:23 PM, Karsten Lehmannlehm...@maqsima.de wrote: Hello I try to read a 2000 character long string out of database. The database is an oracle 10g Express Edition with UTF-8 character set. To read the string we use the PDO abstraction layer with oci-driver. If the 2000 character contains one or more UTF-8 characters we get the problem that PDO truncate the result of the database, with the following message: Warning: PDOStatement::fetchAll() [pdostatement.fetchall]: column 5 data was too large for buffer and was truncated to fit it in C:\wamp\www\wp\bp.php on line 115 The definition of the database column is TESTSTRING VARCHAR2(2000 CHAR) CHAR means that we can store up to 2000 characters independent from the encoding of the character (e.g. UTF8). I also init the PDO with UTF8 $dbh = new PDO('oci:dbname=' . $db_name . ';charset=UTF8', $db_user, $db_pw); If i read the same string with java and JDBC-driver it works fine, so i can exclude a database error. How i have to configre PHP/PDO to be aware of the truncation of the result string? -- 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] Configuring PHP with GD and JPEG support
On Jan 10, 2011, at 9:35 AM, mike dorian wrote: Hello, Questions with regards to compiling PHP to support GD with JPEG on 64-bit CentOS 5.5. 1) Specifying folder for jpeg library When I execute the following command, am I saying the full path to libdir is at /usr/source/lib64? ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with-gd -- with-jpeg-dir=/usr/source --with-libdir=lib64 Should I have done the following instead? ./configure --with-apxs2=/usr/local/apache2/bin/apxs --with-gd -- with-jpeg-dir=/usr/source/lib64 2) After copying the compiled JPEG libraries to the the lib64 directory, PHP is still unable to call the GD functions (ie ImageCreateFromJpeg) properly. Once I've done the following. ./configure --enable-shared make make install I have the following files installed. Meanwhile the /usr/local/ lib64 directory doesn't have these files. Despite copying the whole set to /usr/local/lib64, the above configure command still doesn't work. /usr/local/lib/libjpeg.a /usr/local/lib/libjpeg.la /usr/local/lib/libjpeg.so - libjpeg.so.8.0.2 /usr/local/lib/libjpeg.so.8 - libjpeg.so.8.0.2 /usr/local/lib/libjpeg.so.8.0.2 The following displays a blank page on my Firefox. ?php header('Content-Type: image/jpeg'); $image_file_path = '/usr/local/apache2/htdocs/image.jpg'; $image_file_path; $im = @ImageCreateFromJpeg($image_file_path); echo $im; imagejpeg($im); echo End: imagedestroy($im); ? Hi Mike, Dont know about your server config, but maybe try using this create image script and see. function create_image() { //Let's generate a totally random string using md5 $md5_hash = md5(rand(0,999)); //We don't need a 32 character long string so we trim it down to 5 $security_code = substr($md5_hash, 15, 5); //Set the session to store the security code $_SESSION[security_code] = $security_code; //Set the image width and height $width = 100; $height = 20; //Create the image resource $image = ImageCreate($width, $height); //We are making three colors, white, black and gray $white = ImageColorAllocate($image, 255, 255, 255); $black = ImageColorAllocate($image, 0, 0, 0); $grey = ImageColorAllocate($image, 204, 204, 204); //Make the background black ImageFill($image, 0, 0, $black); //Add randomly generated string in white to the image ImageString($image, 3, 30, 3, $security_code, $white); //Throw in some lines to make it a little bit harder for any bots to break ImageRectangle($image,0,0,$width-1,$height-1,$grey); imageline($image, 0, $height/2, $width, $height/2, $grey); imageline($image, $width/2, 0, $width/2, $height, $grey); //Tell the browser what kind of file is come in header(Content-Type: image/jpeg); //Output the newly created image in jpeg format ImageJpeg($image); //Free up resources ImageDestroy($image); } Was taken from the WebCheatSheet.com - Create CAPTCHA Protection. Namely to see how they are using the ImageJpeg, not the whole code. But, at least you will have a code you know produces an image if anything and you can shorten the things on the list to check. ;) 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] Connecting PHP to MySQL on Windows - RESOLVED
Hi, Thanks to everyone who assisted me. Removing the line: ::1 localhost from the c:\windows\system32\drivers\etc\hosts file did the trick and allowed me to access MySQL databases without any problems. I confirmed this was the correct solution by re-adding the line and seeing it fail again, removing the line again and successfully connecting. So the problem was nothing to do with Apache, PHP or MySQL directly but was a local networking issue. Of course, the hosts file on my working Windows XP computer at home did not have this line in the hosts file Many thanks again. Mike michaelgmann...@gmail.com On Sat, Jan 8, 2011 at 7:06 AM, Gavin Chalkley gavin.chalk...@gmail.comwrote: Mike, A plain HOST file should have the commented part at the top, and only 1 reference to localhost unless you are creating Virtual hosts. I would suggest removing the ::1 line BR Gavin On Fri, Jan 7, 2011 at 4:18 AM, michael manning michaelgmann...@gmail.comwrote: Hi, I swapped out 'localhost for '127.0.0.1' in the url but this had no effect. I had a look at my hosts file at c:\windows\system32\drivers\etc and this contained: # Copyright (c) 1993-2006 Microsoft Corp. # # This is a sample HOSTS file used by Microsoft TCP/IP for Windows. # # This file contains the mappings of IP addresses to host names. Each # entry should be kept on an individual line. The IP address should # be placed in the first column followed by the corresponding host name. # The IP address and the host name should be separated by at least one # space. # # Additionally, comments (such as these) may be inserted on individual # lines or following the machine name denoted by a '#' symbol. # # For example: # # 102.54.94.97 rhino.acme.com # source server # 38.25.63.10 x.acme.com # x client host 127.0.0.1 localhost ::1 localhost and so this seems fine as well. cheers Mike On Fri, Jan 7, 2011 at 1:25 PM, Daniel Brown danbr...@php.net wrote: On Thu, Jan 6, 2011 at 21:12, michael manning michaelgmann...@gmail.com wrote: Hi Daniel, When testing with the CLI, netstat shows: [snip!] What happens when you swap out 'localhost' for '127.0.0.1' in the connection routine? If that works, check to make sure your hosts file properly designates 127.0.0.1 as 'localhost' (by editing c:\windows\system32\drivers\etc\hosts I believe been a while since I've done it on Windows). -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- Best regards, Gavin C
Re: [PHP-DB] Can't find sqlite_open
Hello, I have a script that is calling sqlite_open, however, sqlite_open can't be found on my systems. I'm running RHEL5 and I get the following relevant output from php -i: PDO support = enabled PDO drivers = odbc, sqlite pdo_sqlite PDO Driver for SQLite 3.x = enabled PECL Module version = 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6 2006/01/01 12:50:12 sniper Exp $ SQLite Library = 3.3.6 From searching around, it seems like there is a PDO version, which looks to be enabled here, and a older version, which would provide sqlite_open. Is that correct? sqlite_open is not a PDO statement. It belongs to the PECL SQLite extension. This extension is quite old and is superseded by the SQLite3 extension and/or PDO. Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] combining the results of mysql query and finding the unique tuples in php
On 1/7/2011 12:10 AM, Fahim M wrote: Hi I have a certain number of mysql tables(relation), say 50, some of them having 5 fields and some with 6 fields. a particular search item may be found in multiple tables with multiple rows. I am using a loop to find all those. My problem is I want to first combine all those results and then find all the unique entries. (the query results may overlap). What is the best way to do it? If you're looking for unique results, do a UNION query for all the tables. You'll need to add a dummy field for the 5-field tables, and make sure the field names are the same (use oldfieldname AS newfieldname to make sure everything ends up the correct result column). UNION queries automatically remove duplicates. Toby -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] combining the results of mysql query and finding the unique tuples in php
On Fri, Jan 7, 2011 at 9:46 AM, Toby Hart Dyke t...@hartdyke.com wrote: On 1/7/2011 12:10 AM, Fahim M wrote: Hi I have a certain number of mysql tables(relation), say 50, some of them having 5 fields and some with 6 fields. a particular search item may be found in multiple tables with multiple rows. I am using a loop to find all those. My problem is I want to first combine all those results and then find all the unique entries. (the query results may overlap). What is the best way to do it? If you're looking for unique results, do a UNION query for all the tables. You'll need to add a dummy field for the 5-field tables, and make sure the field names are the same (use oldfieldname AS newfieldname to make sure everything ends up the correct result column). UNION queries automatically remove duplicates. Toby -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Though from the sounds of it, there is a problem with the table design if you have multiple results in multiple tables -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Connecting PHP to MySQL on Windows
Mike, A plain HOST file should have the commented part at the top, and only 1 reference to localhost unless you are creating Virtual hosts. I would suggest removing the ::1 line BR Gavin On Fri, Jan 7, 2011 at 4:18 AM, michael manning michaelgmann...@gmail.comwrote: Hi, I swapped out 'localhost for '127.0.0.1' in the url but this had no effect. I had a look at my hosts file at c:\windows\system32\drivers\etc and this contained: # Copyright (c) 1993-2006 Microsoft Corp. # # This is a sample HOSTS file used by Microsoft TCP/IP for Windows. # # This file contains the mappings of IP addresses to host names. Each # entry should be kept on an individual line. The IP address should # be placed in the first column followed by the corresponding host name. # The IP address and the host name should be separated by at least one # space. # # Additionally, comments (such as these) may be inserted on individual # lines or following the machine name denoted by a '#' symbol. # # For example: # # 102.54.94.97 rhino.acme.com # source server # 38.25.63.10 x.acme.com # x client host 127.0.0.1 localhost ::1 localhost and so this seems fine as well. cheers Mike On Fri, Jan 7, 2011 at 1:25 PM, Daniel Brown danbr...@php.net wrote: On Thu, Jan 6, 2011 at 21:12, michael manning michaelgmann...@gmail.com wrote: Hi Daniel, When testing with the CLI, netstat shows: [snip!] What happens when you swap out 'localhost' for '127.0.0.1' in the connection routine? If that works, check to make sure your hosts file properly designates 127.0.0.1 as 'localhost' (by editing c:\windows\system32\drivers\etc\hosts I believe been a while since I've done it on Windows). -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- Best regards, Gavin C
Re: [PHP-DB] Connecting PHP to MySQL on Windows
On Fri, Jan 7, 2011 at 15:06, Gavin Chalkley gavin.chalk...@gmail.com wrote: Mike, A plain HOST file should have the commented part at the top, and only 1 reference to localhost unless you are creating Virtual hosts. I would suggest removing the ::1 line As long as he never plans to use IPv6 on there, that would be fine. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Connecting PHP to MySQL on Windows
On Thu, Jan 6, 2011 at 01:13, michael manning michaelgmann...@gmail.com wrote: Hi, I have installed MySQL 5.1.52, Apache 2.2.15 and PHP 5.3.2 on my Windows Vista computer. The MySQL works fine with the command line client and I have tested the Apache and PHP and it appears fine when I test this with the phpinfo() function. There is just one exception - I cannot connect to a MySQL database from within a PHP script. I have installed the MySQLi extension I know you said MySQL works fine from the CLI, but a stupid question: is the MySQL server running while you're trying to test from PHP? And, if so, on what port does netstat show it running? -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] using PDO vs mysql extension
On Thu, Jan 6, 2011 at 02:52, Miriam Natanzon miriamna...@gmail.com wrote: Hi, I want to build an internal module for common DB-involved tasks: starting with connection, simple queries and go on to multiple pages of results and connecting tables and so on. We are currently working with MySQL but maybe in the future we'll add use of MSSQL (but it's negligible in comparison with the MYSQL percentage of use). I wonder if to base my functions on the abstract layer of PDO or to use mysql function (means mysql_connect and so on). As I see it, mysql functions are more readable and comfortable, but in this case : using MSSQL in the future would affect with new DB module with another implementation. What do would do in such a situation?? You could stick with the native mysql_* and mysqli_* functions, but if you have any hint that your application may eventually change its database platform, or if you at least want the option to easily swap, you should definitely use abstraction. You could use PDO or, as I did, create your own abstraction layers with a common application interface. Then all you need to do is change a configuration variable if you need to switch from, say, MySQL to SQL Server, PostgreSQL, et cetera. The application never notices a difference. For example: ?php define('DB_PLATFORM','mysql'); $db = new DB(DB_PLATFORM); $db-select('*','tablename'); ? There will be some pitfalls and intricacies now and again that require some additional adjustments in the model, but I've found that, for myself, I prefer having full control over the code. Well, that and the fact that it was a specific requirement in the spec of the project. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Connecting PHP to MySQL on Windows
Hi Daniel, When testing with the CLI, netstat shows: = C:\Users\mmanningnetstat -b Active Connections Proto Local Address Foreign AddressState TCP127.0.0.1:80 mmanning-PC:49391 ESTABLISHED [httpd.exe] TCP127.0.0.1:3306 mmanning-PC:49338 ESTABLISHED [mysqld.exe] TCP127.0.0.1:27015mmanning-PC:49157 ESTABLISHED [AppleMobileDeviceService.exe] TCP127.0.0.1:49157mmanning-PC:27015 ESTABLISHED [iTunesHelper.exe] TCP127.0.0.1:49262mmanning-PC:49263 ESTABLISHED [firefox.exe] TCP127.0.0.1:49263mmanning-PC:49262 ESTABLISHED [firefox.exe] TCP127.0.0.1:49264mmanning-PC:49265 ESTABLISHED [firefox.exe] TCP127.0.0.1:49265mmanning-PC:49264 ESTABLISHED [firefox.exe] TCP127.0.0.1:49338mmanning-PC:3306 ESTABLISHED [mysql.exe] TCP127.0.0.1:49391mmanning-PC:http ESTABLISHED [firefox.exe] TCP130.130.48.79:49208nimmo-37:8080 ESTABLISHED [googletalk.exe] TCP130.130.48.79:49238nimue-37:8080 ESTABLISHED [chrome.exe] TCP130.130.48.79:49344nimue-37:8080 ESTABLISHED [chrome.exe] === I ran the same test script on my computer at home without difficulty. It is running Apache 2.2.16, PHP 5.3.3 and MySQL 5.1.51 on Windows XP Professional SP3. regards Mike michaelgmann...@gmail.com On Fri, Jan 7, 2011 at 3:49 AM, Daniel Brown danbr...@php.net wrote: On Thu, Jan 6, 2011 at 01:13, michael manning michaelgmann...@gmail.com wrote: Hi, I have installed MySQL 5.1.52, Apache 2.2.15 and PHP 5.3.2 on my Windows Vista computer. The MySQL works fine with the command line client and I have tested the Apache and PHP and it appears fine when I test this with the phpinfo() function. There is just one exception - I cannot connect to a MySQL database from within a PHP script. I have installed the MySQLi extension I know you said MySQL works fine from the CLI, but a stupid question: is the MySQL server running while you're trying to test from PHP? And, if so, on what port does netstat show it running? -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/
Re: [PHP-DB] Connecting PHP to MySQL on Windows
Hi, I swapped out 'localhost for '127.0.0.1' in the url but this had no effect. I had a look at my hosts file at c:\windows\system32\drivers\etc and this contained: # Copyright (c) 1993-2006 Microsoft Corp. # # This is a sample HOSTS file used by Microsoft TCP/IP for Windows. # # This file contains the mappings of IP addresses to host names. Each # entry should be kept on an individual line. The IP address should # be placed in the first column followed by the corresponding host name. # The IP address and the host name should be separated by at least one # space. # # Additionally, comments (such as these) may be inserted on individual # lines or following the machine name denoted by a '#' symbol. # # For example: # # 102.54.94.97 rhino.acme.com # source server # 38.25.63.10 x.acme.com # x client host 127.0.0.1 localhost ::1 localhost and so this seems fine as well. cheers Mike On Fri, Jan 7, 2011 at 1:25 PM, Daniel Brown danbr...@php.net wrote: On Thu, Jan 6, 2011 at 21:12, michael manning michaelgmann...@gmail.com wrote: Hi Daniel, When testing with the CLI, netstat shows: [snip!] What happens when you swap out 'localhost' for '127.0.0.1' in the connection routine? If that works, check to make sure your hosts file properly designates 127.0.0.1 as 'localhost' (by editing c:\windows\system32\drivers\etc\hosts I believe been a while since I've done it on Windows). -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/
Re: [PHP-DB] Word Matching Application
On Wed, Jan 5, 2011 at 21:23, Ron Piggott ron.pigg...@actsministries.org wrote: I only know how to do one array at a time, using FOREACH, like this: === echo ul\r\n; foreach($match_words as $word) { echo li . $word . /li\r\n; } echo /ul\r\n; === You could either swap that out for a simple `for` loop or add in an array_combine() call and sort like so: foreach ($new_array_name as $word = $explanation) -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Word Matching Application
This works very well. Thank you for your assistance Dan. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -Original Message- From: Daniel Brown Sent: Wednesday, January 05, 2011 9:34 PM To: Ron Piggott Cc: php-db@lists.php.net ; Mike Stowe Subject: Re: [PHP-DB] Word Matching Application On Wed, Jan 5, 2011 at 21:23, Ron Piggott ron.pigg...@actsministries.org wrote: I only know how to do one array at a time, using FOREACH, like this: === echo ul\r\n; foreach($match_words as $word) { echo li . $word . /li\r\n; } echo /ul\r\n; === You could either swap that out for a simple `for` loop or add in an array_combine() call and sort like so: foreach ($new_array_name as $word = $explanation) -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Word Activity Application
The FOREACH below is giving me the error: Invalid argument supplied for foreach() Not surprising as the $match_words will contain a boolean result from shuffle. Shuffle works directly on the variable it is given and returns true or false, depending on success. rtfm ;-) Does anyone understand what I have done to cause this error? #query for words $query = SELECT `reference` , `word` , `explanation` FROM `Bible_dictionary` WHERE `live` =1 ORDER BY RAND( ) LIMIT 5 ; $words_match_up_result=mysql_query($query); $records_found=mysql_numrows($words_match_up_result); echo $records_found . br; # output is 5 #create array from mySQL query $words = array(); $explanations = array(); $i=1; while ( $i = $records_found ) { $words[$i] = stripslashes( mysql_result($words_match_up_result,($i -1),word) ); $explanations[$i] = stripslashes( mysql_result($words_match_up_result,($i -1),explanation) ); ++$i; } #shuffle arrays $match_words = shuffle ( $words ); $match_explanations = shuffle ( $explanations ); change to: $match_words = $words; shuffle($match_words); $match_explanations = $explanations; shuffle($match_explanations); However if these need to stay in sync this will not work. i.e if a explanation's index needs to match the corresponding word's index, then you'll have to do this another way. But as I understand your need here, this isn't a problem? #display words on the screen foreach($match_words as $word) { echo $word . br /\r\n; } The Verse of the Day Encouragement from Gods Word http://www.TheVerseOfTheDay.info From: Ron Piggott Sent: Sunday, January 02, 2011 5:54 PM To: php-db@lists.php.net Subject: Word Activity Application I am working on a word activity --- matching words and their definitions. I want to display 5 words on the left hand side and the 5 definitions on the right hand side. But I want the definitions displayed in a different order than the words so the user submits their answer. Should I use PHP to display the definitions in random order? OR Is there a way do this in mySQL that would mix and match results from different rows? This is the query gives me the 5 results SELECT `reference` , `word` , `explanation` FROM `Bible_dictionary` WHERE `live` =1 ORDER BY RAND( ) LIMIT 5 Ron The Verse of the Day Encouragement from Gods Word http://www.TheVerseOfTheDay.info -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Two forms on one page
On 1/3/2011 10:52 PM, Ethan Rosenberg wrote: Dear List - I would like to have two(2) forms in one PHP script. I would like to have the forms appear sequentially; ie, that the first form would appear, the data would be entered, and then the second form would appear, the data would be entered, and the script would exit. The code below displays both forms simultaneously. After the data is entered for the first form, the second form appears again. After the data is entered for the second form, the script displays the statement from the first form. Would you please help me correct the script so that it will perform as required. If you're trying to do this without actually submitting the first form, then you'll have to use JavaScript to hide/show the second form. Personally, I'd use jQuery/jQuery UI to get a date picker on the first text box, then after a date is entered, display the second box. Toby -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Two forms on one page
On Jan 3, 2011, at 5:17 PM, Ethan Rosenberg wrote: 'Submit Ktten' Shouldn't this be.. 'Submit Kitten' If your asking for it to explicitly equal that (===), then you need to spell Kitten with an i in it? But, that may not be what is actually wrong, just somehting I think I caught. 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] Re: [PHP] Regex for telephone numbers
On Thu, Dec 30, 2010 at 14:07, Ethan Rosenberg eth...@earthlink.net wrote: Josh - I used use \d{3}-\d{3}-\d{4}. It works beautifully!! Just keep in mind that invalid numbers will also pass that check, such as 000-000- or 123-456-6789. That's why my example was a bit more involved. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: [PHP] Regex for telephone numbers
Hi Ethan, Could you do a string compare and check at certain characters for a dash? IE: check the second character to see if it is a dash for 1-800... if that is not a dash, check the fourth character for a dash, 469-9... then the other places where dashes would be based on those two characters. You may have to investigate how international numbers would work and adjust appropriately, but for the US, that should work. Then just send an error message when it isn't like you want. JAT Karl On Dec 29, 2010, at 6:27 PM, Josh Kehn wrote: On Dec 29, 2010, at 7:12 PM, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thank you for all your help in the past. Here is another one I would like to have a regex which would validate that a telephone number is in the format xxx-xxx-. Thanks. Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php You can't, phone numbers are more complex then that. You could use \d{3}-\d{3}-\d{4} to match that basic pattern for all numbers though. Regards, -Josh ___ http://joshuakehn.com Sent from my iPod -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php 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: [PHP] Regex for telephone numbers
You could also help them out a little with something like.. $phone = str_replace((, , $phone); $phone = str_replace(), -, $phone); HTH, Karl On Dec 29, 2010, at 6:27 PM, Josh Kehn wrote: On Dec 29, 2010, at 7:12 PM, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thank you for all your help in the past. Here is another one I would like to have a regex which would validate that a telephone number is in the format xxx-xxx-. Thanks. Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php You can't, phone numbers are more complex then that. You could use \d{3}-\d{3}-\d{4} to match that basic pattern for all numbers though. Regards, -Josh ___ http://joshuakehn.com Sent from my iPod -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php 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] bindParam OR bindValue
i am not 100% sure but should you not remove ' from around the variables to have (NULL, :f1, :l1 etc? prepared statements take care of escaping and quoting. http://www.php.net/manual/en/pdo.prepare.php does it help? art -- Visit me at: http://artur.ejsmont.org/blog/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: [PHP] Problems w/ goto
On Sat, Dec 18, 2010 at 17:02, David Hutto smokefl...@gmail.com wrote: or maybe it's saturday morning and i'm drunk? This seems to be the most likely, and considering how all messages are permanently and independently archived and propagate throughout the Internet, it might be a good reason not to go nuts in sending unrelated and unintelligible messages of this nature in the future. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: [PHP] Problems w/ goto
On Mon, Dec 20, 2010 at 7:37 AM, Daniel Brown danbr...@php.net wrote: On Sat, Dec 18, 2010 at 17:02, David Hutto smokefl...@gmail.com wrote: or maybe it's saturday morning and i'm drunk? This seems to be the most likely, and considering how all messages are permanently and independently archived and propagate throughout the Internet, it might be a good reason not to go nuts in sending unrelated and unintelligible messages of this nature in the future. Yeah, that hindsights 20/20 ain't it? -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- They're installing the breathalyzer on my email account next week. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SUM() Math in mySQL
On 20/12/10 15:38, Ron Piggott wrote: I am working on the query below. It’s purpose is to manage the RSS Feed for the site. The part of the query I am struggling with is: SUM(`include_in_rss_feed`) AS current_rss_feed What I need is the total value of *ALL* the `include_in_rss_feed` , not the specific row. `include_in_rss_feed` is an integer. It only ever has a value of 1 Well you're going to get an error with your current attempt - but you know that already I assume. You have an aggregate function (sum) but no group by clause for the outer query. I think you're better off just having 2 queries - one with the sum() and the group by include_in_rss_feed, and one with all of your other data. I don't think combining them will be very easy. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: [PHP] Problems w/ goto
On Fri, Dec 17, 2010 at 12:16, Richard Quadling rquadl...@gmail.com wrote: And have you seen all the sad faces ... : { on http://docs.php.net/manual/en/control-structures.goto.php#92763 Can't be good for them. If only people knew how many hours - literally, hours - it took me to keep that page clean and free from vandalism after GOTO was introduced. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL to 0 result
On 14 December 2010 21:53, Ron Piggott ron.pigg...@actsministries.org wrote: What change is needed to this query so if “currently_in_rss” is NULL it will be assigned a value of 0 SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` Ron Doesn't that query give you an error saying that `reference` isn't in the GROUP BY clause? I use MS SQL, so the wording my be different but it would be along the lines of ... For the SQL statement : SELECT POH_Contract, COUNT(POH_Contract) FROM [BV-CLUSTER-SQL].Contracts.dbo.POP_Header WHERE POH_Status = 1 GROUP BY POH_Status Column 'bv-cluster-sql.contracts.dbo.pop_header.POH_CONTRACT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. So, fixing the query ... SELECT POH_Contract, COUNT(POH_Contract) FROM [BV-CLUSTER-SQL].Contracts.dbo.POP_Header WHERE POH_Status = 1 GROUP BY POH_Status, POH_Contract now works. So, your query may need to be SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include`, `reference` Normally COUNT() will count NULLs, but as you are grouping by the counted column, nulls would be in their own row. Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL to 0 result
On 15/12/10 21:42, Richard Quadling wrote: On 14 December 2010 21:53, Ron Piggottron.pigg...@actsministries.org wrote: What change is needed to this query so if “currently_in_rss” is NULL it will be assigned a value of 0 SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` Ron Doesn't that query give you an error saying that `reference` isn't in the GROUP BY clause? Nah mysql lets you do it. Other db's enforce it as you pointed out. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Resetting auto_increment
On Tue, Dec 14, 2010 at 12:34, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your help. How do I reset auto_increment so that the primary key will start from 1. The primary key is now 2421. I have deleted all the data in the table and started over, and the primary key just increments from its previous value. Setting auto_increment=0 does not work for me, why I don't know. UPDATE tablename AUTO_INCREMENT=1; -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Resetting auto_increment
At 12:38 PM 12/14/2010, Daniel Brown wrote: On Tue, Dec 14, 2010 at 12:34, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your help. How do I reset auto_increment so that the primary key will start from 1. The primary key is now 2421. I have deleted all the data in the table and started over, and the primary key just increments from its previous value. Setting auto_increment=0 does not work for me, why I don't know. UPDATE tablename AUTO_INCREMENT=1; -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ == Daniel - Thanks. This is what I get - mysql update Visit3 auto_increment=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1' at line 1 Same error if auto_increment is in caps. What did I do wrong? Thanks in advance. Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Resetting auto_increment
On 12/14/2010 1:02 PM, Ethan Rosenberg wrote: At 12:38 PM 12/14/2010, Daniel Brown wrote: On Tue, Dec 14, 2010 at 12:34, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your help. How do I reset auto_increment so that the primary key will start from 1. The primary key is now 2421. I have deleted all the data in the table and started over, and the primary key just increments from its previous value. Setting auto_increment=0 does not work for me, why I don't know. UPDATE tablename AUTO_INCREMENT=1; -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ == Daniel - Thanks. This is what I get - mysql update Visit3 auto_increment=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1' at line 1 Use 'ALTER tablename AUTO_INCREMENT=1' rather than 'UPDATE tablename AUTO_INCREMENT=1'. Toby -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Resetting auto_increment
On Tue, Dec 14, 2010 at 13:02, Ethan Rosenberg eth...@earthlink.net wrote: UPDATE tablename AUTO_INCREMENT=1; mysql update Visit3 auto_increment=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1' at line 1 Same error if auto_increment is in caps. What did I do wrong? Sorry, you didn't --- I did. I was working on something for myself simultaneously and somehow crossed wires. My apologies. That's not an UPDATE query, it's an ALTER TABLE query. ALTER TABLE Visit3 AUTO_INCREMENT=1; Again, my apologies. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Resetting auto_increment
On Tue, Dec 14, 2010 at 13:43, Toby Hart Dyke t...@hartdyke.com wrote: Use 'ALTER tablename AUTO_INCREMENT=1' rather than 'UPDATE tablename AUTO_INCREMENT=1'. Yeah, again, my fault. Not enough coffee on this cold (~7F) winter's day, perhaps. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL to 0 result
On Tue, Dec 14, 2010 at 4:53 PM, Ron Piggott ron.pigg...@actsministries.org wrote: What change is needed to this query so if “currently_in_rss” is NULL it will be assigned a value of 0 SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info SELECT `reference`, ifnull(COUNT(`reference`),0) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Incorrect encoding when data read from a mysql database
On Fri, Dec 3, 2010 at 4:42 AM, Neil Jackson n...@webcoza.co.za wrote: Hi, I am designing a website that requires storing data in 3 different languages, English, Portuguese and Tetum. When I read the data from the database the accented characters are displayed as a ? in Firefox and Opera, a square in IE and correctly in Chrome How can I convert it on the fly and display the data correctly? Thanks for the help Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php 1. set the db to store unicode characters 2. send the html with a utf-8 character header -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: [PHP] Closing Browser
If you are trying to do some automation type stuff you might want to look at something like Robo Task - http://www.robotask.com/ You can schedule a browser open to specified URL's (php script) and have it close the window handle after the page is loaded. Regards, Will T Chief Technical Officer Oddity Software LLC Toll Free 1.888.506.5995 http://www.odditysoftware.com -Original Message- From: tedd [mailto:tedd.sperl...@gmail.com] Sent: Monday, December 06, 2010 3:39 PM To: Ethan Rosenberg; php-db-lists.php.net; php-gene...@lists.php.net Subject: [PHP-DB] Re: [PHP] Closing Browser At 12:45 AM -0500 12/3/10, Ethan Rosenberg wrote: Dear List - How do I close the browser window, with a script [shell or Perl], after I exit PHP? Thanks. Ethan Ethan: PHP runs before the browser reads the result. As such, if PHP closed the window when it exited, then there would not be an open window for the user to see. I suggest you re-think what you are trying to do. In fact, if you simply state what you are trying to do, it might be a better way to get our opinion. Cheers, tedd -- --- http://sperling.com/ -- 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] NULL values
Dear Ron Or try this SELECT * FROM `paypal_payment_info` WHERE ifnull(os1, '') 'commission_paid' regds amit The difference between fiction and reality? Fiction has to make sense. On Sat, Dec 4, 2010 at 7:55 AM, Ron Piggott ron.pigg...@actsministries.orgwrote: When I do the following query in mySQL only 1 record is retrieved. SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE 'commission_paid' I am surprised by this. This one record has no characters in it, but the “INSERT INTO” that created it used: ( `os1` ) VALUES ( ‘’ ) instead of: ( `os1` ) VALUES ( NULL ) . There are a number of records where `os1` is NULL. I would like these rows to retrieve as well. How do I make a WHERE clause for a cell that is NULL ? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] NULL values
On 4 December 2010 11:40, Amit Tandon att...@gmail.com wrote: Dear Ron Or try this SELECT * FROM `paypal_payment_info` WHERE ifnull(os1, '') 'commission_paid' regds amit The difference between fiction and reality? Fiction has to make sense. On Sat, Dec 4, 2010 at 7:55 AM, Ron Piggott ron.pigg...@actsministries.orgwrote: When I do the following query in mySQL only 1 record is retrieved. SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE 'commission_paid' I am surprised by this. This one record has no characters in it, but the “INSERT INTO” that created it used: ( `os1` ) VALUES ( ‘’ ) instead of: ( `os1` ) VALUES ( NULL ) . There are a number of records where `os1` is NULL. I would like these rows to retrieve as well. How do I make a WHERE clause for a cell that is NULL ? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_isnull where isnull(column, '') 'value' -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL values
On Fri, Dec 3, 2010 at 6:25 PM, Ron Piggott ron.pigg...@actsministries.org wrote: When I do the following query in mySQL only 1 record is retrieved. SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE 'commission_paid' I am surprised by this. This one record has no characters in it, but the “INSERT INTO” that created it used: ( `os1` ) VALUES ( ‘’ ) instead of: ( `os1` ) VALUES ( NULL ) . There are a number of records where `os1` is NULL. I would like these rows to retrieve as well. How do I make a WHERE clause for a cell that is NULL ? You need to explicitly check for NULLs. The regular operators (, , =, LIKE) work on values. NULL columns have no value, so you need to use IS NULL or IS NOT NULL. Also, if you're not doing wildcard matches, you should probably just use or =: SELECT * FROM `paypal_payment_info` WHERE (`os1` 'commission_paid' OR `os` IS NULL) Scotty -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Using placeholder without pass by reference in MySqli
? Bastien phps...@gmail.com wrote in message news:962f0fd6-0eaa-495a-831e-da05bc1ab...@gmail.com... On 2010-11-26, at 7:46 PM, None abcdef...@mailinator.com wrote: ?Could be a silly question, but i can't find a method to use placeholder (MySqli) without passing by reference (bind_param) or receive by reference (bind_result)? is that right? I really hate use reference becouse in large program is common create a circular reference, even in multiple query with same data i lost the information after first query so i need to clean an refill at every step. I prefer functions like MySql or DBI. Do you think it will be implemented, function like param, param_array, fetch_assoc, fetch_all_assoc, etc. where is the correct place to ask for this method? here? Thank for your advice -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Look at the prepared statements section of mysqli. Bastien Koert Sent from my iPhone= I double checked the entire documentation, but it's not possible to use prepared queries without sending or receiving data by reference it only accept bind_result and bind_param and, in fact, this's explicitly named in http://us.php.net/manual/en/mysqli-stmt.prepare.php Could I be wrong? there are undocumented methods that allows this? Thank you as always. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: [PHP] Strange Query Error...
On Nov 27, 2010, at 10:08 AM, Daniel P. Brown wrote: one primary question: are you using the mysql_* family, mysqli_* family, or another method of interfacing with MySQL? mysql_ $results = mysql_query($query) or die(mysql_error()); Don -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Could you show the actual code, not just the query. The way you are setting up the query could be affecting it. i.e. using single quotes when assigning to a variable would not work with your query. -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ON DUPLICATE KEY UPDATE
Read through this: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html, especially the last 2 paragraphs. On Sun, Nov 28, 2010 at 12:40 PM, Ron Piggott ron.pigg...@actsministries.org wrote: My question is with the syntax below: If the “ON DUPLICATE KEY UPDATE `name` = '$name'” occurs will $_SESSION['referral_clients_reference'] be populated with the mysql_insert_id() value? If not, should I just query for it? Ron mysql_query(INSERT INTO `referral_clients` ( `reference` , `name` , `paypal_email` , `last_update` ) VALUES ( NULL , '$name', '$paypal_email', CURRENT_TIMESTAMP ) ON DUPLICATE KEY UPDATE `name` = '$name';); $_SESSION['referral_clients_reference'] = mysql_insert_id(); The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] Re: [PHP] Strange Query Error...
On Nov 27, 2010, at 1:24 PM, Don Wieland wrote: On Nov 27, 2010, at 10:44 AM, Daniel P. Brown wrote: Note how you keep changing case here. For example, m.aucciim_id vs. m.AUCCIIM_ID. Also note that all of this is cAsE-sEnSiTiVe. You are right. But it still chokes in PHP: select m.* from Members m inner join Member_Years my on m.AUCCIIM_ID = my.member_id where now() DATE_ADD(DATE_SUB(concat (member_year + 1,'-07-01'), INTERVAL 1 DAY), INTERVAL 30 DAY) AND m.Preferred_First_Name LIKE 'Don%' group by m.AUCCIIM_ID order by m.preferred_last_name Don Don't know if you necessarily need this but I found it while checking to see if one letter names are a good idea in MySQL statements. Might help in your like query. http://techonthenet.com/sql/like.php 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] Using placeholder without pass by reference in MySqli
On 2010-11-26, at 7:46 PM, None abcdef...@mailinator.com wrote: ?Could be a silly question, but i can't find a method to use placeholder (MySqli) without passing by reference (bind_param) or receive by reference (bind_result)? is that right? I really hate use reference becouse in large program is common create a circular reference, even in multiple query with same data i lost the information after first query so i need to clean an refill at every step. I prefer functions like MySql or DBI. Do you think it will be implemented, function like param, param_array, fetch_assoc, fetch_all_assoc, etc. where is the correct place to ask for this method? here? Thank for your advice -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Look at the prepared statements section of mysqli. Bastien Koert Sent from my iPhone -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] If condition in query
Dear list - Thank you for all your excellent help. I wish to search a table. In this case, I have five(5) columns: site, Record, BMI, Weight and Height. I wish to be able to search on one or more of the columns. If I use a query like: $ste = $_POST['site']; $req = $_POST['Record']; $wgt = $_POST['Weight']; $hgt = $_POST['Height']; $bmi = $_POST['BMI']; $sql1 = select * from intake2 where site = '$ste' Weight = '$wgt' Record = '$req' '$hgt' = Height '$bmi' = BMI ; $result = mysqli_query($cxn, $sql1); and do not use all the variables, no data is returned. I use to extract the data from the query. while($row = mysqli_fetch_array($result[0])) { $site2 = $row[0]; $record2 = $row[1]; $bmi2 = $row[2]; $wgt2 = $row[3]; $hgt2 = $row[4]; printf(%s\t%d\t%3.1f\t%d\t%dbr /, $site2, $record2, $bmi2, $wgt2, $hgt2); } If I put an extra test in the query to exclude blank values;eg, (if(isset ($bmi) '$bmi' = BMI ), $result defaults to a boolean and mysqli_fetch_array($result) fails. I wish to be able to search on one or more, but not necessarily all, of the parameters and be able to output the results. Advice and help please. Thanks in advance. Ethan First you need to protect your input from injection and other attacks. http://en.wikipedia.org/wiki/SQL_injection for the problem you ask, I'd suggest building the query in php rather than SQL A simple example would be: $where ' '; if (isset($ste)) { $where .= site = '$ste'; } if (isset($wgt)) { $where .= , Weight = '$wgt'; } $sql .= WHERE $where; -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] If condition in query
My question - 1. why have u used mysqli_fetch_array($result[0]), in place of mysqli_fetch_array($result)) i.e. without array brackets as $result is not an array. What is returned by mysqli_fetch_array is an array. 2. Have u checked the your query (print $sq1) after u added *extra test in the query to exclude blank values;eg, (if(isset ($bmi) '$bmi' = BMI )*,. If your output works in mysql (mysql client), it would work here also regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Nov 18, 2010 at 10:22 PM, Niel Archer n...@chance.now wrote: Dear list - Thank you for all your excellent help. I wish to search a table. In this case, I have five(5) columns: site, Record, BMI, Weight and Height. I wish to be able to search on one or more of the columns. If I use a query like: $ste = $_POST['site']; $req = $_POST['Record']; $wgt = $_POST['Weight']; $hgt = $_POST['Height']; $bmi = $_POST['BMI']; $sql1 = select * from intake2 where site = '$ste' Weight = '$wgt' Record = '$req' '$hgt' = Height '$bmi' = BMI ; $result = mysqli_query($cxn, $sql1); and do not use all the variables, no data is returned. I use to extract the data from the query. while($row = mysqli_fetch_array($result[0])) { $site2 = $row[0]; $record2 = $row[1]; $bmi2 = $row[2]; $wgt2 = $row[3]; $hgt2 = $row[4]; printf(%s\t%d\t%3.1f\t%d\t%dbr /, $site2, $record2, $bmi2, $wgt2, $hgt2); } If I put an extra test in the query to exclude blank values;eg, (if(isset ($bmi) '$bmi' = BMI ), $result defaults to a boolean and mysqli_fetch_array($result) fails. I wish to be able to search on one or more, but not necessarily all, of the parameters and be able to output the results. Advice and help please. Thanks in advance. Ethan First you need to protect your input from injection and other attacks. http://en.wikipedia.org/wiki/SQL_injection for the problem you ask, I'd suggest building the query in php rather than SQL A simple example would be: $where ' '; if (isset($ste)) { $where .= site = '$ste'; } if (isset($wgt)) { $where .= , Weight = '$wgt'; } $sql .= WHERE $where; -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
well i guess you could do that. but it gets complicated after a while and there will be a lot of work and probably after a while you will get into some problems. You have to handle escaping, special types etc. Then what about performance? how to query the data ... using similar approach or is it just for inserts? there is a bit of work to make it really usable i guess. I am not saying its wrong though, I have seen this approach twice ... in general its possible. Maybe better choice would be to try to use some orm ? there are plenty of frameworks out there the only problem is the learning curve may be steep. What others think? art On 17 November 2010 13:51, Vinay Kannan viny...@gmail.com wrote: Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan. -- Visit me at: http://artur.ejsmont.org/blog/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
On Wed, Nov 17, 2010 at 8:51 AM, Vinay Kannan viny...@gmail.com wrote: Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan. codeigniter works this way. But they have a lot of extra functionality to keep the data safe for inserts. Check it out. -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
From: Vinay Kannan viny...@gmail.com To: PHP DB php-db@lists.php.net, php mysql php_mysql_usergr...@yahoogroups.com Sent: Wednesday, November 17, 2010 2:51:35 PM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: [PHP-DB] query help Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan. Hi Vinay, You may want to try codeigniter. More info here. http://codeigniter.com/user_guide/database/active_record.html#insert with kind regards, Max. Max Kimambo Franz-Stenzer-Straße, 51 12679, Berlin. T: +493057706550 (new number) M: +4917649520175 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan. You don't give any info about the database engine, but assuming you're using MySQL take a look at http://dev.mysql.com/doc/refman/5.0/en/insert.html Specifically you can use your idea to build an INSERT/VALUE version of the syntax INSERT INTO table (col1, col2, .colN.) VALUES (col1Value1, col2value1, colNvalue1), (col1Value2, col2value2, colNvalue2), ... -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] query help
Hey, You can also try PEAR module MDB2 to insert an array into a table. The function is: executeMultiple. This works for other SQL queries too. Check this link please: http://pear.php.net/manual/en/package.database.mdb2.intro-execute.php Cheers, -Original Message- From: Artur Ejsmont [mailto:ejsmont.ar...@gmail.com] Sent: 17 noiembrie 2010 16:07 To: Vinay Kannan Cc: PHP DB; php mysql Subject: Re: [PHP-DB] query help well i guess you could do that. but it gets complicated after a while and there will be a lot of work and probably after a while you will get into some problems. You have to handle escaping, special types etc. Then what about performance? how to query the data ... using similar approach or is it just for inserts? there is a bit of work to make it really usable i guess. I am not saying its wrong though, I have seen this approach twice ... in general its possible. Maybe better choice would be to try to use some orm ? there are plenty of frameworks out there the only problem is the learning curve may be steep. What others think? art On 17 November 2010 13:51, Vinay Kannan viny...@gmail.com wrote: Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan. -- Visit me at: http://artur.ejsmont.org/blog/ -- 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] query help
The most performant methods are to use well parameterized stored procedure or a prepared parameterized statement. : Ashay -Original Message- From: Niel Archer [mailto:n...@chance.now] Sent: Wednesday, November 17, 2010 6:30 AM To: php-db@lists.php.net Subject: Re: [PHP-DB] query help Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan. You don't give any info about the database engine, but assuming you're using MySQL take a look at http://dev.mysql.com/doc/refman/5.0/en/insert.html Specifically you can use your idea to build an INSERT/VALUE version of the syntax INSERT INTO table (col1, col2, .colN.) VALUES (col1Value1, col2value1, colNvalue1), (col1Value2, col2value2, colNvalue2), ... -- Niel Archer niel.archer (at) blueyonder.co.uk -- 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] French and Spanish Accent Letters
On Wed, Nov 10, 2010 at 10:03, Ron Piggott ron.pigg...@actsministries.org wrote: I have a column that is VARCHAR 250. I need it to be able to accept french and spanish accents. The purpose of the column is organization names. The “Collation” default is “latin1_swedish_ci” What do I need to do? Ron You need to ask on a database list. If it's MySQL, blast it to my...@lists.mysql.com, or - regardless of database - you can send it to php...@lists.php.net. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] French and Spanish Accent Letters
On Wed, Nov 10, 2010 at 10:16, Ron Piggott ron.pigg...@actsministries.org wrote: I did Daniel --- php-db@lists.php.net ; Thank you for telling me about the mySQL list. I failed to mention in my original e-mail the database is mySQL. My fault. Force of habit mentioning the php-db@ list. Sorry if there was any confusion on that. In any case, you'll probably get exactly the answer you're trying to find on the MySQL list. Quite possibly quicker there than the php-db@ list as well. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] French and Spanish Accent Letters
Hi Ron, you should use CHARSET=utf8 COLLATE=utf8_unicode_ci in your table for a wide option of languages. Example: CREATE TABLE `t_my_table` ( `row_id` int(10) unsigned NOT NULL, `row_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Cheers. -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] French and Spanish Accent Letters
I use utf-8 for that it works well Bastien Koert 905-904-0334 Sent from my iPhone On 2010-11-10, at 10:03 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I have a column that is VARCHAR 250. I need it to be able to accept french and spanish accents. The purpose of the column is organization names. The “Collation” default is “latin1_swedish_ci” What do I need to do? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Data Migration
with kind regards, From: Karl DeSaulniers k...@designdrumm.com To: php-db@lists.php.net Sent: Monday, November 8, 2010 12:19:23 PM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: [PHP-DB] Data Migration Hello All, I was wondering if I could get some pointers on the best/safest method to migrate data on a fairly simple scale. I have a registration form that gets submitted. I want to take that info and store it in a table as a sort of Que. if you will. When the application is approved, I want to have something set up for the owner or his employees to access and be able to activate the account. Behind the scenes, the info from the registration table is simply moved to the users table and an email is sent out. What would be the simplest way to migrate the data between the tables? Is there a bulk way to move data in PHP? Or is that best/safest done in MySQL? Examples, tuts, keywords appreciated. TIA Karl DeSaulniers Design Drumm http://designdrumm.com PS: I'm on google now.. - Original Message - Just thinking about it, how about a stored procedure, that does select from registration table and insert into the user table, you can then delete the inserted record from the registration table. Something like this. Insert into table_users (fields1, field2) Select field1, field2 from registrations_table WHERE some_criteria you can have a marker field for tracking what was already worked up on. Just a thought. with kind regards, Max. Max Kimambo Franz-Stenzer-Straße, 51 12679, Berlin. T: +493057706550 (new number) M: +4917649520175 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Data Migration
On 2010-11-08, at 6:19 AM, Karl DeSaulniers k...@designdrumm.com wrote: Hello All, I was wondering if I could get some pointers on the best/safest method to migrate data on a fairly simple scale. I have a registration form that gets submitted. I want to take that info and store it in a table as a sort of Que. if you will. When the application is approved, I want to have something set up for the owner or his employees to access and be able to activate the account. Behind the scenes, the info from the registration table is simply moved to the users table and an email is sent out. What would be the simplest way to migrate the data between the tables? Is there a bulk way to move data in PHP? Or is that best/safest done in MySQL? Examples, tuts, keywords appreciated. TIA Karl DeSaulniers Design Drumm http://designdrumm.com PS: I'm on google now.. Karl, A better solution might be to add a temp flag or status field and avoid the need to move data. When the user clicks the link in the email, then you would only need to update that one field to activate the user. Bastien Koert Sent from my iPhone -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: ezmlm warning
On Fri, 2010-11-05 at 06:58 -0500, Karl DeSaulniers wrote: Hi, Can anyone admin please explain this to me? I received one myself. -- Blessings, David M. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: ezmlm warning
Karl DeSaulniers wrote: Hi, Can anyone admin please explain this to me? If the email server gets bounce messages back for emails set out, then it tries to check those emails. If the warning message gets through then nothing really happens, but if that bounces as well, then it will disable sending more emails to that address. I had one for this list this morning as well ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: ezmlm warning
On Fri, Nov 5, 2010 at 08:13, Lester Caine les...@lsces.co.uk wrote: Karl DeSaulniers wrote: Hi, Can anyone admin please explain this to me? If the email server gets bounce messages back for emails set out, then it tries to check those emails. If the warning message gets through then nothing really happens, but if that bounces as well, then it will disable sending more emails to that address. I had one for this list this morning as well ... Mostly everyone on the list would've gotten that same one. It's nothing of which to be concerned, it just means that your local mailservers did what they should: they bounced a poor attempt at a phishing message claiming to be from the lists.php.net support team (there is no such group). When your mailserver rightfully bounced the email, ezmlm sent the notice to you that it bounced. You can check the content of bounced messages yourself by following the instructions in any given bounce message you receive from us. Inside the email, you'll see a message number (not to be confused with a message ID), and you can build a dynamic email address to which you simply send a blank email. Moments later, you should receive a copy of the original email --- and usually that will come through, by request. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: ezmlm warning
On 5 November 2010 16:14, Daniel Brown danbr...@php.net wrote: On Fri, Nov 5, 2010 at 08:13, Lester Caine les...@lsces.co.uk wrote: Karl DeSaulniers wrote: Hi, Can anyone admin please explain this to me? If the email server gets bounce messages back for emails set out, then it tries to check those emails. If the warning message gets through then nothing really happens, but if that bounces as well, then it will disable sending more emails to that address. I had one for this list this morning as well ... Mostly everyone on the list would've gotten that same one. It's nothing of which to be concerned, it just means that your local mailservers did what they should: they bounced a poor attempt at a phishing message claiming to be from the lists.php.net support team (there is no such group). When your mailserver rightfully bounced the email, ezmlm sent the notice to you that it bounced. You can check the content of bounced messages yourself by following the instructions in any given bounce message you receive from us. Inside the email, you'll see a message number (not to be confused with a message ID), and you can build a dynamic email address to which you simply send a blank email. Moments later, you should receive a copy of the original email --- and usually that will come through, by request. Or you can use the news.php.net site to read the message. http://news.php.net/php.db/47430 -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: ezmlm warning
I see. Thank you all. Best, Karl Sent from losPhone On Nov 5, 2010, at 11:14 AM, Daniel Brown danbr...@php.net wrote: On Fri, Nov 5, 2010 at 08:13, Lester Caine les...@lsces.co.uk wrote: Karl DeSaulniers wrote: Hi, Can anyone admin please explain this to me? If the email server gets bounce messages back for emails set out, then it tries to check those emails. If the warning message gets through then nothing really happens, but if that bounces as well, then it will disable sending more emails to that address. I had one for this list this morning as well ... Mostly everyone on the list would've gotten that same one. It's nothing of which to be concerned, it just means that your local mailservers did what they should: they bounced a poor attempt at a phishing message claiming to be from the lists.php.net support team (there is no such group). When your mailserver rightfully bounced the email, ezmlm sent the notice to you that it bounced. You can check the content of bounced messages yourself by following the instructions in any given bounce message you receive from us. Inside the email, you'll see a message number (not to be confused with a message ID), and you can build a dynamic email address to which you simply send a blank email. Moments later, you should receive a copy of the original email --- and usually that will come through, by request. -- /Daniel P. Brown Network Infrastructure Manager Documentation, Webmaster Teams http://www.php.net/ -- 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] Reading from file
Return Values If only two parameters were passed to this function, the values parsed will be returned as an array. Otherwise, if optional parameters are passed, the function will return the number of assigned values. The optional parameters must be passed by reference. http://php.net/manual/en/function.fscanf.php so you should expect an array. print_r it to see what is inside. art On 2 November 2010 03:34, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thank you for all your help. Here is another one. I have a file [/home/ethan/PHP/RecNum] which consists of the number 1005. It also has been 1005. No matter how I do it, it is only recognized as 1. Here is the code. In this case, the file is 1005. $fptr1 = fopen(/home/ethan/PHP/RecNum, r+); $recNum = (int)fscanf($fptr1,%s); echo $recNum; //which always is 1 If I do not typecast $recNum to int, the value is Array. Thanks. Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Visit me at: http://artur.ejsmont.org/blog/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Reading from file
At 06:08 AM 11/2/2010, Artur Ejsmont wrote: Return Values If only two parameters were passed to this function, the values parsed will be returned as an array. Otherwise, if optional parameters are passed, the function will return the number of assigned values. The optional parameters must be passed by reference. so you should expect an array. print_r it to see what is inside. art On 2 November 2010 03:34, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thank you for all your help. Here is another one. I have a file [/home/ethan/PHP/RecNum] which consists of the number 1005. It also has been 1005. No matter how I do it, it is only recognized as 1. Here is the code. In this case, the file is 1005. $fptr1 = fopen(/home/ethan/PHP/RecNum, r+); $recNum = (int)fscanf($fptr1,%s); echo $recNum; //which always is 1 If I do not typecast $recNum to int, the value is Array. Thanks. Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] == Artur - Thanks. I also wanted to be able to increment recNum. Her is what finally worked for me: $fptr1 = fopen(/home/ethan/PHP/RecNum, r+); $recNum = fscanf($fptr1,%d); $sql1 = insert into intake2 (Site,Record,BMI) values ('A',$recNum[0],19); $recNum[0] = $recNum[0] + 1; rewind($fptr1); fprintf($fptr1,%d, $recNum[0]); Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Reading from file
On 02/11/10 14:34, Ethan Rosenberg wrote: Dear List - Thank you for all your help. Here is another one. I have a file [/home/ethan/PHP/RecNum] which consists of the number 1005. It also has been 1005. No matter how I do it, it is only recognized as 1. Here is the code. In this case, the file is 1005. $fptr1 = fopen(/home/ethan/PHP/RecNum, r+); $recNum = (int)fscanf($fptr1,%s); echo $recNum; //which always is 1 If I do not typecast $recNum to int, the value is Array. Right, which is what the manual says as well: http://www.php.net/fscanf If only two parameters were passed to this function, the values parsed will be returned as an array. So you can either do: $line = fscanf($fptr1, %s); $recNum = $line[0]; // get the first element in the array as $recNum or while ($line = fscanf($fptr1, %s\n)) { $recNum = $line[0]; } -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP Exercises
Sanjay Mantoor wrote: Twaha, I found PHP manual is best. Refer http://www.php.net/manual/en/ On Fri, Oct 31, 2008 at 1:49 PM, Twaha Uddessy udde...@yahoo.com wrote: Hello all, Iam new here,trying hard to study PHP.Can anyone help me with a link where I can get PHP exercises and possible solution?. Thank you in advance -- Sanjay Mantoor -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php I know this post is old, but if you are still looking for PHP exercises, there's a http://phpexercises.com site with 26 beginner to intemediate exercises. The site is PHP Exercises, and it covers variables, control structures, forms, arrays and functions, as well as some other things. You see the problem first, then click a button for the answer script and a link to the script's output. We just set it up, and would appreciate comments about how it works. -- View this message in context: http://old.nabble.com/PHP-Exercises-tp20261635p30099292.html Sent from the Php - Database mailing list archive at Nabble.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] big table / hadoop / map reduce
sure that was a bit more helpful, thanks :) i was still wondering to what other use cases would that apply. This is a good article (best so far i guess): http://code.google.com/edu/parallel/mapreduce-tutorial.html The thing is that reduce has to aggregate data or it would be impractical. So i am trying to see more examples to fully understand the limitations of the method. Lets say i want to find top 10 IP addresses in an access log: - split log into small files - i take one fragment (one file) - worker maps to a list of ip, 1 - before reduce is called data is sorted by ip - reduce makes ip, totalCountPerLogFileSample so i have a bunch of files with aggregated lists of IP, totalCountPerFile. But then would it not have to be merged across all results again? with another sort/reduce call? or to avoid that do i need initial data to be already clustered so one ip appears only in one chunk file? Does it make sense? As i said i am still trying to figure out how should it be applied and when ... also how to transform problems to make it still work : ) I want to write some simple map reduce like the one above just to see it working and play around a bit :) cheers Art On 22 October 2010 16:49, Andrés G. Montañez andresmonta...@gmail.com wrote: Imagine you have to get track of some kind of traffic, for example, ad impressions; lets supose that you have millions of those hits; you will have to have a few servers to receive the notifications of the impression of an ad. After the end of the day, you will have that info across a bunch of servers; mostly you will have a record of each impression indicating the Identifier (id) of the Ad. To this info to become useful, you will have to agregate it; for example to know which is the Ad with most impressions. You will have to iterate over all servers and MAP the info into one place; now that you have all the info, you will have to REDUCE it; so you will have one record per Ad identifier indicating the TOTAL impressions of that day. That's the basic idea. It's like aftermath of Divide and Conquer. Hope this will be useful. Cheers. On 22 October 2010 13:27, Artur Ejsmont ejsmont.ar...@gmail.com wrote: hehe sorry but this does not help :-) i can google for wikipedia definitions. I was hoping for some really good articles/examples that would put it into enough context. I would like to have good idea when it could be useful. So far had no luck with that. Its like with design patterns ... people who dont understand them should not write articles trying to explain them to others :P Art On 22 October 2010 15:29, Andrés G. Montañez andresmonta...@gmail.com wrote: Hi Artur, Here is an article on wikipedia: http://en.wikipedia.org/wiki/MapReduce And here are the native implementations in php: http://www.php.net/manual/en/function.array-map.php http://www.php.net/manual/en/function.array-reduce.php The basic idea is to gather a lot of data, from several nodes, and map them togheter; then, assuming a lot of this data is repeated across the dataset, we reduce them. Cheers. On 22 October 2010 12:14, Artur Ejsmont ejsmont.ar...@gmail.com wrote: Hi there guys and girls Have anyone came across any reasonable explanation / articles on how hadoop and map reduce work in practice? i have read a few articles now and then and i must say i am puzzled am i stupid or they just cant find an easy way to explain it? :P What i would hope for is explanation on simple example of application with some code samples preferably. anyone good at it here? cheers -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- Visit me at: http://artur.ejsmont.org/blog/ -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- Visit me at: http://artur.ejsmont.org/blog/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] big table / hadoop / map reduce
Hi Artur, in your IPs examples, lets supouse you have ten access log files (from ten different servers), there you already have the mapping part done. Then you reduce each log into anonther new file, indicating the IP address and the times it's repeated. At this stage you have a reduced version of each log file; then you need to map them into a new unique file, this file will be the merge of all the reduced versions of the log files. This this unique file, you will need to reduce it again, and there you will have an unique file with all the IPs address and the times they appear. There is no limit on the times you can call map and reduce. Cheers. On 30 October 2010 15:51, Artur Ejsmont ejsmont.ar...@gmail.com wrote: sure that was a bit more helpful, thanks :) i was still wondering to what other use cases would that apply. This is a good article (best so far i guess): http://code.google.com/edu/parallel/mapreduce-tutorial.html The thing is that reduce has to aggregate data or it would be impractical. So i am trying to see more examples to fully understand the limitations of the method. Lets say i want to find top 10 IP addresses in an access log: - split log into small files - i take one fragment (one file) - worker maps to a list of ip, 1 - before reduce is called data is sorted by ip - reduce makes ip, totalCountPerLogFileSample so i have a bunch of files with aggregated lists of IP, totalCountPerFile. But then would it not have to be merged across all results again? with another sort/reduce call? or to avoid that do i need initial data to be already clustered so one ip appears only in one chunk file? Does it make sense? As i said i am still trying to figure out how should it be applied and when ... also how to transform problems to make it still work : ) I want to write some simple map reduce like the one above just to see it working and play around a bit :) cheers Art On 22 October 2010 16:49, Andrés G. Montañez andresmonta...@gmail.com wrote: Imagine you have to get track of some kind of traffic, for example, ad impressions; lets supose that you have millions of those hits; you will have to have a few servers to receive the notifications of the impression of an ad. After the end of the day, you will have that info across a bunch of servers; mostly you will have a record of each impression indicating the Identifier (id) of the Ad. To this info to become useful, you will have to agregate it; for example to know which is the Ad with most impressions. You will have to iterate over all servers and MAP the info into one place; now that you have all the info, you will have to REDUCE it; so you will have one record per Ad identifier indicating the TOTAL impressions of that day. That's the basic idea. It's like aftermath of Divide and Conquer. Hope this will be useful. Cheers. On 22 October 2010 13:27, Artur Ejsmont ejsmont.ar...@gmail.com wrote: hehe sorry but this does not help :-) i can google for wikipedia definitions. I was hoping for some really good articles/examples that would put it into enough context. I would like to have good idea when it could be useful. So far had no luck with that. Its like with design patterns ... people who dont understand them should not write articles trying to explain them to others :P Art On 22 October 2010 15:29, Andrés G. Montañez andresmonta...@gmail.com wrote: Hi Artur, Here is an article on wikipedia: http://en.wikipedia.org/wiki/MapReduce And here are the native implementations in php: http://www.php.net/manual/en/function.array-map.php http://www.php.net/manual/en/function.array-reduce.php The basic idea is to gather a lot of data, from several nodes, and map them togheter; then, assuming a lot of this data is repeated across the dataset, we reduce them. Cheers. On 22 October 2010 12:14, Artur Ejsmont ejsmont.ar...@gmail.com wrote: Hi there guys and girls Have anyone came across any reasonable explanation / articles on how hadoop and map reduce work in practice? i have read a few articles now and then and i must say i am puzzled am i stupid or they just cant find an easy way to explain it? :P What i would hope for is explanation on simple example of application with some code samples preferably. anyone good at it here? cheers -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- Visit me at: http://artur.ejsmont.org/blog/ -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- Visit me at: http://artur.ejsmont.org/blog/ -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] big table / hadoop / map reduce
yeah i think that would make sense. if you find more good examples from different areas let me know ... i think i get the basic idea ... will try to apply it some time :) cheers :) art On 30 October 2010 18:58, Andrés G. Montañez andresmonta...@gmail.com wrote: Hi Artur, in your IPs examples, lets supouse you have ten access log files (from ten different servers), there you already have the mapping part done. Then you reduce each log into anonther new file, indicating the IP address and the times it's repeated. At this stage you have a reduced version of each log file; then you need to map them into a new unique file, this file will be the merge of all the reduced versions of the log files. This this unique file, you will need to reduce it again, and there you will have an unique file with all the IPs address and the times they appear. There is no limit on the times you can call map and reduce. Cheers. On 30 October 2010 15:51, Artur Ejsmont ejsmont.ar...@gmail.com wrote: sure that was a bit more helpful, thanks :) i was still wondering to what other use cases would that apply. This is a good article (best so far i guess): http://code.google.com/edu/parallel/mapreduce-tutorial.html The thing is that reduce has to aggregate data or it would be impractical. So i am trying to see more examples to fully understand the limitations of the method. Lets say i want to find top 10 IP addresses in an access log: - split log into small files - i take one fragment (one file) - worker maps to a list of ip, 1 - before reduce is called data is sorted by ip - reduce makes ip, totalCountPerLogFileSample so i have a bunch of files with aggregated lists of IP, totalCountPerFile. But then would it not have to be merged across all results again? with another sort/reduce call? or to avoid that do i need initial data to be already clustered so one ip appears only in one chunk file? Does it make sense? As i said i am still trying to figure out how should it be applied and when ... also how to transform problems to make it still work : ) I want to write some simple map reduce like the one above just to see it working and play around a bit :) cheers Art On 22 October 2010 16:49, Andrés G. Montañez andresmonta...@gmail.com wrote: Imagine you have to get track of some kind of traffic, for example, ad impressions; lets supose that you have millions of those hits; you will have to have a few servers to receive the notifications of the impression of an ad. After the end of the day, you will have that info across a bunch of servers; mostly you will have a record of each impression indicating the Identifier (id) of the Ad. To this info to become useful, you will have to agregate it; for example to know which is the Ad with most impressions. You will have to iterate over all servers and MAP the info into one place; now that you have all the info, you will have to REDUCE it; so you will have one record per Ad identifier indicating the TOTAL impressions of that day. That's the basic idea. It's like aftermath of Divide and Conquer. Hope this will be useful. Cheers. On 22 October 2010 13:27, Artur Ejsmont ejsmont.ar...@gmail.com wrote: hehe sorry but this does not help :-) i can google for wikipedia definitions. I was hoping for some really good articles/examples that would put it into enough context. I would like to have good idea when it could be useful. So far had no luck with that. Its like with design patterns ... people who dont understand them should not write articles trying to explain them to others :P Art On 22 October 2010 15:29, Andrés G. Montañez andresmonta...@gmail.com wrote: Hi Artur, Here is an article on wikipedia: http://en.wikipedia.org/wiki/MapReduce And here are the native implementations in php: http://www.php.net/manual/en/function.array-map.php http://www.php.net/manual/en/function.array-reduce.php The basic idea is to gather a lot of data, from several nodes, and map them togheter; then, assuming a lot of this data is repeated across the dataset, we reduce them. Cheers. On 22 October 2010 12:14, Artur Ejsmont ejsmont.ar...@gmail.com wrote: Hi there guys and girls Have anyone came across any reasonable explanation / articles on how hadoop and map reduce work in practice? i have read a few articles now and then and i must say i am puzzled am i stupid or they just cant find an easy way to explain it? :P What i would hope for is explanation on simple example of application with some code samples preferably. anyone good at it here? cheers -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- Visit me at: http://artur.ejsmont.org/blog/ -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- Visit me at:
Re: [PHP-DB] Incrementing Primary Key
From: Ethan Rosenberg eth...@earthlink.net To: rquadl...@googlemail.com, Ethan Rosenberg eth...@earthlink.net Cc: php-db-lists.php.net php-db@lists.php.net Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: Re: [PHP-DB] Incrementing Primary Key At 10:10 AM 10/27/2010, Richard Quadling wrote: On 27 October 2010 14:11, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. Â The sites will be identified by a letter {A,B,C }. Â The medical record number [primary key] Â will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. Just doing my job, sir! Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY Thank you. I'm using MySQL, which I do not think has the ability to auto-increment a primary key from any value other than 1. Here is some pseudo code. Please help me to set it up properly. Table Intake SiteID char(2) primary key not null, // This is A for site1, B for site 2 RecordNum int(10,0) primary key not null auto_increment, // increment starts from 10001 etc. Thanks Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] Hi Ethan, This will set a new auto increment value for a table . ALTER TABLE RecordNum AUTO_INCREMENT=1001 Regards, Max. -- 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] Incrementing Primary Key
At 03:40 AM 10/28/2010, Max E.K wrote: From: Ethan Rosenberg eth...@earthlink.net To: rquadl...@googlemail.com, Ethan Rosenberg eth...@earthlink.net Cc: php-db-lists.php.net php-db@lists.php.net Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: Re: [PHP-DB] Incrementing Primary Key At 10:10 AM 10/27/2010, Richard Quadling wrote: On 27 October 2010 14:11, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. Ã The sites will be identified by a letter {A,B,C }. Ã The medical record number [primary key] Ã will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. Just doing my job, sir! Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY Thank you. I'm using MySQL, which I do not think has the ability to auto-increment a primary key from any value other than 1. Here is some pseudo code. Please help me to set it up properly. Table Intake SiteID char(2) primary key not null, // This is A for site1, B for site 2 RecordNum int(10,0) primary key not null auto_increment, // increment starts from 10001 etc. Thanks Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] Hi Ethan, This will set a new auto increment value for a table . ALTER TABLE RecordNum AUTO_INCREMENT=1001 Regards, Max. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php == Max - Thanks. I must be doing something wrong, since the RecordNum starts from 1, and increments by 1. Maybe I am setting up the table incorrectly? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Incrementing Primary Key
On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 03:40 AM 10/28/2010, Max E.K wrote: From: Ethan Rosenberg eth...@earthlink.net To: rquadl...@googlemail.com, Ethan Rosenberg eth...@earthlink.net Cc: php-db-lists.php.net php-db@lists.php.net Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: Re: [PHP-DB] Incrementing Primary Key At 10:10 AM 10/27/2010, Richard Quadling wrote: On 27 October 2010 14:11, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. Â The sites will be identified by a letter {A,B,C }. Â The medical record number [primary key] Â will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. Just doing my job, sir! Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY Thank you. I'm using MySQL, which I do not think has the ability to auto-increment a primary key from any value other than 1. Here is some pseudo code. Please help me to set it up properly. Table Intake SiteID char(2) primary key not null, // This is A for site1, B for site 2 RecordNum int(10,0) primary key not null auto_increment, // increment starts from 10001 etc. Thanks Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] Hi Ethan, This will set a new auto increment value for a table . ALTER TABLE RecordNum AUTO_INCREMENT=1001 Regards, Max. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php == Max - Thanks. I must be doing something wrong, since the RecordNum starts from 1, and increments by 1. Maybe I am setting up the table incorrectly? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php see http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html To set an auto increment start value ALTER TABLE tbl AUTO_INCREMENT = 100; -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Incrementing Primary Key
At 01:17 PM 10/28/2010, Bastien Koert wrote: On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 03:40 AM 10/28/2010, Max E.K wrote: From: Ethan Rosenberg eth...@earthlink.net To: rquadl...@googlemail.com, Ethan Rosenberg eth...@earthlink.net Cc: php-db-lists.php.net php-db@lists.php.net Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: Re: [PHP-DB] Incrementing Primary Key At 10:10 AM 10/27/2010, Richard Quadling wrote: On 27 October 2010 14:11, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. Ã The sites will be identified by a letter {A,B,C }. Ã The medical record number [primary key] Ã will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. Just doing my job, sir! Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY Thank you. I'm using MySQL, which I do not think has the ability to auto-increment a primary key from any value other than 1. Here is some pseudo code. Please help me to set it up properly. Table Intake SiteID char(2) primary key not null, // This is A for site1, B for site 2 RecordNum int(10,0) primary key not null auto_increment, // increment starts from 10001 etc. Thanks Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] Hi Ethan, This will set a new auto increment value for a table . ALTER TABLE RecordNum AUTO_INCREMENT=1001 Regards, Max. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php == Max - Thanks. I must be doing something wrong, since the RecordNum starts from 1, and increments by 1. Maybe I am setting up the table incorrectly? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php see: deleted because of spam filter. To set an auto increment start value ALTER TABLE tbl AUTO_INCREMENT = 100; Bastien Cat, the other other white meat Bastien - Thanks. It still does not work. This is what I have done to change the auto_increment: drop exiting_table;//called intake create table intake2 (Site char not null, Record int(10) not null auto_increment, BMI int(2),primary key(Site,Record)); alter table intake2 auto_increment=1000; insert into intake2 (Site,Record,BMI) values ('A',(null),15); insert into intake2 (Site,Record,BMI) values ('A',(null),18); insert into intake2 (Site,Record,BMI) values ('A',(null),13); mysql select * from intake2; +--++--+ | Site | Record | BMI | +--++--+ | A| 1 | 15 | | A| 2 | 18 | | A| 3 | 19 | +--++--+ 3 rows in set (0.00 sec) What is my mistake? Ethan MySQL 5.1 PHP
RE: [PHP-DB] Incrementing Primary Key
The () around null seem like that could be the issue, just try to put NULL for the value -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: Thursday, October 28, 2010 2:02 PM To: Bastien Koert Cc: Max E.K; php-db-lists.php.net; rquadl...@googlemail.com Subject: Re: [PHP-DB] Incrementing Primary Key At 01:17 PM 10/28/2010, Bastien Koert wrote: On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 03:40 AM 10/28/2010, Max E.K wrote: From: Ethan Rosenberg eth...@earthlink.net To: rquadl...@googlemail.com, Ethan Rosenberg eth...@earthlink.net Cc: php-db-lists.php.net php-db@lists.php.net Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: Re: [PHP-DB] Incrementing Primary Key At 10:10 AM 10/27/2010, Richard Quadling wrote: On 27 October 2010 14:11, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. Ã The sites will be identified by a letter {A,B,C }. Ã The medical record number [primary key] Ã will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. Just doing my job, sir! Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY Thank you. I'm using MySQL, which I do not think has the ability to auto-increment a primary key from any value other than 1. Here is some pseudo code. Please help me to set it up properly. Table Intake SiteID char(2) primary key not null, // This is A for site1, B for site 2 RecordNum int(10,0) primary key not null auto_increment, // increment starts from 10001 etc. Thanks Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] Hi Ethan, This will set a new auto increment value for a table . ALTER TABLE RecordNum AUTO_INCREMENT=1001 Regards, Max. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php == Max - Thanks. I must be doing something wrong, since the RecordNum starts from 1, and increments by 1. Maybe I am setting up the table incorrectly? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php see: deleted because of spam filter. To set an auto increment start value ALTER TABLE tbl AUTO_INCREMENT = 100; Bastien Cat, the other other white meat Bastien - Thanks. It still does not work. This is what I have done to change the auto_increment: drop exiting_table;//called intake create table intake2 (Site char not null, Record int(10) not null auto_increment, BMI int(2),primary key(Site,Record)); alter
RE: [PHP-DB] Incrementing Primary Key
Actually, after looking at it further, you have the records set to only have not null values yet you are passing a null value to it. -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: Thursday, October 28, 2010 2:02 PM To: Bastien Koert Cc: Max E.K; php-db-lists.php.net; rquadl...@googlemail.com Subject: Re: [PHP-DB] Incrementing Primary Key At 01:17 PM 10/28/2010, Bastien Koert wrote: On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 03:40 AM 10/28/2010, Max E.K wrote: From: Ethan Rosenberg eth...@earthlink.net To: rquadl...@googlemail.com, Ethan Rosenberg eth...@earthlink.net Cc: php-db-lists.php.net php-db@lists.php.net Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: Re: [PHP-DB] Incrementing Primary Key At 10:10 AM 10/27/2010, Richard Quadling wrote: On 27 October 2010 14:11, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. Ã The sites will be identified by a letter {A,B,C }. Ã The medical record number [primary key] Ã will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. Just doing my job, sir! Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY Thank you. I'm using MySQL, which I do not think has the ability to auto-increment a primary key from any value other than 1. Here is some pseudo code. Please help me to set it up properly. Table Intake SiteID char(2) primary key not null, // This is A for site1, B for site 2 RecordNum int(10,0) primary key not null auto_increment, // increment starts from 10001 etc. Thanks Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] Hi Ethan, This will set a new auto increment value for a table . ALTER TABLE RecordNum AUTO_INCREMENT=1001 Regards, Max. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php == Max - Thanks. I must be doing something wrong, since the RecordNum starts from 1, and increments by 1. Maybe I am setting up the table incorrectly? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php see: deleted because of spam filter. To set an auto increment start value ALTER TABLE tbl AUTO_INCREMENT = 100; Bastien Cat, the other other white meat Bastien - Thanks. It still does not work. This is what I have done to change the auto_increment: drop exiting_table;//called intake create table intake2 (Site char not null, Record int(10) not null auto_increment, BMI
Re: [PHP-DB] Incrementing Primary Key
On Thu, Oct 28, 2010 at 2:14 PM, Jimmy Sole jimmys...@gmail.com wrote: Actually, after looking at it further, you have the records set to only have not null values yet you are passing a null value to it. -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: Thursday, October 28, 2010 2:02 PM To: Bastien Koert Cc: Max E.K; php-db-lists.php.net; rquadl...@googlemail.com Subject: Re: [PHP-DB] Incrementing Primary Key At 01:17 PM 10/28/2010, Bastien Koert wrote: On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 03:40 AM 10/28/2010, Max E.K wrote: From: Ethan Rosenberg eth...@earthlink.net To: rquadl...@googlemail.com, Ethan Rosenberg eth...@earthlink.net Cc: php-db-lists.php.net php-db@lists.php.net Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: Re: [PHP-DB] Incrementing Primary Key At 10:10 AM 10/27/2010, Richard Quadling wrote: On 27 October 2010 14:11, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. Â The sites will be identified by a letter {A,B,C }. Â The medical record number [primary key] Â will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. Just doing my job, sir! Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY Thank you. I'm using MySQL, which I do not think has the ability to auto-increment a primary key from any value other than 1. Here is some pseudo code. Please help me to set it up properly. Table Intake SiteID char(2) primary key not null, // This is A for site1, B for site 2 RecordNum int(10,0) primary key not null auto_increment, // increment starts from 10001 etc. Thanks Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] Hi Ethan, This will set a new auto increment value for a table . ALTER TABLE RecordNum AUTO_INCREMENT=1001 Regards, Max. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php == Max - Thanks. I must be doing something wrong, since the RecordNum starts from 1, and increments by 1. Maybe I am setting up the table incorrectly? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php see: deleted because of spam filter. To set an auto increment start value ALTER TABLE tbl AUTO_INCREMENT = 100; Bastien Cat, the other other white meat Bastien - Thanks. It still does not work. This is what I have done to change the auto_increment: drop exiting_table;//called intake create table intake2
Re: [PHP-DB] Variable Assignment
mysql SET @st=100; mysql SELECT @st; +--+ | @st | +--+ | 100 | +--+ Cheers. On 28 October 2010 19:10, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - I cannot figure this one out: mysql $st=1000; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$st=1000' at line 1 Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Variable Assignment
At 05:13 PM 10/28/2010, Andrés G. Montañez wrote: mysql SET @st=100; mysql SELECT @st; +--+ | @st | +--+ | 100 | +--+ Cheers. On 28 October 2010 19:10, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - I cannot figure this one out: mysql $st=1000; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$st=1000' at line 1 Ethan MySQL 5.1  PHP 5  Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay Andres - Thanks. It works! However, I am totally confused. All the texts state that to set a variable, the code is: $st=100; What are they missing and what am I missing? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Variable Assignment
However, I am totally confused. All the texts state that to set a variable, the code is: $st=100; That's how you do it in php. Doing it in the db is different and depends on the db you are using. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] printf
Mysql doesn't have a printf function Regards Den 2010 10 29 00:47 skrev Ethan Rosenberg eth...@earthlink.net: Dear List - I am afraid that I am missing something in a major way. mysql printf(%b %d %f %s\n, 123, 123, 123, test); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'printf(%b %d %f %s\n, 123, 123, 123, test)' at line 1 Where is my mistake? The code above is copied from the MySQL manual!! And while we are on the subject of my ignorance. I wish to write to file named Site the letter A, and read it back. I wish to write to file named Starter the number 1000 , and read it back. How do I do it? I'd better ask these questions now, so when they come up in the next hour, I will not have to bother you with another email! Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Incrementing Primary Key
I think having a table for each site would be sufficent -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: Wednesday, October 27, 2010 9:11 AM To: php-db-lists.php.net Subject: [PHP-DB] Incrementing Primary Key Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. The sites will be identified by a letter {A,B,C }. The medical record number [primary key] will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan -- 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] Incrementing Primary Key
Just set the primary key to AUTO_INCREMENT on each field and you won't have to worry about it -Original Message- From: Ethan Rosenberg [mailto:eth...@earthlink.net] Sent: Wednesday, October 27, 2010 9:11 AM To: php-db-lists.php.net Subject: [PHP-DB] Incrementing Primary Key Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. The sites will be identified by a letter {A,B,C }. The medical record number [primary key] will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan -- 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] Incrementing Primary Key
Hi Ethan, in this case, you should manage the keys by your application, and store the used ids in aonther table, similar to a sequence in Oracle or Postgresql. Thats so if you want all the records un one table (a field for the letter, and the other for the numeric part). Otherwise, having a table for each site will be enough, as Jimmy suggests. -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Incrementing Primary Key
In my opinion, having a database for each site would require a lot of unnecessary work, as you would have to connect to every database in order to handle the sites. Having one database with tables for all the sites would be more productive as you would not have to change the connection info for PHP every time you want to use a different database. I use the PDO mysql adapter, what do you use? -Original Message- From: Andrés G. Montañez [mailto:andresmonta...@gmail.com] Sent: Wednesday, October 27, 2010 9:19 AM To: Ethan Rosenberg Cc: php-db-lists.php.net Subject: Re: [PHP-DB] Incrementing Primary Key Hi Ethan, in this case, you should manage the keys by your application, and store the used ids in aonther table, similar to a sequence in Oracle or Postgresql. Thats so if you want all the records un one table (a field for the letter, and the other for the numeric part). Otherwise, having a table for each site will be enough, as Jimmy suggests. -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- 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] Incrementing Primary Key
Hi Ethan, I am working currently as clinical developer and have developed multi site databases for clinical trials. I would say the approach you take will depend on number of factors. How is the site connectivity? I had a scenario where sites had erratic and slow connectivity so i had to setup MySql server at each site and let that data sync over to a central location, with help of mysqldump then i put all the data together into one database from 3 replicas. The application is more responsive over LAN and users are less likely to complain when entering data(can be a major difference between success / failure) If connectivity is not an issue and the app will reside on a central web server then putting everything in a single database is an option. Of course your database design will have to reflect that using appropriate keys to be able to tie the CRF data to a particular site patient visit combination. If you chose to go with replication, then you have to take care on how you auto increment values so that there are no collisions, best is to assign a range to each site this is the simplest way. 1001 - 5000 Site A 5001 - 10001 Site B Of course you will need some input from your statisticians on how they want the data, that will also drive your design decisions on what works best. Separate db or not: I would separate data for each study into its own database, its much easier in terms of administration and locking the study. Is the study single blinded, double blinded ? How will randomization take place ? These are all questions you need to know before you can decide how you structure your database and application therefore. If you have other questions feel free to get in touch. with kind regards, Max. Max Kimambo Franz-Stenzer-Straße, 51 12679, Berlin. T: +493057706550 (new number) M: +4917649520175 - Original Message - From: Ethan Rosenberg eth...@earthlink.net To: php-db-lists.php.net php-db@lists.php.net Sent: Wednesday, October 27, 2010 3:11:00 PM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: [PHP-DB] Incrementing Primary Key Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. The sites will be identified by a letter {A,B,C }. The medical record number [primary key] will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan -- 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] Incrementing Primary Key
Yes indeed, as Jimmy says, having a database for each site is impractical and a waste of resources. On 27 October 2010 11:23, Jimmy Sole jimmys...@gmail.com wrote: In my opinion, having a database for each site would require a lot of unnecessary work, as you would have to connect to every database in order to handle the sites. Having one database with tables for all the sites would be more productive as you would not have to change the connection info for PHP every time you want to use a different database. I use the PDO mysql adapter, what do you use? -Original Message- From: Andrés G. Montañez [mailto:andresmonta...@gmail.com] Sent: Wednesday, October 27, 2010 9:19 AM To: Ethan Rosenberg Cc: php-db-lists.php.net Subject: Re: [PHP-DB] Incrementing Primary Key Hi Ethan, in this case, you should manage the keys by your application, and store the used ids in aonther table, similar to a sequence in Oracle or Postgresql. Thats so if you want all the records un one table (a field for the letter, and the other for the numeric part). Otherwise, having a table for each site will be enough, as Jimmy suggests. -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Incrementing Primary Key
On 27 October 2010 14:11, Ethan Rosenberg eth...@earthlink.net wrote: Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. The sites will be identified by a letter {A,B,C }. The medical record number [primary key] will start at 1001 and increment by one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. How do I do this? Do I need a separate database for each site? Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. Just doing my job, sir! Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php