Re: [PHP-DB] MySQL Query
At 1:01 PM +0100 5/3/02, CM wrote: >SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, >PaysPeriode.DateDebut,PaysPeriode.DateFin >FROM Periode, PaysPeriode, Pays >WHERE Pays.RefPays = PaysPeriode.Refpays >AND Periode.RefPeriode = PaysPeriode.RefPeriode >AND PaysPeriode.DateDebut > 2002020700 >AND Pays.RefPays = 1 >ORDER BY Periode.libPeriode, PaysPeriode.DateDebut > >This is soo close to working but just not quite. The Access query just >pulled out unique values for the Periode.LibPeriod field which was perfect. >But the MySQL seems to pull them all out. I have tried GROUPING the result >by the Periode.LibPeriode which gives me unique values in the field but I >need the unique values with the highest startdate i.e. PaysPeriode.DateDebut >but it always pulls out the first start date? I'd add a Max() function with the Group By, like... SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, MAX(PaysPeriode.DateDebut), PaysPeriode.DateFin FROM Periode, PaysPeriode, Pays WHERE Pays.RefPays = PaysPeriode.Refpays AND Periode.RefPeriode = PaysPeriode.RefPeriode AND PaysPeriode.DateDebut > 2002020700 AND Pays.RefPays = 1 GROUP BY Periode.libPeriode ORDER BY Periode.libPeriode, PaysPeriode.DateDebut I'm not certain why your Access query only pulled one record. From the skim, it seems as if it should pull all that match, since you didn't indicate you wanted only those distinctly, or a max value. Though often an Inner Join can act as a limiter as well, so that maybe why it worked. But anyway, the find all records, max or min of the value you want, group records (max, min, ave, count , etc. functions all REQUIRE the Group By, you'll generate an error if you forget it), then order them, should work. Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . transforming nonprofits through technology . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Concept help required
At 12:53 PM + 1/7/02, George Pitcher wrote: > >One of the features of the FMPro solution is that when a user is looking at >a list of resources (in fact, university course packs) each row will display >the number of associated records from the 'books' database. Now which is the >best method to do this (speed/efficiency)? I have tried performing a >row-level query on the related db but its very slow and times out before the >second row can be displayed. Alternately, I could set the main db to >increase/decrease a number field when adding or deleting books from the >list. Which is recommended? Not to interrupt with the current discussion, because some of the issues brought up, will eventually be needed by you. But for the issue at hand, what you want is SQL. I, too, am an avid FileMaker Developer, who uses PHP/MySQL or FileMaker/Lasso, FileMaker/PHP for web-based solutions. I very much miss my FileMaker calculations, but SQL can handle many of them. I actually find Filemaker's handling of string functions (Filemaker calls them text functions) more flexible, and full-featured then SQL. But with a lot of practice in SQL, you can replicated (recreate FileMaker level functionality) by using SQL. For your situation, it sounds like a calculation like the one below will work. Not knowing what you considered resources, I've basically mentally associated to categories. Lets state that you have a library table that stores books, articles, etc. A category table that stores categories. And a join table called media_cat that stores all relations between library and category. That being true every library_item can have one or more categories, and you wish to create a count of the number of items in each category. The following query will suffice, and should be adaptable for your situation: SELECT library.type, media_cat.category, categories.description, COUNT(library.type) AS count FROM categories, media_cat LEFT JOIN library ON library.libraryID=media_cat.fileID WHERE categories.category=media_cat.category GROUP BY media_cat.category ORDER BY media_cat.category The results from this could be formatted to display like so: Communications (with 9 documents) Donor Relations (with 3 documents) or like so: * databases (with 2 items) applications, query languages, theory, and implementation * networking (with 1 items) networks, telecommunications, protocols, routers, etc. The above explains the more complicated query between three tables, but you can also do this with a single table, or two tables. Alnisa -- ......... Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . transforming nonprofits through technology . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] PHP/MySQL Library Catalog System
At 12:26 PM -0800 1/10/02, Daniel Barton wrote: >Hi all, > I've noted that a least a couple of people on this list work with >library catalog systems - I'm interested in finding out whether any of >these systems are open-source or are available for a nominal fee. I'm >familiar enough with PHP/MySQL to make one, but I'd also like to avoid >re-inventing the wheel if possible, particularly since I'm not a >librarian :-). The non-profit that I work for has a significant >biological science library that we would like to create a/use an >existing web-based catalog system for. > Also, I'm aware that there is specific funding available for doing >this @ non-profits... I was wondering if anyone else had endeavored >along these lines. > Hi Daniel: You may want to check HotScripts, or the PHP Resource Index (http://www.hotscripts.com) and (http://php.resourceindex.com). These locations will most likely have an array of free or low-cost library solutions. The biggest issue is despite the thought that a library is a library, not all solutions are the same or that similar. Many work for specific purposes. A good example is I've just finished the first phase development of a library system. The solution will eventually be open source, but serves a limited need. The primary goal is for the solution to track materials (books, articles, media), and software for our various client and users. To that regard the library and software tables store any reference of a particular material. From this base, clients and users an then build their own personal libraries for viewing and tracking purposes. So each client can have a library, as well as each user at a client site. Ultimately, I hope to add a lending component to it, and work with some of our nonprofits to do cross-organizational check-outs and returns. The system is highly geared for technology-based libraries, since this is of course our primary purpose for providing the service. So before you go searching, you may want to think a bit more about what your needs are. Are you just tracking books? What about articles? Journals? Magazines? Media? Do you need categories? How many categories can an item be stored in? Single? Multiple? This are just some issues. But once you've gone through them, you may have a better idea of which pre-built systems will work for you or not. Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . transforming nonprofits through technology . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Using PHP with filemaker?
At 4:14 PM +0100 9/10/01, joel wrote: >I am trying to get php to write the data stored in some variables directly >to a filemaker pro 5.5 database. So far I have got the PHP to write to a >.txt file as tab delineated text, but I then have to manually import this >data into filemaker. > >I have not been able to find any specific function for editing filemaker >databases using PHP... >But the php.net manual states: > Try this php class created by Chris Hansen, it was specifically designed to allow PHP to connect with FileMaker. http://www.iviking.org/ to download, or http://www.iviking.org/FX/ to test. Alnisa -- ............. Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . applying technology to transform . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] How can you hide database login passwd in yourscript?
At 6:22 PM +1000 9/5/01, speedboy wrote: > > $0 option: >> Put your user ID and password in a config file, then give only the >> webserver user access to it. Read the config file to make it work. This >> also allows easy switching between test and production environments. > >You can't change the group owner of a file unless you have root. > >That does not stop another php user fopen'ing your config file. > If your creating a config file, first always include .php or .php3 as part of the name, this means that if someone tries to directly access the file using a web browser the file compiles and prints as blank. Secondly, references to the config file should be placed after the and before the tags so even if the page breaks the path to file is never shown to the user. Additionally, if you like and your host is set-up for it, you can store config files and other includes in the (data) folder of your website. Many ISPs offer set-ups that include an (html), (data), (scripts), and (log) folder. The benefit of using a data folder is that it is inaccessible to anyone trying to access it without root permission. Files located in the (data) folder generally must be specified using the path_to_file method ( /myISP/theirHostArea/myUserName/data/) Generally speaking, the first two methods are secure enough. If a PHP user does guess the correct path to you config file, they generally can not access it using the http://www.yoursite.com/include/config.inc.php method, since the file pre-compiles as blank or empty. This means that they would need to gain root privileges to read the file as text. The data folder provides a nice extra layer of security, pretty much insuring that the file can't be called using the http:// method at all since its outside of your web root. Alnisa -- ..... Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . applying technology to transform . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: RE: RE: RE: [PHP-DB] Query construction
At 1:44 PM +0100 8/21/01, Russ Michell wrote: > > I always love that feeling when you beat a problem that refuses to go away. >So do I - but I aint beaten it yet! :-( > >I'm still getting '2' as being the number of posts in each category >when '2' is actually only the >number of posts in *one* of the categories...weird-huh? > Try SELECT category, COUNT(category) AS cat_count FROM postings GROUP BY category ORDER BY category I find using the specific field you want count, instead of the count all fields changes the count results on occasion. Alnisa -- ......... Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . applying technology to transform . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Another SQL Question
At 10:31 AM -0700 8/13/01, Barry Prentiss wrote: >Thx Again Alnisa, > You were very close, and instrumental in solving this seemingly simple >query problem. > The actual query that worked looks like this: > > SQL> select cat.id, cat.name, count(faq_cat.cat_id) as num > 2 from mdfaq_faq_cat faq_cat, mdfaq_category cat > 3 where faq_cat.cat_id (+) = cat.id > 4 group by cat.id, cat.name; > Funny, I think I just sent you something somewhat similar. Glad it worked. Alnisa -- ......... Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . applying technology to transform . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Another SQL Question
At 9:29 AM -0700 8/13/01, Barry Prentiss wrote: >Thx Alnisa, > I had tried your suggested approach earlier. > I just tried it again I got the following from SQL*Plus: Wow, Oracle, what a pain. (Actually, it's probably pure laziness that MySQL doesn't check for this, but anyway). Anyway, I looked up the Oracle errors ORA-00937, and ORA-00979, and they basically both indicate that you can't return a column that isn't included in the Group BY expression. You can't just remove the GROUP BY, because the COUNT requires a GROUP statement. Error 979 basically says line 1, includes a non-grouped expression. Error 937 is the not-a single-group group error, or says basically the same thing. You can look up Oracle errors at: http://otn.oracle.co.kr/docs/oracle78/server.804/a58312/newch220.htm >SQL> select cat.id, cat.name, count(faq_cat.cat_id) as num_id > 2 from mdfaq_faq_cat faq_cat, mdfaq_category cat > 3 where faq_cat.cat_id = cat.id group by cat.name; >select cat.id, cat.name, count(faq_cat.cat_id) as num_id >* >ERROR at line 1: >ORA-00979: not a GROUP BY expression Applying the above error codes, which indicate that any column returned, must be part of the GROUP function, then the code should look something like: SELECT cat.id, cat.name, COUNT(faq_cat.cat_id) AS num_id FROM faq_cat, cat WHERE faq_cat.id = cat.id GROUP BY cat.name, cat.id or in Oracle speak (as close as I can gather anyway) SQL>SELECT cat.id, cat.name, COUNT(faq_cat.cat_id) AS num_id 2 FROM mdfaq_faq_cat faq_cat, mdfaq_category cat 3 WHERE faq_cat.id = cat.id GROUP BY cat.name, cat.id This just adds the cat.id to the GROUP BY statement. I'm not even certain if that's allowable. But if not remove the cat.id and see if it works, or what error is returned. All these different implementations of SQL occasional kill me. Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . applying technology to transform . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Another SELECTING problem :-(
At 3:10 PM +0100 8/9/01, Dave Watkinson wrote: >You'll note that both step requires a return, which general means a >subquery, I believe for MySQL to hanle subqueries you actually have >to perform to separate queries. If your not using MySQL then you >could write a statement such as: > >SELECT empid FROM emp_cont WHERE (SELECT COUNT(empid) FROM emp_cont >GROUP BY empid) [note statement may need tweaking since I use MySQL >and can't test it] > >that didn't work > Yeah, that one shouldn't work on MySQL. MySQL doesn't allow subqueries like this. But here's another option you could use: SELECT empid, COUNT(empid) FROM emp_cont GROUP BY empid HAVING COUNT(empid) > 1 I tend to use either group by or having, so forgot that having can be used to limit a group by. But this statement should work for you, as well. Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . applying technology to transform .
RE: [PHP-DB] Another SELECTING problem :-(
At 12:52 PM +0100 8/9/01, Dave Watkinson wrote:
>
>I have (many many many) tables ... two of which are linked by a third,
>so that there can be a one-to-many relationship. What I'd like to do is
>find which ids from table 1 have more than one relation in table 2, via
>table 3.
>
>I've tried this...
>
>SELECT empid, COUNT(empid) FROM emp_cont , employers, contacts WHERE
>emp_cont.empid = employers.uid and emp_cont.contid = contacts.uid
>and count(empid) > 1;
>
Your getting a lot of errors, because the statement has well a lot of
mistakes. Let's start near the beginning, you state:
SELECT empid, COUNT(empid) FROM emp_cont , employers, contacts
This statement should produce an error, since you didn't tell the
database which table to take empid from. A working statement would
look like
SELECT empid, COUNT(empid) FROM emp_cont or
SELECT emp_cont.empid, COUNT( emp_cont.empid) FROM emp_cont ,
employers, contacts
Either statement provides SQL with implicit instructions, as to which
empid to use and where.
The next major error is to use a Count function without the group by.
I'm not an SQL guru, but I believe whenever you use count you must
also use group by or having so adding a
SELECT empid, COUNT(empid) FROM emp_cont GROUP BY empid
will correct that.
Unfortunately, I think the larger issue is how your trying to find
your query. Your query did confuse me a little, so I could be wrong,
but technically speaking, if you are trying to locate an employee
with more than one record in employee contracts, then there's no need
to involve the contracts table within the query.
So say I have table Employees that contains empid, name, ssn. Then I
have table Employee Contracts that contains, empid, contractid.
Assuming that all empid(s) on Employee Contracts have a correlating
empid in Employees (which they will if the table was defined
properly) then the steps to get what you want are:
Step 1: Return a Count of empid
SELECT COUNT(empid) AS countid FROM emp_cont GROUP BY empid
Step 2: Return employees with more than one contract
SELECT empid FROM emp_cont WHERE countid >1
You'll note that both step requires a return, which general means a
subquery, I believe for MySQL to hanle subqueries you actually have
to perform to separate queries. If your not using MySQL then you
could write a statement such as:
SELECT empid FROM emp_cont WHERE (SELECT COUNT(empid) FROM emp_cont
GROUP BY empid) [note statement may need tweaking since I use MySQL
and can't test it]
If your using MySQL, I believe you need to return the results from
one, then use the results to find the next. Or you could use PHP to
cheat, and return all results, but only display the ones where the
count is greater than 1.
A subquery is best if your tables are large, returning 1000 results
to display 50 wastes time. But if you have under 200 employees, then
just use the SQL statement
$results=mysql_query (
SELECT empid, COUNT(empid) AS countid FROM emp_cont GROUP BY empid);
while ($row=mysql_fetch_array($results)){
$countid= $row["countid"];
$empid = $row["empid"];
if($countid=="1")
{
$display_employee .="";
}
else
{
$display_employee .="
$empid";
};
}
?>
Alnisa
--
.
Alnisa Allgood
Executive Director
Nonprofit Tech
(ph) 415.337.7412 (fx) 415.337.7927
(url) http://www.nonprofit-techworld.org
(url) http://www.nonprofit-tech.org
(url) http://www.tech-library.org
.
Nonprofit Tech E-Update
mailto:[EMAIL PROTECTED]
.
applying technology to transform
.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Temporary Tables in MySQL
Hi All:
I have a problem. I trying to create a temporary table from the query
results of the following statement.
$results=mysql_query ("SELECT CONCAT(membername, \" \", mission, \"
\", brief, \" \", city, \" \", state, \" \", programs) AS searchable,
memberid FROM members") or die("DIE! DIE! DIE!");
The query seems to work fine by itself using phpmyadmin, returns a
results table with two columns memberid, and searchable (note the \
must be removed if typed directly into phpmyadmin)
I want to use these results to create a new temporary table with two
columns memberid and searchable. But the book I'm referencing states
that you can create a table from SELECT results in one step is either
lying, or I'm doing something very wrong, because when I format the
statement like:
CREATE TABLE ezsearch SELECT CONCAT(membername, \" \", mission, \"
\", brief, \" \", city, \" \", state, \" \", programs) AS searchable,
memberid FROM members
I get massive errors.
I'm reading MySQL from New Riders, but I can't seem to figure this
out based on the information they've provided.
Alnisa
--
.
Alnisa Allgood
Executive Director
Nonprofit Tech
(ph) 415.337.7412 (fx) 415.337.7927
(url) http://www.nonprofit-techworld.org
(url) http://www.nonprofit-tech.org
(url) http://www.tech-library.org
.
Nonprofit Tech E-Update
mailto:[EMAIL PROTECTED]
.
applying technology to transform
.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
