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] 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
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 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] / 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
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
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 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
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
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]
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]
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
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
> 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
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
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]
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]