RE: [PHP] significance of escape character in string in PHP - MySQL
There's no need for it to be a flame war. The mysql extension is officially not recommended for writing new code, so anyone using it should be informed of this fact. I think it should consist of more than "don't use that," but at the very least that should cause the questioner to want to know why. http://php.net/intro.mysql This issue is problematic for exactly the reason Norah demonstrates above: "it's working." Great that in this case it hasn't been left at that, but most will see it work and think they've "got it right." I believe the community has a responsibility to give good advice and recommend best practices as well as directly addressing people's problems, so it's right that things like this get repeatedly pointed out where appropriate. -Stuart Stuart Dallas 3ft9 Ltd http://3ft9.com/ -- Thanks, didn't know about this. No doubt it's been general news for months or years but I see the PHP manual page you linked to was edited 3 days ago. Will have to see what ADOdb is doing - the last release I saw didn't support PDO_MySQL or mysqli. I googled and saw a first release of 5.5 will be this month? Cheers Arno -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql custom global defined variable
Am 13.03.13 10:35, schrieb Kevin Peterson:
> In my database design, I tend to store some variable that is meant to be
> acting as a ROLE or TYPE as SMALLINT. For example :
>
> CREATE TABLE `house` (
>`id` int(11) NOT NULL AUTO_INCREMENT,
>`type` smallint(11) NOT NULL,
> )
>
>
> And in php, I do
>
> define('HOUSE_SMALL_TYPE', '0');
> define('HOUSE_MEDIUM_TYPE', '1');
>
> So in php, in SELECT queries I do :
>
> $this->db->query("SELECT * FROM house
> WHERE type=?;", HOUSE_SMALL_TYPE);
>
> My questions are :
> 1. In the php part, is there is a better way to do this ?
I stopped using define in favor of somehow "namespaced constants" as
const in classes. But basically there is no difference.
Advantages of using constants (which ever) are code completion to avoid
spelling errors. I see no possible improvements here.
> 2. In the mysql itself, does mysql also has global define functionality (like
> the define in php) ? I also want to do kind of SELECT * FROM house WHERE type
> = HOUSE_SMALL_TYPE in mysql query.
Maybe this is what you are looking for?
http://forums.mysql.com/read.php?98,273432,273432
--
Marco Behnke
Dipl. Informatiker (FH), SAE Audio Engineer Diploma
Zend Certified Engineer PHP 5.3
Tel.: 0174 / 9722336
e-Mail: ma...@behnke.biz
Softwaretechnik Behnke
Heinrich-Heine-Str. 7D
21218 Seevetal
http://www.behnke.biz
signature.asc
Description: OpenPGP digital signature
Re: [PHP] mysql custom global defined variable
On Mar 13, 2013, at 10:35 AM, Kevin Peterson wrote:
> In my database design, I tend to store some variable that is meant to be
> acting as a ROLE or TYPE as SMALLINT. For example :
>
>CREATE TABLE `house` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `type` smallint(11) NOT NULL,
>)
>
>
> And in php, I do
>
>define('HOUSE_SMALL_TYPE', '0');
>define('HOUSE_MEDIUM_TYPE', '1');
>
> So in php, in SELECT queries I do :
>
>$this->db->query("SELECT * FROM house
>WHERE type=?;", HOUSE_SMALL_TYPE);
>
> My questions are :
> 1. In the php part, is there is a better way to do this ?
> 2. In the mysql itself, does mysql also has global define functionality (like
> the define in php) ? I also want to do kind of SELECT * FROM house WHERE type
> = HOUSE_SMALL_TYPE in mysql query.
>
Question 1:
I see no possible improvements, you could however use an array with values
instead of constants, but that's rather a personal choice as I don't like
constants that much, unless you are on your own namespace.
My example implementation:
$houseTypes = array(
'house_small_type' => 0,
'house_medium_type' => 1,
etc.
);
Question 2:
You could use ENUM data type, but it has quite a few disadvantages:
1- Translation could be tricky to implement
2- DDL shouldn't be used for data!
3- Updating or deleting values can leave your old records in an inconsistent
state
You can also use SET to set variables, I've never used them but I think they
could work in your case:
http://dev.mysql.com/doc/refman/5.5/en/user-variables.html
Greetings.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql custom global defined variable
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.
[PHP] mysql and php questions
Hello all:
I had a few questions.
First, I'm using php-fastcgi with nginx for my primary web server. I was
wondering what sorts of optimizations exist to make this process a lot
faster.
Second, I'm setting up a custom application, and it contains an
authentication module for login/registration. In doing this, I
discovered PDO (I used to just use the mysql_* functions). According to
google, it's easier to prevent mysql injection attacks with PDO, so I
dove in.
Before, I was using $pdo->exec("...");, but I read that I need to call
quote on the variables I'm passing in. It looks like that all quote does
is just add '...' on the variables, but I could be wrong.
So, here's my questions:
First, I know that prepared statements are immune to mysql injection
attacks, if I just use the variables with placeholders in the
statements. I know that caching these means that the optimization does
not have to be done every time, but is this the most efficient method
for adding a single user for registration? Or would a basic query be better.
Also, I had the idea of building up common queries and cacheing them,
but this isn't really possible since each php script (as far as I'm
aware) gets it's own process or environment. If I can build the prepared
statements and cache them, it seems like things would be a lot quicker.
Is this something commonly done?
--
Take care,
Ty
http://tds-solutions.net
The aspen project: a barebones light-weight mud engine:
http://code.google.com/p/aspenmud
He that will not reason is a bigot; he that cannot reason is a fool; he that
dares not reason is a slave.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP/mySQL Developer Partner needed...
Greetings, I have a site that I am developing and I am looking to partner/ developer with great php/mySQL skills (for share of potential profits) to assist me in finishing this site. I am looking for someone who can invest (like myself) their time and skills to complete the site in exchange for a percentage of profits the site will make. This is a side project for me, so I am looking for someone who would like to invest 8-10 hours a week to finish this site. To reiterate, I am looking for partner to invest their time and skills. I am not looking to pay someone an hourly wage for the work they do on the site ;-) If you are interested, please contact me PRIVATELY and include a few examples of your work (websites,etc...). Thanks. Don -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL table design
Leave the past behind. You're moving forward. And - for whatever reason that they were used originally, you now have the opportunity to rid yourself of column names that must be a pia to type all the time. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL table design
On Thu, Mar 22, 2012 at 7:42 PM, Stuart Dallas wrote: > On 23 Mar 2012, at 00:10, Chris Stinemetz wrote: > >> Is it possible to create a MySQL table with characters such as "." and >> "[]" in the column headers? If so would you explain how? > > Try putting the column names in backticks (`). > > BUT... whatever the reason why you want to do that, IT'S WRONG. > > Seriously, don't do it. It will cause you more problems than you think it > will solve, and I don't even know what problem you think it will solve. > I am just trying to preserve the source headers from the original data I want to import into the table. Thank you, Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL table design
On 2012-03-22, at 8:10 PM, Chris Stinemetz wrote: > Hello List, > > Is it possible to create a MySQL table with characters such as "." and > "[]" in the column headers? If so would you explain how? > > Thank you, > > Chris > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > Periods are not allowed, nor other characters that are reflected in file paths. You can use the back tick to wrap the field names, but I really don't recommend it. If you ever need to port this to another DB, it might not be as forgiving Bastien -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL table design
On 23 Mar 2012, at 00:10, Chris Stinemetz wrote: > Is it possible to create a MySQL table with characters such as "." and > "[]" in the column headers? If so would you explain how? Try putting the column names in backticks (`). BUT... whatever the reason why you want to do that, IT'S WRONG. Seriously, don't do it. It will cause you more problems than you think it will solve, and I don't even know what problem you think it will solve. -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL table design
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
Re: [PHP] mysql list to two-column list
On 19 Mar 2012, at 22:43, Tom Sparks wrote:
> I have a members list witch I print out once a week,
> I would like to make the list into two-column list, but I dont know where to
> start looking to change the code?
>
> here is the code
> "
> $result = mysql_query("SELECT * FROM customers ORDER BY LastName");
>
> while($row = mysql_fetch_array($result))
> {
> echo $row['LastName'];
> echo " " . $row['FirstName'];
> echo " " . $row['CustomNo'];
> echo "";
> }
> "
The following is untested so it may contain syntax errors, but I'm pretty sure
the logic is sound and I think it will give you what you want. You may want to
play with the cellpadding value to adjust the amount of space between the table
cells.
echo '';
$column = 1;
while ($row = mysql_fetch_assoc($result))
{
if ($column == 1) {
echo ''.PHP_EOL;
}
echo ' '.$row['LastName'].' '.$row['FirstName'].'
'.$row['CustomNo'].'';
$column++;
if ($column > 2) {
echo ''.PHP_EOL;
$column = 1;
}
}
if ($column == 2) {
echo ' '.PHP_EOL;
}
echo '';
Assuming I'm right in my interpretation of what you want, the following needs
to be said... this is *very* basic HTML being rendered by *very* basic PHP. I
suggest you learn about basic HTML first, then learn basic PHP, then work out
how to use logic in PHP to build the HTML you want.
If you're unsure how the above works, start by viewing the source of the page
in your browser. If it's still not clear, run it in your head. Use two pieces
of paper, one to store the variables and the other to keep track of the output.
Go through each loop and for each line update the variables and update the
output for each echo statement.
If I got what you want wrong then I have absolutely no clue what you're after.
-Stuart
--
Stuart Dallas
3ft9 Ltd
http://3ft9.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
please delete me
---
tom_a_sparks "It's a nerdy thing I like to do"
Please use ISO approved file formats excluding Office Open XML -
http://www.gnu.org/philosophy/no-word-attachments.html
Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
3 x (x)Ubuntu 10.04, Amiga A1200 WB 3.1, UAE AF 2006 Premium Edition, AF 2012
Plus Edition, Sam440 AOS 4.1.2, Roland DXY-1300 pen plotter, Cutok DC330
cutter/pen plotter
Wanted: RiscOS system, GEOS system (C64/C128), Atari ST, Apple Macintosh
(6502/68k/PPC only)
--- On Tue, 20/3/12, Tom Sparks wrote:
> From: Tom Sparks
> Subject: Re: [PHP] mysql list to two-column list
> To: a...@ashleysheridan.co.uk
> Cc: "php-general"
> Received: Tuesday, 20 March, 2012, 10:41 AM
> --- On Tue, 20/3/12, Ashley Sheridan
>
> wrote:
>
> From: Ashley Sheridan
> Subject: Re: [PHP] mysql list to two-column list
> To: "Tom Sparks"
> Cc: "php-general"
> Received: Tuesday, 20 March, 2012, 10:15 AM
>
>
>
>
>
>
>
>
> On Mon, 2012-03-19 at 16:09 -0700, Tom Sparks wrote:
>
> --- On Tue, 20/3/12, Ashley Sheridan
> wrote:
>
> On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
>
> >>I have a members list witch I print out once a
> week,
> >>I would like to make the list into two-column list,
> but I dont know >>where to start looking to change the
> code?
>
> >>here is the code
> >>"
> >>$result = mysql_query("SELECT * FROM customers ORDER
> BY LastName");
>
> >>while($row = mysql_fetch_array($result))
> >> {
> >> echo $row['LastName'];
> >> echo " " . $row['FirstName'];
> >> echo " " . $row['CustomNo'];
> >> echo "";
> >> }
> >>"
>
> >>example output:
>
> >>Bond James 007
> >>Quagmire Glenn 101
> >>Griffin Peter 102
> >>etc
>
> >>---
> >>tom_a_sparks "It's a nerdy thing I like to do"
> >>Please use ISO approved file formats excluding
> Office Open XML - >>http://www.gnu.org/philosophy/no-word-attachments.html
> >>Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
>
>
> >How do you mean?
> my goal is to do something phonebook like
> > In your example, it's a 3-column list (surname,
> >forename, customer number) although you've added a
> 'cute' field to the >first entry for giggles. It's a
> simple thing to join the first two >fields, but what do
> you determine to be a single column?
>
>
>
> Your reply of 9 words adds no extra information. Rather than
> have us all guess what you want, try and tell us
> specifically what it is that you want.
>
>
>
>
> Cleveland Brown | Griffin
> family
> Cleveland Brown, Jr. | Brian
> Griffin
>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
--- On Tue, 20/3/12, Ashley Sheridan wrote:
From: Ashley Sheridan
Subject: Re: [PHP] mysql list to two-column list
To: "Tom Sparks"
Cc: "php-general"
Received: Tuesday, 20 March, 2012, 10:15 AM
On Mon, 2012-03-19 at 16:09 -0700, Tom Sparks wrote:
--- On Tue, 20/3/12, Ashley Sheridan wrote:
On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
>>I have a members list witch I print out once a week,
>>I would like to make the list into two-column list, but I dont know >>where
>>to start looking to change the code?
>>here is the code
>>"
>>$result = mysql_query("SELECT * FROM customers ORDER BY LastName");
>>while($row = mysql_fetch_array($result))
>> {
>> echo $row['LastName'];
>> echo " " . $row['FirstName'];
>> echo " " . $row['CustomNo'];
>> echo "";
>> }
>>"
>>example output:
>>Bond James 007
>>Quagmire Glenn 101
>>Griffin Peter 102
>>etc
>>---
>>tom_a_sparks "It's a nerdy thing I like to do"
>>Please use ISO approved file formats excluding Office Open XML -
>>>>http://www.gnu.org/philosophy/no-word-attachments.html
>>Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
>How do you mean?
my goal is to do something phonebook like
> In your example, it's a 3-column list (surname,
>forename, customer number) although you've added a 'cute' field to the >first
>entry for giggles. It's a simple thing to join the first two >fields, but what
>do you determine to be a single column?
Your reply of 9 words adds no extra information. Rather than have us all guess
what you want, try and tell us specifically what it is that you want.
Cleveland Brown | Griffin family
Cleveland Brown, Jr. | Brian Griffin
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
>> my goal is to do something phonebook like >> > > Your reply of 9 words adds no extra information. Rather than have us all > guess what you want, try and tell us specifically what it is that you > want. Hi Tom I *think* what want to be asking is, "what HTML do I change/add such that my data coming from the database will format on the page in the browser like a phone book (in columns)?" If so, then: This is a PHP list where people mostly discuss PHP-specific things.. whereas it seem what you need is to learn some basic HTML which will allow you to format content on a page so it looks like a "phone book". I would say: Forget your database data for a day. Start with seeing if you can just write some static HTML that looks like what you want (formatted like a phone book), and THEN alter your real PHP code so that it spits out the HTML that mimics what you mocked up. If you get stuck with the HTML part of this task, then consult a good HTML list. If you get stuck with something specific to PHP, then ask again about that, here. Good luck, -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
On Mon, 2012-03-19 at 16:09 -0700, Tom Sparks wrote:
> --- On Tue, 20/3/12, Ashley Sheridan wrote:
>
> On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
>
> >>I have a members list witch I print out once a week,
> >>I would like to make the list into two-column list, but I dont know >>where
> >>to start looking to change the code?
>
> >>here is the code
> >>"
> >>$result = mysql_query("SELECT * FROM customers ORDER BY LastName");
>
> >>while($row = mysql_fetch_array($result))
> >> {
> >> echo $row['LastName'];
> >> echo " " . $row['FirstName'];
> >> echo " " . $row['CustomNo'];
> >> echo "";
> >> }
> >>"
>
> >>example output:
>
> >>Bond James 007
> >>Quagmire Glenn 101
> >>Griffin Peter 102
> >>etc
>
> >>---
> >>tom_a_sparks "It's a nerdy thing I like to do"
> >>Please use ISO approved file formats excluding Office Open XML -
> http://www.gnu.org/philosophy/no-word-attachments.html
> >>Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
>
>
> >How do you mean?
> my goal is to do something phonebook like
> > In your example, it's a 3-column list (surname,
> >forename, customer number) although you've added a 'cute' field to the
> >>first entry for giggles. It's a simple thing to join the first two >fields,
> >but what do you determine to be a single column?
>
Your reply of 9 words adds no extra information. Rather than have us all
guess what you want, try and tell us specifically what it is that you
want.
--
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] mysql list to two-column list
--- On Tue, 20/3/12, Ashley Sheridan wrote:
On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
>>I have a members list witch I print out once a week,
>>I would like to make the list into two-column list, but I dont know >>where
>>to start looking to change the code?
>>here is the code
>>"
>>$result = mysql_query("SELECT * FROM customers ORDER BY LastName");
>>while($row = mysql_fetch_array($result))
>> {
>> echo $row['LastName'];
>> echo " " . $row['FirstName'];
>> echo " " . $row['CustomNo'];
>> echo "";
>> }
>>"
>>example output:
>>Bond James 007
>>Quagmire Glenn 101
>>Griffin Peter 102
>>etc
>>---
>>tom_a_sparks "It's a nerdy thing I like to do"
>>Please use ISO approved file formats excluding Office Open XML -
http://www.gnu.org/philosophy/no-word-attachments.html
>>Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
>How do you mean?
my goal is to do something phonebook like
> In your example, it's a 3-column list (surname,
>forename, customer number) although you've added a 'cute' field to the >first
>entry for giggles. It's a simple thing to join the first two >fields, but what
>do you determine to be a single column?
--
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql list to two-column list
On Mon, 2012-03-19 at 15:43 -0700, Tom Sparks wrote:
> I have a members list witch I print out once a week,
> I would like to make the list into two-column list, but I dont know where to
> start looking to change the code?
>
> here is the code
> "
> $result = mysql_query("SELECT * FROM customers ORDER BY LastName");
>
> while($row = mysql_fetch_array($result))
> {
> echo $row['LastName'];
> echo " " . $row['FirstName'];
> echo " " . $row['CustomNo'];
> echo "";
> }
> "
>
> example output:
>
> Bond James Bond 007
> Quagmire Glenn 101
> Griffin Peter 102
> etc
>
> ---
> tom_a_sparks "It's a nerdy thing I like to do"
> Please use ISO approved file formats excluding Office Open XML -
> http://www.gnu.org/philosophy/no-word-attachments.html
> Ubuntu wiki page https://wiki.ubuntu.com/tomsparks
> 3 x (x)Ubuntu 10.04, Amiga A1200 WB 3.1, UAE AF 2006 Premium Edition, AF 2012
> Plus Edition, Sam440 AOS 4.1.2, Roland DXY-1300 pen plotter, Cutok DC330
> cutter/pen plotter
> Wanted: RiscOS system, GEOS system (C64/C128), Atari ST, Apple Macintosh
> (6502/68k/PPC only)
>
How do you mean? In your example, it's a 3-column list (surname,
forename, customer number) although you've added a 'cute' field to the
first entry for giggles. It's a simple thing to join the first two
fields, but what do you determine to be a single column?
--
Thanks,
Ash
http://www.ashleysheridan.co.uk
[PHP] mysql list to two-column list
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)
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL over TCP results on CLOSE_WAIT state in PHP 5.3.8
On Mon, Mar 5, 2012 at 7:33 AM, wrote: >> > I have a MySQL server A, a server B with PHP 5.3.8 and a server C with >> > PHP 5.3.3. I'm connecting to the MySQL server on A via TCP from B and C >> > using an internal network. Server B and C use the same PHP application. >> > There are also same PHP scripts that get data from the database and then >> > calculate up to 30 minutes. I close all database connection before doing >> > the calculation to save connections (and ports) using: >> > $thread_id = mysqli_thread_id( $this->handle ); >> > mysqli_kill( $this->handle, $thread_id ); >> > mysqli_close( $this->handle ); >> > >> > During a review on our servers I discovered that server B has a lot of >> > network connection in the state "CLOSE_WAIT". Server C running the same >> > PHP application has not. I see the difference that server B is using >> > mysqlnd and server C not. >> > >> > serverB# netstat -an | grep 3306 >> > tcp 1 0 10.8.0.58:47455 10.8.0.1:3306 >> > CLOSE_WAIT >> > >> > serverA# cat firewall >> > Feb 17 16:21:49 www kernel: [6587053.325075] SFW2-OUT-ERROR IN= OUT=tun0 >> > SRC=10.8.0.1 DST=10.8.0.58 LEN=40 TOS=0x00 PREC=0x00 TTL=64 ID=0 DF >> > PROTO=TCP SPT=3306 DPT=47455 WINDOW=0 RES=0x00 RST URGP=0 >> > >> > Does anybody have an idea why this happens? How can I avoid this or >> > investigate in this? Is this a know issue? >> >> Is the underlying OS for servers B & C exactly the same? Down to >> kernel version (if Linux/Unix), NICs and driver version, >> configurations, etc.? It would help if specify OS type and kernel >> version. > > No, client B and C have different OS versions. You are right this can be a > reason too. Do you have any hints how I can trace that down? > > Details client B (newer OS with CLOSE_WAIT problems): > $ uname -a > Linux www3 3.1.9-1.4-default #1 SMP Fri Jan 27 08:55:10 UTC 2012 (efb5ff4) > x86_64 x86_64 x86_64 GNU/Linux > $ mysql --version > mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using readline 6.2 > $ ethtool -i eth0 > driver: r8169 > version: 2.3LK-NAPI > firmware-version: rtl_nic/rtl8168e-2.fw > bus-info: :04:00.0 > supports-statistics: yes > supports-test: no > supports-eeprom-access: no > supports-register-dump: yes > > Details client C: > $ uname -a > Linux www13 2.6.34.10-0.4-default #1 SMP 2011-10-19 22:16:41 +0200 x86_64 > x86_64 x86_64 GNU/Linux > $ mysql --version > mysql Ver 14.14 Distrib 5.1.57, for suse-linux-gnu (x86_64) using readline > 6.1 > $ ethtool -i eth0 > driver: r8169 > version: 2.3LK-NAPI > firmware-version: > bus-info: :04:00.0 > > Server A: > Linux www 3.1.9-1.4-default #1 SMP Fri Jan 27 08:55:10 UTC 2012 (efb5ff4) > x86_64 x86_64 x86_64 GNU/Linux > $ mysql --version > mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using readline 6.2 > $ ethtool -i eth0 > driver: r8169 > version: 2.3LK-NAPI > firmware-version: rtl_nic/rtl8168e-2.fw > bus-info: :04:00.0 > supports-statistics: yes > supports-test: no > supports-eeprom-access: no > supports-register-dump: yes > > I also have set wait_timeout 45 in my.cnf on server A and mysqli.reconnect On > on client B and C in php.ini. Troubleshooting that much variances will take a lot of time. Do you have root access? Was the OS installed via standard distribution? Did someone reconfigured and recompiled the kernel and system? I'd suggest you start compiling from source of the following: * MySQL client - same version (preferred) or newer than server (make sure config is the same on both boxes for PHP) * compile PHP thereafter Then you'll know if it's the OS related or not (ie: configuration, kernel version, etc.) Also, your kernel versions doesn't seem to be stable per [1]. You might want to check each flavor's distributor. Just out of curiosity, are both boxes (B & C) are the same Linux flavor? Best regards, Tommy [1]http://www.kernel.org/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL over TCP results on CLOSE_WAIT state in PHP 5.3.8
> > I have a MySQL server A, a server B with PHP 5.3.8 and a server C with > > PHP 5.3.3. I'm connecting to the MySQL server on A via TCP from B and C > > using an internal network. Server B and C use the same PHP application. > > There are also same PHP scripts that get data from the database and then > > calculate up to 30 minutes. I close all database connection before doing > > the calculation to save connections (and ports) using: > > $thread_id = mysqli_thread_id( $this->handle ); > > mysqli_kill( $this->handle, $thread_id ); > > mysqli_close( $this->handle ); > > > > During a review on our servers I discovered that server B has a lot of > > network connection in the state "CLOSE_WAIT". Server C running the same > > PHP application has not. I see the difference that server B is using > > mysqlnd and server C not. > > > > serverB# netstat -an | grep 3306 > > tcp1 0 10.8.0.58:47455 10.8.0.1:3306 > > CLOSE_WAIT > > > > serverA# cat firewall > > Feb 17 16:21:49 www kernel: [6587053.325075] SFW2-OUT-ERROR IN= OUT=tun0 > > SRC=10.8.0.1 DST=10.8.0.58 LEN=40 TOS=0x00 PREC=0x00 TTL=64 ID=0 DF > > PROTO=TCP SPT=3306 DPT=47455 WINDOW=0 RES=0x00 RST URGP=0 > > > > Does anybody have an idea why this happens? How can I avoid this or > > investigate in this? Is this a know issue? > > Is the underlying OS for servers B & C exactly the same? Down to > kernel version (if Linux/Unix), NICs and driver version, > configurations, etc.? It would help if specify OS type and kernel > version. No, client B and C have different OS versions. You are right this can be a reason too. Do you have any hints how I can trace that down? Details client B (newer OS with CLOSE_WAIT problems): $ uname -a Linux www3 3.1.9-1.4-default #1 SMP Fri Jan 27 08:55:10 UTC 2012 (efb5ff4) x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using readline 6.2 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: rtl_nic/rtl8168e-2.fw bus-info: :04:00.0 supports-statistics: yes supports-test: no supports-eeprom-access: no supports-register-dump: yes Details client C: $ uname -a Linux www13 2.6.34.10-0.4-default #1 SMP 2011-10-19 22:16:41 +0200 x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.1.57, for suse-linux-gnu (x86_64) using readline 6.1 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: bus-info: :04:00.0 Server A: Linux www 3.1.9-1.4-default #1 SMP Fri Jan 27 08:55:10 UTC 2012 (efb5ff4) x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using readline 6.2 $ ethtool -i eth0 driver: r8169 version: 2.3LK-NAPI firmware-version: rtl_nic/rtl8168e-2.fw bus-info: :04:00.0 supports-statistics: yes supports-test: no supports-eeprom-access: no supports-register-dump: yes I also have set wait_timeout 45 in my.cnf on server A and mysqli.reconnect On on client B and C in php.ini. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL over TCP results on CLOSE_WAIT state in PHP 5.3.8
On Mon, Feb 27, 2012 at 4:06 AM, wrote: > Hi, > > I have a MySQL server A, a server B with PHP 5.3.8 and a server C with PHP > 5.3.3. I'm connecting to the MySQL server on A via TCP from B and C using an > internal network. Server B and C use the same PHP application. There are also > same PHP scripts that get data from the database and then calculate up to 30 > minutes. I close all database connection before doing the calculation to save > connections (and ports) using: > $thread_id = mysqli_thread_id( $this->handle ); > mysqli_kill( $this->handle, $thread_id ); > mysqli_close( $this->handle ); > > During a review on our servers I discovered that server B has a lot of network > connection in the state "CLOSE_WAIT". Server C running the same PHP > application has not. I see the difference that server B is using mysqlnd and > server C not. > > serverB# netstat -an | grep 3306 > tcp 1 0 10.8.0.58:47455 10.8.0.1:3306 CLOSE_WAIT > > serverA# cat firewall > Feb 17 16:21:49 www kernel: [6587053.325075] SFW2-OUT-ERROR IN= OUT=tun0 > SRC=10.8.0.1 DST=10.8.0.58 LEN=40 TOS=0x00 PREC=0x00 TTL=64 ID=0 DF PROTO=TCP > SPT=3306 DPT=47455 WINDOW=0 RES=0x00 RST URGP=0 > > Does anybody have an idea why this happens? How can I avoid this or > investigate in this? Is this a know issue? > Is the underlying OS for servers B & C exactly the same? Down to kernel version (if Linux/Unix), NICs and driver version, configurations, etc.? It would help if specify OS type and kernel version. Best regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL over TCP results on CLOSE_WAIT state in PHP 5.3.8
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 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? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL and PHP weirdness
Bear with me here. I have a problem with PHP and MySQL that's been stumping me for a couple of days now. I'm not even sure how to describe it, so I'll just do my best. There's a row in our bugs database that looks like every other row in the table, but when it's pulled from the database and displayed in PHP, the "description" field -- which is defined as a "mediumtext" field -- is displayed as a date field with a value of 12/31/1969. Moreoever, when I use PHPMyAdmin to look at the row directly, the "description" field has the data that I expect it to. Just for fun, here's the text in question: Quarterly Course Set Up - Spring 2012 (114) Section Course titleCourse Start Date 114MHI214 The Internet and the Future of Patient Care 04/02/2012 114MHI212 Health Information Systems Analysis and Design 04/02/2012 Program administrator 2 users; Laurel Aroner - Susan Catron - Jennifer Kremer Instructors; MHI214; Peter Yellowlees MHI212; Robert Balch Per instructions from Rita Smith-Simms - I'm creating this task for myself and based on instructions given which are that all listed courses are now to be backed-up and restored sooner (original course set up time-frame was a month before course starts). Adding instructions - Follow the 52-step quarterly course set up process and information from the course matrix; if matrix incomplete, input information during this set up As you track your time each day, please include in the notes which courses (use project code) you worked on and indicate either working on or completed. Create and notify by 2/15/2012 I've made sure there are no odd characters that would mess up how PHP is displaying the text. I've tried changing the field type from "mediumtext" to "text" but this didn't work. If anyone has any ideas as to why this might be happening -- or if I just wasn't clear -- please let me know. -- Sláinte, Richard S. Crawford (rich...@underpope.com) http://www.underpope.com Twitter: http://twitter.com/underpope Facebook: http://www.facebook.com/underpope Google+: http://gplus.to/underpope
Re: Re: [PHP] mysql adapter and DAL
mysqli - it's what I wanted. thanks
Fwd: Re: [PHP] mysql adapter and DAL
Sorry, I clicked the 'reply' button instead of reply to all. Original Message Subject: Re: [PHP] mysql adapter and DAL Date: Sat, 17 Sep 2011 19:07:48 +0530 From: Nilesh Govindarajan To: shahrzad khorrami On Sat 17 Sep 2011 04:54:13 PM IST, shahrzad khorrami wrote: > hi all, > I'm looking for a mysql adapter for create my dal.. > where can I find a good one? and have you ever written a dal with a mysql > adapter? > in my dal I want to pass parameters to sql sting, for example like > following: > $db->query($sql, array($name, $family)) > > thanks, > Shahrzad Khorrami > Why do you want to reinvent the wheel? There are many Database Abstraction Layer packages which support MySQL. Pear::MDB2, Zend_Db, CodeIgniter, ... the list doesn't end. Search for them! -- Nilesh Govindarajan http://nileshgr.com -- Nilesh Govindarajan http://nileshgr.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql adapter and DAL
hi, If you are building your own dal I guess you would have to build your own adapter, simply use mysqli function and wrap them in a class. On 17 September 2011 12:24, shahrzad khorrami wrote: > hi all, > I'm looking for a mysql adapter for create my dal.. > where can I find a good one? and have you ever written a dal with a mysql > adapter? > in my dal I want to pass parameters to sql sting, for example like > following: > $db->query($sql, array($name, $family)) > > thanks, > Shahrzad Khorrami >
[PHP] mysql adapter and DAL
hi all, I'm looking for a mysql adapter for create my dal.. where can I find a good one? and have you ever written a dal with a mysql adapter? in my dal I want to pass parameters to sql sting, for example like following: $db->query($sql, array($name, $family)) thanks, Shahrzad Khorrami
Re: [PHP] mysql insert internal server error 500
I think it's not the best place to send it but: $dolgF is not closed with ; ...and the insert is not in a variable! (And also I think it's not a good way using COOKIE in PHP because we have sessions) So the answer is: the all.. :D -or what's the full part you use for insert int this source!? On Mon, May 16, 2011 at 5:11 PM, Grega Leskovšek wrote: > $ime=$_COOKIE['user']; > $dolgF=filesize($filename) > INSERT INTO `friendlyCMS`.`log` (`imepriimek`, `clock`, `action`, > `onfile`, `filesize`) VALUES ( $ime, CURRENT_TIMESTAMP, > 'saved',$filename, $dolgF); > > What is wrong with this? > PS First column of the log table is idlog primary key autoincrement > not null - I suppose I omit this when adding to table? > Thanks in advance, Grega > -- When the sun rises I receive and when it sets I forgive -> > http://moj.skavt.net/gleskovs/ > Always in Heart, Grega Leskovšek > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
[PHP] mysql insert internal server error 500
$ime=$_COOKIE['user']; $dolgF=filesize($filename) INSERT INTO `friendlyCMS`.`log` (`imepriimek`, `clock`, `action`, `onfile`, `filesize`) VALUES ( $ime, CURRENT_TIMESTAMP, 'saved',$filename, $dolgF); What is wrong with this? PS First column of the log table is idlog primary key autoincrement not null - I suppose I omit this when adding to table? Thanks in advance, Grega -- When the sun rises I receive and when it sets I forgive -> http://moj.skavt.net/gleskovs/ Always in Heart, Grega Leskovšek -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql problems [SOLVED]
>[SNIP]
> added and else clause.
> while ($_parent != 0)
> {
> if
> ($num_rows > 0)
>{
>
> perform some action
>}
>else
>{
> $_parent =
> "0";
>}
> }
>
> and that solved the
> problem.
>
> Thank you, everyone for your help.
>
> Curtis
A small remark:
I think it is good programming practice to place such static if-clauses before
the while statement.
This prevents a lot of redundant checks and thus saves time.
Best regards,
Jasper
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql problems [SOLVED]
Sean Greenslade wrote:
>>
>
> [MASSIVE
SNIP]
>
> Well, from what I saw while wading through your
code, you allow
> unsanitized
> variables to be
concatenated to your queries. Big no-no! For ANY
>
client-generated variable, always sanitize with
mysql_real_escape_string.
> In
> fact, sanitize all your
variables. It can't hurt.
>
> Also, please don't take a
request for your entire code too literally. We
> don't like to see
pages and pages and pages of code, just the pertinent
> bits.
> --
> --Zootboy
>
> Sent from my PC.
>
Thanks to all, but it was an infinite loop. there was a
while ($_parent != "0") { } loop. In the loop the database
is queried. If the returned number of rows is greater than 0 then
perform then grab a $_parent from the database. At some point, there
must be a parent that is = 0 and the loop breaks. However, if the
page is called with category number that doesn't exist, then the if/then
clause is never true and $_parent never gets set to 0. I simply
added and else clause.
while ($_parent != 0)
{
if
($num_rows > 0)
{
perform some action
}
else
{
$_parent =
"0";
}
}
and that solved the
problem.
Thank you, everyone for your help.
Curtis
Re: Re: [PHP] mysql problems
Tim Streater wrote: > On 11 May 2011 at 19:25, Curtis Maurand wrote: > >> $_cartTotal="$0.00"; > > Surely that should be: > > $_cartTotal = "0.00"; Good pickup. I missed that. I didn't write the code, I'm just trying to figure out what's going on. Thanks, I'll look at that. --C
Re: Re: [PHP] mysql problems
On 11 May 2011 at 19:25, Curtis Maurand wrote: > $_cartTotal="$0.00"; Surely that should be: $_cartTotal = "0.00"; tim -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql problems
On Wed, May 11, 2011 at 2:25 PM, Curtis Maurand wrote: > > > Marc Guay wrote: > >> Does anyone have any ideas? > > > > Sounds like it's getting caught in a loop. Post the whole script > for > > best results. > > > It looks like the site is > under attack, because I keep seeing the query, "SELECT catagory_parent FROM > t_catagories where catagory_ID=" . > $_currentCat" > > where $_currentCat is equal to a > value not in the database. The only way that this can happen is if > the page is called directly without going through the default page. > > > the script follows. its called leftNav.php > [MASSIVE SNIP] Well, from what I saw while wading through your code, you allow unsanitized variables to be concatenated to your queries. Big no-no! For ANY client-generated variable, always sanitize with mysql_real_escape_string. In fact, sanitize all your variables. It can't hurt. Also, please don't take a request for your entire code too literally. We don't like to see pages and pages and pages of code, just the pertinent bits. -- --Zootboy Sent from my PC.
Re: [PHP] mysql problems
Marc Guay wrote:
>> Does anyone have any ideas?
>
> Sounds like it's getting caught in a loop. Post the whole script
for
> best results.
>
It looks like the site is
under attack, because I keep seeing the query, "SELECT catagory_parent FROM
t_catagories where catagory_ID=" .
$_currentCat"
where $_currentCat is equal to a
value not in the database. The only way that this can happen is if
the page is called directly without going through the default page.
the script follows. its called leftNav.php
_subTotal;
$_counter++;
}
$_cartTotal = "$".number_format($_getSubTotal,2);
$_cartCount = $_counter;
mysql_close($dbhandle);
}
tallyCart($_SESSION["u_id"]);
?>
You have items in your
cart.
Cart total:
� Go to
cart
";
//select a database
to work with
$selected =
mysql_select_db("pinetree",$dbhandle2)
or
die("Could not select examples");
while ($_parent !="0") {
$result_2 = mysql_query("SELECT catagory_parent
FROM t_catagories where catagory_ID=" .$_parent);
$num_rows_2 =
mysql_num_rows($result_2);
if($num_rows_2 > "0")
{
while ($row = mysql_fetch_array($result_2)) {
$_parent= $row{'catagory_parent'};
$_parents[$counter] = $row{'catagory_parent'};
$counter++;
}
}
}
mysql_close($dbhandle2);
function getParent($catID,
$matchingID){
//$username = "alaric";
$username
= "pinetree";
//$password = "removed";
$password = "removed";
//$hostname =
"127.0.0.1";
$hostname = "www.superseeds.com";
$_parent="1";
$_currentCat=$catID;
$dbhandle2 = mysql_connect($hostname, $username,
$password)
or die("Unable to connect
to MySQL");
//echo "Connected to
MySQL";
//select a database to work with
$selected =
mysql_select_db("pinetree",$dbhandle2)
or die("Could not select examples");
while
($_parent !="0") {
$result_2 = mysql_query("SELECT catagory_parent
FROM t_catagories where catagory_ID=" . $_currentCat);
while
($row = mysql_fetch_array($result_2)) {
$_parent=$row{'catagory_parent'};
if($row{'catagory_parent'}==$matchingID){
mysql_close($dbhandle2);
return true;
}
}
}
mysql_close($dbhandle2);
return false;
}
?>
0){
global $_parents;
global $username;
global $password;
global $hostname;
$dbhandle3 = mysql_connect($hostname, $username,
$password)
or
die("Unable to connect to MySQL");
$selected = mysql_select_db("pinetree",$dbhandle3)
or die("Could not
select examples");
//execute the SQL query and return
records
if($_parent!="0"){
$result = mysql_query("SELECT catagory_ID,
catagory_name, catagory_parent FROM t_catagories where
catagory_parent=".$_parent ." ORDER BY catagory_name");
}else{
$result = mysql_query("SELECT
catagory_ID, catagory_name, catagory_parent FROM t_catagories where
catagory_parent=".$_parent);
}
//fetch tha data from the database
if($_parent=="0"){
echo "";
}else{
if($_style!=""){
echo "";
}else{
echo "";
}
}
while ($row = mysql_fetch_array($result)) {
$_cat_id=$row{'catagory_ID'};
$_match_2="+";
foreach($_parents as $id){
if($id== $_cat_id){
$_match_2="-";
}
}
if($_cat_id==$_GET["cat"]){
$_match_2="-";
}
if($row{'catagory_parent'}=="0"){
echo
"";
}else{
if(getRowCount($row{'catagory_ID'})!="0"){
echo "";
echo "";
}else{
echo
" ";
}
}
echo "" .
$row{'catagory_name'}."";
echo
"";
//generateNav($_cat_id);
$_match=0;
foreach($_parents as $id){
if($id== $_cat_id){
generateNav($_cat_id,"");
$_match=1;
}
}
if($_cat_id==$_GET["cat"] && $_match==0){
generateNav($_cat_id,"");
}else{
if($row{'catagory_parent'}!="0"){
generateNav($_cat_id,"none");
}
}
}
if($row{'catagory_parent'}=="0"){
echo
"";
}else{
echo "";
Re: [PHP] mysql problems
Marc Guay wrote:
>> Does anyone have any ideas?
>
> Sounds like it's getting caught in a loop. Post the whole script
for
> best results.
>
It looks like the site is
under attack, because I keep seeing the query, "SELECT catagory_parent FROM
t_catagories where catagory_ID=" .
$_currentCat"
where $_currentCat is equal to a
value not in the database. The only way that this can happen is if
the page is called directly without going through the default page.
the script follows. its called leftNav.php
_subTotal;
$_counter++;
}
$_cartTotal = "$".number_format($_getSubTotal,2);
$_cartCount = $_counter;
mysql_close($dbhandle);
}
tallyCart($_SESSION["u_id"]);
?>
You have items in your
cart.
Cart total:
� Go to
cart
";
//select a database
to work with
$selected =
mysql_select_db("pinetree",$dbhandle2)
or
die("Could not select examples");
while ($_parent !="0") {
$result_2 = mysql_query("SELECT catagory_parent
FROM t_catagories where catagory_ID=" .$_parent);
$num_rows_2 =
mysql_num_rows($result_2);
if($num_rows_2 > "0")
{
while ($row = mysql_fetch_array($result_2)) {
$_parent= $row{'catagory_parent'};
$_parents[$counter] = $row{'catagory_parent'};
$counter++;
}
}
}
mysql_close($dbhandle2);
function getParent($catID,
$matchingID){
//$username = "alaric";
$username
= "pinetree";
//$password = "removed";
$password = "removed";
//$hostname =
"127.0.0.1";
$hostname = "www.superseeds.com";
$_parent="1";
$_currentCat=$catID;
$dbhandle2 = mysql_connect($hostname, $username,
$password)
or die("Unable to connect
to MySQL");
//echo "Connected to
MySQL";
//select a database to work with
$selected =
mysql_select_db("pinetree",$dbhandle2)
or die("Could not select examples");
while
($_parent !="0") {
$result_2 = mysql_query("SELECT catagory_parent
FROM t_catagories where catagory_ID=" . $_currentCat);
while
($row = mysql_fetch_array($result_2)) {
$_parent=$row{'catagory_parent'};
if($row{'catagory_parent'}==$matchingID){
mysql_close($dbhandle2);
return true;
}
}
}
mysql_close($dbhandle2);
return false;
}
?>
0){
global $_parents;
global $username;
global $password;
global $hostname;
$dbhandle3 = mysql_connect($hostname, $username,
$password)
or
die("Unable to connect to MySQL");
$selected = mysql_select_db("pinetree",$dbhandle3)
or die("Could not
select examples");
//execute the SQL query and return
records
if($_parent!="0"){
$result = mysql_query("SELECT catagory_ID,
catagory_name, catagory_parent FROM t_catagories where
catagory_parent=".$_parent ." ORDER BY catagory_name");
}else{
$result = mysql_query("SELECT
catagory_ID, catagory_name, catagory_parent FROM t_catagories where
catagory_parent=".$_parent);
}
//fetch tha data from the database
if($_parent=="0"){
echo "";
}else{
if($_style!=""){
echo "";
}else{
echo "";
}
}
while ($row = mysql_fetch_array($result)) {
$_cat_id=$row{'catagory_ID'};
$_match_2="+";
foreach($_parents as $id){
if($id== $_cat_id){
$_match_2="-";
}
}
if($_cat_id==$_GET["cat"]){
$_match_2="-";
}
if($row{'catagory_parent'}=="0"){
echo
"";
}else{
if(getRowCount($row{'catagory_ID'})!="0"){
echo "";
echo "";
}else{
echo
" ";
}
}
echo "" .
$row{'catagory_name'}."";
echo
"";
//generateNav($_cat_id);
$_match=0;
foreach($_parents as $id){
if($id== $_cat_id){
generateNav($_cat_id,"");
$_match=1;
}
}
if($_cat_id==$_GET["cat"] && $_match==0){
generateNav($_cat_id,"");
}else{
if($row{'catagory_parent'}!="0"){
generateNav($_cat_id,"none");
}
}
}
if($row{'catagory_parent'}=="0"){
echo
"";
}else{
echo "";
Re: [PHP] mysql problems
> Does anyone have any ideas? Sounds like it's getting caught in a loop. Post the whole script for best results. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql problems
I'm running PHP 5.3.6, Mysql 5.1.51 and Apache 2.2.17
I have
code that does a simple mysql_query(); the query on the commandline
returns an empty set. when run via PHP and the web server, the page
hangs, it never gets to the if (mysql_num_rows($result) > 0) {}. and
the queries per second on mysql goes from roughly 4 per second to about
12,000.
Does anyone have any ideas?
Thanks,
Curtis
Re: [PHP] mysql error
Hi Grega, Probably you wanna define a primary key, if so, then change from "uniqueid" to "primary key". 2011/5/6 Curtis Maurand : > > > > engine= > > --C > > Grega Leskovšek wrote: >> Can smbd please look at this sentence - I got an error and do > not >> know how to fix it - I am still very unfamiliar with > MYSQL: >> >> CREATE TABLE log ( idlog int auto_increment > not null, imepriimek >> varchar(50), clock timestamp, action > varchar(30), onfile >> varchar(100), filesize float(6,2), > uniqueid(idlog) ); >> >> ERROR 1064 (42000): You have an > error in your SQL syntax; check the >> manual that corresponds to > your MySQL server version for the right >> syntax to use near > '(idlog) )' at line 1 >> >> -- When the sun rises I receive > and when it sets I forgive -> >> > http://moj.skavt.net/gleskovs/ >> Always in Heart, Grega > LeskovĹĄek >> >> -- >> PHP General > Mailing List (http://www.php.net/) >> To unsubscribe, visit: > http://www.php.net/unsub.php >> >> > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql error
engine= --C Grega Leskov¹ek wrote: > Can smbd please look at this sentence - I got an error and do not > know how to fix it - I am still very unfamiliar with MYSQL: > > CREATE TABLE log ( idlog int auto_increment not null, imepriimek > varchar(50), clock timestamp, action varchar(30), onfile > varchar(100), filesize float(6,2), uniqueid(idlog) ); > > ERROR 1064 (42000): You have an error in your SQL syntax; check the > manual that corresponds to your MySQL server version for the right > syntax to use near '(idlog) )' at line 1 > > -- When the sun rises I receive and when it sets I forgive -> > http://moj.skavt.net/gleskovs/ > Always in Heart, Grega LeskovÅ¡ek > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP] mysql error
On Thu, May 5, 2011 at 10:29, Grega Leskovšek wrote: > Can smbd please look at this sentence - I got an error and do not > know how to fix it - I am still very unfamiliar with MYSQL: > > CREATE TABLE log ( idlog int auto_increment not null, imepriimek > varchar(50), clock timestamp, action varchar(30), onfile > varchar(100), filesize float(6,2), uniqueid(idlog) ); > > ERROR 1064 (42000): You have an error in your SQL syntax; check the > manual that corresponds to your MySQL server version for the right > syntax to use near '(idlog) )' at line 1 This is by no means a PHP question, and should not be asked on the PHP General mailing list. Please ask questions in the appropriate place; for this, the MySQL General list is the correct forum, and they've been CC'd. In the future, if it relates to a PHP database issue, you may want to use the PHP Database mailing list, also CC'd. This not only means you'll get more on-target help faster, but also helps in archiving data in the proper location for future searchers. -- Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql error
Hello Grega, What do you mean by uniqueid(idlog)? -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Grega Leskovšek To: php-general Date created: , 5:29:44 PM Subject: [PHP] mysql error Can smbd please look at this sentence - I got an error and do not know how to fix it - I am still very unfamiliar with MYSQL: CREATE TABLE log ( idlog int auto_increment not null, imepriimek varchar(50), clock timestamp, action varchar(30), onfile varchar(100), filesize float(6,2), uniqueid(idlog) ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(idlog) )' at line 1 -- When the sun rises I receive and when it sets I forgive -> http://moj.skavt.net/gleskovs/ Always in Heart, Grega Leskovšek -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[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] MySQL Unbuffered Query Behavior Change
I was previously on PHP 5.1.6 and was using the following code: $dbr = mysql_unbuffered_query($query, $this->con); while($row = mysql_fetch_array($dbr, $assoc ? MYSQL_ASSOC : MYSQL_NUM)) $this->result[] = $row; $this->rows = mysql_num_rows($dbr); It worked properly. The documentation at http://www.php.net/manual/en/function.mysql-num-rows.php indicates that mysql_num_rows won't return the correct result on unbuffered queries until all of the rows had been fetched, but since I was looping through and fetching all rows with mysql_fetch_array, it worked properly and wasn't a problem. I've upgraded to PHP 5.3.5 and now mysql_num_rows always returns 0. I am fetching all rows before calling mysql_num_rows, just like the documentation says to do, so mysql_num_rows should return the correct result. Why is it not? Is the documentation wrong now (should it have been updated to say that mysql_num_rows now NEVER works with unbuffered queries instead of saying you have to fetch all rows first)? Is there a bug in PHP? Or am I doing something wrong and it just coincidentally worked before? Thanks, Nick -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql 5.5 and PHP Mysql API Version 5.1.41
BTW, how is the traffic on your site? What's the max connection limit and timeout set on the MySQL server? [1] from searching [2] might be the solution to your problem. Good luck, Tommy [1] http://forums.mysql.com/read.php?52,364493,364831#msg-364831 [2] http://www.google.com/search?q=php+SQLSTATE[HY000]+[2013] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql 5.5 and PHP Mysql API Version 5.1.41
On Tue, Feb 15, 2011 at 9:57 AM, Matthias Laug wrote:
> Hey there,
>
> I've just migrated to Mysql 5.5 from source and it works like a charm. Still
> every now and then (in intervals of approximatly an hour) I get the following
> error:
>
> Error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2013]
> Lost connection to MySQL server at 'reading initial communication packet',
> system error: 110' in
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php:129
> Stack trace: #0
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php(129):
> PDO->__construct('mysql:port=6664...', '#', '#', Array) #1
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Mysql.php(96):
> Zend_Db_Adapter_Pdo_Abstract->_connect() #2
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Abstract.php(448):
> Zend_Db_Adapter_Pdo_Mysql->_connect() #3
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php(238):
> Zend_Db_Adapter_Abstract->query('SET NAMES 'utf8...', Array) #4
> /home/ydadmin/build/lieferando.de/11720/application/Bootstrap.php(99):
> Zend_Db_Adapter_Pdo_Abstract->query('SET NAMES 'utf8...') #5
> /home/ydadmin/build/lieferando.de/11720/library/Zend/App in
> /home/ydadmin/build/lieferando.de/11720/library/Zend/Db/Adapter/Pdo/Abstract.php
> on line 144
>
> It is like any queue is running full and this is the result, but actually no
> clue. My first guess is the API Version of the mysql client I am using with
> the precompiled php binaries (Ubuntu 10.10 Server, PHP 5.3.2-1ubuntu4.7 with
> Suhosin-Patch (cli) (built: Jan 12 2011 18:36:55))
>
> Does anyone else have the same problems?
>
> Thanks for any help,
> Matthias
> --
Have you tried a basic test w/o framework like this to ensure that
both installed versions of PHP & MySQL are working properly as
expected?
Test connection for sample data:
Penelope Guiness
Nick Wahlberg
Platform info:
Server Info: 5.5.7-rc-log
Client Info: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
Webserver: Microsoft-IIS/7.5
PHP: 5.3.5-cgi-fcgi
Test connection for sample data: '.PHP_EOL;
$dbh = new PDO( $dsn, $user, $passwd );
foreach( $dbh->query( $sql ) as $row )
{
echo ucfirst( strtolower( $row['first_name'] ) ).' '
.ucfirst( strtolower( $row['last_name'] ) ).PHP_EOL;
}
echo PHP_EOL.'Platform info: '.PHP_EOL;
$mysqli = new mysqli( $host, $user, $passwd, $dbname );
echo 'Server Info: '.$mysqli->server_info.PHP_EOL;
echo 'Client Info: '.$mysqli->client_info.PHP_EOL;
$mysqli->close();
echo 'Webserver: '.$_SERVER['SERVER_SOFTWARE'].PHP_EOL;
echo 'PHP: '.PHP_VERSION.'-'.PHP_SAPI.PHP_EOL;
show_source(__FILE__);
Note: The MySQL server is my own Win x64 compilation. Nevertheless,
PHP shouldn't have any problems with MySQL 5.5.
Regards,
Tommy
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP+MySQL question
On 11-02-16 11:55 AM, Robert Cummings wrote: On 11-02-16 11:37 AM, דניאל דנון wrote: Hi. I have a table called "images" with 4 columns - `image_id`, `item_name`, `image_url`, `image_views` (Where image_id is UNIQUE and AUTO-INCREMENT). Sometimes, there might be many items with the same name (but not with the same url). I want to make sure that each "item name" has at most 3 images (and therefore I need to delete the rest). The problem is that I want to keep the images with the most views. I've tried to look for efficient solutions either in MySQL or in PHP, but they are mostly very resource-intensive, Such as selecting all different names in PHP (using GROUP BY), then, for each name, doing DELETE FROM images WHERE item_name = 'ITEM-NAME-HERE' ORDER BY image_views ASC LIMIT (here some sub-query with count on how many rows have the name ITEM-NAME-HERE minus 3). I'd be glad if anyone could help me or point me to the right direction. I'd use a cron job to manage the purging process... and off the top of my head I'd probably go the following route: Get the list of images with more than 3 of the same name: SELECT item_name, SUM( 1 ) AS total FROM images HAVING total> 3; Get the 3 best images for each image returned above: SELECT image_id FROM images WHERE image_name = '[[NAME]]' ORDER BY image_views DESC. Oops... that should have a LIMIT clause on it: SELECT image_id FROM images WHERE image_name = '[[NAME]]' ORDER BY image_views DESC LIMIT 3 Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP+MySQL question
On 11-02-16 11:37 AM, דניאל דנון wrote: Hi. I have a table called "images" with 4 columns - `image_id`, `item_name`, `image_url`, `image_views` (Where image_id is UNIQUE and AUTO-INCREMENT). Sometimes, there might be many items with the same name (but not with the same url). I want to make sure that each "item name" has at most 3 images (and therefore I need to delete the rest). The problem is that I want to keep the images with the most views. I've tried to look for efficient solutions either in MySQL or in PHP, but they are mostly very resource-intensive, Such as selecting all different names in PHP (using GROUP BY), then, for each name, doing DELETE FROM images WHERE item_name = 'ITEM-NAME-HERE' ORDER BY image_views ASC LIMIT (here some sub-query with count on how many rows have the name ITEM-NAME-HERE minus 3). I'd be glad if anyone could help me or point me to the right direction. I'd use a cron job to manage the purging process... and off the top of my head I'd probably go the following route: Get the list of images with more than 3 of the same name: SELECT item_name, SUM( 1 ) AS total FROM images HAVING total > 3; Get the 3 best images for each image returned above: SELECT image_id FROM images WHERE image_name = '[[NAME]]' ORDER BY image_views DESC. (Make sure to quote your criteria properly in the above-- this is pseudo codish). Now delete the laggards using the ID we just retrieved: DELETE FROM images WHERE image_id NOT IN ([[ID_LIST]]). That should get you to a decent solution. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP+MySQL question
Hi. I have a table called "images" with 4 columns - `image_id`, `item_name`, `image_url`, `image_views` (Where image_id is UNIQUE and AUTO-INCREMENT). Sometimes, there might be many items with the same name (but not with the same url). I want to make sure that each "item name" has at most 3 images (and therefore I need to delete the rest). The problem is that I want to keep the images with the most views. I've tried to look for efficient solutions either in MySQL or in PHP, but they are mostly very resource-intensive, Such as selecting all different names in PHP (using GROUP BY), then, for each name, doing DELETE FROM images WHERE item_name = 'ITEM-NAME-HERE' ORDER BY image_views ASC LIMIT (here some sub-query with count on how many rows have the name ITEM-NAME-HERE minus 3). I'd be glad if anyone could help me or point me to the right direction. Daniel. -- Use ROT26 for best security
[PHP] Mysql 5.5 and PHP Mysql API Version 5.1.41
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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search query ignoring dots
Hi,
Monday, January 24, 2011, 10:50:41 PM, you wrote:
BP> Hi all,
BP> I have to perform a mysql query in a table with millions of records.
BP> I've full-text indexed my search field and I'm searching with MATCH AGAINST.
BP> But there is a problem. In this field there are company names that
BP> contain dots, for istance I've "PO.SE. srl" and I want to find it if
BP> the user search for: "POSE" or "PO.SE" or "P.O.S.E." etc.
BP> I googled in the web but I don't find any solution. I don't want to
BP> add a new field with the cleaned version of my string because I would
BP> like to solve with the query and I prefer that the mysql table not
BP> become too big. But if I will not find a different solution, I will
BP> use this escamotage.
BP> I've find a post that is similar but the solution don't seem to solve
BP> my situation.
BP> You can see it at the url:
BP> http://forums.mysql.com/read.php?10,395557,395584#msg-395584
BP> In my case replace(email, '.', '') = replace(theSearchValue, '.', '');
BP> is indifferent and don't change my results.
BP> My query, searching "POSE", is:
BP> select aziende.* from aziende where 1>0 AND
BP> (MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
BP> (replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
BP> '')) order by aziende.ragione_sociale limit 0, 10
BP> The alternative choice could be REGEXP but I've red that it make my
BP> query slow in a table of millions of records and I don't know how to
BP> exclude dots in the regular expression.
BP> Can anyone help me?
BP> Thanks in advance.
BP> Barbara
BP> --
BP>
BP> Barbara Picci
BP> Micro srl
BP> viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
BP> http://www.microsrl.com
In the interest of speed it may be worth creating a table with just a
link id and the text to search which you can cleanup before inserting.
It will probably save you a headache in the future and will be quicker
than complicated queries.
The list of possible ids can then be tested against the full table for
any other criteria which if the table is indexed properly will be fast
too.
--
regards,
Tom
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql search query ignoring dots
> -Original Message-
> From: Barbara Picci [mailto:barbara.pi...@sardi.it]
> Sent: Monday, January 24, 2011 4:51 AM
> To: php-general@lists.php.net
> Subject: [PHP] Mysql search query ignoring dots
>
> Hi all,
>
> I have to perform a mysql query in a table with millions of records.
> I've full-text indexed my search field and I'm searching with MATCH
> AGAINST.
> But there is a problem. In this field there are company names that contain
> dots, for istance I've "PO.SE. srl" and I want to find it if the user
search for:
> "POSE" or "PO.SE" or "P.O.S.E." etc.
> I googled in the web but I don't find any solution. I don't want to add a
new
> field with the cleaned version of my string because I would like to solve
> with the query and I prefer that the mysql table not become too big. But
if I
> will not find a different solution, I will use this escamotage.
> I've find a post that is similar but the solution don't seem to solve my
> situation.
> You can see it at the url:
> http://forums.mysql.com/read.php?10,395557,395584#msg-395584
> In my case replace(email, '.', '') = replace(theSearchValue, '.', ''); is
> indifferent and don't change my results.
>
> My query, searching "POSE", is:
>
> select aziende.* from aziende where 1>0 AND
> (MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
> (replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
> '')) order by aziende.ragione_sociale limit 0, 10
>
> The alternative choice could be REGEXP but I've red that it make my query
> slow in a table of millions of records and I don't know how to exclude
dots
> in the regular expression.
>
> Can anyone help me?
>
> Thanks in advance.
> Barbara
>
> --
>
> Barbara Picci
> Micro srl
> viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
> http://www.microsrl.com
>
I don't see anything relevant regarding PHP. As for ' a table of millions
of records,' that sounds like questions for the DBA.
Regards,
Tommy
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:40, Daniel P. Brown wrote: > For your convenience, both have been CC'd on this email. Actually, PHP-DB (php...@lists.php.net) was accidentally BCC'd. -- Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:35, Don Wieland wrote: > Hi all, > > Is there a list/form to get some help on compiling mySQL queries? I am > executing them via PHP, but do not want to ask for help here if it is no the > appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mySQL query assistance...
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 ;-) Don -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL database export to Excel
On Fri, 2010-11-26 at 15:21 +0200, Sotiris Katsaniotis wrote:
> Greetings fellow PHP developers!
>
> I am looking of a relatively simple way to export a whole database into
> an Excel file. I have several methods to export tables to Excel files
> but unfortunately I have failed to export a whole database!
>
> Can someone be so kind to point me in the right direction?
>
> Thanks a lot!
>
DISCLAIMER
This is untested, and just off the top of my pointy head, do some
research, and know how much strain this COULD put on your system.
/DISCLAIMER
there are MANY MySQL commands that you can use, and combine them into 1
script, and then export them into excel, using multiple pages with
something like this:
http://www.codediesel.com/php/creating-excel-documents-in-php/
example mysql queries:
SHOW DATABASES; -- gets a lits of all the databases (skip MySQL and
information_schema)
SHOW TABLES FROM {tablename}; -- gets all the tables in a specific
database
SELECT column_name FROM information_schema.columns WHERE
table_name='{tablename}' AND table_schema='{databasename}'; -- gets you
a list of all the column headers (there are other ways you can avoid
doing this, but this works)
SELECT * FROM {databasename}.{tablename}; -- gets all the rows from a
table in the database
if you were to create a script, and providing your scipt has enough
"max_execution_time", and "memory_limit" (should prolly be done from the
CLI) you can prolly get it done...
and yeah, this is a pointer to how it *COULD* be done, but it's not the
only way, nor is it prolly the best either.
Good Luck, and if you need more MySQL related questions answered, try
the my...@lists.mysql.com mailing list. they are really good over
there.
Steve
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL database export to Excel
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! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql data encryption / Transparent data encyrption
Hello, This doesn't relates to php directly. Oracle , MSSQL supports TDE innately. Is there any way to adding that support to mysql too ? Looking for some free, opensource solution. Something which won't be much of a hit on performance. thanks -- Regards, Nitesh Nandy
Re: [PHP] MySQL Query Help
On Sun, 21 Nov 2010 11:19:04 -0700 "Ben Miller" wrote: > > To help clarify - the 3 tables look something like the following (tableName > => column,column,column...): > > Products => product_id,product_name,product_description... (key = > product_id) > Criteria => criteria_id,criteria_title,criteria_text,... (key = > criteria_id) > Criteria_values => product_id,criteria_id,criteria_value,... (key = > product_id & criteria_id) > > The user selects up to X product_id's to compare, stored in > $selected_products. > > I then need to get each criteria_title and criteria_text from > table(criteria) where there is a matching criteria_id in > table(criteria_values) for each/all $selected_products, also returning the > criteria_value for each $selected_products, ultimately ending up with an > array or object that looks something like: > > (Assuming the user selected Product A (product_id=1), Product B > (product_id=2) and Product C (product_id=3) > > criteria => Array ( > [$criteria_id] => Array ( > [title] => query_row[criteria_title] > [text] => query_row[criteria_text] > [values] => Array ( > [1] => Product A's value for this criteria > [2] => Product B's value for this criteria > [3] => Product C's value for this criteria > ) > ) > [$criteria_id] => Array ( > . > ) > ) > > Again, displaying only/all criteria where there is a matching value for > each/all $selected_products > > Thanks again, > Ben > > You should probably select all relevant rows for each product, without checking that a given criteria has a matching value for each product. Use php to filter out the criteria which do not apply to all products. -- Simcha Younger -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help
SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT
JOIN criteria c ON cv.key=c.key WHERE c.value IS NOT NULL
Hard to answer without more detail, but I am guessing the answer will be
something like the above. Your question makes it hard to understand whether
c or cv is joined to p. So swap em around if I misunderstood.
iPhone 4. It rocks!
On Nov 21, 2010, at 1:37 AM, Simcha Younger wrote:
> On Sat, 20 Nov 2010 13:54:29 -0700
> "Ben Miller" wrote:
>
>> Hi,
>>
>> I'm building a website for a client in which I need to compare their
>> products, side-by-side, but only include criteria for which all
>> selected products have a value for that criteria.
>>
>> In my database (MySQL), I have a tables named "products","criteria"
>> and "criteria_values"
>>
>> If I have something like
>>
>> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) //
>> All products selected for comparison by the user
>>
>> I need to get only rows from "criteria" where there is a row in
>> "criteria_values" matching "criteria.criteria_id" for each
>> $selected_product
>> - in other words, if any of the $selected_product does not have a row
>> in "criteria_values" that matches "criteria.criteria_id", that
>> criteria would not be returned. I hope that makes sense.
>
> It would be a lot easier to think about this if you could provide the
table structure or create table statements.
>
> If I understood correctly, you have products which reference a criteria ID
which has no matching value. If this is the problem you have a to first take
care of the integrity of your data, as this should never happen.
>
To help clarify - the 3 tables look something like the following (tableName
=> column,column,column...):
Products => product_id,product_name,product_description... (key =
product_id)
Criteria => criteria_id,criteria_title,criteria_text,... (key =
criteria_id)
Criteria_values => product_id,criteria_id,criteria_value,... (key =
product_id & criteria_id)
The user selects up to X product_id's to compare, stored in
$selected_products.
I then need to get each criteria_title and criteria_text from
table(criteria) where there is a matching criteria_id in
table(criteria_values) for each/all $selected_products, also returning the
criteria_value for each $selected_products, ultimately ending up with an
array or object that looks something like:
(Assuming the user selected Product A (product_id=1), Product B
(product_id=2) and Product C (product_id=3)
criteria => Array (
[$criteria_id] => Array (
[title] => query_row[criteria_title]
[text] => query_row[criteria_text]
[values] => Array (
[1] => Product A's value for this criteria
[2] => Product B's value for this criteria
[3] => Product C's value for this criteria
)
)
[$criteria_id] => Array (
.
)
)
Again, displaying only/all criteria where there is a matching value for
each/all $selected_products
Thanks again,
Ben
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT JOIN
criteria c ON cv.key=c.key WHERE c.value IS NOT NULL
Hard to answer without more detail, but I am guessing the answer will be
something like the above. Your question makes it hard to understand whether c
or cv is joined to p. So swap em around if I misunderstood.
iPhone 4. It rocks!
On Nov 21, 2010, at 1:37 AM, Simcha Younger wrote:
> On Sat, 20 Nov 2010 13:54:29 -0700
> "Ben Miller" wrote:
>
>> Hi,
>>
>> I'm building a website for a client in which I need to compare their
>> products, side-by-side, but only include criteria for which all selected
>> products have a value for that criteria.
>>
>> In my database (MySQL), I have a tables named "products","criteria" and
>> "criteria_values"
>>
>> If I have something like
>>
>> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
>> products selected for comparison by the user
>>
>> I need to get only rows from "criteria" where there is a row in
>> "criteria_values" matching "criteria.criteria_id" for each $selected_product
>> - in other words, if any of the $selected_product does not have a row in
>> "criteria_values" that matches "criteria.criteria_id", that criteria would
>> not be returned. I hope that makes sense.
>
> It would be a lot easier to think about this if you could provide the table
> structure or create table statements.
>
> If I understood correctly, you have products which reference a criteria ID
> which has no matching value. If this is the problem you have a to first take
> care of the integrity of your data, as this should never happen.
>
>
> --
> Simcha Younger
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
On Sat, 20 Nov 2010 13:54:29 -0700
"Ben Miller" wrote:
> Hi,
>
> I'm building a website for a client in which I need to compare their
> products, side-by-side, but only include criteria for which all selected
> products have a value for that criteria.
>
> In my database (MySQL), I have a tables named "products","criteria" and
> "criteria_values"
>
> If I have something like
>
> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
> products selected for comparison by the user
>
> I need to get only rows from "criteria" where there is a row in
> "criteria_values" matching "criteria.criteria_id" for each $selected_product
> - in other words, if any of the $selected_product does not have a row in
> "criteria_values" that matches "criteria.criteria_id", that criteria would
> not be returned. I hope that makes sense.
It would be a lot easier to think about this if you could provide the table
structure or create table statements.
If I understood correctly, you have products which reference a criteria ID
which has no matching value. If this is the problem you have a to first take
care of the integrity of your data, as this should never happen.
--
Simcha Younger
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help
Since we are just tossing out development environments.
We moved to Aptana in conjunction with TortoiseSVN for a team environment
development timelines dropped.
Personally I do not feel any gui editor makes you a better programmer, maybe
you understand the fundamentals a little less.
Not that anything so far has been an answer to your question.
Developing mysql statements that not only require minimum resources but that
are highly effective. Take a lot of trial and error.
I feel there is no better tool in my mind to test query concepts than
NaviCat.
Not only is the product very user friendly but as a senior developer it
gives me more insight into the impact the query has on my servers.
Always think longevity of the product you are producing.
Imagine one day you have 650,000 products how will the query impact the
service you have written.
I feel as a certified MySQL DBA you should understand that what works today
may NOT be the best choice in query statements for the future.
Always analyze your query statements for query length and system resources
requirements.
Depending on the structure of your database/tables/fields your query may be
achieved many different ways.
I might suggest you try an extended select statement.
"SELECT product from sometable WHERE product='$array1' AND product=(SELECT
product_name from sometable where other matching critera)";
By extending or what some may call concating the statement the return is
more effective.
Richard L. Buskirk
-Original Message-
From: Ben Miller [mailto:biprel...@gmail.com]
Sent: Saturday, November 20, 2010 3:54 PM
To: 'php-general'
Subject: [PHP] MySQL Query Help
Hi,
I'm building a website for a client in which I need to compare their
products, side-by-side, but only include criteria for which all selected
products have a value for that criteria.
In my database (MySQL), I have a tables named "products","criteria" and
"criteria_values"
If I have something like
$selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
products selected for comparison by the user
I need to get only rows from "criteria" where there is a row in
"criteria_values" matching "criteria.criteria_id" for each $selected_product
- in other words, if any of the $selected_product does not have a row in
"criteria_values" that matches "criteria.criteria_id", that criteria would
not be returned. I hope that makes sense.
I've played around with a few join queries, but none have given the desired
results. Best I've been able to come up with so far is to query "criteria"
for each DISTINCT(criteria_id) and then run through each $selected_product
to make sure each has a criteria_value with a matching criteria_id,
eliminating any criteria where the number of criteria_values <
count($selected_product), but this seems pretty inefficient.
Thanks in advance for any help.
Ben Miller
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
I'm going to jump in and throw in my 2 cents...
Have you used dreamweaver?
I would suggest Dreamweaver to any new programmer beginning php/mysql.
It helped me out tremendously in the beginning. I'm not an advanced programmer
with hand coding classes yet, but I can get any job completed for clients with
dreamweaver. Custom content management systems, image galleries from mysql
etc...
Give it a try, It lets you add the the prewritten code and then you can switch
to code view and see whats its doing.
RD
On Nov 20, 2010, at 3:54 PM, Ben Miller wrote:
> Hi,
>
> I'm building a website for a client in which I need to compare their
> products, side-by-side, but only include criteria for which all selected
> products have a value for that criteria.
>
> In my database (MySQL), I have a tables named "products","criteria" and
> "criteria_values"
>
> If I have something like
>
> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
> products selected for comparison by the user
>
> I need to get only rows from "criteria" where there is a row in
> "criteria_values" matching "criteria.criteria_id" for each $selected_product
> - in other words, if any of the $selected_product does not have a row in
> "criteria_values" that matches "criteria.criteria_id", that criteria would
> not be returned. I hope that makes sense.
>
> I've played around with a few join queries, but none have given the desired
> results. Best I've been able to come up with so far is to query "criteria"
> for each DISTINCT(criteria_id) and then run through each $selected_product
> to make sure each has a criteria_value with a matching criteria_id,
> eliminating any criteria where the number of criteria_values <
> count($selected_product), but this seems pretty inefficient.
>
> Thanks in advance for any help.
>
> Ben Miller
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[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
Re: [PHP] mysql help (sorry, a bit OT)
Hello Gary, Actually, what I do here is the following: I create a subdomain called beta.mysite.com (for me it's http://beta.oire.org/ and http://beta.gviragon.org/ :-)). There I have a copy of my database and all of my files. The only difference is the mysql_select_db in the connect.php file. There I can easily test everything I need (including PHP files and MySql queries) without any danger to crash the main release). -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion - Original message - From: Gary To: php-general@lists.php.net Date: Tuesday, November 16, 2010, 3:35:12 PM Subject: [PHP] mysql help (sorry, a bit OT) Is there a way to check the syntax of a query, short of running it? I've got an insert to do (but of course it's a valid question for any query that changes the db contents) and would like to know that the sql I am generating (in php - see! not so off-topic!) is correct. What I don't want to do is run it for testing (live system *sigh*) and find out it is correct (it will change the db), but... I have to test it to check that the syntax (at least) *is* correct. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql help (sorry, a bit OT)
> -Original Message- > From: Gary [mailto:php-gene...@garydjones.name] > Sent: Tuesday, November 16, 2010 5:35 AM > To: php-general@lists.php.net > Subject: [PHP] mysql help (sorry, a bit OT) > > Is there a way to check the syntax of a query, short of running it? I've got an > insert to do (but of course it's a valid question for any query that changes > the db contents) and would like to know that the sql I am generating (in php > - see! not so off-topic!) is correct. > > What I don't want to do is run it for testing (live system *sigh*) and find out > it is correct (it will change the db), but... I have to test it to check that the > syntax (at least) *is* correct. > Gary, If you use a current version of the MySQL workbench, the tool can send complete SELECT, UPDATE, INSERT, DELETE statement, with all the fields, to the query window for the selected table. That way you'll know that you have the proper field (name and quoted with `). Add that to what Ash suggested of having a local copy of the database, you'll have little or no chance of breakage in the app because of a silly SQL syntax error ;) Regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql help (sorry, a bit OT)
It's always best practice to have a staging server for testing these sorts of things. If in doubt, run it in a transaction, but don't commit it, I.e. roll it back. That way you'll see if it would run but nothing actually changes. Thanks, Ash http://www.ashleysheridan.co.uk - Reply message - From: "Gary" Date: Tue, Nov 16, 2010 13:35 Subject: [PHP] mysql help (sorry, a bit OT) To: Is there a way to check the syntax of a query, short of running it? I've got an insert to do (but of course it's a valid question for any query that changes the db contents) and would like to know that the sql I am generating (in php - see! not so off-topic!) is correct. What I don't want to do is run it for testing (live system *sigh*) and find out it is correct (it will change the db), but... I have to test it to check that the syntax (at least) *is* correct. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Open Source PHP/ mySQL Project Management
Redmine is (I think) one of the best out there. It is written in ruby and does have all you need + some more. Cheers! On Fri, Nov 12, 2010 at 12:23 AM, Don Wieland wrote: > Hi gang, > > I am looking into Project Management apps for my projects. Any suggestions: > > I am interested in tracking Projects, Milestones, Tickets, Files, > Discussions, Documents, Time Tracking, etc... Also, would like to have the > system have robust email integration Reminders, Email Ticket echos (where a > user can reply it will post back into the PM system and echo back email to > assigned users - with file attachments) > > Suggestions? Thanks! > > Don > > -- > 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: Re[2]: [PHP] Open Source PHP/ mySQL Project Management
With the exception of some work-mandated systems such as fogbugz, I've used mantis successfully for quite a long time. On Fri, Nov 12, 2010 at 1:09 AM, Andre Polykanine wrote: > Hello Jonathan, > > I tried to use Mantis, however it didn't send e-mails properly so I > gave up. > -- > With best regards from Ukraine, > Andre > Skype: Francophile > Twitter: http://twitter.com/m_elensule > Facebook: http://facebook.com/menelion > > - Original message - > From: Jonathan Tapicer > To: Don Wieland > Date: Friday, November 12, 2010, 3:00:32 AM > Subject: [PHP] Open Source PHP/ mySQL Project Management > > Hi, > > I don't know if it meets all of the features you enumerated but Mantis > (http://www.mantisbt.org/) is very good, and it is PHP+MySQL (or > Postgres, or MSSQL). > > Jonathan > > On Thu, Nov 11, 2010 at 7:23 PM, Don Wieland > wrote: > > Hi gang, > > > > I am looking into Project Management apps for my projects. Any > suggestions: > > > > I am interested in tracking Projects, Milestones, Tickets, Files, > > Discussions, Documents, Time Tracking, etc... Also, would like to have > the > > system have robust email integration Reminders, Email Ticket echos (where > a > > user can reply it will post back into the PM system and echo back email > to > > assigned users - with file attachments) > > > > Suggestions? Thanks! > > > > Don > > > > -- > > 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 > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- http://blogs.linux.ie/kenguest/
Re[2]: [PHP] Open Source PHP/ mySQL Project Management
Hello Jonathan, I tried to use Mantis, however it didn't send e-mails properly so I gave up. -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion - Original message - From: Jonathan Tapicer To: Don Wieland Date: Friday, November 12, 2010, 3:00:32 AM Subject: [PHP] Open Source PHP/ mySQL Project Management Hi, I don't know if it meets all of the features you enumerated but Mantis (http://www.mantisbt.org/) is very good, and it is PHP+MySQL (or Postgres, or MSSQL). Jonathan On Thu, Nov 11, 2010 at 7:23 PM, Don Wieland wrote: > Hi gang, > > I am looking into Project Management apps for my projects. Any suggestions: > > I am interested in tracking Projects, Milestones, Tickets, Files, > Discussions, Documents, Time Tracking, etc... Also, would like to have the > system have robust email integration Reminders, Email Ticket echos (where a > user can reply it will post back into the PM system and echo back email to > assigned users - with file attachments) > > Suggestions? Thanks! > > Don > > -- > 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Open Source PHP/ mySQL Project Management
Hi, I don't know if it meets all of the features you enumerated but Mantis (http://www.mantisbt.org/) is very good, and it is PHP+MySQL (or Postgres, or MSSQL). Jonathan On Thu, Nov 11, 2010 at 7:23 PM, Don Wieland wrote: > Hi gang, > > I am looking into Project Management apps for my projects. Any suggestions: > > I am interested in tracking Projects, Milestones, Tickets, Files, > Discussions, Documents, Time Tracking, etc... Also, would like to have the > system have robust email integration Reminders, Email Ticket echos (where a > user can reply it will post back into the PM system and echo back email to > assigned users - with file attachments) > > Suggestions? Thanks! > > Don > > -- > 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] Open Source PHP/ mySQL Project Management
Hello Don, I would suggest Trac. It is written in Python, however I haven't seen anything better for a while. -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion - Original message - From: Don Wieland To: php-general@lists.php.net Date: Friday, November 12, 2010, 12:23:11 AM Subject: [PHP] Open Source PHP/ mySQL Project Management Hi gang, I am looking into Project Management apps for my projects. Any suggestions: I am interested in tracking Projects, Milestones, Tickets, Files, Discussions, Documents, Time Tracking, etc... Also, would like to have the system have robust email integration Reminders, Email Ticket echos (where a user can reply it will post back into the PM system and echo back email to assigned users - with file attachments) Suggestions? Thanks! Don -- 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
[PHP] Open Source PHP/ mySQL Project Management
Hi gang, I am looking into Project Management apps for my projects. Any suggestions: I am interested in tracking Projects, Milestones, Tickets, Files, Discussions, Documents, Time Tracking, etc... Also, would like to have the system have robust email integration Reminders, Email Ticket echos (where a user can reply it will post back into the PM system and echo back email to assigned users - with file attachments) Suggestions? Thanks! Don -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] php - mysql tandem and multi core performance
Hi all, I have to setup a new dedicated server for my job, it will need to host some mysql databases with php accessing them, for a number of reasons it need to be a Windows server ( 2008 ) the amount of data will not be very great ( the biggest table will contain between 100.000 and 200.000 rows ), but php will perform some complex, full-text queries to the databases. now I have a couple of choices about server configuration: 1) Bi Xeon E5504 - it has 8 cores at 2.4 Ghz, with 24 GB RAM 2) Xeon i7 W3520 - it has 4 cores ar 2.66 Ghz with 12 GB RAM obviously the second choice is quite cheaper than the first; my question is: on your experience, how do php / mySQL behave on multicores? maybe its better a lesser_core / highest frequency system than a more_core / lower frequency? can a 12 GB / 24 GB RAM configuration make the difference? in which terms? thank you for your answer, Giulio Giulio Mastrosanti giu...@cantoberon.it -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] protecting a directory by php mysql
On Thu, 2010-08-12 at 02:02 +0430, Ali Asghar Toraby Parizy wrote: > Hi. > I need a protected directory in my server that only the authenticated user > can reach all files in that folder(file type is zip and picture or a web > page, maybe). I save user ID and passwords in mysql database. today I tried > to use htaccess but mod_auth_mysql isn't installed on my server. > I know that I can protect web pages by a simple PHP script(checking log in > session) but I don't know how I can protect images and files! > Thanks for any help You can protect with a .htaccess with a more basic protection, by having a password stored in an encrypted form in a file somewhere that Apache can see. However, if you need to authorise users via details in a DB, then your best bet is to store all the files themselves outside of the web root, and then use a script to access them. The script can authenticate users however you want, and only when they are logged in will it read from the file and write that back out to the client with the correct headers. If you can't put the files outside of web root, then you have little hope of securing them if you can't get mod_auth_mysql installed. Thanks, Ash http://www.ashleysheridan.co.uk
[PHP] protecting a directory by php mysql
Hi. I need a protected directory in my server that only the authenticated user can reach all files in that folder(file type is zip and picture or a web page, maybe). I save user ID and passwords in mysql database. today I tried to use htaccess but mod_auth_mysql isn't installed on my server. I know that I can protect web pages by a simple PHP script(checking log in session) but I don't know how I can protect images and files! Thanks for any help -- Ali Asghar Torabi
[PHP] MySQL+PHP
Dear all,
I'm a newbie, need your help.
Now I am building a databasea using MySQL and PHP, I want to put two tables
in one searching, the two are in one database.
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM table1 WHERE ".implode(" AND
", $data);
$number = "SELECT FOUND_ROWS()";
How can I change this if I want to put table2 in it.
Thank you.
Best wishes,
diana
2010-7-21
Re: [PHP] MySQL select matching
On Mon, 19 Jul 2010 10:36:40 -0600 "Ashley M. Kirchner" wrote: > mysql> select * from table where id='1'; > +---+-+-+---+ > | 1 | 123 | 0.0 | C | > | 1 | 234 | 0.1 | D | > | 1 | 345 | 0.0 | D | > | 1 | 456 | 0.1 | C | > | 1 | 567 | 0.1 | G | > +---+-+-+---+ > > Now, I have to find other IDs that match the above result. In the > table, that would be ID '3' (and in the entire DB, there may be > others as well - I need to find all those IDs.) But, notice how ID 0003 > isn't in the same order as ID 1, but the data is still the same. > select distinct id from `table` where concat(`b`, `c`, `d`) in (select concat(`b`,`c`,`d` from `table` where id = '0001') AND id != '0001'; (untested) -- Simcha Younger -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
I am very keen to see a closure to this thread so that I can add to my snippets. Let's all know what worked best out of many solutions that have been proposed. --Shreyas On Tue, Jul 20, 2010 at 10:07 AM, Jim Lucas wrote: > Peter wrote: > >> Hi All, >> >> I have a table which contain's some duplicate rows. I just want to delete >> the duplicate records alone >> not original records. >> >> Assume my table as look as below >> >> column1 column2 >> 1 >>a >> 1 >>a >> 2 >>b >> 3 >>c >> 3 >>c >> >> >> >> i want the above table need to be as below, After executing the mysql >> query. >> >> column1 >>column2 >> 1 >>a >> 2 >>b >> 3 >>c >> >> >> >> >> Thanks in advance.. >> >> Regards >> Peter >> >> > Use the SQL command alter with the ignore flag. > > ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` ) > > I tested this on my test DB and it worked fine. It erased all the > duplicates and left one instance of the multiple entry values. > > This will add a permanent unique restraint to the table. So, you will > never have dupps again. > > Jim Lucas > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
Peter wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter Use the SQL command alter with the ignore flag. ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` ) I tested this on my test DB and it worked fine. It erased all the duplicates and left one instance of the multiple entry values. This will add a permanent unique restraint to the table. So, you will never have dupps again. Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Just to add more perspective : You will have a table with DISTINCT values. Drop the initial table (better take a back-up); copy from the temporary table which will have only DISTINCT values. Regards, Shreyas On Mon, Jul 19, 2010 at 7:58 PM, Shreyas Agasthya wrote: > How about this : > > CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); > INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; > > Regards, > Shreyas > > On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling wrote: > >> On 19 July 2010 05:44, Peter wrote: >> > Hi All, >> > >> > I have a table which contain's some duplicate rows. I just want to >> delete >> > the duplicate records alone >> > not original records. >> > >> > Assume my table as look as below >> > >> > column1 column2 >> > 1 >> >a >> > 1 >> >a >> > 2 >> >b >> > 3 >> >c >> > 3 >> >c >> > >> > >> > >> > i want the above table need to be as below, After executing the mysql >> > query. >> > >> > column1 >> >column2 >> > 1 >> >a >> > 2 >> >b >> > 3 >> >c >> > >> > >> > >> > >> > Thanks in advance.. >> > >> > Regards >> > Peter >> > >> >> If your table had a db generated sequential unique identifier (an >> identity / autoinc), then something along these lines may be what you >> are looking for ... >> >> -- Delete everything except the UniqueIDs we want to keep. >> DELETE FROM >>Table >> WHERE >>UniqueID NOT IN >>( >>-- Just get the UniqueIDs we want to keep. >>SELECT >>UniqueID >>FROM >>( >>-- Get the earlist UniqueID for each Col1, Col2, >> pairing. >>SELECT >>Col1, >>Col2, >>MIN(UniqueID) AS UniqueID >>FROM >>Table >>GROUP BY >>Col1, >>Col2 >>) >>) >> >> UNTESTED >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > > -- > Regards, > Shreyas Agasthya > -- Regards, Shreyas Agasthya
[PHP] MySQL select matching
I may be going at this completely wrong but at the moment I'm stuck. I have a DB from a client and need to do several searches on it. This one sentence is important because it's their DB, not mine. So I can't modify the way the DB was created in the first place, I can only work with what I have. And, whatever the solution to this might be, it does NOT have to be strictly MySQL, it can also be a PHP solution (which is why I'm sending it there as well.) So, having said that, consider the following table: +---+-+-+---+ | 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 | | 2 | 123 | 0.0 | C | | 2 | 234 | 0.1 | D | | 2 | 345 | 0.0 | D | | 3 | 234 | 0.1 | D | | 3 | 345 | 0.0 | D | | 3 | 123 | 0.0 | C | | 3 | 456 | 0.1 | C | | 3 | 567 | 0.1 | G | | 4 | 123 | 0.0 | C | | 4 | 234 | 0.1 | D | | 4 | 345 | 0.0 | D | +---+-+-+---+ 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. So how do I efficiently search through the DB to find other IDs that matches the one I need? I can't imagine doing a for loop selecting each ID and comparing their result to the one I'm starting with. If the DB contains thousands upon thousands of rows, that might take a very long time. Open to suggestions. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
How about this : CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; Regards, Shreyas On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling wrote: > On 19 July 2010 05:44, Peter wrote: > > Hi All, > > > > I have a table which contain's some duplicate rows. I just want to > delete > > the duplicate records alone > > not original records. > > > > Assume my table as look as below > > > > column1 column2 > > 1 > >a > > 1 > >a > > 2 > >b > > 3 > >c > > 3 > >c > > > > > > > > i want the above table need to be as below, After executing the mysql > > query. > > > > column1 > >column2 > > 1 > >a > > 2 > >b > > 3 > >c > > > > > > > > > > Thanks in advance.. > > > > Regards > > Peter > > > > If your table had a db generated sequential unique identifier (an > identity / autoinc), then something along these lines may be what you > are looking for ... > > -- Delete everything except the UniqueIDs we want to keep. > DELETE FROM >Table > WHERE >UniqueID NOT IN >( >-- Just get the UniqueIDs we want to keep. >SELECT >UniqueID >FROM >( >-- Get the earlist UniqueID for each Col1, Col2, > pairing. >SELECT >Col1, >Col2, >MIN(UniqueID) AS UniqueID >FROM >Table >GROUP BY >Col1, >Col2 >) >) > > UNTESTED > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
On 19 July 2010 15:01, Richard Quadling wrote: > On 19 July 2010 05:44, Peter wrote: >> Hi All, >> >> I have a table which contain's some duplicate rows. I just want to delete >> the duplicate records alone >> not original records. >> >> Assume my table as look as below >> >> column1 column2 >> 1 >> a >> 1 >> a >> 2 >> b >> 3 >> c >> 3 >> c >> >> >> >> i want the above table need to be as below, After executing the mysql >> query. >> >> column1 >> column2 >> 1 >> a >> 2 >> b >> 3 >> c >> >> >> >> >> Thanks in advance.. >> Slightly more concise ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Get the earliest UniqueIDs for each Col1, Col2 pairing. SELECT MIN(UniqueID) FROM Table GROUP BY Col1, Col2 ) http://www.devx.com/tips/Tip/14665 DELETE Table FROM Table T1, Table T2 WHERE T1.Col1 = T2.Col1 AND T1.Col2 = T2.Col2 AND T1.UniqueID > T2.UniqueID http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm etc. Many different ways. http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table Method 3 should be the fastest. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On 19 July 2010 05:44, Peter wrote: > Hi All, > > I have a table which contain's some duplicate rows. I just want to delete > the duplicate records alone > not original records. > > Assume my table as look as below > > column1 column2 > 1 > a > 1 > a > 2 > b > 3 > c > 3 > c > > > > i want the above table need to be as below, After executing the mysql > query. > > column1 > column2 > 1 > a > 2 > b > 3 > c > > > > > Thanks in advance.. > > Regards > Peter > If your table had a db generated sequential unique identifier (an identity / autoinc), then something along these lines may be what you are looking for ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Just get the UniqueIDs we want to keep. SELECT UniqueID FROM ( -- Get the earlist UniqueID for each Col1, Col2, pairing. SELECT Col1, Col2, MIN(UniqueID) AS UniqueID FROM Table GROUP BY Col1, Col2 ) ) UNTESTED -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Hi Shiplu, Thanks for reply. Distinct function hide the duplicate records while we selecting the record through the Query i want to remove the duplicate entries in my table i need a Delete Query instead of Select Query shiplu wrote: Use distinct. SELECT DISTINCT COLUMN1, COLUMN2 FROM ... ... Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Use distinct. SELECT DISTINCT COLUMN1, COLUMN2 FROM ... ... Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On Mon, Jul 19, 2010 at 10:44 AM, Peter wrote: > Hi All, > > I have a table which contain's some duplicate rows. I just want to delete > the duplicate records alone > not original records. > > Assume my table as look as below > > column1 column2 > 1 > a > 1 > a > 2 > b > 3 > c > 3 > c > > > > i want the above table need to be as below, After executing the mysql > query. > > column1 > column2 > 1 > a > 2 > b > 3 > c > > > > > Thanks in advance.. > > Regards > Peter > Create a table with similar structure and add UNIQUE INDEX on both columns. Execute the following query: INSERT IGNORE INTO NEW_TABLE (column1, column2) SELECT column1, column2 FROM OLD_TABLE This will give you distinct rows as required. -- Keep Smiling :-) Shafiq Rehman Blog: http://shafiq.pk, Twitter: http://twitter.com/shafiq -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On Mon, Jul 19, 2010 at 10:14:30AM +0530, Peter wrote: > Hi All, > > I have a table which contain's some duplicate rows. I just want to > delete the duplicate records alone > not original records. > > Assume my table as look as below > > column1 column2 > 1 > a > 1 > a > 2 > b > 3 > c > 3 > c > > > > i want the above table need to be as below, After executing the mysql > query. > > column1 > column2 > 1 > a > 2 > b > 3 > c > > If you're looking for a MySQL solution to this, this is the wrong list to ask the question on. In fact, I'd be surprised to find a MySQL query which would do this. For a PHP solution, you'll need to query MySQL for all the rows, in order by the column you want to use to kill duplicates. Then loop through the rows one at a time in PHP, checking the contents of that column against the last iteration. If they are the same, issue a DELETE command in MySQL. Continue the loop until all rows are exhausted. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL Query Puzzle
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
Re: [PHP] mysql case statement
On Monday 28 June 2010 11:14:53 Andrew Ballard wrote: > On Mon, Jun 28, 2010 at 10:27 AM, David McGlone wrote: > > Tanel, we both learned something. I didn't fully understand join myself > > yet, but I think I do now. > > > > but let me ask this if the join wasn't there would an if statement like I > > mentioned have worked? > > > > Blessings, > > David M. > > I think you are confusing a few things. You can't really rely on > testing "empty(DB_HIDDENPANELS)" because the value of the constant > DB_HIDDENPANELS is most likely a string that was set with an earlier > call to define. The OP could have tested for the column value > "hiddenpanel" using an if (...) test as you suggeted. However, given > that the OP stated he "would like to select hiddenpanel only if there > is a corresponding value in DB_HIDDENPANELS," the INNER JOIN will do > that at the database query level, so an if (...) then test in PHP > isn't really necessary. That is exactly what I was wondering. I haven't got to joins yet, and didn't understand them at all until Richard explained a few minutes ago. I knew my suggestion was a complete shot in the dark and I did feel stupid posting it, but I'm glad now, because I learned something invaluable. Thanks to this list, someday, which I hope is sooner than later, I'll be able to help somebody with complete confidence. -- Blessings, David M. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Mon, Jun 28, 2010 at 10:27 AM, David McGlone wrote: > Tanel, we both learned something. I didn't fully understand join myself yet, > but I think I do now. > > but let me ask this if the join wasn't there would an if statement like I > mentioned have worked? > > Blessings, > David M. I think you are confusing a few things. You can't really rely on testing "empty(DB_HIDDENPANELS)" because the value of the constant DB_HIDDENPANELS is most likely a string that was set with an earlier call to define. The OP could have tested for the column value "hiddenpanel" using an if (...) test as you suggeted. However, given that the OP stated he "would like to select hiddenpanel only if there is a corresponding value in DB_HIDDENPANELS," the INNER JOIN will do that at the database query level, so an if (...) then test in PHP isn't really necessary. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Sunday 27 June 2010 22:12:41 Brandon Rampersad wrote: > no At least smack me and give us an explanation. :-) -- Blessings, David M. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Monday 28 June 2010 09:49:55 Andrew Ballard wrote:
> On Sun, Jun 27, 2010 at 4:08 AM, Tanel Tammik wrote:
> > Hello,
> >
> > how to select only if value is present?
> >
> >$query = $db->query("select menus.id, menus.name,
> > case
> >when panels.id is not null then '1'
> >end as hiddenpanel
> >
> >from " . \DB_MENUS . " as menus
> > left join " . \DB_HIDDENPANELS . " as panels on (menus.id =
> > panels.menu_id)
> >where menus.id='" . (int)$id . "'
> >");
> >
> > i would like to select hiddenpanel only if there is a corresponding value
> > in DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding
> > value in HIDDENPANELS table!
> >
> > Br
> > Tanel
>
> That's what a LEFT JOIN does - it returns all rows from the LEFT table
> that match the criteria in the WHERE clause, and then returns any rows
> from the RIGHT table that happen do match. If you only want rows that
> exist in both tables, change the join from LEFT (OUTER) JOIN to INNER
> JOIN.
Tanel, we both learned something. I didn't fully understand join myself yet,
but I think I do now.
but let me ask this if the join wasn't there would an if statement like I
mentioned have worked?
Blessings,
David M.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
On Sun, Jun 27, 2010 at 4:08 AM, Tanel Tammik wrote:
> Hello,
>
> how to select only if value is present?
>
> $query = $db->query("select menus.id, menus.name,
> case
> when panels.id is not null then '1'
> end as hiddenpanel
>
> from " . \DB_MENUS . " as menus
> left join " . \DB_HIDDENPANELS . " as panels on (menus.id =
> panels.menu_id)
> where menus.id='" . (int)$id . "'
> ");
>
> i would like to select hiddenpanel only if there is a corresponding value in
> DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding value
> in HIDDENPANELS table!
>
> Br
> Tanel
>
That's what a LEFT JOIN does - it returns all rows from the LEFT table
that match the criteria in the WHERE clause, and then returns any rows
from the RIGHT table that happen do match. If you only want rows that
exist in both tables, change the join from LEFT (OUTER) JOIN to INNER
JOIN.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql case statement
no
On Sun, Jun 27, 2010 at 8:29 PM, David McGlone wrote:
> On Sunday 27 June 2010 04:08:24 Tanel Tammik wrote:
> > Hello,
> >
> > how to select only if value is present?
> >
> > $query = $db->query("select menus.id, menus.name,
> > case
> > when panels.id is not null then '1'
> > end as hiddenpanel
> >
> > from " . \DB_MENUS . " as menus
> > left join " . \DB_HIDDENPANELS . " as panels on (menus.id =
> > panels.menu_id)
> > where menus.id='" . (int)$id . "'
> > ");
> >
> > i would like to select hiddenpanel only if there is a corresponding value
> > in DB_HIDDENPANELS. At the moment i get NULL if there is no
> corresponding
> > value in HIDDENPANELS table!
>
> I would use an if statement since you only need to determine true or false.
> Something like:
>
> $query = $db->query("select menus.id, menus.name,
> from " . \DB_MENUS . " as menus
> left join " . \DB_HIDDENPANELS . " as panels on (menus.id =
> panels.menu_id)
> where menus.id='" . (int)$id . "'
> ");
>
> if (empty(DB_HIDDENPANELS)) {
>echo "";
>
> }
>
> else {
>echo "hiddenpanel";
>
> }
>
> But I would wait for others to chime in on this one, because I'm very far
> from
> an expert, there's also got to be a much better efficient way to write the
> if
> statement above, but it's what I would do in a case like this until I found
> a
> better way.
>
> --
> Blessings,
> David M.
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
A Brandon_R Production
Re: [PHP] mysql case statement
On Sunday 27 June 2010 04:08:24 Tanel Tammik wrote:
> Hello,
>
> how to select only if value is present?
>
> $query = $db->query("select menus.id, menus.name,
> case
> when panels.id is not null then '1'
> end as hiddenpanel
>
> from " . \DB_MENUS . " as menus
> left join " . \DB_HIDDENPANELS . " as panels on (menus.id =
> panels.menu_id)
> where menus.id='" . (int)$id . "'
> ");
>
> i would like to select hiddenpanel only if there is a corresponding value
> in DB_HIDDENPANELS. At the moment i get NULL if there is no corresponding
> value in HIDDENPANELS table!
I would use an if statement since you only need to determine true or false.
Something like:
$query = $db->query("select menus.id, menus.name,
from " . \DB_MENUS . " as menus
left join " . \DB_HIDDENPANELS . " as panels on (menus.id =
panels.menu_id)
where menus.id='" . (int)$id . "'
");
if (empty(DB_HIDDENPANELS)) {
echo "";
}
else {
echo "hiddenpanel";
}
But I would wait for others to chime in on this one, because I'm very far from
an expert, there's also got to be a much better efficient way to write the if
statement above, but it's what I would do in a case like this until I found a
better way.
--
Blessings,
David M.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
