Re: [PHP] Database Question
Ashley M. Kirchner wrote: Someone's going to tell me to go buy a book, I just know it. I'll ask anyway: I'm starting to log weather data to a database and I'm trying to figure out what's the best way to create the tables. The reports are coming in every minute, of every hour, 24 hours a day. Eventually, I'd like to do some calculations on the statistics, displaying daily values (which can be broken down to hourly), but then also daily and monthly averages. To me, it doesn't make sense to dump everything into one big table, but I can't figure out what's the best way to break it down either. Keep in mind that the only data I have, is what comes in for that minute. The daily averages I have to calculate myself (later.) But I can't see one large table being very effective when it comes to calculating that stuff. So, how should I break the tables down? Create a new table every day (20061219_data, 20061220_data, etc.) and insert all the values in it? Or, break it down per values (temp_table, humidity_table, etc.) and insert daily data in them? 'Scuse me for re-opening an old thread, but apart from the other suggestions you could use table partitioning. If you're using mysql, see http://dev.mysql.com/doc/refman/5.1/en/partitioning.html Note it only came in at version 5.1. If you're using postgresql, see http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html Has been there for a while now. Using something else? Err, find their docs :) The links provided will explain things better than I can.. But if you're only getting 1 new record per minute, then I'd just chuck it all in one table - as Jochem said you're only going to get roughly 500k items per year - not that many. Indexing it might be a bit fiddly but that depends on the queries that you are running. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Question
# [EMAIL PROTECTED] / 2006-12-20 00:01:29 -0700: I'm starting to log weather data to a database and I'm trying to figure out what's the best way to create the tables. The reports are coming in every minute, of every hour, 24 hours a day. Eventually, I'd like to do some calculations on the statistics, displaying daily values (which can be broken down to hourly), but then also daily and monthly averages. To me, it doesn't make sense to dump everything into one big table, but I can't figure out what's the best way to break it down either. Why doesn't it make sense? (That is a honest question: breaking the table makes no sense to me, so I'm really curious about your conclusions.) Keep in mind that the only data I have, is what comes in for that minute. The daily averages I have to calculate myself (later.) But I can't see one large table being very effective when it comes to calculating that stuff. Use materialized views. I use triggers and/or rules (in PostgreSQL) in situations like the one you describe. So, how should I break the tables down? Create a new table every day (20061219_data, 20061220_data, etc.) and insert all the values in it? Or, break it down per values (temp_table, humidity_table, etc.) and insert daily data in them? Imagine you have the data broken into monthly tables. I want to see average values from 2005-11-13 till 2006-02-16, what will you do? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Question
So you have two single table votes.. make this a third. I'm guessing that each time you collect data, it's going to be one of each piece of data every time. Temperature, barametric pressure, humidity, wind direction, etc. You're not going to have 5 things all the time and like 3 other things only sometimes. You'd want to split the data into separate tables if there was some data that was infrequently. Take a contact database for instance. You might have name, address, phone, birthday. But then maybe your company has forms that some people fill out. You wouldn't want all the data for a form that they may or may not fill out in the same table. You'd end up with a lot of empty spaces for the forms that some people never needed to fill out. So you'd put that data into a separate table and link them via a contact ID or something. As for efficiency, it's probably more efficient to keep everything in one table and do your statistics by using SQL to filter down by date and use aggregate functions like SUM() and whatever your database's version of AVERAGE and other math functions are. This way, it's handled very quickly and efficiently inside the database engine before it returns any data (which is pretty much the slow part of database access.. especially when there's a lot of data to return. Data return and uber-complex joins.. but even they can be more efficient than returning too much data). You could section your tables off by date if you want. 1/2 million records a year and you could get away with having a few years in one table, or keep it year to year. You gotta ask yourself though, if you're going to want statistics that cross multiple years or whatever boundary you set for your splitting. If so, you're really better off having it all in one table than trying to UNION the tables later (although that's viable too I guess.. it just gives me the willies.. bad experiences.. hah) Just some additional thoughts on top of what's already been mentioned. Oh yeah... buy a book and take it to the insert database type mailing list :) Good luck! -TG = = = Original message = = = Someone's going to tell me to go buy a book, I just know it. I'll ask anyway: I'm starting to log weather data to a database and I'm trying to figure out what's the best way to create the tables. The reports are coming in every minute, of every hour, 24 hours a day. Eventually, I'd like to do some calculations on the statistics, displaying daily values (which can be broken down to hourly), but then also daily and monthly averages. To me, it doesn't make sense to dump everything into one big table, but I can't figure out what's the best way to break it down either. Keep in mind that the only data I have, is what comes in for that minute. The daily averages I have to calculate myself (later.) But I can't see one large table being very effective when it comes to calculating that stuff. So, how should I break the tables down? Create a new table every day (20061219_data, 20061220_data, etc.) and insert all the values in it? Or, break it down per values (temp_table, humidity_table, etc.) and insert daily data in them? -- A ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Question
[EMAIL PROTECTED] wrote: So you have two single table votes.. make this a third. I'm guessing that each time you collect data, it's going to be one of each piece of data every time. As for efficiency, it's probably more efficient to keep everything in one table and do your statistics by using SQL to filter down by date and use aggregate functions like SUM() and whatever your database's version of AVERAGE and other math functions are. This way, it's handled very quickly and efficiently inside the database engine another suggestion. create several another tables also. that maintains only the daily averages etc... or any other data that u may need to run on to get your stats. running Mysql average, sum command on that single database everytime will be rather cumbersome. it will be faster to run commands once and store in a database. ie. cache -- Thanking You Sumeet Shroff http://www.prateeksha.com Web Designers and PHP / Mysql Ecommerce Development, Mumbai India -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Database Question
Someone's going to tell me to go buy a book, I just know it. I'll ask anyway: I'm starting to log weather data to a database and I'm trying to figure out what's the best way to create the tables. The reports are coming in every minute, of every hour, 24 hours a day. Eventually, I'd like to do some calculations on the statistics, displaying daily values (which can be broken down to hourly), but then also daily and monthly averages. To me, it doesn't make sense to dump everything into one big table, but I can't figure out what's the best way to break it down either. Keep in mind that the only data I have, is what comes in for that minute. The daily averages I have to calculate myself (later.) But I can't see one large table being very effective when it comes to calculating that stuff. So, how should I break the tables down? Create a new table every day (20061219_data, 20061220_data, etc.) and insert all the values in it? Or, break it down per values (temp_table, humidity_table, etc.) and insert daily data in them? -- A -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Question
Ashley M. Kirchner wrote: Someone's going to tell me to go buy a book, I just know it. I'll go buy a book? (can't argue with you intuition now can I ;-) ask anyway: I'm starting to log weather data to a database and I'm trying to figure out what's the best way to create the tables. The reports are coming in every minute, of every hour, 24 hours a day. Eventually, I'd like to do some calculations on the statistics, displaying daily values (which can be broken down to hourly), but then also daily and monthly averages. To me, it doesn't make sense to dump everything into one big table, but I can't figure out what's the best way to break it down either. Keep in mind that the only data I have, is what comes in for that minute. The daily averages I have to calculate myself (later.) But I can't see one large table being very effective when it comes to calculating that stuff. So, how should I break the tables down? Create a new table every day (20061219_data, 20061220_data, etc.) and insert all the values in it? Or, break it down per values (temp_table, humidity_table, etc.) and insert daily data in them? I would keep everything in a single table (which will allow easier calculations - using a date range based where clause and whatever averaging SQL functions you desire) 60 * 24 * 365 = 525600 so in one year you have about half a million records in any given year, you might consider create a new table once a decade if you feel like being conservative with regard to potential table sizes. I guess that your using MySQL? I'm quite sure it can handle the amount of data you are going to have, in a single table. -- A -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Question
At 12/19/2006 11:01 PM, Ashley M. Kirchner wrote: I'm starting to log weather data to a database and I'm trying to figure out what's the best way to create the tables. The reports are coming in every minute, of every hour, 24 hours a day. Eventually, I'd like to do some calculations on the statistics, displaying daily values (which can be broken down to hourly), but then also daily and monthly averages. To me, it doesn't make sense to dump everything into one big table, but I can't figure out what's the best way to break it down either. Keep in mind that the only data I have, is what comes in for that minute. The daily averages I have to calculate myself (later.) But I can't see one large table being very effective when it comes to calculating that stuff. So, how should I break the tables down? Create a new table every day (20061219_data, 20061220_data, etc.) and insert all the values in it? Or, break it down per values (temp_table, humidity_table, etc.) and insert daily data in them? (This question doesn't pertain to PHP but to database techniques; you may get better and more friendly advice on a MySQL list.) I'm curious, why doesn't it make sense to you to keep all the data in one big table? MySQL is certainly robust enough to keep a whack of data together. Only when table size becomes problem, say with the practicality of backup or the speed of queries or the size of the hard drive, do you need to worry about breaking it down into smaller chunks. But every database has its limits and you're smart to decide up front how to split it up. A major factor in how you choose to store your data should be how it will be used. What kinds of queries will be most common? What time-spans do they cover? Do they usually interrogate just one parameter, e.g. either temperature or humidity but not both, or do they often query two or more parameters in search of correlations? Without knowing more, my first tendency would be to keep all the data in a single table. One table would actually occupy less disk space than splitting the data into parallel tables because some fields would need to be duplicated in every table (timestamp, record id, perhaps location, etc.). I might choose to split the data into one table per year for ease of backup and archiving. Another approach is to allow up to 5 or 10 years of data accumulate in a single table, then archive (copy out delete) the oldest year's data every year to keep the table size manageable. The daily averages I have to calculate myself (later.) But I can't see one large table being very effective when it comes to calculating that stuff. I believe it will be more efficient to calculate averages from a single table than from multiple tables. In both cases the database engine has to select the same fields to calculate the averages, but if the data is split into separate tables the engine will have to select from each table separately before compiling them. Regards, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Database question and PHP
I have a MS Acess Database that goes along with my web page. I want to transfer the data to a MySQL database since there are php functions for the MySQL database. I do not have a MySQL database running on the system that I am developing the web page. However there is a MySQL database on the webserver that the web site is to reside. Is the databases that reside on webservers easy to maintain? Is using that database a good option or is there another database option I should use? I am converting this website from Cold Fusion to PHP. In the Cold Fusion version there was a option in the CFSELECT Tag to add a query from a database to populate the options for the select tag. Is there a way to do this in php? I am not talking about the query string but the call to get the select tag to populate the options. Thanks, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database question and PHP
I have a MS Acess Database that goes along with my web page. I want to transfer the data to a MySQL database since there are php functions for the MySQL database. I do not have a MySQL database running on the system that I am developing the web page. However there is a MySQL database on the webserver that the web site is to reside. Is the databases that reside on webservers easy to maintain? Yes, very easy. Google for PHPMyAdmin and install it. Is using that database a good option or is there another database option I should use? Probably the best option as long as it performs adequately for your purposes. It is tried, and true, and well supported. I am converting this website from Cold Fusion to PHP. In the Cold Fusion version there was a option in the CFSELECT Tag to add a query from a database to populate the options for the select tag. Is there a way to do this in php? I can think of several ways. You can either roll your own, or use one of the pre-packaged tools that are out there. I always roll my own, so I can't recommend any other way, but I'm sure others will chime in with their favorites too. :-) JM -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database question and PHP
On Fri, February 3, 2006 2:15 pm, Paul Goepfert wrote: I have a MS Acess Database that goes along with my web page. I want to transfer the data to a MySQL database since there are php functions for the MySQL database. I do not have a MySQL database running on the system that I am developing the web page. However there is a MySQL database on the webserver that the web site is to reside. Is the databases that reside on webservers easy to maintain? Is using that database a good option or is there another database option I should use? You should probably install (download/upload) phpMyAdmin to your web-server. This will give you an interface to MySQL that's at least sort of like MS Access. I am converting this website from Cold Fusion to PHP. In the Cold Fusion version there was a option in the CFSELECT Tag to add a query from a database to populate the options for the select tag. Is there a way to do this in php? I am not talking about the query string but the call to get the select tag to populate the options. PHP is a lot more flexible than CF. So there's not a single, specific, custom tag for every feature you might want. But there is a TON of sample code, or PHP libraries/functions out there to make it fairly easy to do this. A quick search should turn up what you need: http://info.com/PHP+SELECT+OPTION+MySQL+populate -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Database question
Hi Group, Currently i am connecting the underlying database server from every php page. To reduce the connection overhead i am thinking to store the PEAR DB object into the registry (session) at the time of user login. Here i am connecting the Database only one time and rest of the time i am using the object stored in the memory. Even this is more helpful when the application is connecting more than one database..My application is getting feed from four different databases reside on two different servers. I don't know what ever i am thinking is correct or not.. Please guide me if i am wrong anywhere and if any one of you have better idea then please disucss. Thanks Hardik Doshi __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] database question
Hi all,
I'm trying to grab information from two DBs one being a mamber db and the
other a report DB
I want to print a list of users(pilots for a Virtual Airline) from the Pilot
db and then next to them the nº of reports and total hours they have so far
from the REPORT DB. The Pilot ID on this list must be a link so that when
clicked it will show the details of the reports.
I got this to but in a general for using a variable $login
so when the pilot/member logins it stores his ID in the $login and then I
use the SELECT * FROM table WHERE column = $login
to be more specific using this code:
?
require(require/config.php);
require(require/authentication.php);
$auth=authenticate($login, $password);
$link = mysql_connect(localhost, user, password)
or die(Could not connect);
mysql_select_db(databse) or die(Could not select database);
$query = SELECT flight_hhmm FROM pirep WHERE pilot_id='$login';
$result = mysql_query($query) or die(Query failed);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$hhmm=explode(':',$line['flight_hhmm']);
$totalm+=$hhmm[0]*60+$hhmm[1];
}
mysql_free_result($result);
?
then to print out the number of hours I use this:
?
echo $auth[login]. '. You have ';
echo floor($totalm / 60).':'.($totalm % 60). ' total hours.';
?
this show the total hours for $login which is the pilot id in the report DB.
now I have this code to produce TOTAL reports for the site:
$link = mysql_connect(localhost, user, password)
or die(Could not connect);
mysql_select_db(database) or die(Could not select database);
$query = SELECT * FROM pirep ;
$result = mysql_query($query) or die(Query failed);
$nb1 = mysql_numrows($result);
This gets me the nº of rows for all pilots the using echo $nb1 will print
the total reports.
Now I tried alot but no luck. I wanted to get a list like so:
MVC103 - 100 reports Filed - 130 Hours Total
MVC104 - 10 report filed - 50 hours total
etc,
having the ID (MVCxxx) being a link so when clicked it will show the
datails. I tried the count() statement but like I said I new to PHP.
If anyone can help PLS.. take a look at the site, mind you it is in
portuguese http://novo.cdmvirtual.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Database Question
Hi all,
I'm trying to grab information from two DBs one being a mamber db and the
other a report DB
I want to print a list of users(pilots for a Virtual Airline) from the Pilot
db and then next to them the nº of reports and total hours they have so far
from the REPORT DB. The Pilot ID on this list must be a link so that when
clicked it will show the details of the reports.
I got this to but in a general for using a variable $login
so when the pilot/member logins it stores his ID in the $login and then I
use the SELECT * FROM table WHERE column = $login
to be more specific using this code:
?
require(require/config.php);
require(require/authentication.php);
$auth=authenticate($login, $password);
$link = mysql_connect(localhost, user, password)
or die(Could not connect);
mysql_select_db(databse) or die(Could not select database);
$query = SELECT flight_hhmm FROM pirep WHERE pilot_id='$login';
$result = mysql_query($query) or die(Query failed);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$hhmm=explode(':',$line['flight_hhmm']);
$totalm+=$hhmm[0]*60+$hhmm[1];
}
mysql_free_result($result);
?
then to print out the number of hours I use this:
?
echo $auth[login]. '. You have ';
echo floor($totalm / 60).':'.($totalm % 60). ' total hours.';
?
this show the total hours for $login which is the pilot id in the report DB.
now I have this code to produce TOTAL reports for the site:
$link = mysql_connect(localhost, user, password)
or die(Could not connect);
mysql_select_db(database) or die(Could not select database);
$query = SELECT * FROM pirep ;
$result = mysql_query($query) or die(Query failed);
$nb1 = mysql_numrows($result);
This gets me the nº of rows for all pilots the using echo $nb1 will print
the total reports.
Now I tried alot but no luck. I wanted to get a list like so:
MVC103 - 100 reports Filed - 130 Hours Total
MVC104 - 10 report filed - 50 hours total
etc,
having the ID (MVCxxx) being a link so when clicked it will show the
datails. I tried the count() statement but like I said I new to PHP.
If anyone can help PLS.. take a look at the site, mind you it is in
portuguese http://novo.cdmvirtual.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database question
yeah good call on a type it should of been \s not \S -- Jon Farmer Systems Programmer, Entanet www.enta.net Tel 01952 428969 Mob 07763 620378 PGP Key available, send email with subject: Send PGP Key - Original Message - From: Jim Musil [EMAIL PROTECTED] To: py [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 10:56 PM Subject: Re: [PHP] database question \S refers to a non whitespace character. In the following: select whatever from articles where textlines regexp ^\Sbingo\S$ what does \S means? py - Original Message - From: Jon Farmer [EMAIL PROTECTED] To: Warren Vail [EMAIL PROTECTED]; Michael Hall [EMAIL PROTECTED]; PHP List [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 9:54 AM Subject: Re: [PHP] database question Actually the sql statement you want is select whatever from articles where textlines regexp ^\Sbingo\S$ regards Jon -- Jon Farmer Systems Programmer, Entanet www.enta.net Tel 01952 428969 Mob 07763 620378 PGP Key available, send email with subject: Send PGP Key - Original Message - From: Warren Vail [EMAIL PROTECTED] To: Michael Hall [EMAIL PROTECTED]; PHP List [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 2:57 PM Subject: RE: [PHP] database question Try; SELECT whatever FROM articles WHERE textlines LIKE %searchword% Two warnings; 1) This will force a table scan (the contents of each row in the entire table because there can be no index to support faster searching of contents that float in the column) which will be very slow on a large database (even a medium size one). 2) This will also find words that exist inside other words. (ie the word ward exists inside toward) If you try to solve this by imbedding blanks between the wildcard (%) and the text, you will probably not be able to find the word at the end of a line, or just prior to a comma or period. I also believe there may be a way to make the search case insensitive, look for something like a WHERE tolower(textlines) LIKE ... to force the column values to be all lower case and make sure your search values are all lower case as well. Good luck, Warren Vail -Original Message- From: Michael Hall [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 29, 2001 2:21 PM To: PHP List Subject: [PHP] database question How can I search a MySQL database field that contains sentences (VARCHAR datatype) or entire texts (TEXT datatype) for single words? Let's say I want to search 100 articles stored in a database field as TEXT for the word bingo, is there any SQL or PHP way of doing that? Mick -- Michael Hall [EMAIL PROTECTED] [EMAIL PROTECTED] http://openlearningcommunity.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- Jim Musil - Multimedia Programmer Nettmedia - 212-629-0004 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] database question
How can I search a MySQL database field that contains sentences (VARCHAR datatype) or entire texts (TEXT datatype) for single words? Let's say I want to search 100 articles stored in a database field as TEXT for the word bingo, is there any SQL or PHP way of doing that? Mick -- Michael Hall [EMAIL PROTECTED] [EMAIL PROTECTED] http://openlearningcommunity.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] database question
You could try something like this: SELECT * FROM database WHERE text LIKE '%bingo%' Rick How can I search a MySQL database field that contains sentences (VARCHAR datatype) or entire texts (TEXT datatype) for single words? Let's say I want to search 100 articles stored in a database field as TEXT for the word bingo, is there any SQL or PHP way of doing that? Mick -- Michael Hall [EMAIL PROTECTED] [EMAIL PROTECTED] http://openlearningcommunity.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] database question
Actually the sql statement you want is select whatever from articles where textlines regexp ^\Sbingo\S$ regards Jon -- Jon Farmer Systems Programmer, Entanet www.enta.net Tel 01952 428969 Mob 07763 620378 PGP Key available, send email with subject: Send PGP Key - Original Message - From: Warren Vail [EMAIL PROTECTED] To: Michael Hall [EMAIL PROTECTED]; PHP List [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 2:57 PM Subject: RE: [PHP] database question Try; SELECT whatever FROM articles WHERE textlines LIKE %searchword% Two warnings; 1) This will force a table scan (the contents of each row in the entire table because there can be no index to support faster searching of contents that float in the column) which will be very slow on a large database (even a medium size one). 2) This will also find words that exist inside other words. (ie the word ward exists inside toward) If you try to solve this by imbedding blanks between the wildcard (%) and the text, you will probably not be able to find the word at the end of a line, or just prior to a comma or period. I also believe there may be a way to make the search case insensitive, look for something like a WHERE tolower(textlines) LIKE ... to force the column values to be all lower case and make sure your search values are all lower case as well. Good luck, Warren Vail -Original Message- From: Michael Hall [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 29, 2001 2:21 PM To: PHP List Subject: [PHP] database question How can I search a MySQL database field that contains sentences (VARCHAR datatype) or entire texts (TEXT datatype) for single words? Let's say I want to search 100 articles stored in a database field as TEXT for the word bingo, is there any SQL or PHP way of doing that? Mick -- Michael Hall [EMAIL PROTECTED] [EMAIL PROTECTED] http://openlearningcommunity.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] database question
select whatever from articles where textlines regexp ^\Sbingo\S$ Again, that would fail if the word 'bingo' is and the end of the sentence. Allthou i realized my own example doesnt work either in that case. Best way would be remove all characters like ,.!? and and split the string into words. But another question, do regular expressions really work in string comparisons ? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] database question
In the following: select whatever from articles where textlines regexp ^\Sbingo\S$ what does \S means? py - Original Message - From: Jon Farmer [EMAIL PROTECTED] To: Warren Vail [EMAIL PROTECTED]; Michael Hall [EMAIL PROTECTED]; PHP List [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 9:54 AM Subject: Re: [PHP] database question Actually the sql statement you want is select whatever from articles where textlines regexp ^\Sbingo\S$ regards Jon -- Jon Farmer Systems Programmer, Entanet www.enta.net Tel 01952 428969 Mob 07763 620378 PGP Key available, send email with subject: Send PGP Key - Original Message - From: Warren Vail [EMAIL PROTECTED] To: Michael Hall [EMAIL PROTECTED]; PHP List [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 2:57 PM Subject: RE: [PHP] database question Try; SELECT whatever FROM articles WHERE textlines LIKE %searchword% Two warnings; 1) This will force a table scan (the contents of each row in the entire table because there can be no index to support faster searching of contents that float in the column) which will be very slow on a large database (even a medium size one). 2) This will also find words that exist inside other words. (ie the word ward exists inside toward) If you try to solve this by imbedding blanks between the wildcard (%) and the text, you will probably not be able to find the word at the end of a line, or just prior to a comma or period. I also believe there may be a way to make the search case insensitive, look for something like a WHERE tolower(textlines) LIKE ... to force the column values to be all lower case and make sure your search values are all lower case as well. Good luck, Warren Vail -Original Message- From: Michael Hall [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 29, 2001 2:21 PM To: PHP List Subject: [PHP] database question How can I search a MySQL database field that contains sentences (VARCHAR datatype) or entire texts (TEXT datatype) for single words? Let's say I want to search 100 articles stored in a database field as TEXT for the word bingo, is there any SQL or PHP way of doing that? Mick -- Michael Hall [EMAIL PROTECTED] [EMAIL PROTECTED] http://openlearningcommunity.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] database question
\S refers to a non whitespace character. In the following: select whatever from articles where textlines regexp ^\Sbingo\S$ what does \S means? py - Original Message - From: Jon Farmer [EMAIL PROTECTED] To: Warren Vail [EMAIL PROTECTED]; Michael Hall [EMAIL PROTECTED]; PHP List [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 9:54 AM Subject: Re: [PHP] database question Actually the sql statement you want is select whatever from articles where textlines regexp ^\Sbingo\S$ regards Jon -- Jon Farmer Systems Programmer, Entanet www.enta.net Tel 01952 428969 Mob 07763 620378 PGP Key available, send email with subject: Send PGP Key - Original Message - From: Warren Vail [EMAIL PROTECTED] To: Michael Hall [EMAIL PROTECTED]; PHP List [EMAIL PROTECTED] Sent: Thursday, November 29, 2001 2:57 PM Subject: RE: [PHP] database question Try; SELECT whatever FROM articles WHERE textlines LIKE %searchword% Two warnings; 1) This will force a table scan (the contents of each row in the entire table because there can be no index to support faster searching of contents that float in the column) which will be very slow on a large database (even a medium size one). 2) This will also find words that exist inside other words. (ie the word ward exists inside toward) If you try to solve this by imbedding blanks between the wildcard (%) and the text, you will probably not be able to find the word at the end of a line, or just prior to a comma or period. I also believe there may be a way to make the search case insensitive, look for something like a WHERE tolower(textlines) LIKE ... to force the column values to be all lower case and make sure your search values are all lower case as well. Good luck, Warren Vail -Original Message- From: Michael Hall [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 29, 2001 2:21 PM To: PHP List Subject: [PHP] database question How can I search a MySQL database field that contains sentences (VARCHAR datatype) or entire texts (TEXT datatype) for single words? Let's say I want to search 100 articles stored in a database field as TEXT for the word bingo, is there any SQL or PHP way of doing that? Mick -- Michael Hall [EMAIL PROTECTED] [EMAIL PROTECTED] http://openlearningcommunity.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- Jim Musil - Multimedia Programmer Nettmedia - 212-629-0004 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] database question
what does \S means? http://www.php.net/manual/en/pcre.pattern.syntax.php This page gives a good description of many of the regex metacharacters and their meanings. Sincerely, Craig Vincent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
