[PHP-DB] subtracting one query from the other
Please help, I am stuck and my mind is frozen. I want to subtrack one query from the other. I have one query that brings all the records. The second gets another set of records. I want to subtract the second query from the first one . How do i do this? First query produces like 1000 records. Second has like 800 records i want to display the other 200 that dont exist in the first query. Please help. I have this but it is too slow and doesnt work in php but does in mssql. select distinct a.playlist, a.material_id, a.destination_locator, a.destination, a.air_time, a.traffic_duration, a.comment, a.title, a.device_name, m.media_locator as source_locator, m.medium_name as source from cch_dubber_dublist_view11 a left outer join lib_copy_view m on a.material_id=m.material_id where (A.material_id is not null) and (A.material_id <>'') and NOT EXISTS ( select null from dev_device_view d inner join lib_copy_view c on d.device_name= c.medium_name where c.material_id = a.material_id AND c.media_locator = a.destination_locator AND d.device_name = a.device_name ) _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Populating a dropdown list with ENUM values...
Thanks to everyone who responded. I have found a function that
fills the role perfectly.
-Original Message-
From: Russ [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 26, 2002 7:47 PM
To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Populating a dropdown list with ENUM values...
G'day Scott:
I wrote this function to do just what you're atfer:
You may need to mess with it for you're own needs - but go for your
life!
//function: "enum_select()" - automatically generate an HTML select
menu from a MySQL ENUM field
//1). takes a table name: '$table'
//2). a name for the menu '$name'
//3). a CSS class
function enum_select($table,$name,$class) {
$sql = "SHOW COLUMNS FROM $table";
$result = mysql_query($sql);
$select = "\n\t";
while($myrow = mysql_fetch_row($result)){
$enum_field = substr($myrow[1],0,4);
if($enum_field == "enum"){
global $enum_field;
$enums = substr($myrow[1],5,-1);
$enums = ereg_replace("'","",$enums);
$enums = explode(",",$enums);
foreach($enums as $val) {
$select .= "$val\n\t";
}//end foreach
}//end if
}//end while
$select .= "\r";
return $select;
}//end function
All the best.
Russ
-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 27, 2002 3:38 AM
To: '[EMAIL PROTECTED]'
Subject: [PHP-DB] Populating a dropdown list with ENUM values...
I am working on an application that has several different fields
that are populated dropdown lists. In every case, this data is simply
data
from a MySQL table column and works great. I have run into a new
difficulty
though... One field that I am wanting to populate in a dropdown list is
an
ENUM field. I am trying to figure out how to do this. I have seen a
couple
ideas in the newsgroups, but most are pretty complex and about a year or
so
old. I am hoping for a simpler solution that may have been developed
since
these posts. Thanks in advance.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
--
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
RE: [PHP-DB] Populating a dropdown list with ENUM values...
G'day Scott:
I wrote this function to do just what you're atfer:
You may need to mess with it for you're own needs - but go for your
life!
//function: "enum_select()" - automatically generate an HTML select
menu from a MySQL ENUM field
//1). takes a table name: '$table'
//2). a name for the menu '$name'
//3). a CSS class
function enum_select($table,$name,$class) {
$sql = "SHOW COLUMNS FROM $table";
$result = mysql_query($sql);
$select = "\n\t";
while($myrow = mysql_fetch_row($result)){
$enum_field = substr($myrow[1],0,4);
if($enum_field == "enum"){
global $enum_field;
$enums = substr($myrow[1],5,-1);
$enums = ereg_replace("'","",$enums);
$enums = explode(",",$enums);
foreach($enums as $val) {
$select .= "$val\n\t";
}//end foreach
}//end if
}//end while
$select .= "\r";
return $select;
}//end function
All the best.
Russ
-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 27, 2002 3:38 AM
To: '[EMAIL PROTECTED]'
Subject: [PHP-DB] Populating a dropdown list with ENUM values...
I am working on an application that has several different fields
that are populated dropdown lists. In every case, this data is simply
data
from a MySQL table column and works great. I have run into a new
difficulty
though... One field that I am wanting to populate in a dropdown list is
an
ENUM field. I am trying to figure out how to do this. I have seen a
couple
ideas in the newsgroups, but most are pretty complex and about a year or
so
old. I am hoping for a simpler solution that may have been developed
since
these posts. Thanks in advance.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
--
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: Populating a dropdown list with ENUM values...
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] says... > I am working on an application that has several different fields > that are populated dropdown lists. In every case, this data is simply data > from a MySQL table column and works great. I have run into a new difficulty > though... One field that I am wanting to populate in a dropdown list is an > ENUM field. I am trying to figure out how to do this. I have seen a couple > ideas in the newsgroups, but most are pretty complex and about a year or so > old. I am hoping for a simpler solution that may have been developed since > these posts. Thanks in advance. > > Scott Nipp > Phone: (214) 858-1289 > E-mail: [EMAIL PROTECTED] > Web: http:\\ldsa.sbcld.sbc.com Then you've possibly seen this one? I can't remember from whence I got it so can't properly attribute it. Watch the linewraps :-) Cheers -- David Robley Temporary Kiwi! Quod subigo farinam -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] PHP and msql Error
Hi With MySql that means that there is no result returned. Usually SQL error, but could be a setup problem if you have not run any queries succesfuly. Not sure if the same applies to MSql, but try echoing the error, echo msql_error($conn) (if it is the same as MySql) HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- > -Original Message- > From: comp boy [mailto:[EMAIL PROTECTED]] > Sent: 26 June 2002 20:09 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: [PHP-DB] PHP and msql Error > > > I am getting the following error: > > Warning: Supplied argument is not a valid mSQL result resource in > /usr/local/apache/htdocs/inc/listings.inc on line 24 > > listings.inc: 24 > $numRows = msql_numrows( $result ); > > The manual on php.net says that it takes no variables msql_numrows(void) > however it says it is another version of msql_num_rows(int msql); > > The code I am trying to run, runs perfectly fine on another one of our > systems. I was just setting up a development server and now I am getting > this problem. The specs are the same except for the version of Apache > production is 1.3.9 and development is 1.3.26 > > PHP vesrion 4.1.2 > > ./msqladmin version > Version Details :- > msqladmin version 2.0.11 > mSQL server version 2.0.11 > mSQL protocol version 23 > mSQL connection Localhost via UNIX socket > Target platform Linux-2.2.14-5.0-i686 > > PHP was configured: > ./configure --with-apxs=/usr/local/apache/bin/apxs --with-msql > > msql was a default install. > > Does anyone know what I did wrong? Do I need to recompile with some > different options? > > TIA > Jason > > > -- > 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
RE: [PHP-DB] Last ID from database?
This is how i do the same thing as i belive you're asking.
$result = mysql_query("select max(id) from [table]");
$lastID = mysql_result($result, 0);
Hope this helps
Jeff
"Cosby, Christopher"
, [EMAIL PROTECTED]
ciatl.com> cc:
Subject: RE: [PHP-DB] Last ID from
database?
06/26/2002 04:08 PM
> -Original Message-
> From: Chris Payne [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 26, 2002 3:57 PM
> To: [EMAIL PROTECTED]
> Subject: [PHP-DB] Last ID from database?
>
>
> Hi there everyone,
>
> How Can I grab just the LAST ID from a database in MySQL with PHP
> easily?
Which LAST ID? There are several meanings there. I'll try a couple.
1) If the column is the only auto_increment column in a table, and you want
to know the value of that column after an INSERT, just use the following to
get the value that was inserted.
2) Just want to get the highest value in a column? Try this.
columnname;
}
?>
>
> Thanks you :-)
>
> Chris
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002
>
>
- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - -
This e-mail and any attachments may contain information which is
confidential, proprietary, privileged or otherwise protected by law. The
information is solely intended for the named addressee (or a person
responsible for delivering it to the addressee). If you are not the
intended recipient of this message, you are not authorized to read, print,
retain, copy or disseminate this message or any part of it. If you have
received this e-mail in error, please notify the sender immediately by
return e-mail and delete it from your computer.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Last ID from database?
> -Original Message- > From: Chris Payne [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 26, 2002 3:57 PM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Last ID from database? > > > Hi there everyone, > > How Can I grab just the LAST ID from a database in MySQL with PHP > easily? Which LAST ID? There are several meanings there. I'll try a couple. 1) If the column is the only auto_increment column in a table, and you want to know the value of that column after an INSERT, just use the following to get the value that was inserted. 2) Just want to get the highest value in a column? Try this. columnname; } ?> > > Thanks you :-) > > Chris > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002 > > - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer.
Re: [PHP-DB] Last ID from database?
Hi Chris, On Wed, 26 Jun 2002, Chris Payne wrote: > Hi there everyone, > > How Can I grab just the LAST ID from a database in MySQL with PHP > easily? http://devrim.oper.metu.edu.tr - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Last ID from database?
$sql= "SELECT ID FROM table_name ORDER BY ID DESC limit 1" . .. ... -- jp At 15:56 26/06/02, Chris Payne wrote: >Hi there everyone, > >How Can I grab just the LAST ID from a database in MySQL with PHP >easily? > >Thanks you :-) > >Chris > > >--- >Outgoing mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002 > Juan Pablo Aqueveque <[EMAIL PROTECTED]> Ingeniero de Sistemas Departamento de Redes y Comunicaciones http://www.drc.uct.cl Universidad Católica de Temuco. Tel:(5645) 205 630 Fax:(5645) 205 628 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Last ID from database?
-Original Message- From: Ryan Jameson (USA) Sent: Wednesday, June 26, 2002 1:59 PM To: 'Chris Payne' Subject: RE: [PHP-DB] Last ID from database? It depends on if you just inserted it, if you did: mysql_insert_id if not, you'll need to do a query for SELECT max(ID) as lastID from Table :-) <>< Ryan -Original Message- From: Chris Payne [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 26, 2002 1:57 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Last ID from database? Hi there everyone, How Can I grab just the LAST ID from a database in MySQL with PHP easily? Thanks you :-) Chris --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Last ID from database?
Chris, $insertID = mysql_insert_id(); Check http://www.php.net/manual/en/function.mysql-insert-id.php for additional info. Rich -Original Message- From: Chris Payne [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 26, 2002 3:57 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Last ID from database? Hi there everyone, How Can I grab just the LAST ID from a database in MySQL with PHP easily? Thanks you :-) Chris --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Last ID from database?
Hi there everyone, How Can I grab just the LAST ID from a database in MySQL with PHP easily? Thanks you :-) Chris --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002
[PHP-DB] PHP and msql Error
I am getting the following error: Warning: Supplied argument is not a valid mSQL result resource in /usr/local/apache/htdocs/inc/listings.inc on line 24 listings.inc: 24 $numRows = msql_numrows( $result ); The manual on php.net says that it takes no variables msql_numrows(void) however it says it is another version of msql_num_rows(int msql); The code I am trying to run, runs perfectly fine on another one of our systems. I was just setting up a development server and now I am getting this problem. The specs are the same except for the version of Apache production is 1.3.9 and development is 1.3.26 PHP vesrion 4.1.2 ./msqladmin version Version Details :- msqladmin version 2.0.11 mSQL server version 2.0.11 mSQL protocol version 23 mSQL connection Localhost via UNIX socket Target platform Linux-2.2.14-5.0-i686 PHP was configured: ./configure --with-apxs=/usr/local/apache/bin/apxs --with-msql msql was a default install. Does anyone know what I did wrong? Do I need to recompile with some different options? TIA Jason -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Populating a dropdown list with ENUM values...
Did you see the very last user contributed comment at http://www.php.net/manual/en/function.mysql-fetch-field.php? It'll help you immensely. Christopher S. Cosby SciCare Software Services 770.236.1128 > -Original Message- > From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 26, 2002 3:38 PM > To: '[EMAIL PROTECTED]' > Subject: [PHP-DB] Populating a dropdown list with ENUM values... > > > I am working on an application that has several different fields > that are populated dropdown lists. In every case, this data > is simply data > from a MySQL table column and works great. I have run into a > new difficulty > though... One field that I am wanting to populate in a > dropdown list is an > ENUM field. I am trying to figure out how to do this. I > have seen a couple > ideas in the newsgroups, but most are pretty complex and > about a year or so > old. I am hoping for a simpler solution that may have been > developed since > these posts. Thanks in advance. > > Scott Nipp > Phone: (214) 858-1289 > E-mail: [EMAIL PROTECTED] > Web: http:\\ldsa.sbcld.sbc.com > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer.
[PHP-DB] Populating a dropdown list with ENUM values...
I am working on an application that has several different fields that are populated dropdown lists. In every case, this data is simply data from a MySQL table column and works great. I have run into a new difficulty though... One field that I am wanting to populate in a dropdown list is an ENUM field. I am trying to figure out how to do this. I have seen a couple ideas in the newsgroups, but most are pretty complex and about a year or so old. I am hoping for a simpler solution that may have been developed since these posts. Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Question
Thanks for all of the advice. I am learning, and want to learn the right way, and all the advice given will benefit me greatly in the long run. I wish there was an easier way to deal with relationships in MySql, but I am sure that will come along in time. Mike > From: "Tracker 1" <[EMAIL PROTECTED]> > Date: Mon, 24 Jun 2002 01:57:17 -0700 > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Re: Question > > you are thinking backwards.. > > drives 1-> computers > > tblComputers > iCompID As BigInt Primary Key Autonumber, > sCompName As VARCHAR(25) > ... > > tblDrives > iDriveID As BigInt Primary Key Autonumber, > iComputerID As BigInt Not Null, > Foreign Key fkDriveComputer (iCompID) References tblComputers(iCompID) > > note, you need to use InnoDB tables in mysql for foreign key references > to work properly, you should do it in your code anyway.. > > select * from tblComputers > for each rsComp.. > select * from drives where iComputerID = (rsComp.iCompID) > > there you go.. :) > > -- > === > Michael J. Ryan - tracker1[*at*]theroughnecks.com > Roughneck BBS: http://www.theroughnecks.net telnet://theroughnecks.net > === > Y!: aztracker1 - aim: azTracker1 - icq: 4935386 - msn: see email > One program for aim/icq/yahoo/msn/irc - http://www.trillian.cc/ > > > "Mike Tuller" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... >> I am wanting to create a PHP frontend to a database that holds information >> about the computers that I take care of. I have a problem though when it >> comes to storing hard drive information. In most cases, the computers have >> one drive, but some have 2 or more, so I can't create the main database with >> fields like this. >> >> ComputerID >> HardDriveType >> HardDriveSize >> >> I know I will need to create a separate table to hold information about the >> drives, and connect them to the computer by attaching the primary key of the >> drives table to the Computer table. >> >> Computer Table >> >> ComputerID >> DriveID >> >> Drive Table >> >> DriveID >> HardDriveType >> HardDriveSize >> >> This is where I am unsure. If there is more than one drive, then this would >> be incomplete because it would only show one drive. What is the best way to >> make it so that all drives show for the computer, or am I doing this >> backwards? Should I tie the computer to the drive instead? >> >> Thanks, >> Mike >> > > > > -- > 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
Re: [PHP-DB] OSCON: anybody interested in my Oracle talk?
Hello, I m sure I will not be in OSCON, but if there is a possibily for you to go to International PHP Conf, you will make certainly a very good session there, not lost your job and certainly see me ;)). International PHP Conference 2002 call for papers : http://www.phpconference.de/2002/kt/input/ Calling for sessions : http://php.weblogs.com/ (certainly others sites with the call) hth pa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] OSCON: anybody interested in my Oracle talk?
hi, seems like oreilley will cancel my tutorial about PHP and Oracle at OSCON in san diego as there are not enough ppls interested. so - if you go to oscon, and if you are interested in that subject _and_ if you have not decided to sign up for my tutorial - hurry up and register (deadline is friday), elsewise it'll be cancelled:-( hoping for the best... tc PS: i was going to do some intersting new stuff for OSCON (scollable cursors etc) - so if you use oracle with PHP this is a *must*:-) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Why do these values not match?
On Wed, 26 Jun 2002 06:52:14 -0700 (PDT) Manuel <[EMAIL PROTECTED]> wrote: > Any idea why this problem does not manifest itself in Foxpro, Excel or Visual Basic? This problem happens also with VB. But later :), By example if you use decimal type, smallest non-zero number is +/-0.0001, that differs from php. But just for your information, make "my" past remarks a general rule on howto work with floating point number, any langage, any platform. Just a fact. hth pa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Why do these values not match?
Any idea why this problem does not manifest itself in Foxpro, Excel or Visual Basic? Pierre-Alain Joye wrote:On Tue, 25 Jun 2002 18:14:13 -0700 wrote: > IEEE standard is precision to 6 decimal places. > Having an epsilon of < 2 is ridiculously small, and > well under the IEEE / ANSI standard. While floats > should never be directly compared as a matter of > course, in this instance it's a bit silly. Never test equality with two floats,this is a rule in ANSI C too, not directly the standard, but a well known fact. And this fact is one in many langages. Try a test with mysql :). I will not continue discussions about bugs or not, that has been already discussed many times :) check the archives. hth hth pa ~ Manuel Ochoa ~ Seven days is too long to wait for a gun! - Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup
[PHP-DB] Re: prevent results
Hi,
i found a solution by myself
i think it's very complex but may help others
for finding solutions
Thats the query:
SELECT entries.id, entries.head, entries.date, entries.author
FROM entries
LEFT JOIN category ON entries.cat_no REGEXP
CONCAT("\\.",category.id,"(\\.|$)")
LEFT JOIN entgroup ON entries.id = entgroup.eid
LEFT JOIN catgroup ON category.id = catgroup.cid
LEFT JOIN mzgroup AS mze ON mze.id = entgroup.gid
LEFT JOIN mzgroup AS mzc ON mzc.id = catgroup.gid
WHERE
MATCH (head,text) AGAINST ("Who's not") &&
(entgroup.gid IS NULL || entgroup.gid = *user-group-id*)
GROUP BY entries.id HAVING min(catgroup.gid IS NULL || catgroup.gid =
*user-group-id*)<>0;
This is the result if all conditions are true otherwise i get none
++---++--+
| id | head | date | author |
++---++--+
| 6 | Who's not ... | 2002-06-13 | Martin Adler |
++---++--+
the soluton for my former problem is in the HAVING clause
the stuff in the min()-function returns only 1 when the category
have the same group-id as the user or the category don't have a
group-id otherwise it returns 0 this condition is executet for every
row in the table. If there's no conflict with the user-rights in "every"
row should be 1 the result of the condition
The min()-function selects the smallest value, if everything is Ok
the value is 1 and i get my result if not the value is 0 and 0 <> 0
isn't true and i don't get a result
if anyone have a questions about this query
just mail me
i'll try to help
i also thank everyone who tried to help me to find a soluton
and if anyone have a better solution please let me know
Martin
- Original Message -
From: "Martin Adler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, June 26, 2002 9:19 AM
Subject: prevent results
> Hi,
>
> unfortunately I still stand before the same problem
> is it possible to force mysql in a SELECT clause depend on a
> condition to prevent results?
> I would be happy and grateful for every help
>
> OK here is my Query
>
> SELECT entries.id, entries.head, entries.date, entries.author,
(catgroup.gid
> IS NULL || catgroup.gid = *user-group-id*) AS indi
> FROM entries
> LEFT JOIN category ON entries.cat_no REGEXP
> CONCAT("\\.",category.id,"(\\.|$)")
> LEFT JOIN entgroup ON entries.id = entgroup.eid
> LEFT JOIN catgroup ON category.id = catgroup.cid
> LEFT JOIN mzgroup AS mze ON mze.id = entgroup.gid
> LEFT JOIN mzgroup AS mzc ON mzc.id = catgroup.gid
> WHERE
> MATCH (head,text) AGAINST ("Who's not") &&
> (entgroup.gid IS NULL || entgroup.gid = *user-group-id*);
>
>
> With thies query, i try to get a entry with a fulltext search.
> The entry should only be shown if the entry-group-id is NULL or
> is the same as the user-group-id and
> if all category-group-id's above the entry is NULL or
> is the same as the user-group-id
>
> With "GROUP BY indi" i'll get maximally 2 results with which i can handle
> easy in PHP
> but i want to try it in MySQL.
> Maybe someone knows a cleaner solution
>
> This is the result:
> ++---++--+--+
> | id | head | date | author | indi |
> ++---++--+--+
> | 6 | Who's not ... | 2002-06-13 | Martin Adler |1 |
> | 6 | Who's not ... | 2002-06-13 | Martin Adler |0 |
> | 6 | Who's not ... | 2002-06-13 | Martin Adler |1 |
> ++---++--+--+
>
>
> Here are the used tables
>
> Table entries
>
++-+-+---++--+--
> ---+--
--
> --+
> | id | uid | cat_no | head | date | author |
text
> | keywd|
>
++-+-+---++--+--
> ---+--
--
> --+
> | 1 | 1 | 0.1.3.8 | Headline | 2002-03-12 | Martin Adler | Some
Text
> | keywords |
> | 2 | 2 | 0.1.2 | Mittagessen | 2002-04-03 | Martin Adler |
> Fruehjahrsputz | Spring
> |
> | 3 | 4 | 0.1.2 | Hausboot | 2002-04-03 | Martin Adler |
> Quarktasche | Summer
> |
> | 4 | 2 | 0.1.2.21.22 | Kongo | 2002-04-04 | Martin Adler |
Kebup
> | Springer |
> | 5 | 2 | 0.1.3 | Hanfkissen| 2002-04-04 | Martin Adler |
> Bananen sind lecker!| Palmen Testwagen
> Radiergummi |
> | 6 | 2 | 0.1.2.24| Who's not ... | 2002-06-13 | Martin Adler | .in
> the group technik shouldn't be able to read this |
> |
>
++-+-+---++--+--
> ---+
[PHP-DB] Sybase problem.
Hello,
I have problem with Sybase. When I try to get data from binary column
using sybase_query, the output consist of 4, most significant digits
only, wheras the rest is omitted. My PHP script looks like follows:
";
sybase_select_db ( "my_db", $conn);
$res = sybase_query ("select TestColumn from TestTable", $conn);
echo sybase_num_rows($res);
echo "";
$r=sybase_result($res,0,0);
echo "$r" ;
?>
---
Value $r after execution of query is 0001 whereas it should be something
like 00012354.
I've tried meny variations of that simple script without success.
I'll be gratefull for help.
Gural.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHPed and DBG
On Wed, 26 Jun 2002 10:41:26 +0200 "Dib, Walid (MED, Stagiaire GEMS)" <[EMAIL PROTECTED]> wrote: > > > > Hello, > > I downloaded the application PHPEd and DBG, I need débugger but I cannot > bind the two applications is what somebody has an idea. Thank you in > advance the link to the forum : http://sourceforge.net/forum/forum.php?forum_id=113095 voila :) pa ps: sorry, wrong thread :) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Form not working...
On Wed, 26 Jun 2002 19:11:46 +1000 Adam Royle <[EMAIL PROTECTED]> wrote: > What you said (Mike) is mostly correct, although the GetSQLValueString() > function in Scott's code automatically puts the quotes around the values > if the datatype definition of "text" is passed to the function. > > Adam the link to the forum : http://sourceforge.net/forum/forum.php?forum_id=113095 voila :) pa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHPed and DBG
On Wed, 26 Jun 2002 10:41:26 +0200 "Dib, Walid (MED, Stagiaire GEMS)" <[EMAIL PROTECTED]> wrote: > > > > Hello, > > I downloaded the application PHPEd and DBG, I need débugger but I cannot > bind the two applications is what somebody has an idea. Thank you in > advance I did not use DBG on windows, but I remember tips and explanations in the DBG faq and sourceforge forum (dbg project page). hth pa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Form not working...
What you said (Mike) is mostly correct, although the GetSQLValueString() function in Scott's code automatically puts the quotes around the values if the datatype definition of "text" is passed to the function. Adam -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: converting a string to a date
My fault...
wasn't looking hard enough i guess, thats what 2 a.m. will do to you I guess.
here is how to do it, for anyone who finds themselves in a similar
situation:
$result = date( "y m d", strtotime("$aDay $aMonth $aYear"));
echo $result;
Seth Yount wrote:
> I am trying to find an example of converting a string to a date format
> suitable for MySQL input. for example...
>
> ' 2002 March 15 ' needs to be converted to ' 02-03-15 '
>
> I can reverse this process fine, but am having trouble doing it this way
>
> -- Seth
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PHPed and DBG
Hello, I downloaded the application PHPEd and DBG, I need débugger but I cannot bind the two applications is what somebody has an idea. Thank you in advance -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] converting a string to a date
I am trying to find an example of converting a string to a date format suitable for MySQL input. for example... ' 2002 March 15 ' needs to be converted to ' 02-03-15 ' I can reverse this process fine, but am having trouble doing it this way -- Seth -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] prevent results
Hi,
unfortunately I still stand before the same problem
is it possible to force mysql in a SELECT clause depend on a
condition to prevent results?
I would be happy and grateful for every help
OK here is my Query
SELECT entries.id, entries.head, entries.date, entries.author, (catgroup.gid
IS NULL || catgroup.gid = *user-group-id*) AS indi
FROM entries
LEFT JOIN category ON entries.cat_no REGEXP
CONCAT("\\.",category.id,"(\\.|$)")
LEFT JOIN entgroup ON entries.id = entgroup.eid
LEFT JOIN catgroup ON category.id = catgroup.cid
LEFT JOIN mzgroup AS mze ON mze.id = entgroup.gid
LEFT JOIN mzgroup AS mzc ON mzc.id = catgroup.gid
WHERE
MATCH (head,text) AGAINST ("Who's not") &&
(entgroup.gid IS NULL || entgroup.gid = *user-group-id*);
With thies query, i try to get a entry with a fulltext search.
The entry should only be shown if the entry-group-id is NULL or
is the same as the user-group-id and
if all category-group-id's above the entry is NULL or
is the same as the user-group-id
With "GROUP BY indi" i'll get maximally 2 results with which i can handle
easy in PHP
but i want to try it in MySQL.
Maybe someone knows a cleaner solution
This is the result:
++---++--+--+
| id | head | date | author | indi |
++---++--+--+
| 6 | Who's not ... | 2002-06-13 | Martin Adler |1 |
| 6 | Who's not ... | 2002-06-13 | Martin Adler |0 |
| 6 | Who's not ... | 2002-06-13 | Martin Adler |1 |
++---++--+--+
Here are the used tables
Table entries
++-+-+---++--+--
---+
--+
| id | uid | cat_no | head | date | author | text
| keywd|
++-+-+---++--+--
---+
--+
| 1 | 1 | 0.1.3.8 | Headline | 2002-03-12 | Martin Adler | Some Text
| keywords |
| 2 | 2 | 0.1.2 | Mittagessen | 2002-04-03 | Martin Adler |
Fruehjahrsputz | Spring
|
| 3 | 4 | 0.1.2 | Hausboot | 2002-04-03 | Martin Adler |
Quarktasche | Summer
|
| 4 | 2 | 0.1.2.21.22 | Kongo | 2002-04-04 | Martin Adler | Kebup
| Springer |
| 5 | 2 | 0.1.3 | Hanfkissen| 2002-04-04 | Martin Adler |
Bananen sind lecker!| Palmen Testwagen
Radiergummi |
| 6 | 2 | 0.1.2.24| Who's not ... | 2002-06-13 | Martin Adler | .in
the group technik shouldn't be able to read this |
|
++-+-+---++--+--
---+
--+
* These are the entries, which are assigned to the categories
Table category
| id | cat_no | cat |
+++---+
| 1 | 0 | ROOT |
| 2 | 0.1| Hardware |
| 3 | 0.1| Software |
| 4 | 0.1.2 | HP|
| 5 | 0.1.2.4| e-pc |
| 6 | 0.1.2.4.5 | CD-Brenner|
| 7 | 0.1.2 | PC|
| 8 | 0.1.3 | php |
| 9 | 0.1.3.8| rekursiv |
| 10 | 0.1.3.8.9 | path |
| 12 | 0.1.3.8.9.10.11| kategorie12 |
| 15 | 0.1.3.8.9.10.11| kategorie15 |
| 16 | 0.1.3.8.9.10.11| kategorie16 |
| 17 | 0.1.3.8.9.10.11| kategorie17 |
| 20 | 0.1.3.8.9.10.11.12 | kategorie20 |
| 21 | 0.1.2 | Netzwerktechnik |
| 22 | 0.1.2.21 | Router u. L3-Switches |
| 24 | 0.1.2 | abba |
+++---+
* cat_no is the Hierarchy --HP is a subdir of Hardware and Hardware is a
subdir of ROOT and ROOT is the topleveldir
Table mzgroup
++-+
| id | name|
++-+
| 1 | nobody |
| 2 | technik |
++-+
Table catgroup
+-+-+
| gid | cid |
+-+-+
| 2 | 24 |
| 2 | 23 |
| 1 | 2 |
+-+-+
* gid = group id, eid = category id
Table entgroup
+-+-+
| gid | eid |
+-+-+
| 2 | 6 |
+-+-+
* gid = group id, eid = entries id
thank's
sincerely
Martin
-
Martin Adler CGI, Perl, PHP
Continum AG Tel. +49 761 4794090
Bötzinger Straße 29a Fax. +49 761 4794099
79111 Freiburg i. Br.
