date:20071003


Aleksandar Vojnovic wrote:
It seems you missed my point :) if you would need all the data then 
select them all, but if you need only partial data from the table then 
you could limit yourself to that specific columns. I doubt everybody 
need everything all the time. True?


Ahh - you meant the select * from table bit ;) My apologies.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?

2007-10-03 Thread Aleksandar Vojnovic

It seems you missed my point :) if you would need all the data then 
select them all, but if you need only partial data from the table then 
you could limit yourself to that specific columns. I doubt everybody 
need everything all the time. True?


Aleksandar

Chris wrote:

Aleksandar Vojnovic wrote:
I would also suggest to limit yourself to things you actually need 
not to select the whole table.


In this case you can't because you're looking for records that exist 
in one table that don't exist in another.


Apart from looking at the whole table in each case how else would you 
do that?




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?


Aleksandar Vojnovic wrote:
I would also suggest to limit yourself to things you actually need not 
to select the whole table.


In this case you can't because you're looking for records that exist in 
one table that don't exist in another.


Apart from looking at the whole table in each case how else would you do 
that?


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Re: the opposite of a join?

2007-10-03 Thread Aleksandar Vojnovic

I would also suggest to limit yourself to things you actually need not 
to select the whole table.


Aleksandar

Jim Lucas wrote:

Colin Guthrie wrote:

Martin Marques wrote:

SELECT * FROM company WHERE id NOT IN (SELECT companyID FROM contacts);


Not ideal as has been mentioned else where in this thread.

Col


I think one would have to take into account the DB type being used here.

I can have MySQL and PostgreSQL setup and running with the same table 
structure (well, as close as you can get) configured with two 
different databases in them.


SQL #1SELECT*
FROMcompany
WHEREid
NOT IN(
SELECTcompanyID
FROMcontacts
);

SQL #2 SELECTcompany.*
FROMcompany
LEFT JOIN contacts
ON(
company.companyID = contacts.companyID
)
WHEREcontacts.companyID IS NULL

Now, both SQL statements will perform relatively the same on either 
DB's with a small data set.


but, if you have a large data set, MySQL will benefit from having the 
Sub-Query style statement


Where-as PostgreSQL will shine with the JOIN command.

This is only from my own personal testing.  Mind you that I have only 
been using PostgreSQL for a year or so.  But one problem that I have 
always ran into with MySQL is that when JOIN'ing tables that have 
large data sets is a PITA.


So, if I was running MySQL, I would use SQL #1, but if I were using 
PostgreSQL, I would use SQL #2


If anybody else has suggestions or comments about performance between 
MySQL vs. PostgreSQL with regards to similarly formed SQL calls, I 
would like to hear their experiences.




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PHP4 and Constructor

2007-10-03 Thread T K

Thanks guys!

I got the same name trick for PHP 4 class constructor.

All the best,

T

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Re: the opposite of a join?

2007-10-03 Thread Jim Lucas

Robert Cummings wrote:

On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote:
This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a
year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables
that have large data sets is a PITA.

Were you doing left joins when you experienced those problems? Left
joins are usually very fast.

So, if I was running MySQL, I would use SQL #1, but if I were using PostgreSQL,
I would use SQL #2

I'd use the left join whenever available.

Cheers,
Rob.

Honestly, I cannot remember. It was right when I first started with
PHP/mysql back in 1999. I think we were using a JOIN (without the LEFT)

Which I think the default is an INNER JOIN if I do recall.

I really have never played with performance over the past few years.

This past year I have been working on a new DB with Call Detail Records
for a phone company. On average we have to deal with processing 2 - 4
million records each billing cycle. So, having to work with that amount
of CDR's and a couple thousand client records that are associated with
them, makes for a good performance test on SQL statements.

--
Jim Lucas

"Perseverance is not a long race;
it is many short races one after the other"

Walter Elliot

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PHP4 and Constructor

2007-10-03 Thread Joseph Crawford

this is why for backwards compatibility with PHP 4 people usually do
this.  Note that if you use public/private/protected it probably will
not work on PHP 4

Foo($params);
 }

 function Foo($params)
 {
  // Actual Constructor
 }
}
?>

On 10/4/07, Jean Molliné <[EMAIL PROTECTED]> wrote:
> Hi,
> In PHP4, the constructor has the same name as the class.
>
> class ClassName {
> function ClassName() {
> 
> }
> }
>
>
> T K a écrit :
> > Hi,
> >
> > I would like to use constructor in PHP4, but how do you make a constructor?
> > In PHP5, I do like
> >
> > class ClassName {
> >function __construct() {
> > 
> > }
> > }
> >
> > But PHP4 doesn't have such a thing as __construct()... Reading the
> > official manual didn't make me understood. Does this mean the very
> > first `function` in class is always the constructor for the Class?
> >
> > Tek
> >
> >
>


-- 
Joseph Crawford Jr.
Zend Certified Engineer
http://www.josephcrawford.com/
1-315-820-4244
[EMAIL PROTECTED]

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PHP4 and Constructor

2007-10-03 Thread Jean Molliné


Hi,
In PHP4, the constructor has the same name as the class.

class ClassName {
   function ClassName() {
   
   }
}


T K a écrit :

Hi,

I would like to use constructor in PHP4, but how do you make a constructor?
In PHP5, I do like

class ClassName {
   function __construct() {

}
}

But PHP4 doesn't have such a thing as __construct()... Reading the
official manual didn't make me understood. Does this mean the very
first `function` in class is always the constructor for the Class?

Tek

Re: [PHP-DB] PHP4 and Constructor


T K wrote:

Hi,

I would like to use constructor in PHP4, but how do you make a constructor?


Name the function the same as the class.

class ABC {
  function ABC() {
//constructor in php4
$this->__construct(); // just call the php5 one.
  }
  function __construct() {
//constructor in php5.
  }
}


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PHP4 and Constructor

2007-10-03 Thread T K

Hi,

I would like to use constructor in PHP4, but how do you make a constructor?
In PHP5, I do like

class ClassName {
   function __construct() {

}
}

But PHP4 doesn't have such a thing as __construct()... Reading the
official manual didn't make me understood. Does this mean the very
first `function` in class is always the constructor for the Class?

Tek

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Re: the opposite of a join?


Robert Cummings wrote:

On Thu, 2007-10-04 at 11:23 +1000, Chris wrote:

Robert Cummings wrote:

