[PHP-DB] PostgreSQL lib and character case
I use pg_fetch_array() to get a record content. But it seems that to access elements of the returned associative array, I should use lowercase field names. Is there any way to use case-insensitive field names? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL - counting number of instances of a word in a field
It is easy to get around this limitation: for each column containing indexable content, create a shadow column containing content prepared for FULLTEXT indexing. I usually do the following: - for each word: - replace all non whitespace, non-blank characters by _ - right pad to 4 with _ for example B.M.W. become B_M_W_, tax will become tax_ now you prepare similarly the string to MATCH actually doing so is good personal hygiene regardless of the value of ft_max_len set by the host. it is mandatory when you deal with languages which do not separate words by whitespaces (in my case Japanese) HTH Ignatius _ - Original Message - From: Gavin Amm [EMAIL PROTECTED] To: Ignatius Reilly [EMAIL PROTECTED] Sent: Wednesday, April 28, 2004 10:03 AM Subject: RE: [PHP-DB] MySQL - counting number of instances of a word in a field Yes, They do. Unfortunately the site is hosted by an ISP has set the indexed words to 3, the site requires essential words of 3 char length to be searched through, such as tax, ato, cgt, etc... I will certainly still index the 2 main columns in the table... Thanks for your help. -Original Message- From: Ignatius Reilly [mailto:[EMAIL PROTECTED] Sent: Wednesday, 28 April 2004 4:34 PM To: Gavin Amm; [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySQL - counting number of instances of a word in a field The functions do not exist. However, FULLTEXT indexes will do much of this work for you. I encourage you to experiment with them. _ - Original Message - From: Gavin Amm [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 28, 2004 7:18 AM Subject: [PHP-DB] MySQL - counting number of instances of a word in a field Hi guys, I'm trying to find a MySQL function(s) that will allow me to count the number of words in a field. For eg: [table: pages] id - title - keywords - body 1 - Home - home, page - This is my home page. p Enjoy your stay. 2 - Feedback - feedback, form, contact - Please enter any feedback or comments in the form below.brYour feedback will be used to improve our service. How do I, for example, count the number of instances of a word such as feedback in say the BODY field? I'm putting together a search engine I'd like to 'rank' the results based on the number of instances of each word found in a few fields. In this example, if they search for the words feedback and improve, the count would result in the row id, word searched and the number of instances of that word: (formatting doesn't matter, it's just to give you an idea) id 2: feedback count = 2 id 2: improve count = 1 I can play with the weightings later, just need to figure out the counting... Thanks guys, Gav -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] scrolling drop down menu
Cause I care, here's a suggestion... On any drop downs, add an onmouseout event in javascript. I put together a really simple example, I'm sure you could change it to be more usefull for multiple scoll boxes..? http://www.beertastic.co.uk/matthew.html Good luck...! Tris... matthew perry [EMAIL PROTECTED] 28/04/2004 05:18 To [EMAIL PROTECTED] cc Subject [PHP-DB] scrolling drop down menu Then I withdraw the question. Thank you for your time. John W. Holmes wrote: matthew perry wrote: When my users choose an option in the drop down menu, they sometimes accidentally change what they have chosen when the move the center scrolling button of their mouse. I was giving a presentation in front of my company's owner and did this myself. 5 or so index fingers instantly pointed at me saying See you do that to! Wh! Is there a way to stop a menu from scrolling with the center scrolling mouse button? Your questions have nothing to do with PHP. And absolutely nothing to do with DB... * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PostgreSQL lib and character case
Tumurbaatar S. wrote: I use pg_fetch_array() to get a record content. But it seems that to access elements of the returned associative array, I should use lowercase field names. Is there any way to use case-insensitive field names? Assuming you want all lower case, convert the form field entry using strtolower(). -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL - counting number of instances of a word in a field
The functions do not exist. However, FULLTEXT indexes will do much of this work for you. I encourage you to experiment with them. _ - Original Message - From: Gavin Amm [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 28, 2004 7:18 AM Subject: [PHP-DB] MySQL - counting number of instances of a word in a field Hi guys, I'm trying to find a MySQL function(s) that will allow me to count the number of words in a field. For eg: [table: pages] id - title - keywords - body 1 - Home - home, page - This is my home page. p Enjoy your stay. 2 - Feedback - feedback, form, contact - Please enter any feedback or comments in the form below.brYour feedback will be used to improve our service. How do I, for example, count the number of instances of a word such as feedback in say the BODY field? I'm putting together a search engine I'd like to 'rank' the results based on the number of instances of each word found in a few fields. In this example, if they search for the words feedback and improve, the count would result in the row id, word searched and the number of instances of that word: (formatting doesn't matter, it's just to give you an idea) id 2: feedback count = 2 id 2: improve count = 1 I can play with the weightings later, just need to figure out the counting... Thanks guys, Gav -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: PostgreSQL lib and character case
Hi - Tumurbaatar S. wrote: I use pg_fetch_array() to get a record content. But it seems that to access elements of the returned associative array, I should use lowercase field names. Is there any way to use case-insensitive field names? This is how Postgres works: it always returns arrays indexed with lowercase results. AFAIK there's no way to change this behavior from PHP. In general, it's important to know that different databases do this differently: - MySQL will return case matching the case of column names in the db -- or if you specify column names in your select clause (SELECT myColUmnName FROM ...) then the case of the array will match the case you use in your SELECT clause. - Oracle will return all UPPERCASE column names. - SQLite is configurable (defaults to mixed case) - PostgreSQL returns all lowercase ... etc. Of course, as someone mentioned, you can always col strtolower() when trying to access a column from postgres result set: $arr = pg_fetch_array($q); $value = $arr[ strtolower($mixedCaseColName) ]; It's best practice to use a database abstraction layer that provides column name case changing portability features -- like PEAR::DB or Creole. That way you can always use a single case (e.g. lowercase) for accessing columns and you won't have to rewrite all your code when you try to deploy your app on Oracle. Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Request for help on (My)SQL Statement
That would work except I'd change the last line to send email. The problem is the users are showing up to see any alert of new posts on the site. And when they do access PHPBB it will highlight those topics that have new posts anyway. Just got to get them there in the first place. Thanks, John -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 6:23 PM To: Bedard, John Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Request for help on (My)SQL Statement Well then there's no reason to copy the value. Just select from the BB table. Just: $l = mysql_fetch_array(mysql_query(SELECT max(post_time) as latest_post FROM nuke_phpbb_posts)); $s = mysql_fetch_array(mysql_query(SELECT max(script_run_date) as latest_script from script_data) if ($l['latest_post'] $s['latest_script']) { echo NEW POSTS!; } Or something of that nature. I've done this in the past by setting a cookie on the user's system when they log in so I know the last time they logged in, and then you can count how many posts have been made since they were there last.. that works pretty good. -Micah On Tuesday 27 April 2004 05:02 pm, [EMAIL PROTECTED] wrote: I'm running a notification script that does an action if the date of the last post is greater than the last time the script ran, which is the value stored in the other table. The action is to notify the members if there has been a new post in the past 24 hours. The client is trying to generate traffic - the members aren't using the site. She figures if they are reminded when there's activity on the site (without just forwarding all posts to the members - she doesn't want to turn it into a mailing list per se) they'll start using it. The design may be flawed but I don't know any other way to do it. As long as it serves the purpose and isn't a total abomination... :) Thanks, John -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 5:30 PM To: Bedard, John Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Request for help on (My)SQL Statement Not with MySQL that I know of, some local guru might have an idea.. Why are you storing the data in another table? Usually duplicating data in a database is a big signal of flawed design. If you're using it somewhere else, just select it from the originating table each time. Unless you're trying to do something else that I'm not taking into account. -Micah On Tuesday 27 April 2004 04:14 pm, [EMAIL PROTECTED] wrote: So you're saying there's no way to do it in this version with a single statement? Thanks, John -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 5:00 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Request for help on (My)SQL Statement That would be the way to do it except that MySQL doesn't support sub-selects until version 4.1, which is in alpha still. -Micah On Tuesday 27 April 2004 04:05 pm, Swan, Nicole wrote: Have you tried a nested query? I think your problem is that you're really using information from two different tables. Maybe: UPDATE ngc_polling SET lastrundate = (SELECT max(post_time) FROM nuke_phpbb_posts); --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT Helena, Montana (406)447-4310 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 4:52 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Request for help on (My)SQL Statement :::Novice Alert::: I've done simple select and insert statements, so I suppose this is the most complicated thing I've tried to do. but seems to me it should be pretty easy. But I'm a designer by background, not a developer or programmer. I've got everything else I'm trying to do working. I'm trying to take the largest value out of one table and update another table. The former is a PHPBB table and the latter is a table I created just to store the value of the date of the most recent post for use in something else. UPDATE ngc_polling SET lastrundate = max(nuke_phpbb_posts.post_time) It returns Invalid use of group function. I've been looking at the documentation on MySQL.com to no avail. I'm testing my query in phpMyAdmin before I try to implement it elsewhere. This is what I'm trying to do, any help besides RTFM (been doing that) would be greatly appreciated. Thanks! John John Bedard | Interaction Designer (SWEIII) | Northrop Grumman Mission Systems | Helena, Montana, U.S.A. | [EMAIL PROTECTED] | 406.443.8630 | Fax 406.443.8601 | -- 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:
Re: [PHP-DB] PostgreSQL lib and character case
El Wednesday 28 April 2004 03:21, Tumurbaatar S. escribió: I use pg_fetch_array() to get a record content. But it seems that to access elements of the returned associative array, I should use lowercase field names. Is there any way to use case-insensitive field names? No. PHP is case-sensitive, so you have to use lower case (unless you made the relation name case-sensitive when you created the PG table). -- 19:21:02 up 43 min, 6 users, load average: 1.46, 0.73, 0.58 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] First letter
Cornelia Boenigk wrote: Hi Matt You can teat a string like an Array $first = $string[0]; $second = $string[1] and so on. Actually this method is deprecated. The current best way is to use curly braces. $first = $string{0}; $second = $string{1}; Lang -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php