Re: MySQL client relicense?
On Thu, 11 Dec 2003 12:26:48 +0100 [EMAIL PROTECTED] wrote: Is this true? Has the license for the MySQL client libraries changed from LGPL to GPL? yes, it's true. but no, you can use MySQL 4.x with PHP. PHP at this moment _*doesn't distribute bundled libmysql*_ (PHP uses it's own license, not GPL), but you still can build PHP with external libmysql. this fact can affect only Win32 users, because they don't build PHP themselves (in most cases). --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.1 Internationalization (ENUM)
On Wed, 10 Dec 2003 15:25:57 +0200
Juri Shimon [EMAIL PROTECTED] wrote:
Hi!
create table t(a enum ('','__','__'));
show create table t;
CREATE TABLE `t` (
`a` enum('','???','???') default NULL
) TYPE=MyISAM DEFAULT CHARSET=cp1251
firstly, upgrade you mysql to current stable version (4.0.16).
and imho you need cp1251_ukrainian_ci to use ukrainian characters properly.
---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access my mysql db remotely...
On Wed, 22 Oct 2003 14:00:13 +0800 Hiu Yen Onn [EMAIL PROTECTED] wrote: i want to create a user who access from internet remotely to my db (outside firewall)? what is the solution then? how can i define the user accoutn? [EMAIL PROTECTED] if your firewall allows other users to connect to your host using port 3306 (which is default for MySQL), then you should put there user's hostname. smthng like this: [EMAIL PROTECTED] --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMITS
On Thu, 16 Oct 2003 11:49:29 -0400 Cummings, Shawn (GNAPs) [EMAIL PROTECTED] wrote: when I do a query is there a way to IGNORE the first X number of returned records??? For instance I want to see 15 records after the first 50. yes, use LIMIT clause: SELECT * FROM tablename LIMIT 50,15; but first read about it here: http://www.mysql.com/doc/en/SELECT.html --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PASSWORD() function problem
On Wed, 15 Oct 2003 17:26:23 +0800
Manisha Sathe [EMAIL PROTECTED] wrote:
Hi,
I inserted one record thr PHPMyAdmin - mem_pass field of member table set to xyz
using function 'PASSWORD'
Then trying to select the same - select * from member where mem_pass =
PASSWORD('xyz') - then it is not getting selected
I do not know why I am not getting the result. please help me. what's going wrong ?
mem_pass field should be at least 16 characters lengthwise to store result of
PASSWORD()
check it, plz.
---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Missing library named 'z' ??
On Mon, 13 Oct 2003 21:01:37 -0700 Matt Young [EMAIL PROTECTED] wrote: The manual says: For example, if the library is installed in `/usr/local/mysql/lib', use -L/usr/local/mysql/lib -lmysqlclient -lz on the link command. But I found no z library? you need libz. run these commands to locate it: locate libz.so or ldconfig -p | grep libz.so --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting rows which table1.row=table2.row
On Tue, 14 Oct 2003 11:55:18 +0200 (CEST) [EMAIL PROTECTED] wrote: i have mysql version 3.22 perhaps you mean 3.23 ? but no matter, it's time to upgrade definitely. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: 'XXX.MYI' (errno: 2)
On Thu, 2 Oct 2003 07:33:55 -0700 (PDT) shahanawaz lakhani [EMAIL PROTECTED] wrote: Our MySql version : mysql Ver 11.12 Distrib 3.23.32, for pc-linux-gnu (i686) upgrade first. 3.23.32 is _very_ old version. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select statement to get the difference
On Wed, 1 Oct 2003 10:56:59 +0200 Stefan Berger [EMAIL PROTECTED] wrote: I have 2 tables and i want to find difference (the id exists in one table but in the other not) between them. With SELECT attachment_id from tbl_attachment, tbl_msg2atta where attachment_id = atta_id i receive all id wich are equals in both tables. I need to know which id is not present in the other table. SELECT attachment_id FROM tbl1 LEFT JOIN tbl2 ON tbl1.field = tbl2.field WHERE tbl2.field IS NULL; P.S. read about SELECT LEFT JOIN in manual. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'IF NOT EXISTS' ignored?
On Mon, 29 Sep 2003 12:30:28 +0200 Director General: NEFACOMP [EMAIL PROTECTED] wrote: It seems Victoria didn't understand the real problem: I think that query should be stopped as soon as the table exists. But if it doesn't exist, the query should create it and insert some records. The problem is: WHY is MySQL trying to insert records while the table exists? It should only insert records after creating the table (and table will be created only when t doesn't exist already). I don't think you're right. The main aim of this query is to INSERT data into table. And before this it checks if the table already exists and creates it if not. So, there is no problem imho. So, in that query, will the IF condition apply for table creation only? Yes. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'IF NOT EXISTS' ignored?
On Mon, 29 Sep 2003 15:16:57 +0200 Director General: NEFACOMP [EMAIL PROTECTED] wrote: Now, do you think I was right in my rephrasing? To me, I think that is a bug and should be reported to MySQL. When the IF condition is false, it should break the rest of the statement. What is your view on this? For me this is not a bug, just poorly documented feature. So, Hassan can't do it using this statement, he needs to check if table exists and then INSERT, cause the statement he's trying to use is not intended for conditional INSERT, but for conditional CREATE. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'IF NOT EXISTS' ignored?
On Mon, 29 Sep 2003 16:02:23 +0200 Director General: NEFACOMP [EMAIL PROTECTED] wrote: For me, I think he may use two statements: 1. Create the table if it does not exist. 2. Insert data if that will not create duplicates. Yep, IMHO this is the only solution. Another alternative is to first DROP that table and recreate it. But I think the above 2 steps procedure will work. I suppose in this case Hassan should use DROP IF EXISTS and SELECT .. INSERT. Unfortunately, I don't see a way to send these queries at once to the server for execution. I don't think, that this queries should be sent at once (and there is no way to do it). They can be successfully executed one by one. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: phpMyAdmin request problem
On Thu, 25 Sep 2003 10:11:27 +0200 (CEST) Artem Batoussov [EMAIL PROTECTED] wrote: The first part has been generated by phpMyAdmin and I've just added CHARACTER SET koi8r COLLATION koi8r_general_ci like it's done in the example from this doc : http://www.mysql.com/documentation/mysql/bychapter/manual_Charset.html#Charset-cyrillic-sets . But I have a syntaxe error in this part of code ! Which is it ? please, tell us what error message do you get and your version of MySQL server. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables
On Thu, 25 Sep 2003 02:26:28 -0600 [EMAIL PROTECTED] wrote: 1) What happens if two (or more) users create a temporary table with the same name at the same time? 2) If i don't drop the tables, when are the tables droped by the server? 3) what are the memory and or performance issues of temporary tables? 4) Can i use session based temporary tables? http://www.mysql.com/doc/en/CREATE_TABLE.html The temporary table is visible only to the current connection, and will be deleted automatically when the connection is closed. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted.) From MySQL 4.0.2 on, you must have the CREATE TEMPORARY TABLES privilege to be able to create temporary tables. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql compile error.
On Thu, 25 Sep 2003 15:28:15 +0200 Richard Pijnenburg [EMAIL PROTECTED] wrote: i'm trying to compile mysql-4.0.15a on my server and i'm getting the folowing error: Richard, please, resend your letter one more time, we have real enjoy reading it again and again... =) --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashes after optimize
On Thu, 25 Sep 2003 09:23:13 -0500 Ben Ricker [EMAIL PROTECTED] wrote: Here is the output we got this morning: test.MESSAGES optimizeerror 13 when fixing table test.MESSAGES optimizestatus Operation failed # perror 13 Error code 13: Permission denied check if it's allright with rights on this table ? --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: Moving a database
On Wed, 24 Sep 2003 09:14:33 EDT [EMAIL PROTECTED] wrote: Has anyone ever had to move a database from one machine to another? I tried to just copy all the files from /var/lib/mysql/db1 to another machine into /var/lib/mysql/db2. Although I can see the tables using SHOW TABLES, I get an error 13 trying to access or repair them. Is it possible to move a database this way? #perror 13 Error code 13: Permission denied yes, it's possible. but you should not forget about proper rights on these files. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import Paradox data to MySQL?
On Wed, 24 Sep 2003 18:19:30 +0400 [EMAIL PROTECTED] wrote: Is there a painless way to import Paradox data and database structure to MySQL? try to follow this advice: http://dbforums.com/arch/51/2003/5/766832 --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL connection with PHP
On Wed, 24 Sep 2003 18:44:06 +0100 (BST) John Cole [EMAIL PROTECTED] wrote: Fatal error: Call to undefined function: mysql_connect() in /var/... on line 2 This error means, that you PHP was built without MySQL support. You need to add MySQL support or to ask your admin to do it. PHP version: 4.0.4 MySQL version: 3.23.36-1 and it's time to update your PHP MySQL, definitely. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT data into multiple tables
On Wed, 24 Sep 2003 14:23:04 -0500 Dan J. Rychlik [EMAIL PROTECTED] wrote: Hello, I have a question about INSERTing data into 2 different tables with one statement. Can you do this? INSERT INTO table1 (name,address,phone) VALUES ( ' USER ', ' USERADDY ',' USERPHONE') AND table2 (name) VALUES( ' USER '); No. Use 2 queries for that: INSERT INTO table1 (name,address,phone) VALUES ( ' USER ', ' USERADDY ',' USERPHONE'); INSERT INTO table2 (name) VALUES( ' USER '); --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Handling Arrays
On Tue, 2 Sep 2003 21:40:35 -0500 John Macon [EMAIL PROTECTED] wrote: I have a quick question about arrays. I know that this is probably pretty easy for most of you out there, so here it goes. If you remove $array[2], would $array[3] then automatically become the new $array[2]? Thanks in advance for your help. 1) this is MySQL-general list, not php-general. 2) of course it wouldn't. just try and you'll see. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.14 stops responding to PHP 4.3.2
On Wed, 3 Sep 2003 11:42:35 -0400 Parker Morse [EMAIL PROTECTED] wrote: Periodically our PHP sites will fail to connect using mysql_pconnect(). We currently have three sites on the colo box (two more are waiting on shared hosting until I can solve this problem) and they all fail at once. mysqld is still running, though. If I shut down mysqld and restart, they are able to connect again. This makes me think the problem is with how I have MySQL configured. However, nothing useful is being logged anywhere in the /var/log heirarchy, so I can't figure out what's going wrong. Please, read http://www.php.net/manual/en/features.persistent-connections.php I recommend you not to use *_pconnect. There are some big problems with persistent connections and these problems are 'by design'. Use SQLrelay if you need real connection pooling. In your case MySQL probably says 'too many connections' and you can catch this error message if you'll turn on error_log in php.ini. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: passwords
On Wed, 3 Sep 2003 00:14:42 -0300
Fernando (BOL) [EMAIL PROTECTED] wrote:
Hi,
Can anyone tell me which function have more security/encryption?
PASSWORD( ) ?
ENCODE( ) ?
MD5( ) ?
Please, read http://www.mysql.com/doc/en/Miscellaneous_functions.html
PASSWORD MD5 are realizations of hashing algorithms (you can't get original data
from the hash), while ENCODE encrypts string, using some password and can be decrypted
using the same password string.
There is no need to use ENCODE in case of storing passwords, instead you can use MD5
and queries like this:
SELECT * FROM users WHERE
MD5('password_entered_by_user')=hashed_password_field_from_database;
---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.14 stops responding to PHP 4.3.2
On Wed, 3 Sep 2003 18:23:00 -0400 Dan Greene [EMAIL PROTECTED] wrote: not knowing anything about PHP (java geek, myself), I'm guessing that your connections are timing out, and php is not configured to try to reconnect PHP always reconnects, but *_pconnect seeks for the already existing connection first. with the popularity of PHP, I have to think it has built-in connection pooling facilities that may help resolve these issues... no, it's not Java, there is no Application Server. there is only Apache and Apache's module - PHP. so, you have to use some third-party application if you need connection pooling. this can be for example SQLrelay (sqlrelay.sf.net) or SRM (www.vl-srm.net - this one is rather PHP-specific). --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Got a warning!
On Tue, 2 Sep 2003 12:12:42 +0200 Magnus D. Klein [EMAIL PROTECTED] wrote: I tried connect a mysql-db using php4 under linux. Then I get a warning by the parser from the function mysql_free_result($result); Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /srv/www/htdocs In all reference books I found this syntax. Has anyone an idea what wrong? you should use mysql_free_result(); only with resources you get from mysql_query(); --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Abt Mysqldump
On Tue, 2 Sep 2003 17:02:37 +0530 (IST) Uma Shankari T. [EMAIL PROTECTED] wrote: but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. what exactly does it say ? --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
On Tue, 2 Sep 2003 08:45:42 -0400 Albert [EMAIL PROTECTED] wrote: When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? Thanks you should enter this in mysql console, not in shell. run .../mysql/bin/mysql binary (--help option will show you all possible keys) and then enter MySQL commands. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TABLE - OR
On Mon, 01 Sep 2003 17:13:20 +0900 Nishant [EMAIL PROTECTED] wrote: I dont know why my brain stopped working :) I have two tables T1 and T2, both of which have a column : SerialNo Now I want to select those rows from T1 whose SerialNo is not present in T2. This dont work: select T1.* from table1 as T1 straight_join table2 as T2 where T1.serialNo != T2.serialNo order by T1.serialNo Looking for some help. Look here: http://www.mysql.com/doc/en/JOIN.html *** If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table: mysql SELECT table1.* FROM table1 -LEFT JOIN table2 ON table1.id=table2.id -WHERE table2.id IS NULL; *** --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot Connect to Server
On Mon, 1 Sep 2003 11:34:31 +0100 Phil Ewington - 43 Plc [EMAIL PROTECTED] wrote: The error message is: Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/tmp/mysql.sock' (111) in /home/sites/.. on line 3 Unable to select database is MySQL socket really placed in /var/tmp? if not, than you need to specify path to it in mysql.default_socket directive in php.ini or your MySQL server isn't running on localhost at all? --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot Connect to Server
On Mon, 1 Sep 2003 11:51:07 +0100 Phil Ewington - 43 Plc [EMAIL PROTECTED] wrote: Could not read response from last sender :o( from [EMAIL PROTECTED] ? it's Yet Another Spammer(or Autoresponder) on this list. YAS(A) =) dunno why list administration haven't blocked him yet. These robots are autoresponding on each message to the list with senseless messages: [EMAIL PROTECTED] [EMAIL PROTECTED] --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
On Mon, 1 Sep 2003 17:19:52 +0200 Joris Beckers [EMAIL PROTECTED] wrote: I've got a user, admFrederic He got the grants shown below. Those are correct and I configured them that way. don't forget about FLUSH PRIVILEGES; --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NFS and MySQL
On Mon, 1 Sep 2003 15:40:09 +0100 Simon Green [EMAIL PROTECTED] wrote: Hi I know that there are two problems when using NFS and MySQL. One is speed. My question is that with a good filer like a Netapps with Gig Ethernet would speed stop being a problem? Two locking. Why is this a problem? Do locks get missed when righting from the system to the filer? I suppose, you're talking about this: http://www.mysql.com/doc/en/Multiple_servers.html It seems, that there is no problems with MySQL on NFS, if you're not using more than one server in the same data directory. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: listing all people who have the same firstname and lastname
On Fri, 29 Aug 2003 12:42:03 -0700 Grant Cooper [EMAIL PROTECTED] wrote: I'm trying to get a query to work by listing all the people in a row with the same last name and first name. key, fname, lname 1 ,John, Smith 4, John, Smith 5, Cody,Edwards 2, Cody, Edwards SELECT fname,lname, COUNT(*) as cnt FROM table GROUP BY CONCAT(fname,lname); --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying a Linux machine
On Fri, 29 Aug 2003 09:15:48 +1000 Michael Piko [EMAIL PROTECTED] wrote: I should have mentioned the error: [kryten2] ERROR 2013: Lost connection to MySQL server during query check your MySQLd log to see what happends. usually you can find it in /var/log/ --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question: how to set field a lookup field from another table?
On Fri, 29 Aug 2003 01:02:57 -0400 (EDT) Jordan Morgan [EMAIL PROTECTED] wrote: Hi, I'm very new to MySQL. I normally use MS Access but my project needs MySQL. I'm able to set a field(categoryID) in table A(product) a lookup field to another table(category) in Access but I can't find anywhere that teaches me how to do that in MySQL. I'm managing MySQL through a web interface provided by my hosting company. I can use SQL statements as well as clicking a few buttons. Any advice is highly appreciated. Thanks a bunch! You mean you need to create a FOREIGN KEY? Read this: In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See section 7.5.5.2 Foreign Key Constraints. For other table types, MySQL Server only parses the FOREIGN KEY syntax in CREATE TABLE commands, but does not use/store this info. http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
On Thu, 28 Aug 2003 08:54:54 -0400
Mark Richards [EMAIL PROTECTED] wrote:
I am still quite new to MySQL and have a basic question. I am using PHP, so
forgive me if this is more a PHP issue.
I want to perform an update to a specific record based on a condition. In
the outer loop, I have Query1, which returns set Result1. Inside this loop,
I run an UPDATE query which returns Result2.
// executed first query...
while ($row = mysql_fetch_assoc($result1))
{
// get the record ID for the row we are on.
$recid = $row[id];
// construct a new query
$q2 =UPDATE `table` SET `review` = 1 where id =
'.$recid.';;
$result2 = mysql_query($q2)
}
You don't need execute UPDATE's in the loop in this case.
Try smthing like that:
?
.
$ids = Array();
while ($row = mysql_fetch_assoc($result1))
{
$ids[] = $row[id];
}
$ids_string = implode(',',$ids);
//of course, we need to check if $id_string is not empty
$q2 =UPDATE table SET review = 1 WHERE id IN (.$ids_string.);
.
?
--
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
On Thu, 28 Aug 2003 16:23:35 +0200 Fred van Engen [EMAIL PROTECTED] wrote: You need to make a separate connection to MySQL for the outer query to prevent the inner query from messing up the outer query's result set. See the PHP manual for obtaining the connection id's from mysql_connect and using them with other mysql functions. ouch! it's a kinda bad advice. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
On Thu, 28 Aug 2003 16:37:41 +0200
Fred van Engen [EMAIL PROTECTED] wrote:
Please elaborate.
I've already answered:
On Thu, 28 Aug 2003 17:07:19 +0400
Antony Dovgal [EMAIL PROTECTED] wrote:
You don't need execute UPDATE's in the loop in this case.
Try smthing like that:
?
.
$ids = Array();
while ($row = mysql_fetch_assoc($result1))
{
$ids[] = $row[id];
}
$ids_string = implode(',',$ids);
//of course, we need to check if $id_string is not empty
$q2 =UPDATE table SET review = 1 WHERE id IN (.$ids_string.);
.
?
there is no need to use nested loop at all.
just collect id's and execute 1(one) query, which will update all records.
and you don't need to make another one connection, of course.
---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
On Tue, 26 Aug 2003 17:00:16 -0400 Nicola Hartland [EMAIL PROTECTED] wrote: When I do the tests on frontpage, it doesn't seem to do anything. what test are you talking about? On the Mysqladmin it tells me that my odbc driver 3,.51 not found is that a problem and how do I get the driver? you don't need ODBC driver to work in PHP with MySQL. PHP has native builtin extension for MySQL. just use mysql_*(); functions and be happy =) --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 09:05:17 -0300 bernardaum [EMAIL PROTECTED] wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? yes, use TRUNCATE TABLE table; to restart sequence. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 13:27:25 +0100 Simon [EMAIL PROTECTED] wrote: On Wednesday 27 August 2003 1:05 pm, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? When I need to do this, I just drop and re-create the table. http://www.mysql.com/doc/en/TRUNCATE.html Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. (c) I suppose TRUNCATE is more efficient way to get the same result =) --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 09:38:16 -0400 Paul DuBois [EMAIL PROTECTED] wrote: At 9:05 -0300 8/27/03, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? Why bother? MySQL doesn't care if there are gaps in the sequence. And if you're using the ID to relate records in the table to records in another table, you'll destroy the correspondence. imho, he's talking about resetting auto_increment to 0, not about gaps in ID: When I -- delete all -- the records ... --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
On Wed, 27 Aug 2003 09:33:38 -0400 Nicola Hartland [EMAIL PROTECTED] wrote: I am reading the Book PHP and MYsql for dummies and it talks of a test you can do to see if PHP and SQL are talking with each other. hmm.. you will have to excuse my ignorance what Is a mysql_*;function as I said I am completely new to this and don't know how to set this up. visit http://php.net/mysql - you will see. I'm talking about mysql_connect();, mysql_query(); etc. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 10:04:02 -0400 Paul DuBois [EMAIL PROTECTED] wrote: Second reason still applies. yes, but I suppose he knows what he's trying to do =) If it's still something deemed desireable: ALTER TABLE tbl_name AUTO_INCREMENT = 1; yes, this is another one possible solution, but I think that DELETE FROM table; ALTER TABLE table AUTO_INCREMENT = 1; is not so pretty, as TRUNCATE TABLE table; and is not so efficient. am I wrong? (or 0, but sequences don't really begin with 0, they begin with 1. :-)) yep =) but we both know what he's talking about... it's a some kind of telepathy =)) --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Telemobile auto-reply
On Tue, 26 Aug 2003 02:54:13 -0700 Scott Haneda [EMAIL PROTECTED] wrote: Anyone else get one of these for every post to this list? yes, every time I post an answer. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many records can a single MySql Table Hold.
On Mon, 25 Aug 2003 10:05:32 +0530 (IST) Rupak Banerjee [EMAIL PROTECTED] wrote: Hi, I have a mysql database running on Mysql version 3.23.53 on a Red Hat Linux 7.2. In the database there is a single table with over 150,000 records. Unfortunately, the performance of that table is very poor, comparing to other tables in the same database. Can somebody help me out. Thanks, Rupak Banerjee. read about query optimization and table indexes. use EXPLAIN SELECT .. to see what happends with your query. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie mysql table size?
On Mon, 25 Aug 2003 15:17:25 +0700 Alf Koswara [EMAIL PROTECTED] wrote: Hi, i read the mysql manual and it's explained the maximum table size in many operating system, but i can't find the maximum size for MS window (win32), what's the maximum table size for win32? My office want to migrate the database server to mysql, and it's contains a millions record. Can mysql hold up tables with each have millions record? Thanks, Alfha K. You can have up to 4 billion rows in one MyISAM table. Table size is limited only by your filesystem (as you understand, limits of your filesystem is not the topic to be discussed on this list). In case of win32, I suppose, the best place to search info about filesystem limits would be microsoft.com. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My SQL Search
On Fri, 22 Aug 2003 23:28:18 + Nathan Simms [EMAIL PROTECTED] wrote: Hi, I'm currently using the following mysql statement to perform a search. Right now it is only returning the results which are found in the description field. How do I modify this statement so that it is searching the following fields: company_name, product_name, description. SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND description regexp '#FORM.query#' try this one: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND description LIKE '%FORM.query%' AND company_name LIKE '%FORM.query%' AND product_name LIKE '%FORM.query%'; of course, you should use LIKE only when you're seeking for partial match. in other cases use company_name='Bla'. and don't forget about indexes. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tellme Timestamp
On Fri, 22 Aug 2003 17:14:38 -0500 Dan J. Rychlik [EMAIL PROTECTED] wrote: Hello All, I am trying to find out how I can change my timestamp(14) to timestamp(8). use DATE fields instead. or use DATE_FORMAT() with TIMESTAMP to get only date. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP mysql_connect randomly failing
On Thu, 21 Aug 2003 18:18:37 -0700 Jon Drukman [EMAIL PROTECTED] wrote: that is true, but in this case, neither $php_errmsg nor mysql_error() return anything. i print them both out when the connect fails and they are both just blank. try to use error_reporting(E_ALL); and to see what happends. maybe error logging will help you to solve your trouble. I don't beleive in such bugs, it's a problem of your code imho. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP API Question.
On Thu, 21 Aug 2003 17:56:55 -0400 Rajesh Kumar [EMAIL PROTECTED] wrote: But does anyone know of a way to find out the number of seconds it took to execute a particular query using PHP? use microtime(); - http://php.net/microtime --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: apache/mysql errors....
On Wed, 20 Aug 2003 10:52:35 -0700 bruce [EMAIL PROTECTED] wrote: I have added the mysql user/password to the php file. I can access mysql using the user/password from the linux command line. What login pass do you use in PHP and what login pass do you use in command line? Hmm..let me guess: you just type mysql and it works? If yes, then you just using default root login without password. You need to use GRANT(read corresponding docs) to add another one user, cause your PHP app definitely do not need root privileges. And don't forget about FLUSH PRIVILEGES. However, when I check mysql, I don't have an Apache user defined in the user table. Do I need to have one defined, or should the php app utilize the user/passwd/dbname that I provide in the db_connect function? There is no Apache user. You may add user with login my_super_duper_user and use it in PHP to connect to MySQL. I can access the phpMyAdmin application with no apparent issues/problems... Your phpMyAdmin uses default settings too. As an additional question, if I have a web app that has a mysql db, do I need to allow Apache to have access to each table that the app uses...? Not Apache, but user, that is used by your PHP app should have access to tables databases, which you want to read/change. Read about GRANT in docs and give corresponding privileges to your user. Any help/assitance/pointers to resolve this would be greatle appreciated... http://www.mysql.com/doc/en/GRANT.html --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP mysql_connect randomly failing
On Wed, 20 Aug 2003 21:31:48 -0700 Jon Drukman [EMAIL PROTECTED] wrote: mysql_error is not set when mysql_connect fails, because there is no actual mysql resource to get the error message from. yes, there is no mysql resource at this moment. just don't specify it and mysql_error() will tell something like Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111). --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP mysql_connect randomly failing
On Tue, 19 Aug 2003 14:36:15 -0700 Jon Drukman [EMAIL PROTECTED] wrote: I've got a library of PHP code whose first line is a mysql_connect statement, like this: $dbh=mysql_connect() or die(mysql connect failed: $php_errmsg); Approximately 1% of the time it just fails, for no stated reason: Warning: mysql_connect() [http://www.php.net/function.mysql-connect]: in /var/httpd/htdocs/pi/pi.php on line 3 mysql connect failed: Any ideas why this would be happening? PHP is version 4.3.1 (same results with the latest 4.3.3 release candidate), Mysql is 4.0.12 You should turn track_errors = On in your php.ini (default value is Off), if you want to see last PHP error in $php_errmsg variable. But I suppose the best solution would be not to use $php_errmsg, but to use mysql_error() - at least this function does not depend on configuration settings. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with SELECT statement for date range
On Tue, 19 Aug 2003 04:11:32 -0700 (PDT) Rob Sirota [EMAIL PROTECTED] wrote: Hello, I am having a problem when doing a SELECT. Here is the scenerio: I have a table that has an event StartDate and EndDate, based on the current Date NOW() I need to know which records are currently active. Can anyone help with a quick SELECT statement? I suppose you need this: SELECT * FROM table WHERE NOW() BETWEEN StartDate AND EndDate; --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting output of Text when retriving data via PHP
On Tue, 19 Aug 2003 12:42:44 -0500 Sean Meegan [EMAIL PROTECTED] wrote: I have a table with a Longtext field in it. This is a field which I would like to print out, but I only wish to print a couple of lines of data from this field for each record. I'm using PHP with $row=mysql_fetch_row function and then printing out that row as one of of the fields. Can someone aid me with this? don't retreive all this text. get some string from it using SUBSTRING() in MySQL. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