On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote:
This is only from my own personal testing.  Mind you that I have only been using PostgreSQL for a 
year or so.  But one problem that I have always ran into with MySQL is that when JOIN'ing tables 
that have large data sets is a PITA.

Were you doing left joins when you experienced those problems? Left
joins are usually very fast.

If indexed properly of course ;)


Yes, but you're not going to get a performance improvement if you use
anything else if the table isn't properly indexed.


A subselect could win out in terms of performance especially if the 
table in the subselect is reasonably small (eg all fits into memory).


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Re: the opposite of a join?

2007-10-03 Thread Robert Cummings

On Thu, 2007-10-04 at 11:23 +1000, Chris wrote:
> Robert Cummings wrote:
> > On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote:
> >> This is only from my own personal testing.  Mind you that I have only been 
> >> using PostgreSQL for a 
> >> year or so.  But one problem that I have always ran into with MySQL is 
> >> that when JOIN'ing tables 
> >> that have large data sets is a PITA.
> > 
> > Were you doing left joins when you experienced those problems? Left
> > joins are usually very fast.
> 
> If indexed properly of course ;)

Yes, but you're not going to get a performance improvement if you use
anything else if the table isn't properly indexed.

Cheers,
Rob.
-- 
...
SwarmBuy.com - http://www.swarmbuy.com

Leveraging the buying power of the masses!
...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Re: the opposite of a join?


Robert Cummings wrote:

On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote:
This is only from my own personal testing.  Mind you that I have only been using PostgreSQL for a 
year or so.  But one problem that I have always ran into with MySQL is that when JOIN'ing tables 
that have large data sets is a PITA.


Were you doing left joins when you experienced those problems? Left
joins are usually very fast.


If indexed properly of course ;)

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Re: the opposite of a join?

2007-10-03 Thread Robert Cummings

On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote:
>
> This is only from my own personal testing.  Mind you that I have only been 
> using PostgreSQL for a 
> year or so.  But one problem that I have always ran into with MySQL is that 
> when JOIN'ing tables 
> that have large data sets is a PITA.

Were you doing left joins when you experienced those problems? Left
joins are usually very fast.

> So, if I was running MySQL, I would use SQL #1, but if I were using 
> PostgreSQL, I would use SQL #2

I'd use the left join whenever available.

Cheers,
Rob.
-- 
...
SwarmBuy.com - http://www.swarmbuy.com

Leveraging the buying power of the masses!
...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??


Markus Wolff wrote:

Chris schrieb:

It can't be file permissions, I've even tried to set the database file
to 777... no change at all.


My guess is still permissions. If you try a raw fopen instead of using 
pdo, what happens?


\n";
} else {
  echo "Unable to open db\n";
}
fclose($fp);
?>


Hey Chris,

mmh wonder what could possibly be wrong when even trying 777? :-)


Some hosts check for and disable access to files that have wide-open 
permissions - usually when php is running as a cgi but I've seen it when 
using mod_php too.


If you try something other than 777 what happens? (644 just to see if 
you can open it, then work on writing to it).


Can you see the file from this script?

$file = dirname(__FILE__) . '/frontend.db';
echo is_file($file) . '';

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Re: the opposite of a join?

2007-10-03 Thread Jim Lucas


Colin Guthrie wrote:

Martin Marques wrote:

SELECT * FROM company WHERE id NOT IN (SELECT companyID FROM contacts);


Not ideal as has been mentioned else where in this thread.

Col


I think one would have to take into account the DB type being used here.

I can have MySQL and PostgreSQL setup and running with the same table structure (well, as close as 
you can get) configured with two different databases in them.


SQL #1  SELECT  *
FROMcompany
WHERE   id
NOT IN  (
SELECT  companyID
FROMcontacts
);

SQL #2  SELECT  company.*
FROMcompany
LEFT JOIN contacts
ON  (
company.companyID = contacts.companyID
)
WHERE   contacts.companyID IS NULL

Now, both SQL statements will perform relatively the same on either DB's with a 
small data set.

but, if you have a large data set, MySQL will benefit from having the Sub-Query 
style statement

Where-as PostgreSQL will shine with the JOIN command.

This is only from my own personal testing.  Mind you that I have only been using PostgreSQL for a 
year or so.  But one problem that I have always ran into with MySQL is that when JOIN'ing tables 
that have large data sets is a PITA.


So, if I was running MySQL, I would use SQL #1, but if I were using PostgreSQL, 
I would use SQL #2

If anybody else has suggestions or comments about performance between MySQL vs. PostgreSQL with 
regards to similarly formed SQL calls, I would like to hear their experiences.


--
Jim Lucas

   "Some men are born to greatness, some achieve greatness,
   and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: the opposite of a join?

2007-10-03 Thread Colin Guthrie

Martin Marques wrote:
> SELECT * FROM company WHERE id NOT IN (SELECT companyID FROM contacts);

Not ideal as has been mentioned else where in this thread.

Col

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Build a form that returns to the parent window

This is not really a database question, but here's something to point 
you in the right direction...


In your main page insert this Javascript function:

function sayHello(name) {
   alert("Hello "+name);
}

In the newly-opened window (it's important that this window has been 
opened by the main page, preferrably using window.open()), do this:




This should do the trick for you. The "opener" property of the window 
object holds a reference to the main window.


CU
 Markus

Thodoris schrieb:

Hi guys,
   I have been working on a project for some time now and I'm trying to 
make a form that includes (many things and) an edit box with a button by 
it. I want this button to open a pop-up window and include a search form 
that queries a database and return to its self the result set in a table 
in which the last cell contains a link (or button). This link should 
return to the parent window a value of this specific row.
   Well I have build the whole interface and since php is server side I 
thought I could write this including javascript. Non of my experiments 
worked because the pop-up window does not returm to the parent window 
something. I searched through the web and nothing seems to do the magic 
for me. Please any suggestions would be welcome cause I have been 
working on this for several hours as we speak.




--
Markus Wolff
-Development-

NorthClick GmbH

Gasstr. 10 - 22761 Hamburg
Tel.: 040 8 22 44 999 - Fax: 040 8 22 44 998
Internet: http://www.northclick.de/

Geschäftsführer: F. Detzner | M. Henze | C. Springub Amtsgericht Hamburg,
HRB 94459

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] the opposite of a join?

2007-10-03 Thread Martin Marques


[EMAIL PROTECTED] wrote:

