Re: [PHP-DB] mySQL SELECT query
In the SQL standard this would be AND NOT ministry_directory.listing_approved=0 AND NOT ministry_directory.listing_approved=4 MySQL supports (and there are probably more RDBs that do): AND ministry_directory.listing_approved!=0 AND ministry_directory.listing_approved!=4 On Fri, Aug 29, 2008 at 7:09 PM, Ron Piggott [EMAIL PROTECTED] wrote: Is there a way to make this part of a SELECT query more concise? AND ministry_directory.listing_approved NOT LIKE '0' AND ministry_directory.listing_approved NOT LIKE '4' I know of the IN ( 10, 12 ) command. Is there something similar available for NOT LIKE? Ron -- 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] mySQL SELECT query
or NOT IN (0,4) -- matt On Fri, Aug 29, 2008 at 12:19 PM, Evert Lammerts [EMAIL PROTECTED]wrote: In the SQL standard this would be AND NOT ministry_directory.listing_approved=0 AND NOT ministry_directory.listing_approved=4 MySQL supports (and there are probably more RDBs that do): AND ministry_directory.listing_approved!=0 AND ministry_directory.listing_approved!=4 On Fri, Aug 29, 2008 at 7:09 PM, Ron Piggott [EMAIL PROTECTED] wrote: Is there a way to make this part of a SELECT query more concise? AND ministry_directory.listing_approved NOT LIKE '0' AND ministry_directory.listing_approved NOT LIKE '4' I know of the IN ( 10, 12 ) command. Is there something similar available for NOT LIKE? Ron -- 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] Mysql logs
Hi All, Please help me to set the logging on in mysql. I am using the following command when starting mysql: service mysqld start --log=/usr/local/ But logging is not maintained through this command. Best Regards, Manoj Kumar Singh
Re: [PHP-DB] Mysql logs
You have to edit your my.cnf file. Here's a link to the docs: http://dev.mysql.com/doc/refman/5.0/en/log-files.html Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Manoj Singh wrote: Hi All, Please help me to set the logging on in mysql. I am using the following command when starting mysql: service mysqld start --log=/usr/local/ But logging is not maintained through this command. Best Regards, Manoj Kumar Singh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql logs
Hi Micah, Thanks for help. Best Regards, Manoj Kumar Singh On Thu, Jul 24, 2008 at 11:02 AM, Micah Gersten [EMAIL PROTECTED] wrote: You have to edit your my.cnf file. Here's a link to the docs: http://dev.mysql.com/doc/refman/5.0/en/log-files.html Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Manoj Singh wrote: Hi All, Please help me to set the logging on in mysql. I am using the following command when starting mysql: service mysqld start --log=/usr/local/ But logging is not maintained through this command. Best Regards, Manoj Kumar Singh
Re: [PHP-DB] MySQL circular buffer
(i.e. stack_id 500 stack_id 601 vs where stack_id = 500 limit 100)
stack_id between 501 and 600 (stack_id 500 stack_id 601) is much
better
What I would like to know is if anybody has experience
implementing this sort of data structure in MySQL (linked list?) or
any advice.
tables:
process_table:
IDProcess PK
mail_table
IDMail PK
IDPrrocess FK references process_table.IDProcess ON DELETE SET NULL
Mail TEXT
From VCH(255)
TO VCH(255)
DateModified DATE ON UPDATE CURRENT_TIMESTAMP
Mailed TINYINT DEFAULT 0;
--
code:
define('MaxProccessTimeMinutes', 30);
define('BatchCount', 100);
INSERT INTO process_table VALUES ();
$lnIdProcess = GetLastIDProcess();
label send_mail:
$ldDateExpired = time() - MaxProccessTimeMinutes * 60;
UPDATE mail_table
SET IDProcess = $lnIdProcess
WHERE
Mailed = 0 AND
(
IDProcess IS NULL OR
(
IDProcess IS NOT NULL AND
DateModified = $ldDateExpired
);
)
LIMIT BatchCount;
while ($result = SELECT IDMail, MailText, From, To FROM mail_table WHERE
IDProcess = $lnIDProcess)
{
if (send_mail())
{
UPDATE mail_table SET Mailed = 1 WHERE IDMail = $result['IDMail'];
}
}
if there are other mails goto send_mail;
else DELETE FROM process_table WHERE IDProcess = $lnIDProcess;
this way more than one process can send mails, also if one process exits
prematurely the other can send his emails later.
Time to time run cron:
DELETE FROM mail_table WHERE Mailed = 1;
rebuild indexes on mail_table;
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL circular buffer
Hello, I'm looking at implementing a database-level stack for a (multi stack) mail merge queue (able to queue up to 1 million messages per stack). Current workflow: server 1 (containing main db) transmits data sets (used to populate mail merge template) to server 2 server 2 web-facing script (script 1) puts data sets onto stack (db) server 2 mail process script (script 2) pulls single data block (say 100 rows) from front of stack (fifo), merges the data with the template and sends data to smtp process server 2 script 2 removes processed block of rows The problems I am considering include keeping track of the value of the primary key across multiple instances of script 2 (the script will run from a cron), whether to select by limit or range (i.e. stack_id 500 stack_id 601 vs where stack_id = 500 limit 100) and looping the index back to zero while ensuring there is no data that hasn't been deleted. So - it seems easier to avoid these problems and implement a circular buffer :) What I would like to know is if anybody has experience implementing this sort of data structure in MySQL (linked list?) or any advice. There don't seem to be any current implementations so the last question is - is there a good reason for that? Too many overheads? I know this sort of structure is best kept in memory and not on disk, but I am not sure of any other solution to a queue this size. Any comments welcome. Many thanks, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL replication delaying issue
Hello Everybody, We have 6 multi-master MySql instances within a LAN , that are replicating is a sequential manner. Server Environment : Six identical Linux Enterprise version 4 running, servers with having MySQL version 6. we are using MySQL multi-master replication method for database replication. There is a delay (5-7 minutes) of that data replication process. We notice that it's because of MySQL table locking. Your comments are highly appreciated. Many thanks in advance and best regards, Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) 7 Fax +971 4 295 8910 + [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.dotw.com/ DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means.
Re: [PHP-DB] MySQL replication delaying issue
On Tue, Feb 26, 2008 at 10:49 AM, Lasitha Alawatta [EMAIL PROTECTED] wrote:
Hello Everybody,
We have 6 multi-master MySql instances within a LAN , that are replicating
is a sequential manner.
Server Environment : Six identical Linux Enterprise version 4 running,
servers with having MySQL version 6.
we are using MySQL multi-master replication method for database
replication.
There is a delay (5-7 minutes) of that data replication process. We notice
that it's because of MySQL table locking.
Your comments are highly appreciated.
Ask on a MySQL list. It has nothing to do with PHP.
http://lists.mysql.com/
Specifically: Replication - http://lists.mysql.com/replication
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RES: [PHP-DB] MySQL replication delaying issue
I did not say: do not ask on this list. I said: you would get BETTER answers if you asked on a MySQL list. _ De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 13:47 Para: Thiago Pojda Assunto: RE: [PHP-DB] MySQL replication delaying issue Hay, If U dont know about that then u can ignore that mail. U need not to answer each and every mails in this list. MOST of the PHP developer are using MYSQL as backend. Thats Y I sent that message to phpresources list also. From: Thiago Pojda [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 26, 2008 8:39 PM To: Lasitha Alawatta; php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RES: [PHP-DB] MySQL replication delaying issue Not trying to be an ass, but wouldn't you get better answers if you asked in a MySQL list? I work with PHP, but have near to 0 experience with MySQL. _ De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 12:50 Para: php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Assunto: [PHP-DB] MySQL replication delaying issue Prioridade: Alta Hello Everybody, We have 6 multi-master MySql instances within a LAN , that are replicating is a sequential manner. Server Environment : Six identical Linux Enterprise version 4 running, servers with having MySQL version 6. we are using MySQL multi-master replication method for database replication. There is a delay (5-7 minutes) of that data replication process. We notice that its because of MySQL table locking. Your comments are highly appreciated. Many thanks in advance and best regards, Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) 7 Fax +971 4 295 8910 + [EMAIL PROTECTED] http://www.dotw.com/ cid:image002.jpg@01C7A7A4.0AC70A00 DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means.
RES: [PHP-DB] MySQL replication delaying issue
Not trying to be an ass, but wouldn't you get better answers if you asked in a MySQL list? I work with PHP, but have near to 0 experience with MySQL. _ De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 12:50 Para: php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Assunto: [PHP-DB] MySQL replication delaying issue Prioridade: Alta Hello Everybody, We have 6 multi-master MySql instances within a LAN , that are replicating is a sequential manner. Server Environment : Six identical Linux Enterprise version 4 running, servers with having MySQL version 6. we are using MySQL multi-master replication method for database replication. There is a delay (5-7 minutes) of that data replication process. We notice that its because of MySQL table locking. Your comments are highly appreciated. Many thanks in advance and best regards, Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) 7 Fax +971 4 295 8910 + mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.dotw.com/ cid:image002.jpg@01C7A7A4.0AC70A00 DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means.
RE: [PHP-DB] MySQL replication delaying issue
Oky, Sorry for that.. J From: Thiago Pojda [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 26, 2008 8:49 PM To: Lasitha Alawatta Cc: php-db@lists.php.net Subject: RES: [PHP-DB] MySQL replication delaying issue I did not say: do not ask on this list. I said: you would get BETTER answers if you asked on a MySQL list. De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 13:47 Para: Thiago Pojda Assunto: RE: [PHP-DB] MySQL replication delaying issue Hay, If U don't know about that then u can ignore that mail. U need not to answer each and every mails in this list. MOST of the PHP developer are using MYSQL as backend. That's Y I sent that message to phpresources list also. From: Thiago Pojda [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 26, 2008 8:39 PM To: Lasitha Alawatta; php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RES: [PHP-DB] MySQL replication delaying issue Not trying to be an ass, but wouldn't you get better answers if you asked in a MySQL list? I work with PHP, but have near to 0 experience with MySQL. De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 12:50 Para: php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Assunto: [PHP-DB] MySQL replication delaying issue Prioridade: Alta Hello Everybody, We have 6 multi-master MySql instances within a LAN , that are replicating is a sequential manner. Server Environment : Six identical Linux Enterprise version 4 running, servers with having MySQL version 6. we are using MySQL multi-master replication method for database replication. There is a delay (5-7 minutes) of that data replication process. We notice that it's because of MySQL table locking. Your comments are highly appreciated. Many thanks in advance and best regards, Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) 7 Fax +971 4 295 8910 + [EMAIL PROTECTED] cid:image002.jpg@01C7A7A4.0AC70A00 http://www.dotw.com/ DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL replication delaying issue
Hello, Thanks a LOT... Best Regards, Lasitha From: Thiago Pojda [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 26, 2008 9:51 PM To: Lasitha Alawatta Cc: php-db@lists.php.net Subject: RES: [PHP-DB] MySQL replication delaying issue That's ok, good luck with your problem. Perhaps this could help :) http://forums.mysql.com/read.php?26,197734,197734#msg-197734 De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 14:44 Para: Thiago Pojda Cc: php-db@lists.php.net Assunto: RE: [PHP-DB] MySQL replication delaying issue Oky, Sorry for that.. J From: Thiago Pojda [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 26, 2008 8:49 PM To: Lasitha Alawatta Cc: php-db@lists.php.net Subject: RES: [PHP-DB] MySQL replication delaying issue I did not say: do not ask on this list. I said: you would get BETTER answers if you asked on a MySQL list. De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 13:47 Para: Thiago Pojda Assunto: RE: [PHP-DB] MySQL replication delaying issue Hay, If U don't know about that then u can ignore that mail. U need not to answer each and every mails in this list. MOST of the PHP developer are using MYSQL as backend. That's Y I sent that message to phpresources list also. From: Thiago Pojda [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 26, 2008 8:39 PM To: Lasitha Alawatta; php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RES: [PHP-DB] MySQL replication delaying issue Not trying to be an ass, but wouldn't you get better answers if you asked in a MySQL list? I work with PHP, but have near to 0 experience with MySQL. De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 12:50 Para: php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Assunto: [PHP-DB] MySQL replication delaying issue Prioridade: Alta Hello Everybody, We have 6 multi-master MySql instances within a LAN , that are replicating is a sequential manner. Server Environment : Six identical Linux Enterprise version 4 running, servers with having MySQL version 6. we are using MySQL multi-master replication method for database replication. There is a delay (5-7 minutes) of that data replication process. We notice that it's because of MySQL table locking. Your comments are highly appreciated. Many thanks in advance and best regards, Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) 7 Fax +971 4 295 8910 + [EMAIL PROTECTED] cid:image002.jpg@01C7A7A4.0AC70A00 http://www.dotw.com/ DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RES: [PHP-DB] MySQL replication delaying issue
That's ok, good luck with your problem. Perhaps this could help :) http://forums.mysql.com/read.php?26,197734,197734#msg-197734 _ De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 14:44 Para: Thiago Pojda Cc: php-db@lists.php.net Assunto: RE: [PHP-DB] MySQL replication delaying issue Oky, Sorry for that.. J From: Thiago Pojda [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 26, 2008 8:49 PM To: Lasitha Alawatta Cc: php-db@lists.php.net Subject: RES: [PHP-DB] MySQL replication delaying issue I did not say: do not ask on this list. I said: you would get BETTER answers if you asked on a MySQL list. _ De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 13:47 Para: Thiago Pojda Assunto: RE: [PHP-DB] MySQL replication delaying issue Hay, If U dont know about that then u can ignore that mail. U need not to answer each and every mails in this list. MOST of the PHP developer are using MYSQL as backend. Thats Y I sent that message to phpresources list also. From: Thiago Pojda [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 26, 2008 8:39 PM To: Lasitha Alawatta; php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RES: [PHP-DB] MySQL replication delaying issue Not trying to be an ass, but wouldn't you get better answers if you asked in a MySQL list? I work with PHP, but have near to 0 experience with MySQL. _ De: Lasitha Alawatta [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 26 de fevereiro de 2008 12:50 Para: php-db@lists.php.net; [EMAIL PROTECTED]; [EMAIL PROTECTED] Assunto: [PHP-DB] MySQL replication delaying issue Prioridade: Alta Hello Everybody, We have 6 multi-master MySql instances within a LAN , that are replicating is a sequential manner. Server Environment : Six identical Linux Enterprise version 4 running, servers with having MySQL version 6. we are using MySQL multi-master replication method for database replication. There is a delay (5-7 minutes) of that data replication process. We notice that its because of MySQL table locking. Your comments are highly appreciated. Many thanks in advance and best regards, Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) 7 Fax +971 4 295 8910 + [EMAIL PROTECTED] http://www.dotw.com/ cid:image002.jpg@01C7A7A4.0AC70A00 DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means.
RE: [PHP-DB] mysql data truncation does not cause an error to be thrown
You may want to check the my.ini setting for the table type you are using and see if there is a setting in there that needs to be enabled. I thought I read once that truncation may happen silently depending on the my.ini setting. Just a thought. Ken Vandegrift Shari's Management Corporation Web Developer/Administrator (Direct) 503-605-4132 [EMAIL PROTECTED] -Original Message- From: Andrew Blake [mailto:[EMAIL PROTECTED] Sent: Thursday, November 08, 2007 7:51 AM To: Instruct ICC Cc: php-db@lists.php.net Subject: Re: [PHP-DB] mysql data truncation does not cause an error to be thrown Hiya I could check the length of the field against the entry data and javascript myself out of trouble but i was more worried that there is no error or message when mysql clearly returns one saying i've truncated this yet php ignores it completely. It should fail or know about the truncation at least ! Cheers for your reply though :-) Andy Instruct ICC wrote: Using mysql_query if i try to force more data than a field can have the data is truncated yet no error is throw at all. Is there a way round this ? Cheers Andy This isn't exactly what you want to hear, but how about validating your input before submitting a query? _ Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare! http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotm ailnews -- 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] mysql data truncation does not cause an error to be thrown
I agree. And maybe there is an error reporting level that can be set in mysql. But you should also start sanitizing user input. And do it on the server side (even if you do some on the client side). Maybe your form's action script could be sent to directly and your javascript validation sidestepped. Date: Thu, 8 Nov 2007 15:50:38 + From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: php-db@lists.php.net Subject: Re: [PHP-DB] mysql data truncation does not cause an error to be thrown Hiya I could check the length of the field against the entry data and javascript myself out of trouble but i was more worried that there is no error or message when mysql clearly returns one saying i've truncated this yet php ignores it completely. It should fail or know about the truncation at least ! Cheers for your reply though :-) Andy Instruct ICC wrote: Using mysql_query if i try to force more data than a field can have the data is truncated yet no error is throw at all. Is there a way round this ? Cheers Andy This isn't exactly what you want to hear, but how about validating your input before submitting a query? _ Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare! http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotmailnews -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Help yourself to FREE treats served up daily at the Messenger Café. Stop by today. http://www.cafemessenger.com/info/info_sweetstuff2.html?ocid=TXT_TAGLM_OctWLtagline
RE: [PHP-DB] mysql data truncation does not cause an error to be thrown
Using mysql_query if i try to force more data than a field can have the data is truncated yet no error is throw at all. Is there a way round this ? Cheers Andy This isn't exactly what you want to hear, but how about validating your input before submitting a query? _ Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare! http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotmailnews
[PHP-DB] mysql data truncation does not cause an error to be thrown
Using mysql_query if i try to force more data than a field can have the data is truncated yet no error is throw at all. Is there a way round this ? Cheers Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql data truncation does not cause an error to be thrown
I figured it out it was the mysql install not php :-) cheers for your help though :-) Vandegrift, Ken wrote: You may want to check the my.ini setting for the table type you are using and see if there is a setting in there that needs to be enabled. I thought I read once that truncation may happen silently depending on the my.ini setting. Just a thought. Ken Vandegrift Shari's Management Corporation Web Developer/Administrator (Direct) 503-605-4132 [EMAIL PROTECTED] -Original Message- From: Andrew Blake [mailto:[EMAIL PROTECTED] Sent: Thursday, November 08, 2007 7:51 AM To: Instruct ICC Cc: php-db@lists.php.net Subject: Re: [PHP-DB] mysql data truncation does not cause an error to be thrown Hiya I could check the length of the field against the entry data and javascript myself out of trouble but i was more worried that there is no error or message when mysql clearly returns one saying i've truncated this yet php ignores it completely. It should fail or know about the truncation at least ! Cheers for your reply though :-) Andy Instruct ICC wrote: Using mysql_query if i try to force more data than a field can have the data is truncated yet no error is throw at all. Is there a way round this ? Cheers Andy This isn't exactly what you want to hear, but how about validating your input before submitting a query? _ Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare! http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotm ailnews -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql data truncation does not cause an error to be thrown
Hiya I could check the length of the field against the entry data and javascript myself out of trouble but i was more worried that there is no error or message when mysql clearly returns one saying i've truncated this yet php ignores it completely. It should fail or know about the truncation at least ! Cheers for your reply though :-) Andy Instruct ICC wrote: Using mysql_query if i try to force more data than a field can have the data is truncated yet no error is throw at all. Is there a way round this ? Cheers Andy This isn't exactly what you want to hear, but how about validating your input before submitting a query? _ Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare! http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotmailnews -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] mysql data truncation does not cause an error to be thrown
Maybe tell the list the exact solution? File/variable/setting? Cheers. I figured it out it was the mysql install not php :-) cheers for your help though :-) Vandegrift, Ken wrote: You may want to check the my.ini setting for the table type you are using and see if there is a setting in there that needs to be enabled. I thought I read once that truncation may happen silently depending on the my.ini setting. _ Peek-a-boo FREE Tricks Treats for You! http://www.reallivemoms.com?ocid=TXT_TAGHMloc=us
[PHP-DB] MySQL Identifying worst-performing codes
Hello friends, There is a tool call idera (SQL diagnostic manager). Basically it is a performance monitoring and diagnostics tool. It has a feature; Identifying of worst-performing codes - Identifies performance bottlenecks such as the worst-performing stored procedures, long-running queries, most frequently run queries, SQL Statements and SQL batches http://www.idera.com/Products/SQLdm/Features.aspx I'm looking for a same like tool for MySQL. Is anyone have any ideas. Thanks in advance, Best Regards, Lasitha DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql statement
I am looking for some advice on how to achieve something and so far have been unable to do what I am looking to do. Here is the query I am using: mysql SELECT * - FROM `orders` - WHERE `ordernum` LIKE 35132 - OR `price` LIKE 35132 - OR `partnum` LIKE 35132 - OR `vendor` LIKE 35132 - OR `purpose` LIKE 35132 - OR `tracking` LIKE 35132 - OR `contact` LIKE 35132 - AND `group` LIKE 'mac' - ORDER BY `ordernum` - LIMIT 0 , 30; First here is the table structure: mysql describe orders; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(255) | NO | PRI | | auto_increment | | ordernum| int(10) | NO | | || | date| varchar(60) | NO | | || | time| varchar(20) | NO | | || | group | varchar(20) | NO | | || | quantity| int(10) | NO | | || | description | varchar(255) | NO | | || | price | decimal(3,0) | NO | | || | partnum | varchar(40) | NO | | || | vendor | varchar(65) | NO | | || | purpose | varchar(255) | NO | | || | tracking| varchar(120) | NO | | || | contact | varchar(255) | NO | | || | eta | varchar(50) | NO | | || | department | varchar(125) | NO | | || | notes | varchar(255) | NO | | || +-+--+--+-+-++ 16 rows in set (0.00 sec) I am trying to essentially LIMIT all records returned to be limited by the `group` field so I can search for records and limit the rows returned by that one field. Any tips? TIA. jas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql error...
this is bugging me to no end (no pun intended)... I am getting the error:
invalid query: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'Condition, ConstructType, BldgDimensions, Stories,
CurrentParking, MaxParking, P' at line 1
I cannot find a thing wrong with this... any ideas what may be the
problem? All the data types are either int(11) or varchar(50).
John
INSERT INTO tblBuildings (SiteID, Name, Available, MfgWHSqFt,
OfficeSqFt, TotalSqFt, MinSqFtAvailable, MaxSqFtAvailable, Condition,
ConstructType, BldgDimensions, Stories, CurrentParking, MaxParking,
ParkingSurface, PctOccupied, ClearSpan, CeilingMax, PctAC, PctHeated,
FloorType, Sprinkled, DHDoors, DHSize, GLDoors, GLSize, RRDoors, RRSize,
CraneNum, CraneSize, RefrigSize) VALUES ('', '[enter building name]',
'Y', '', '', '', '', '', '- Select -', '- Select -', '', '', '', '', '-
Select -', '', '', '', '', '', '- Select -', 'Y', '', '', '', '', '',
'', '', '', '')
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] mysql error...
Condition is a reserved word in mysql...you will need to change the field name
bastien Date: Tue, 28 Aug 2007 18:11:20 -0700 From: [EMAIL PROTECTED] To:
php-db@lists.php.net Subject: [PHP-DB] mysql error... this is bugging me to
no end (no pun intended)... I am getting the error: invalid query: You have
an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'Condition, ConstructType,
BldgDimensions, Stories, CurrentParking, MaxParking, P' at line 1 I cannot
find a thing wrong with this... any ideas what may be the problem? All the
data types are either int(11) or varchar(50). JohnINSERT INTO
tblBuildings (SiteID, Name, Available, MfgWHSqFt, OfficeSqFt, TotalSqFt,
MinSqFtAvailable, MaxSqFtAvailable, Condition, ConstructType, BldgDimensions,
Stories, CurrentParking, MaxParking, ParkingSurface, PctOccupied, ClearSpan,
CeilingMax, PctAC, PctHeated, FloorType, Sprinkled, DHDoors, DHSize, GLDoors,
GLSize, RRDoors, RRSize, CraneNum, CraneSize, RefrigSize) VALUES ('', '[enter
building name]', 'Y', '', '', '', '', '', '- Select -', '- Select -', '', '',
'', '', '- Select -', '', '', '', '', '', '- Select -', 'Y', '', '', '', '',
'', '', '', '', '') -- PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
_
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+worldmkt=en-USform=QBRE
Re: [PHP-DB] mysql error...
John Pillion wrote: this is bugging me to no end (no pun intended)... I am getting the error: invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Condition, ConstructType, BldgDimensions, Stories, CurrentParking, MaxParking, P' at line 1 I cannot find a thing wrong with this... any ideas what may be the problem? All the data types are either int(11) or varchar(50). condition is a reserved word in mysql 5. http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL- Stored Procedures Views
Hi All, Any body has experience using MySQL Stored Procedures Views with PHP. How is the performance of using SPs in mysql. Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql different server different EXPLAIN result
Dear All I'm running 3 servers with mysql database - Local : 5.0.18-log, MySQL Community Edition (GPL) - Main Server : 4.1.22-standard-log, MySQL Community Edition - Standard (GPL) - Backup Server : 4.1.20, Source Distribution I copied a database from the Main Server to Local and Backup Server and run a SQL explain command on the database but i get different EXPLAIN result. I think because i copied it from one source shouldn't it show the same EXPLAIN result cause i didn't change any of the index, keys or records Here is the sql SELECT `t_order_match`.`match_id`, `t_order_match`.`order_ticket1` as hit_order_ticket, `tc1`.`user_id` as hit_user_id, `tc1`.`name` as hit_name, `tb1`.`company`as hit_company, `t_order_match`.`order_ticket2` as queue_order_ticket, `tc2`.`user_id` as queue_user_id, `tc2`.`name` as queue_name, `tb2`.`company` as queue_company, `t_order_match`.`commodity_id`, `t_order_match`.`month`, `t_order_match`.`lot`, `t_order_match`.`price`, `t_order_match`.`volume`, `t_order_match`.`time` FROM `t_order_match` left Join (`t_order_journal` AS `tj1` Inner Join (`t_customer` AS `tc1` INNER JOIN t_brokerage as tb1 ON tc1.broker_id=tb1.broker_id) ON `tj1`.`account_id` = `tc1`.`account_id`) ON `t_order_match`.`order_ticket1` = `tj1`.`order_ticket` left Join (`t_order_journal` AS `tj2` Inner Join (`t_customer` AS `tc2` INNER JOIN t_brokerage as tb2 ON tc2.broker_id=tb2.broker_id) ON `tj2`.`account_id` = `tc2`.`account_id`) ON `t_order_match`.`order_ticket2` = `tj2`.`order_ticket` limit 100; *The result of the Master * ++-+---++---+-+-+---+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+-+-+---+---+-+ | 1 | SIMPLE | tc1 | ALL| NULL | NULL |NULL | NULL | 320 | | | 1 | SIMPLE | tj1 | index | NULL | acc | 21 | NULL | 20674 | Using index | | 1 | SIMPLE | tc2 | *ALL*| NULL | NULL |NULL | NULL | 320 | | | 1 | SIMPLE | tj2 | index | NULL | acc | 21 | NULL | 20674 | Using index | | 1 | SIMPLE | t_order_match | *ALL * | NULL | NULL |NULL | NULL | 5972 | | | 1 | SIMPLE | tb1 | eq_ref | PRIMARY | PRIMARY | 20 | bbjengine78787-beta.tc1.broker_id | 1 | | | 1 | SIMPLE | tb2 | eq_ref | PRIMARY | PRIMARY | 20 | bbjengine78787-beta.tc2.broker_id | 1 | | ++-+---++---+-+-+---+---+-+ 7 rows in set (0.00 sec) *The result of Local * ++-+---++--+-+-+-+--+---+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra | ++-+---++--+-+-+-+--+---+ | 1 | SIMPLE | t_order_match | ALL| NULL | NULL| NULL| NULL| 2914 | | | 1 | SIMPLE | tj1 | eq_ref | PRIMARY,acc | PRIMARY | 4 | 166bbj-betafull.t_order_match.order_ticket1 |1 | | | 1 | SIMPLE | tc1 | ref| PRIMARY,account_id,broker_id | PRIMARY | 22 | 166bbj-betafull.tj1.account_id |1 | | | 1 | SIMPLE | tb1 | ref| PRIMARY | PRIMARY | 22 | 166bbj-betafull.tc1.broker_id |1 | | | 1 | SIMPLE | tj2 | eq_ref | PRIMARY,acc |
Re: [PHP-DB] mysql different server different EXPLAIN result
Hi I'm running 3 servers with mysql database - Local : 5.0.18-log, MySQL Community Edition (GPL) - Main Server : 4.1.22-standard-log, MySQL Community Edition - Standard (GPL) - Backup Server : 4.1.20, Source Distribution I copied a database from the Main Server to Local and Backup Server and run a SQL explain command on the database but i get different EXPLAIN result. I think because i copied it from one source shouldn't it show the same EXPLAIN result cause i didn't change any of the index, keys or records Not if that's what has changed between versions. Check the change log for details of what was altered, to see if it's relevant. -- Niel Archer -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql different server different EXPLAIN result
Chenri wrote: Dear All I'm running 3 servers with mysql database - Local : 5.0.18-log, MySQL Community Edition (GPL) - Main Server : 4.1.22-standard-log, MySQL Community Edition - Standard (GPL) - Backup Server : 4.1.20, Source Distribution Not sure why you're surprised why they are different. As with all software the new version will have stuff the old version didn't have. In this case v5 will have improvements in the engine. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL user anHTACCESSusername not found?
From: Instruct ICC [EMAIL PROTECTED] Why is this second log entry present? I don't see any place where I ask MySQL to authenticate an HTACCESS username. It came to me. The very fact that we are using mod_auth_mysql in the Apache web server means that Apache is trying to authenticate anHTACCESSusername in the MySQL database. I suppose since the web server could not connect to the database, anHTACCESSusername was not found after some timeout. _ http://imagine-windowslive.com/hotmail/?locale=en-usocid=TXT_TAGHM_migration_HM_mini_2G_0507 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL user anHTACCESSusername not found?
In my apache web server error log, I see the following: [Fri Jul 13 11:19:55 2007] [error] [client an.ip.addr.ess] MySQL ERROR: Can't connect to MySQL server on 'mysql.server.ip.address' (110) [Fri Jul 13 11:19:55 2007] [error] [client an.ip.addr.ess] MySQL user anHTACCESSusername not found: /path/to/webpage I can understand the first log entry; the client machine cannot contact the server machine at the moment. (It cleared up without any changes as far as I can tell. Perhaps a slow network?). What I don't understand is why MySQL appears to be trying to authenticate the HTACCESS username. Once the user logs in to the webpage with his HTACCESS username, the script connects to the MySQL server with the same MySQL login credentials for everyone. Even with register_globals on (which I can't turn off), I am explicitly setting the $username variable in mysql_connect() which I call as mysql_connect($server, $username, $password) to use to connect to MySQL. I'm on PHP 4.3.10, MySQL 4.1.20, sql.safe_mode is Off, mysql.default_user had no value. Doesn't the web server own the process anyway? From the docs: mysql_connect username The username. Default value is defined by mysql.default_user. In SQL safe mode, this parameter is ignored and the name of the user that owns the server process is used. mysql.default_user string The default user name to use when connecting to the database server if no other name is specified. Doesn't apply in SQL safe mode. Why is this second log entry present? I don't see any place where I ask MySQL to authenticate an HTACCESS username. Perhaps there is a default setting I can turn off? Thanks. _ http://imagine-windowslive.com/hotmail/?locale=en-usocid=TXT_TAGHM_migration_HM_mini_2G_0507 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Error 1366
Hi All, I want to load data from dump file to MySQL table using LOAD DATA INFILE but there is Error 1366 : mysql LOAD DATA - INFILE 'D:/SITE/SOMETABLE.SQL' - INTO TABLE SOMETABLE - FIELDS TERMINATED BY ',' - OPTIONALLY ENCLOSED BY '' - LINES TERMINATED BY ')'; ERROR 1366 (HY000): Incorrect integer value: '--MySQL dump 10.10 -- --S' for column 'ID' at row 1 this is the header of my dump file: DROP TABLE IF EXISTS `sometable`; CREATE TABLE `sometableo` ( `ID` smallint(6) NOT NULL auto_increment, `Name` varchar(30) NOT NULL, `title` tinytext, `description` tinytext, `cat` tinytext, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; LOCK TABLES `sometable` WRITE; /*!4 ALTER TABLE `sometable` DISABLE KEYS */; INSERT INTO `sometable` VALUES (79,'110_1099','AAA','AA','AAA'),(80,'110_1100','AAA','DFGDFGF','AAA'), any idea for solving the problem? - Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.
Re: [PHP-DB] MySQL Error 1366
elk dolk wrote: Hi All, I want to load data from dump file to MySQL table using LOAD DATA INFILE but there is Error 1366 : mysql LOAD DATA - INFILE 'D:/SITE/SOMETABLE.SQL' - INTO TABLE SOMETABLE - FIELDS TERMINATED BY ',' - OPTIONALLY ENCLOSED BY '' - LINES TERMINATED BY ')'; ERROR 1366 (HY000): Incorrect integer value: '--MySQL dump 10.10 -- --S' for column 'ID' at row 1 LOAD DATA INFILE imports a CSV like file, it can't contain create table statements or insert statements. See the documentation: http://dev.mysql.com/doc/refman/4.1/en/load-data.html If you have a script with create table insert statements, use source: source (\.)Execute a SQL script file. Takes a file name as an argument. So \. filename -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Error 1366
Chetanji says, This may be a typo of yours. Look at the header... CREATE TABLE `sometableo` The 'o' is added by mistake? Blessings, Chetanji elk dolk wrote: Hi All, I want to load data from dump file to MySQL table using LOAD DATA INFILE but there is Error 1366 : mysql LOAD DATA - INFILE 'D:/SITE/SOMETABLE.SQL' - INTO TABLE SOMETABLE - FIELDS TERMINATED BY ',' - OPTIONALLY ENCLOSED BY '' - LINES TERMINATED BY ')'; ERROR 1366 (HY000): Incorrect integer value: '--MySQL dump 10.10 -- --S' for column 'ID' at row 1 this is the header of my dump file: DROP TABLE IF EXISTS `sometable`; CREATE TABLE `sometableo` ( `ID` smallint(6) NOT NULL auto_increment, `Name` varchar(30) NOT NULL, `title` tinytext, `description` tinytext, `cat` tinytext, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; LOCK TABLES `sometable` WRITE; /*!4 ALTER TABLE `sometable` DISABLE KEYS */; INSERT INTO `sometable` VALUES (79,'110_1099','AAA','AA','AAA'),(80,'110_1100','AAA','DFGDFGF','AAA'), any idea for solving the problem? - Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when. -- View this message in context: http://www.nabble.com/MySQL-Error-1366-tf3830472.html#a10846547 Sent from the Php - Database mailing list archive at Nabble.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL BETWEEN Problems
I'm using BETWEEN to return a list all people who's last names fall between A and F: WHERE last_name BETWEEN 'A' AND 'F' ... but it's only returning names between A and E. The same thing happens when I use: WHERE last_name = 'A' AND last_name = 'F' ... Shouldn't this work the way I expect it? What am I doing wrong? Tony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL BETWEEN Problems
I think you need between 'A' and 'F%' Aa is between A and F but 'Fa' is not between 'A' and 'F' (but 'Ez' would be between 'A' and 'F') -B Tony Grimes wrote: I'm using BETWEEN to return a list all people who's last names fall between A and F: WHERE last_name BETWEEN 'A' AND 'F' ... but it's only returning names between A and E. The same thing happens when I use: WHERE last_name = 'A' AND last_name = 'F' ... Shouldn't this work the way I expect it? What am I doing wrong? Tony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL BETWEEN Problems
Try using the substr, since you are only comparing the first letters... (sorry I did not reply all on the other email, Dan) i.e. select name from users where substr(name, 1, 1) = 'A' and substr(name, 1, 1) = 'B'; -B Dan Shirah wrote: So just change it to WHERE last_name BETWEEN 'A' AND 'G' . That'll get you Aa-Fz :) On 5/18/07, Tony Grimes [EMAIL PROTECTED] wrote: We tried that and it didn't work. We've also tried every combination of upper and lower case too. Tony On 5/18/07 2:18 PM, Brad Bonkoski [EMAIL PROTECTED] wrote: I think you need between 'A' and 'F%' Aa is between A and F but 'Fa' is not between 'A' and 'F' (but 'Ez' would be between 'A' and 'F') -B Tony Grimes wrote: I'm using BETWEEN to return a list all people who's last names fall between A and F: WHERE last_name BETWEEN 'A' AND 'F' ... but it's only returning names between A and E. The same thing happens when I use: WHERE last_name = 'A' AND last_name = 'F' ... Shouldn't this work the way I expect it? What am I doing wrong? Tony -- 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] MySQL BETWEEN Problems
Yeah, but what do I do for Q-Z? I guess I¹ll have to write a condition to use last_name = Q¹ if it finds a Z. I¹ll have to write conditions to look for other exceptions too. Aaarrgh! *looks up at the Gods* Damn you BETWEEN! Why must you torture me so!?! Have a good weekend. On 5/18/07 2:51 PM, Dan Shirah [EMAIL PROTECTED] wrote: So just change it to WHERE last_name BETWEEN 'A' AND 'G' . That'll get you Aa-Fz :) On 5/18/07, Tony Grimes [EMAIL PROTECTED] wrote: We tried that and it didn't work. We've also tried every combination of upper and lower case too. Tony On 5/18/07 2:18 PM, Brad Bonkoski [EMAIL PROTECTED] wrote: I think you need between 'A' and 'F%' Aa is between A and F but 'Fa' is not between 'A' and 'F' (but 'Ez' would be between 'A' and 'F') -B Tony Grimes wrote: I'm using BETWEEN to return a list all people who's last names fall between A and F: WHERE last_name BETWEEN 'A' AND 'F' ... but it's only returning names between A and E. The same thing happens when I use: WHERE last_name = 'A' AND last_name = 'F' ... Shouldn't this work the way I expect it? What am I doing wrong? Tony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php http://www.php.net/unsub.php
Re: [PHP-DB] MySQL BETWEEN Problems
Nice! That did the trick. Thanks Brad. I live to fight another day. Tony On 5/18/07 3:00 PM, Brad Bonkoski [EMAIL PROTECTED] wrote: Try using the substr, since you are only comparing the first letters... (sorry I did not reply all on the other email, Dan) i.e. select name from users where substr(name, 1, 1) = 'A' and substr(name, 1, 1) = 'B'; -B Dan Shirah wrote: So just change it to WHERE last_name BETWEEN 'A' AND 'G' . That'll get you Aa-Fz :) On 5/18/07, Tony Grimes [EMAIL PROTECTED] wrote: We tried that and it didn't work. We've also tried every combination of upper and lower case too. Tony On 5/18/07 2:18 PM, Brad Bonkoski [EMAIL PROTECTED] wrote: I think you need between 'A' and 'F%' Aa is between A and F but 'Fa' is not between 'A' and 'F' (but 'Ez' would be between 'A' and 'F') -B Tony Grimes wrote: I'm using BETWEEN to return a list all people who's last names fall between A and F: WHERE last_name BETWEEN 'A' AND 'F' ... but it's only returning names between A and E. The same thing happens when I use: WHERE last_name = 'A' AND last_name = 'F' ... Shouldn't this work the way I expect it? What am I doing wrong? Tony -- 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] MySQL 0 index date and time functions (mode or typo)?
In MySQL, regardless of the documentation version, http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function _week I've seen the explanation for receiving a 0 from the WEEK function depending on the mode setting which causes a range between 0-53. But what about: DAYOFMONTH 0 to 31 MONTH 0 to 12 I was wondering if 0 may be for invalid dates, but I get NULL for the following invalid date: SELECT MONTH( '1998-14-03' ), DAYOFMONTH( '1998-14-03' ) When might I receive a 0 for these two functions? Regards, Dwight -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Query on the Fly
That's one of the uses of Ajax. http://www.xajaxproject.org/ http://wiki.xajaxproject.org/Tutorials:Learn_xajax_in_10_Minutes You don't have to use an iframe. Regards, Dwight -Original Message- From: Todd A. Dorschner [mailto:[EMAIL PROTECTED] Sent: Friday, May 11, 2007 8:31 AM To: php-db@lists.php.net Subject: [PHP-DB] MySQL Query on the Fly Good Morning, I've been searching and toying with this solution for some time but can't find that right answer. Looking for solutions/suggestions to the following: I created a program that will allow people to track sales and depending on what they've sold, they will either get a set bonus, or a bonus based on a percentage. I have no problem creating a drop down box for them to select from, and I could probably create a select button that would do the action, but I'd rather have it in one step. So what I'm trying to do is somehow when they select from the drop down (via a JavaScript onchange or something) I'd like it to pull data from the MySQL database. I'd like it to pull the amount and then each or per based on the ID of what they've selected from the drop down. If it was always based on each sale or based on a percentage, that would be easy as I could code that into the drop box, but I'd like to keep it based on the ID so that it can pull out of the database the name, and then when changed pull the info. Any ideas? Thanks in advance for the help. If you have any questions or it seems like I left something out, please let me know. Thank you for your time, Todd Dorschner -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Query on the Fly
Good Morning, I've been searching and toying with this solution for some time but can't find that right answer. Looking for solutions/suggestions to the following: I created a program that will allow people to track sales and depending on what they've sold, they will either get a set bonus, or a bonus based on a percentage. I have no problem creating a drop down box for them to select from, and I could probably create a select button that would do the action, but I'd rather have it in one step. So what I'm trying to do is somehow when they select from the drop down (via a JavaScript onchange or something) I'd like it to pull data from the MySQL database. I'd like it to pull the amount and then each or per based on the ID of what they've selected from the drop down. If it was always based on each sale or based on a percentage, that would be easy as I could code that into the drop box, but I'd like to keep it based on the ID so that it can pull out of the database the name, and then when changed pull the info. Any ideas? Thanks in advance for the help. If you have any questions or it seems like I left something out, please let me know. Thank you for your time, Todd Dorschner
Re: [PHP-DB] MySQL ERRORS
Chetan Graham wrote:
Hi to All,
I am having problems with the MySQL DB. It started with this command from
a call in a PHP script...
INSERT INTO docprouser (id,valid,password)VALUES
('user5','Y',md5('ksobhinyai')); or die(mysql_error());
That doesn't do anything (it will create a parse error).
You need to:
mysql_query(INSERT INTO docprouser (id,valid,password) VALUES(
'user5','Y',md5('ksobhinyai'))) or die(mysql_error());
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql question.
hi i need help regarding a sql query in my php app. the query is : $SQL = SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes' LIMIT $startingID,$items_numbers_list; i want to sort this query by the number of the repeated EMail counts. can anyone help me with that please ?
Re: [PHP-DB] mysql question.
On 4/3/07, Me2resh Lists [EMAIL PROTECTED] wrote: hi i need help regarding a sql query in my php app. the query is : $SQL = SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes' LIMIT $startingID,$items_numbers_list; i want to sort this query by the number of the repeated EMail counts. can anyone help me with that please ? $SQL = SELECT EMail,count(EMail) AS repeated FROM mena_guests WHERE Voted = 'yes' GROUP BY EMail ORDER BY count(EMail) LIMIT $startingID,$items_numbers_list; I can't remember if in the order clause you can order by the alias of the field or using the count again, test it to see what's the proper syntax -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql question.
Hello, I try your SQL statements. There is no problem on your SQL syntax and you can use alias in the order by clause.:) $SQL = SELECT EMail,count(EMail) AS repeated FROM mena_guests WHERE Voted = 'yes' GROUP BY EMail ORDER BY repeated LIMIT $startingID,$items_numbers_list; -- Republic Of Turkey - Ministry of National Education Education Technology Department Ankara / TURKEY Web: http://www.haydartuna.net Dimiter Ivanov [EMAIL PROTECTED], haber iletisinde sunlari yazdi:[EMAIL PROTECTED] On 4/3/07, Me2resh Lists [EMAIL PROTECTED] wrote: hi i need help regarding a sql query in my php app. the query is : $SQL = SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes' LIMIT $startingID,$items_numbers_list; i want to sort this query by the number of the repeated EMail counts. can anyone help me with that please ? $SQL = SELECT EMail,count(EMail) AS repeated FROM mena_guests WHERE Voted = 'yes' GROUP BY EMail ORDER BY count(EMail) LIMIT $startingID,$items_numbers_list; I can't remember if in the order clause you can order by the alias of the field or using the count again, test it to see what's the proper syntax -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Foreign Key Issue
Luchino - Samel wrote: *Question No. 01* there are some kind of foreign key in database Some of them want give any error when you delete from the table where the foreign key is in, this cause the remove is recursive and it delete also the ORDER linked to the CUSTOMER (CASCADE foreign key). In other kind of foreign key (I don't remember the name) you cannot delete CUSTOMER if there is some ORDER linked to them. restrict hope this will help c-ya 2007/3/26, Lasitha Alawatta [EMAIL PROTECTED]: Hello, I have 2 issue, regarding MySQL Foreign Key. I have two tables; Table 01 *CUSTOMER* column name characteristic SID Primary Key Full_Name Table *ORDERS* column name characteristic Order_ID Primary Key Order_Date Customer_SID Foreign Key Amount When I run ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID); that sql statement, I inserted 2 records to both tables. * * *Question No. 01.* Then I removed 1 record from CUSTOMER table. But It want give any error message. It should give an error message, because in ORDERS table already have some records relevant to the deleted customer record in CUSTOMER table. you have restrict constraint on Customer_SID in table orders. You have 2 options: 1. delete from orders where Customer_SID=foo delete from customer where SID=foo 2. read http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Foreign Key Issue
*Question No. 01* there are some kind of foreign key in database Some of them want give any error when you delete from the table where the foreign key is in, this cause the remove is recursive and it delete also the ORDER linked to the CUSTOMER (CASCADE foreign key). In other kind of foreign key (I don't remember the name) you cannot delete CUSTOMER if there is some ORDER linked to them. hope this will help c-ya 2007/3/26, Lasitha Alawatta [EMAIL PROTECTED]: Hello, I have 2 issue, regarding MySQL Foreign Key. I have two tables; Table 01 *CUSTOMER* column name characteristic SID Primary Key Full_Name Table *ORDERS* column name characteristic Order_ID Primary Key Order_Date Customer_SID Foreign Key Amount When I run ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID); that sql statement, I inserted 2 records to both tables. * * *Question No. 01.* Then I removed 1 record from CUSTOMER table. But It want give any error message. It should give an error message, because in ORDERS table already have some records relevant to the deleted customer record in CUSTOMER table. Question is why it want give any error ? (I'm using phpMyAdmin) *Question No. 02.* Is there any tool available for to get a Database diagram (like in MS SQL Server). I found a tool call DBDesigner 4. But it wants show the DB Diagram. Thanks in Advance, Lasitha. DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Samel alias Luca Close the world,txen eht nepo! You will never break my mind! LinuxUser:410006 eversor:316704 vindicare:316705 Wii owner: 7579-9856-9598-5214
[PHP-DB] MySQL Foreign Key Issue
Hello, I have 2 issue, regarding MySQL Foreign Key. I have two tables; Table 01 CUSTOMER column name characteristic SID Primary Key Full_Name Table ORDERS column name characteristic Order_ID Primary Key Order_Date Customer_SID Foreign Key Amount When I run ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID); that sql statement, I inserted 2 records to both tables. Question No. 01. Then I removed 1 record from CUSTOMER table. But It want give any error message. It should give an error message, because in ORDERS table already have some records relevant to the deleted customer record in CUSTOMER table. Question is why it want give any error ? (I'm using phpMyAdmin) Question No. 02. Is there any tool available for to get a Database diagram (like in MS SQL Server). I found a tool call DBDesigner 4. But it wants show the DB Diagram. Thanks in Advance, Lasitha. DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL queries
Hello all, Is there a discussion list for MySQL queries (I have a rookie MySQL query question and don't want to bug this list with it) Ron -- = It's is not, it isn't ain't, and it's it's, not its, if you mean it is. If you don't, it's its. Then too, it's hers. It isn't her's. It isn't our's either. It's ours, and likewise yours and theirs. -- Oxford Uni Press = Ron Croonenberg | | Phone: 1 765 658 4761 Lab Instructor | Fax: 1 765 658 4732 Technology Coordinator| | Department of Computer Science| e-mail: [EMAIL PROTECTED] DePauw University | 275 Julian Science Math Center | 602 South College Ave.| Greencastle, IN 46135| = http://www.csc.depauw.edu/RonCroonenberg.html = -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL queries
Ron Croonenberg wrote: Hello all, Is there a discussion list for MySQL queries (I have a rookie MySQL query question and don't want to bug this list with it) http://lists.mysql.com/ -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL queries
howdy - Original Message - From: Chris [EMAIL PROTECTED] To: Ron Croonenberg [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Thursday, March 22, 2007 7:06 AM Subject: Re: [PHP-DB] MySQL queries Ron Croonenberg wrote: Hello all, Is there a discussion list for MySQL queries (I have a rookie MySQL query question and don't want to bug this list with it) ??? what u mean?? this is the place u can bug about query well to be honest.. we unable to answer all kind question.. but you might ask to us. about query, there is no nobs in query (my opinion) if you face 2 or more table, u probably fail.. but plz don't give up yet. http://lists.mysql.com/ -- Postgresql php tutorials http://www.designmagick.com/ -- 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] MySQL sort stopped working
I have this page that lists artists alphabetically: http://www.wallacegalleries.com/artists.php At least it did until today. Nobody made any changes to the files, but the database stopped sorting by artist_id. The list shown is just the default order that mysql spits out if the SORT BY clause is omitted (as if I clicked 'browse' in phpMyAdmin). The SQL works in phpMyAdmin, and the site uses PEAR::DB. We patched the server last week, but the problem showed up this morning. Anyone have any ideas on what could be causing this? Thanks in advance, Tony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL sort stopped working
too small a sort space on the server? have you checked the server config? Bastien From: Tony Grimes [EMAIL PROTECTED] To: PHP-DB php-db@lists.php.net Subject: [PHP-DB] MySQL sort stopped working Date: Tue, 13 Mar 2007 13:22:25 -0600 I have this page that lists artists alphabetically: http://www.wallacegalleries.com/artists.php At least it did until today. Nobody made any changes to the files, but the database stopped sorting by artist_id. The list shown is just the default order that mysql spits out if the SORT BY clause is omitted (as if I clicked 'browse' in phpMyAdmin). The SQL works in phpMyAdmin, and the site uses PEAR::DB. We patched the server last week, but the problem showed up this morning. Anyone have any ideas on what could be causing this? Thanks in advance, Tony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Have Some Fresh Air Fun This March Break http://local.live.com/?mkt=en-ca/?v=2cid=A6D6BDB4586E357F!147 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL sort stopped working
Tony Grimes wrote: I have this page that lists artists alphabetically: http://www.wallacegalleries.com/artists.php At least it did until today. Nobody made any changes to the files, but the database stopped sorting by artist_id. The list shown is just the default order that mysql spits out if the SORT BY clause is omitted (as if I clicked 'browse' in phpMyAdmin). Right. All databases do exactly the same thing. There are no guarantees about sorting unless you tell it exactly how to sort. The SQL works in phpMyAdmin, and the site uses PEAR::DB. We patched the server last week, but the problem showed up this morning. What sql? With an order by? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
I got 5 or 6 users... and a respectable computer... On Fri, 02 Mar 2007 22:48:52 -0800, Micah Stevens wrote Reloading the grant tables should happen almost immediately unless you have an extremely large set of users, very little memory, or a very slow computer. On 03/02/2007 06:17 PM, bedul wrote: - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: Roberto F Tavares Neto [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Saturday, March 03, 2007 3:17 AM Subject: Re: [PHP-DB] Mysql autentication problem Strange. If you look at the users table, is there a password hash in the password field? i think this must be reload?? not just flush?? it happen to me.. or perhaps need some times to realy reload?? Roberto F Tavares Neto wrote: Micah: I did create the database. Then, I use the: GRANT ALL PRIVILEGES ON db.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' to do the 2 and 3 steps. FLUSH PRIVILEGES to do the step 4. But the step 5 really does not work... only on the shell or when I remove the password from the user... Roberto Micah Stevens escreveu: Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Roberto F Tavares Neto Prof. Assistente - Departamento de Engenharia de Produção Universidade Federal de São Carlos - UFSCAR -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
I did the reload, but with no effect... On Sat, 3 Mar 2007 09:17:36 +0700, bedul wrote - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: Roberto F Tavares Neto [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Saturday, March 03, 2007 3:17 AM Subject: Re: [PHP-DB] Mysql autentication problem Strange. If you look at the users table, is there a password hash in the password field? i think this must be reload?? not just flush?? it happen to me.. or perhaps need some times to realy reload?? Roberto F Tavares Neto wrote: Micah: I did create the database. Then, I use the: GRANT ALL PRIVILEGES ON db.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' to do the 2 and 3 steps. FLUSH PRIVILEGES to do the step 4. But the step 5 really does not work... only on the shell or when I remove the password from the user... Roberto Micah Stevens escreveu: Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- 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 -- Roberto F Tavares Neto Prof. Assistente - Departamento de Engenharia de Produção Universidade Federal de São Carlos - UFSCAR -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
In mysql 4.1, they changed the hash format of the passwords. If you stored the password from a 3.x client, and area accessing with a 4.x or 5.x client, or the other way around, you might have similar problems. Your command line client is likely the same version as the server, so I'm guessing that your php might be older. However, this situation is usually accompanied by a specific error message referencing the client version. Do you have that? -Micah On 03/02/2007 11:35 PM, bedul wrote: now you mention it. i hope the same problem will solve. i use in windows (own pc)..not in real-life server.. thx for your repair on my missuderstanding - Original Message - *From:* Micah Stevens mailto:[EMAIL PROTECTED] *To:* bedul mailto:[EMAIL PROTECTED] *Cc:* php-db@lists.php.net mailto:php-db@lists.php.net *Sent:* Saturday, March 03, 2007 1:48 PM *Subject:* Re: [PHP-DB] Mysql autentication problem Reloading the grant tables should happen almost immediately unless you have an extremely large set of users, very little memory, or a very slow computer. On 03/02/2007 06:17 PM, bedul wrote: - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: Roberto F Tavares Neto [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Saturday, March 03, 2007 3:17 AM Subject: Re: [PHP-DB] Mysql autentication problem Strange. If you look at the users table, is there a password hash in the password field? i think this must be reload?? not just flush?? it happen to me.. or perhaps need some times to realy reload?? Roberto F Tavares Neto wrote: Micah: I did create the database. Then, I use the: GRANT ALL PRIVILEGES ON db.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' to do the 2 and 3 steps. FLUSH PRIVILEGES to do the step 4. But the step 5 really does not work... only on the shell or when I remove the password from the user... Roberto Micah Stevens escreveu: Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Mysql autentication problem
Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
Hi, So it works from PHP w/o a password, but with a password does not? What are you using for a password? I don't mean post it here, but are there any 'funny' characters in it? I've had trouble because of that before, on other systems. Try a dumb, obviously OK password, like 'apples' and see if that works. Increase the password complexity from there... Ted On 3/2/07, Roberto F Tavares Neto [EMAIL PROTECTED] wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
Ted, I try the classic abc and had the same problem... Roberto Ted Fines escreveu: Hi, So it works from PHP w/o a password, but with a password does not? What are you using for a password? I don't mean post it here, but are there any 'funny' characters in it? I've had trouble because of that before, on other systems. Try a dumb, obviously OK password, like 'apples' and see if that works. Increase the password complexity from there... Ted On 3/2/07, Roberto F Tavares Neto [EMAIL PROTECTED] wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Roberto Fernandes Tavares Neto Prof. Assistente Departamento de Engenharia de Produção Universidade Federal de São Carlos [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] tel +55 16 3351-9240 http://www.dep.ufscar.br/docentes_desc.php?uid=165 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
Hi, When you're connecting from your PHP script, are you connecting from the same machine that the db is on, or a different one? If a different one, you'll need to do grant commands like: grant all privileges on mydbname.* to 'mydbuser'@'myhostname.mycompany.net' identified by 'mypassword'; Ted On 3/2/07, Roberto F Tavares Neto [EMAIL PROTECTED] wrote: Ted, I try the classic abc and had the same problem... Roberto Ted Fines escreveu: Hi, So it works from PHP w/o a password, but with a password does not? What are you using for a password? I don't mean post it here, but are there any 'funny' characters in it? I've had trouble because of that before, on other systems. Try a dumb, obviously OK password, like 'apples' and see if that works. Increase the password complexity from there... Ted On 3/2/07, Roberto F Tavares Neto [EMAIL PROTECTED] wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Roberto Fernandes Tavares Neto Prof. Assistente Departamento de Engenharia de Produção Universidade Federal de São Carlos [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] tel +55 16 3351-9240 http://www.dep.ufscar.br/docentes_desc.php?uid=165
RE: [PHP-DB] Mysql autentication problem
have you tried to 'flush privileges' to get the server to recognize the user account. Another option might be to open the mysqld from the command line and use the GRANT statement to create user without the gui. bastien From: Roberto F Tavares Neto [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Mysql autentication problem Date: Fri, 02 Mar 2007 10:05:54 -0300 Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Free Alerts : Be smart - let your information find you ! http://alerts.live.com/Alerts/Default.aspx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
I tried both already. It does not work. Roberto Bastien Koert escreveu: have you tried to 'flush privileges' to get the server to recognize the user account. Another option might be to open the mysqld from the command line and use the GRANT statement to create user without the gui. bastien From: Roberto F Tavares Neto [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Mysql autentication problem Date: Fri, 02 Mar 2007 10:05:54 -0300 Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Free Alerts : Be smart - let your information find you ! http://alerts.live.com/Alerts/Default.aspx -- Roberto Fernandes Tavares Neto Prof. Assistente Departamento de Engenharia de Produção Universidade Federal de São Carlos [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] tel +55 16 3351-9240 http://www.dep.ufscar.br/docentes_desc.php?uid=165 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
Ted, The [EMAIL PROTECTED] does the same effect as 'mydbuser'@'myhostname.mycompany.net' , right? Btw, for web apps, I got another user, @localhost only. This user is working... I thought it was a problem on the GUI, so I tried a [EMAIL PROTECTED] from mysql command line. It fails too... Tks! Roberto Ted Fines escreveu: Hi, When you're connecting from your PHP script, are you connecting from the same machine that the db is on, or a different one? If a different one, you'll need to do grant commands like: grant all privileges on mydbname.* to 'mydbuser'@'myhostname.mycompany.net' identified by 'mypassword'; Ted On 3/2/07, Roberto F Tavares Neto [EMAIL PROTECTED] wrote: Ted, I try the classic abc and had the same problem... Roberto Ted Fines escreveu: Hi, So it works from PHP w/o a password, but with a password does not? What are you using for a password? I don't mean post it here, but are there any 'funny' characters in it? I've had trouble because of that before, on other systems. Try a dumb, obviously OK password, like 'apples' and see if that works. Increase the password complexity from there... Ted On 3/2/07, Roberto F Tavares Neto [EMAIL PROTECTED] wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Roberto Fernandes Tavares Neto Prof. Assistente Departamento de Engenharia de Produção Universidade Federal de São Carlos [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] tel +55 16 3351-9240 http://www.dep.ufscar.br/docentes_desc.php?uid=165 -- Roberto Fernandes Tavares Neto Prof. Assistente Departamento de Engenharia de Produção Universidade Federal de São Carlos [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] tel +55 16 3351-9240 http://www.dep.ufscar.br/docentes_desc.php?uid=165 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
Micah: I did create the database. Then, I use the: GRANT ALL PRIVILEGES ON db.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' to do the 2 and 3 steps. FLUSH PRIVILEGES to do the step 4. But the step 5 really does not work... only on the shell or when I remove the password from the user... Roberto Micah Stevens escreveu: Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- Roberto Fernandes Tavares Neto Prof. Assistente Departamento de Engenharia de Produção Universidade Federal de São Carlos [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] tel +55 16 3351-9240 http://www.dep.ufscar.br/docentes_desc.php?uid=165 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
Strange. If you look at the users table, is there a password hash in the password field? Roberto F Tavares Neto wrote: Micah: I did create the database. Then, I use the: GRANT ALL PRIVILEGES ON db.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' to do the 2 and 3 steps. FLUSH PRIVILEGES to do the step 4. But the step 5 really does not work... only on the shell or when I remove the password from the user... Roberto Micah Stevens escreveu: Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
- Original Message - From: Micah Stevens [EMAIL PROTECTED] To: Roberto F Tavares Neto [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Saturday, March 03, 2007 3:17 AM Subject: Re: [PHP-DB] Mysql autentication problem Strange. If you look at the users table, is there a password hash in the password field? i think this must be reload?? not just flush?? it happen to me.. or perhaps need some times to realy reload?? Roberto F Tavares Neto wrote: Micah: I did create the database. Then, I use the: GRANT ALL PRIVILEGES ON db.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' to do the 2 and 3 steps. FLUSH PRIVILEGES to do the step 4. But the step 5 really does not work... only on the shell or when I remove the password from the user... Roberto Micah Stevens escreveu: Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- 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] Mysql autentication problem
Reloading the grant tables should happen almost immediately unless you have an extremely large set of users, very little memory, or a very slow computer. On 03/02/2007 06:17 PM, bedul wrote: - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: Roberto F Tavares Neto [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Saturday, March 03, 2007 3:17 AM Subject: Re: [PHP-DB] Mysql autentication problem Strange. If you look at the users table, is there a password hash in the password field? i think this must be reload?? not just flush?? it happen to me.. or perhaps need some times to realy reload?? Roberto F Tavares Neto wrote: Micah: I did create the database. Then, I use the: GRANT ALL PRIVILEGES ON db.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' to do the 2 and 3 steps. FLUSH PRIVILEGES to do the step 4. But the step 5 really does not work... only on the shell or when I remove the password from the user... Roberto Micah Stevens escreveu: Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql autentication problem
now you mention it. i hope the same problem will solve. i use in windows (own pc)..not in real-life server.. thx for your repair on my missuderstanding - Original Message - From: Micah Stevens To: bedul Cc: php-db@lists.php.net Sent: Saturday, March 03, 2007 1:48 PM Subject: Re: [PHP-DB] Mysql autentication problem Reloading the grant tables should happen almost immediately unless you have an extremely large set of users, very little memory, or a very slow computer. On 03/02/2007 06:17 PM, bedul wrote: - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: Roberto F Tavares Neto [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Saturday, March 03, 2007 3:17 AM Subject: Re: [PHP-DB] Mysql autentication problem Strange. If you look at the users table, is there a password hash in the password field? i think this must be reload?? not just flush?? it happen to me.. or perhaps need some times to realy reload?? Roberto F Tavares Neto wrote: Micah: I did create the database. Then, I use the: GRANT ALL PRIVILEGES ON db.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' to do the 2 and 3 steps. FLUSH PRIVILEGES to do the step 4. But the step 5 really does not work... only on the shell or when I remove the password from the user... Roberto Micah Stevens escreveu: Did you give the user permissions to use the database in question? Here's my sequence of actions: . 1) Create DB if it doesn't exist 2) Create the user w/password 3) Give the user permission to use the database. 4) Flush privileges to update the server. 5) login and enjoy. -Micah Roberto F Tavares Neto wrote: Hello, I'm trying to do a very simple thing: create a database and a user to use it. So, initially, I use the web interface phpmyadmin. Logged as root, I created the database, and the user with some password. But, I could not login using phpmyadmin. Either any php-based system could connect to the BD. So, I make sure that I got the [EMAIL PROTECTED] and [EMAIL PROTECTED] created, with permissions and same password. Nothing. But I *can* log on mysql shell. One more info: when the user is set without password, it works fine on PHP. Does anyone can give me a clue of what is happening? Thanks! Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MYSQL REGEXP question
No, it shouldn't because there are only two B's, one is followed by a 'v' (one of your exceptions) and the other is at the end of the line, right? -Micah Mike van Hoof wrote: That is it almost :) SELECT 'oer bv b' REGEXP 'b[^v]$|b[^v]\s?'; gives me 0 as a result, while it shoud give me 1, because there is a b in there which is not bv. Mike Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat Micah Stevens schreef: Your code states: Match: one of the following letters: -,b,|,^,b negative look ahead one of these: -,v,$,|,v Which isn't what you're looking for. Remember the negating '^' only works at the start of a [] list. And '$' only means line-end if it's outside [], inside it stands for the '$' character. I think this would work better: b[^v]$|b[^v]\s? HTH, -Micah [EMAIL PROTECTED] wrote: Hello, i am try to make a regular expression work, but keep getting an error message does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike AdmID:FB05127CFE0569CA76887DED108E0F2F ** IMPORTANT NOTICE This communication is for the exclusive use of the intended recipient(s) named above. If you receive this communication in error, you should notify the sender by e-mail or by telephone (+44) 191 224 4461, delete it and destroy any copies of it. This communication may contain confidential information and material protected by copyright, design right or other intellectual property rights which are and shall remain the property of Piranha Studios Limited. Any form of distribution, copying or other unauthorised use of this communication or the information in it is strictly prohibited. Piranha Studios Limited asserts its rights in this communication and the information in it and reserves the right to take action against anyone who misuses it or the information in it. Piranha Studios Limited cannot accept any liability sustained as a result of software viruses and would recommend that you carry out your own virus checks before opening any attachment. GWAVAsig -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MYSQL REGEXP question
Hello, i am try to make a regular expression work, but keep getting an error message does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ** IMPORTANT NOTICE This communication is for the exclusive use of the intended recipient(s) named above. If you receive this communication in error, you should notify the sender by e-mail or by telephone (+44) 191 224 4461, delete it and destroy any copies of it. This communication may contain confidential information and material protected by copyright, design right or other intellectual property rights which are and shall remain the property of Piranha Studios Limited. Any form of distribution, copying or other unauthorised use of this communication or the information in it is strictly prohibited. Piranha Studios Limited asserts its rights in this communication and the information in it and reserves the right to take action against anyone who misuses it or the information in it. Piranha Studios Limited cannot accept any liability sustained as a result of software viruses and would recommend that you carry out your own virus checks before opening any attachment. GWAVAsigAdmID:FB05127CFE0569CA76887DED108E0F2F ** IMPORTANT NOTICE This communication is for the exclusive use of the intended recipient(s) named above. If you receive this communication in error, you should notify the sender by e-mail or by telephone (+44) 191 224 4461, delete it and destroy any copies of it. This communication may contain confidential information and material protected by copyright, design right or other intellectual property rights which are and shall remain the property of Piranha Studios Limited. Any form of distribution, copying or other unauthorised use of this communication or the information in it is strictly prohibited. Piranha Studios Limited asserts its rights in this communication and the information in it and reserves the right to take action against anyone who misuses it or the information in it. Piranha Studios Limited cannot accept any liability sustained as a result of software viruses and would recommend that you carry out your own virus checks before opening any attachment. GWAVAsig -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MYSQL REGEXP question
Your code states: Match: one of the following letters: -,b,|,^,b negative look ahead one of these: -,v,$,|,v Which isn't what you're looking for. Remember the negating '^' only works at the start of a [] list. And '$' only means line-end if it's outside [], inside it stands for the '$' character. I think this would work better: b[^v]$|b[^v]\s? HTH, -Micah [EMAIL PROTECTED] wrote: Hello, i am try to make a regular expression work, but keep getting an error message does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike AdmID:FB05127CFE0569CA76887DED108E0F2F ** IMPORTANT NOTICE This communication is for the exclusive use of the intended recipient(s) named above. If you receive this communication in error, you should notify the sender by e-mail or by telephone (+44) 191 224 4461, delete it and destroy any copies of it. This communication may contain confidential information and material protected by copyright, design right or other intellectual property rights which are and shall remain the property of Piranha Studios Limited. Any form of distribution, copying or other unauthorised use of this communication or the information in it is strictly prohibited. Piranha Studios Limited asserts its rights in this communication and the information in it and reserves the right to take action against anyone who misuses it or the information in it. Piranha Studios Limited cannot accept any liability sustained as a result of software viruses and would recommend that you carry out your own virus checks before opening any attachment. GWAVAsig
Re: [PHP-DB] mysql rereading result set (fetch_assoc)
christine wrote: Hi, I would ask which way is more efficient and save time? Save each row to array or mysql_data_seek(0) ? Probably mysql_data_seek(0). That just resets the mysql pointer back to the start and so it doesn't re-run the query or anything like that and doesn't take up any extra memory. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql rereading result set (fetch_assoc)
Hi Hi, I would ask which way is more efficient and save time? Save each row to array or mysql_data_seek(0) ? That totally depends on which resources are more valuable to you. The array will likely use more memory but be faster to process. While mysql_data_seek(0) would probably use no additional memory but be slower. It also might require you to duplicate code. Niel -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MYSQL REGEXP question
Hello, i am try to make a regular expression work, but keep getting an error message does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql rereading result set (fetch_assoc)
I'm running thru a result set using fetch_assoc. Now I want to run through it again. How? TIA, Larry Woods -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL SQL Query Help
I have a table: id fkid foobar 1 1 345yellow 2 1 34 red 3 2 3459 green 4 2 345brown I want to select the largest value of foo for a unique fkid, and return bar, the results ordered by bar. In this case, 345 is the largest value of foo for fkid 1, and 3459 is the largest for fkid 2. green comes before yellow. My desired result set would be: fkid foobar 2 3459 green 1 345yellow How would I write that in SQL? fkid and foo are ints, bar is a varchar. Beckman --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL SQL Query Help
Hi Try: SELECT fkid, MAX(foo), bar FROM table GROUP BY fkid ORDER BY bar DESC Niel -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL SQL Query Help
select max(bar) from mytable where unique fkid order by bar asc as far as I remember!! -Original Message- From: Peter Beckman [mailto:[EMAIL PROTECTED] Sent: Lunes, 13 de Noviembre de 2006 04:59 p.m. To: PHP-DB Mailing List Subject: [PHP-DB] MySQL SQL Query Help I have a table: id fkid foobar 1 1 345yellow 2 1 34 red 3 2 3459 green 4 2 345brown I want to select the largest value of foo for a unique fkid, and return bar, the results ordered by bar. In this case, 345 is the largest value of foo for fkid 1, and 3459 is the largest for fkid 2. green comes before yellow. My desired result set would be: fkid foobar 2 3459 green 1 345yellow How would I write that in SQL? fkid and foo are ints, bar is a varchar. Beckman --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos. This message is for the sole use of the person or entity to whom it is being sent. Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law. If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited. If you received this document by mistake please notify immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL SQL Query Help
Actually, that should not work, it should give you an error. This should work: SELECT `fkid`,max(`foo`) as foo,`bar` FROM `test2` GROUP BY `fkid` ORDER BY `bar` ASC Miguel Guirao wrote: select max(bar) from mytable where unique fkid order by bar asc as far as I remember!! -Original Message- From: Peter Beckman [mailto:[EMAIL PROTECTED] Sent: Lunes, 13 de Noviembre de 2006 04:59 p.m. To: PHP-DB Mailing List Subject: [PHP-DB] MySQL SQL Query Help I have a table: id fkid foobar 1 1 345yellow 2 1 34 red 3 2 3459 green 4 2 345brown I want to select the largest value of foo for a unique fkid, and return bar, the results ordered by bar. In this case, 345 is the largest value of foo for fkid 1, and 3459 is the largest for fkid 2. green comes before yellow. My desired result set would be: fkid foobar 2 3459 green 1 345yellow How would I write that in SQL? fkid and foo are ints, bar is a varchar. Beckman --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL SQL Query Help
On Mon, 13 Nov 2006, [EMAIL PROTECTED] wrote: Actually, that should not work, it should give you an error. This should work: SELECT `fkid`,max(`foo`) as foo,`bar` FROM `test2` GROUP BY `fkid` ORDER BY `bar` ASC Yes, but if the data is in a different order that fails and doesn't maintain row order: mysql create temporary table test2 (id tinyint,fkid tinyint, foo smallint, bar varchar(20)); mysql insert into test2 values (1,1,34,'red'), (2,1,345,'yellow'), (3,2,345,'brown'), (4,2,3459,'green'); mysql select * from test2; +--+--+--++ | id | fkid | foo | bar| +--+--+--++ |1 |1 | 34 | red| |2 |1 | 345 | yellow | |3 |2 | 345 | brown | |4 |2 | 3459 | green | +--+--+--++ mysql SELECT `fkid`,max(`foo`) as foo,`bar` FROM `test2` GROUP BY `fkid` ORDER BY `bar` ASC; +--+--+---+ | fkid | foo | bar | +--+--+---+ |2 | 3459 | brown | |1 | 345 | red | +--+--+---+ 2 rows in set (0.00 sec) Notice how 3459 is supposed to be green but reports brown, and 345 should be yellow but reports red? Any other solutions that maintain row integrity? Beckman Miguel Guirao wrote: select max(bar) from mytable where unique fkid order by bar asc as far as I remember!! -Original Message- From: Peter Beckman [mailto:[EMAIL PROTECTED] Sent: Lunes, 13 de Noviembre de 2006 04:59 p.m. To: PHP-DB Mailing List Subject: [PHP-DB] MySQL SQL Query Help I have a table: id fkid foobar 1 1 345yellow 2 1 34 red 3 2 3459 green 4 2 345brown I want to select the largest value of foo for a unique fkid, and return bar, the results ordered by bar. In this case, 345 is the largest value of foo for fkid 1, and 3459 is the largest for fkid 2. green comes before yellow. My desired result set would be: fkid foobar 2 3459 green 1 345yellow How would I write that in SQL? fkid and foo are ints, bar is a varchar. Beckman --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL SQL Query Help
Peter Beckman wrote: On Mon, 13 Nov 2006, [EMAIL PROTECTED] wrote: Actually, that should not work, it should give you an error. This should work: SELECT `fkid`,max(`foo`) as foo,`bar` FROM `test2` GROUP BY `fkid` ORDER BY `bar` ASC Yes, but if the data is in a different order that fails and doesn't maintain row order: mysql create temporary table test2 (id tinyint,fkid tinyint, foo smallint, bar varchar(20)); mysql insert into test2 values (1,1,34,'red'), (2,1,345,'yellow'), (3,2,345,'brown'), (4,2,3459,'green'); mysql select * from test2; +--+--+--++ | id | fkid | foo | bar| +--+--+--++ |1 |1 | 34 | red| |2 |1 | 345 | yellow | |3 |2 | 345 | brown | |4 |2 | 3459 | green | +--+--+--++ mysql SELECT `fkid`,max(`foo`) as foo,`bar` FROM `test2` GROUP BY `fkid` ORDER BY `bar` ASC; +--+--+---+ | fkid | foo | bar | +--+--+---+ |2 | 3459 | brown | |1 | 345 | red | +--+--+---+ 2 rows in set (0.00 sec) Notice how 3459 is supposed to be green but reports brown, and 345 should be yellow but reports red? Any other solutions that maintain row integrity? You might have to go a subquery. (I'm not great at subqueries so there would have to be a better way to write this anyway). This works in postgresql: select fkid, foo, bar from test2 t2 where (select max(foo) from test2 t1 where t1.fkid=t2.fkid)=foo; but mysql won't let you reference the same table inside outside: mysql select fkid, foo, bar from test2 t2 where (select max(foo) from test2 t1 where t1.fkid=t2.fkid)=foo; ERROR 1137 (HY000): Can't reopen table: 't2' http://dev.mysql.com/doc/refman/5.0/en/subqueries.html The mysql mailing list might have better ideas.. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql databases
sorry, i meant that i wanted to remove the oldest entry and put in a new entry with UPDATE or INSERT. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql databases
whynot keep all and just sort/limit them to get the data you want? bastien From: bob plano [EMAIL PROTECTED] To: php-db@lists.php.net Subject: Re: [PHP-DB] mysql databases Date: Sat, 14 Oct 2006 12:47:38 -0500 sorry, i meant that i wanted to remove the oldest entry and put in a new entry with UPDATE or INSERT. -- 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] mysql databases
how would you add something new into a table and take out something old? example: (1) 1st something (2) 2nd something (3) 3rd something (4) 4th something and then you add in something new so then the table would look like (1) new something (2) 1st something (3) 2nd something (4) 3rd something thanks in advance -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql databases
Hi Bob Your question isn't very clear. Do you want to remove the oldest entry, newest, or is there some other criteria to diceide? Niel -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql databases
He means that he wants to use REPLACE and take out the old entry and update it with a new one. Although, you would probably use UPDATE instead. -- Dave W
Re: [PHP-DB] mysql databases
Hi He means that he wants to use REPLACE and take out the old entry and update it with a new one. Although, you would probably use UPDATE instead. Hmmm... Thought this was DB list, not mind-readers. I would also *guess* that would be the intention, but his example seems to remove the newest entry, not the oldest, hence I stopped guessing and asked. Niel -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql ORDER BY problems
Ok, here's what i got in my mysql db. I got a table listed with numbers as follows 1 2 3 4 5 6 7 10 11 12 13 14 15 16 17 18 19 20 21 22 25 These numbers I can display fine. I'm using .. $query=SELECT * FROM db ORDER BY numbers ASC; Right now it displays it as 1 10 11 12 . 2 22 23 25 3 4 5 6 7 Is there a way with my mysql query so that I can list the numbers in correct order? Any help is appricated. - Rob
RE: [PHP-DB] mysql ORDER BY problems
if you have these as strings, I would recommend a column data type conversion to int(or other numeric as the case may be)failing that I would use the CAST command to convert the data to numerics see here http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html to use select cast(fieldname as integer) from table where ... order by ... bastien From: Rob W. [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] mysql ORDER BY problems Date: Sun, 18 Jun 2006 17:19:03 -0500 Ok, here's what i got in my mysql db. I got a table listed with numbers as follows 1 2 3 4 5 6 7 10 11 12 13 14 15 16 17 18 19 20 21 22 25 These numbers I can display fine. I'm using .. $query=SELECT * FROM db ORDER BY numbers ASC; Right now it displays it as 1 10 11 12 . 2 22 23 25 3 4 5 6 7 Is there a way with my mysql query so that I can list the numbers in correct order? Any help is appricated. - Rob -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL/PHP Left Join Question
Hello, all. I don't know if this is a php-mysql question or just a mysql, but here goes: I have a list of DVDs that my library loans out, and I'd like to allow people to add comments to each item. Since this list gets regenerated periodically (it's extracted from another system, the library catalog), there isn't a consistent ID in the dvd tables, so I'm using the call number (which will look like DVD 2324) as the key. Anyhow, I join the tables like this to get all the DVDs and all the comments associated with the DVDs: SELECT distinct dvds.title, dvds.publisher, dvds.publication_date, dvds.call_number, comment.id, comment.parent_id, comment.comment, comment.name FROM dvds LEFT JOIN comment ON dvds.call_number=comment.parent_id WHERE dvds.title LIKE 'A%' ORDER BY dvds.title With this, I'll get results like DVD 101A.I. This movie rocked DVD 101A.I. This Movie stunk DVD 102Adaptation . . . DVD 103After Hours . . . When I loop in PHP through the records, of course, I want just the one DVD with however many comments associated with it. Is it possible to do this (i.e., screen out DVD dupes) in MySQL, or do I have to do it in PHP? If this is a dumb question, my humblest apologies, and I'd be interested if there was a better way to handle this . . . . Andrew -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL/PHP Left Join Question
One thing I've done in situations like this is just load your returned DB data
into an array. Something like this would do:
$dvdarr[$result['call_number']]['Title'] = $result['title'];
$dvdarr[$result['call_number']]['Publisher'] = $result['publisher'];
$dvdarr[$result['call_number']]['Comments'][] = $result['comment'];
Put that in a loop of your results. Notice the [] on the comments. That'll
collect all your comments under the umbrella of 'Comments'.
Then when you go to do your output, you'd do something like this:
forach ($dvdarr as $callnumber = $dvddata) {
$title = $dvddata['Title'];
$publisher = $dvddata['Publisher'];
$comments = $dvddata['Comments'];
foreach ($comments as $comment) {
// do whatever
}
}
There are other ways to handle this.. this might be one of the easier ones (if
I'm understanding your problem correctly).
-TG
= = = Original message = = =
Hello, all. I don't know if this is a php-mysql question or just a
mysql, but here goes:
I have a list of DVDs that my library loans out, and I'd like to allow
people to add comments to each item. Since this list gets regenerated
periodically (it's extracted from another system, the library
catalog), there isn't a consistent ID in the dvd tables, so I'm using
the call number (which will look like DVD 2324) as the key. Anyhow, I
join the tables like this to get all the DVDs and all the comments
associated with the DVDs:
SELECT distinct dvds.title, dvds.publisher, dvds.publication_date,
dvds.call_number,
comment.id, comment.parent_id, comment.comment, comment.name
FROM dvds
LEFT JOIN comment
ON dvds.call_number=comment.parent_id
WHERE dvds.title LIKE 'A%'
ORDER BY dvds.title
With this, I'll get results like
DVD 101A.I. This movie rocked
DVD 101A.I. This Movie stunk
DVD 102Adaptation . . .
DVD 103After Hours . . .
When I loop in PHP through the records, of course, I want just the one
DVD with however many comments associated with it. Is it possible to
do this (i.e., screen out DVD dupes) in MySQL, or do I have to do it
in PHP?
If this is a dumb question, my humblest apologies, and I'd be
interested if there was a better way to handle this . . . .
Andrew
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL/PHP Left Join Question
I approach this by assigning a value to a variable and then comparing to see
if i need to write out a new title
$oldDVD = ;
while ($rows=mysql_fetch_array($result))
{
//decide whether to show the DVD title
if ($oldDVD != $rows['title'])
{
echo trtd.$rows['title']./td/tr;
$oldDVD = $rows['title'];
}
//echo out the rest of the data
echo trtd.$rows['comment']./td/tr;
}
hth
Bastien
From: Andrew Darby [EMAIL PROTECTED]
To: php-db@lists.php.net
Subject: [PHP-DB] MySQL/PHP Left Join Question
Date: Thu, 25 May 2006 11:28:56 -0400
Hello, all. I don't know if this is a php-mysql question or just a
mysql, but here goes:
I have a list of DVDs that my library loans out, and I'd like to allow
people to add comments to each item. Since this list gets regenerated
periodically (it's extracted from another system, the library
catalog), there isn't a consistent ID in the dvd tables, so I'm using
the call number (which will look like DVD 2324) as the key. Anyhow, I
join the tables like this to get all the DVDs and all the comments
associated with the DVDs:
SELECT distinct dvds.title, dvds.publisher, dvds.publication_date,
dvds.call_number,
comment.id, comment.parent_id, comment.comment, comment.name
FROM dvds
LEFT JOIN comment
ON dvds.call_number=comment.parent_id
WHERE dvds.title LIKE 'A%'
ORDER BY dvds.title
With this, I'll get results like
DVD 101A.I. This movie rocked
DVD 101A.I. This Movie stunk
DVD 102Adaptation . . .
DVD 103After Hours . . .
When I loop in PHP through the records, of course, I want just the one
DVD with however many comments associated with it. Is it possible to
do this (i.e., screen out DVD dupes) in MySQL, or do I have to do it
in PHP?
If this is a dumb question, my humblest apologies, and I'd be
interested if there was a better way to handle this . . . .
Andrew
--
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] MySQL/PHP Left Join Question
Some good examples of how to deal with this in PHP have already been given, especially the associative array solution. The question I have is with something like this, how do you weight out the pros/cons of code development versus speed? i.e. for code development breaking the logic up into 2 queries would yield a much more readable result, but speed wise what is better multiple queries with smaller more precise record sets, or having PHP parse through the result set to properly weed out duplicate titles based on the outer join applied? This is assuming that there is no SQL construct to solve this problem, which personally I know of none. -Brad Andrew Darby wrote: Hello, all. I don't know if this is a php-mysql question or just a mysql, but here goes: I have a list of DVDs that my library loans out, and I'd like to allow people to add comments to each item. Since this list gets regenerated periodically (it's extracted from another system, the library catalog), there isn't a consistent ID in the dvd tables, so I'm using the call number (which will look like DVD 2324) as the key. Anyhow, I join the tables like this to get all the DVDs and all the comments associated with the DVDs: SELECT distinct dvds.title, dvds.publisher, dvds.publication_date, dvds.call_number, comment.id, comment.parent_id, comment.comment, comment.name FROM dvds LEFT JOIN comment ON dvds.call_number=comment.parent_id WHERE dvds.title LIKE 'A%' ORDER BY dvds.title With this, I'll get results like DVD 101A.I. This movie rocked DVD 101A.I. This Movie stunk DVD 102Adaptation . . . DVD 103After Hours . . . When I loop in PHP through the records, of course, I want just the one DVD with however many comments associated with it. Is it possible to do this (i.e., screen out DVD dupes) in MySQL, or do I have to do it in PHP? If this is a dumb question, my humblest apologies, and I'd be interested if there was a better way to handle this . . . . Andrew -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL/PHP Left Join Question
Thanks to Bastien and TG for their suggestions. I'd been looking at it Bastien's way, but TG's is probably better suited to what I want to do. I'd forgotten about that possibility, cramming it all into an array . . . . Again, thanks for the help, Andrew p.s. I'd be curious about the relative speeds of these methods, too. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL/PHP Left Join Question
test both and let us know bastien From: Andrew Darby [EMAIL PROTECTED] To: php-db@lists.php.net Subject: Re: [PHP-DB] MySQL/PHP Left Join Question Date: Thu, 25 May 2006 12:33:15 -0400 Thanks to Bastien and TG for their suggestions. I'd been looking at it Bastien's way, but TG's is probably better suited to what I want to do. I'd forgotten about that possibility, cramming it all into an array . . . . Again, thanks for the help, Andrew p.s. I'd be curious about the relative speeds of these methods, too. -- 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] mysql searching with fulltext indexing
Hi Listies I have a question Is there any to query a mysql table to get it to return the field names that it currently has marked as Fulltext Indexes? I need to build a searching engine, there are 60 tables being searched, i have created the fulltext indexes on all tables required and i currently have the tables in an array now what i want to do is use a for loop to loop through each table find its releveant FTS Indexes and then based on the field name use it in a MATCH() AGAINST() query and then return all the results in xml? Anyone have any idea how i can query the tables in order to get it to return the indexes Thanks in advance (hoping you guys can help)
