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 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 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 table design
On Thu, Mar 22, 2012 at 7:42 PM, Stuart Dallas 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
On 2012-03-22, at 8:10 PM, Chris Stinemetz 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 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 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 "";
> }
> "
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 '';
$column = 1;
while ($row = mysql_fetch_assoc($result))
{
if ($column == 1) {
echo ''.PHP_EOL;
}
echo ' '.$row['LastName'].' '.$row['FirstName'].'
'.$row['CustomNo'].'';
$column++;
if ($column > 2) {
echo ''.PHP_EOL;
$column = 1;
}
}
if ($column == 2) {
echo ' '.PHP_EOL;
}
echo '';
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 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 wrote:
> From: Tom Sparks
> Subject: Re: [PHP] mysql list to two-column list
> To: a...@ashleysheridan.co.uk
> Cc: "php-general"
> Received: Tuesday, 20 March, 2012, 10:41 AM
> --- On Tue, 20/3/12, Ashley Sheridan
>
> wrote:
>
> From: Ashley Sheridan
> Subject: Re: [PHP] mysql list to two-column list
> To: "Tom Sparks"
> Cc: "php-general"
> 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
> 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 "";
> >> }
> >>"
>
> >>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 Tue, 20/3/12, Ashley Sheridan wrote:
From: Ashley Sheridan
Subject: Re: [PHP] mysql list to two-column list
To: "Tom Sparks"
Cc: "php-general"
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 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 "";
>> }
>>"
>>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
>> 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 Mon, 2012-03-19 at 16:09 -0700, Tom Sparks wrote:
> --- On Tue, 20/3/12, Ashley Sheridan 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 "";
> >> }
> >>"
>
> >>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
--- On Tue, 20/3/12, Ashley Sheridan 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 "";
>> }
>>"
>>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 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 "";
> }
> "
>
> 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 over TCP results on CLOSE_WAIT state in PHP 5.3.8
On Mon, Mar 5, 2012 at 7:33 AM, 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
> > 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, Feb 27, 2012 at 4:06 AM, 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: Re: [PHP] mysql adapter and DAL
mysqli - it's what I wanted. thanks
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 To: shahrzad khorrami 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: [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 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 >
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 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]
>[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: [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: Re: [PHP] mysql problems
Tim Streater wrote: > On 11 May 2011 at 19:25, Curtis Maurand 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: Re: [PHP] mysql problems
On 11 May 2011 at 19:25, Curtis Maurand 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: [PHP] mysql problems
On Wed, May 11, 2011 at 2:25 PM, Curtis Maurand 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 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
_subTotal;
$_counter++;
}
$_cartTotal = "$".number_format($_getSubTotal,2);
$_cartCount = $_counter;
mysql_close($dbhandle);
}
tallyCart($_SESSION["u_id"]);
?>
You have items in your
cart.
Cart total:
� Go to
cart
";
//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
MySQL";
//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;
}
?>
0){
global $_parents;
global $username;
global $password;
global $hostname;
$dbhandle3 = mysql_connect($hostname, $username,
$password)
or
die("Unable to connect to MySQL");
$selected = mysql_select_db("pinetree",$dbhandle3)
or die("Could not
select examples");
//execute the SQL query and return
records
if($_parent!="0"){
$result = mysql_query("SELECT catagory_ID,
catagory_name, catagory_parent FROM t_catagories where
catagory_parent=".$_parent ." ORDER BY catagory_name");
}else{
$result = mysql_query("SELECT
catagory_ID, catagory_name, catagory_parent FROM t_catagories where
catagory_parent=".$_parent);
}
//fetch tha data from the database
if($_parent=="0"){
echo "";
}else{
if($_style!=""){
echo "";
}else{
echo "";
}
}
while ($row = mysql_fetch_array($result)) {
$_cat_id=$row{'catagory_ID'};
$_match_2="+";
foreach($_parents as $id){
if($id== $_cat_id){
$_match_2="-";
}
}
if($_cat_id==$_GET["cat"]){
$_match_2="-";
}
if($row{'catagory_parent'}=="0"){
echo
"";
}else{
if(getRowCount($row{'catagory_ID'})!="0"){
echo "";
echo "";
}else{
echo
" ";
}
}
echo "" .
$row{'catagory_name'}."";
echo
"";
//generateNav($_cat_id);
$_match=0;
foreach($_parents as $id){
if($id== $_cat_id){
generateNav($_cat_id,"");
$_match=1;
}
}
if($_cat_id==$_GET["cat"] && $_match==0){
generateNav($_cat_id,"");
}else{
if($row{'catagory_parent'}!="0"){
generateNav($_cat_id,"none");
}
}
}
if($row{'catagory_parent'}=="0"){
echo
"";
}else{
echo "";
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
_subTotal;
$_counter++;
}
$_cartTotal = "$".number_format($_getSubTotal,2);
$_cartCount = $_counter;
mysql_close($dbhandle);
}
tallyCart($_SESSION["u_id"]);
?>
You have items in your
cart.
Cart total:
� Go to
cart
";
//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
MySQL";
//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;
}
?>
0){
global $_parents;
global $username;
global $password;
global $hostname;
$dbhandle3 = mysql_connect($hostname, $username,
$password)
or
die("Unable to connect to MySQL");
$selected = mysql_select_db("pinetree",$dbhandle3)
or die("Could not
select examples");
//execute the SQL query and return
records
if($_parent!="0"){
$result = mysql_query("SELECT catagory_ID,
catagory_name, catagory_parent FROM t_catagories where
catagory_parent=".$_parent ." ORDER BY catagory_name");
}else{
$result = mysql_query("SELECT
catagory_ID, catagory_name, catagory_parent FROM t_catagories where
catagory_parent=".$_parent);
}
//fetch tha data from the database
if($_parent=="0"){
echo "";
}else{
if($_style!=""){
echo "";
}else{
echo "";
}
}
while ($row = mysql_fetch_array($result)) {
$_cat_id=$row{'catagory_ID'};
$_match_2="+";
foreach($_parents as $id){
if($id== $_cat_id){
$_match_2="-";
}
}
if($_cat_id==$_GET["cat"]){
$_match_2="-";
}
if($row{'catagory_parent'}=="0"){
echo
"";
}else{
if(getRowCount($row{'catagory_ID'})!="0"){
echo "";
echo "";
}else{
echo
" ";
}
}
echo "" .
$row{'catagory_name'}."";
echo
"";
//generateNav($_cat_id);
$_match=0;
foreach($_parents as $id){
if($id== $_cat_id){
generateNav($_cat_id,"");
$_match=1;
}
}
if($_cat_id==$_GET["cat"] && $_match==0){
generateNav($_cat_id,"");
}else{
if($row{'catagory_parent'}!="0"){
generateNav($_cat_id,"none");
}
}
}
if($row{'catagory_parent'}=="0"){
echo
"";
}else{
echo "";
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 error
Hi Grega, Probably you wanna define a primary key, if so, then change from "uniqueid" to "primary key". 2011/5/6 Curtis Maurand : > > > > 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
On Thu, May 5, 2011 at 10:29, 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 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. -- 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 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 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 5.5 and PHP Mysql API Version 5.1.41
BTW, how is the traffic on your site? What's the max connection limit and timeout set on the MySQL server? [1] from searching [2] might be the solution to your problem. Good luck, Tommy [1] http://forums.mysql.com/read.php?52,364493,364831#msg-364831 [2] http://www.google.com/search?q=php+SQLSTATE[HY000]+[2013] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql 5.5 and PHP Mysql API Version 5.1.41
On Tue, Feb 15, 2011 at 9:57 AM, Matthias Laug wrote:
> Hey there,
>
> I've just migrated to Mysql 5.5 from source and it works like a charm. Still
> every now and then (in intervals of approximatly an hour) I get the following
> error:
>
> Error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2013]
> Lost connection to MySQL server at 'reading initial communication packet',
> system error: 110' in
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php:129
> Stack trace: #0
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php(129):
> PDO->__construct('mysql:port=6664...', '#', '#', Array) #1
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Mysql.php(96):
> Zend_Db_Adapter_Pdo_Abstract->_connect() #2
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Abstract.php(448):
> Zend_Db_Adapter_Pdo_Mysql->_connect() #3
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php(238):
> Zend_Db_Adapter_Abstract->query('SET NAMES 'utf8...', Array) #4
> /home/ydadmin/build/lieferando.de/11720/application/Bootstrap.php(99):
> Zend_Db_Adapter_Pdo_Abstract->query('SET NAMES 'utf8...') #5
> /home/ydadmin/build/lieferando.de/11720/library/Zend/App in
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php
> on line 144
>
> It is like any queue is running full and this is the result, but actually no
> clue. My first guess is the API Version of the mysql client I am using with
> the precompiled php binaries (Ubuntu 10.10 Server, PHP 5.3.2-1ubuntu4.7 with
> Suhosin-Patch (cli) (built: Jan 12 2011 18:36:55))
>
> Does anyone else have the same problems?
>
> Thanks for any help,
> Matthias
> --
Have you tried a basic test w/o framework like this to ensure that
both installed versions of PHP & MySQL are working properly as
expected?
Test connection for sample data:
Penelope Guiness
Nick Wahlberg
Platform info:
Server Info: 5.5.7-rc-log
Client Info: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
Webserver: Microsoft-IIS/7.5
PHP: 5.3.5-cgi-fcgi
Test connection for sample data: '.PHP_EOL;
$dbh = new PDO( $dsn, $user, $passwd );
foreach( $dbh->query( $sql ) as $row )
{
echo ucfirst( strtolower( $row['first_name'] ) ).' '
.ucfirst( strtolower( $row['last_name'] ) ).PHP_EOL;
}
echo PHP_EOL.'Platform info: '.PHP_EOL;
$mysqli = new mysqli( $host, $user, $passwd, $dbname );
echo 'Server Info: '.$mysqli->server_info.PHP_EOL;
echo 'Client Info: '.$mysqli->client_info.PHP_EOL;
$mysqli->close();
echo 'Webserver: '.$_SERVER['SERVER_SOFTWARE'].PHP_EOL;
echo 'PHP: '.PHP_VERSION.'-'.PHP_SAPI.PHP_EOL;
show_source(__FILE__);
Note: The MySQL server is my own Win x64 compilation. Nevertheless,
PHP shouldn't have any problems with MySQL 5.5.
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 1>0 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 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 1>0 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 query assistance...
On Mon, Nov 29, 2010 at 14:40, Daniel P. Brown wrote: > For your convenience, both have been CC'd on this email. Actually, PHP-DB (php...@lists.php.net) was accidentally BCC'd. -- 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:35, Don Wieland 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. -- 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
On Sun, 21 Nov 2010 11:19:04 -0700 "Ben Miller" wrote: > > 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 > > You should probably select all relevant rows for each product, without checking that a given criteria has a matching value for each product. Use php to filter out the criteria which do not apply to all products. -- Simcha Younger -- 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 wrote:
> On Sat, 20 Nov 2010 13:54:29 -0700
> "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.
>
> 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
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 wrote:
> On Sat, 20 Nov 2010 13:54:29 -0700
> "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.
>
> 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
>
> --
> 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" 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
--
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
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 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 To: 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)
> -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 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" Date: Tue, Nov 16, 2010 13:35 Subject: [PHP] mysql help (sorry, a bit OT) To: 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 select matching
On Mon, 19 Jul 2010 10:36:40 -0600 "Ashley M. Kirchner" 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 -- 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 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
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
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 wrote: > 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 wrote: > >> On 19 July 2010 05:44, 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 >> > >> >> 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
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 wrote: > On 19 July 2010 05:44, 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 > > > > 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
On 19 July 2010 15:01, Richard Quadling wrote: > On 19 July 2010 05:44, 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.. >> 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
On 19 July 2010 05:44, 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 > 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
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
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 Mon, Jul 19, 2010 at 10:44 AM, 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 > 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 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 case statement
On Monday 28 June 2010 11:14:53 Andrew Ballard wrote: > On Mon, Jun 28, 2010 at 10:27 AM, David McGlone 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 Mon, Jun 28, 2010 at 10:27 AM, David McGlone 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 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 Monday 28 June 2010 09:49:55 Andrew Ballard wrote:
> On Sun, Jun 27, 2010 at 4:08 AM, 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!
> >
> > 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 Sun, Jun 27, 2010 at 4:08 AM, 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!
>
> 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
no
On Sun, Jun 27, 2010 at 8:29 PM, David McGlone 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 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 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 : 'you need some styling!' 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 search html tags excluded
On Fri, 2010-06-04 at 15:00 +0300, Tanel Tammik wrote: > "Ashley Sheridan" wrote in message > news:1275652880.2217.54.ca...@localhost... > > On Fri, 2010-06-04 at 14:54 +0300, Tanel Tammik wrote: > > > >> "Ashley Sheridan" wrote in message > >> news:1275652342.2217.51.ca...@localhost... > >> > On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: > >> > > >> >> "Ashley Sheridan" 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 : 'you need some styling!' > >> >> >> > >> >> >> 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 the < & > without 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
"Ashley Sheridan" wrote in message news:1275652880.2217.54.ca...@localhost... > On Fri, 2010-06-04 at 14:54 +0300, Tanel Tammik wrote: > >> "Ashley Sheridan" wrote in message >> news:1275652342.2217.51.ca...@localhost... >> > On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: >> > >> >> "Ashley Sheridan" 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 : 'you need some styling!' >> >> >> >> >> >> 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 the < & > without 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 14:54 +0300, Tanel Tammik wrote: > "Ashley Sheridan" wrote in message > news:1275652342.2217.51.ca...@localhost... > > On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: > > > >> "Ashley Sheridan" 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 : 'you need some styling!' > >> >> > >> >> 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 the < & > without 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" wrote in message news:1275652342.2217.51.ca...@localhost... > On Fri, 2010-06-04 at 14:44 +0300, Tanel Tammik wrote: > >> "Ashley Sheridan" 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 : 'you need some styling!' >> >> >> >> 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:44 +0300, Tanel Tammik wrote: > "Ashley Sheridan" 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 : 'you need some styling!' > >> > >> 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" 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 : 'you need some styling!' >> >> 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: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 : 'you need some styling!' > > 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 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]
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 query not working!
On Wed, Mar 31, 2010 at 9:08 AM, Andrew Ballard wrote:
> Nope. All it does is suppress the error message. Just try it:
>
>
> @mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
> not connect');
>
> ?>
>
> Output:
> Could not connect
>
> @mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
> not connect');
>
> ?>
>
> Output:
>
> Warning: mysql_connect() [ href='function.mysql-connect'>function.mysql-connect]: Can't
> connect to MySQL server on 'localhost' (10061) in PHPDocument1
> on line 3
> 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!
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 wrote:
> On Wed, Mar 31, 2010 at 8:46 AM, Ashley Sheridan
> 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"
> >> To: "Parham Doustdar"
> >> Cc:
> >> 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; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon
> @
> >> > jabber.org
> >> > Yahoo! messenger: andre.polykanine; ICQ: 191749952
> >> > Twitter: m_elensule
> >> >
> >> > - Original message -
> >> > From: Parham Doustdar
> >> > To: 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]
> >> > >> > //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:
>
>
> @mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
> not connect');
>
> ?>
>
> Output:
> Could not connect
>
> @mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
> not connect');
>
> ?>
>
> Output:
>
> Warning: mysql_connect() [ href='function.mysql-connect'>function.mysql-connect]: Can't
> connect to MySQL server on 'localhost' (10061) in PHPDocument1
> on line 3
> 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 8:46 AM, Ashley Sheridan
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"
>> To: "Parham Doustdar"
>> Cc:
>> 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; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
>> > jabber.org
>> > Yahoo! messenger: andre.polykanine; ICQ: 191749952
>> > Twitter: m_elensule
>> >
>> > - Original message -
>> > From: Parham Doustdar
>> > To: 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]
>> > > > //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:
Output:
Could not connect
Output:
Warning: mysql_connect() [function.mysql-connect]: Can't
connect to MySQL server on 'localhost' (10061) in PHPDocument1
on line 3
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!
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
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"
> > To: "Parham Doustdar"
> > Cc:
> > 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; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon
> @
> > > jabber.org
> > > Yahoo! messenger: andre.polykanine; ICQ: 191749952
> > > Twitter: m_elensule
> > >
> > > - Original message -
> > > From: Parham Doustdar
> > > To: 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]
> > > > > //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, 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"
> To: "Parham Doustdar"
> Cc:
> 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; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
> > jabber.org
> > Yahoo! messenger: andre.polykanine; ICQ: 191749952
> > Twitter: m_elensule
> >
> > - Original message -
> > From: Parham Doustdar
> > To: 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]
> > > //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!
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 wrote:
> Hello Parham,
>
> Adding to Ash's question, why to use the @ operator before
> mysql_query?
> --
> With best regards from Ukraine,
> Andre
> Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
> jabber.org
> Yahoo! messenger: andre.polykanine; ICQ: 191749952
> Twitter: m_elensule
>
> - Original message -
> From: Parham Doustdar
> To: 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]
> //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!
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"
To: "Parham Doustdar"
Cc:
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; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar
To: 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]
$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!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar
To: 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]
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!
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]
> //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: Return Number of Matched Rows
On Thu, Mar 25, 2010 at 9:19 PM, James Colannino wrote: > 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.
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 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
[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
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 K&R 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 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
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 Fri, Feb 12, 2010 at 09:44:47AM +1030, James McLean wrote: > On Fri, Feb 12, 2010 at 9:31 AM, Joseph Thayne 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
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 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 9:31 AM, Joseph Thayne 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
On Fri, Feb 12, 2010 at 9:31 AM, Jochem Maas 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
Op 2/11/10 10:51 PM, James McLean schreef:
> On Fri, Feb 12, 2010 at 8:27 AM, Joseph Thayne 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
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 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
On Fri, Feb 12, 2010 at 8:27 AM, Joseph Thayne 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
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
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 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("A fatal MySQL error
>> occured.\nQuery: " . $query . "\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
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("A fatal MySQL error
>> occured.\nQuery: " . $query . "\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