I have a company table and a contacts table.  In the contacts table, there
is a field called "companyID" which is a link to a row in the company table.

 


What is the easiest way to query the company table for all the company rows
whose ID is NOT linked to in the contact table? Basically, the opposite of a
join?


SELECT * FROM company WHERE id NOT IN (SELECT companyID FROM contacts);

--
 21:50:04 up 2 days,  9:07,  0 users,  load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués |   SELECT 'mmarques' ||
Centro de Telemática|   '@' || 'unl.edu.ar';
Universidad Nacional|   DBA, Programador,
del Litoral |   Administrador
-

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??


Hi again Markus,
   This is realy confusing but since the command line execution works 
for you then the problem should be php configuration for the mod_php. I 
came up with this try to edit your php.ini and in case you use 
pdo_sqlite extension then change the order of the loaded modules to this:


|  extension=pdo.so
 extension=pdo_sqlite.so
 extension=sqlite.so

You could also define the full path of the extensions like this:
||
extension_dir="/usr/local/include/php/ext/pdo/"

instead of this:

||extension_dir = "./"

In case your apache is compiled by hand and you have updated sqlite you 
should recompile your apache against the new sqlite.


Hope this do some magic for you cause I have run out of ideas.

|

--
Thodoris



O/H Markus Wolff έγραψε:

Hi Thodoris,

I've checked on one of the three boxes now and the SQLite version used 
by both the commandline client and PDO is 3.2.8. I know the other two 
boxes have different versions, but I always created the database anew 
on each box.


I also tried chown/chmod on the parent directory, no change.

CU
 Markus

Thodoris schrieb:

Hey Markus,
You should try to "chown apache:apache" and "chmod +w" to the 
directory that includes frontend.db . And the link that I posted says:


|/*
 ** file_format==1Version 3.0.0.
 ** file_format==2Version 3.1.3.
 ** file_format==3Version 3.1.4.
 **
 ** Version 3.0 can only use files with file_format==1. Version 3.1.3
 ** can read and write files with file_format==1 or file_format==2.
 ** Version 3.1.4 can read and write file formats 1, 2 and 3.
 */

Meaning that not all sqlite3 versions support all file formats. That 
is why you should check the version of sqlite.

|

--
Thodoris




O/H Markus Wolff έγραψε:

Hey there,

I've double-checked on three different machines now, and I'm always 
getting the same error. All having different versions of PHP, 
Apache, PDO and SQLite. So I figure it must be something that I'm 
doing wrong. I just can't figure out what it is - and I'm puzzled 
because I had used SQLite before (although briefly) and don't think 
I'm doing anything different than before.


Anyway, here's what I'm doing, step-by-step:

# sqlite3 frontend.db

Here I insert the following SQL script:

CREATE TABLE website (
website_id INTEGER PRIMARY KEY,
always_expand INTEGER
);

CREATE TABLE page (
page_id INTEGER NOT NULL PRIMARY KEY,
parent_id INTEGER,
website_id INTEGER NOT NULL,
title TEXT,
link TEXT,
depth INTEGER,
visible INTEGER,
element_id INTEGER,
nav_path TEXT,
protected INTEGER,
sort_order INTEGER
);

Then I exit the client and make the PHP script:

# nano test.php

The content of the script still being that of my original message.
Then I adjust the rights:

# chown apache:apache frontend.db
# chmod 777 frontend.db

Then I execute the script on the command line:

# php test.php

No error.

Then I call the script on the website, one of the examples being:
http://www.21st.de/test.php

The script still manages to open the database and do a SELECT query, 
but throws the said exception when trying to do the DELETE statement.


These are all the steps that are involved to reproduce the error on 
three machines. No more, no less. Now, have I overlooked anything? 
Am I missing something really, really stupid? Or is it some kind of 
a bug? But certainly that could not have gone unnoticed for so long? 
(Tested on PHP versions 5.1.4, 5.2.0 and 5.2.4).


CU
 Markus

Markus Wolff - NorthClick schrieb:

Hey there,

I'm trying to open an SQLite3 database from a PHP very simple PHP
script:

$db = dirname(__FILE__).'/frontend.db';
$pdo = new PDO('sqlite:'.$db);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query("SELECT * FROM page LIMIT 1");
echo "Deleting pages\n";   $pdo->query("DELETE FROM page");
echo "Deleting websites\n";
$pdo->query("DELETE FROM website");

The database file contains no data whatsoever, just the table
definitions (in case you were wondering, this is a stripped-down 
version

of a larger script for debugging purposes, hence the seemingly idiotic
DELETE statements that won't do any good in an empty database anyway,
but I digress...).

When executed on the command line, this works perfectly. When I 
execute

the same script via Apache and mod_php, I'm getting this exception:

PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
missing database in /home/mwolff/webs/markus/cms/test.php on line 8

Getting experimental, I've tried to change the calls for the DELETE
statements from $pdo->query() to $pdo->exec(), just to see what 
happens.

Well, what happens is that I'm getting a different error:

PDOException: SQLSTATE[HY000]: General error: 14 unable to open 
database

file in /home/mwolff/webs/markus/cms/test.php on line 6

