[PHP-DB] most popular places
Hi there, I am trying to do a tricky task with PHP and MySQl 4.0.x There are 3 content tables where each of them contains a city_id and there is a 4th table which holds the city_id and the acording city_name Now I would like to find out the most used city names. For example: members picturesreports 3 4 3 2 9 8 9 8 8 So the most used city_ids are 8 3 9 To find this out would be a chelenging task, but to merge them with the city name is even more difficult I am working on this quite a while now and cant find a way. Maybe someone of you guys could give me a smart push. Thank you for any help, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Placing html entities into database?
Hi there, I am wondering how to store special characters like the german ö for example into a mysql db. Should the characters somehow be transforemd into html entities like ouml;? What are the benefits if it works without them as well? Thank you for any hint, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Placing html entities into database?
Viacheslav Kaloshin wrote: 2 possible solutions: 1 - use latest versions (4.1) of mysql and UTF-8 encoding. I am using 4.0.18 and the data look korrekt when retriefed out of the database. I did not enable any kind of encodeing. Is there a downfall in how I am doing it, which I just now do not notice? Regards, Merlin 2(bad) - before storing encode all string to base64 or something like this :-) On Tue, 08 Feb 2005 10:29:20 +0100, Merlin [EMAIL PROTECTED] wrote: Hi there, I am wondering how to store special characters like the german ö for example into a mysql db. Should the characters somehow be transforemd into html entities like ouml;? What are the benefits if it works without them as well? Thank you for any hint, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Placing html entities into database?
Only one: if you plan to use more, than one character set. Say, you want store in one column text with greek, german, italian and chineese characters ;-) The same column should hold english and german text. Acording to your reply this seems fine since there are no special characters in english?! Thanx, Merlin Viacheslav Kaloshin wrote: On Tue, 08 Feb 2005 10:54:33 +0100, Merlin [EMAIL PROTECTED] wrote: 1 - use latest versions (4.1) of mysql and UTF-8 encoding. I am using 4.0.18 and the data look korrekt when retriefed out of the database. I did not enable any kind of encodeing. Is there a downfall in how I am doing it, which I just now do not notice? Only one: if you plan to use more, than one character set. Say, you want store in one column text with greek, german, italian and chineese characters ;-) But if only one character set and no plans to extend - no problem to use as is without any reencoding Regards, Merlin 2(bad) - before storing encode all string to base64 or something like this :-) On Tue, 08 Feb 2005 10:29:20 +0100, Merlin [EMAIL PROTECTED] wrote: Hi there, I am wondering how to store special characters like the german ö for example into a mysql db. Should the characters somehow be transforemd into html entities like ouml;? What are the benefits if it works without them as well? Thank you for any hint, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Getting total results
Hello everybody, I am trying to split results comming from a mysql db with php into more html pages. Example: Results 1-10 out of 100 Therefor I limit the sql statement with: limit 1, 10 The sql statement is very complex. So far I have always used the same statement but without the limit and with a count inside to get the total number of results. Now with this statement it would be a huge decrease in performance. Is there a way to work with the limit statement, but to get the total number of results? As far as I know the LIMIT command only limits the visiblity, but mysql queries all the data. Any ideas which could help? Thank you in advance, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] selecting rows with no reference
Hi there, I know this is a question for the mysql mailing list, but maybe one of you guys also could help out with that. I do have to tables (users, messages). Now there unfortunatelly are some messages where the sender in the user table is missing. I am trying to find all messages which do not have the belonging user anymore. So I tried this: SELECT m.subject, m.status FROM user u, messages m WHERE m.sender_user_id != u.user_id But this returns far to many rows. Has anybody an idea how to do that? Thank you in advance, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Get next data set
Hi there, I am trying to figure out how to write a sql statement for mysql with php that returns the next possible data set. For example I do have the ID number of a current data set, now I would like to retrieve the next data set with the next higher ID number. Sometimes there are deleted Ids, thats why one cant just increment the data ID. Here is how I tried it: $stmt = SELECT g.place FROM $T33 g WHERE g.ID $prev[id] LIMIT 1 ; That unfortunatelly returns the very first row in the table. Any ideas how to solve that? Thanx for any help, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Get next data set
Merlin wrote: Hi there, I am trying to figure out how to write a sql statement for mysql with php that returns the next possible data set. For example I do have the ID number of a current data set, now I would like to retrieve the next data set with the next higher ID number. Sometimes there are deleted Ids, thats why one cant just increment the data ID. Here is how I tried it: $stmt = SELECT g.place FROM $T33 g WHERE g.ID $prev[id] LIMIT 1 ; That unfortunatelly returns the very first row in the table. Any ideas how to solve that? Thanx for any help, Merlin got it :-) ORDER BY ID desc LIMIT 1 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] database sends unclear messages
Hi there, I have a LAMP appplication which sends a report via email if a database error occures. This workes pretty fine. The message contains sql statement and error message from the db server. Lately there are lots of errors showing up every few days which look not like sql errors. Sometimes the message only contains error executing mysql statement: And thats it. In most cases it contains the sql statement as well (as it should!), but the error message from the sql server is missing. So this seems to be a problem of the server. I am running a 1 year old mysql version which might contain bugs. My guess is that there is a machine crawling my site which brings the server down. But I cant prove it. If such a message occures it always comes with tens or even hundred messages all sent in a few or even one minute. Has anybody a suggestion what to do, or a hint what could be happening here? Thanx a lot in advance, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] how to deal with this (hack) attack?
Hi there, I am running a medium size php/mysql website. In the last days I recieved more and more mysql errors due to url tempering. Now my website went down for at least 5 minutes, or more detailed the database server mysql 3.x went down. In the apache logs I found requests like connection:close comming from unspecified clients. The site is running again (I did not do anything). Can anybody give me a hint on how to prevent such atacks? What is that connection:close thing? Any help is greatly appreciated! Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] To many connections problem with LAMP
Hello, I am getting more and more to many connections errors on my web application buid with LAMP. After doing some research it looks like this is a problem because I am using pmysql_connect instead of mysql_connect. Istn't it better to use persistant connections? There are about 14000 PI the system has to serve each day. It looks like this is close to the limit? The server itself is running on about 10%cpu. Is there a way to increase the amount of connections possible? Can anybody give me a suggestion on how to solve this problem and where to go from here? Thanx in advance. Regards, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] delte stmt problem with joins
Hi there, I would like to delete a row inside a mysql table. To find out which rows are affected the system has to check another table. Somehow I am doing this statement wrong and I cant find out why. Does anybody see the error? Thanx for any help on that. DELETE FROM $table1 pi RIGHT JOIN $table2 AS p ON p.ID = pi.product_id WHERE p.owner_id = '15173' Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] delte stmt problem with joins
Hi John, I am using MySQL 3.x My guess is that it does not support a query like this. If so, I am sure that there is a workaround I just dont know about. The values $tablex are present. This is the error msg: Error: 1064 You have an error in your SQL syntax near 'pi INNER JOIN table2 AS p ON p.ID = pi.product_id WHERE p.ow' at line 3 Merlin John W. Holmes wrote: Merlin wrote: I would like to delete a row inside a mysql table. To find out which rows are affected the system has to check another table. Somehow I am doing this statement wrong and I cant find out why. Does anybody see the error? Thanx for any help on that. DELETE FROM $table1 pi RIGHT JOIN $table2 AS p ON p.ID = pi.product_id WHERE p.owner_id = '15173' 1. You're using MySQL 4, right? 2. Echo your query to the screen when you're having trouble. Ensure you really have a value for $table1, $table2, etc. 3. Try this format: DELETE $table1 FROM $table1 pi, $table2 p WHERE p.ID = pi.product_id AND p.owner_id = '15173' 4. If you're still having trouble, what does mysql_error() say after you execute the query? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] joining two sql statements
Hello, I am having a problem on how to get products out of 2 diffenent mysql tables to display them on one php site. Goal is to display 10 listings each site which can contain data out of both tables. Problem though is, that I want to fill the same variable with values from both tables. Right now it kind of works with 2 statements. I would like to join them into one statement, as there are more products comming up, which would complety confuse the whole thing. Has anybody an idea on how to join those 2 statements? Here they come: $stmt= SELECT p.*, u.first_name AS company_name, u.branch, UNIX_TIMESTAMP( p.start_date )AS start_date, c.*, p.currency, u.user_name, u.user_id, co.country_name_safe, co.country, pr.province, ci.city FROM $T5 u, $T110 p LEFT JOIN $T111 AS c ON p.ID = c.product_id AND c.branch = 03 LEFT JOIN $DB3.$geo_T1 AS co ON co.country_code = p.country LEFT JOIN $DB3.$geo_T2 AS pr ON pr.country_code = p.country AND pr.province_id = p.province LEFT JOIN $DB3.$geo_T3 AS ci ON ci.ID = p.city WHERE p.online = 1 AND p.owner_id = u.user_id /* AND u.premium_terminates = $today */ $where_stmt ORDER BY p.timestamp desc LIMIT $limit_bottom,$hits_per_site ; // apartments $stmt= SELECT p.*, u.first_name AS company_name, u.branch AS branch_x, c.*, p.currency, u.user_name, u.user_id, co.country_name_safe, co.country, pr.province, ci.city FROM $T5 u, $T112 p LEFT JOIN $T111 AS c ON p.ID = c.product_id AND c.branch = 06 LEFT JOIN $DB3.$geo_T1 AS co ON co.country_code = p.country LEFT JOIN $DB3.$geo_T2 AS pr ON pr.country_code = p.country AND pr.province_id = p.province LEFT JOIN $DB3.$geo_T3 AS ci ON ci.ID = p.city WHERE p.online = 1 AND p.owner_id = u.user_id /* AND u.premium_terminates = $today */ $where_stmt ORDER BY p.timestamp desc LIMIT $limit_bottom,$hits_per_site ; Thanx for any help on that, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] full text search in mysql
As of Version 4.0.1, MySQL can also perform boolean full-text searches
I am running Mysql 3.x . This sounds like a simple task to me. Is it not
implemented in 3.x?
It does not have to be OR, NOR or anything, just AND
merlin
Larry E . Ullman [EMAIL PROTECTED] schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
I do get results for islands in greece as well. How can I specify in my
statement, that both words have to be present?
From the MySQL manual:
As of Version 4.0.1, MySQL can also perform boolean full-text searches
using the IN BOOLEAN MODE modifier.
mysql SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL
-YourSQL' IN BOOLEAN MODE);
Use + to indicate required words.
Larry
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] full text search in mysql
Hi fellowes, I am working on a full text search in php and mysql. It workes pretty good, with one exception. If two words are entered, I do get results for each words in seperate as well. Examle: carribean islands I do get results for islands in greece as well. How can I specify in my statement, that both words have to be present? Thanx for any hint on that, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql problem importing data
Hi there, I have a data file with data seperated by semicolons. It is about 8MB big. Now I just want to import it into mysql. I tryed several things. First I took phpmyadmin, which resulted in a white page without error msg. Then I tryed it by hand with load data. This did not work as well. No I installed myodbc and tryed to export it from MS Access. This completly crashes my database server with this message: /usr/local/mysql/bin/safe_mysqld: line 1: 1067 Segmentation fault nice --5 nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/home/m ysqladm/data --user=mysql --pid-file=/home/mysqladm/var/mysqld.pid --skip-lo cking -u mysqladm /home/mysqladm/data/SARATOGA.err 21 030807 00:00:45 mysqld ended Has anybody an idea on how to import that data? Its nicely seperated by ; Thanx for any help, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sql or php?
Hi there, I am wondering if this could be done with mysql and without php, just with sql. I do have a table with text and another one with pictures. Now I would like to print out all titles and mark those tests containing a picture with a camera. I tryed some LEFT JOIN stuff, and grouped it by the picture table. Somehow it returns always yes. Has anybody a good idea? Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] To many connections error
Hello everybody, I am running a community portal with LAMP . So far everything was running fine. This night the site went down for a few hours. The error message from mysql was to many connections. Is there a way to increase the number of allowed connections, or do I have a hardware problem here (celleron 1GHZ 256 MB ram)? The traffic for the site is rising constantly and I fear to get in trouble. Thanx for your help. Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] incrementing values
Hi there, is there a function in mysql to increment a value inside a field? I am always doing this with php, just thought this might be really usefull?! Thanx, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sorting values
Hello everybody, I am pulling out several values out of a mysqldb. Those come with different tables and different criterias, thats why I cant sort them inside the sql statement. I need to make 3 of those statements. All the different results have an associated timestamp. How could I sort them after pulling out the values into variables (sort after time desc)? for esample: new user pictures time 10:00:00 new user articles time 12:00:00 new whatever time 11:00:00 thanx for any help, merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Truncated data retrieved from MSSQL Server
Hello, I'm trying to retrieve data from a table sitting on a MSSQL Server Database but the query result is always truncated over 255 characters... PHP 4.05 is running on a Linux box The column is a varchar 5000 Thanks for any help. Thomas. -- PHP Database 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]
