[PHP-DB]problem in creating the ibm_db2 extension
Hi All, I have installed db2 on my* fedora core 7* system and now i wan t to access the database using the* php apis*. For this reason I downloaded the *ibm_db2 1.6* package and tried to create the extension. I did the following phpize output is Configuring for: PHP Api Version: 20041225 Zend Module Api No: 20060613 Zend Extension Api No: 220060519 ./configure --with-IBM_DB2=/opt/ibm/db2/V9.5 part of the ouput checking for re2c... no configure: WARNING: You will need re2c 0.12.0 or later if you want to regenerate PHP parsers. checking for gawk... gawk checking for IBM_DB2 support... yes, shared checking Looking for DB2 CLI libraries... checking in /opt/ibm/db2/V9.5... checking in /opt/ibm/db2/V9.5/lib64... checking in /opt/ibm/db2/V9.5/lib32... found checking for DB2 CLI include files in default path... checking in /opt/ibm/db2/V9.5... not found and I get the above error Any help would be of great help.Thanks in advance With Regards, harsha
[PHP-DB] Re: [PHP] Best practices for using MySQL index
Shelley wrote: Hi all, I am currently responsible for a subscription module and need to design the DB tables and write code. I have described my table design and queries in the post: http://phparch.cn/index.php/mysql/38-MySQL-configuration/152-best-practices-for-using-mysql-index The problem is, in a short time the table will hold millions of records. So the query and index optimization is very important. Any suggestion will be greatly appreciated. Hi, While this is not a MySQL mailing list, I try to give you some hints and keep it short. Index on most integer fields only. Text fields can be indexed, but is not important when you design your DB well. Don't index just all integer fields. Keep track of the cardinality of a column. If you expect a field to have 100.000 records, but with only 500 distinct values it has no use to put an index on that column. A full record search is quicker. Put the columns with the highest cardinality as the first keys, since MySQL will find these if no index is explicitly given. You can look at an index with SHOW INDEX FROM table and this gives you a column cardinality. Try out your select statements and use EXPLAIN SELECT whatever FROM table and use some joins on other tables. This will show you which possible indexes are found and which one is being used for that query. You can sometimes force or ignore an index being used like this SELECT whatever FROM table USE INDEX (userID). Try the MySQL manual for more options. But do use the EXPLAIN statement to have a close look on the use of indexes and the use of sorting methods. Because both are important. Having a good index, but a slow sorting method won't get you good results. I hope this is a good short hint on using indexes. But becoming a master does not come over night. Try the website www.mysqlperformanceblog.com for more good solid tips on these topics. Aschwin Wesselius -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Best practices for using MySQL index
On Wed, Apr 30, 2008 at 5:14 PM, Aschwin Wesselius [EMAIL PROTECTED] wrote: Shelley wrote: Hi all, I am currently responsible for a subscription module and need to design the DB tables and write code. I have described my table design and queries in the post: http://phparch.cn/index.php/mysql/38-MySQL-configuration/152-best-practices-for-using-mysql-index The problem is, in a short time the table will hold millions of records. So the query and index optimization is very important. Any suggestion will be greatly appreciated. Hi, While this is not a MySQL mailing list, I try to give you some hints and keep it short. Index on most integer fields only. Text fields can be indexed, but is not important when you design your DB well. Don't index just all integer fields. Keep track of the cardinality of a column. If you expect a field to have 100.000 records, but with only 500 distinct values it has no use to put an index on that column. A full record search is quicker. Hmmm... That's new. :) Put the columns with the highest cardinality as the first keys, since MySQL will find these if no index is explicitly given. You can look at an index with SHOW INDEX FROM table and this gives you a column cardinality. Try out your select statements and use EXPLAIN SELECT whatever FROM table and use some joins on other tables. This will show you which possible indexes are found and which one is being used for that query. You can sometimes force or ignore an index being used like this SELECT whatever FROM table USE INDEX (userID). Try the MySQL manual for more options. But do use the EXPLAIN statement to have a close look on the use of indexes and the use of sorting methods. Because both are important. Having a good index, but a slow sorting method won't get you good results. I hope this is a good short hint on using indexes. Yes. It is. But becoming a master does not come over night. Try the website www.mysqlperformanceblog.com for more good solid tips on these topics. Good link. Thanks. Aschwin Wesselius -- Regards, Shelley
[PHP-DB] Re: [PHP] Best practices for using MySQL index
Shelley wrote: Don't index just all integer fields. Keep track of the cardinality of a column. If you expect a field to have 100.000 records, but with only 500 distinct values it has no use to put an index on that column. A full record search is quicker. Hmmm... That's new. :) Well, to give you a good measure: keep the cardinality between 30 to 70-80 percent of your total records in a column. But sometimes your field is NULL or empty, so it really depends. You can't just put it into a standard configuration. And it also really depends on how many records a table contains etc. Besides that, benchmarking your development environment (you do have one do you?) can gives you a good idea on how your hardware and setup performs. Aschwin Wesselius
[PHP-DB] Re: [PHP] Best practices for using MySQL index
On Wed, 2008-04-30 at 11:14 +0200, Aschwin Wesselius wrote: Shelley wrote: Hi all, I am currently responsible for a subscription module and need to design the DB tables and write code. I have described my table design and queries in the post: http://phparch.cn/index.php/mysql/38-MySQL-configuration/152-best-practices-for-using-mysql-index The problem is, in a short time the table will hold millions of records. So the query and index optimization is very important. Any suggestion will be greatly appreciated. Hi, While this is not a MySQL mailing list, I try to give you some hints and keep it short. Index on most integer fields only. Text fields can be indexed, but is not important when you design your DB well. Could you describe a well designed DB that contains searchable text that doesn't contain a text index... fulltext or otherwise. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Timestamps
On 30 Apr 2008, at 16:29, Jason Pruim wrote: Okay... So I know this should be simple... Trying to store a timestamp in a MySQL database... The timestamp I am making like so: $modifiedTimestamp = time(); and then just $sql = Update `mytable` set timestamp='$modifiedTimestamp' where Record='1'; Simple right? Not quite...in my database it's storing a 0 in the timestamp field which is a int(10) field. I have googled, and searched manuals, but have not been able to figure out what is going on Any Ideas? timestamp is a reserved word. Try putting it in backticks. -Stut -- http://stut.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Timestamps
On Apr 30, 2008, at 11:35 AM, Stut wrote: On 30 Apr 2008, at 16:29, Jason Pruim wrote: Okay... So I know this should be simple... Trying to store a timestamp in a MySQL database... The timestamp I am making like so: $modifiedTimestamp = time(); and then just $sql = Update `mytable` set timestamp='$modifiedTimestamp' where Record='1'; Simple right? Not quite...in my database it's storing a 0 in the timestamp field which is a int(10) field. I have googled, and searched manuals, but have not been able to figure out what is going on Any Ideas? timestamp is a reserved word. Try putting it in backticks. Okay, so I did a really crappy job at my sudo code... The field name is actually Last_Updated. so my update code looks like this: Last_Updated='$modifiedTimestamp' *Slaps his wrist... Bad copy/paste! BAD!!! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Timestamps
On Apr 30, 2008, at 10:54 AM, Jason Pruim wrote: Hi Yves, Thanks for the tip, that worked, I think I'll use that from now on.. Just out of curiosity though, any idea why it wasn't working as I was writing it :) Did you try putting the query that PHP is generating in phpMyAdmin or MySQL Query Browser? See if it throws an error when attempting to update. It *appears* that the query should work. ~Philip PS... Was it you, Jason, or someone else who asked about the security of the community knowing their database structure and I encouraged the use of `backticks` around all field and table names? On Apr 30, 2008, at 11:47 AM, YVES SUCAET wrote: Hi Jason, It's not because you create a date/time value that you automatically have an integer-value. You need to specify first that you want the date/ time value converted to an integer value first. See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp for an example of how to do this. Actually, by using this function, you probably don't even need to create the $modifiedTimestamp variable anymore. You can just write your SQL query as follows: $sql = Update `mytable` set timestamp=UNIX_TIMESTAMP() where Record='1'; HTH, Yves -- Original Message -- Received: Wed, 30 Apr 2008 10:39:11 AM CDT From: Jason Pruim [EMAIL PROTECTED] To: Stut [EMAIL PROTECTED]Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Timestamps On Apr 30, 2008, at 11:35 AM, Stut wrote: On 30 Apr 2008, at 16:29, Jason Pruim wrote: Okay... So I know this should be simple... Trying to store a timestamp in a MySQL database... The timestamp I am making like so: $modifiedTimestamp = time(); and then just $sql = Update `mytable` set timestamp='$modifiedTimestamp' where Record='1'; Simple right? Not quite...in my database it's storing a 0 in the timestamp field which is a int(10) field. I have googled, and searched manuals, but have not been able to figure out what is going on Any Ideas? timestamp is a reserved word. Try putting it in backticks. Okay, so I did a really crappy job at my sudo code... The field name is actually Last_Updated. so my update code looks like this: Last_Updated='$modifiedTimestamp' *Slaps his wrist... Bad copy/paste! BAD!!! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB]problem in creating the ibm_db2 extension
H Thirividi wrote: Hi All, I have installed db2 on my* fedora core 7* system and now i wan t to access the database using the* php apis*. For this reason I downloaded the *ibm_db2 1.6* package and tried to create the extension. I did the following phpize output is Configuring for: PHP Api Version: 20041225 Zend Module Api No: 20060613 Zend Extension Api No: 220060519 ./configure --with-IBM_DB2=/opt/ibm/db2/V9.5 part of the ouput checking for re2c... no configure: WARNING: You will need re2c 0.12.0 or later if you want to regenerate PHP parsers. checking for gawk... gawk checking for IBM_DB2 support... yes, shared checking Looking for DB2 CLI libraries... checking in /opt/ibm/db2/V9.5... checking in /opt/ibm/db2/V9.5/lib64... checking in /opt/ibm/db2/V9.5/lib32... found checking for DB2 CLI include files in default path... checking in /opt/ibm/db2/V9.5... not found It's looking for the include/, lib/ etc folders under that (ie the headers and so on). Where are they located? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Best practices for using MySQL index
Index on most integer fields only. Text fields can be indexed, but is not important when you design your DB well. Don't index just all integer fields. Keep track of the cardinality of a column. If you expect a field to have 100.000 records, but with only 500 distinct values it has no use to put an index on that column. A full record search is quicker. Hmmm... That's new. :) To explain that further the idea is that if you have something like a 'status' field which can only hold 5 values, there's no point indexing it if there's a reasonably even spread. If you could only ever have a handful of fields with a status code of '1', then it's worth indexing if you have to find those particular records quickly. I don't think mysql supports partial indexes, but some databases do so you only index the fields that match a certain criteria. I'd suggest a more thorough approach to working out what to index rather than just trying to guess what's going on. Work out how long queries are taking (either use the mysql slow log or if you're using a database abstraction class, it should be easy enough to hack in) and concentrate on those first. http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database (While it's on a postgresql site, there's nothing specifically for postgresql in that article - the same rules apply to mysql, oracle, mssql). -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query
I have an interesting question. When I run the following query through my PHP script it produces 1 result. I know this because I echo the value of $num to the screen from the following syntax: $num=mysql_numrows($result); When I do the query in the SQL tab of phpMyAdmin there are 6 results. The 6 requests are correct. Any ideas why I have 2 different search results? Ron SELECT * FROM ministry_directory INNER JOIN ministry_directory_listing_categories ON ministry_directory.entry = ministry_directory_listing_categories.ministry_directory_entry WHERE ministry_directory.listing_type = 2 AND ministry_directory_listing_categories.ministry_directory_category_reference IN ( 10 ) ORDER BY ministry_directory.name ASC -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query
Ron Piggott wrote: I have an interesting question. When I run the following query through my PHP script it produces 1 result. I know this because I echo the value of $num to the screen from the following syntax: $num=mysql_numrows($result); When I do the query in the SQL tab of phpMyAdmin there are 6 results. The 6 requests are correct. Any ideas why I have 2 different search results? Show us the php code.. maybe you're overwriting the $result variable in your loop. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query
You just drew my attention my include that brings in the site menu and was changing the conflict. Thanks :) Ron On Thu, 2008-05-01 at 13:19 +1000, Chris wrote: Ron Piggott wrote: I have an interesting question. When I run the following query through my PHP script it produces 1 result. I know this because I echo the value of $num to the screen from the following syntax: $num=mysql_numrows($result); When I do the query in the SQL tab of phpMyAdmin there are 6 results. The 6 requests are correct. Any ideas why I have 2 different search results? Show us the php code.. maybe you're overwriting the $result variable in your loop. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