Argh... what can possibly be wrong here? The script works from the
commandline, with the exact same PHP version (Debian package, PHP
5.2.0-8+etch7, and we also tried

[PHP-DB] Build a form that returns to the parent window


Hi guys,
   I have been working on a project for some time now and I'm trying to 
make a form that includes (many things and) an edit box with a button by 
it. I want this button to open a pop-up window and include a search form 
that queries a database and return to its self the result set in a table 
in which the last cell contains a link (or button). This link should 
return to the parent window a value of this specific row.
   Well I have build the whole interface and since php is server side I 
thought I could write this including javascript. Non of my experiments 
worked because the pop-up window does not returm to the parent window 
something. I searched through the web and nothing seems to do the magic 
for me. Please any suggestions would be welcome cause I have been 
working on this for several hours as we speak.


--
Thodoris

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??


Hi Thodoris,

I've checked on one of the three boxes now and the SQLite version used 
by both the commandline client and PDO is 3.2.8. I know the other two 
boxes have different versions, but I always created the database anew on 
each box.


I also tried chown/chmod on the parent directory, no change.

CU
 Markus

Thodoris schrieb:

Hey Markus,
You should try to "chown apache:apache" and "chmod +w" to the directory 
that includes frontend.db . And the link that I posted says:


|/*
 ** file_format==1Version 3.0.0.
 ** file_format==2Version 3.1.3.
 ** file_format==3Version 3.1.4.
 **
 ** Version 3.0 can only use files with file_format==1. Version 3.1.3
 ** can read and write files with file_format==1 or file_format==2.
 ** Version 3.1.4 can read and write file formats 1, 2 and 3.
 */

Meaning that not all sqlite3 versions support all file formats. That is 
why you should check the version of sqlite.

|

--
Thodoris




O/H Markus Wolff έγραψε:

Hey there,

I've double-checked on three different machines now, and I'm always 
getting the same error. All having different versions of PHP, Apache, 
PDO and SQLite. So I figure it must be something that I'm doing wrong. 
I just can't figure out what it is - and I'm puzzled because I had 
used SQLite before (although briefly) and don't think I'm doing 
anything different than before.


Anyway, here's what I'm doing, step-by-step:

# sqlite3 frontend.db

Here I insert the following SQL script:

CREATE TABLE website (
website_id INTEGER PRIMARY KEY,
always_expand INTEGER
);

CREATE TABLE page (
page_id INTEGER NOT NULL PRIMARY KEY,
parent_id INTEGER,
website_id INTEGER NOT NULL,
title TEXT,
link TEXT,
depth INTEGER,
visible INTEGER,
element_id INTEGER,
nav_path TEXT,
protected INTEGER,
sort_order INTEGER
);

Then I exit the client and make the PHP script:

# nano test.php

The content of the script still being that of my original message.
Then I adjust the rights:

# chown apache:apache frontend.db
# chmod 777 frontend.db

Then I execute the script on the command line:

# php test.php

No error.

Then I call the script on the website, one of the examples being:
http://www.21st.de/test.php

The script still manages to open the database and do a SELECT query, 
but throws the said exception when trying to do the DELETE statement.


These are all the steps that are involved to reproduce the error on 
three machines. No more, no less. Now, have I overlooked anything? Am 
I missing something really, really stupid? Or is it some kind of a 
bug? But certainly that could not have gone unnoticed for so long? 
(Tested on PHP versions 5.1.4, 5.2.0 and 5.2.4).


CU
 Markus

Markus Wolff - NorthClick schrieb:

Hey there,

I'm trying to open an SQLite3 database from a PHP very simple PHP
script:

$db = dirname(__FILE__).'/frontend.db';
$pdo = new PDO('sqlite:'.$db);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query("SELECT * FROM page LIMIT 1");
echo "Deleting pages\n";   $pdo->query("DELETE FROM page");
echo "Deleting websites\n";
$pdo->query("DELETE FROM website");

The database file contains no data whatsoever, just the table
definitions (in case you were wondering, this is a stripped-down version
of a larger script for debugging purposes, hence the seemingly idiotic
DELETE statements that won't do any good in an empty database anyway,
but I digress...).

When executed on the command line, this works perfectly. When I execute
the same script via Apache and mod_php, I'm getting this exception:

PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
missing database in /home/mwolff/webs/markus/cms/test.php on line 8

Getting experimental, I've tried to change the calls for the DELETE
statements from $pdo->query() to $pdo->exec(), just to see what happens.
Well, what happens is that I'm getting a different error:

PDOException: SQLSTATE[HY000]: General error: 14 unable to open database
file in /home/mwolff/webs/markus/cms/test.php on line 6

Argh... what can possibly be wrong here? The script works from the
commandline, with the exact same PHP version (Debian package, PHP
5.2.0-8+etch7, and we also tried upgrading to the latest Debian package
of 5.2.4, to no avail).

It can't be file permissions, I've even tried to set the database file
to 777... no change at all.

Does this ring a bell with anyone here?

Thanks,
 Markus





--
Markus Wolff
-Development-

NorthClick GmbH

Gasstr. 10 - 22761 Hamburg
Tel.: 040 8 22 44 999 - Fax: 040 8 22 44 998
Internet: http://www.northclick.de/

Geschäftsführer: F. Detzner | M. Henze | C. Springub Amtsgericht Hamburg,
HRB 94459

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??


Hey Markus,
You should try to "chown apache:apache" and "chmod +w" to the directory 
that includes frontend.db . And the link that I posted says:


|/*
** file_format==1Version 3.0.0.
** file_format==2Version 3.1.3.
** file_format==3Version 3.1.4.
**
** Version 3.0 can only use files with file_format==1. Version 3.1.3
** can read and write files with file_format==1 or file_format==2.
** Version 3.1.4 can read and write file formats 1, 2 and 3.
*/

Meaning that not all sqlite3 versions support all file formats. That is 
why you should check the version of sqlite.

|

--
Thodoris




O/H Markus Wolff έγραψε:

Hey there,

I've double-checked on three different machines now, and I'm always 
getting the same error. All having different versions of PHP, Apache, 
PDO and SQLite. So I figure it must be something that I'm doing wrong. 
I just can't figure out what it is - and I'm puzzled because I had 
used SQLite before (although briefly) and don't think I'm doing 
anything different than before.


Anyway, here's what I'm doing, step-by-step:

# sqlite3 frontend.db

Here I insert the following SQL script:

CREATE TABLE website (
website_id INTEGER PRIMARY KEY,
always_expand INTEGER
);

CREATE TABLE page (
page_id INTEGER NOT NULL PRIMARY KEY,
parent_id INTEGER,
website_id INTEGER NOT NULL,
title TEXT,
link TEXT,
depth INTEGER,
visible INTEGER,
element_id INTEGER,
nav_path TEXT,
protected INTEGER,
sort_order INTEGER
);

Then I exit the client and make the PHP script:

# nano test.php

The content of the script still being that of my original message.
Then I adjust the rights:

# chown apache:apache frontend.db
# chmod 777 frontend.db

Then I execute the script on the command line:

# php test.php

No error.

Then I call the script on the website, one of the examples being:
http://www.21st.de/test.php

The script still manages to open the database and do a SELECT query, 
but throws the said exception when trying to do the DELETE statement.


These are all the steps that are involved to reproduce the error on 
three machines. No more, no less. Now, have I overlooked anything? Am 
I missing something really, really stupid? Or is it some kind of a 
bug? But certainly that could not have gone unnoticed for so long? 
(Tested on PHP versions 5.1.4, 5.2.0 and 5.2.4).


CU
 Markus

Markus Wolff - NorthClick schrieb:

Hey there,

I'm trying to open an SQLite3 database from a PHP very simple PHP
script:

$db = dirname(__FILE__).'/frontend.db';
$pdo = new PDO('sqlite:'.$db);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query("SELECT * FROM page LIMIT 1");
echo "Deleting pages\n";   $pdo->query("DELETE FROM page");
echo "Deleting websites\n";
$pdo->query("DELETE FROM website");

The database file contains no data whatsoever, just the table
definitions (in case you were wondering, this is a stripped-down version
of a larger script for debugging purposes, hence the seemingly idiotic
DELETE statements that won't do any good in an empty database anyway,
but I digress...).

When executed on the command line, this works perfectly. When I execute
the same script via Apache and mod_php, I'm getting this exception:

PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
missing database in /home/mwolff/webs/markus/cms/test.php on line 8

Getting experimental, I've tried to change the calls for the DELETE
statements from $pdo->query() to $pdo->exec(), just to see what happens.
Well, what happens is that I'm getting a different error:

PDOException: SQLSTATE[HY000]: General error: 14 unable to open database
file in /home/mwolff/webs/markus/cms/test.php on line 6

Argh... what can possibly be wrong here? The script works from the
commandline, with the exact same PHP version (Debian package, PHP
5.2.0-8+etch7, and we also tried upgrading to the latest Debian package
of 5.2.4, to no avail).

It can't be file permissions, I've even tried to set the database file
to 777... no change at all.

Does this ring a bell with anyone here?

Thanks,
 Markus

Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??


Hey there,

I've double-checked on three different machines now, and I'm always 
getting the same error. All having different versions of PHP, Apache, 
PDO and SQLite. So I figure it must be something that I'm doing wrong. I 
just can't figure out what it is - and I'm puzzled because I had used 
SQLite before (although briefly) and don't think I'm doing anything 
different than before.


Anyway, here's what I'm doing, step-by-step:

# sqlite3 frontend.db

Here I insert the following SQL script:

CREATE TABLE website (
website_id INTEGER PRIMARY KEY,
always_expand INTEGER
);

CREATE TABLE page (
page_id INTEGER NOT NULL PRIMARY KEY,
parent_id INTEGER,
website_id INTEGER NOT NULL,
title TEXT,
link TEXT,
depth INTEGER,
visible INTEGER,
element_id INTEGER,
nav_path TEXT,
protected INTEGER,
sort_order INTEGER
);

Then I exit the client and make the PHP script:

# nano test.php

The content of the script still being that of my original message.
Then I adjust the rights:

# chown apache:apache frontend.db
# chmod 777 frontend.db

Then I execute the script on the command line:

# php test.php

No error.

Then I call the script on the website, one of the examples being:
http://www.21st.de/test.php

The script still manages to open the database and do a SELECT query, but 
throws the said exception when trying to do the DELETE statement.


These are all the steps that are involved to reproduce the error on 
three machines. No more, no less. Now, have I overlooked anything? Am I 
missing something really, really stupid? Or is it some kind of a bug? 
But certainly that could not have gone unnoticed for so long? (Tested on 
PHP versions 5.1.4, 5.2.0 and 5.2.4).


CU
 Markus

Markus Wolff - NorthClick schrieb:

Hey there,

I'm trying to open an SQLite3 database from a PHP very simple PHP
script:

$db = dirname(__FILE__).'/frontend.db';
$pdo = new PDO('sqlite:'.$db);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query("SELECT * FROM page LIMIT 1");
echo "Deleting pages\n";   
$pdo->query("DELETE FROM page");

echo "Deleting websites\n";
$pdo->query("DELETE FROM website");

The database file contains no data whatsoever, just the table
definitions (in case you were wondering, this is a stripped-down version
of a larger script for debugging purposes, hence the seemingly idiotic
DELETE statements that won't do any good in an empty database anyway,
but I digress...).

When executed on the command line, this works perfectly. When I execute
the same script via Apache and mod_php, I'm getting this exception:

PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
missing database in /home/mwolff/webs/markus/cms/test.php on line 8

Getting experimental, I've tried to change the calls for the DELETE
statements from $pdo->query() to $pdo->exec(), just to see what happens.
Well, what happens is that I'm getting a different error:

PDOException: SQLSTATE[HY000]: General error: 14 unable to open database
file in /home/mwolff/webs/markus/cms/test.php on line 6

Argh... what can possibly be wrong here? The script works from the
commandline, with the exact same PHP version (Debian package, PHP
5.2.0-8+etch7, and we also tried upgrading to the latest Debian package
of 5.2.4, to no avail).

It can't be file permissions, I've even tried to set the database file
to 777... no change at all.

Does this ring a bell with anyone here?

Thanks,
 Markus



--
Markus Wolff
-Development-

NorthClick GmbH

Gasstr. 10 - 22761 Hamburg
Tel.: 040 8 22 44 999 - Fax: 040 8 22 44 998
Internet: http://www.northclick.de/

Geschäftsführer: F. Detzner | M. Henze | C. Springub Amtsgericht Hamburg,
HRB 94459

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??


I run into this:
http://www.php.net/manual/en/ref.pdo-sqlite.php

while searching for a solution to this exception:
PDOException: SQLSTATE[HY000]: General error: 1

Take a look at it bacause this might be happenning due to version 
incompatibilities. For example only sqlite version 3.1.4 and after can 
read file formats 1,2 and 3. Is you database format new?|

|

--
Thodoris


O/H Markus Wolff έγραψε:

Chris schrieb:

It can't be file permissions, I've even tried to set the database file
to 777... no change at all.


My guess is still permissions. If you try a raw fopen instead of 
using pdo, what happens?


\n";
} else {
  echo "Unable to open db\n";
}
fclose($fp);
?>


