Re: [PHP] mysql custom global defined variable
On Mar 13, 2013, at 10:35 AM, Kevin Peterson wrote:
In my database design, I tend to store some variable that is meant to be
acting as a ROLE or TYPE as SMALLINT. For example :
CREATE TABLE `house` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` smallint(11) NOT NULL,
)
And in php, I do
define('HOUSE_SMALL_TYPE', '0');
define('HOUSE_MEDIUM_TYPE', '1');
So in php, in SELECT queries I do :
$this-db-query(SELECT * FROM house
WHERE type=?;, HOUSE_SMALL_TYPE);
My questions are :
1. In the php part, is there is a better way to do this ?
2. In the mysql itself, does mysql also has global define functionality (like
the define in php) ? I also want to do kind of SELECT * FROM house WHERE type
= HOUSE_SMALL_TYPE in mysql query.
Question 1:
I see no possible improvements, you could however use an array with values
instead of constants, but that's rather a personal choice as I don't like
constants that much, unless you are on your own namespace.
My example implementation:
$houseTypes = array(
'house_small_type' = 0,
'house_medium_type' = 1,
etc.
);
Question 2:
You could use ENUM data type, but it has quite a few disadvantages:
1- Translation could be tricky to implement
2- DDL shouldn't be used for data!
3- Updating or deleting values can leave your old records in an inconsistent
state
You can also use SET to set variables, I've never used them but I think they
could work in your case:
http://dev.mysql.com/doc/refman/5.5/en/user-variables.html
Greetings.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql custom global defined variable
Am 13.03.13 10:35, schrieb Kevin Peterson:
In my database design, I tend to store some variable that is meant to be
acting as a ROLE or TYPE as SMALLINT. For example :
CREATE TABLE `house` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` smallint(11) NOT NULL,
)
And in php, I do
define('HOUSE_SMALL_TYPE', '0');
define('HOUSE_MEDIUM_TYPE', '1');
So in php, in SELECT queries I do :
$this-db-query(SELECT * FROM house
WHERE type=?;, HOUSE_SMALL_TYPE);
My questions are :
1. In the php part, is there is a better way to do this ?
I stopped using define in favor of somehow namespaced constants as
const in classes. But basically there is no difference.
Advantages of using constants (which ever) are code completion to avoid
spelling errors. I see no possible improvements here.
2. In the mysql itself, does mysql also has global define functionality (like
the define in php) ? I also want to do kind of SELECT * FROM house WHERE type
= HOUSE_SMALL_TYPE in mysql query.
Maybe this is what you are looking for?
http://forums.mysql.com/read.php?98,273432,273432
--
Marco Behnke
Dipl. Informatiker (FH), SAE Audio Engineer Diploma
Zend Certified Engineer PHP 5.3
Tel.: 0174 / 9722336
e-Mail: ma...@behnke.biz
Softwaretechnik Behnke
Heinrich-Heine-Str. 7D
21218 Seevetal
http://www.behnke.biz
signature.asc
Description: OpenPGP digital signature
Re: [PHP] MySQL table design
On 23 Mar 2012, at 00:10, Chris Stinemetz wrote: Is it possible to create a MySQL table with characters such as . and [] in the column headers? If so would you explain how? Try putting the column names in backticks (`). BUT... whatever the reason why you want to do that, IT'S WRONG. Seriously, don't do it. It will cause you more problems than you think it will solve, and I don't even know what problem you think it will solve. -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL table design
On 2012-03-22, at 8:10 PM, Chris Stinemetz chrisstinem...@gmail.com wrote: Hello List, Is it possible to create a MySQL table with characters such as . and [] in the column headers? If so would you explain how? Thank you, Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Periods are not allowed, nor other characters that are reflected in file paths. You can use the back tick to wrap the field names, but I really don't recommend it. If you ever need to port this to another DB, it might not be as forgiving Bastien -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL table design
On Thu, Mar 22, 2012 at 7:42 PM, Stuart Dallas stu...@3ft9.com wrote: On 23 Mar 2012, at 00:10, Chris Stinemetz wrote: Is it possible to create a MySQL table with characters such as . and [] in the column headers? If so would you explain how? Try putting the column names in backticks (`). BUT... whatever the reason why you want to do that, IT'S WRONG. Seriously, don't do it. It will cause you more problems than you think it will solve, and I don't even know what problem you think it will solve. I am just trying to preserve the source headers from the original data I want to import into the table. Thank you, Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL table design
Leave the past behind. You're moving forward. And - for whatever reason that they were used originally, you now have the opportunity to rid yourself of column names that must be a pia to type all the time. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
I have a members list witch I print out once a week,
I would like to make the list into two-column list, but I dont know where to
start looking to change the code?
here is the code
$result = mysql_query(SELECT * FROM customers ORDER BY LastName);
while($row = mysql_fetch_array($result))
{
echo $row['LastName'];
echo . $row['FirstName'];
echo . $row['CustomNo'];
echo br /;
}
example output:
Bond James Bond 007
Quagmire Glenn 101
Griffin Peter 102
etc
---
tom_a_sparks It's a nerdy thing I like to do
Please use ISO approved file formats excluding Office Open XML -
http://www.gnu.org/philosophy/no-word-attachments.html
Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
3 x (x)Ubuntu 10.04, Amiga A1200 WB 3.1, UAE AF 2006 Premium Edition, AF 2012
Plus Edition, Sam440 AOS 4.1.2, Roland DXY-1300 pen plotter, Cutok DC330
cutter/pen plotter
Wanted: RiscOS system, GEOS system (C64/C128), Atari ST, Apple Macintosh
(6502/68k/PPC only)
How do you mean? In your example, it's a 3-column list (surname,
forename, customer number) although you've added a 'cute' field to the
first entry for giggles. It's a simple thing to join the first two
fields, but what do you determine to be a single column?
--
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] mysql list to two-column list
--- On Tue, 20/3/12, Ashley Sheridan a...@ashleysheridan.co.uk wrote:
On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
I have a members list witch I print out once a week,
I would like to make the list into two-column list, but I dont know where
to start looking to change the code?
here is the code
$result = mysql_query(SELECT * FROM customers ORDER BY LastName);
while($row = mysql_fetch_array($result))
{
echo $row['LastName'];
echo . $row['FirstName'];
echo . $row['CustomNo'];
echo br /;
}
example output:
Bond James 007
Quagmire Glenn 101
Griffin Peter 102
etc
---
tom_a_sparks It's a nerdy thing I like to do
Please use ISO approved file formats excluding Office Open XML -
http://www.gnu.org/philosophy/no-word-attachments.html
Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
How do you mean?
my goal is to do something phonebook like
In your example, it's a 3-column list (surname,
forename, customer number) although you've added a 'cute' field to the first
entry for giggles. It's a simple thing to join the first two fields, but what
do you determine to be a single column?
--
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
On Mon, 2012-03-19 at 16:09 -0700, Tom Sparks wrote:
--- On Tue, 20/3/12, Ashley Sheridan a...@ashleysheridan.co.uk wrote:
On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
I have a members list witch I print out once a week,
I would like to make the list into two-column list, but I dont know where
to start looking to change the code?
here is the code
$result = mysql_query(SELECT * FROM customers ORDER BY LastName);
while($row = mysql_fetch_array($result))
{
echo $row['LastName'];
echo . $row['FirstName'];
echo . $row['CustomNo'];
echo br /;
}
example output:
Bond James 007
Quagmire Glenn 101
Griffin Peter 102
etc
---
tom_a_sparks It's a nerdy thing I like to do
Please use ISO approved file formats excluding Office Open XML -
http://www.gnu.org/philosophy/no-word-attachments.html
Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
How do you mean?
my goal is to do something phonebook like
In your example, it's a 3-column list (surname,
forename, customer number) although you've added a 'cute' field to the
first entry for giggles. It's a simple thing to join the first two fields,
but what do you determine to be a single column?
Your reply of 9 words adds no extra information. Rather than have us all
guess what you want, try and tell us specifically what it is that you
want.
--
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] mysql list to two-column list
my goal is to do something phonebook like Your reply of 9 words adds no extra information. Rather than have us all guess what you want, try and tell us specifically what it is that you want. Hi Tom I *think* what want to be asking is, what HTML do I change/add such that my data coming from the database will format on the page in the browser like a phone book (in columns)? If so, then: This is a PHP list where people mostly discuss PHP-specific things.. whereas it seem what you need is to learn some basic HTML which will allow you to format content on a page so it looks like a phone book. I would say: Forget your database data for a day. Start with seeing if you can just write some static HTML that looks like what you want (formatted like a phone book), and THEN alter your real PHP code so that it spits out the HTML that mimics what you mocked up. If you get stuck with the HTML part of this task, then consult a good HTML list. If you get stuck with something specific to PHP, then ask again about that, here. Good luck, -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
--- On Tue, 20/3/12, Ashley Sheridan a...@ashleysheridan.co.uk wrote:
From: Ashley Sheridan a...@ashleysheridan.co.uk
Subject: Re: [PHP] mysql list to two-column list
To: Tom Sparks tom_a_spa...@yahoo.com.au
Cc: php-general php-general@lists.php.net
Received: Tuesday, 20 March, 2012, 10:15 AM
On Mon, 2012-03-19 at 16:09 -0700, Tom Sparks wrote:
--- On Tue, 20/3/12, Ashley Sheridan a...@ashleysheridan.co.uk wrote:
On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
I have a members list witch I print out once a week,
I would like to make the list into two-column list, but I dont know where
to start looking to change the code?
here is the code
$result = mysql_query(SELECT * FROM customers ORDER BY LastName);
while($row = mysql_fetch_array($result))
{
echo $row['LastName'];
echo . $row['FirstName'];
echo . $row['CustomNo'];
echo br /;
}
example output:
Bond James 007
Quagmire Glenn 101
Griffin Peter 102
etc
---
tom_a_sparks It's a nerdy thing I like to do
Please use ISO approved file formats excluding Office Open XML -
http://www.gnu.org/philosophy/no-word-attachments.html
Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
How do you mean?
my goal is to do something phonebook like
In your example, it's a 3-column list (surname,
forename, customer number) although you've added a 'cute' field to the first
entry for giggles. It's a simple thing to join the first two fields, but what
do you determine to be a single column?
Your reply of 9 words adds no extra information. Rather than have us all guess
what you want, try and tell us specifically what it is that you want.
Cleveland Brown | Griffin family
Cleveland Brown, Jr. | Brian Griffin
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
please delete me
---
tom_a_sparks It's a nerdy thing I like to do
Please use ISO approved file formats excluding Office Open XML -
http://www.gnu.org/philosophy/no-word-attachments.html
Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
3 x (x)Ubuntu 10.04, Amiga A1200 WB 3.1, UAE AF 2006 Premium Edition, AF 2012
Plus Edition, Sam440 AOS 4.1.2, Roland DXY-1300 pen plotter, Cutok DC330
cutter/pen plotter
Wanted: RiscOS system, GEOS system (C64/C128), Atari ST, Apple Macintosh
(6502/68k/PPC only)
--- On Tue, 20/3/12, Tom Sparks tom_a_spa...@yahoo.com.au wrote:
From: Tom Sparks tom_a_spa...@yahoo.com.au
Subject: Re: [PHP] mysql list to two-column list
To: a...@ashleysheridan.co.uk
Cc: php-general php-general@lists.php.net
Received: Tuesday, 20 March, 2012, 10:41 AM
--- On Tue, 20/3/12, Ashley Sheridan
a...@ashleysheridan.co.uk
wrote:
From: Ashley Sheridan a...@ashleysheridan.co.uk
Subject: Re: [PHP] mysql list to two-column list
To: Tom Sparks tom_a_spa...@yahoo.com.au
Cc: php-general php-general@lists.php.net
Received: Tuesday, 20 March, 2012, 10:15 AM
On Mon, 2012-03-19 at 16:09 -0700, Tom Sparks wrote:
--- On Tue, 20/3/12, Ashley Sheridan a...@ashleysheridan.co.uk
wrote:
On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
I have a members list witch I print out once a
week,
I would like to make the list into two-column list,
but I dont know where to start looking to change the
code?
here is the code
$result = mysql_query(SELECT * FROM customers ORDER
BY LastName);
while($row = mysql_fetch_array($result))
{
echo $row['LastName'];
echo . $row['FirstName'];
echo . $row['CustomNo'];
echo br /;
}
example output:
Bond James 007
Quagmire Glenn 101
Griffin Peter 102
etc
---
tom_a_sparks It's a nerdy thing I like to do
Please use ISO approved file formats excluding
Office Open XML - http://www.gnu.org/philosophy/no-word-attachments.html
Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
How do you mean?
my goal is to do something phonebook like
In your example, it's a 3-column list (surname,
forename, customer number) although you've added a
'cute' field to the first entry for giggles. It's a
simple thing to join the first two fields, but what do
you determine to be a single column?
Your reply of 9 words adds no extra information. Rather than
have us all guess what you want, try and tell us
specifically what it is that you want.
Cleveland Brown | Griffin
family
Cleveland Brown, Jr. | Brian
Griffin
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
On 19 Mar 2012, at 22:43, Tom Sparks wrote:
I have a members list witch I print out once a week,
I would like to make the list into two-column list, but I dont know where to
start looking to change the code?
here is the code
$result = mysql_query(SELECT * FROM customers ORDER BY LastName);
while($row = mysql_fetch_array($result))
{
echo $row['LastName'];
echo . $row['FirstName'];
echo . $row['CustomNo'];
echo br /;
}
The following is untested so it may contain syntax errors, but I'm pretty sure
the logic is sound and I think it will give you what you want. You may want to
play with the cellpadding value to adjust the amount of space between the table
cells.
echo 'table cellspacing=0 cellpadding=5 border=0';
$column = 1;
while ($row = mysql_fetch_assoc($result))
{
if ($column == 1) {
echo 'tr'.PHP_EOL;
}
echo ' td'.$row['LastName'].' '.$row['FirstName'].'
'.$row['CustomNo'].'/td';
$column++;
if ($column 2) {
echo '/tr'.PHP_EOL;
$column = 1;
}
}
if ($column == 2) {
echo 'tdnbsp;/td'.PHP_EOL;
}
echo '/tr/table';
Assuming I'm right in my interpretation of what you want, the following needs
to be said... this is *very* basic HTML being rendered by *very* basic PHP. I
suggest you learn about basic HTML first, then learn basic PHP, then work out
how to use logic in PHP to build the HTML you want.
If you're unsure how the above works, start by viewing the source of the page
in your browser. If it's still not clear, run it in your head. Use two pieces
of paper, one to store the variables and the other to keep track of the output.
Go through each loop and for each line update the variables and update the
output for each echo statement.
If I got what you want wrong then I have absolutely no clue what you're after.
-Stuart
--
Stuart Dallas
3ft9 Ltd
http://3ft9.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL over TCP results on CLOSE_WAIT state in PHP 5.3.8
I have a MySQL server A, a server B with PHP 5.3.8 and a server C with PHP 5.3.3. I'm connecting to the MySQL server on A via TCP from B and C using an internal network. Server B and C use the same PHP application. There are also same PHP scripts that get data from the database and then calculate up to 30 minutes. I close all database connection before doing the calculation to save connections (and ports) using: $thread_id = mysqli_thread_id( $this-handle ); mysqli_kill( $this-handle, $thread_id ); mysqli_close( $this-handle ); During a review on our servers I discovered that server B has a lot of network connection in the state CLOSE_WAIT. Server C running the same PHP application has not. I see the difference that server B is using mysqlnd and server C not. serverB# netstat -an | grep 3306 tcp1 0 10.8.0.58:47455 10.8.0.1:3306 CLOSE_WAIT serverA# cat firewall Feb 17 16:21:49 www kernel: [6587053.325075] SFW2-OUT-ERROR IN= OUT=tun0 SRC=10.8.0.1 DST=10.8.0.58 LEN=40 TOS=0x00 PREC=0x00 TTL=64 ID=0 DF PROTO=TCP SPT=3306 DPT=47455 WINDOW=0 RES=0x00 RST URGP=0 Does anybody have an idea why this happens? How can I avoid this or investigate in this? Is this a know issue? Is the underlying OS for servers B C exactly the same? Down to kernel version (if Linux/Unix), NICs and driver version, configurations, etc.? It would help if specify OS type and kernel version. No, client B and C have different OS versions. You are right this can be a reason too. Do you have any hints how I can trace that down? Details client B (newer OS with CLOSE_WAIT problems): $ uname -a Linux www3 3.1.9-1.4-default #1 SMP Fri Jan 27 08:55:10 UTC 2012 (efb5ff4) x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using readline 6.2 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: rtl_nic/rtl8168e-2.fw bus-info: :04:00.0 supports-statistics: yes supports-test: no supports-eeprom-access: no supports-register-dump: yes Details client C: $ uname -a Linux www13 2.6.34.10-0.4-default #1 SMP 2011-10-19 22:16:41 +0200 x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.1.57, for suse-linux-gnu (x86_64) using readline 6.1 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: bus-info: :04:00.0 Server A: Linux www 3.1.9-1.4-default #1 SMP Fri Jan 27 08:55:10 UTC 2012 (efb5ff4) x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using readline 6.2 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: rtl_nic/rtl8168e-2.fw bus-info: :04:00.0 supports-statistics: yes supports-test: no supports-eeprom-access: no supports-register-dump: yes I also have set wait_timeout 45 in my.cnf on server A and mysqli.reconnect On on client B and C in php.ini. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL over TCP results on CLOSE_WAIT state in PHP 5.3.8
On Mon, Mar 5, 2012 at 7:33 AM, php-l...@dubistmeinheld.de wrote: I have a MySQL server A, a server B with PHP 5.3.8 and a server C with PHP 5.3.3. I'm connecting to the MySQL server on A via TCP from B and C using an internal network. Server B and C use the same PHP application. There are also same PHP scripts that get data from the database and then calculate up to 30 minutes. I close all database connection before doing the calculation to save connections (and ports) using: $thread_id = mysqli_thread_id( $this-handle ); mysqli_kill( $this-handle, $thread_id ); mysqli_close( $this-handle ); During a review on our servers I discovered that server B has a lot of network connection in the state CLOSE_WAIT. Server C running the same PHP application has not. I see the difference that server B is using mysqlnd and server C not. serverB# netstat -an | grep 3306 tcp 1 0 10.8.0.58:47455 10.8.0.1:3306 CLOSE_WAIT serverA# cat firewall Feb 17 16:21:49 www kernel: [6587053.325075] SFW2-OUT-ERROR IN= OUT=tun0 SRC=10.8.0.1 DST=10.8.0.58 LEN=40 TOS=0x00 PREC=0x00 TTL=64 ID=0 DF PROTO=TCP SPT=3306 DPT=47455 WINDOW=0 RES=0x00 RST URGP=0 Does anybody have an idea why this happens? How can I avoid this or investigate in this? Is this a know issue? Is the underlying OS for servers B C exactly the same? Down to kernel version (if Linux/Unix), NICs and driver version, configurations, etc.? It would help if specify OS type and kernel version. No, client B and C have different OS versions. You are right this can be a reason too. Do you have any hints how I can trace that down? Details client B (newer OS with CLOSE_WAIT problems): $ uname -a Linux www3 3.1.9-1.4-default #1 SMP Fri Jan 27 08:55:10 UTC 2012 (efb5ff4) x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using readline 6.2 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: rtl_nic/rtl8168e-2.fw bus-info: :04:00.0 supports-statistics: yes supports-test: no supports-eeprom-access: no supports-register-dump: yes Details client C: $ uname -a Linux www13 2.6.34.10-0.4-default #1 SMP 2011-10-19 22:16:41 +0200 x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.1.57, for suse-linux-gnu (x86_64) using readline 6.1 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: bus-info: :04:00.0 Server A: Linux www 3.1.9-1.4-default #1 SMP Fri Jan 27 08:55:10 UTC 2012 (efb5ff4) x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using readline 6.2 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: rtl_nic/rtl8168e-2.fw bus-info: :04:00.0 supports-statistics: yes supports-test: no supports-eeprom-access: no supports-register-dump: yes I also have set wait_timeout 45 in my.cnf on server A and mysqli.reconnect On on client B and C in php.ini. Troubleshooting that much variances will take a lot of time. Do you have root access? Was the OS installed via standard distribution? Did someone reconfigured and recompiled the kernel and system? I'd suggest you start compiling from source of the following: * MySQL client - same version (preferred) or newer than server (make sure config is the same on both boxes for PHP) * compile PHP thereafter Then you'll know if it's the OS related or not (ie: configuration, kernel version, etc.) Also, your kernel versions doesn't seem to be stable per [1]. You might want to check each flavor's distributor. Just out of curiosity, are both boxes (B C) are the same Linux flavor? Best regards, Tommy [1]http://www.kernel.org/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL over TCP results on CLOSE_WAIT state in PHP 5.3.8
On Mon, Feb 27, 2012 at 4:06 AM, php-l...@dubistmeinheld.de wrote: Hi, I have a MySQL server A, a server B with PHP 5.3.8 and a server C with PHP 5.3.3. I'm connecting to the MySQL server on A via TCP from B and C using an internal network. Server B and C use the same PHP application. There are also same PHP scripts that get data from the database and then calculate up to 30 minutes. I close all database connection before doing the calculation to save connections (and ports) using: $thread_id = mysqli_thread_id( $this-handle ); mysqli_kill( $this-handle, $thread_id ); mysqli_close( $this-handle ); During a review on our servers I discovered that server B has a lot of network connection in the state CLOSE_WAIT. Server C running the same PHP application has not. I see the difference that server B is using mysqlnd and server C not. serverB# netstat -an | grep 3306 tcp 1 0 10.8.0.58:47455 10.8.0.1:3306 CLOSE_WAIT serverA# cat firewall Feb 17 16:21:49 www kernel: [6587053.325075] SFW2-OUT-ERROR IN= OUT=tun0 SRC=10.8.0.1 DST=10.8.0.58 LEN=40 TOS=0x00 PREC=0x00 TTL=64 ID=0 DF PROTO=TCP SPT=3306 DPT=47455 WINDOW=0 RES=0x00 RST URGP=0 Does anybody have an idea why this happens? How can I avoid this or investigate in this? Is this a know issue? Is the underlying OS for servers B C exactly the same? Down to kernel version (if Linux/Unix), NICs and driver version, configurations, etc.? It would help if specify OS type and kernel version. Best regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql adapter and DAL
hi, If you are building your own dal I guess you would have to build your own adapter, simply use mysqli function and wrap them in a class. On 17 September 2011 12:24, shahrzad khorrami shahrzad.khorr...@gmail.comwrote: hi all, I'm looking for a mysql adapter for create my dal.. where can I find a good one? and have you ever written a dal with a mysql adapter? in my dal I want to pass parameters to sql sting, for example like following: $db-query($sql, array($name, $family)) thanks, Shahrzad Khorrami
Fwd: Re: [PHP] mysql adapter and DAL
Sorry, I clicked the 'reply' button instead of reply to all. Original Message Subject: Re: [PHP] mysql adapter and DAL Date: Sat, 17 Sep 2011 19:07:48 +0530 From: Nilesh Govindarajan cont...@nileshgr.com To: shahrzad khorrami shahrzad.khorr...@gmail.com On Sat 17 Sep 2011 04:54:13 PM IST, shahrzad khorrami wrote: hi all, I'm looking for a mysql adapter for create my dal.. where can I find a good one? and have you ever written a dal with a mysql adapter? in my dal I want to pass parameters to sql sting, for example like following: $db-query($sql, array($name, $family)) thanks, Shahrzad Khorrami Why do you want to reinvent the wheel? There are many Database Abstraction Layer packages which support MySQL. Pear::MDB2, Zend_Db, CodeIgniter, ... the list doesn't end. Search for them! -- Nilesh Govindarajan http://nileshgr.com -- Nilesh Govindarajan http://nileshgr.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] mysql adapter and DAL
mysqli - it's what I wanted. thanks
Re: [PHP] mysql insert internal server error 500
I think it's not the best place to send it but: $dolgF is not closed with ; ...and the insert is not in a variable! (And also I think it's not a good way using COOKIE in PHP because we have sessions) So the answer is: the all.. :D -or what's the full part you use for insert int this source!? On Mon, May 16, 2011 at 5:11 PM, Grega Leskovšek legr...@gmail.com wrote: $ime=$_COOKIE['user']; $dolgF=filesize($filename) INSERT INTO `friendlyCMS`.`log` (`imepriimek`, `clock`, `action`, `onfile`, `filesize`) VALUES ( $ime, CURRENT_TIMESTAMP, 'saved',$filename, $dolgF); What is wrong with this? PS First column of the log table is idlog primary key autoincrement not null - I suppose I omit this when adding to table? Thanks in advance, Grega -- When the sun rises I receive and when it sets I forgive - http://moj.skavt.net/gleskovs/ Always in Heart, Grega Leskovšek -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql problems [SOLVED]
Sean Greenslade wrote:
[MASSIVE
SNIP]
Well, from what I saw while wading through your
code, you allow
unsanitized
variables to be
concatenated to your queries. Big no-no! For ANY
client-generated variable, always sanitize with
mysql_real_escape_string.
In
fact, sanitize all your
variables. It can't hurt.
Also, please don't take a
request for your entire code too literally. We
don't like to see
pages and pages and pages of code, just the pertinent
bits.
--
--Zootboy
Sent from my PC.
Thanks to all, but it was an infinite loop. there was a
while ($_parent != 0) { } loop. In the loop the database
is queried. If the returned number of rows is greater than 0 then
perform then grab a $_parent from the database. At some point, there
must be a parent that is = 0 and the loop breaks. However, if the
page is called with category number that doesn't exist, then the if/then
clause is never true and $_parent never gets set to 0. I simply
added and else clause.
while ($_parent != 0)
{
if
($num_rows 0)
{
perform some action
}
else
{
$_parent =
0;
}
}
and that solved the
problem.
Thank you, everyone for your help.
Curtis
RE: [PHP] mysql problems [SOLVED]
[SNIP]
added and else clause.
while ($_parent != 0)
{
if
($num_rows 0)
{
perform some action
}
else
{
$_parent =
0;
}
}
and that solved the
problem.
Thank you, everyone for your help.
Curtis
A small remark:
I think it is good programming practice to place such static if-clauses before
the while statement.
This prevents a lot of redundant checks and thus saves time.
Best regards,
Jasper
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] mysql problems
On 11 May 2011 at 19:25, Curtis Maurand cur...@maurand.com wrote: $_cartTotal=$0.00; Surely that should be: $_cartTotal = 0.00; tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP] mysql problems
Tim Streater wrote: On 11 May 2011 at 19:25, Curtis Maurand cur...@maurand.com wrote: $_cartTotal=$0.00; Surely that should be: $_cartTotal = 0.00; Good pickup. I missed that. I didn't write the code, I'm just trying to figure out what's going on. Thanks, I'll look at that. --C
Re: [PHP] mysql problems
Does anyone have any ideas? Sounds like it's getting caught in a loop. Post the whole script for best results. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql problems
Marc Guay wrote:
Does anyone have any ideas?
Sounds like it's getting caught in a loop. Post the whole script
for
best results.
It looks like the site is
under attack, because I keep seeing the query, SELECT catagory_parent FROM
t_catagories where catagory_ID= .
$_currentCat
where $_currentCat is equal to a
value not in the database. The only way that this can happen is if
the page is called directly without going through the default page.
the script follows. its called leftNav.php
?php
include
'media/includes/productDetail.php';
//$username =
alaric;
$username = pinetree;
//$password = password_removed;
$password =
password_removed;
$hostname = 127.0.0.1;
//$hostname = www.superseeds.com;
if($_SESSION[u_id]==){
$_SESSION[u_id] = uniqid();
}
//
$_cartTotal=$0.00;
$_cartCount=0;
function tallyCart($_u_id){
global $username;
global
$password;
global $hostname;
global $_cartTotal;
global
$_cartCount;
$dbhandle =
mysql_connect($hostname, $username, $password)
or die(Unable to connect to
MySQL);
$selected =
mysql_select_db(pinetree,$dbhandle)
or die(Could not select examples);
//execute the SQL query and
return records
$result = mysql_query(SELECT
* from tbl_Cart where u_ID='.$_u_id.');
$_holder=;
$_counter=0;
$_getSubTotal=0;
$_showCheckOut=0;
while ($row = mysql_fetch_array($result)) {
$_showCheckOut=1;
$_pdetail=new
ProductDetail($row{'product_ID'}, $row{'product_Quantity'}, $_u_id);
$_getSubTotal +=
$_pdetail-_subTotal;
$_counter++;
}
$_cartTotal = $.number_format($_getSubTotal,2);
$_cartCount = $_counter;
mysql_close($dbhandle);
}
tallyCart($_SESSION[u_id]);
?
div id=div_cartCall
div id=div_cartCall_head
You have ?php echo $_cartCount? items in your
cart.br/br/
Cart total: ?php
echo $_cartTotal?
/div
div id=div_cartCall_foot
a href=cart.php#65533; Go to
cart/a
/div
/div
p
?php
//$username = alaric;
$username = pinetree;
//$password =
removed;
$password = removed;
//$hostname = 127.0.0.1;
$hostname =
www.superseeds.com;
$_parents = array();
$counter=0;
if($_GET[cat]!=){
$_parent =$_GET[cat];
}
else{
$_parent =0;
}
$dbhandle2 = mysql_connect($hostname, $username, $password)
or die(Unable to connect to MySQL);
//echo
Connected to MySQLbr;
//select a database
to work with
$selected =
mysql_select_db(pinetree,$dbhandle2)
or
die(Could not select examples);
while ($_parent !=0) {
$result_2 = mysql_query(SELECT catagory_parent
FROM t_catagories where catagory_ID= .$_parent);
$num_rows_2 =
mysql_num_rows($result_2);
if($num_rows_2 0)
{
while ($row = mysql_fetch_array($result_2)) {
$_parent= $row{'catagory_parent'};
$_parents[$counter] = $row{'catagory_parent'};
$counter++;
}
}
}
mysql_close($dbhandle2);
function getParent($catID,
$matchingID){
//$username = alaric;
$username
= pinetree;
//$password = removed;
$password = removed;
//$hostname =
127.0.0.1;
$hostname = www.superseeds.com;
$_parent=1;
$_currentCat=$catID;
$dbhandle2 = mysql_connect($hostname, $username,
$password)
or die(Unable to connect
to MySQL);
//echo Connected to
MySQLbr;
//select a database to work with
$selected =
mysql_select_db(pinetree,$dbhandle2)
or die(Could not select examples);
while
($_parent !=0) {
$result_2 = mysql_query(SELECT catagory_parent
FROM t_catagories where catagory_ID= . $_currentCat);
while
($row = mysql_fetch_array($result_2)) {
$_parent=$row{'catagory_parent'};
if($row{'catagory_parent'}==$matchingID){
mysql_close($dbhandle2);
return true;
}
}
}
mysql_close($dbhandle2);
return false;
}
?
?php
function getRowCount($_catID){
global
$_parents;
global $username;
global $password;
global
$hostname;
$dbhandle = mysql_connect($hostname, $username, $password)
or die(Unable to connect to
MySQL);
$selected = mysql_select_db(pinetree,$dbhandle)
or die(Could not select
examples);
$result = mysql_query(SELECT COUNT(*) as theCount FROM t_catagories
where catagory_parent=.$_catID);
while ($row = mysql_fetch_array($result)) {
if($row{'theCount'}==0){
mysql_close($dbhandle);
return
0;
}
else{
mysql_close($dbhandle);
return
.$row{'theCount'};
}
}
}
function
generateNav($_parent, $_style){
if(getRowCount($_parent)0){
global $_parents;
global $username;
global $password;
global $hostname;
$dbhandle3 =
Re: [PHP] mysql problems
Marc Guay wrote:
Does anyone have any ideas?
Sounds like it's getting caught in a loop. Post the whole script
for
best results.
It looks like the site is
under attack, because I keep seeing the query, SELECT catagory_parent FROM
t_catagories where catagory_ID= .
$_currentCat
where $_currentCat is equal to a
value not in the database. The only way that this can happen is if
the page is called directly without going through the default page.
the script follows. its called leftNav.php
?php
include
'media/includes/productDetail.php';
//$username =
alaric;
$username = pinetree;
//$password = password_removed;
$password =
password_removed;
$hostname = 127.0.0.1;
//$hostname = www.superseeds.com;
if($_SESSION[u_id]==){
$_SESSION[u_id] = uniqid();
}
//
$_cartTotal=$0.00;
$_cartCount=0;
function tallyCart($_u_id){
global $username;
global
$password;
global $hostname;
global $_cartTotal;
global
$_cartCount;
$dbhandle =
mysql_connect($hostname, $username, $password)
or die(Unable to connect to
MySQL);
$selected =
mysql_select_db(pinetree,$dbhandle)
or die(Could not select examples);
//execute the SQL query and
return records
$result = mysql_query(SELECT
* from tbl_Cart where u_ID='.$_u_id.');
$_holder=;
$_counter=0;
$_getSubTotal=0;
$_showCheckOut=0;
while ($row = mysql_fetch_array($result)) {
$_showCheckOut=1;
$_pdetail=new
ProductDetail($row{'product_ID'}, $row{'product_Quantity'}, $_u_id);
$_getSubTotal +=
$_pdetail-_subTotal;
$_counter++;
}
$_cartTotal = $.number_format($_getSubTotal,2);
$_cartCount = $_counter;
mysql_close($dbhandle);
}
tallyCart($_SESSION[u_id]);
?
div id=div_cartCall
div id=div_cartCall_head
You have ?php echo $_cartCount? items in your
cart.br/br/
Cart total: ?php
echo $_cartTotal?
/div
div id=div_cartCall_foot
a href=cart.php#65533; Go to
cart/a
/div
/div
p
?php
//$username = alaric;
$username = pinetree;
//$password =
removed;
$password = removed;
//$hostname = 127.0.0.1;
$hostname =
www.superseeds.com;
$_parents = array();
$counter=0;
if($_GET[cat]!=){
$_parent =$_GET[cat];
}
else{
$_parent =0;
}
$dbhandle2 = mysql_connect($hostname, $username, $password)
or die(Unable to connect to MySQL);
//echo
Connected to MySQLbr;
//select a database
to work with
$selected =
mysql_select_db(pinetree,$dbhandle2)
or
die(Could not select examples);
while ($_parent !=0) {
$result_2 = mysql_query(SELECT catagory_parent
FROM t_catagories where catagory_ID= .$_parent);
$num_rows_2 =
mysql_num_rows($result_2);
if($num_rows_2 0)
{
while ($row = mysql_fetch_array($result_2)) {
$_parent= $row{'catagory_parent'};
$_parents[$counter] = $row{'catagory_parent'};
$counter++;
}
}
}
mysql_close($dbhandle2);
function getParent($catID,
$matchingID){
//$username = alaric;
$username
= pinetree;
//$password = removed;
$password = removed;
//$hostname =
127.0.0.1;
$hostname = www.superseeds.com;
$_parent=1;
$_currentCat=$catID;
$dbhandle2 = mysql_connect($hostname, $username,
$password)
or die(Unable to connect
to MySQL);
//echo Connected to
MySQLbr;
//select a database to work with
$selected =
mysql_select_db(pinetree,$dbhandle2)
or die(Could not select examples);
while
($_parent !=0) {
$result_2 = mysql_query(SELECT catagory_parent
FROM t_catagories where catagory_ID= . $_currentCat);
while
($row = mysql_fetch_array($result_2)) {
$_parent=$row{'catagory_parent'};
if($row{'catagory_parent'}==$matchingID){
mysql_close($dbhandle2);
return true;
}
}
}
mysql_close($dbhandle2);
return false;
}
?
?php
function getRowCount($_catID){
global
$_parents;
global $username;
global $password;
global
$hostname;
$dbhandle = mysql_connect($hostname, $username, $password)
or die(Unable to connect to
MySQL);
$selected = mysql_select_db(pinetree,$dbhandle)
or die(Could not select
examples);
$result = mysql_query(SELECT COUNT(*) as theCount FROM t_catagories
where catagory_parent=.$_catID);
while ($row = mysql_fetch_array($result)) {
if($row{'theCount'}==0){
mysql_close($dbhandle);
return
0;
}
else{
mysql_close($dbhandle);
return
.$row{'theCount'};
}
}
}
function
generateNav($_parent, $_style){
if(getRowCount($_parent)0){
global $_parents;
global $username;
global $password;
global $hostname;
$dbhandle3 =
Re: [PHP] mysql problems
On Wed, May 11, 2011 at 2:25 PM, Curtis Maurand cur...@maurand.com wrote: Marc Guay wrote: Does anyone have any ideas? Sounds like it's getting caught in a loop. Post the whole script for best results. It looks like the site is under attack, because I keep seeing the query, SELECT catagory_parent FROM t_catagories where catagory_ID= . $_currentCat where $_currentCat is equal to a value not in the database. The only way that this can happen is if the page is called directly without going through the default page. the script follows. its called leftNav.php [MASSIVE SNIP] Well, from what I saw while wading through your code, you allow unsanitized variables to be concatenated to your queries. Big no-no! For ANY client-generated variable, always sanitize with mysql_real_escape_string. In fact, sanitize all your variables. It can't hurt. Also, please don't take a request for your entire code too literally. We don't like to see pages and pages and pages of code, just the pertinent bits. -- --Zootboy Sent from my PC.
Re: [PHP] mysql error
Hi Grega, Probably you wanna define a primary key, if so, then change from uniqueid to primary key. 2011/5/6 Curtis Maurand cur...@maurand.com: engine= --C Grega Leskovšek wrote: Can smbd please look at this sentence - I got an error and do not know how to fix it - I am still very unfamiliar with MYSQL: CREATE TABLE log ( idlog int auto_increment not null, imepriimek varchar(50), clock timestamp, action varchar(30), onfile varchar(100), filesize float(6,2), uniqueid(idlog) ); ERROR 1064 (42000): 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 '(idlog) )' at line 1 -- When the sun rises I receive and when it sets I forgive - http://moj.skavt.net/gleskovs/ Always in Heart, Grega LeskovĹĄek -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql error
engine= --C Grega Leskov¹ek wrote: Can smbd please look at this sentence - I got an error and do not know how to fix it - I am still very unfamiliar with MYSQL: CREATE TABLE log ( idlog int auto_increment not null, imepriimek varchar(50), clock timestamp, action varchar(30), onfile varchar(100), filesize float(6,2), uniqueid(idlog) ); ERROR 1064 (42000): 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 '(idlog) )' at line 1 -- When the sun rises I receive and when it sets I forgive - http://moj.skavt.net/gleskovs/ Always in Heart, Grega LeskovÅ¡ek -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql error
Hello Grega, What do you mean by uniqueid(idlog)? -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Grega Leskovšek legr...@gmail.com To: php-general Date created: , 5:29:44 PM Subject: [PHP] mysql error Can smbd please look at this sentence - I got an error and do not know how to fix it - I am still very unfamiliar with MYSQL: CREATE TABLE log ( idlog int auto_increment not null, imepriimek varchar(50), clock timestamp, action varchar(30), onfile varchar(100), filesize float(6,2), uniqueid(idlog) ); ERROR 1064 (42000): 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 '(idlog) )' at line 1 -- When the sun rises I receive and when it sets I forgive - http://moj.skavt.net/gleskovs/ Always in Heart, Grega Leskovšek -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql error
On Thu, May 5, 2011 at 10:29, Grega Leskovšek legr...@gmail.com wrote: Can smbd please look at this sentence - I got an error and do not know how to fix it - I am still very unfamiliar with MYSQL: CREATE TABLE log ( idlog int auto_increment not null, imepriimek varchar(50), clock timestamp, action varchar(30), onfile varchar(100), filesize float(6,2), uniqueid(idlog) ); ERROR 1064 (42000): 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 '(idlog) )' at line 1 This is by no means a PHP question, and should not be asked on the PHP General mailing list. Please ask questions in the appropriate place; for this, the MySQL General list is the correct forum, and they've been CC'd. In the future, if it relates to a PHP database issue, you may want to use the PHP Database mailing list, also CC'd. This not only means you'll get more on-target help faster, but also helps in archiving data in the proper location for future searchers. -- /Daniel P. Brown Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql search query ignoring dots
-Original Message-
From: Barbara Picci [mailto:barbara.pi...@sardi.it]
Sent: Monday, January 24, 2011 4:51 AM
To: php-general@lists.php.net
Subject: [PHP] Mysql search query ignoring dots
Hi all,
I have to perform a mysql query in a table with millions of records.
I've full-text indexed my search field and I'm searching with MATCH
AGAINST.
But there is a problem. In this field there are company names that contain
dots, for istance I've PO.SE. srl and I want to find it if the user
search for:
POSE or PO.SE or P.O.S.E. etc.
I googled in the web but I don't find any solution. I don't want to add a
new
field with the cleaned version of my string because I would like to solve
with the query and I prefer that the mysql table not become too big. But
if I
will not find a different solution, I will use this escamotage.
I've find a post that is similar but the solution don't seem to solve my
situation.
You can see it at the url:
http://forums.mysql.com/read.php?10,395557,395584#msg-395584
In my case replace(email, '.', '') = replace(theSearchValue, '.', ''); is
indifferent and don't change my results.
My query, searching POSE, is:
select aziende.* from aziende where 10 AND
(MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
(replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
'')) order by aziende.ragione_sociale limit 0, 10
The alternative choice could be REGEXP but I've red that it make my query
slow in a table of millions of records and I don't know how to exclude
dots
in the regular expression.
Can anyone help me?
Thanks in advance.
Barbara
--
Barbara Picci
Micro srl
viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
http://www.microsrl.com
I don't see anything relevant regarding PHP. As for ' a table of millions
of records,' that sounds like questions for the DBA.
Regards,
Tommy
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search query ignoring dots
Hi,
Monday, January 24, 2011, 10:50:41 PM, you wrote:
BP Hi all,
BP I have to perform a mysql query in a table with millions of records.
BP I've full-text indexed my search field and I'm searching with MATCH AGAINST.
BP But there is a problem. In this field there are company names that
BP contain dots, for istance I've PO.SE. srl and I want to find it if
BP the user search for: POSE or PO.SE or P.O.S.E. etc.
BP I googled in the web but I don't find any solution. I don't want to
BP add a new field with the cleaned version of my string because I would
BP like to solve with the query and I prefer that the mysql table not
BP become too big. But if I will not find a different solution, I will
BP use this escamotage.
BP I've find a post that is similar but the solution don't seem to solve
BP my situation.
BP You can see it at the url:
BP http://forums.mysql.com/read.php?10,395557,395584#msg-395584
BP In my case replace(email, '.', '') = replace(theSearchValue, '.', '');
BP is indifferent and don't change my results.
BP My query, searching POSE, is:
BP select aziende.* from aziende where 10 AND
BP (MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
BP (replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
BP '')) order by aziende.ragione_sociale limit 0, 10
BP The alternative choice could be REGEXP but I've red that it make my
BP query slow in a table of millions of records and I don't know how to
BP exclude dots in the regular expression.
BP Can anyone help me?
BP Thanks in advance.
BP Barbara
BP --
BP
BP Barbara Picci
BP Micro srl
BP viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
BP http://www.microsrl.com
In the interest of speed it may be worth creating a table with just a
link id and the text to search which you can cleanup before inserting.
It will probably save you a headache in the future and will be quicker
than complicated queries.
The list of possible ids can then be tested against the full table for
any other criteria which if the table is indexed properly will be fast
too.
--
regards,
Tom
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:35, Don Wieland d...@dwdataconcepts.com wrote: Hi all, Is there a list/form to get some help on compiling mySQL queries? I am executing them via PHP, but do not want to ask for help here if it is no the appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:40, Daniel P. Brown daniel.br...@parasane.net wrote: For your convenience, both have been CC'd on this email. Actually, PHP-DB (php...@lists.php.net) was accidentally BCC'd. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL database export to Excel
On Fri, 2010-11-26 at 15:21 +0200, Sotiris Katsaniotis wrote:
Greetings fellow PHP developers!
I am looking of a relatively simple way to export a whole database into
an Excel file. I have several methods to export tables to Excel files
but unfortunately I have failed to export a whole database!
Can someone be so kind to point me in the right direction?
Thanks a lot!
DISCLAIMER
This is untested, and just off the top of my pointy head, do some
research, and know how much strain this COULD put on your system.
/DISCLAIMER
there are MANY MySQL commands that you can use, and combine them into 1
script, and then export them into excel, using multiple pages with
something like this:
http://www.codediesel.com/php/creating-excel-documents-in-php/
example mysql queries:
SHOW DATABASES; -- gets a lits of all the databases (skip MySQL and
information_schema)
SHOW TABLES FROM {tablename}; -- gets all the tables in a specific
database
SELECT column_name FROM information_schema.columns WHERE
table_name='{tablename}' AND table_schema='{databasename}'; -- gets you
a list of all the column headers (there are other ways you can avoid
doing this, but this works)
SELECT * FROM {databasename}.{tablename}; -- gets all the rows from a
table in the database
if you were to create a script, and providing your scipt has enough
max_execution_time, and memory_limit (should prolly be done from the
CLI) you can prolly get it done...
and yeah, this is a pointer to how it *COULD* be done, but it's not the
only way, nor is it prolly the best either.
Good Luck, and if you need more MySQL related questions answered, try
the my...@lists.mysql.com mailing list. they are really good over
there.
Steve
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT JOIN criteria c ON cv.key=c.key WHERE c.value IS NOT NULL Hard to answer without more detail, but I am guessing the answer will be something like the above. Your question makes it hard to understand whether c or cv is joined to p. So swap em around if I misunderstood. iPhone 4. It rocks! On Nov 21, 2010, at 1:37 AM, Simcha Younger sim...@syounger.com wrote: On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com wrote: Hi, I'm building a website for a client in which I need to compare their products, side-by-side, but only include criteria for which all selected products have a value for that criteria. In my database (MySQL), I have a tables named products,criteria and criteria_values If I have something like $selected_product = array(1=Product 1,2=Product 2...) // All products selected for comparison by the user I need to get only rows from criteria where there is a row in criteria_values matching criteria.criteria_id for each $selected_product - in other words, if any of the $selected_product does not have a row in criteria_values that matches criteria.criteria_id, that criteria would not be returned. I hope that makes sense. It would be a lot easier to think about this if you could provide the table structure or create table statements. If I understood correctly, you have products which reference a criteria ID which has no matching value. If this is the problem you have a to first take care of the integrity of your data, as this should never happen. -- Simcha Younger sim...@syounger.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help
SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT JOIN criteria c ON cv.key=c.key WHERE c.value IS NOT NULL Hard to answer without more detail, but I am guessing the answer will be something like the above. Your question makes it hard to understand whether c or cv is joined to p. So swap em around if I misunderstood. iPhone 4. It rocks! On Nov 21, 2010, at 1:37 AM, Simcha Younger sim...@syounger.com wrote: On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com wrote: Hi, I'm building a website for a client in which I need to compare their products, side-by-side, but only include criteria for which all selected products have a value for that criteria. In my database (MySQL), I have a tables named products,criteria and criteria_values If I have something like $selected_product = array(1=Product 1,2=Product 2...) // All products selected for comparison by the user I need to get only rows from criteria where there is a row in criteria_values matching criteria.criteria_id for each $selected_product - in other words, if any of the $selected_product does not have a row in criteria_values that matches criteria.criteria_id, that criteria would not be returned. I hope that makes sense. It would be a lot easier to think about this if you could provide the table structure or create table statements. If I understood correctly, you have products which reference a criteria ID which has no matching value. If this is the problem you have a to first take care of the integrity of your data, as this should never happen. To help clarify - the 3 tables look something like the following (tableName = column,column,column...): Products = product_id,product_name,product_description... (key = product_id) Criteria = criteria_id,criteria_title,criteria_text,... (key = criteria_id) Criteria_values = product_id,criteria_id,criteria_value,... (key = product_id criteria_id) The user selects up to X product_id's to compare, stored in $selected_products. I then need to get each criteria_title and criteria_text from table(criteria) where there is a matching criteria_id in table(criteria_values) for each/all $selected_products, also returning the criteria_value for each $selected_products, ultimately ending up with an array or object that looks something like: (Assuming the user selected Product A (product_id=1), Product B (product_id=2) and Product C (product_id=3) criteria = Array ( [$criteria_id] = Array ( [title] = query_row[criteria_title] [text] = query_row[criteria_text] [values] = Array ( [1] = Product A's value for this criteria [2] = Product B's value for this criteria [3] = Product C's value for this criteria ) ) [$criteria_id] = Array ( . ) ) Again, displaying only/all criteria where there is a matching value for each/all $selected_products Thanks again, Ben -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
I'm going to jump in and throw in my 2 cents... Have you used dreamweaver? I would suggest Dreamweaver to any new programmer beginning php/mysql. It helped me out tremendously in the beginning. I'm not an advanced programmer with hand coding classes yet, but I can get any job completed for clients with dreamweaver. Custom content management systems, image galleries from mysql etc... Give it a try, It lets you add the the prewritten code and then you can switch to code view and see whats its doing. RD On Nov 20, 2010, at 3:54 PM, Ben Miller wrote: Hi, I'm building a website for a client in which I need to compare their products, side-by-side, but only include criteria for which all selected products have a value for that criteria. In my database (MySQL), I have a tables named products,criteria and criteria_values If I have something like $selected_product = array(1=Product 1,2=Product 2...) // All products selected for comparison by the user I need to get only rows from criteria where there is a row in criteria_values matching criteria.criteria_id for each $selected_product - in other words, if any of the $selected_product does not have a row in criteria_values that matches criteria.criteria_id, that criteria would not be returned. I hope that makes sense. I've played around with a few join queries, but none have given the desired results. Best I've been able to come up with so far is to query criteria for each DISTINCT(criteria_id) and then run through each $selected_product to make sure each has a criteria_value with a matching criteria_id, eliminating any criteria where the number of criteria_values count($selected_product), but this seems pretty inefficient. Thanks in advance for any help. Ben Miller -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help
Since we are just tossing out development environments. We moved to Aptana in conjunction with TortoiseSVN for a team environment development timelines dropped. Personally I do not feel any gui editor makes you a better programmer, maybe you understand the fundamentals a little less. Not that anything so far has been an answer to your question. Developing mysql statements that not only require minimum resources but that are highly effective. Take a lot of trial and error. I feel there is no better tool in my mind to test query concepts than NaviCat. Not only is the product very user friendly but as a senior developer it gives me more insight into the impact the query has on my servers. Always think longevity of the product you are producing. Imagine one day you have 650,000 products how will the query impact the service you have written. I feel as a certified MySQL DBA you should understand that what works today may NOT be the best choice in query statements for the future. Always analyze your query statements for query length and system resources requirements. Depending on the structure of your database/tables/fields your query may be achieved many different ways. I might suggest you try an extended select statement. SELECT product from sometable WHERE product='$array1' AND product=(SELECT product_name from sometable where other matching critera); By extending or what some may call concating the statement the return is more effective. Richard L. Buskirk -Original Message- From: Ben Miller [mailto:biprel...@gmail.com] Sent: Saturday, November 20, 2010 3:54 PM To: 'php-general' Subject: [PHP] MySQL Query Help Hi, I'm building a website for a client in which I need to compare their products, side-by-side, but only include criteria for which all selected products have a value for that criteria. In my database (MySQL), I have a tables named products,criteria and criteria_values If I have something like $selected_product = array(1=Product 1,2=Product 2...) // All products selected for comparison by the user I need to get only rows from criteria where there is a row in criteria_values matching criteria.criteria_id for each $selected_product - in other words, if any of the $selected_product does not have a row in criteria_values that matches criteria.criteria_id, that criteria would not be returned. I hope that makes sense. I've played around with a few join queries, but none have given the desired results. Best I've been able to come up with so far is to query criteria for each DISTINCT(criteria_id) and then run through each $selected_product to make sure each has a criteria_value with a matching criteria_id, eliminating any criteria where the number of criteria_values count($selected_product), but this seems pretty inefficient. Thanks in advance for any help. Ben Miller -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com wrote: Hi, I'm building a website for a client in which I need to compare their products, side-by-side, but only include criteria for which all selected products have a value for that criteria. In my database (MySQL), I have a tables named products,criteria and criteria_values If I have something like $selected_product = array(1=Product 1,2=Product 2...) // All products selected for comparison by the user I need to get only rows from criteria where there is a row in criteria_values matching criteria.criteria_id for each $selected_product - in other words, if any of the $selected_product does not have a row in criteria_values that matches criteria.criteria_id, that criteria would not be returned. I hope that makes sense. It would be a lot easier to think about this if you could provide the table structure or create table statements. If I understood correctly, you have products which reference a criteria ID which has no matching value. If this is the problem you have a to first take care of the integrity of your data, as this should never happen. -- Simcha Younger sim...@syounger.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql help (sorry, a bit OT)
Hello Gary, Actually, what I do here is the following: I create a subdomain called beta.mysite.com (for me it's http://beta.oire.org/ and http://beta.gviragon.org/ :-)). There I have a copy of my database and all of my files. The only difference is the mysql_select_db in the connect.php file. There I can easily test everything I need (including PHP files and MySql queries) without any danger to crash the main release). -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion - Original message - From: Gary php-gene...@garydjones.name To: php-general@lists.php.net php-general@lists.php.net Date: Tuesday, November 16, 2010, 3:35:12 PM Subject: [PHP] mysql help (sorry, a bit OT) Is there a way to check the syntax of a query, short of running it? I've got an insert to do (but of course it's a valid question for any query that changes the db contents) and would like to know that the sql I am generating (in php - see! not so off-topic!) is correct. What I don't want to do is run it for testing (live system *sigh*) and find out it is correct (it will change the db), but... I have to test it to check that the syntax (at least) *is* correct. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql help (sorry, a bit OT)
It's always best practice to have a staging server for testing these sorts of things. If in doubt, run it in a transaction, but don't commit it, I.e. roll it back. That way you'll see if it would run but nothing actually changes. Thanks, Ash http://www.ashleysheridan.co.uk - Reply message - From: Gary php-gene...@garydjones.name Date: Tue, Nov 16, 2010 13:35 Subject: [PHP] mysql help (sorry, a bit OT) To: php-general@lists.php.net Is there a way to check the syntax of a query, short of running it? I've got an insert to do (but of course it's a valid question for any query that changes the db contents) and would like to know that the sql I am generating (in php - see! not so off-topic!) is correct. What I don't want to do is run it for testing (live system *sigh*) and find out it is correct (it will change the db), but... I have to test it to check that the syntax (at least) *is* correct. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql help (sorry, a bit OT)
-Original Message- From: Gary [mailto:php-gene...@garydjones.name] Sent: Tuesday, November 16, 2010 5:35 AM To: php-general@lists.php.net Subject: [PHP] mysql help (sorry, a bit OT) Is there a way to check the syntax of a query, short of running it? I've got an insert to do (but of course it's a valid question for any query that changes the db contents) and would like to know that the sql I am generating (in php - see! not so off-topic!) is correct. What I don't want to do is run it for testing (live system *sigh*) and find out it is correct (it will change the db), but... I have to test it to check that the syntax (at least) *is* correct. Gary, If you use a current version of the MySQL workbench, the tool can send complete SELECT, UPDATE, INSERT, DELETE statement, with all the fields, to the query window for the selected table. That way you'll know that you have the proper field (name and quoted with `). Add that to what Ash suggested of having a local copy of the database, you'll have little or no chance of breakage in the app because of a silly SQL syntax error ;) Regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL select matching
On Mon, 19 Jul 2010 10:36:40 -0600 Ashley M. Kirchner ash...@pcraft.com wrote: mysql select * from table where id='1'; +---+-+-+---+ | 1 | 123 | 0.0 | C | | 1 | 234 | 0.1 | D | | 1 | 345 | 0.0 | D | | 1 | 456 | 0.1 | C | | 1 | 567 | 0.1 | G | +---+-+-+---+ Now, I have to find other IDs that match the above result. In the table, that would be ID '3' (and in the entire DB, there may be others as well - I need to find all those IDs.) But, notice how ID 0003 isn't in the same order as ID 1, but the data is still the same. select distinct id from `table` where concat(`b`, `c`, `d`) in (select concat(`b`,`c`,`d` from `table` where id = '0001') AND id != '0001'; (untested) -- Simcha Younger sim...@syounger.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
I am very keen to see a closure to this thread so that I can add to my snippets. Let's all know what worked best out of many solutions that have been proposed. --Shreyas On Tue, Jul 20, 2010 at 10:07 AM, Jim Lucas li...@cmsws.com wrote: Peter wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter Use the SQL command alter with the ignore flag. ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` ) I tested this on my test DB and it worked fine. It erased all the duplicates and left one instance of the multiple entry values. This will add a permanent unique restraint to the table. So, you will never have dupps again. Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
Use distinct. SELECT DISTINCT COLUMN1, COLUMN2 FROM ... ... Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Hi Shiplu, Thanks for reply. Distinct function hide the duplicate records while we selecting the record through the Query i want to remove the duplicate entries in my table i need a Delete Query instead of Select Query shiplu wrote: Use distinct. SELECT DISTINCT COLUMN1, COLUMN2 FROM ... ... Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On 19 July 2010 05:44, Peter pet...@egrabber.com wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter If your table had a db generated sequential unique identifier (an identity / autoinc), then something along these lines may be what you are looking for ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Just get the UniqueIDs we want to keep. SELECT UniqueID FROM ( -- Get the earlist UniqueID for each Col1, Col2, pairing. SELECT Col1, Col2, MIN(UniqueID) AS UniqueID FROM Table GROUP BY Col1, Col2 ) ) UNTESTED -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On 19 July 2010 15:01, Richard Quadling rquadl...@gmail.com wrote: On 19 July 2010 05:44, Peter pet...@egrabber.com wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Slightly more concise ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Get the earliest UniqueIDs for each Col1, Col2 pairing. SELECT MIN(UniqueID) FROM Table GROUP BY Col1, Col2 ) http://www.devx.com/tips/Tip/14665 DELETE Table FROM Table T1, Table T2 WHERE T1.Col1 = T2.Col1 AND T1.Col2 = T2.Col2 AND T1.UniqueID T2.UniqueID http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm etc. Many different ways. http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table Method 3 should be the fastest. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
How about this : CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; Regards, Shreyas On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling rquadl...@gmail.comwrote: On 19 July 2010 05:44, Peter pet...@egrabber.com wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter If your table had a db generated sequential unique identifier (an identity / autoinc), then something along these lines may be what you are looking for ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Just get the UniqueIDs we want to keep. SELECT UniqueID FROM ( -- Get the earlist UniqueID for each Col1, Col2, pairing. SELECT Col1, Col2, MIN(UniqueID) AS UniqueID FROM Table GROUP BY Col1, Col2 ) ) UNTESTED -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
Just to add more perspective : You will have a table with DISTINCT values. Drop the initial table (better take a back-up); copy from the temporary table which will have only DISTINCT values. Regards, Shreyas On Mon, Jul 19, 2010 at 7:58 PM, Shreyas Agasthya shreya...@gmail.comwrote: How about this : CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; Regards, Shreyas On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling rquadl...@gmail.comwrote: On 19 July 2010 05:44, Peter pet...@egrabber.com wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter If your table had a db generated sequential unique identifier (an identity / autoinc), then something along these lines may be what you are looking for ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Just get the UniqueIDs we want to keep. SELECT UniqueID FROM ( -- Get the earlist UniqueID for each Col1, Col2, pairing. SELECT Col1, Col2, MIN(UniqueID) AS UniqueID FROM Table GROUP BY Col1, Col2 ) ) UNTESTED -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Regards, Shreyas Agasthya -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
Peter wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter Use the SQL command alter with the ignore flag. ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` ) I tested this on my test DB and it worked fine. It erased all the duplicates and left one instance of the multiple entry values. This will add a permanent unique restraint to the table. So, you will never have dupps again. Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On Mon, Jul 19, 2010 at 10:14:30AM +0530, Peter wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c If you're looking for a MySQL solution to this, this is the wrong list to ask the question on. In fact, I'd be surprised to find a MySQL query which would do this. For a PHP solution, you'll need to query MySQL for all the rows, in order by the column you want to use to kill duplicates. Then loop through the rows one at a time in PHP, checking the contents of that column against the last iteration. If they are the same, issue a DELETE command in MySQL. Continue the loop until all rows are exhausted. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On Mon, Jul 19, 2010 at 10:44 AM, Peter pet...@egrabber.com wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter Create a table with similar structure and add UNIQUE INDEX on both columns. Execute the following query: INSERT IGNORE INTO NEW_TABLE (column1, column2) SELECT column1, column2 FROM OLD_TABLE This will give you distinct rows as required. -- Keep Smiling :-) Shafiq Rehman Blog: http://shafiq.pk, Twitter: http://twitter.com/shafiq -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Sun, Jun 27, 2010 at 4:08 AM, Tanel Tammik keevit...@gmail.com wrote: Hello, how to select only if value is present? $query = $db-query(select menus.id, menus.name, case when panels.id is not null then '1' end as hiddenpanel from . \DB_MENUS . as menus left join . \DB_HIDDENPANELS . as panels on (menus.id = panels.menu_id) where menus.id=' . (int)$id . ' ); i would like to select hiddenpanel only if there is a corresponding value in DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding value in HIDDENPANELS table! Br Tanel That's what a LEFT JOIN does - it returns all rows from the LEFT table that match the criteria in the WHERE clause, and then returns any rows from the RIGHT table that happen do match. If you only want rows that exist in both tables, change the join from LEFT (OUTER) JOIN to INNER JOIN. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Monday 28 June 2010 09:49:55 Andrew Ballard wrote: On Sun, Jun 27, 2010 at 4:08 AM, Tanel Tammik keevit...@gmail.com wrote: Hello, how to select only if value is present? $query = $db-query(select menus.id, menus.name, case when panels.id is not null then '1' end as hiddenpanel from . \DB_MENUS . as menus left join . \DB_HIDDENPANELS . as panels on (menus.id = panels.menu_id) where menus.id=' . (int)$id . ' ); i would like to select hiddenpanel only if there is a corresponding value in DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding value in HIDDENPANELS table! Br Tanel That's what a LEFT JOIN does - it returns all rows from the LEFT table that match the criteria in the WHERE clause, and then returns any rows from the RIGHT table that happen do match. If you only want rows that exist in both tables, change the join from LEFT (OUTER) JOIN to INNER JOIN. Tanel, we both learned something. I didn't fully understand join myself yet, but I think I do now. but let me ask this if the join wasn't there would an if statement like I mentioned have worked? Blessings, David M. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Sunday 27 June 2010 22:12:41 Brandon Rampersad wrote: no At least smack me and give us an explanation. :-) -- Blessings, David M. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Mon, Jun 28, 2010 at 10:27 AM, David McGlone da...@dmcentral.net wrote: Tanel, we both learned something. I didn't fully understand join myself yet, but I think I do now. but let me ask this if the join wasn't there would an if statement like I mentioned have worked? Blessings, David M. I think you are confusing a few things. You can't really rely on testing empty(DB_HIDDENPANELS) because the value of the constant DB_HIDDENPANELS is most likely a string that was set with an earlier call to define. The OP could have tested for the column value hiddenpanel using an if (...) test as you suggeted. However, given that the OP stated he would like to select hiddenpanel only if there is a corresponding value in DB_HIDDENPANELS, the INNER JOIN will do that at the database query level, so an if (...) then test in PHP isn't really necessary. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Monday 28 June 2010 11:14:53 Andrew Ballard wrote: On Mon, Jun 28, 2010 at 10:27 AM, David McGlone da...@dmcentral.net wrote: Tanel, we both learned something. I didn't fully understand join myself yet, but I think I do now. but let me ask this if the join wasn't there would an if statement like I mentioned have worked? Blessings, David M. I think you are confusing a few things. You can't really rely on testing empty(DB_HIDDENPANELS) because the value of the constant DB_HIDDENPANELS is most likely a string that was set with an earlier call to define. The OP could have tested for the column value hiddenpanel using an if (...) test as you suggeted. However, given that the OP stated he would like to select hiddenpanel only if there is a corresponding value in DB_HIDDENPANELS, the INNER JOIN will do that at the database query level, so an if (...) then test in PHP isn't really necessary. That is exactly what I was wondering. I haven't got to joins yet, and didn't understand them at all until Richard explained a few minutes ago. I knew my suggestion was a complete shot in the dark and I did feel stupid posting it, but I'm glad now, because I learned something invaluable. Thanks to this list, someday, which I hope is sooner than later, I'll be able to help somebody with complete confidence. -- Blessings, David M. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Sunday 27 June 2010 04:08:24 Tanel Tammik wrote:
Hello,
how to select only if value is present?
$query = $db-query(select menus.id, menus.name,
case
when panels.id is not null then '1'
end as hiddenpanel
from . \DB_MENUS . as menus
left join . \DB_HIDDENPANELS . as panels on (menus.id =
panels.menu_id)
where menus.id=' . (int)$id . '
);
i would like to select hiddenpanel only if there is a corresponding value
in DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding
value in HIDDENPANELS table!
I would use an if statement since you only need to determine true or false.
Something like:
$query = $db-query(select menus.id, menus.name,
from . \DB_MENUS . as menus
left join . \DB_HIDDENPANELS . as panels on (menus.id =
panels.menu_id)
where menus.id=' . (int)$id . '
);
if (empty(DB_HIDDENPANELS)) {
echo ;
}
else {
echo hiddenpanel;
}
But I would wait for others to chime in on this one, because I'm very far from
an expert, there's also got to be a much better efficient way to write the if
statement above, but it's what I would do in a case like this until I found a
better way.
--
Blessings,
David M.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
no
On Sun, Jun 27, 2010 at 8:29 PM, David McGlone da...@dmcentral.net wrote:
On Sunday 27 June 2010 04:08:24 Tanel Tammik wrote:
Hello,
how to select only if value is present?
$query = $db-query(select menus.id, menus.name,
case
when panels.id is not null then '1'
end as hiddenpanel
from . \DB_MENUS . as menus
left join . \DB_HIDDENPANELS . as panels on (menus.id =
panels.menu_id)
where menus.id=' . (int)$id . '
);
i would like to select hiddenpanel only if there is a corresponding value
in DB_HIDDENPANELS. At the moment i get NULL if there is no
corresponding
value in HIDDENPANELS table!
I would use an if statement since you only need to determine true or false.
Something like:
$query = $db-query(select menus.id, menus.name,
from . \DB_MENUS . as menus
left join . \DB_HIDDENPANELS . as panels on (menus.id =
panels.menu_id)
where menus.id=' . (int)$id . '
);
if (empty(DB_HIDDENPANELS)) {
echo ;
}
else {
echo hiddenpanel;
}
But I would wait for others to chime in on this one, because I'm very far
from
an expert, there's also got to be a much better efficient way to write the
if
statement above, but it's what I would do in a case like this until I found
a
better way.
--
Blessings,
David M.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
A Brandon_R Production
Re: [PHP] Mysql search html tags excluded
On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Mysql search html tags excluded
Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search html tags excluded
On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Mysql search html tags excluded
Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652342.2217.51.ca...@localhost... On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk OK! then i should use preg_replace before making the serch entry for DB storage? What would be the regular expression for that? Basically i need to get rid everything between the html tags with tags included? Br Tanel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search html tags excluded
On Fri, 2010-06-04 at 14:54 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652342.2217.51.ca...@localhost... On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk OK! then i should use preg_replace before making the serch entry for DB storage? What would be the regular expression for that? Basically i need to get rid everything between the html tags with tags included? Br Tanel No, you'd have to use a regex within MySQL, not PHP. Like I said, it would be very complex, and I wouldn't know where to begin writing a query that would search for specific strings and ignore any content within thewithout writing sub-queries. Also, you did see that I said it would be a lot slower didn't you? Imagine at the moment a query is taking a second to complete. With this sort of complex regex it could take maybe 5 seconds. That's 5 seconds per person searching. Are you not able to make a second field in the DB? Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Mysql search html tags excluded
Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652880.2217.54.ca...@localhost... On Fri, 2010-06-04 at 14:54 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652342.2217.51.ca...@localhost... On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk OK! then i should use preg_replace before making the serch entry for DB storage? What would be the regular expression for that? Basically i need to get rid everything between the html tags with tags included? Br Tanel No, you'd have to use a regex within MySQL, not PHP. Like I said, it would be very complex, and I wouldn't know where to begin writing a query that would search for specific strings and ignore any content within thewithout writing sub-queries. Also, you did see that I said it would be a lot slower didn't you? Imagine at the moment a query is taking a second to complete. With this sort of complex regex it could take maybe 5 seconds. That's 5 seconds per person searching. Are you not able to make a second field in the DB? Thanks, Ash http://www.ashleysheridan.co.uk Yes i can. You misunderestood me or i didn't express myself correctly. how can i get rid of the tags before entering the data into the second field created for search engine? Br Tanel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search html tags excluded
On Fri, 2010-06-04 at 15:00 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652880.2217.54.ca...@localhost... On Fri, 2010-06-04 at 14:54 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275652342.2217.51.ca...@localhost... On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: Ashley Sheridan a...@ashleysheridan.co.uk wrote in message news:1275651371.2217.46.ca...@localhost... On Fri, 2010-06-04 at 14:12 +0300, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Use a second field in the DB that stores the content without any HTML tags. That way, you can search and not worry about tags and attribute values getting in the way. Thanks, Ash http://www.ashleysheridan.co.uk Is this the only way? Couldn't i do it in mysql query? Seems much cleaner... Br, Tanel You could try and do it in MySQL with a regex to filter out the HTML tags. The regex would be real complex though, and prone to failure if the HTML wasn't perfectly formed. And it would be a *lot* slower than searching a plain text field. I think it's far cleaner to use a second field like that. Thanks, Ash http://www.ashleysheridan.co.uk OK! then i should use preg_replace before making the serch entry for DB storage? What would be the regular expression for that? Basically i need to get rid everything between the html tags with tags included? Br Tanel No, you'd have to use a regex within MySQL, not PHP. Like I said, it would be very complex, and I wouldn't know where to begin writing a query that would search for specific strings and ignore any content within thewithout writing sub-queries. Also, you did see that I said it would be a lot slower didn't you? Imagine at the moment a query is taking a second to complete. With this sort of complex regex it could take maybe 5 seconds. That's 5 seconds per person searching. Are you not able to make a second field in the DB? Thanks, Ash http://www.ashleysheridan.co.uk Yes i can. You misunderestood me or i didn't express myself correctly. how can i get rid of the tags before entering the data into the second field created for search engine? Br Tanel Ah right. Use strip_tags(). As long as the HTML is well-formed and doesn't contain any malformed tags it should remove them correctly. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Mysql search html tags excluded
At 2:12 PM +0300 6/4/10, Tanel Tammik wrote: Hello, if there is some webpage content with html tags in database is it possible to search it without tags? data : 'div style=you need some styling!/div' when i now search for 'you style' i don't want to get any rows! is it possible? when i search 'you styling' i get the row! Br Tanel Tanel: If your database has html tags in it, then it's pretty simple to grab the data from the db and preform strip_tags(). After which you can search what's left. If you want to have the db do the search, then look in to full text to do the searching for you. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
On Wed, 2010-03-31 at 16:20 +0430, Parham Doustdar wrote:
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than the
file starting to download. This is supposed to be a file download counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file'] .
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
What is the output of $query?
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] MySQL query not working!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than the
file starting to download. This is supposed to be a file download counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file'] .
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
Andre,
The @ operator is used for error catching statements. When you put:
@mysql_connect('localhost', 'username', 'password') or die('Could not
connect.');
If PHP fails to make a connection, the script execution is stopped, and the
error message between the apostrophes is given.
And, I found out what the problem was; I should have put:
if (mysql_num_rows($result))
rather than just
if ($result)
Thanks!
- Original Message -
From: Andre Polykanine an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
Sent: Wednesday, March 31, 2010 4:41 PM
Subject: Re: [PHP] MySQL query not working!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please
note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than
the
file starting to download. This is supposed to be a file download counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file']
.
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
Hi!
To view error:
you must use mysql_query(). @ before mysql - error supression.
next
you can use the following:
$result=..
if($result){
if(mysql_num_rows($result)){
/* you have record in table */
}else{
/* you haven't */
On Wed, Mar 31, 2010 at 4:11 PM, Andre Polykanine an...@oire.org wrote:
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than
the
file starting to download. This is supposed to be a file download counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file']
.
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
With regards,
Alexei Bovanenko
Re: [PHP] MySQL query not working!
On Wed, 2010-03-31 at 16:50 +0430, Parham Doustdar wrote:
Andre,
The @ operator is used for error catching statements. When you put:
@mysql_connect('localhost', 'username', 'password') or die('Could not
connect.');
If PHP fails to make a connection, the script execution is stopped, and the
error message between the apostrophes is given.
And, I found out what the problem was; I should have put:
if (mysql_num_rows($result))
rather than just
if ($result)
Thanks!
- Original Message -
From: Andre Polykanine an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
Sent: Wednesday, March 31, 2010 4:41 PM
Subject: Re: [PHP] MySQL query not working!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please
note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than
the
file starting to download. This is supposed to be a file download counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file']
.
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
My understanding of the @ here would be that PHP won't register the
error, so it won't ever die()
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] MySQL query not working!
Yes ash.. me too think the same... @ will supress any error which would have
lead to die()... so die() wont come ever
Midhun Girish
On Wed, Mar 31, 2010 at 6:16 PM, Ashley Sheridan
a...@ashleysheridan.co.ukwrote:
On Wed, 2010-03-31 at 16:50 +0430, Parham Doustdar wrote:
Andre,
The @ operator is used for error catching statements. When you put:
@mysql_connect('localhost', 'username', 'password') or die('Could not
connect.');
If PHP fails to make a connection, the script execution is stopped, and
the
error message between the apostrophes is given.
And, I found out what the problem was; I should have put:
if (mysql_num_rows($result))
rather than just
if ($result)
Thanks!
- Original Message -
From: Andre Polykanine an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
Sent: Wednesday, March 31, 2010 4:41 PM
Subject: Re: [PHP] MySQL query not working!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon
@
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please
note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other
than
the
file starting to download. This is supposed to be a file download
counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' .
$_GET['file']
.
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = '
.
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
My understanding of the @ here would be that PHP won't register the
error, so it won't ever die()
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] MySQL query not working!
On Wed, Mar 31, 2010 at 8:46 AM, Ashley Sheridan
a...@ashleysheridan.co.uk wrote:
On Wed, 2010-03-31 at 16:50 +0430, Parham Doustdar wrote:
Andre,
The @ operator is used for error catching statements. When you put:
@mysql_connect('localhost', 'username', 'password') or die('Could not
connect.');
If PHP fails to make a connection, the script execution is stopped, and the
error message between the apostrophes is given.
And, I found out what the problem was; I should have put:
if (mysql_num_rows($result))
rather than just
if ($result)
Thanks!
- Original Message -
From: Andre Polykanine an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
Sent: Wednesday, March 31, 2010 4:41 PM
Subject: Re: [PHP] MySQL query not working!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please
note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than
the
file starting to download. This is supposed to be a file download counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file']
.
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
My understanding of the @ here would be that PHP won't register the
error, so it won't ever die()
Thanks,
Ash
http://www.ashleysheridan.co.uk
Nope. All it does is suppress the error message. Just try it:
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
Could not connect
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
br /
bWarning/b: mysql_connect() [a
href='function.mysql-connect'function.mysql-connect/a]: Can't
connect to MySQL server on 'localhost' (10061) in bPHPDocument1/b
on line b3/bbr /
Could not connect
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
hey Andrew ,
you are correct thanks for pointing tht.. i should have checked it
before so @ just prevents the warnings and errors from showing up
Midhun Girish
On Wed, Mar 31, 2010 at 6:38 PM, Andrew Ballard aball...@gmail.com wrote:
On Wed, Mar 31, 2010 at 8:46 AM, Ashley Sheridan
a...@ashleysheridan.co.uk wrote:
On Wed, 2010-03-31 at 16:50 +0430, Parham Doustdar wrote:
Andre,
The @ operator is used for error catching statements. When you put:
@mysql_connect('localhost', 'username', 'password') or die('Could not
connect.');
If PHP fails to make a connection, the script execution is stopped, and
the
error message between the apostrophes is given.
And, I found out what the problem was; I should have put:
if (mysql_num_rows($result))
rather than just
if ($result)
Thanks!
- Original Message -
From: Andre Polykanine an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
Sent: Wednesday, March 31, 2010 4:41 PM
Subject: Re: [PHP] MySQL query not working!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon
@
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please
note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other
than
the
file starting to download. This is supposed to be a file download
counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' .
$_GET['file']
.
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename =
' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
My understanding of the @ here would be that PHP won't register the
error, so it won't ever die()
Thanks,
Ash
http://www.ashleysheridan.co.uk
Nope. All it does is suppress the error message. Just try it:
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
Could not connect
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
br /
bWarning/b: mysql_connect() [a
href='function.mysql-connect'function.mysql-connect/a]: Can't
connect to MySQL server on 'localhost' (10061) in bPHPDocument1/b
on line b3/bbr /
Could not connect
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
On Wed, Mar 31, 2010 at 9:08 AM, Andrew Ballard aball...@gmail.com wrote:
Nope. All it does is suppress the error message. Just try it:
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
Could not connect
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
br /
bWarning/b: mysql_connect() [a
href='function.mysql-connect'function.mysql-connect/a]: Can't
connect to MySQL server on 'localhost' (10061) in bPHPDocument1/b
on line b3/bbr /
Could not connect
Andrew
OK, for the sake of the archives, I wish there was an EDIT feature
to this list. Copy/paste will get you every time; or, Insanity: doing
the same thing over and over again and expecting different results.
:-)
At any rate, the point I was TRYING to make is correct, even if the
example wasn't quite right.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
At 4:20 PM +0430 3/31/10, Parham Doustdar wrote:
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than the
file starting to download. This is supposed to be a file download counter:
[code]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file'] .
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
Hi Parham:
Considering that no one made comment, let me say that using $_GET in
such a fashion is dangerous. One should always clean/scrub all
variables that makeup a db query.
Doing what you did above is opening your database to possible SQL
injection. This is not a secure thing to do.
For example, let's say I provide the following string to your form (first GET):
anything OR '1' = '1'; DROP TABLE customers
If your database configuration allows for multiple statements, then
any table named customers would be immediately dropped from your
database. I'm sure you can see how you would not want to allow
someone to drop tables from your database. In short, never trust
anything coming from client-side.
Here's a reference on the subject:
http://en.wikipedia.org/wiki/SQL_injection
There are many others.
Cheers,
tedd
--
---
http://sperling.com http://ancientstones.com http://earthstones.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL: Return Number of Matched Rows
On Mar 25, 2010, at 5:10 PM, James Colannino wrote: Hey everyone, I have a question. If I do a mysql query that updates a column in a row to the same value, I get 0 rows affected. However, I also get 1 or more matched rows. Is there a way that I can return the number of matched rows, rather than the number of rows affected? I'm trying to get something done with as few SQL queries as possible. Thanks! James As for as I know, MySQL simply just doesn't report a row as being affected if nothing has changed in it. To get the number of matched rows, try doing a SELECT query before you do the UPDATE query. I don't know if that will produce the results you're looking for, but it might. Take care, Floyd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL: Return Number of Matched Rows
Floyd Resler wrote: As for as I know, MySQL simply just doesn't report a row as being affected if nothing has changed in it. To get the number of matched rows, try doing a SELECT query before you do the UPDATE query. I don't know if that will produce the results you're looking for, but it might. Yeah, the extra select is what I was hoping to avoid :-P The MySQL client will return both the number of rows matched and the number of rows affected by the query; I was hoping perhaps the PHP API offered a way for me to do the same. Ah well... Thanks! James -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL: Return Number of Matched Rows
On Thu, Mar 25, 2010 at 9:19 PM, James Colannino ja...@colannino.orgwrote: Yeah, the extra select is what I was hoping to avoid :-P The MySQL client will return both the number of rows matched and the number of rows affected by the query; I was hoping perhaps the PHP API offered a way for me to do the same. Ah well... Thanks! You can access data about the latest query via mysql_info() http://php.net/manual/en/function.mysql-info.php You can parse the string for the values you need. There may be a more elegant solution.
[PHP] Re: PHP / mySQL Project...
Hi Don, i work for the company simplynetworks in germany. I have access to may programmers with the best quality to the best prices. We work quick and no dirty ;-) I am programmer too and my company offer you the best object oriented software of the market. Some references of my clients in Germany: DMC (Digital media center) - Neckermann (www.neckerman.de /nl/be) Shop development - 150 developer and many smoll teams. Development with PHP 4 and 5, JQuery, Prototype, CSS, XML, HTML, MYSQL and Oracle and so on. Astroshop.de (www.astroshop.de) Shop redesign and refactory. JQuery, PHP5 strong object oriented, SPL, MySQL, Zend Framework and EzComponents integration. ssc - services - Daimler Chrysler (SWAN Projekt for OFTP data transfer). PHP5 and Java, MySQL, HTML, CSS, Javascript,etc Speechconcept (linguistics) - Strong object oriented Software with DOJO, Zend Framework and many modules and very complex tasks. If you are interessing contact please to this email address. Regards Carlos Medina Don Wieland schrieb: Hello, I am needing assistance IMMEDIATELY in finishing up a project (the developer went in to have shoulder surgery and will be out of commission for 3 weeks) and I need this finished soon. Candidate must have good english skills, a solid knowledge of HTML, CSS, PHP, mySQL, Javascript, AJAX, and JQuery. Developer may work remotely. Please contact me via email, PRIVATELY, with your skills and sample of online project you have done. Also, this will be an hourly job - so what Hourly Rate you expect to get paid would be nice. Thanks! Don Wieland D W D a t a C o n c e p t s ~ d...@dwdataconcepts.com Direct Line - (949) 305-2771 Integrated data solutions to fit your business needs. Need assistance in dialing in your FileMaker solution? Check out our Developer Support Plan at: http://www.dwdataconcepts.com/DevSup.html Appointment 1.0v9 - Powerful Appointment Scheduling for FileMaker Pro 9 or higher http://www.appointment10.com For a quick overview - http://www.appointment10.com/Appt10_Promo/Overview.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Try putting tick marks (`) around the field and table names. So your SQL query would then look like: INSERT INTO `history` (`v_id`, `hour`, `visits`, `date`) VALUES (45, 0, 59, '2010 01 27'); This is a good practice to get into. The problem is that MySQL allows you to create tables and fields with the same name as functions. If the tick marks are not there, then it assumes you mean to try using the function. In your case, hour is a function in mysql. I would assume that the reason it works in phpmyadmin is that it filters the query somehow to add the tick marks in. Joseph james stojan wrote: I'm at my wits end trying to make this mysql statement insert work in PHP. I'm not getting any errors from PHP or mysql but the insert fails (nothing is inserted) error reporting is on and is reporting other errors. When I echo out the query and manually paste it into PHP myAdmin the query inserts without a problem. I know that I am connecting to the database as well part of the data being inserted comes from the same database and that the mysql user has permission to do inserts (even tried as root no luck). $query=INSERT INTO upload_history (v_id,hour,visits,date) VALUES (.$v_id.,.$hour.,.$visits.,'$date1'.);; $r2=mysql_query($query) or die(bA fatal MySQL error occured/b.\nbr /Query: . $query . br /\nError: ( . mysql_errno() . ) . mysql_error()); This is an echo of $query and runs in phpmyadmin. INSERT INTO history (v_id,hour,visits,date) VALUES (45,0,59,'2010 01 27'); Any idea what is going on here? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
james stojan wrote on 11/02/2010 22:21: $query=INSERT INTO upload_history (v_id,hour,visits,date) VALUES (.$v_id.,.$hour.,.$visits.,'$date1'.);; The ,'$date1'. is not correct syntax, change it to ,'.$date.' -- Kind regards Kim Emax - masterminds.dk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Also, in PHP you should NOT put the last semi-colon at the end of your SQL statement. http://www.php.net/manual/en/function.mysql-query.php On Feb 11, 2010, at 1:26 PM, Joseph Thayne wrote: Try putting tick marks (`) around the field and table names. So your SQL query would then look like: INSERT INTO `history` (`v_id`, `hour`, `visits`, `date`) VALUES (45, 0, 59, '2010 01 27'); This is a good practice to get into. The problem is that MySQL allows you to create tables and fields with the same name as functions. If the tick marks are not there, then it assumes you mean to try using the function. In your case, hour is a function in mysql. I would assume that the reason it works in phpmyadmin is that it filters the query somehow to add the tick marks in. Joseph james stojan wrote: I'm at my wits end trying to make this mysql statement insert work in PHP. I'm not getting any errors from PHP or mysql but the insert fails (nothing is inserted) error reporting is on and is reporting other errors. When I echo out the query and manually paste it into PHP myAdmin the query inserts without a problem. I know that I am connecting to the database as well part of the data being inserted comes from the same database and that the mysql user has permission to do inserts (even tried as root no luck). $query=INSERT INTO upload_history (v_id,hour,visits,date) VALUES (.$v_id.,.$hour.,.$visits.,'$date1'.);; $r2=mysql_query($query) or die(bA fatal MySQL error occured/b.\nbr /Query: . $query . br /\nError: ( . mysql_errno() . ) . mysql_error()); This is an echo of $query and runs in phpmyadmin. INSERT INTO history (v_id,hour,visits,date) VALUES (45,0,59,'2010 01 27'); Any idea what is going on here? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Thank you. You were right on the money, hour was the problem and the tick marks solved it. I spent 3 hours trying to figure out why I never got an error but there was no insert and php myadmin does add the tick marks automatically. Probably a good habit to always use the tick marks. Learn something new everyday. On Thu, Feb 11, 2010 at 4:26 PM, Joseph Thayne webad...@thaynefam.orgwrote: Try putting tick marks (`) around the field and table names. So your SQL query would then look like: INSERT INTO `history` (`v_id`, `hour`, `visits`, `date`) VALUES (45, 0, 59, '2010 01 27'); This is a good practice to get into. The problem is that MySQL allows you to create tables and fields with the same name as functions. If the tick marks are not there, then it assumes you mean to try using the function. In your case, hour is a function in mysql. I would assume that the reason it works in phpmyadmin is that it filters the query somehow to add the tick marks in. Joseph james stojan wrote: I'm at my wits end trying to make this mysql statement insert work in PHP. I'm not getting any errors from PHP or mysql but the insert fails (nothing is inserted) error reporting is on and is reporting other errors. When I echo out the query and manually paste it into PHP myAdmin the query inserts without a problem. I know that I am connecting to the database as well part of the data being inserted comes from the same database and that the mysql user has permission to do inserts (even tried as root no luck). $query=INSERT INTO upload_history (v_id,hour,visits,date) VALUES (.$v_id.,.$hour.,.$visits.,'$date1'.);; $r2=mysql_query($query) or die(bA fatal MySQL error occured/b.\nbr /Query: . $query . br /\nError: ( . mysql_errno() . ) . mysql_error()); This is an echo of $query and runs in phpmyadmin. INSERT INTO history (v_id,hour,visits,date) VALUES (45,0,59,'2010 01 27'); Any idea what is going on here?
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Actually, the syntax is just fine. I personally would prefer it the way you mention, but there actually is nothing wrong with the syntax. The ,'$date1'. is not correct syntax, change it to ,'.$date.' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 8:27 AM, Joseph Thayne webad...@thaynefam.org wrote:
Actually, the syntax is just fine. I personally would prefer it the way you
mention, but there actually is nothing wrong with the syntax.
The ,'$date1'. is not correct syntax, change it to ,'.$date.'
My personal preference these days is to use Curly braces around
variables in strings such as this, I always find excessive string
concatenation such as is often used when building SQL queries hard to
read, and IIRC there was performance implications to it as well
(though I don't have access to concrete stats right now).
In your case, the variable would be something like this:
$query=INSERT INTO upload_history (v_id,hour,visits,date) VALUES
({$v_id}, {$hour}, {$visits}, '{$date}');
Much more readable and maintainable IMO.
No need for the trailing semicolon in SQL that uses an API like you
are using so save another char there too.
Backticks around column names are not required and IMO again they just
make the code hard to read. Just because phpMyAdmin uses them, doesn't
mean we all need to.
Cheers
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
That is a good idea to use the curly braces. I consistently forget
about them, and fell like an idiot every time I am reminded of them.
As for the backticks, they are required because of MySQL, not because of
phpMyAdmin. The issue was not that phpMyAdmin uses backticks, it is
that MySQL pretty much requires them when naming a field the same as an
internal function to my knowledge. If someone else knows of another way
to designate to MySQL that a field named HOUR is the name of a field
rather than the name of the internal function, I would love to know.
James McLean wrote:
On Fri, Feb 12, 2010 at 8:27 AM, Joseph Thayne webad...@thaynefam.org wrote:
Actually, the syntax is just fine. I personally would prefer it the way you
mention, but there actually is nothing wrong with the syntax.
The ,'$date1'. is not correct syntax, change it to ,'.$date.'
My personal preference these days is to use Curly braces around
variables in strings such as this, I always find excessive string
concatenation such as is often used when building SQL queries hard to
read, and IIRC there was performance implications to it as well
(though I don't have access to concrete stats right now).
In your case, the variable would be something like this:
$query=INSERT INTO upload_history (v_id,hour,visits,date) VALUES
({$v_id}, {$hour}, {$visits}, '{$date}');
Much more readable and maintainable IMO.
No need for the trailing semicolon in SQL that uses an API like you
are using so save another char there too.
Backticks around column names are not required and IMO again they just
make the code hard to read. Just because phpMyAdmin uses them, doesn't
mean we all need to.
Cheers
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Op 2/11/10 10:51 PM, James McLean schreef:
On Fri, Feb 12, 2010 at 8:27 AM, Joseph Thayne webad...@thaynefam.org wrote:
Actually, the syntax is just fine. I personally would prefer it the way you
mention, but there actually is nothing wrong with the syntax.
The ,'$date1'. is not correct syntax, change it to ,'.$date.'
My personal preference these days is to use Curly braces around
variables in strings such as this, I always find excessive string
concatenation such as is often used when building SQL queries hard to
read, and IIRC there was performance implications to it as well
(though I don't have access to concrete stats right now).
In your case, the variable would be something like this:
$query=INSERT INTO upload_history (v_id,hour,visits,date) VALUES
({$v_id}, {$hour}, {$visits}, '{$date}');
actually IIRC the engine compiles that to OpCodes that equate to:
$query = 'INSERT INTO upload_history (v_id,hour,visits,date) VALUES ('.$v_id.',
'.$hour.', '.$visits.', '\''.{$date}.'\')';
Much more readable and maintainable IMO.
No need for the trailing semicolon in SQL that uses an API like you
are using so save another char there too.
Backticks around column names are not required and IMO again they just
make the code hard to read. Just because phpMyAdmin uses them, doesn't
mean we all need to.
Cheers
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 9:31 AM, Jochem Maas joc...@iamjochem.com wrote:
Op 2/11/10 10:51 PM, James McLean schreef:
My personal preference these days is to use Curly braces around
variables in strings such as this, I always find excessive string
concatenation such as is often used when building SQL queries hard to
read, and IIRC there was performance implications to it as well
(though I don't have access to concrete stats right now).
In your case, the variable would be something like this:
$query=INSERT INTO upload_history (v_id,hour,visits,date) VALUES
({$v_id}, {$hour}, {$visits}, '{$date}');
actually IIRC the engine compiles that to OpCodes that equate to:
$query = 'INSERT INTO upload_history (v_id,hour,visits,date) VALUES
('.$v_id.', '.$hour.', '.$visits.', '\''.{$date}.'\')';
Interesting point, but the original code is still more readable, the
opcode's aren't our problem (at least in this case) :)
Cheers
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 9:31 AM, Joseph Thayne webad...@thaynefam.org wrote: As for the backticks, they are required because of MySQL, not because of phpMyAdmin. The issue was not that phpMyAdmin uses backticks, it is that MySQL pretty much requires them when naming a field the same as an internal function to my knowledge. If someone else knows of another way to designate to MySQL that a field named HOUR is the name of a field rather than the name of the internal function, I would love to know. Ahh I see :) Wasn't aware of that. Personally i've always been over-descriptive when designing my tables which is possibly why I've never run into that limitation :) Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
Yeah, I am a lot more descriptive now. I ran into it quite a bit when I was first starting out. James McLean wrote: On Fri, Feb 12, 2010 at 9:31 AM, Joseph Thayne webad...@thaynefam.org wrote: As for the backticks, they are required because of MySQL, not because of phpMyAdmin. The issue was not that phpMyAdmin uses backticks, it is that MySQL pretty much requires them when naming a field the same as an internal function to my knowledge. If someone else knows of another way to designate to MySQL that a field named HOUR is the name of a field rather than the name of the internal function, I would love to know. Ahh I see :) Wasn't aware of that. Personally i've always been over-descriptive when designing my tables which is possibly why I've never run into that limitation :) Thanks.
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 09:44:47AM +1030, James McLean wrote: On Fri, Feb 12, 2010 at 9:31 AM, Joseph Thayne webad...@thaynefam.org wrote: As for the backticks, they are required because of MySQL, not because of phpMyAdmin. The issue was not that phpMyAdmin uses backticks, it is that MySQL pretty much requires them when naming a field the same as an internal function to my knowledge. If someone else knows of another way to designate to MySQL that a field named HOUR is the name of a field rather than the name of the internal function, I would love to know. Backticks are also required to preserve casing in MySQL, if you name something in mixed or upper case; MySQL lowercases table and field names otherwise. It's a silly misfeature of MySQL. I can't conceive of why a DBMS would assume something which should be understood in the context of a field name should instead be interpreted as a function call. Buy maybe that's just me. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql statement works in phpmyadmin but not in php page
I was going to write an example as to what should happen instead of what
actually does when id dawned on me why MySQL works the way it does. One of
the biggest complaints people have with MySQL is in speed. To demonstrate
what I just realized, take the following statement that will select the hour
from a given time as well as the value from the hour field:
SELECT HOUR('13:42:37') as thehour, hour FROM mytable;
Not a big deal and pretty straight forward. What about the following?
SELECT HOUR(mydate) as thehour, hour FROM mytable;
Still pretty simple to determine which are the functions and which are the
field names. However, take the following:
SELECT HOUR(NOW()) as thehour, hour FROM mytable;
As humans, glancing at it, it makes perfect sense to us as to which is
which. However, try telling a computer how to interpret the above
statement. You could look for parenthesis. That would work fine on the
first two statements, but once you get to the third, you have to worry about
recursion and all possible permutations of the data that could come through.
This exponentially increases the complexity and processing time/power
required to run the query. Granted, that query is a simple one, but plug it
into a query filled with multiple joins, and you have the potential of a
nightmare. So why focus on adding in functionality that adds so much
complexity and will end up requiring that much extra support when a simple
character (the tick mark) will take care of the work for you and you can
then focus on other things such as data integrity and general processing
speed?
Joseph
-Original Message-
From: Paul M Foster [mailto:pa...@quillandmouse.com]
Sent: Thursday, February 11, 2010 9:15 PM
To: php-general@lists.php.net
Subject: Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Fri, Feb 12, 2010 at 09:44:47AM +1030, James McLean wrote:
On Fri, Feb 12, 2010 at 9:31 AM, Joseph Thayne webad...@thaynefam.org
wrote:
As for the backticks, they are required because of MySQL, not because of
phpMyAdmin. The issue was not that phpMyAdmin uses backticks, it is
that
MySQL pretty much requires them when naming a field the same as an
internal
function to my knowledge. If someone else knows of another way to
designate
to MySQL that a field named HOUR is the name of a field rather than the
name
of the internal function, I would love to know.
Backticks are also required to preserve casing in MySQL, if you name
something in mixed or upper case; MySQL lowercases table and field names
otherwise. It's a silly misfeature of MySQL.
I can't conceive of why a DBMS would assume something which should be
understood in the context of a field name should instead be interpreted
as a function call. Buy maybe that's just me.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql statement works in phpmyadmin but not in php page
On Thu, Feb 11, 2010 at 09:49:02PM -0600, Joseph Thayne wrote:
I was going to write an example as to what should happen instead of what
actually does when id dawned on me why MySQL works the way it does. One of
the biggest complaints people have with MySQL is in speed.
The much-vaunted speed of MySQL is the biggest complaint? Sheesh.
To demonstrate
what I just realized, take the following statement that will select the hour
from a given time as well as the value from the hour field:
SELECT HOUR('13:42:37') as thehour, hour FROM mytable;
Not a big deal and pretty straight forward. What about the following?
SELECT HOUR(mydate) as thehour, hour FROM mytable;
Still pretty simple to determine which are the functions and which are the
field names. However, take the following:
SELECT HOUR(NOW()) as thehour, hour FROM mytable;
As humans, glancing at it, it makes perfect sense to us as to which is
which. However, try telling a computer how to interpret the above
statement. You could look for parenthesis. That would work fine on the
first two statements, but once you get to the third, you have to worry about
recursion and all possible permutations of the data that could come through.
This exponentially increases the complexity and processing time/power
required to run the query. Granted, that query is a simple one, but plug it
into a query filled with multiple joins, and you have the potential of a
nightmare. So why focus on adding in functionality that adds so much
complexity and will end up requiring that much extra support when a simple
character (the tick mark) will take care of the work for you and you can
then focus on other things such as data integrity and general processing
speed?
I understand what you're saying, and you may be right about why MySQL
was built this way. However, it's like telling the programmers not to
build a better parser; just make the user backtick stuff so we don't
have to write a proper parser. For a one-off script only I was going to
use, I'd do this. But not for a professional level product used by
millions, speed or no speed. Imagine if KR had tried to shortcut the C
parser this way; the C parser is almost endlessly re-entrant and must
accommodate some seriously obfuscated code. Which it does reliably.
Besides, if you've got a parser which understands joins, parsing things
like the distinction between hour (field name) and hour (function call)
is a piece of cake.
If a programmer working for me tried to pawn this off as a done, I'd
make him redo it. Again, maybe it's just me.
Anyway, we're way off topic
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL ID -- what happens when you run out of range?
It will continue to use the max number which of course will cause an error. Joseph Parham Doustdar wrote: Hello there, A friend called me today and was wondering what happens if the ID colomn of an MYSQL database, set to autoinc reaches the int limit. Will it return and begin choosing the ID's that have been deleted, or... what? Thanks! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
