How can avoid 2 selects and 2 while?
table_1:
product_id features .
1aaa
2bbb
3ccc
table_2 :
product_idname sizeprice
1 111 111
1 12 2 221
1 133 331
2 21 1 112
2 22 2222
3 31 1113
3 32 2223
3 33 3333
What i want is the next report for example the size =2
.
Report of products with size =2
Product features : aaa
Name 12Price 221
Product features : bbb
Name 22 Price 222
Product features : ccc
Name 32 Price 223
...
basically what i do now with php is :
...
$cliente=mysql_query(select * from table_1);
while($row = mysql_fetch_array($cliente))
{
printf(\nProduct features : %s\n,$row[features]);
$cliente2=mysql_query(select name,price from table_2 where
product_id='$row[product_id]' and size=2);
while($row2 = mysql_fetch_array($cliente2))
{
. print(Name %s --- Price %s\n,$row2[name],$row2[price],);
}
}
.
How can i do this without 2 select and whitout 2 while and the features be
printed just one time?
Thank you in advance
Jose Julian Buda
Re: How can avoid 2 selects and 2 while? SOLVED
- Original Message -
From: Brent Baisley brentt...@gmail.com
To: Jose Julian Buda jb...@noticiasargentinas.com
Sent: Thursday, February 12, 2009 1:21 PM
Subject: Re: How can avoid 2 selects and 2 while?
On Thu, Feb 12, 2009 at 10:43 AM, Jose Julian Buda
jb...@noticiasargentinas.com wrote:
table_1:
product_id features .
1aaa
2bbb
3ccc
table_2 :
product_idname sizeprice
1 111 111
1 12 2 221
1 133 331
2 21 1 112
2 22 2222
3 31 1113
3 32 2223
3 33 3333
What i want is the next report for example the size =2
.
Report of products with size =2
Product features : aaa
Name 12Price 221
Product features : bbb
Name 22 Price 222
Product features : ccc
Name 32 Price 223
...
basically what i do now with php is :
...
$cliente=mysql_query(select * from table_1);
while($row = mysql_fetch_array($cliente))
{
printf(\nProduct features : %s\n,$row[features]);
$cliente2=mysql_query(select name,price from table_2 where
product_id='$row[product_id]' and size=2);
while($row2 = mysql_fetch_array($cliente2))
{
. print(Name %s --- Price %s\n,$row2[name],$row2[price],);
}
}
.
How can i do this without 2 select and whitout 2 while and the features
be printed just one time?
Thank you in advance
Jose Julian Buda
Whenever you have a query in a loop, you know you are doing something
wrong. Your query will pull repeated values, but there is no way
around that. You'll then check if the repeated value changes in PHP.
$cliente = mysql_query(SELECT table_1.*, table_2.name, table_2.price
FROM table_2 JOIN table_1 ON table_2.product_id=table_1.product_id
WHERE table_2.size=2 ORDER BY table_1.product_id);
$prev_prod_id = -1;
while($row = mysql_fetch_array($cliente))
{
if ( $row['product_id']!=$prev_prod_id ) {
printf(\nProduct features : %s\n,$row[features]);
$prev_prod_id = $row['product_id'];
}
print(Name %s --- Price %s\n,$row[name],$row[price],);
}
Note I have not tested it, but you get the idea.
Brent Baisley
Brent , it work fine with just 1 select query
Thank you very much
Jose Julian Buda
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Install problem: log file issue
In the process of installing MySQL 5.1.31, I receive the following error when I attempt to start mysqld_safe: 090212 10:57:40 mysqld_safe Logging to '/var/log/mysqld.log'. 090212 10:57:40 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied 090212 10:57:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied My my.cnf file reads: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data general_log = 1 general_log_file = /app/mysql/log/ socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log I thought the general_log_file variable would define the location of the log file, yet mysqld is trying to write the log file to a different directory. Does anyone have any suggestions on how to fix this? Thanks! Colin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
Colin, hi, so I think your mysql account doesn't have access /var/log/ directory Permission. Yours, fire9 在 2009-2-13，上午1:02， csego...@gmail.com 写道： In the process of installing MySQL 5.1.31, I receive the following error when I attempt to start mysqld_safe: 090212 10:57:40 mysqld_safe Logging to '/var/log/mysqld.log'. 090212 10:57:40 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied 090212 10:57:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied My my.cnf file reads: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data general_log = 1 general_log_file = /app/mysql/log/ socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log I thought the general_log_file variable would define the location of the log file, yet mysqld is trying to write the log file to a different directory. Does anyone have any suggestions on how to fix this? Thanks! Colin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com
Re: Install problem: log file issue
That is intentional. I want to write the log to a non-standard location. Thanks! C fire9 wrote: Colin, hi, so I think your mysql account doesn't have access /var/log/ directory Permission. Yours, fire9 ÔÚ 2009-2-13£¬ÉÏÎç1:02£¬ csego...@gmail.com mailto:csego...@gmail.com дµÀ£º In the process of installing MySQL 5.1.31, I receive the following error when I attempt to start mysqld_safe: 090212 10:57:40 mysqld_safe Logging to '/var/log/mysqld.log'. 090212 10:57:40 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied 090212 10:57:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied My my.cnf file reads: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data general_log = 1 general_log_file = /app/mysql/log/ socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log I thought the general_log_file variable would define the location of the log file, yet mysqld is trying to write the log file to a different directory. Does anyone have any suggestions on how to fix this? Thanks! Colin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How can avoid 2 selects and 2 while? SOLVED
Jose-
preface the product_id column with table ID e.g.
table_1.product_id
table_2.product_id
or use the alias assigned to tableName
Saludos Cordiales desde EEUU
Martin
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business
of Sender. This transmission is of a confidential nature and Sender does not
endorse distribution to any party other than intended recipient. Sender does
not necessarily endorse content contained within this transmission.
From: jb...@noticiasargentinas.com
To: mysql@lists.mysql.com
Subject: Re: How can avoid 2 selects and 2 while? SOLVED
Date: Thu, 12 Feb 2009 13:45:13 -0300
- Original Message -
From: Brent Baisley brentt...@gmail.com
To: Jose Julian Buda jb...@noticiasargentinas.com
Sent: Thursday, February 12, 2009 1:21 PM
Subject: Re: How can avoid 2 selects and 2 while?
On Thu, Feb 12, 2009 at 10:43 AM, Jose Julian Buda
jb...@noticiasargentinas.com wrote:
table_1:
product_id features .
1aaa
2bbb
3ccc
table_2 :
product_idname sizeprice
1 111 111
1 12 2 221
1 133 331
2 21 1 112
2 22 2222
3 31 1113
3 32 2223
3 33 3333
What i want is the next report for example the size =2
.
Report of products with size =2
Product features : aaa
Name 12Price 221
Product features : bbb
Name 22 Price 222
Product features : ccc
Name 32 Price 223
...
basically what i do now with php is :
...
$cliente=mysql_query(select * from table_1);
while($row = mysql_fetch_array($cliente))
{
printf(\nProduct features : %s\n,$row[features]);
$cliente2=mysql_query(select name,price from table_2 where
product_id='$row[product_id]' and size=2);
while($row2 = mysql_fetch_array($cliente2))
{
. print(Name %s --- Price %s\n,$row2[name],$row2[price],);
}
}
.
How can i do this without 2 select and whitout 2 while and the features
be printed just one time?
Thank you in advance
Jose Julian Buda
Whenever you have a query in a loop, you know you are doing something
wrong. Your query will pull repeated values, but there is no way
around that. You'll then check if the repeated value changes in PHP.
$cliente = mysql_query(SELECT table_1.*, table_2.name, table_2.price
FROM table_2 JOIN table_1 ON table_2.product_id=table_1.product_id
WHERE table_2.size=2 ORDER BY table_1.product_id);
$prev_prod_id = -1;
while($row = mysql_fetch_array($cliente))
{
if ( $row['product_id']!=$prev_prod_id ) {
printf(\nProduct features : %s\n,$row[features]);
$prev_prod_id = $row['product_id'];
}
print(Name %s --- Price %s\n,$row[name],$row[price],);
}
Note I have not tested it, but you get the idea.
Brent Baisley
Brent , it work fine with just 1 select query
Thank you very much
Jose Julian Buda
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
_
Windows Live™: Keep your life in sync.
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
Re: Install problem: log file issue
Andy, Thanks for the suggestion. my_print_defaults mysqld indicates that it is reading my my.cnf. Furthermore, I have tried both the log and log-output variables in my.cnf with no success. Any other ideas? Thanks! C Andy Smith wrote: It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How can avoid 2 selects and 2 while?
I would do the following:
SELECT t1.features, t2.name, t2.size, t2.price
FROM table_2 as t2 LEFT JOIN table_1 as t1 ON t2.product_id = t1.product_id
Then in (psuedo) PHP:
$prev_features = ;
foreach($result as $row){
if ($row['features'] != $prev_features) {
echo ''Product features: . $row[''features'] ;
$prev_features = $row['features'];
}
echo $row info...
}
DH
On Thu, Feb 12, 2009 at 10:43 AM, Jose Julian Buda
jb...@noticiasargentinas.com wrote:
table_1:
product_id features .
1aaa
2bbb
3ccc
table_2 :
product_idname sizeprice
1 111 111
1 12 2 221
1 133 331
2 21 1 112
2 22 2222
3 31 1113
3 32 2223
3 33 3333
What i want is the next report for example the size =2
.
Report of products with size =2
Product features : aaa
Name 12Price 221
Product features : bbb
Name 22 Price 222
Product features : ccc
Name 32 Price 223
...
basically what i do now with php is :
...
$cliente=mysql_query(select * from table_1);
while($row = mysql_fetch_array($cliente))
{
printf(\nProduct features : %s\n,$row[features]);
$cliente2=mysql_query(select name,price from table_2 where
product_id='$row[product_id]' and size=2);
while($row2 = mysql_fetch_array($cliente2))
{
. print(Name %s --- Price %s\n,$row2[name],$row2[price],);
}
}
.
How can i do this without 2 select and whitout 2 while and the features
be printed just one time?
Thank you in advance
Jose Julian Buda
Re: Install problem: log file issue
I haven't looked into it in detail an don't have time right nw, but it is also hardcoded in mysqld_safe. I have seen a case where it had to be changed there to make it work. try that and please report back :) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Thu, Feb 12, 2009 at 6:39 PM, csego...@gmail.com csego...@gmail.com wrote: Andy, Thanks for the suggestion. my_print_defaults mysqld indicates that it is reading my my.cnf. Furthermore, I have tried both the log and log-output variables in my.cnf with no success. Any other ideas? Thanks! C Andy Smith wrote: It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
Andy, Michael, and Walter - thank you! Adding a [mysqld_safe] group to my.cnf gets me further but the start still fails. The good thing is that the failure is no longer due to the inability to write the log file. The [mysqld_safe] section of my.cnf reads: [mysqld_safe] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data #log = /app/mysql/log/mysqld.log log-bin = /app/mysql/log/mysql-bin.log log-output = FILE general_log = 1 general_log_file = /app/mysql/log/msyql_general.log socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log Luckily, I now have an error log which reads: 090212 18:24:04 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data InnoDB: Log scan progressed past the checkpoint lsn 0 39548 090212 18:24:04 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 46409 090212 18:24:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 090212 18:24:04 InnoDB: Started; log sequence number 0 46409 090212 18:24:04 [ERROR] /app/mysql/libexec/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2) 090212 18:24:04 [ERROR] Can't start server: can't create PID file: No such file or directory 090212 18:24:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended Now I need to figure out how to point PID file creation the appropriate directory. C Walter Heck wrote: I haven't looked into it in detail an don't have time right nw, but it is also hardcoded in mysqld_safe. I have seen a case where it had to be changed there to make it work. try that and please report back :) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Thu, Feb 12, 2009 at 6:39 PM, csego...@gmail.com csego...@gmail.com wrote: Andy, Thanks for the suggestion. my_print_defaults mysqld indicates that it is reading my my.cnf. Furthermore, I have tried both the log and log-output variables in my.cnf with no success. Any other ideas? Thanks! C Andy Smith wrote: It could be that your my.cnf isn't being read (permissions or in the wrong directory), or that you have multiple my.cnf files. Quoting csego...@gmail.com csego...@gmail.com: That is intentional. I want to write the log to a non-standard location. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select data from another Mysql Server?
I have two Mysql servers and I want to Read data from one Mysql server to another using stored procedure.