Hey Chris,

mmh wonder what could possibly be wrong when even trying 777? :-)

Anyway, just to make sure (and because I'm desperate enough to grasp for
straws right now), I tried exacly what you proposed and it still yields
the same results.

Thanks for your suggestion anyway, sometimes one forgets to try the most
obvious things first :-)

CU
 Markus

RE: [PHP-DB] Re: [PHP] RE: the opposite of a join?

2007-10-03 Thread Ryan Jameson \(USA\)

You can also do an outer join and look for NULLS in the key (which means
"no match"). In some cases it may be more efficient. <>< Ryan

-Original Message-
From: James Ausmus [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 03, 2007 10:00 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; php-db@lists.php.net
Subject: [PHP-DB] Re: [PHP] RE: the opposite of a join?

On 10/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Hi J,
> >
> >
> > Checkout this,
> >
> >
> > SELECT * FROM tbl_company where id not in (SELECT companyID from
> > tbl_contacts)
> >
>
>
> Brilliant! This is exactly what I was looking for, and is quite 
> logical/readable!  Thanks to everyone for the ideas!
>
> J
>

No, don't do this! It is a very inefficient way to retrieve the
information you are looking for (Use a query analysis tool to check it
out yourself, if you want) - if your tables get to any larger size at
all, it will start having a noticeable performance impact on your script
(not to mention your DB) - let the DB do the hard work and use a LEFT
JOIN syntax, the database can optimize that much more efficiently. Only
if your DB doesn't support the LEFT JOIN syntax would you want to do the
above.

-James



>
>
>
>
>
> >
> >
> >
> >
> > Regards,
> > Lasitha Alawatta
> > Application Developer
> > Destinations of the World Holding Establishment P O Box: 19950 
> > Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 
> > (Ext.)
> > 7 Fax +971 4 295 8910
> > + [EMAIL PROTECTED]
> >
> > -Original Message-
> > From: John Pillion [mailto:[EMAIL PROTECTED] 
> > 
> On Behalf Of
> > [EMAIL PROTECTED]
> > Sent: Wednesday, October 03, 2007 2:21 PM
> > To: [EMAIL PROTECTED]; php-db@lists.php.net
> > Subject: [PHP-DB] the opposite of a join?
> >
> > I have a company table and a contacts table.  In the contacts table,

> > there is a field called "companyID" which is a link to a row in the 
> > company table.
> >
> >
> >
> > What is the easiest way to query the company table for all the 
> > company rows whose ID is NOT linked to in the contact table? 
> > Basically, the opposite of a join?
> >
> >
> >
> > Thanks
> >
> >
> >
> > J
> >
> >
> >
> > DOTW DISCLAIMER:
> >
> > This e-mail and any attachments are strictly confidential and 
> > intended for the addressee only. If you are not the named addressee 
> > you must not >
> disclose, copy or take
> > any action in reliance of this transmission and you should notify us

> > as soon as possible. If you have received it in error, please 
> > contact the message sender immediately.
> > This e-mail and any attachments are believed to be free from viruses

> > but it is your responsibility to carry out all necessary virus 
> > checks and DOTW accepts no liability in connection therewith.
> >
> > This e-mail and all other electronic (including voice) 
> > communications from the sender's company are for informational 
> > purposes only.  No such communication is intended by the sender to 
> > constitute either an electronic record or an electronic signature or

> > to constitute any agreement by the sender to conduct a transaction 
> > by electronic means.
> >
>
> >
>
>

--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] RE: the opposite of a join?

2007-10-03 Thread Gary Josack

I agree with this. Never use a subquery when a join will work. The 
optimizer with thank you with performance.


James Ausmus wrote:

On 10/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
  

Hi J,


Checkout this,


SELECT * FROM tbl_company where id not in (SELECT companyID from
tbl_contacts)

  

Brilliant! This is exactly what I was looking for, and is quite
logical/readable!  Thanks to everyone for the ideas!

J




No, don't do this! It is a very inefficient way to retrieve the
information you are looking for (Use a query analysis tool to check it
out yourself, if you want) - if your tables get to any larger size at
all, it will start having a noticeable performance impact on your
script (not to mention your DB) - let the DB do the hard work and use
a LEFT JOIN syntax, the database can optimize that much more
efficiently. Only if your DB doesn't support the LEFT JOIN syntax
would you want to do the above.

-James



  








Regards,
Lasitha Alawatta
Application Developer
Destinations of the World Holding Establishment
P O Box: 19950
Dubai, United Arab Emirates
( Ph +971 4 295 8510 (Board) / 1464 (Ext.)
7 Fax +971 4 295 8910
+ [EMAIL PROTECTED]

-Original Message-
From: John Pillion [mailto:[EMAIL PROTECTED] 
  

On Behalf Of


[EMAIL PROTECTED]
Sent: Wednesday, October 03, 2007 2:21 PM
To: [EMAIL PROTECTED]; php-db@lists.php.net
Subject: [PHP-DB] the opposite of a join?

I have a company table and a contacts table.  In the contacts table,
there
is a field called "companyID" which is a link to a row in the company
table.



What is the easiest way to query the company table for all the company
rows
whose ID is NOT linked to in the contact table? Basically, the opposite
of a
join?



Thanks



J



DOTW DISCLAIMER:

This e-mail and any attachments are strictly confidential and intended
for the addressee only. If you are not the named addressee you must not >
  

disclose, copy or take


any action in reliance of this transmission and you should notify us as
soon as possible. If you have received it in error, please contact the
message sender immediately.
This e-mail and any attachments are believed to be free from viruses but
it is your responsibility to carry out all necessary virus checks and
DOTW accepts no liability
in connection therewith.

This e-mail and all other electronic (including voice) communications
from the sender's company are for informational purposes only.  No such
communication is intended
by the sender to constitute either an electronic record or an electronic
signature or to constitute any agreement by the sender to conduct a
transaction by electronic means.

  



  


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] RE: the opposite of a join?

2007-10-03 Thread James Ausmus

On 10/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Hi J,
> >
> >
> > Checkout this,
> >
> >
> > SELECT * FROM tbl_company where id not in (SELECT companyID from
> > tbl_contacts)
> >
>
>
> Brilliant! This is exactly what I was looking for, and is quite
> logical/readable!  Thanks to everyone for the ideas!
>
> J
>

No, don't do this! It is a very inefficient way to retrieve the
information you are looking for (Use a query analysis tool to check it
out yourself, if you want) - if your tables get to any larger size at
all, it will start having a noticeable performance impact on your
script (not to mention your DB) - let the DB do the hard work and use
a LEFT JOIN syntax, the database can optimize that much more
efficiently. Only if your DB doesn't support the LEFT JOIN syntax
would you want to do the above.

-James



>
>
>
>
>
> >
> >
> >
> >
> > Regards,
> > Lasitha Alawatta
> > Application Developer
> > Destinations of the World Holding Establishment
> > P O Box: 19950
> > Dubai, United Arab Emirates
> > ( Ph +971 4 295 8510 (Board) / 1464 (Ext.)
> > 7 Fax +971 4 295 8910
> > + [EMAIL PROTECTED]
> >
> > -Original Message-
> > From: John Pillion [mailto:[EMAIL PROTECTED] 
> On Behalf Of
> > [EMAIL PROTECTED]
> > Sent: Wednesday, October 03, 2007 2:21 PM
> > To: [EMAIL PROTECTED]; php-db@lists.php.net
> > Subject: [PHP-DB] the opposite of a join?
> >
> > I have a company table and a contacts table.  In the contacts table,
> > there
> > is a field called "companyID" which is a link to a row in the company
> > table.
> >
> >
> >
> > What is the easiest way to query the company table for all the company
> > rows
> > whose ID is NOT linked to in the contact table? Basically, the opposite
> > of a
> > join?
> >
> >
> >
> > Thanks
> >
> >
> >
> > J
> >
> >
> >
> > DOTW DISCLAIMER:
> >
> > This e-mail and any attachments are strictly confidential and intended
> > for the addressee only. If you are not the named addressee you must not >
> disclose, copy or take
> > any action in reliance of this transmission and you should notify us as
> > soon as possible. If you have received it in error, please contact the
> > message sender immediately.
> > This e-mail and any attachments are believed to be free from viruses but
> > it is your responsibility to carry out all necessary virus checks and
> > DOTW accepts no liability
> > in connection therewith.
> >
> > This e-mail and all other electronic (including voice) communications
> > from the sender's company are for informational purposes only.  No such
> > communication is intended
> > by the sender to constitute either an electronic record or an electronic
> > signature or to constitute any agreement by the sender to conduct a
> > transaction by electronic means.
> >
>
> >
>
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] RE: the opposite of a join?

2007-10-03 Thread jd.pillion

> Hi J, 
> 
> 
> Checkout this,
> 
> 
> SELECT * FROM tbl_company where id not in (SELECT companyID from
> tbl_contacts)
> 
 
 
Brilliant! This is exactly what I was looking for, and is quite
logical/readable!  Thanks to everyone for the ideas!
 
J
 
 
 
 
 
 
> 
> 
> 
> 
> Regards,
> Lasitha Alawatta
> Application Developer
> Destinations of the World Holding Establishment
> P O Box: 19950
> Dubai, United Arab Emirates
> ( Ph +971 4 295 8510 (Board) / 1464 (Ext.)
> 7 Fax +971 4 295 8910
> + [EMAIL PROTECTED] 
> 
> -Original Message-
> From: John Pillion [mailto:[EMAIL PROTECTED] 
On Behalf Of
> [EMAIL PROTECTED]
> Sent: Wednesday, October 03, 2007 2:21 PM
> To: [EMAIL PROTECTED]; php-db@lists.php.net
> Subject: [PHP-DB] the opposite of a join?
> 
> I have a company table and a contacts table.  In the contacts table,
> there
> is a field called "companyID" which is a link to a row in the company
> table.
> 
>  
> 
> What is the easiest way to query the company table for all the company
> rows
> whose ID is NOT linked to in the contact table? Basically, the opposite
> of a
> join?
> 
>  
> 
> Thanks
> 
>  
> 
> J
> 
>  
> 
> DOTW DISCLAIMER:
> 
> This e-mail and any attachments are strictly confidential and intended
> for the addressee only. If you are not the named addressee you must not >
disclose, copy or take
> any action in reliance of this transmission and you should notify us as 
> soon as possible. If you have received it in error, please contact the 
> message sender immediately.
> This e-mail and any attachments are believed to be free from viruses but 
> it is your responsibility to carry out all necessary virus checks and 
> DOTW accepts no liability
> in connection therewith. 
> 
> This e-mail and all other electronic (including voice) communications 
> from the sender's company are for informational purposes only.  No such 
> communication is intended
> by the sender to constitute either an electronic record or an electronic 
> signature or to constitute any agreement by the sender to conduct a 
> transaction by electronic means.
> 

>

Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??


Chris schrieb:

It can't be file permissions, I've even tried to set the database file
to 777... no change at all.


My guess is still permissions. If you try a raw fopen instead of using 
pdo, what happens?


\n";
} else {
  echo "Unable to open db\n";
}
fclose($fp);
?>


Hey Chris,

mmh wonder what could possibly be wrong when even trying 777? :-)

Anyway, just to make sure (and because I'm desperate enough to grasp for
straws right now), I tried exacly what you proposed and it still yields
the same results.

Thanks for your suggestion anyway, sometimes one forgets to try the most
obvious things first :-)

CU
 Markus


--
Markus Wolff
-Development-

NorthClick GmbH

Gasstr. 10 - 22761 Hamburg
Tel.: 040 8 22 44 999 - Fax: 040 8 22 44 998
Internet: http://www.northclick.de/

Geschäftsführer: F. Detzner | M. Henze | C. Springub Amtsgericht Hamburg,
HRB 94459

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] the opposite of a join?

2007-10-03 Thread James Ausmus

On 10/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> I have a company table and a contacts table.  In the contacts table, there
> is a field called "companyID" which is a link to a row in the company
> table.
>
>
>
> What is the easiest way to query the company table for all the company
> rows
> whose ID is NOT linked to in the contact table? Basically, the opposite of
> a
> join?



SELECT company.*
FROM company LEFT JOIN contacts ON (company.companyID = contacts.companyID)
WHERE contacts.companyID IS NULL

(Assuming your DB can handle a left join)

-James



Thanks
>
>
>
> J
>
>
>
>

RE: [PHP-DB] the opposite of a join?

2007-10-03 Thread Lasitha Alawatta


Hi J, 


Checkout this,


SELECT * FROM tbl_company where id not in (SELECT companyID from
tbl_contacts)






Regards,
Lasitha Alawatta
Application Developer
Destinations of the World Holding Establishment
P O Box: 19950
Dubai, United Arab Emirates
( Ph +971 4 295 8510 (Board) / 1464 (Ext.)
7 Fax +971 4 295 8910
+ [EMAIL PROTECTED] 

-Original Message-
From: John Pillion [mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Wednesday, October 03, 2007 2:21 PM
To: [EMAIL PROTECTED]; php-db@lists.php.net
Subject: [PHP-DB] the opposite of a join?

I have a company table and a contacts table.  In the contacts table,
there
is a field called "companyID" which is a link to a row in the company
table.

 

What is the easiest way to query the company table for all the company
rows
whose ID is NOT linked to in the contact table? Basically, the opposite
of a
join?

 

Thanks

 

J

 

DOTW DISCLAIMER:

This e-mail and any attachments are strictly confidential and intended for the 
addressee only. If you are not the named addressee you must not disclose, copy 
or take
any action in reliance of this transmission and you should notify us as soon as 
possible. If you have received it in error, please contact the message sender 
immediately.
This e-mail and any attachments are believed to be free from viruses but it is 
your responsibility to carry out all necessary virus checks and DOTW accepts no 
liability
in connection therewith. 

This e-mail and all other electronic (including voice) communications from the 
sender's company are for informational purposes only.  No such communication is 
intended
by the sender to constitute either an electronic record or an electronic 
signature or to constitute any agreement by the sender to conduct a transaction 
by electronic means.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Re: [PHP] the opposite of a join?

2007-10-03 Thread Matt Anderton

you could do a RIGHT OUTER JOIN WHERE the company table is on the right to
show you the companies that do not exist in the contacts table:

SELECT a.name, b.name
FROM contacts a
RIGHT OUTER JOIN company b
ON a.company_id = b.id
WHERE a.name IS NULL;

results:
+--+---+
| name | name  |
+--+---+
| NULL | Company C |
+--+---+

contacts:
++--++
| id | name | company_id |
++--++
|  1 | Gerald Ford|  2 |
|  2 | Jimmy Carter |  1 |
|  3 | Bill Clinton |  2 |
++--++

company:
++---+
| id | name  |
++---+
|  1 | Company A |
|  2 | Company B |
|  3 | Company C |
++---+

-- matt



On 10/3/07, Zoltán Németh <[EMAIL PROTECTED]> wrote:
>
> 2007. 10. 3, szerda keltezéssel 05.21-kor [EMAIL PROTECTED] ezt írta:
> > I have a company table and a contacts table.  In the contacts table,
> there
> > is a field called "companyID" which is a link to a row in the company
> table.
> >
> >
> >
> > What is the easiest way to query the company table for all the company
> rows
> > whose ID is NOT linked to in the contact table? Basically, the opposite
> of a
> > join?
> >
>
> maybe something like
>
> SELECT * FROM company WHERE (SELECT COUNT(*) FROM contact WHERE
> company_id = company.company_id)=0
>
> it's not very efficient, but I don't have any better idea. someone else?
>
> greets
> Zoltán Németh
>
> >
> >
> > Thanks
> >
> >
> >
> > J
> >
> >
> >
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

[PHP-DB] Re: [PHP] the opposite of a join?

2007-10-03 Thread Satyam

- Original Message - 
From: "Zoltán Németh" <[EMAIL PROTECTED]>


it's not very efficient, but I don't have any better idea. someone else?



Indeed, that sort of query is one of the worst and there is little you can 
do to improve it save making sure you have an index on the field of the 
table pointed at, even if you create it for this query and drop it once 
done.



greets
Zoltán Németh




Thanks



J





--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.39/1045 - Release Date: 
02/10/2007 18:43





--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Re: [PHP] the opposite of a join?

2007-10-03 Thread Nadim Attari


Zoltán Németh wrote:

2007. 10. 3, szerda keltezéssel 05.21-kor [EMAIL PROTECTED] ezt írta:
  

I have a company table and a contacts table.  In the contacts table, there
is a field called "companyID" which is a link to a row in the company table.

 


What is the easiest way to query the company table for all the company rows
whose ID is NOT linked to in the contact table? Basically, the opposite of a
join?




maybe something like

SELECT * FROM company WHERE (SELECT COUNT(*) FROM contact WHERE
company_id = company.company_id)=0

it's not very efficient, but I don't have any better idea. someone else?

greets
Zoltán Németh

From the Manual

   *

 If there is no matching record for the right table in the |ON| or
 |USING| part in a |LEFT JOIN|, a row with all columns set to
 |NULL| is used for the right table. You can use this fact to find
 records in a table that have no counterpart in another table:

 mysql> SELECT table1.* FROM table1
 ->LEFT JOIN table2 ON table1.id=table2.id
 ->WHERE table2.id IS NULL;
 


 This example finds all rows in |table1| with an |id| value that is
 not present in |table2| (that is, all rows in |table1| with no
 corresponding row in |table2|). This assumes that |table2.id| is
 declared |NOT NULL|.

Here it goes:

select company.* from company left join contacts on company.companyId = 
contacts.companyId where contacts.companyId IS NULL


Hope it helps...

Nadim Attari
Alienworkers.com

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] the opposite of a join?

2007-10-03 Thread TG


Actually you still want to use a join, just an OUTER join instead of an INNER 
one.

With an OUTER join, you can get all the rows that match as well as rows where 
it doesn't match:

http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

In the example there, DepartmentID 36 is present in the `Employee` table but 
not in the `Department` table so you get NULL for the `Department` data.

Then it's just a matter of checking for NULL.Remember that you can't do 
"WHERE DepartmentID = NULL" because that will always end up being TRUE 
(can't use regular comparisons with NULL), you have to use "WHERE 
DepartmentID IS NULL".

-TG



- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>, 
Date: Wed, 3 Oct 2007 05:21:06 -0500
Subject: [PHP] the opposite of a join?

> I have a company table and a contacts table.  In the contacts table, there
> is a field called "companyID" which is a link to a row in the company table.
> 
>  
> 
> What is the easiest way to query the company table for all the company rows
> whose ID is NOT linked to in the contact table? Basically, the opposite of a
> join?
> 
>  
> 
> Thanks
> 
>  
> 
> J
> 
>  
> 
> 
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] the opposite of a join?

2007-10-03 Thread Zoltán Németh

2007. 10. 3, szerda keltezéssel 05.21-kor [EMAIL PROTECTED] ezt írta:
> I have a company table and a contacts table.  In the contacts table, there
> is a field called "companyID" which is a link to a row in the company table.
> 
>  
> 
> What is the easiest way to query the company table for all the company rows
> whose ID is NOT linked to in the contact table? Basically, the opposite of a
> join?
> 

maybe something like

SELECT * FROM company WHERE (SELECT COUNT(*) FROM contact WHERE
company_id = company.company_id)=0

it's not very efficient, but I don't have any better idea. someone else?

greets
Zoltán Németh

>  
> 
> Thanks
> 
>  
> 
> J
> 
>  
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Can't open SQLite DB... but only when using mod_php??