Re: [PHP] mysql custom global defined variable

2013-03-13 Thread Marco Behnke
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

2013-03-13 Thread Camilo Sperberg

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

2012-03-22 Thread Jim Giner
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

2012-03-22 Thread Chris Stinemetz
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

2012-03-22 Thread Bastien


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

2012-03-22 Thread Stuart Dallas
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

2012-03-19 Thread Stuart Dallas
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

2012-03-19 Thread Tom Sparks
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

2012-03-19 Thread Tom Sparks
--- 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

2012-03-19 Thread Govinda
>> 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

2012-03-19 Thread Ashley Sheridan
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

2012-03-19 Thread Tom Sparks
--- 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

2012-03-19 Thread Ashley Sheridan
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

2012-03-05 Thread Tommy Pham
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

2012-03-05 Thread php-list
> > 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

2012-02-27 Thread Tommy Pham
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

2011-09-17 Thread shahrzad khorrami
mysqli - it's what I wanted. thanks


Fwd: Re: [PHP] mysql adapter and DAL

2011-09-17 Thread Nilesh Govindarajan
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

2011-09-17 Thread jean-baptiste verrey
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

2011-05-16 Thread Bálint Horváth
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]

2011-05-14 Thread Jasper Mulder

>[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]

2011-05-14 Thread Curtis Maurand


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

2011-05-12 Thread Curtis Maurand



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

2011-05-12 Thread Tim Streater
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

2011-05-11 Thread Sean Greenslade
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

2011-05-11 Thread Curtis Maurand


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

2011-05-11 Thread Curtis Maurand


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

2011-05-11 Thread Marc Guay
> 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

2011-05-10 Thread xianhua zhou
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

2011-05-06 Thread 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
> 
>


Re: [PHP] mysql error

2011-05-05 Thread Daniel Brown
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

2011-05-05 Thread Andre Polykanine
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

2011-02-18 Thread Tommy Pham
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

2011-02-18 Thread Tommy Pham
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

2011-01-24 Thread Tom Rogers
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

2011-01-24 Thread Tommy Pham
> -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...

2010-11-29 Thread Daniel P. Brown
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...

2010-11-29 Thread Daniel P. Brown
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

2010-11-26 Thread Steve Staples
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

2010-11-22 Thread Simcha Younger
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

2010-11-21 Thread Ben Miller


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

2010-11-21 Thread PW
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

2010-11-20 Thread Simcha Younger
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

2010-11-20 Thread admin
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

2010-11-20 Thread Richard West
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)

2010-11-19 Thread Andre Polykanine
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)

2010-11-16 Thread Tommy Pham
> -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)

2010-11-16 Thread a...@ashleysheridan.co.uk
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

2010-07-20 Thread Simcha Younger
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

2010-07-20 Thread Shreyas Agasthya
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

2010-07-19 Thread Jim Lucas

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

2010-07-19 Thread Shreyas Agasthya
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

2010-07-19 Thread Shreyas Agasthya
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

2010-07-19 Thread Richard Quadling
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

2010-07-19 Thread Richard Quadling
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

2010-07-19 Thread Peter

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

2010-07-19 Thread shiplu
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

2010-07-18 Thread Shafiq Rehman
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

2010-07-18 Thread Paul M Foster
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

2010-06-28 Thread David McGlone
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

2010-06-28 Thread Andrew Ballard
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

2010-06-28 Thread David McGlone
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

2010-06-28 Thread David McGlone
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

2010-06-28 Thread Andrew Ballard
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

2010-06-27 Thread Brandon Rampersad
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

2010-06-27 Thread David McGlone
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

2010-06-04 Thread tedd

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

2010-06-04 Thread Ashley Sheridan
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

2010-06-04 Thread Tanel Tammik

"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

2010-06-04 Thread Ashley Sheridan
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

2010-06-04 Thread Tanel Tammik

"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

2010-06-04 Thread Ashley Sheridan
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

2010-06-04 Thread Tanel Tammik

"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

2010-06-04 Thread Ashley Sheridan
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!

2010-03-31 Thread tedd

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!

2010-03-31 Thread Andrew Ballard
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!

2010-03-31 Thread Midhun Girish
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!

2010-03-31 Thread Andrew Ballard
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!

2010-03-31 Thread Midhun Girish
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!

2010-03-31 Thread Ashley Sheridan
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!

2010-03-31 Thread Alexey Bovanenko
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!

2010-03-31 Thread Parham Doustdar

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!

2010-03-31 Thread Andre Polykanine
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!

2010-03-31 Thread Ashley Sheridan
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

2010-03-25 Thread Yousif Masoud
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

2010-03-25 Thread James Colannino
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

2010-03-25 Thread Floyd Resler

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...

2010-02-22 Thread Carlos Medina

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

2010-02-11 Thread Paul M Foster
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

2010-02-11 Thread Joseph Thayne
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

2010-02-11 Thread Paul M Foster
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

2010-02-11 Thread Joseph Thayne
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

2010-02-11 Thread James McLean
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

2010-02-11 Thread James McLean
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

2010-02-11 Thread Jochem Maas
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

2010-02-11 Thread Joseph Thayne
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

2010-02-11 Thread James McLean
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

2010-02-11 Thread Joseph Thayne


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

2010-02-11 Thread james stojan
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

2010-02-11 Thread Mari Masuda
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



  1   2   3   4   5   6   7   8   9   10   >