Re: [PHP-DB] Help with connecting PHP/MySQL to MS Outlook
Chris, Does anyone know of a way to retrieve MS Outlook data via PHP? I am creating an intrant for my company and want to grab data from the MS Oulook contact list on the server to using in my PHP/MySQL intranet project. Any help would be most appreciated. Rather than using PHP to go 'into' Outlook, doesn't OL have an export function you could use to put it into some mid-step file format and then read that into PHP? Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php/mysql problem
Hi Stefan,
i've a problem with php and mysql.
a mysql-server is running on my server on that i can access from
'outside'.
but, if i want to connect to the server with a 'local' php skript (running
on the same server), mysql refuses the login ('acces denied for user
[...]'). the problem is that the login i use in the php skript is exactly
the same as the one i use for connecting from 'outside'.
another strange thing is that the php skript can connect to other
mysql-dbs
on other servers...
is it a config-problem ? i've no idea anymore...
you@remotehost is not the same (MySQL user) as you@localhost!
Check out the MySQL manual's page on user privileges and access control.
Regards,
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Formatting txt from a database
Lisi,
To save reinventing the wheel would strip_tags() be any use to you? (see
manual)
=dn
I am retrieving text stored in a database, and I want to strip out any
HTML
tags and then only display the first 30 characters with ... to show the
user that there is more text to view.
My problem is with stripping out HTML tags, the code is stripping out the
text enclosed within the HTML tags as well.
Here is the code I am using:
$pat = {1}.+{1};
if (ereg(text, $type)){ //if the field type is text look for HTML
tags to remove
$content_array[$c] = ereg_replace($pat, '', $content_array[$c]);
$content_array[$c] = substr($content_array[$c], 1, 30);
$content_array[$c] = $content_array[$c];
}
echo $content_array[$c];
What should the correct $pat be?
Thanks,
-Lisi
--
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] Help please
SpiderWebb, One posting to one list is enough... I dont know if this is possible in PHP (Newbie) im working on a project where each product has 3 diffierent prices depending on the amount sold so say for example 1- 100 price A 101-299 price B and above 300 Price C. What I need to be able to do is increment an mysql database field each time an =possibly an AUTO_INCREMENT field is what is needed here item is sold then look at that field to decide which price variable to write to the price field of the database. Could someone point me in the right direction where I could solve this or to someone who could =when you say database twice, can I assume you are talking about two different tables? =if the user has identified the product, then presumably (s)he has also mentioned an order quantity. Thus the easiest thing to do is to query the whole price row from the tbl and make the price-rate choice using your program logic. However it would likely be more efficient to have MySQL do it but to comment we would need to see the tbl schema. =hope that gets you started, =dn PS: there are many tutorials available covering the PHP/MySQL combination. Check with the product home sites and follow links to likely sources. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] The noobest of questions...
Hi Brett,
[please keep conversations on the list - someone else might have helped you
more promptly - others might benefit from following the conversation]
This is how I have the code written:
if ($num_results 0){
fopen(http://www.vcathletics.org/Misc/AdminMenu.htm,r;);
}
If I go to that link directly, it works. But command above just loads a
blank page into the frame. I know its working, because if $num_results is
not 0, I get the error echo.
Firstly, do you prove the contents of $num_results to be sure that the
then-clause is actioned?
Secondly fopen returns a value which is resource for use in later f*()
function calls - or which indicates that the open failed for some reason.
Please refer to: http://uk2.php.net/manual/en/function.fopen.php
fopen() only opens the file, or in this case web page. It is thereafter
necessary to read the file (several functions available) - and to fclose()
it later...
You talk about loads a blank page into the frame. Do you want to read the
contents of the web page with a view to further processing (my example: read
the page and locate any links - to verify 'continuity'), or do you want to
'read' the page to present it within your output, eg one frame within a
frameset (in which case fopen() etc is probably not the best route)?
Regards,
=dn
Brett,
Works for me!
Show your code,
=dn
How do you load a standard web page from PHP.
I thought, from what I read, that is was with fopen() ... but its not
working for me.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] More Apache Installation problems
Alex, Congrats at getting Apache off the ground. Lots of advice here about hardware, but... Are both your WS and Svr Windows boxes? If so, which OpSys? If you want both to share access to the Internet, consider modem sharing or ICS (Internet Connection Service - IIRC - M$ not v.generous on advice about this, except 50,000 repeat-pages telling you how to get it going). If the problem is simply getting the two machines to 'see' each other, eg for in-house web serving only, then the issue is one of DNS or WINS naming. Which do you use/run a server for? If the answer is basically neither, then we should introduce a static naming system for your two machines (do NOT use this method in conjunction with ICS!): - use WinExplorer to burrow down to C:\WINNT\system32\drivers\etc (YMMV by OpSys) - use Notepad to open hosts (NB no filetype/extension or 'dot'). - you should see lines such as: 127.0.0.1 localhost - copy-and-paste that line to a new line below - alter the IP address to the appropriate 192.168.1.n (different on each machine) - alter the localhost to a suitable network name for each machine, eg Fred and Barney - repeat on the other machine (IPaddr and name must be unique/different) - reboot both machines - use DOS box from one to ping the other machine's IPaddr, then networkNm - repeat from other machine - use IE and try both IPaddr and networkNm in Address: field should now work (pre-requisite: holding your tongue correctly) NB have assumed that TCP/IP is up and running on both boxes NBB by repeating the localhost line copy, using the same 192... IPaddr but changing the networkNm, you can add VirtualHosts to your Apache config on that particular machine, ie have more than one named 'web site' under the one server. NBBB the host files are static and easy to maintain in this scenario, but quickly get out of control in a corporate environment - which is why we would then use a WINS and/or DNS server! Hope it helps, =dn - Original Message - From: Adam Williams [EMAIL PROTECTED] To: Alex Francis [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, December 09, 2002 8:53 PM Subject: Re: [PHP-DB] More Apache Installation problems what you need to do is have 1 nic going directly to your cable modem using a cross over cable, and then with the 2nd NIC in your WS plug it into the hub along with the web server box, or ditch the hub and connect the WS to the web server with a cross over cable. Adam On Mon, 9 Dec 2002, Alex Francis wrote: Adam, You are correct. I had two NICs, set up exactly as you describe, but kept getting problems (I would lose my internet connection and have to reboot to get it back. I think I will try againand if that fails, buy a cable modem router. Adam Williams [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... No I think he has his WS with a real IP from his ISP and then his web server plugged into the same hub as his WS but with 192.168.1.1. He'll either need to get a 2nd NIC and give it an ip like 192.168.1.2 or get a cable modem router. Or change his WS to linux and add eth0:0 with 192.168.1.2 :) Adam On Mon, 9 Dec 2002, Gene Dymarskiy wrote: I am a bit confused. Your WS is connected to cable modem and gets public IP assigned by your ISP. Your WS is also connected to a hub shared with your web server. does it mean you have two network cards, one connected to cable modem, and the other to the hub? -Original Message- From: Alex Francis [mailto:[EMAIL PROTECTED]] Sent: Monday, December 09, 2002 2:20 PM To: Gene Dymarskiy Subject: RE: [PHP-DB] More Apache Installation problems I need to have a server assigned IP address on my workstation for my cable modem. -Original Message- From: Gene Dymarskiy [mailto:[EMAIL PROTECTED]] Sent: Monday, December 09, 2002 8:06 PM To: Alex Francis; [EMAIL PROTECTED] Subject: RE: [PHP-DB] More Apache Installation problems I'd check IP address of your workstation. make sure both the WS and the server are on the same subnet. Your WS should have 192.168 address, too. good luck -Original Message- From: Alex Francis [mailto:[EMAIL PROTECTED]] Sent: Monday, December 09, 2002 2:00 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] More Apache Installation problems It seems like a network problem. I can access the network through windows network neighborhood but can't ping it, any suggestions? Gene Dymarskiy [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... make sure both your server and workstation can see each other. try pinging the server: ping 192.168.1.1 -Original Message- From: Alex Francis [mailto:[EMAIL PROTECTED]] Sent: Monday, December 09, 2002 1:39
Re: [PHP-DB] Update Query Help...
SCOTT, I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. NOW() is a function and should not be part of a quoted string. =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Query Help...
SCOTT, The list's crystal ball filter is down for maintenance. Show us the tbl schema and the debug print of $update. Then we won't be firing blind! =dn I understand that the column name does not normally need quotes, but without quotes on the column name I get a mysql error message about no column named 'id'. Unfortunately, I cannot even get the UPDATE statement working outside of PHP through an direct SQL statement. Everything looks like it should work, but the affected columns is always zero, and obviously the atime column is not getting the timestamp. This is quite frustrating. Thanks again for the help. Hopefully someone will come up with something to help me get this working. -Original Message- From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Try: $update = UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- 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] Apache, PHP and MySQL
Alex, Sorry, I cant find this Action line. =can't find it where? It's on the page number that I quoted for WT's book (unless there's a new edition out?). If it's not in the conf file then it must be added - review p799 and around - they have a section about setting up Apache in one place, and then later when talking about setting up PHP they come back to making additions to Apache's conf file - some possibility for confusion there if you think 'been there, done that'... If you mean the DLLs being copied to the Windows\System directory, I have done that. =good stuff Is there a better tutorial for installing Apache and PHP, and if so should I try to uninstall and start =there are many tutorials around. On the PHP site there is a page of links. Pick one, eg PHPBuilder, and then scout around for what you want. Alternatively, there's been plenty of discussion here on the lists (probably more on PHP-General and PHP-Win, cf this one PHP-DB = really for database-related topics) so a charge through the archives will unearth treasures. again. I could even reformat if necessary - all my backups on the server are on a separate hardfile. =totally unnecessary to reformat. =have you tested 'pure Apache' by placing an HTML file in the/an appropriate folder and asking for it to be served through Apache (cf through the file system) - and did that work? =from this/the wrong end of the telescope, it sounds more like a conf issue for PHP than anything else (calling for refinement not brute force!) I am running Apache 1.3.24 for Win32 =a good vintage, if not the latest nouveau. =if you follow WT's instructions and it still fails, come back with the relevant snippets from the conf file and we'll work from there. =dn Dl Neil [EMAIL PROTECTED] wrote in message 142701c29f16$204ba340$c900a8c0@jrbrown">news:142701c29f16$204ba340$c900a8c0@jrbrown... Alex, I am trying to set up a test server to test updates before changing my uploaded pages. I have running Windows 98. I have Apache installed and running, and thought I have PHP installed properly. (Installed it as Welling and Thomson). However when I try to run my test.php file which should give phpinfo I get a message telling me that I am downloading a file from localhost. I presume Apache is not recognising the file type. I have added .php to the AddType application section of httpd.conf Since WT published a ClearModuleList has been added to the Apache conf file. Make sure that your brave addition doesn't get 'undone'. (shouldn't there be an Action line too? p799) Also, you aren't running Apache 2.n.n are you? =dn -- 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] The noobest of questions...
Brett, Works for me! Show your code, =dn How do you load a standard web page from PHP. I thought, from what I read, that is was with fopen() ... but its not working for me. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Return 1 instance of each unique record?
Hi Doug, How do I get MySQL to summarize a query so that I receive only 1 instance per similar record. I.E. if you have 10 records with name Smith and 5 records with Barney, etc, that it would return 2 records instead of 15. Try the MySQL DISTINCT feature. =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Single occurance of data
Alex, I have two tables in my database with a department name. One table (department), the name only appears once and it includes all departments in the school. In the other table, (courses) department can appear several times, but not all departments will be in the table. I used the following code to try to get the names of the departments from the courses table, but unfortunately I get all occurences of the department. Can someone help me get ony one occurrence of each department from the courses table. $query = select departments.department, courses.department from departments, courses where departments.department=courses.department; $result = mysql_query($query); Check out MySQL's DISTINCT modifier. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Connecting to remote db securely
I didn't realize that, is this discussed in Paul DuBois' book? Or, can you =not book, but books! =MySQL (New Riders) discusses network access/security in Chap 12, but Jason's solution is also discussed in Chap 11. Unfortunately this book is getting a bit old when it comes to these more advanced topics. =I haven't managed to get my hot and sweaty paws on Cookbook (O'Reilly) yet, but have hope that recently introduced security/advanced features will be included... =Smile nicely and maybe Paul will give the new book a plug!? =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Deleting topics older than 2 hours?
Leif, Sounds to me like you didn't understand the question. The table =two sides to every story: either I'm too thick to understand, or you didn't explain sufficiently - or both... see problem with necessarily open-ended answer given below and consider... structure is something like: topics_ | id | forum | author | subject | posttime | - _posts | id | topic | author | message | posttime| - The problem is, if I run a query like (I know the query is probably wrong, I'm just writing it now): delete from topics where posttime + interval 2 hours now() I would leave orphan posts inside of that topic. I'm not sure how (or if it's even possible) to delete the posts as the topic is deleted. =some RDBMSes implement referential integrity/a 'chaining process' built from key-dependences, and thus when a row is DELETEd from topics, any corresponding rows would be removed from posts as well. =others, eg MySQL, do not, and thus unless MySQL's last DELETE format option works sufficiently well, the best option might be two DELETE's: the first per your suggestion above and the second to remove all entries in posts that no longer join to a topics row. =Regards, =dn DL Neil wrote: Sorry Leif, but CrystalBall v2.6 is currently unable to help. The word WHERE features. Show us the money - or second choice: the (erroneous) code... =dn I'm trying to run a cron job to delete forum topics more than 2 hours old. I know how to do that, but the problem is also deleting the posts in the topic at the same time. Can someone shed some light on this, please? -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with Date
Rodrigo, Is there a way to convert a date 20/11/2002 to a Float number in PHP, since the date starts in 30/12/1899, just like the delphi treats the dates =Check out UNIX timestamps (but watch the valid date range), =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql_fetch_assoc question
Hey Graeme, $MemberID = mysql_query(SELECT ID FROM SiteMember WHERE Username = '$valid_user'); $array = mysql_fetch_assoc($MemberID); print_r($array); this will produce: Array ( [ID] = 3 ) All I want is the value 3 so that I can go ahead and insert that into the Lookup table. Any ideas how I can just extract this value? I'm also not 100% clear on what mysql_fetch_assoc does, can anyone explain it? mysql_fetch_assoc() takes the response from MySQL (the query resultset) and hands it to PHP as an associative array. This array only consists of one element: $array['ID'] - which in your example has the value 3. So echo $array['ID']; or similar use will give you what you seem to be asking for. Please read the manual: mysql_fetch_assoc() and the whole appendix on MySQL functions array processing and associative arrays Of course if you are resolving the value (3) in order to submit that to another query (Lookup table), then you should be looking at relational database methodology and table joins! Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AUTO_INCREMENT problemos
Hi simon My table has an INT column named 'member_id' which is set to AUTO_INCREMENT. It works very well but I have inserted and deleted some test rows during the development stage and now my incremented numbers have jumped ahead. The deleted rows (their member_id values) have not been forgotten it seems and the DB is using them still to evaluate the next number. Assuming we're talking about MySQL, ALTER TABLE will do the biz: http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] db questions and newbie...
Matt, I've put this back on the list because other people are smarter than me/can reply more quickly! (and I hate people who pretend to have 'the answer' when they're plainly out of their depth/range of experience - so...) I don't think that I've managed to completely understand where you are heading with this. Possibly because we have different (programming language) backgrounds/application histories. I don't understand why if $varsused is already a list with comma separators that you want to use the | instead, when CSV is so widely used/understood/available. Explode/Implode work to communicate between a string and an array. Perhaps string replacement functions or regular expressions would meet your needs more appropriately? The (annotated) online manual has a number of examples and all manner of ideas in the user contributed comments. The manual's definition section also suggests looking at other 'similar' commands, eg the explode page offers See also preg_split(), spliti(), split(), and implode(). I recommend that you work your way through the string and filesystem function chapters/appendices of the manual to see what catches your attention/strikes a chord with your preferences/history/intentions. When working with a 'new' language it is usually a mistake to try to make it work the way your 'old' one did - also expect your ratio of reading references to writing code to change markedly! Regards, =dn I looked through the documentation on implode and explode... it doesn't seem to give a good example. I want to assign the file name, vars and delimiter. I looked at this example: $pizza = piece1 piece2 piece3 piece4 piece5 piece6; $pieces = explode( , $pizza); $data = foo:*:1023:1000::/home/foo:/bin/sh; list($user,$pass,$uid,$gid,$gecos,$home,$shell) = explode(:,$data); But got all confused. Isn't there a piece of code that I can write so it will look like this: $filename = datafile.txt; $varsused = var1,var2,var3; $delimiter = | ; then the opposite of that to import/implode the records as well as to apply the filter? I know I'm new but I've used other programming languages where it's easier and more intuitive to import/export records and put filter on them. Thanks. -Matt Dl Neil wrote: Matt, Welcome to the PHP and MySQL communities! I have lots of questions and am new to the list... so alas I hope I'm posting to the correct area. In PHP and MySQL... can you import and export to flat files? =yes, check out the online PHP manual, appendix XXX, Filesystem Functions (http://uk2.php.net/manual/en/ref.filesystem.php) I want to export a series of vars, IE: Name, Age, State Bill 25 NY So that in a file, it will appear like so: Bill|25|NY =check out the online PHP manual by entering implode and/or explode into the (functions) search facility With one record per line, separated by a delimiter? And then import those record by being able to set a filter to sort all people from NY or all people that are 25 years old. =sounds like a job for an RDBMS rather than a flat file, but there you go... =check out the online PHP manual for string functions and/or regular expressions Also, I've been trying to playing around with PHP and MySQL. I haven't found a great tutorial. Anyone know of a good link? I'm comfortable somewhat with the myphpadmin to manage the mysql db, but other than that, I haven't found anything simple that goes indepth including: Sorting records IE... alphabetizing certain fields or sorting them by descending etc. =it sounds as if you need to learn SQL. There are a huge number of tutorials available on the web (...check out Google). Better yet - visit the MySQL site and follow the links to a Links page. This will direct you to a number of sites that offer helpful articles and tutorials. Similarly the PHP site will point you to many great resource sites. =Don't overlook the online MySQL manual's Chapter 3 which is a step-by-step tutorial. There are many books available which introduce MySQL or PHP or both together, and are most useful because you can read up the theory and then work through examples step-by-step. AND I haven't found much reference on DB locking to prevent file corruption. =check out the online MySQL manual (it has a search facility too) Any help to these issues would be great! =did you detect a theme? The manuals are really v.good! Sorry if it seems unhelpful to answer in this way, but help-yourself is the first law of the OpenSource jungle! =Regards, =dn -- Matt Zur [EMAIL PROTECTED] http://www.zurnet.com Need a Web Site??? - Visit... www.zurnet.com 1997 - 2002 - 5th Anniversary!!! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] full text search and how to underline keyword in results
andy,
I find the PCRE regex option easier to work with. Herewith a solution which
takes care of case (without changing the original text), and without regard
to spaces or other characters (including the first and last characters of
the string - which you didn't mention/identify as potential problems
earlier!).
You will observe the 'manufactured' the text string and that the found
'needles' are post-processed only by surrounding them with ampersands
instead of your font tags - to prove that the regex works. Trust it suits.
$RegExIn = Singapore singapore singapore. Singapore. singapore;
$RegExPattern = |(singapore)|i;
echo brReplaced: . preg_replace( $RegExPattern, $0, $RegExIn ) .
~;
Regards,
=dn
for ($i=0; $i count($keywords); $i++) {
$blurb = eregi_replace( .$keywords[$i]. , font
color=\#FF\.$keywords[$i]./font , $blurb);
}
great, thats a first success and works fast.
There are just 2 things which fail on this function:
1. Problems with full stops. A word with a full stop will be ignored (e.g
singapore. will not be found if you search for singapore)
2. If you search for singapore and there is a hit Singapore (notice the
capital!) will be replaced with singapore in the text.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] form validation question - regex not working
Chip,
It's not so much off topic as a perennial topic - see archives and many
tutorial articles.
When you've worked out the answer to this question, please try out your
algorithm on my (or any other 'foreign') address. Then consider the imminent
introduction of further TLDs, eg .info (four characters), .family, etc.
Regards,
=dn
PS Sorry, can't help with POSIX RegExs - I confuse myself without any
outside assistance using the PCRE variants.
I hope this isn't too far off topic -
I have a regex for validating email addresses -
if (empty($useremail) || !eregi(^([A-Za-z0-9\.\_-])+@([A-Za-z0-9\_-])+\.
([A-Za-z]{2,3})+$, $useremail))
Notice the {2,3} which is supposed to limit the last part to 2 or 3
letters, but I have been testing this and
it allows as many letters as I put in there, but not 1 only.
What's wrong?
Also, when using eregi do I need to specify A-Za-z or just a-z, since it
is
case-insensitive?
--
Chip Wiegand
Computer Services
Simrad, Inc
www.simradusa.com
[EMAIL PROTECTED]
There is no reason anyone would want a computer in their home.
--Ken Olson, president, chairman and founder of Digital Equipment
Corporation, 1977
(They why do I have 9? Somebody help me!)
--
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] form validation question - regex not working
Jason, Concur with your advice, but was also amused by its juxtaposition with your sig file philosophy... =dn [snipped] I have a regex for validating email addresses - You're strongly advised not to write your own regex for validating email /* The most important things, each person must do for himself. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] form validation question - regex not working
Regarding the good sense or otherwise for checking email addresses: This is from a php/mysql book... If it was from page 115, then please turn over to page 116! Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] form validation question - regex not working
Regarding the problem you are actually asking about:
if (empty($useremail) || !eregi(^([A-Za-z0-9\.\_-])+([A-Za-z0-9\_-])
+\.
([A-Za-z]{2,3})+$, $useremail))
Notice the {2,3} which is supposed to limit the last part to 2 or 3
letters, but I have been testing this and
it allows as many letters as I put in there, but not 1 only.
What's wrong?
Please be aware that I use PCRE not POSIX, but IIRC the last phrase says:
([A-Za-z]{2,3})+$
any upper or lower case letter,
repeated twice or three times,
and that appearing zero or more times, !!!???
appearing at the end of the string.
Remove the + immediately before the terminating $.
Regards,
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Ranking in MySQL
David, I have a MySQL table called images, each with a rating from 1-10. The column is a decimal(10,2). For simplicity's sake, let's say i have the following: CREATE TABLE images ( image_id int not null auto_increment, image_path varchar(255) not null, rating decimal(10,2) not null, primary key(image_id) ); What I'm looking to do is be able to pull from the database the ranked position each image is in. I.e. this image is ranked 70 out of 121 =SQL has no ranking method. However you can (1) select data/rating values out from the table, (2) count how many rows/ratings appear in the table, (3) sequence the result set by rating to rank them. =SELECT data from the table and have it sequenced according to the value in rating: SELECT ... ORDER BY rating DESC; =the DESC is because ranking usually means highest-first! =When the record/resultset hits PHP, you can use MYSQL_NUM_ROWS() to find the number of rows returned from the database - and answer the this image is ranked ... out of ??? question. =Now you can run a FOR loop, to extract each row from the result set (from 1 to 'num_rows'). Each cycle through the loop, the for loop counter will answer the this image is ranked ??? out of ... question. =Ok? =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] time field query problems.
Steve, For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. If this cannot be done in a query, whats the best way to compare time in php? The best way to compare time in PHP is to use the MySQL RDBMS that is managing/retrieving the data for you. Recommendation 1: do not use a Time field (you did mean the back 'half' of a Date-time field didn't you?). Because you are (apparently only) using this field to temporarily lock a row, the value is only ever used for computation (cf display). A Timestamp field is best for computation - a Time field for presentation. Consider also storing such data as an integer field or beware the automatic update feature for Timestamp fields. Recommendation 2: re-consider the (default) use of NULL - this may be the root of the question you're asking: what if the row has never been 'locked' and attempt the (above) SELECT? (then the last comparison clause would be illogical) If the default were zero (0 or 00:00:00) and the retrieval logic updated slightly, things should be less complicated. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Time Allocation Issue
Jefferson, I'm writing a web application in PHP to do referee scheduling for soccer games. For each game (a single row in a table) I have a cell for referee. The cell stores an integer that I can do a join on with another table of people. I need a way to prevent people from being able to schedule themselves for multiple slots at the same time. (All I care about is start time, for now I'm going to ignore the issue of a game ending after the next one has started.) I have tried doing just a unique index on referee, date, and time but the issue is I use a 0 to indicate that the slot is open. Because of that I don't see a way to do it natively in mysql as such each time I do an insert I think I am going to need to do a select right before to make sure there no conflicts. Is there any better more efficient way to do this? Perhaps natively in mysql. TIA Speaking for referees everywhere, I'd say not to bother with 'time' because I wouldn't want to cope with more than one 90-minute game in a day - but perhaps you're working on rapid-fire short-game tournaments or somesuch... There are two issues here: firstly has a referee been assigned to control each/every game - or does this (one) game have a referee assigned to it? Secondly, when a referee is assigned, is (s)he in fact 'available'. Sounds like we should be using a project planning package! The first question is answered by SELECT gameId FROM games WHERE refereeId = 0; and/or SELECT refereeId FROM games WHERE gameId = ?; The second 'fails' if you can SELECT gameId FROM games WHERE refereeId = ? AND gameDate = ? AND gameTime = ? (ie no rows returned implies the referee is available, one row that (s)he is assigned, and more than one row that you have a scheduling snafu!) The two queries are logically quite separate. Will attempting to implement both aspects in a single query actually help your system? MySQL: Insofar as your realisation that games occupy time slots 'from' and 'to' moments in time, you might like to check out the BETWEEN comparator. Many consider it good practise to 'check' with a SELECT before performing an UPDATE or INSERT. Soccer refereeing: Please consider that hard-working referees deserve a rest between matches! Also that running another game is not the only reason why a referee might not be available to you. Hope this helps, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mail
Alex, That's the info in question! Here is the section from my PHP.INI [mail function] ; For Win32 only. SMTP = localhost ; For Win32 only. sendmail_from = [EMAIL PROTECTED] ; For Unix only. You may supply arguments as well (default: 'sendmail -t -i'). ;sendmail_path = I presume you mean the SMTP server set in my mail account. That is at my ISP. The SMTP= entry must be pointing at an SMTP server. Have you installed SMTP on the local box? In any case, others have had major problems using localhost, cf a machine name. FWIW, I recommend using the SMTP svr at your ISP. Because you can set up this line to be exactly the same as you have in your email package - and thus reduce variable factors, if you can get email OUT using your email client, you should be able to have high confidence that your PHP infrastructure will work! (so even if you have set up a local SMTP server, I'd get going on the ISP, and once proven switch back in-house) Regards, =dn I'm still having problems with the mail function. I have installed PHP in a Windows 2000 test server. Is there something I should do to allow this function to work. I am a complete newbie and installed with the default settings. I get a Failed to connect Error when I call the mail() function. // retrieve values $row = mysql_fetch_array($ret); $email = $row[email]; $title = $row[title]; $comments = $row[comments]; $title = $row[title]; mail($email, $title, $comments); Mail is controlled by settings in the php.ini file. Please post the [mail function] section (changing any confidential names to protect the guilty). Setup for Windows differs from *NIX - hence confusion in many tutorials/books... this matter is frequently discussed - see archives. Regards, =dn -- 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] Dates in MYSQL
Hi Alex,
You seem to be struggling with both MySQL and PHP at the same time! Must
be generating high frustration levels. Have you got yourself a
tutorial/book to work from?
I'll go with Mike's suggestion, although I'd go for a 'one stop shop' in
MySQL - as long as we get the required result it's right!
The latest problem is a mix-up of quotation marks - originally you
enclosed the query within single quotes and the SQL/PHP parameters in
double quotes. Now you have both the same (to accommodate the PHP
variable $todaysdate). Change the DATE_FORMAT parameter quotes to
singles.
Other pieces of (unsolicited) advice: make sure that you have error
checking and debugging in place FIRST.
Surrounding this one query I would have a debug print of the query
string. (1) to show me what has been constructed by PHP (syntax
checking), and (2) so that I can copy-paste it out of my browser session
and into a MySQL admin package or command-line query (SQL logic
checking).
Also add a call/calls to check that MySQL returned a valid result to
PHP, how many rows were found, etc. (best to direct you to the online
manual than to repeat that load of info).
Keep on trucking,
=dn
Mike,
I have just tried it again (that was the first way I tried to do the
query)
and get the error
Unknown column '$todaysdate' in 'where clause'
--
Alex Francis
Cameron Design
35, Drumillan Hill
Greenock PA16 0XD
Tel 01475 798106
[EMAIL PROTECTED]
http://www.camerondesign.co.uk
This message is sent in confidence for the addressee only. It may
contain
legally privileged information.
Unauthorised recipients are requested to preserve this confidentiality
and
to advise the sender
immediately of any error in transmission.
Mike [EMAIL PROTECTED] wrote in message
001b01c1e658$6f483c00$[EMAIL PROTECTED]">news:001b01c1e658$6f483c00$[EMAIL PROTECTED]...
Alex,
enterdate text NOT NULL
eventdate text NOT NULL
First, I think I would change the above to a date or datetime or one
of
the
other date time fields used by mysql instead of text fields.
Second I don't think you really need to use TO DAYS and now. This
is what
I
do.
use php date function to get the current date,
$todaysdate = date(Ymd);
then do the query,
$query = SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M
%Y) as
evdt FROM notices WHERE eventdate = $todaysdate ORDER BY
eventdate;
Mike
- Original Message -
From: Alex Francis [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, April 17, 2002 5:26 PM
Subject: Re: [PHP-DB] Dates in MYSQL
Tried the following as suggested - Still no dates showing
$query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D
%M
%Y)as
evdt FROM notices WHERE TO_DAYS(eventdate) = TO_DAYS(now())
order by
eventdate';
$result=mysql_db_query($dbname, $query, $link);
if (!$result) { echo( mysql_error()); }
else
while ($row = mysql_fetch_array($result))
$entername = $row[entername];
$eventdate = $row[evdt];
$eventheading = $row[eventheading];
$id = $row[id];
Database table notices as follows
id int NULL autoincrement
entername text NOT NULL
enterdate text NOT NULL
eventdate text NOT NULL
eventheading text NOT NULL
eventbody text NOT NULL
--
Alex Francis
Cameron Design
35, Drumillan Hill
Greenock PA16 0XD
Tel 01475 798106
[EMAIL PROTECTED]
http://www.camerondesign.co.uk
This message is sent in confidence for the addressee only. It may
contain
legally privileged information.
Unauthorised recipients are requested to preserve this
confidentiality
and
to advise the sender
immediately of any error in transmission.
Dl Neil [EMAIL PROTECTED] wrote in message
0b8401c1e62e$03072ad0$0600a8c0@jrbrown">news:0b8401c1e62e$03072ad0$0600a8c0@jrbrown...
Hi Alex,
Got my select statement to work as follows:
$query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) =
TO_DAYS(now())
order by eventdate';
=well done!
However the TO-DAYS calls do seem a bit OTT.
Please post the schema for tbl:notices - specifically the
datatype for
eventdate.
Now trying to get the date more user friendly and tried:
$query = ' SELECT id, eventheading, DATE_FORMAT(eventdate,
%D %M
%Y)FROM
notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by
eventdate';
...
When I echo ($eventdate) I get nothing. Not even an error.
The problem is that
$eventdate = $row[eventdate];
(which doesn't need the around the whole of the RHS (some
would put
them around eventdate), BTW)
doesn't tie up with:
DATE_FORMAT(eventdate, %D %M %Y)
Recommend you change them to:
DATE_FORMAT(eventdate, %D %M %Y) AS evdt
and
$eventdate = $row[evdt];
Re: [PHP-DB] Dates in MYSQL
Hi Alex, Got my select statement to work as follows: $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by eventdate'; =well done! However the TO-DAYS calls do seem a bit OTT. Please post the schema for tbl:notices - specifically the datatype for eventdate. Now trying to get the date more user friendly and tried: $query = ' SELECT id, eventheading, DATE_FORMAT(eventdate, %D %M %Y)FROM notices WHERE TO_DAYS(eventdate) = TO_DAYS(now()) order by eventdate'; ... When I echo ($eventdate) I get nothing. Not even an error. The problem is that $eventdate = $row[eventdate]; (which doesn't need the around the whole of the RHS (some would put them around eventdate), BTW) doesn't tie up with: DATE_FORMAT(eventdate, %D %M %Y) Recommend you change them to: DATE_FORMAT(eventdate, %D %M %Y) AS evdt and $eventdate = $row[evdt]; See how the SQL and PHP tie together? Now you need to reconsider the other $row[] assignments. =Keep it coming! =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL to Email and Line Breaks
Jeff, I have a form where someone enters the body of an email message into a textarea part of a form to be sent out to a list. Then a script retrieves the body and sends it via email. However I can't get line breaks to show up (I'm using MS Outlook) even if I enter the \n into the textarea form and those \n show up in the database data. I must be missing something. Thanks for any help. Line Breaks across different OpSys: *nix: newline character Mac: carriage return character Windows: both CR and LF LF = Newline/Line Break/Line Feed = ASCII chr(10) CR = Carriage Return = ASCII chr(13) Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: MySQL to Email and Line Breaks
Sure he could (and it might look a lot nicer if he did...), but: 1 he said he doesn't want to, 2 ASCII text can also be formatted (with line breaks, at least), 3 using HTML encourages email bloat, and 4 some email clients don't render HTML Regards, =dn Hey, couldn't you just send the text as HTML and it would retain any formatting? Bob Jeff Oien [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... I have a form where someone enters the body of an email message into a textarea part of a form to be sent out to a list. Then a script retrieves the body and sends it via email. However I can't get line breaks to show up (I'm using MS Outlook) even if I enter the \n into the textarea form and those \n show up in the database data. I must be missing something. Thanks for any help. Jeff Oien -- 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] MySQL to Email and Line Breaks
Jeff, Is there something about the evaluation of \n in different types of strings, eg single quotes/double quotes? (I don't know the answer)... FWIW I don't like (perhaps, I don't trust) the \r\n type of short-cuts. Herewith some sample code: ... $LinkText= Requested data can be found at: $source . CRLF . CRLF . Run date: $DocDate . CRLF; ... The constants are defined in an Include/Require file: ... define( 'LF', chr( 10 ) ); define( 'CR', chr( 13 ) ); define( 'CRLF', CR.LF ); ... It works for me! Regards, =dn How do I get it to do that? I tried replacing \n with \r\n but it's not even finding any \n. This is really puzzling me. If I copy the data from the database field using MySQL-Front and paste it into a word processor it will have the line breaks retained. But what is it in the data that shows these breaks? I can't find any \n in the data. Then when it's sent via email of course there are no line breaks displayed. Jeff Oien Hiya, If I remember correctly, you much send both \r and \n to terminate a line on an e-mail, regardless of the platform. This should include the header aswell. At 12:00 14/04/2002 +0100, DL Neil wrote: Jeff, I have a form where someone enters the body of an email message into a textarea part of a form to be sent out to a list. Then a script retrieves the body and sends it via email. However I can't get line breaks to show up (I'm using MS Outlook) even if I enter the \n into the textarea form and those \n show up in the database data. I must be missing something. Thanks for any help. Line Breaks across different OpSys: *nix: newline character Mac: carriage return character Windows: both CR and LF LF = Newline/Line Break/Line Feed = ASCII chr(10) CR = Carriage Return = ASCII chr(13) Regards, =dn -- 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 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] Contact Database php_mysql
Russell, I am contemplating a Historical Database I barely can spel redundant let alone normalize so I am handicapped. The aim is to enter a name only once. I am studying linking tables. In the [Contact] idContact First_Name,Last_Name,Middle,Suffix,Full_Name,idCity,idState,City_State,i dZip_Code, Address_1,Address_2, [City] id Cityname id (al, ar etc) Statename [ZipCode] id Zip_Code Comment please, I note two previous responses and agree with the basic premise that if you are not going to use the three supporting tables for some information-processing purpose they are overkill, ie if the city, state, and zip data is purely for information-labeling forget it. Similarly if your motivation is some sort of space-saving effort, eg idCity=123 takes less space than Minneapolis, and multiplied by dozens/hundreds of instances seems like a good plan; but what is the real cost of disk storage these days? However, if this motivation is based on spelling - or to be more constructive, consistency of spelling a given word (eg I have to think about some of the longer, repeating-character names like Minneapolis - with apologies to St Paul and all the other good residents of said fair city) then I will disagree with another correspondent and suggest that using the City table to construct the combo/list box in a data-entry form would do wonders for data-entry consistency and spell-checking - although at the expense of response time if the tables grow to be huge. Similarly State data-entry will definitely benefit because of the typing time saved by entering MS instead of M-I-S-S (oops, there's that spelling and character repetititititition issue again). The Zip code is unlikely to save space, and depending upon how 'historical' your data/enquiry, of only limited/recent use. The comment that replaying a five digit zip code with another integer is valid, however it is also parochial in that many non-US countries use postal/location codes that are not exclusively numeric. If 'history' spans more than 200 years the observation is that there will be no zip code to enter and the data will likely require an additional field: Country! At which point it is worth mentioning that State is a parochial term too - many countries don't use them/know their regional subdivisions by that term. I don't think anyone has mentioned City_State. What is the purpose of this field given that there are already two separate fields? You have gone to quite a bit of detail with the physical address and (normalising) breaking out repeating components into separate sub-tables, however what about the concept of a person moving about? Should there be allowance for the fact that an address was only good on a particular date or for a given period of history? Perhaps this is the purpose behind Address_1 and Address_2 (except there is no temporal component)? Why would you have one person and three addresses - and why in a single db row? Shouldn't there be three address rows (or however many are necessary) for each person - how would you otherwise construct a search based upon place, or place and time? There is a Suffix but not a Prefix, eg Sir or Captain. There is space for formal names, but not for nicknames, eg a lot of people are known by their initials, or some familial address, eg Doc (never play cards with him) - my buddy calls me Buckwheat for some reason beyond my understanding but presumably because he thinks I am a little... Hope this is a little...helpful, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Checking for 2 unique row contents
Hi Dave, I am trying to create a registration area that uses a nickname and email pair. I need both to be unique. So how do I that either do not already exist then if unique insert into db. if nickname exist then error = sorry... if email exist then error = sorry... else must be unique insert. I have been trying for hours know but cant get the twin check to work. Can get single check to work though. You do not mention which DBMS you are using but they must all offer something similar (I shall work with MySQL as an example). If you had posted your code we might have been able to work directly at that level to achieve the final tweak... Set up the db-tbl so that the nickname and email fields are both constrained as UNIQUE. You can now attempt to INSERT row data and MySQL will perform all the checks for uniqueness. After the (single row at a time) INSERT operation check mysql_affected_rows() and if it returns 1 then the INSERT failed (and assuming all else is equal) we'll assume because the data is not DISTINCT. If you do not need to know which of the two fields fails this is fine. If you need to know which field failed the uniqueness test, then precede the INSERT/UPDATE with: SELECT COUNT(nickname) AS NicknameTaken, COUNT (email) AS EmailTaken ... WHERE nickname=value OR email=value and then running a PHP IF( 0 ) across the two returned values will tell you that the data is non-DISTINCT and which field(s) are at issue. NB still follow the actual INSERT/UPDATE with an affected rows check if your tbl is not locked and multi-user access is allowed! Man ref: 6.5.3 CREATE TABLE Syntax (http://www.mysql.com/doc/C/R/CREATE_TABLE.html) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] orPRIMARY KEY (index_col_name,...) orKEY [index_name] (index_col_name,...) orINDEX [index_name] (index_col_name,...) orUNIQUE [INDEX] [index_name] (index_col_name,...) ... In MySQL, a UNIQUE key can have only distinct values. An error occurs if you try to add a new row with a key that matches an existing row. ... Man ref: 6.4.3 INSERT Syntax (http://www.mysql.com/doc/I/N/INSERT.html) Similarly 6.4.5 UPDATE Syntax (http://www.mysql.com/doc/U/P/UPDATE.html) applies if changing/updating values INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES (expression,...),(...),... ... If you specify the keyword IGNORE in an INSERT with many value rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted. If you do not specify IGNORE, the insert is aborted if there is any row that duplicates an existing key value. PHP man ref: http://uk.php.net/manual/en/function.mysql-affected-rows.php Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to get consistent UTC from gmmktime (w/o dst-offset) ????
BTW for all who are following this, Patrick has cleverly illustrated
that most of the northern hemisphere locations where summer time is
observed will 'spring forward' this weekend. Most of the southern
hemisphere locations that were in summer time did their 'fall back' last
weekend!
NB the two uses of the word most!
Hi Patrick,
References from the manual:
-
gmmktime -- Get UNIX timestamp for a GMT date
-
gmdate -- Format a GMT/CUT date/time
Identical to the date() function except that the time returned is
Greenwich Mean Time (GMT). For example, when run in Finland (GMT +0200),
the first line below prints Jan 01 1998 00:00:00, while the second
prints Dec 31 1997 22:00:00.
echo date (M d Y H:i:s, mktime (0,0,0,1,1,1998));
echo gmdate (M d Y H:i:s, mktime (0,0,0,1,1,1998));
-
The definition of the Unix Epoch is itself in GMT (and adjusted from
there to local time using the +/-TZ difference): the current time
measured in the number of seconds since the Unix Epoch (January 1 1970
00:00:00 GMT).
When playing around with UNIX epoch seconds it is important to keep them
time-zone separated. So try working your test backwards and putting in a
data/time and then asking for the GMT and the local timestamp values.
They will also be separated by the one hour/two hours.
Question answered, or in my haste to get to my dinner...?
Regards,
=dn
i'm storing events in a mysql-db, using epoch timestamps to pinpoint
the
exact date/time for an event.
so far, I have been using localtime, being aware that there are
inconsistencies
in the number of epoch-seconds, when DST flips on and off.
nevertheless, that
works fine as long as you stick to mktime() and date() for all the
date-math
as in mktime(0,0,0,$month,$day+$offset,$year)
but as soon as you leave php and have to do some calculation in
javascript,
you will encounter functions that behave differently on the pc and mac
platforms. so you have to revert back to the 'add 86400-seconds' to
calculate
the next day.
so I thought it would be nice to have all the timestamps as linear,
non-dst-epoch-seconds (UTC) and I went into the gmmktime() and
gmdate()
functions, assuming they would do the math in a linear second-based
timespace
(in GMT, without DST). That seems to be the case for the output of
'gmdate',
that remains correct, if you count up seconds across the DST boundary
(see
second example).
but it seems that there is no way to calculate the gmt-epoch for a
given
date using gmmktime without having to add/subtract the TZ manually.
gmmktime
will always take your local DST-settings into calculation and upon
conversion
back with gmdate, you will be off by the number of hours of your
timezone.
I thought gmmktime() would handle this as if the computer would be in
Greenwich
(TZ=0), which would then leave us with the two functions
encoding/decoding
consistently.
anyone having solved this properly?
-
print count days:br\n;
for($day=0; $day7; $day++){
$timgmt = gmmktime(0,0,0,3,28+$day,2002);
$timloc = mktime(0,0,0,3,28+$day,2002);
print $timgmt . = gmmktim epoch - . gmdate(D d.m.Y H:i I,$timgmt)
.
br\n;
print $timloc . = mktim epoch - . date(D d.m.Y H:i I,$timloc) .
br\n;
print BR\n;
}
count days:
101727= gmmktim epoch - Wed 27.03.2002 23:00 0
101727= mktim epoch - Thu 28.03.2002 00:00 0
1017356400= gmmktim epoch - Thu 28.03.2002 23:00 0
1017356400= mktim epoch - Fri 29.03.2002 00:00 0
1017442800= gmmktim epoch - Fri 29.03.2002 23:00 0
1017442800= mktim epoch - Sat 30.03.2002 00:00 0
1017529200= gmmktim epoch - Sat 30.03.2002 23:00 0
1017529200= mktim epoch - Sun 31.03.2002 00:00 0
dst on
1017619200= gmmktim epoch - Mon 01.04.2002 00:00 0 off by one
hour due to
DST
1017612000= mktim epoch - Mon 01.04.2002 00:00 1
1017705600= gmmktim epoch - Tue 02.04.2002 00:00 0
1017698400= mktim epoch - Tue 02.04.2002 00:00 1
1017792000= gmmktim epoch - Wed 03.04.2002 00:00 0
1017784800= mktim epoch - Wed 03.04.2002 00:00 1
-
print count seconds:br\n;
$baseepoch = $timloc = mktime(0,0,0,3,28,2002);
for($day=0; $day7; $day++){
$epochnow = $baseepoch + (60 * 60 * 24 * $day);
print $epochnow . = gmmktim epoch - . gmdate(D d.m.Y H:i
I,$epochnow) .
br\n;
print $epochnow . = mktim epoch - . date(D d.m.Y H:i
I,$epochnow) .
br\n;
print BR\n;
}
count seconds:
101727= gmmktim epoch - Wed 27.03.2002 23:00 0 consistent,
but off by
-1 (your TZ)
101727= mktim epoch - Thu 28.03.2002 00:00 0
1017356400= gmmktim epoch - Thu 28.03.2002 23:00 0
1017356400= mktim epoch - Fri 29.03.2002 00:00 0
1017442800= gmmktim epoch - Fri 29.03.2002 23:00 0
1017442800= mktim epoch - Sat 30.03.2002 00:00 0
1017529200= gmmktim epoch - Sat 30.03.2002 23:00 0
1017529200= mktim epoch - Sun 31.03.2002 00:00 0
1017615600= gmmktim epoch - Sun 31.03.2002 23:00 0
Re: [PHP-DB] a Count() ?
Hi Dave, I am trying to count how many product names in my db have the same category id and then show it ie: Catid 1 Product 1 Catid 1 Product 2 Catid 2 Product 3 Catid 3 Product 4 Catid 3 Product 5 Result would be Catid1 has 2 products Catid2 has 1 products Catid3 has 2 products I think it has something to do with the GROUP command but the mysql doc dose not make it clear how to achive this task. Code examples, pointers to web resources or any info thankfully received. Let's take it a step at a time. First of all assemble the SELECT to produce your first list: SELECT * FROM tblNm; then pull in the GROUP BY clause to collect the row-results together in some like-minded fashion. In this case you want to collect all or the rows pertaining to one category (ID) together. (you will need to be more specific about what in the manual is making you uncertain): SELECT * FROM tblNm GROUP BY Catid1; Oops! All of a sudden we only get one line for each CatId (and the rest of the columns produce fairly unpredictable data taken from only one of the rows with that CatId). Get rid of the * (all columns) and replace it with the CatId colNm. Now follow your instincts and check out COUNT() in the manual, and try something like: SELECT Catid1, count(*) FROM tblNm GROUP BY Catid1; As I said 'follow your instincts' and take it one step at a time: Code the simplest query first, then try making it more complicated by adding/amending one clause at a time, crafting the result until it suits your purposes... Let us know how you get on! =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] date problem
Rehab,
i have an input field in a form that accept date called $date and in
databse i made it of type $date so its defualt is -00-00
the problem is when i say:
if($date!=-00-00)
{do something}
he doesn't understand $date!=-00-00
Please copy-paste the actual error message.
Also, what is the CREATE TABLE schema for the date field?
Recommend showing us a little more of the surrounding code.
Have you tried adding a debug ECHO $date immediately before the IF
statement, to be sure what you're dealing with?
Please advise,
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PLEASE HELP !!!!!!!
Manual reference: http://www.php.net/manual/en/function.mysql-connect.php resource mysql_connect ( [string server [, string username [, string password [, bool new_link) Seeing it is not a web page you're connecting to, try removing the http://;. localhost has particular meaning within the IP conventions - if cgi.xyz.com is remote then only use that server's name and remove the 'local' designation, ie use the name from your computer's perspective, not the remote computer's perspective. Use PING to ascertain that you have the server's name expressed correctly. The default values are server = 'localhost:3306' which may be causing some confusion here. I recommend simplifying by leaving out the port number (3306) for now - MySQL knows that (hopefully) and thus the remote computer's RDBMS should be 'listening' on that port - if it's not, we'll have to get downer-and-dirtier... Let us know how you get on! =dn - Original Message - From: Beau Lebens [EMAIL PROTECTED] To: 'Inter-Media Webmaster' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 14 March 2002 00:21 Subject: RE: [PHP-DB] PLEASE HELP !!! try an IP // -Original Message- // From: Inter-Media Webmaster [mailto:[EMAIL PROTECTED]] // Sent: Thursday, 14 March 2002 6:00 AM // To: [EMAIL PROTECTED] // Subject: [PHP-DB] PLEASE HELP !!! // // // PLEASE HELP !!! // HOW I CAN GET CONNETCTION TO REMOTE HOST IN OTHER SERVER WITH // mysql_connect(); // e.g. // my php file is in www.abc.com/index.php // mysql server is localhost on cgi.xyz.com // // can i do something like this // // mysql_connect(http://cgi.xyz.com:localhost:3306;, username, // password); // // PLEASE ANSWER to [EMAIL PROTECTED] // // // // // // -- // 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] An interesting query that needs a different pair of eyes
Howdy John, Two things: firstly the stated objective and the sample query differ (WHERE division.leagueID???). Secondly I'm sorry that I'm short of time this morning, otherwise I'd try to develop/illustrate this for you... May I suggest that you employ the KISS principle: Start off by coding a simple join between the two tables to list every division and every team within it - the way that you relate these two tables is a key to the solution. Also doing that much will locate and correct one problem. Then try adding the GROUP, and later add back the existing HAVING clause. That should achieve the stated objective. Thereafter you may reconsider that you don't even want to have the IF() in there (I'm thinking, that you're thinking, that it will perform the table join - but then I might be thinking something that you're not thinking, and I think and you will also think, that such would be a thankless thing)... Let us know how you get on! =dn What I'm trying to do is display a list of division names assuming that each division has at least 2 teams associated with it. Let's start with the table definitions: table name #1: division | ID | leagueID | name | rank | | 1 | 1| Gold | 1| | 2 | 1| Silver | 2| | 3 | 1| Bronze | 3| | 4 | 2| Level 1 | 1| table name #2: team | ID | leagueID | divisionID | name | | 1 | 1| 1 | Vipers | | 2 | 1| 1 | Warthogs | | 3 | 1| 1 | Kings| | 4 | 1| 2 | Ducks| I created the following query: SELECT division.ID as curID, division.name, SUM( IF(team.divisionID = 'curID', 1, 0)) as teamcount FROM division, team WHERE division.leagueID = '1' GROUP BY division.ID having teamcount 1 This results in zero records. During some testing I found that, instead of trying to sum based on the IF(team.divisionID = 'curID', and instead used an actual value, like this: IF(team.divisionID = '1', that would produce results, but it would list every division name and the team count would be 3 in each row. I hope that was descriptive enough... Thanks in advance for your help! John __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.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] MySQL Result
Hi Chris
I have delete working perfectly by doing the following:
include(connectionstart.php);
mysql_query (DELETE FROM emaillist WHERE EMail = '$email'
);
However, how do I return a true or false flag whether the row was
deleted or
not? I basically need to know how to do a 0 or 1 I guess once the row
has
been deleted or if the specified email address wasn't found.
The manual is your friend
(http://www.php.net/manual/en/function.mysql-query.php)
There are a couple of reasons why a query/DELETE might fail.
Most command checking code failure occurs because people fail to
appreciate that the return-value from mysql_query() takes advantage of
PHP's loose (data) typing, and can be a boolean value sometimes, and a
resource 'pointer' at others.
The next 'hole' to fall into is the correctness of the syntax of the
query, or that the connection between PHP and MySQL has been broken/not
established - illustrated in the manual ref given.
The second 'level' of the game is (as you ask) to check that MySQL was
actually able to do what was instructed. For this you need
mysql_affected_rows() (manual:
http://www.php.net/manual/en/function.mysql-affected-rows.php)
Here's a particularly pedantic (but hopefully illustrative)piece of code
for you:
...
if ( DEBUG ) echo brQuery=$SQLquery~;
$bValidity = $MySQLResultSet
= mysql_query( $SQLquery, $MySQLConnection );
$iNumRowsDeleted = mysql_affected_rows( $MySQLConnection );
if ( MYSQL_DEBUG ) echo brResult=$MySQLResultSet~ Num
rows=$iNumRowsDeleted~;
if ( FALSE == $bValidity )
{
MySQLdbError( $MySQLConnection,
Error: MySQL DELETE syntax/semantic/privilege error );
}
return $bValidity;
- the DELETE is performed within a function
- DEBUG and MYSQL_DEBUG are script-global constants set/reset according
to taste
- MySQLdbError() logs any errors, in or out of user view, also according
to taste
- note that the query is error-suppressed and its result assigned twice
to vars of different types (by naming convention if not PHP construct)
- the function returns a 'worked' or 'didn't' (boolean) value
- other values are returned in the function parameter/argument list, eg
the number of rows deleted
- the mainline can call the function within an IF statement
- the IF-valid check can then proceed to inspect $iNumRowsDeleted to
ensure expected behavior
Other refs: a number of the tutorials linked from MySQL AB's site cover
this topic area.
Regards,
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] timestamp iin MySQL not compatible to the one in PHP???
Andy, So what is the proper function in PHP to convert the MySQL timestamp into a proper format like Sonday, Apr. 20th 2002? SELECT from SQL using UNIX_TIMESTAMP() Format for presentation in PHP using: string date ( string format [, int timestamp]) Regards, =dn I am playing around with the timestamp functions. I created a timestamp with mysql ( the row is timestamp) and do reformat this thing after selecting with php in the folowÃng way: $date_posted[$i] = strftime(%A, %d-%m-%Y %R, $date_posted[$i]); This always returns Tuesday, 19-01-2038 but the mysql timestamp says: 20020305211704 They return the time in different formats. Read the MySQL manual then read the PHP manual (or vice-versa). The MySQL timestamp is human readable. So in your example above: 20020305211704 == 2002-03-05 21:17:04 time() in PHP is the number of seconds since the Unix Epoch. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] WHERE clause
Jennifer,
Wondering do I have to have the WHERE clause in a select query?
$b=mysql_query( SELECT * FROM my_table) -can I use something like
this
or do I have to put WHERE in the statement?
Hate to answer a question with a question, but what happened when you
tried typing this query into the MySQL client? (a far faster solution
than waiting for someone on the list to get back to you!)
RTFM: 6.4.1 SELECT Syntax
SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC],
...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC]
,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name]
[FOR UPDATE | LOCK IN SHARE MODE]]
When you read these 'template' commands in the manual, if the
word/clause/construct is enclosed in square brackets, eg
[WHERE where_definition]
then it is optional. Thus the minimalist SELECT statement looks like:
SELECT select_expression,...
which will only work with some scalar expression, eg SELECT 2+2; or
SELECT month( '2002-03-04' ); so the minimum to work with a table is:
SELECT select_expression,... FROM table_references
and you're bang on the money!
Regards,
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
Ken,
Check out DAYOFMONTH() and MONTH() and a wealth of other useful date
functions. RTFM: 6.3.4 Date and Time Functions
There's a few interesting items here OK, Thanks for the pointer.
Go for it...
The answer to your question about AUTO_INCREMENT 'reset' can be
found at
6.5.3 CREATE TABLE Syntax under table_options.
EH? It says what?? I'm so green that this makes NO sense at all.
Regards,table_options:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM |
MYISAM }
or AUTO_INCREMENT = #
You asked:
Off Topic, I saw something about resetting the auto-increment
counter, but
now I can't find reference to it. Can some kind soul enlighten me???
Sorry to overwhelm you with jargon - it can be difficult to assess a
person's capabilities over the email. So by way of a general answer may
I point you at the MySQL and PHP web site home pages, and from there to
their links to tutorial books and web sites. These will help you with
concepts and examples.
The reference to 6.5.3 CREATE TABLE Syntax is in the electronic manual
at http://www.mysql.com/doc/C/R/CREATE_TABLE.html. The CREATE TABLE
command enables you to (re-)build a table by defining its 'schema'
(definitions, rules and/or constraints). The very next section of the
manual, 6.5.4 ALTER TABLE Syntax deals with making changes to a table's
structure.
If you are starting from scratch the former applies. If amending an
existing table, then the latter is of more interest. Logically enough
both follow much the same rules in terms of what you can/can't do.
Reading the CREATE TABLE command 'template', we see:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
table options is therefore something that may be included almost at
the end of the command. may is indicated by the square brackets =
optional. So a 'bare' command would include the words CREATE and TABLE,
followed by a table name, and then defining one or more fields within
parentheses. eg
CREATE TABLE Cinfo
( Cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Fname TEXT)
The contents of the parentheses - a list of fields and their
definitions, are templated by the two lists: create_definition and
type. Once the fields are defined (and the parentheses closed) you MAY
then decide to add further descriptions of the table. These are defined
in the table_options definitions, and include an additional
specification of interest to you:
AUTO_INCREMENT = #
If you add this clause to the above and replace the # with a suitable
integer, you can define the starting sequence number to be used in the
ID column, eg:
CREATE TABLE Cinfo
( Cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Fname TEXT)
AUTO_INCREMENT = 1001;
Hope that's enough to get you re-started. If you plug AUTO_INCREMENT
into the electronic manual's search facility you find a ton of stuff
showing you how to use such columns/series - and a few warnings.
Ok?
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
Ok? Great, that explains better than most of the tutorials I've read. It is unfortunate that most if not all reference documentation expect the reader to have a college degree just on their subject in order to understand wot in 'ell they're saying. I've done pretty well in figuring out many thing on my own but sometimes ya just need an expert to chip in and make yer day. My thanks, My pleasure. In fact the MySQL manual is a cut above many/most 'out there', but as you say it is a reference manual, ie a set of rules and regulations to describe the functionality - more like a dictionary than an encyclopedia - and not many people's idea of relaxing bed-time reading! By contrast, the tutorial sites are designed to 'teach' the use of such functionality. Taking a PHP example, I noted the LIST() and FOR EACH construct in the manual, and pretty much said yes, ok, so, but it was only when I worked through a tutorial which put them together that I saw a neat and powerful way to manipulate associative arrays (ok, maybe that says more about me than reference manuals and tutorials, but...). If you have done some programming before, or if you are confident in your ability to pick up the basics, then I recommend PHP and MySQL Web Development by Welling and Thomson, SAMS, to you. It has a good tutorial style (beyond presuming introductory PHP/programming knowledge) and presents the combination of PHP and MySQL in a series of practical applications/scenarios. I found it very good - but then I have used SQL before and several other programming languages. If the starting point is not a problem, it should also appeal to your interest/approach. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
Ken,
Check out DAYOFMONTH() and MONTH() and a wealth of other useful date
functions. RTFM: 6.3.4 Date and Time Functions
The answer to your question about AUTO_INCREMENT 'reset' can be found at
6.5.3 CREATE TABLE Syntax under table_options.
Regards,
=dn
I have a test table with the ususal customer information in it along
with a
field for the date of birth. I'm trying to return records of any
customers
whose birthdates are the same day and month as the current date.
Here's the query result:
mysql SELECT * FROM Cinfo WHERE BirthDate = date('m d');
Empty set (0.00 sec)
Here's what is returned with a global query:
mysql select * from Cinfo;
+---+--+---+-+---++-
-+--+--+++
| FirstName | LastName | Address | City| State | Zip
|
HomePhone| WorkPhone| CellPhone| BirthDate | id |
+---+--+---+-+---++-
-+--+--+++
| Joe | Blow | 1200 High St. #12 | St.Looy | UT| 844110
|
801-111-2299 | 801-111-3456 | 801-213-8956 | 1959-03-01 | 1 |
| | | | | |
|
| | || 2 |
| | | | | |
|
| | || 3 |
| | | | | |
|
| | || 4 |
+---+--+---+-+---++-
-+--+--+++
4 rows in set (0.00 sec)
I have a feeling it's the date format in the table. I tried making it
a date
field but just got the current date. What am I doing wrong?
Off Topic, I saw something about resetting the auto-increment
counter, but
now I can't find reference to it. Can some kind soul enlighten me???
--
Ken Thompson, North West Antique Autos
Payette, Idaho
Email: [EMAIL PROTECTED]
http://www.nwaa.com
Sales and brokering of antique autos and parts.
Linux- Coming Soon To A Desktop Near You
Registered Linux User #183936
--
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] Grouped sql output
Hi Rudi, What do you think ? There are some problems. I'll test it tomorrow at work. I'll let you know how she goes. How did you get on? Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: ORDER BY question
Hi Chris, Did you get a useable answer to this? Using PHP and MySQL, how can I do an ORDER BY where city = $city? What happens is this, I have a search engine which works nicely, but I want to bring up the closest matches to the search first at the top, how can I do this? Say you enter Munich Hotels, I want all entries with Munich Hotels to appear first before the rest, any ideas? Try: SELECT ... ORDER BY city=Munich Hotels, city; The first 'column' in the ORDER BY clause evaluates to TRUE or FALSE, and thus puts Munich Hotels (TRUE) above everything else (FALSE), and within those two groupings city is sequenced into alpha ASC (meaningless for the M-Hotels grouping because they all have the same city value). Ok? =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: php-db Finding records with NULL values
Robin That may work for a single field in a record that is NULL, but I'm trying to join two tables, and report the lines that do not have an entry in the second thus: Question Table Q_idQuestion 1Question 1 2Question 2 3Question 3 4Question 4 Response Table R_idMember_idQ_idResponse 111Yes 213No So, member 1 has answered questions 1 and 3 - I want to find how to get the q_ids of the remaining questions... Q_id 2 4 Check out OUTER JOINs (RTFM: 6.4.1.1 JOIN Syntax) =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] using sendmail w/ windows
CrossWalkCentral, any one know how to use send mail with windows instead of linux. Will this work are their other options? Beware of crosswires! Yes one can send mail (the activity of email) from PHP running on Windows. I wouldn't bother with loading sendmail (the name of the package) onto a Windows machine! Take a look at the php.ini's mail section. There are three lines. One is Unix only - leave commented out. Uncomment the other two. One is your default 'from' address. The other points PHP at an SMTP server (can be on any machine - the PHP server, another in the local network, some remote/ISP on the Internet). RTFM about mail(). Email from PHP under Windows is in many ways easier than the same under Linux! Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Grouped sql output
Hi Rudi, I've been struggling for a while now so I thought I'd ask. What's the easiest way to for to print group sql output. For example I need to output customers grouped by zip. Like: zip customer a customer b zip customer d customer e zip customer c custoemr f customer g Do I need to build array's and loop through them or can I print straight from a record set ( using Postgresql or Mysql ) As a general rule, I would ask the RDBMS to do as much as possible (WHERE, GROUP BY, ORDER BY, HAVING), before handing the data to PHP - then use PHP to concentrate on getting the formatting/presentation (HTML) right! What you're asking for is quite common - and easy to implement (once you know how). Does that 'philosophy' answer your question, or were you wanting coding-level advice? If so, please post your current SQL query and if it's not too long, the pertinent part of your PHP prototype, and I/someone else here, will be happy to make suggestions. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Grouped sql output
Hi Rudi, I've done this before in coldfusion. It's the one thing I'm note sure on how to do the same(ish) in PHP - my language of choice. If we have the record set the CF code would look like ul cfoutput query=customers group=zip !--- loop through record set --- li#zip# ol cfoutput !--- inner loop on group by zip --- li#customer# /cfoutput /ol /cfoutput /ul So I'm think also to use 2 loops for the same purpose in PHP. Can I do this from the record set like above or do I need to build array's I wonder ? No need for arrays - the resultset from the RDBMS effectively is one - SQL-talk says it's a table (please show us your SQL query if you have any doubt about this). Make sure the SQL resultset is sorted/grouped correctly! You're approach looks right to me - just more CF than PHP-like. How's this? query RDBMS check result ok/have at least one row extract first row initiate presentation WHILE not EoD note zip code ECHO zip heading WHILE this row's zip == noted zip AND not EoD ECHO customer data end while zip extract next row from resultset end while data terminate presentation OK? Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Double Dipping
Howdy John, Sorry for the lack of information in the subject line, but I didn't know how else to explain it briefly... =don't be sorry, re-word it! I have 2 tables and they look like this: There are 2 other tables involved, but they don't come into play for this portion of the problem so I won't bother you with the details. Some sample data may look like this: Table: teams | ID | leagueID | divisionID | name| | 11 | 1| 5 | Dodgers | | 12 | 1| 5 | Cobras | | 13 | 1| 5 | Bombers | Table: games | ID | leagueID | divisionID | hometeamID | awayteamID | gamedate | gametime | | 1 | 1| 1 | 12 | 13 | 2002-02-21 | 15:30:00 | | 2 | 1| 1 | 11 | 12 | 2002-02-26 | 15:30:00 | Here's the issue... I want to be able to have it pull in the name for each of the teams (home and away) from the database. My existing query looks like the following: (note: $leagueID = 1 in the following line) SELECT gdate, gtime, divisions.name as division, teams.name as teama, teams.name as teamb FROM divisions, teams, games WHERE games.leagueID = '$leagueID' group by gamedate, gametime This has gotten me close, but, not over the hump. As always, I hope I have explained this well enough! The missing item that might have been helpful is a sample output of what you have so far. I'll guess that it's something like: gdate, gtime, division, teama, teamb -02-21 15:30 1 blank blank If you substitute/add hometeamID and awayteamID in the resultset (purely for debugging purposes) then you should see the IDs 12 and 13 appearing, but still no team names under teama or teamb. You probably know that you are missing the join between games and teams. There is no reason why you cannot have two joins - double dipping. Did you try it? WHERE...teams.ID=hometeamID AND teams.ID=awayteamID NB the above does not imply (as some might at first think) that hometeamID would have to equal awayteamID for this to work, the two ANDs/the two joins are independently arranged. Let us know how you get on! =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] debugging?
Ok jas Ok now that I can see the error message how can I fix it... the syntax looks correct and the error I am recieving is as follows You have an error in your SQL syntax near '= 'id'' at line 1 And this is my statement, $sql = mysql_query(DELETE FROM $table_name WHERE $id = 'id',$dbh) or die(mysql_error()); Is there anywhere on php.net or mysql.org that give exact error meanings? Possible confusion between PHP code and SQL. Do you really mean ...WHERE id = '$id' ? =dn Thanks again, Jas Olinux [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... jas, Try this: - Remove the @, it suppresses the error - change die(Could not execute query, please try again later); to die(mysql_error()); $sql = mysql_query(DELETE FROM $table_name WHERE $id = 'id',$dbh) or die(mysql_error()); olinux --- jas [EMAIL PROTECTED] wrote: Can someone tell me how I can find out why I am getting errors executing queries when I try to delete items from a table? I have 2 files... file 1. - Queries database, displays results with option to delete record using a check box, code is as follows... ?php snip __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.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] SQL
Jonathan, The original query doesn't work because the WHERE clause is evaluated earlier than the GROUP/COUNT(). Try putting num1 and num2 in a HAVING clause. Regards, =dn ok... authorizations and charges... two tables.. Card Number 1 gets authorized 20 times for use (20 rows in the authorizations table) and when the transaction is completed they get charged 20 times (20 rows in the charges table) .. however we've been finding that authorizations are not always ending in charges. (Essentially people are cheating the system) and I need to find out who. Card numbers are the same... I ran this without the num1=num2 and it gave me very bad results.. I think my counts are off base. num1 and num2 were the same for all of them. I know this isn't the case. so... SELECT authtable.cardnumber, count(authtable.cardnumber) as num1, count(chargetable.cardnumber) as num2 FROM authtable, chargetable WHERE authtable.cardnumber = chargetable.cardnumber AND num1 != num2 is what I'm shooting for logically.. once again.. I think the counts are messed up... (I might be completely off base here..) How could you possibly have such a condition exist? That is, what relation do the counts have to the contents of the name field? What ARE you trying to really accomplish? Is it possible to do a comparison of values from a count... for example... SELECT table1.name, count(table1.name) as num1, count(table2.name) as num2 FROM table1, table2 WHERE table1.name = table2.name AND num1 = num2 This sort of thing dosent work for me. How do I reffrence a count later in the where clause? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] finding ID's
Ok Jonathan, [I've put the answer back on the list - there are others who can help, and may well get back to you faster than I] Ok I get the auto_increment stuff... Just one more question. Let's say I want to show 25 rows per page. I want the script to generate links to the next page, and so on. How do I make it show from the highest ID to 25 rows down? There is no auto_decrement Here's an example: ID 1 4 5 8 10 11 12 15 17 18 I want to show the 5 newest rows (have the highest ID's). AUTO_INCREMENT (and your 'AUTO_DECREMENT) only has relevance when a row is being added to the table. When a SELECT is performed, the value is returned, fixed, and numeric, just as if it were any other integer and/or one that you had loaded as a literal value. It does not have any effect on SELECTs. Thus if you SELECT * FROM tblNm, you will not necessarily retrieve the rows in ascending sequence of the id field! Did you already realise this? To retrieve the data 'in order' you will need to add an extra clause: SELECT * FROM tblNm ORDER BY id; That said, perhaps you have two questions here: - sequence the results of a SELECT in inverse order to the AUTO_INCREMENT/id field value by using the ORDER BY id DESC. [RTFM: 3.3.4.4 Sorting Rows] BTW Chapter 3 of the manual is a useful tutorial/user guide that covers many of these topics/offers a functional introduction. - how can a long resultset be split up and displayed on several web pages using a PREV/NEXT page link facility? This answer revolves around the LIMIT clause [RTFM: 6.4.1 SELECT Syntax] SELECT select_expression,... [LIMIT [offset,] rows] The first page is populated with a query saying LIMIT 0, 25, and the next LIMIT 25, 50 (or that 24 and 49?) BTW I was working off your earlier quote of 25 cf the later of 5 This combination of HTML, PHP, and MySQL is not discussed in the MySQL manual, but there are a number of tutorial articles available on the various PHP support sites. If you need to get started/make contacts, visit the MySQL and/or PHP home pages and follow their links, or Google-it. There are also some prepared classes/scripts available for the downloading which will save you the coding time (unless you want to learn for yourself). You will also find this in any book covering PHP and MySQL - a most worthwhile investment if you're going to 'walk amongst us' for a while! Regards, =dn (comments interposed, below) Try using something like $getlist = mysql_query(SELECT id FROM yourdb,$dbconnectetc); $numrows = mysql_num_rows($getlist); echo $numrows\n; This will give you the number of records in your db. If you use autoincrement for the id field your will not have to worry about your ++. If you delete any records numrows will always be correct. =this is correct, but COUNT(*) is optimised/more efficient (RTFM: 6.3.7 Functions for Use with GROUP BY Clauses) First off, sorry for the newbie question... :(. I want to be able to query the database and find the record with the highest ID value. Example... each row ideally has an incremented integer ID (1, 2, 3, 4...) but I am running into problems when I try and delete a row (let's say row 2). My PHP currently selects all of the rows and formulates the ID off of that... This I found out is bad because when I delete row 2 the query says there are 3 rows so my PHP will try to make the ID = 4. I just need the code to find the highest ID so I can ++ it. Sorry again for the easy question! =no need to apologise, we all have to start somewhere. =It is an FAQ. By asking it you indicate that you don't (yet) understand the philosophy of the AUTO_INCREMENT facility. It is there to provide an ID for new rows of data, not to be a 'count' of the rows. If you consider that this ID may be used as a key into this table's data from another table (foreign key), then you will realise that changing ID values to reflect intermediate deletions is less than logical. (RTFM: 3.5.9 Using AUTO_INCREMENT, although it doesn't seem to get into this point - the annotated comments are worth a read though). I've just a had a quick look to see where the manual discusses the 'philosophy' and have come up empty - perhaps someone else can steer you right, if you need more. =It is worth reading through PHP's large collection of built-in MySQL_ functions. There are specific functions that will return various 'numbers of rows' to the script to suit various situations. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dynamic Table Columns
Randy,
Suggest pulling the data out of the resultset using a numberically-indexed array. Then
o/p each TD.../TD
inside a loop - you know how many times to loop, because that is set by the number of
rows (plus Total column).
BTW if you use aliases on those SUM() columns - it will make life a lot easier when
you are using associative
arrays to post-process the data.
Regards,
=dn
I have a form (criteria.php) that includes two drop down lists from which a
user can select a start period and an end period from a MySQL table. This
form posts to a form called order_summary.php on which I am using the
$start_period and $end_period variables from the criteria.php form to pull
data from another MySQL table which will produce a summary of sales for
those periods as illustrated by the following example:
$start_period $end_period $total
-
Order Type | APR-00 | MAY-00 | Total |
-
Widget 1 | $100 | $200 | $300 |
Widget 2 | $300 | $200 | $500 |
.
.
.
OK. Fine and good. I have this working. My problem is this:
How can I create a table which will dynamically adjust the number of columns
based on the users request. That is, if the user requests APR-00 through
MAY-00, the table will be built as illustrated above. If the user requests
JAN-01 through JUN-00, the table will be built as illustrated below:
$start_period through
$end_period
--
Order Type | JAN-01 | FEB-01 | MAR-01 | APR-01 |
MAY-01 | JUN-01 |Total |
--
Widget 1 | $100 | $200 | $300 | $300 | $300 | $300 | $300 |
Widget 2 | $300 | $200 | $500 | $300 | $300 | $300 | $300 |
.
.
.
I have posted order_summary.php below.
Any thoughts?
Thanks,
Randy Rankin
order_summary.php
**
?
session_register($dbname);
?
?
require(./includes/salesdb.inc);
$query_first_last_period = select period_id, period_name
from periods
where period_id between $start_period_id and $end_period_id
GROUP BY period_id
HAVING period_id = $start_period_id OR period_id = $end_period_id
ORDER BY period_id;
$result_first_last_period = mysql_query($query_first_last_period);
$row = mysql_fetch_row($result_first_last_period);
for( $i= 0; $icount($row);$i++ )
{
if( $i == 0 )
$start_period_name = $row[$i];
elseif( $i == (count($row)-1) )
$end_period_name = $row[$i];
}
echo font face=Tahoma size=2 color=#333999;
echo Salesperson: b$dbname/bbr;
echo Report Period: b$start_period_name through $end_period_name/b;
echo /fontP;
echo table border=0 cellpadding=2 cellspacing=2;
echo tr bgcolor=#99font face=Tahoma size=2 color=#ff;
echo th align=left bordercolor=#808080font color=#FF face=Tahoma
size=2bCategory/b/font/th;
// Start Get the Order Summary Data ***
$sql_order_summary = SELECT order_type, SUM(extended_dollars)
from salesdata
where salesperson = '$dbname'
and period_id between '$start_period_id' and '$end_period_id'
group by order_type;
$result_order_summary = mysql_query($sql_order_summary) or die (I can't get
the sales data!);
// Stop Get the Order Summary Data ***
// Start Get the Period Names for the Table Header
***
$sql_period_name = Select period_name
from periods
where period_id between '$start_period_id' and '$end_period_id'
order by period_id;
$result_period_name = mysql_query($sql_period_name) or die (I can't get the
period name!);
// End Get the Period Names for the Table Header
***
// Build and Print the Table Headers ***
$num_periods = mysql_numrows($result_period_name);
if ($num_periods == 0)
{
echo font color=#ffbThere were no records found for
the periods selected./b/font;
}
else
while ($row = mysql_fetch_array($result_period_name))
{
$period_name = $row[period_name];
echo th align=left bordercolor=#808080font
color=#FF face=Tahoma size=2
b$period_name/b/th
/font;
}
echo th align=left bordercolor=#808080font
color=#FF face=Tahoma size=2bTotal Sales/b/font/th;
echo /tr;
// End Build and Print the Table Headers
***
// Start Calculate and Format Total Sales
***
$get_itemtot = SELECT SUM(extended_dollars)
FROM salesdata
WHERE salesperson = '$dbname'
and period_id between '$start_period_id' and '$end_period_id' ;
$itemtot_result = mysql_query($get_itemtot) or die (I can't do the math!);
$itemtot =
Re: [PHP-DB] finding ID's
Hi Jon, and Howard, (comments interposed, below) Try using something like $getlist = mysql_query(SELECT id FROM yourdb,$dbconnectetc); $numrows = mysql_num_rows($getlist); echo $numrows\n; This will give you the number of records in your db. If you use autoincrement for the id field your will not have to worry about your ++. If you delete any records numrows will always be correct. =this is correct, but COUNT(*) is optimised/more efficient (RTFM: 6.3.7 Functions for Use with GROUP BY Clauses) First off, sorry for the newbie question... :(. I want to be able to query the database and find the record with the highest ID value. Example... each row ideally has an incremented integer ID (1, 2, 3, 4...) but I am running into problems when I try and delete a row (let's say row 2). My PHP currently selects all of the rows and formulates the ID off of that... This I found out is bad because when I delete row 2 the query says there are 3 rows so my PHP will try to make the ID = 4. I just need the code to find the highest ID so I can ++ it. Sorry again for the easy question! =no need to apologise, we all have to start somewhere. =It is an FAQ. By asking it you indicate that you don't (yet) understand the philosophy of the AUTO_INCREMENT facility. It is there to provide an ID for new rows of data, not to be a 'count' of the rows. If you consider that this ID may be used as a key into this table's data from another table (foreign key), then you will realise that changing ID values to reflect intermediate deletions is less than logical. (RTFM: 3.5.9 Using AUTO_INCREMENT, although it doesn't seem to get into this point - the annotated comments are worth a read though). I've just a had a quick look to see where the manual discusses the 'philosophy' and have come up empty - perhaps someone else can steer you right, if you need more. =It is worth reading through PHP's large collection of built-in MySQL_ functions. There are specific functions that will return various 'numbers of rows' to the script to suit various situations. =Regards, =dn =Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Semi-newbie - postgres polygon/points and PHP?
Ok Roger, Ok, a kinda newbie question. I set up a Postgres DB with a table that had a Point and a Polygon in it (to hold XY and shape settings for munging a jpeg image in php). I could not easily figure out how to extract the data into a reasonably easy to use format in PHP. The best I could do seemed like it was extracted into a string, and then I had to parse that. While this works, it seems like a BIG hack to me, and the docs have been less then forthcoming. I've also tried the usual google search of php postgres polygon to see if I could get an idea. For example, I stored the Point as:(432, 120) And I got (432, 120) as a string, and had to parse it: If I understand you correctly, the cartesian coordinates are stored in the tbl as a string, eg CREATE TABLE room... id... xy tinytext... but each coordinate whilst a single entity is defined by two numbers. Why not store them as integers/reals on a single row, eg CREATE TABLE room... id... x int, y int... Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] counting multiple columns based on different values
John, I'm gonna go out on a limb and guess that I'm missing something obvious (and easy) because this sure seems like it should be able to be done. Here's the issue: I need to pull the client name and ID out of one table and then, count the records in a different table (called ratings) that match two different criteria. If I was doing 2 different sql statments, they would look like this: select clientname, clients.ID, count(*) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' select clientname, clients.ID, count(*) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '3' In a perfect world, I'd be able to receive the following data from a single query: | ClientName | ClientID | Status-2 | Status-3| | Bob| 28 | 103 | 87 | | Steve | 29 | 11 | 106 | | Jerry | 30 | 50 | 82 | The first idea (multiple COUNT()s and GROUP BYs) didn't work, probably because of the way I was confusing the use of table aliases with VIEWs. No matter, let's go to 'plan B'. Following my usual 'formula' approach, breaking it down into smaller problems: 1 getting the data out of the clients tbl and grab all of the status rows 2 refine the first ratings criteria to a 'count' 3 do the same for the second ratings criteria The first is a trivial join: SELECT clientname, clients.ID, ratings.status-2, ratings.status-3 FROM clients, ratings WHERE clients.ID = ratings.clientID AND (ratings.status = '2' OR ratings.status = '3') Just to be sure of my assumptions (remember I don't have enough info to be able to debug/test!), please check that this is ALL of the data you want included, and that none other exists/is missing. This won't satisfy you - you don't want line after line of status data, you want that info aggregated. Taking status=2 first, we should go from: SELECT clientname, clients.ID, ratings.status-2 FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' to: SELECT clientname, clients.ID, count(ratings.status-2) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' GROUP BY clients.ID - or should that be GROUP BY ratings.clientID? (they've 'fiddled' with some of the finer points of GROUP BY recently) Trouble is this falls apart when we add status-3 - as we discovered earlier. Let's review what COUNT() does. SQL groups the rows according to the WHERE clause and then notes the number of rows in the group. Ok, so we can do that too - let's imagine adding an extra column to the table, and put a TRUE into the column if it is relevant to the count, and FALSE if it is not. Yes, there is an IF() in SQL (some very 'meaty' RTFM starts at 6.3 Functions for Use in SELECT and WHERE Clauses). IF( ratings.status = '2', TRUE, FALSE ) So now we could simply count the TRUEs to arrive at the number of status=2 rows. Substitute 1 for TRUE and 0 for FALSE, and we can have SQL do that last calc for us, ie SUM( imaginary-column ). SUM( IF( ratings.status = '2', 1, 0 ) ) Because there is no GROUP structure, we can replicate the same for status='3'. SELECT clientname, clients.ID, SUM( IF( ratings.status = '2', 1, 0 ) ) AS Status-2, SUM( IF( ratings.status = '3', 1, 0 ) ) AS Status-3 FROM clients, ratings WHERE clients.ID = ratings.clientID BTW I am taking from your query the idea that status is a string not a numeric. Another BTW is that appropriate indexing will speed things up considerably if you are operating on larger tables! Beaten it this time? Please advise, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Connecting to mySQL database
Hi Alvin, I'm new to both PHP and mySQL but have no choice but to crash-course on both before end of mid of march to complete my University project. I would really appreciate alittle help from any kind soul out there. I have already created a mySQL database and create all the tables *well at least all those that i think i need*. Now i am trying to connect to the database and test if everything was created properly and make sure i can connect. Can anyone please advise on how i should go abt it?? Thanks a million!!! Welcome to our happy little (ever-expanding) world! I suggest a visit to the PHP and MySQL homepages. If you look for the links and tutorial pages, you will find plenty of the latter. There are a number of web articles on 'intro to MySQL and PHP' and 'making dynamic web sites'. These types of tutorials cover all the basics that you're going to need to get started. Later on, another visit may even yield something about inventory systems, perhaps the sites offering classes might have something handy you can pick up and apply. If you prefer books/the Uni library: PHP and MySQL Web Development by Welling and Thomson or MySQL by DuBois are good choices - both cover interfacing the two products, but there are numerous alternatives that others will recommend. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Fwd: php-db Digest 11 Feb 2002 20:12:53 -0000 Issue 1046
Neil, My 2 cents : In fact Todd has also been spouting ( moaning? g ) on the Windows/PHP list too ;-) Certainly, parsing Word docs is not necessarily a PHP-DB issue, but an interesting problem or hack nonetheless. IMHO M$ have set themselves up for 'illegal' hacking of their file formats simply by virtue of their widespread use and insistence on not releasing the source for their products. But surely, this sort of stuff is worth considering as an exercise - if *everything* was easy there would be no jobs for people in the IT industry generally! =Word is a Microsoft product. Have you been moaning on their lists (always assuming anyone from Microsoft actually responds/is helpful on such lists), asking where the Word-PHP interface is? Why attack only one side of the 'problem'? Perhaps like many others you have a perception that Microsoft couldn't care less about what you are trying to do? Why then make the Open Source community your whipping boys (and girls)? Hope you guys have cooled off settled your differences. DL Neil finished his rather vehement ahem - ggg discussion of the benefits of open-source et al , by *providing 3 helpful suggestions* how to tackle the overriding problem. I think perhaps 'childish remarks' might be an appropriate description *IF* the remarks were meant as a personal attack, rather than an expression that perhaps this list is not appropriate for reverse-engineering discussions. I don't know what crawled up your ass and died or pissed in your cheerios this morning. snip Geez, whats with you types? I mean, You think Open Source is the ultimate solution and Bill Gates it the evil empire. Grow up and don't respond to my inquiries. I don't like childish remarks from an incompetent fool Competence is in the eye of the beholder ( ducks under desk for protection ! ) I've only been on this list for a few weeks, but it's proved very helpful for my development, to think about solutions to other peoples problems, and provide help where possible. Very grateful for the input others have generously provided. I think though that the nature of this list is probably more for newbies than experienced MCP or open-source guru discussions, preferably for discussion of PHP and Databases - and welcome any flames suggesting otherwise vbg In fact this list provides an interesting mix of problems for/from 'newbies' and 'gurus' alike, and let's face it, the 'triad'/LAMPS offers such an amazing 'width' of facilities, few people would claim to be 'experts' across the board - so someone who can perform extremely complex SQL SELECTs might find PHP a major challenge, and vice-versa. I certainly enjoy the learning/stimulation. 'Decoding' Word would be an 'interesting hack' - although I don't think such would be necessary. Following up on some of this thread's references - and their references, eg Alain's article shows that the COM interface allows easy (and perfectly legal/expected) access to MS/Word functionality. From then on it seems possible to perform anything that Word will do from the keyboard (I'm not VBA-competent - just know enough to slightly extend macros). Trouble is, those commands are not PHP (etc) - this would require Word/VBA mastery! One day I'll have 'a chat' with one of our Office specialists - who does know his MS oats, and see how realistic it might be. Unfortunately it has not been my experience that the MS 'support' lists are as friendly and competent/quick to respond as are the various PHP and MySQL communities! Thank you for your comment about competence. Although he has said nothing to me/about it, I notice that Todd has taken 'my' suggestion to his client and received positive feedback. Sometimes technical questions can be best tackled in the non-technical environment! It often pays to step back from the coal-face to take a wider look at a 'problem'. You observe my comments to be vituperative - and yes they were. I will apologise if you think they were overly so. Note that the Microsoft-OpenSource issue was raised previously - mine was a reply/riposte. Some want to dish it out, but can't take it. Certainly I took issue with 'blame' or 'complaint' being laid one-sidedly at the door of PHP/OpenSource - it would be unusual for some incompatibility between two products to belong exclusively with any one or the other? Certainly MS won't accede any fault - even between Access and the rest of MS-Office! The superb ironies are that the best (from the client's point of view) answer he has came from the guy he least admires (shall we say...); and that my history (in PCs at least) is Microsoft through-and-through (some would guess that's how I know about Word) and I count myself as still very much a learner in the LAMPS environment. Unfortunately (speaking for myself) there was a 'personal' component. As you observed, the individual has a reputation for 'moaning', or more precisely for asking questions which
Re: [PHP-DB] Using a Left() result in PHP
Hi Kim,
I want to get the first 64 characters from a database row result and display
only those 64 characters using PHP. The snipped code is here - I've deleted
all the error trapping and comments:
$result = @mysql_query(select ID, Writer, left(text,64) from Reports where
writer like '%kim%';);
while ($row = mysql_fetch_array($result)) {
$ID=$row[ID];
$Writer=$row[Writer];
$text= $row[Text];
echo (p . $ID . . $Writer . . $Text . /p);
This returns a blank result for Text. If I remove the left() from the SQL it
works but displays the entire report - hundreds of words. How can I display
only those first 64 characters - am I missing something obvious?
The 'fieldname' that is passed from MySQL to PHP depends upon the terminology of the
SELECT clause (not the
schema of the table). So the column's name is text (BTW not a good choice) but the
query knows the resultant
value as left(text,64)
- issue the query from the command line or in an admin pkg, and check the column
headings, if you want to see
what I mean!
Use an AS to give the computed-column a 'good name' and you'll be away:-
$result = @mysql_query(select ID, Writer, left(text,64) as Intro from Reports where
writer like '%kim%';);
...
$text= $row[Intro];
Regards,
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Simple Question, hopefully simple answer
Luke, In one of my mySql tables I have a colum that contains many fields of numbers. for example | id | Name | Age | Sex | |1 | Jim | 17 | m| |2 | Dave | 31 | f | |3 | Fred | 25 | m| Ok and I want to get the total value of all the ages out of the table. how can I get this? RTFM (MySQL) for COUNT() and SUM() (and many other useful functions). Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL / PHP Database Help
Jonathan, I'm trying to write a script that checks for multiple entries on a table in a database. So far I've been plagued by MySQL errors. I'm fairly confident my scripting is ok (Then again, I've been wrong before.) I think moreover there might be some difficulty with my theory. Then again, I could try and do this with MySQL join statements. Any feedback would be appreciated. ... Warning: Supplied argument is not a valid MySQL result resource in /home/ufr2/underfoot-www/admin/dupeshows.html on line 51 I notice that others have addressed your PHP/interfacing issues... You asked about theory. The current solution will require n+1 calls to the database (where there are n-rows in the local_shows table). That's quite 'expensive'. Which is likely to be the 'best tool for the job' - an external scripting language (PHP) or the RDBMS itself (MySQL)? Consider a 'MySQL-oriented' solution:- 1 if the two fields (show_date and venue) were made into a UNIQUE index, then the check would become superfluous because MySQL would ensure the situation never arose - at the cost of the time to perform the check/index insertion at every row INSERT or UPDATE (although you may gain a speed increase for certain SELECTS) 2 performing the existing PHP routine using SQL - you show only a list of 'duplicates' (and no DELETE), so: select show_date, venue, count(*) as freq from local_shows group by show_date, venue having freq1 will give you what you have attempted to code thus far. (you may wish/need to juggle the sequence of show_date and venue to suit) Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Simple Question, hopefully simple answer
RTFM (MySQL) for COUNT() and SUM() (and many other useful functions). My MySQL client is giving me a syntax error when I enter that command. :-) =that? Please post the SQL/PHP causing the problem - the list's crystal ball filter is not working. =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL / PHP Database Help
Jonathan, [have put the cc back to the list] Is it possible to make BOTH the date and venue into a single unique index? =Why not? Like a good woman, treat her right, and SQL will do almost anything for you: 6.5.3 CREATE TABLE Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] orPRIMARY KEY (index_col_name,...) orKEY [index_name] (index_col_name,...) orINDEX [index_name] (index_col_name,...) ... Note the ellipses (...) at the end of that last line - many people are used to writing [PRIMARY] KEY or INDEX immediately after field name and definition, forgetting that if it is a separate clause of the CREATE stmt, multiple columns may be specified! (Not that this works for me.) But I'm qcurious about this. I understand where this could be useful as a single unique index.. (as opposed to two unique indexes) Is this possible? How so? =yes it is possible, as above. =the short answer is: wherever you find yourself doing SELECT...WHERE field/index-condition1 AND field/index-condition2 =If only the first field/column is indexed, then obviously the SELECT will be faster than when accessing an unindexed table. However if there is a large fan-out between the two fields columns, (ie there are a large number of different values in field/column2 which share the same value in field/column1) then it may pay to combine the two fields into a single index for even faster results. Of course, the smaller your table, the harder it is to 'see' any return on the investment! (In my case multiple entries are ok, just as long as I can run a report to spot them, and then edit them which usually requires human interaction.) =If your system's data-entry stage is time-constrained then I would be tempted to agree. Otherwise conventional wisdom suggests that it is better to prevent 'dirty' data entering the system or data integrity issues creeping in, than it is to develop a strategy to 'clean' the db post-fact. Usually the person entering the data knows most about it - or has the best opportunity to ask the 'data source' for clarification! Your second suggestion worked rather well... although its not quite generating the output that would be best suited to me. The MySQL docs on Group By and Count are quite weak.. do you have something else you could send me / can you explain these commands. I was sure there is / was a way to do it in MySQL my SQL just isn't what it should be. =if you post the code you've developed thus far, and some sample source data and results, together with some specific criticism, we might be able to help with issues like best suited, or tweak the code I sent earlier to provide for situations that may not have been evident (at least to me) in your first post. =GROUP BY and COUNT() can be combined in many different ways, so what seems straightforward on the surface can yield enormous power when you start to tinker under the hood. I assume what you mean is that the manual is not really a tutorial. =Apart from the manual, I use books (I've picked up a few over the years - some probably now out of print; Paul DuBois' MySQL is current and the most specific - and has a PHP interface chapter, plus other more-PHP books, eg Welling Thomson) and there are a number of tutorial web sites either covering SQL generally or MySQL in particular (start at the MySQL site or any search engine). =Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Searching Documents
Todd, No one knows??? =or isn't telling? hmm... I looked around on php.net couldn't find anything regarding this.. would I need to do these searches in MS Word docs with ASP? =go ahead if you are aspidexterous... =the Word document format is proprietary information - ie not 'open'. So it is a 'bit much' expecting an open system (PHP) to gain access! Try gaining access to words in a Word doc from even Access!? =What about PHP-COM/DCOM? =dn I was wondering if there is a way to search documents. Scenario: I have a application where it uploads MS Word documents to a server and holds the documents location in the database. What I want is to be able to search those Word documents via keywords. Is this possible with PHP? -- 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] Re: Searching Documents
Court, Todd, et al Actually, PHP does COM very wellsearch the PHP-WIN list about COM and Word and you should find some good hints and examples to get going. I haven't accessed the keywords portion of a word document yet, but someone wrote a spellcheck function that used the spellcheck built into Word using PHP COM. There is also a really helpful article at phpbuilder.com that gets you started with COM. If you have trouble finding them, I could forward some messages to you from that list. -Original Message- I was thinking COM/DCOM, but then does PHP able to do that? Or do I need to resort to ASP(Awful Scripting Pages)? Thanks for that comment Court. I have seen the entries in the PHP manual - and possibly one or two of those articles (but not yet studied them - so many PHP info sources/ideas but so little time!). The spell-check exploit caught my eye - a good way to make use of a well-established (and if you use if frequently - a well invested) tool. Unlike Todd's application (getting data out of Word) I was thinking of going the 'other way' (yes there's always one!). HTML/browsers are good for showing information on the screen - whether a data-entry type of form, or a web page or descriptive/narrative information (eg a PHPbuilder article). However when you want to print the data out, browsers are pretty basic! So I was wondering about extending the idea of constructing web documents from a db/on the fly for display in a browser, to going the next step and reproducing the same doc in (say) Word (using its presentation/printing/formatting features), so that the doc can be presented with natty facilities, eg page/chapter headings/footings, page numbers, chapters and table of contents, cover page(s), indexes/indices, etc - 'camera ready'! Anyone seen it done? =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Searching Documents
Todd, Now, if I do COM with PHP, then does this need to be on a Windows server? Currently it sits on a Linux box. I assume if you are setting up a dynamic connection to Word, Word will have to be running - which almost implies a Windows box. Whether the COM component/add-on to PHP (also?) demands a Windows box, I can't say. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Calculate a value during the SQL command
John, I know I could very easily do this after doing the initial database call simply by calculating the two variables, but, I was wondering if there was a way to do this during the sql statement so my results would already have my answer... Here's the setup. I have two tables. Table A has the information about a mailing. Table B has an individual record for each click generated by the e-mail. They look like this: TableA ID // unique identifier mail_date mail_qty // shows how many mails were sent TableB ID // unique identifier mail_ID // matches up with the ID in Table A mem_ID // the member's unique code Here is what I thought was going to work (but didn't): SELECT mail_date, mail_qty, count(*) as clicks, ((clicks / mail_qty)*100) as percentage FROM TableA, TableB WHERE mail_ID = TableA.ID GROUP BY mail_ID What I was hoping to get back was something like this: | mail_date | mail_qty | clicks | percentage | | 2001-02-05 | 1000 | 100 | 10 | If I need to do this the long way, it's not a huge deal, but, I thought this may be something I could do... I hope I explained that good enough. Thanks for your help! If the clicks is removed from the formula, and replaced with count(*) does that solve the problem? Let us know! =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this possible?
Hi Raymond, I have this tricky case, at lest for me : ) I'm trying to get some data out of two tables and listing the data in a product/price site. But. : I have one table with productinfo and one with prices. And it is several columns with the same id in the pricetable, because every product have several sizes. So... how do I get only one row from the product table and two rows from the price table in one line in a page? Is it possible? No, not as such. Code one query to do the join and SELECT the data, grouped and ordered by (say) ProdCode and within that PackSize. Then use PHP to cycle (outer loop) though each product (group), continuing the 'output' across the line when consecutive records are for the same ProdCode (but not re-displaying the ProductInfo data). Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this possible?
Raymond,
See complaint elsewhere as a result of cross-posting. Add my criticism because I'm on
both lists and keep them
in separate InBox folders and now feeling schizoid...
SELECT * [whatever]
FROM pristabell P, varetabell V1, varetabell V2
WHERE P.varenr = V1.varenr AND P.varenr = V2.varenr
AND V2.type = concat(V1.type, meny)
[sorry, machine is busy on something, so haven't prototyped it]
The last AND condition might have to move to a HAVING clause.
This conditional on the larger product always being called name + meny - absolutely
NOT a good idea!
Alternative:
AND V2.pris V1.pris
(better than what I said before)
Have you tried any of these?
Please advise,
=dn
Pricetable: (varetabell)
(varenr, type, pris)
VALUES
(1, '6inch', 29),
(1, '6inch meny', 51),
(1, 'footlong', 45),
(1, 'footlong meny', 66),
(1, 'salat', 39),
(1, 'salat meny', 51),
(2, '6inch', 49),
(2, '6inch meny', 69),
(2, 'footlong', 75),
(2, 'footlong meny', 96),
(2, 'salat', 49),
(2, 'salat meny', 69),
Product table: (pristabell)
---
(varenr, varenavn, innhold)
VALUES
('1','Veggie Delite','Grønnsaker og ost'),
('2','Subway Club', 'Kalkun, skinke og roasbeef'),
('3','Classic Italian BMT', 'Skinke, salami og pepperoni'),
And the query that I have tried looks like this:
SELECT varetabell.varenavn, varetabell.varenr, varetabell.innhold,
pristabell.pris FROM varetabell, pristabell WHERE
pristabell.varenr=varetabell.varenr AND pristabell.type='6inch' AND
pristabell.type='footlong'
- Original Message -
From: Rick Emery [EMAIL PROTECTED]
To: 'Raymond Lilleodegard' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, February 06, 2002 6:23 PM
Subject: RE: [PHP-DB] Is this possible?
Yes, you can do that easily.
It is easier to answer your question if you show us your table structure.
-Original Message-
From: Raymond Lilleodegard [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 06, 2002 11:16 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Is this possible?
Hi!
I have this tricky case, at lest for me : )
I'm trying to get some data out of two tables and listing the data in a
product/price site. But. :
I have one table with productinfo and one with prices.
And it is several columns with the same id in the pricetable, because
every
product have several sizes.
So... how do I get only one row from the product table and two rows from
the price table in one line in a page?
Is it possible?
Best regards
Raymond
--
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 Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Username
Jennifer, You have a clear idea of what you want done by the script - as guided by the book. However, do you have a clear idea of what the script is actually seeing - and therefore why it is doing what it's doing? When debugging, add a heap of ECHO statements to test pertinent values, eg before a branch/decision make sure you KNOW what the value IS (cf what it 'should be'). When you do this, it is most likely that the error/omission will become clear. =dn I am not getting an error message. The te.php is connecting to the data base. Just not doing what I want it to. While that is a very good tutorial (if I was just logging in) but it has nothing to do with inserting information into the database via a form. But thanks for the tut. It will come in handy when I get to that point in my learning stage. Jen Robert Weeks [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... What exactly is the error message? Did you change config.php to use the name of your database and your username password? I've found the articles at devshed to be helpful in the past. This particular one has a pretty good explaination of setting up a user authentication system: http://www.devshed.com/Server_Side/PHP/TimeIsMoney/TimeIsMoney1/page1.html Robert On 2/5/02 9:50 AM, Jennifer Downey [EMAIL PROTECTED] wrote: All I want is to get the username into the database. As soon as test.php shows in browser, the Username, submit button and box ar e there but also it prints the There has been a problem. right off the bat. Now I know there are misprints in the book and I hope I was smart enough to catch them but maybe not. can someone please tell me what I have done wrong? Thanks for all your time. Jennifer Downey -- 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] query error...
Ok jas Ok what is wrong with this sql statement? $sql = UPDATE $table_name SET c_name=\$c_name\,s_addy=\$s_addy\,city=\$city\,state=\state\zip=\zi p\,phone=\$phone\; insufficient checking: comma missing between state and zip incorrectly escaped before phone ... I have looked on MySQL.com at the update function and it states I can use the SET under UPDATE for multiple fields separated by a , and it is not working... Any insight would be great. Insight: use ECHO to output $sql to the screen. If the results are less than satisfactory, copy-paste the sql code into native MySQL or a management package, the errmsgs are usually more informative/directive. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query from two tables
Hi Balazs, The situation: I have 2 tables in an adverising site. In one table, i have the cities used by advertisers, and in the other there are the ads.This ad table has a field which refers to the id of the city in that table. The problem: what mysql method should i use if i want to order the query by cities? I have tried multiple selection like select ads.*, cities.* from ads, cities , but it returns a nonsense. Any help would be appreciated Are you looking for an ORDER BY clause to sequence the rows of the resultset? =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update
Hi Jennifer,
I am having problems with this code. I want to update a members number of
days they have been a member can anyone help?
$query[days]=(UPDATE users set days_member VALUES = ('days_member' +
'1'));
$result=mysql_query($query[days]);
=1 RTFM 6.4.5 UPDATE Syntax
=2 Why would you want to take on a daily load like this. Wouldn't it be easier to
record joining date and
calculate membership period when needed?
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbiequestion)
This is good brain-storming guys! Better to use an integer rather than a date field as 2 simultaneous transactions can still occur on the same second; most date time fields are accurate only to the nearest second. Good point, John. However MySQL (AFAIK) does not have any time functions returning values less than one second. So are you proposing to use PHP's microtime function? That being the case, the two components (seconds and micro-secs) would have to be added together. Also the field would either have to be changed to accept a floating-point value, or the combined number multiplied up to be integral microseconds (of the Unix epoch). Are you guys interested in yet another solution? It simular but I think it's even easier if that's an incentive... =always interested in constructive suggestions - I've learned from this conversation - as well as enjoying the challenge/getting the brain cells to all march around in the same direction... -In your table do put a datetime field, I'll call it lastMod. This will be the last updated date and time (you need time here) -In your form have this as a hidden field so it gets submitted with the rest of the data. -When you receive the data to update the record you have I presume the record ID in $id and the last modified date timein $lastMod. Create a new $now with today's date and the current time Your SQL will look like: UPDATE Foo SET col1 = $col1, col2 = $col2, col3 = $col3, lastMod = $now WHERE recordID = $id AND lastMod = $lastMod As you can see the will succeed only if lastMod has not been changed. And if it had been updated it will not fail (it's perfectly valid SQL) but will not update any records. -Now to check if you did update the record... SELECT lastMod FROM Foo WHERE recordID = $id And compare this to your $now value. Or in MS SQL you can check the @@rowcount variable - (that's a MS SQL value) it will be 1 - success or 0 - collision (if it's 1 you've got duplicate ID's). =if using MySQL then MySQL_affected_rows() would be more efficient - no call to the RDMBS. =Unfortunately if this database requires an additional SELECT, then the solution requires a SELECT (to populate the form), and UPDATE (attempt), and a second SELECT to confirm the UPDATE. This is exactly the same 'cost' as earlier suggestions. (ignoring the UPDATE-clash situation which will have the same effect/cost in all cases) This has some disadvantage in that your end used could go through all the effort to update a record only to have their update fail - I suggest putting a nice error message and refresh the values in the form with the current ones. =which is the accepted fate of all of these suggestions - and the inevitable (if very occasional) impact of multi-user systems - always assuming that such a fact is of major interest to the user!!! It has the advantage of avoiding locking, setting flags, rolling back, you only need to compare one field and more work for you - and it will always work. =So given all of the questions above, I wondered why use an (extra) time field at all? =I don't think that the original question mentioned how many fields were being updated by the form interaction (please correct me if I'm wrong), but I think the outline above could be applied reasonably effectively, even if several fields were being updated (and it MATTERED to the user if some other user updated one or more, meantime). =Borrowing from Frank's code: UPDATE Foo SET col1 = $col1, col2 = $col2, col3 = $col3 WHERE recordID = $id AND col1 = $col1ValueFromSelect, AND col2 = $col2ValueFromSelect, AND col3 = $col3ValueFromSelect =This will complete without incident if the row has been untouched between the initial SELECT and the UPDATE, and will fail if any UPDATE meantime has affected one of the pertinent data fields. NB any UPDATE that does affect the subject-row, but does NOT affect the subject fields, will not trigger a spurious/erroneous 'alert' response - if such a thing were possible in the original scenario. =The operation will need to be followed up by another SELECT (if that's what MS-SQL demands) or the equivalent of a MySQL_affected_rows() [as discussed above]. As before, this result sends the user back to the form, or assures all that the database integrity is good. =The 'cost' remains at two SELECT statements and one UPDATE, but (data characteristics permitting - and assuming no one can spot anything missing) seems not to introduce the possibility of a spurious 'alert' and appears more 'elegant'/self-documenting. =Regards, =dn Interesting problem! Systems would be so much easier to build if we didn't have to allow for users :) Two suggestions, depending on how you want the data dealt with. A table of rows in use, with a time stamp and an owner. When user1 opens the
Re: [PHP-DB] Re: php-db Digest 1 Feb 2002 07:48:33 -0000 Issue 1027
Frank, As yet no response from Garry, but I wanted to ask: You already have DateTable set up (presumably), so extending its application into a relationship with the breeding table is no big deal. Given that one suggestion was that Garry could consider storing monthly statistics in another table (from breeding), and thus saves processing each time/only needs to update once per month and presumably only for the month just passed; wouldn't it make sense FOR HIM to combine the two functions? Initially create the MonthlyStats table to contain only year and month data (which can be related to 'drive' date-oriented SELECTing), and then update/populate each month's row as and when the stats become available. It might also offer opportunities to simplify the processing outlined below. Creating the monthly statistics and then storing them back in the DB is not my favorite way to solve the problem. It violates one of the fundamental rules of good relational database design - that Data is stored in one place only. =As it happens I do agree with you, however I'm playing 'devil's advocate' (a tactic consultants often use to ensure that people understand the whys-and-wherefores of what's being said). =In the case of statistical databases the rules of normalisation/good db design are often varied. Even in relational applications the argument for de-normalisation frequently comes out of the usage pattern - it is more efficient in terms of response time, to 'cache' the answer to an FAQ (in another tbl) than to repeatedly calculate it. =The 'mathematical' argument is to ask if the aggregation/statistics are in fact multiple instances of the same data/representation - I'm not even going to start on that one... For example: if you created the January statistics on February 1 and on February 2 someone updates the DB with a record that affected January, then your monthly total is wrong. (I don't know about the rest of you but occasionally I do get behind). =it depends how these things are treated. In the case of breeding dogs etc, I imagine that you're right. However if there is the concept of a 'cut-off date', eg get your expenses claim in by x-day if you want them paid during this accounting month. Then there is a clear distinction between the months that has little to do with when the expenses were actually incurred! =In the case of the breeders, if there was a monthly magazine, then statistics calculations will have to be completed in advance of the publication date - regardless of how late some of the data was to arrive. =How the data is treated when it arrives late, will vary by application/implication. The average company expenses system will put a claim from last month (or even before) through as if it was a claim for 'this' month and not worry too much. I've watch sales people play some pretty serious tactical-games with their signing-up deals to gain credit within a particular month/quarter... To fix this you have to check every time someone updates the 'breeding' table (in this example) and if the dates are in the past you'll have to recalculate the monthly total (UGLY!!). So we'd like to calculate the daily totals and monthly averages when called for unless there is some reason we can't (poor performance perhaps). =indeed - and whilst we don't know the volumes, the 'demand' does not look significant, even at ten or one hundred times the volume stated below! A DateTable with one record for each day is under 4,000 records for 10 years of days, since you will never update or delete from it and rarely add to it you can index the heck out of it. SQL is build for just this kind of problem. Just my opinion... =thanks. =dn -- 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] Ensuring users don't overwrite each other (NOT a newbie question)
Ollie, First off, apologies, this is the first mention of MS-SQL (that I have noted). I am not 'up' on the latest versions/facilities offered. Ah hang on just thought of a flaw in that - in between the second select, compare and update is enough time for another user to slip in - so I will still need some kind of logging. =Not too much so. If the entire table is locked (between the second SELECT and the UPDATE), how long will it be locked for? - and is that a major issue within your operating parameters? If it is locked, then there is 'no' time... But it brings me nearer the solution! I think the solution is a combination of the 2 - start a transaction do a second select but do the comparision in SQL, then if all ok then start another transaction do the update do the update(s) of the joined tables and then commit both transactions - that way MSSQL will lock the required resources during the transaction (and can't slip in between the 2 sql queries). =the database transaction should not start until the second SELECT, for the reason stated. Most of the time there will be (I assume the probability is v.low) no difference in the database between SELECTs. The only time the user would be aware that what (s)he thinks of a transaction is not the same as the RDBMS' view! =dn Or not? Ollie -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: 02 February 2002 15:40 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Php-Db ML Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Ollie, Probably easier to repeat the 'read' (from the first population of the user form) - and then do the comparison(s) in PHP - either way it will a SQL query that must be evaluated by PHP before the UPDATE is issued. ie put the interaction in a function and call it from the two locations in the code! That way you don't have essentially the same logic implemented in two places (fatal when you come to modify the code/db). Regards, =dn - Original Message - From: Oliver Cronk [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Php-Db ML [EMAIL PROTECTED] Sent: 01 February 2002 15:23 Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Yes thats an interesting idea, and this compare-and-modify-if-unchanged bit would be implemented via SQL IF statement(s) perhaps? Thanks Ollie Hugh Bothwell [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Oliver Cronk [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Thanks for that answer, you filled in some of the blanks for the table / logging solution, but I am now looking at row locking instead of a seperate table (and then doing things similar to what you outlined). Just a thought - if you have the user form echo a copy of the original data back (ie in addition to the modified data), you can compare it to the existing data and warn the user if the data has been changed in the interim. You must make the [compare-and-modify-if-unchanged] atomic, but that's okay, because it's all in the same script anyway - it becomes reasonable to do it as a transaction. -- 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 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 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] Re: Date operations.
Frank,
As yet no response from Garry, but I wanted to ask:
You already have DateTable set up (presumably), so extending its application into a
relationship with the
breeding table is no big deal. Given that one suggestion was that Garry could consider
storing monthly
statistics in another table (from breeding), and thus saves processing each time/only
needs to update once per
month and presumably only for the month just passed; wouldn't it make sense FOR HIM to
combine the two
functions? Initially create the MonthlyStats table to contain only year and month data
(which can be related to
'drive' date-oriented SELECTing), and then update/populate each month's row as and
when the stats become
available. It might also offer opportunities to simplify the processing outlined below.
Your thoughts?
=dn
- Original Message -
From: Frank Flynn [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 30 January 2002 17:05
Subject: [PHP-DB] Re: Date operations.
Gary,
You have a right to be confused - this problem is short but it is about as
complex as they get. Still I have a solution for you, also short and you'll
(hopefully) find it elegant. I found it interesting...
The thing is we will create a Cartesian product on purpose - this is
something they tell people learning SQL to avoid (and usually they are not
helpful) but here it is the only to get a daily average since you don't have
daily records of your pairs just a start and stop date.
I was looking at the problem from the child point of view in my first post
(easier since children only have a single birthday). I'll use your problem
now.
We will keep the DateTable and use your 'breeding' table but I will change
the 'paired' and 'split' columns to int (you don't need to do this but it
will make thing easier - some DBMS's will always put a time stamp in a Date
field and this does screw thing up - it makes it harder to join on them).
So we have:
breeding
---
rec SMALLINT PRIMARY KEY
sire SMALLINT
dam SMALLINT
pairedINT
split INT
num_offspring INT
And
DateTable
dayID INT PRIMARY KEY
myDate DATE
month INT
yearINT
quarter INT
/* and as discussed any other date related columns */
I need to count how many pairs were breeding
on a given day, and then average over the month.
OK - fasten you seat belt :-0
SELECT count(*), myDate
FROM breeding, DateTable
WHERE paired = dayID
AND split = dayID
AND month = 2
AND year = 2001
GROUP BY myDay
ORDER BY myDay
/* you could have just as easily used a start and stop date */
/* myDay = 2/1/2001 and myDate 3/1/2001 */
This will give you output something like this (the total per day):
5 Feb 1, 2001
5 Feb 2, 2001
5 Feb 3, 2001
5 Feb 4, 2001
5 Feb 5, 2001
6 Feb 6, 2001
6 Feb 7, 2001
6 Feb 8, 2001
6 Feb 9, 2001
6 Feb 10, 2001
6 Feb 11, 2001
... And so on ...
To get the average per month I'd use a temporary table (a table you create
just for this report or that is always there but empty except during this
report)
/* start with an empty table */
CREATE TABLE TotalByDay
( total INT,
dayID INT)
/* --- or --- */
TRUNCATE TABLE TotalByDay
/* now fill it... */
/* notice it the same SQL but I've gone back to */
/* dayID (the INT) not the full date and I've done */
/* the whole year so we can average per month */
INSERT INTO TotalByDay
SELECT count(*), dayID
FROM breeding, DateTable
WHERE paired = dayID
AND split = dayID
AND year = 2001
GROUP BY dayID
ORDER BY dayID
/* OK almost there ... Now to average by month */
SELECT ave(total), month, year
FROM TotalByDay, DateTable
WHERE TotalByDay.dayID = DateTable.dayID
GROUP BY month
ORDER BY month
Now you've got output like
5 1 2001
6 2 2001
6 3 2001
7 4 2001
12 5 2001
11 6 2001
11 7 2001
7 8 2001
7 9 2001
9 10 2001
9 11 2001
9 12 2001
From here you convert the month INT back into a name.
Feel free to writ back with any questions (it's headdy stuff) also I don't
have a mySQL db handy to check the syntax against - hopefully I've
remembered everything correctly. The concept is sound, we do this all the
time.
The got ya's are the DateTable must be full (don't fill it to the end of
this year and forget about it - next year your reports will be wrong)
Frank
Ouch, I'm getting a bit lost. I need to count how many pairs were breeding
on a given day, and then average over the month. Playing with mysql I get
for say the 1st day of the year:
select count(*) from breeding
where TO_DAYS(paired) = TO_DAYS('2002-1-1')
AND TO_DAYS('2002-1-1') = TO_DAYS(split);
That will give me the number of pairs breeding that day. Then I would have
to loop through the year and keep track of number of pairs, days of months
etc to get the monthly
Re: [PHP-DB] how to reverse a hudge multidimensional array?
[5] : 5 (max length with a 1 million row table about 25 indexes, so this array should be quite small) =...alternatively, change the SQL (above) to SELECT...INTO a temporary table. Then perform another SELECT...COUNT()...GROUP on that resultset to get a result that lists the number of occurences, and the number of naffair values/couples that occur with that frequency. This is a situation where sub-SELECTs would be a valuable feature! Just to go to the very end of my need, even if its not useful: then i divide the values by their index (i gonna tell you what for) so the final result is : $suite[1] : 2 /1 2 $suite[2] : 6 /2 3 $suite[3] : 3 /3 1 $suite[4] : unset / $suite[5] : 5 /5 1 (as the previous array was small this one is too ;o)) =I think I got lost in this last step... Well you know everything : now it will be easier for me to tell you what i want to calculate : THE NUMBER OF COUPLES of naffaire/ncpte that have an occurence of 1, that have an occurrence of 2, of 3, of 4 =continuing on with the second SQL, to achieve this, use ORDER BY to sort the results. so thereare only 2 long processes : the while loop (it won t be much shorter if i use a for loop) and the array_count_values (this one is a single instruction so i trust php developpers for the optimization of their code ;o) ) =whereas for processing large volumes of data, I would prefer to rely upon the RDBMS - and remember, a temporary table should be constructed entirely in RAM (just like a PHP array) so there's no disk-speed/time penalty to be paid! You will have understand my prob now : the very very big loss of time in my algorythm is the while loop... That s why i would like to do the same whithout using any kind of loop before the array_count_values, before this instruction, the array is far too big. I would win a lot of execution time help please i m sure there must be a solution but i m lost... =is the db on an Internet-accessible server that I could reach? =Whats the number of distinct naffaire values? How many different frequencies result? ie How many records/array elements do you have returned at each step? Hope i managed to be clear. =see comment above. =Regards, =dn Thanx for your help Etienne - Original Message - From: DL Neil [EMAIL PROTECTED] To: Etienne DURAND [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 31, 2002 11:47 PM Subject: Re: [PHP-DB] how to reverse a hudge multidimensional array? Bon soir Etienne, Like I say, Inverting an array is a chore, it's far easier to invert the pointers... but if you must do it:- For loop to control the first dimension using $i For loop to control the second dimension using $j $temp = $array[ $i ][ $j ]; $array[ $i ][ $j ] = $array[ $j ][ $i ]; $array[ $j ][ $i ] = $temp; Ok? =dn thx a lot for your answer, in fact i only have to return one of the 15 datas, so it makes one column... anyway, i already coded a solution with a while. My question was just to know if it was possible to do it without repeating 100 times the same processing. In fact i begin to believe that it is not possible to reverse the 2 dimensions of an array... If you have a solution for me i m interested, even if it takes a lot of processor ressources because i will have a dedicated server and there wont be more than 30 users, not at the same time (that s for a stat intranet)... just to correct my unprecision again, there will be 1M datas to process not 15 because the array i ve to process is the result of my mysql query and that this query will only return a single column of datas (count(*) ) Helll :) Etienne - Original Message - From: DL Neil [EMAIL PROTECTED] To: Etienne Durand [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 31, 2002 10:44 AM Subject: Re: [PHP-DB] how to reverse a hudge multidimensional array? Etienne, Well i hope someone will be able to give me a solution... Here s my problem: I'm working with a hudge mysql table (about 15 columns and 100 rows...) in fact i ve got to count the number of couples `ncompte`/`naffaire` in the table and finaly calculate the number of couples that appear once, twice here is the query i do: $result = mysql_query(SELECT count(*) FROM datas GROUP BY `ncompte`, `naffaire`); its result like something like this: count(*) naffaire ncompte 4 affaire1 compte1 4 affaire2 compte2 1 affaire3 compte3 2 affaire4 compte4 1 affaire5 compte5 (plus many more) my final result should
Re: [PHP-DB] how to reverse a hudge multidimensional array?
Etienne,
Well i hope someone will be able to give me a solution...
Here s my problem:
I'm working with a hudge mysql table (about 15 columns and 100 rows...)
in fact i ve got to count the number of couples `ncompte`/`naffaire` in the
table and finaly calculate the number of couples that appear once, twice
here is the query i do:
$result = mysql_query(SELECT count(*) FROM datas GROUP BY `ncompte`,
`naffaire`);
its result like something like this:
count(*) naffaire ncompte
4 affaire1 compte1
4 affaire2 compte2
1 affaire3 compte3
2 affaire4 compte4
1 affaire5 compte5
(plus many more)
my final result should be:
$result[1] :2
$result[2] :1
$result[4] :2
I manage to do this quite easily but i use a while instruction...
while($row = mysql_fetch_array($result)) {
array_push($suite, $row[count(*)]);
}
As my table is very long the while takes a lot of time to complete...
So my question is : Is there a solution to return my array as follow :
1 2 3 1 4 7
4 5 6 ===2 5 8 ???
7 8 9 3 6 9
It would allow me to not have to use this long long while
So if someone could telle me how to modify my query or what instructions to
use to do that...
=there are various functions in PHP to 'reverse' arrays, but I must confess that I
have never used them.
Regardless, if there is one to suit your purpose, it will surely consume CPU time to
achieve the swap-over of
15M items.
=your example my final result should be: talks of enumerated arrays, so I shall
assume this is the way you
always use them.
=you want to somehow achieve:
array[1][1] = array [1][1];
array[1][2] = array [2][1];
array[1][3] = array [3][1];
etc
(you know that you can't do the above, right!?)
=thereafter the array would be processed by using a mechanism such as two nested FOR
loops to iterate through
the row/column elements of the array, eg
for ( i=1; i15; i++ );
for ( j=1; j100; j++ );
process( array[i][j] );
etc
=can you leave the array where it is, and adjust the way the iterations are managed?
Instead of proceeding
methodically by counting 'up', count 'down', eg
for ( i=15; i0; i-- );
for ( j=100; j0; j-- );
process( array[i][j] );
etc
=Regards,
=dn
--
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] Date operations.
Garry,
CREATE TABLE breeding (
rec SMALLINT(4) UNSIGNED AUTO_INCREMENT,
sire SMALLINT(4) UNSIGNED,
dam SMALLINT(4) UNSIGNED,
paired DATE,
split DATE,
num_offspring TINYINT(1) ZEROFILL,
PRIMARY KEY (rec)
);
First, we define if a pair was breeding for a given date by seeing if that
date lies between the paired date and the split date. In mysql:
SELECT FROM breeding WHERE TO_DAYS(paired) = TO_DAYS('$given_date') AND
TO_DAYS('$given_date') = TO_DAYS(split);
=This seems part of the problem (as well as part of the solution). Procedural logic
suggests that we should
iterate through the days of the month, taking note of the facts, and then perform the
statistical calculation at
the end. Relational logic doesn't work like this, it requires us to process through
the data in the table(s).
This table is not organised by date, indeed it notes periods of time, which are
notoriously problematic to sort
into a 'date order'! However all is not lost...
There is no need to use TO_DAYS() because paired and split are both
dates so you can operate on them directly. I recommend that you take a
look at the manual's section on Time and Date Functions.
You are right. I works without the TO_DAYS(). But I did get them from the
manual :-)
=thanks for the note. It's difficult to know whether to risk insulting someone by
saying RTFM!
=ironically I think you're going to end up right back there, but other functions of
interest may include Month,
MonthName, Extract, Date_Sub, and even Timestamp.
=general advice would be to define the dates in PHP (with MySQL in mind), but keep all
the date processing in
MySQL (if at all possible).
Where it gets messy is this has to be put into a loop, between the given
start and end dates (year and month only). For each day in the loop, I
have to do a query and count the number returned to php. Then average that
figure for the month.
So if I understand correctly, you are thinking in terms of a 'result
table' which would (ideally) have the days 1-28/29/30/31 in the
left-hand column, and the number of pairs who were put together on
that day (paired = day/date = split). Then at the bottom you want to
sum the right-hand column and divide it by the number of days in the
month to give some sort of average mating possibilities per day. Does
that sound right?
Basically for statistical reasons I need average pairs per month, just a
single number. But the months/years to be queried on would be input via a
form. I was thinking of generating the data on-the-fly, but as you suggest
above and from something Frank Flynn suggested, I would be better off
having a separate table with monthly results. They could all be
pre-calculated except the current month.
=I noted Frank's suggestion. Obviously no one wants to duplicate 'the calendar'
unnecessarily, but by having a
calendar table, it does open up the possibility of processing through that table (from
start-date to end-date)
and using relational logic to extract (date) 'related' rows from the breeding tbl. In
many ways, when I have
finished, you will recognise that there is some attraction to that approach!
=You may have missed one of the points that I was making: what is the math behind this
calculation? I'm going to
set up some sample data/an approach, but only talk about a 'week' rather than a
'month' for brevity/ease of
comprehension... So if we have breeding pairs A through E, the breeding tbl
(representative) looks like:
sire dam paired split
Am Af Mon Fri
Bm Bf Tue Thu
Cm Cf Tue Fri
Dm Df history Sun
Em Ef Sat future
where m=male, f=female, and we're only interested in the period Mon-Fri, so rows D and
E are included only to
show exclusion (yes, that last clause does make logical sense...I think - they're
included only so that they can
be excluded!)
=Our first thought is to think in terms of say Monday, and to scan the breeding tbl
to count how many pairs
were breeding that day, and make a note of the count; then move to Tuesday and do
the same; finally summing
and averaging.
=Thus 'temporal logic' (working one day at a time) says:
Mon = 1 pair
Tue = 3 pairs
Wed = 3 pairs
Thu = 3 pairs
Fri = 2 pairs
Sum = 12
nDays = 5
Avg = 2.5
=Now, crucial question: have I understood the logic of the computation correctly? I
shall blythly carry on
regardless...
=Ok, so now we're in a quandry because constructing a SQL statement to look at Monday,
the Tuesday, ... is not
possible - enter Frank (trumpet calls offstage, left).
=However there's more than one way to skin a cat, and according to my (admittedly not
fantastic) math ability,
we could arrive at the same numbers by coming at things the other way around! Let's
count the number of days for
which a pair were breeding:
A = 5
B = 3
C = 4
D = ?
E = ?
Sum = 12
nDays = 5
Avg = 2.5
Proceed directly to Go!
=Let's ignore the ? rows for a
Re: [PHP-DB] how to reverse a hudge multidimensional array?
Bon soir Etienne,
Like I say, Inverting an array is a chore, it's far easier to invert the pointers...
but if you must do it:-
For loop to control the first dimension using $i
For loop to control the second dimension using $j
$temp = $array[ $i ][ $j ];
$array[ $i ][ $j ] = $array[ $j ][ $i ];
$array[ $j ][ $i ] = $temp;
Ok?
=dn
thx a lot for your answer,
in fact i only have to return one of the 15 datas, so it makes one column...
anyway, i already coded a solution with a while. My question was just to
know if it was possible to do it without repeating 100 times the same
processing.
In fact i begin to believe that it is not possible to reverse the 2
dimensions of an array...
If you have a solution for me i m interested, even if it takes a lot of
processor ressources because i will have a dedicated server and there wont
be more than 30 users, not at the same time (that s for a stat intranet)...
just to correct my unprecision again, there will be 1M datas to process not
15 because the array i ve to process is the result of my mysql query and
that this query will only return a single column of datas (count(*) )
Helll :)
Etienne
- Original Message -
From: DL Neil [EMAIL PROTECTED]
To: Etienne Durand [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, January 31, 2002 10:44 AM
Subject: Re: [PHP-DB] how to reverse a hudge multidimensional array?
Etienne,
Well i hope someone will be able to give me a solution...
Here s my problem:
I'm working with a hudge mysql table (about 15 columns and 100
rows...)
in fact i ve got to count the number of couples `ncompte`/`naffaire` in
the
table and finaly calculate the number of couples that appear once,
twice
here is the query i do:
$result = mysql_query(SELECT count(*) FROM datas GROUP BY `ncompte`,
`naffaire`);
its result like something like this:
count(*) naffaire ncompte
4 affaire1 compte1
4 affaire2 compte2
1 affaire3 compte3
2 affaire4 compte4
1 affaire5 compte5
(plus many more)
my final result should be:
$result[1] :2
$result[2] :1
$result[4] :2
I manage to do this quite easily but i use a while instruction...
while($row = mysql_fetch_array($result)) {
array_push($suite, $row[count(*)]);
}
As my table is very long the while takes a lot of time to complete...
So my question is : Is there a solution to return my array as follow :
1 2 3 1 4 7
4 5 6 ===2 5 8 ???
7 8 9 3 6 9
It would allow me to not have to use this long long while
So if someone could telle me how to modify my query or what instructions
to
use to do that...
=there are various functions in PHP to 'reverse' arrays, but I must
confess that I have never used them.
Regardless, if there is one to suit your purpose, it will surely consume
CPU time to achieve the swap-over of
15M items.
=your example my final result should be: talks of enumerated arrays, so
I shall assume this is the way you
always use them.
=you want to somehow achieve:
array[1][1] = array [1][1];
array[1][2] = array [2][1];
array[1][3] = array [3][1];
etc
(you know that you can't do the above, right!?)
=thereafter the array would be processed by using a mechanism such as two
nested FOR loops to iterate through
the row/column elements of the array, eg
for ( i=1; i15; i++ );
for ( j=1; j100; j++ );
process( array[i][j] );
etc
=can you leave the array where it is, and adjust the way the iterations
are managed? Instead of proceeding
methodically by counting 'up', count 'down', eg
for ( i=15; i0; i-- );
for ( j=100; j0; j-- );
process( array[i][j] );
etc
=Regards,
=dn
--
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 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] Date operations.
Wow Garry, I know that I like to kick-start my brain by getting 'into' a list problem/challenge in the (London) mornings, but today I'm completely beaten. Thanks for the broad explanation, which is probably quite meaningful, but I'm not into breeding (could that statement be misunderstood?) and so don't understand the terminology, but let's soldier on... Can I request some db-speak please? You already have a tbl (or more). So let's start by asking for the table definitions Do you already have some SQL as a 'first attempt'? Could you also post that, together with a note about its short-comings/what needs to be added? That would also assist (my, feeble) comprehension. Usually in this sort of problem, the trick is to work out how to order/group the data, and then bearing in mind the various table-relationships, organise the join(s) and apply the requisite statistical functions. (that's the fun part) (if you haven't got anything started yet) Along with the definitions, how about starting a SELECT by listing the data you want to see, filling in the FROM clause, and then skipping to the WHERE clause and putting in the last or last-two criteria, eg the year/date-range to be used in the analysis - well do what you can/makes sense to you, so far. That might be enough to 'inspire' a solution - or start us on the way... Please advise, =dn Hi All, I have a problem working out a suitable algorithm either in PHP or MySQL. Basically I have a DB that keeps track of breeding records. Each record has a paired data, and a split-up date. I need to generate some statistics to work out average numbers of pairs per month, averaged on a daily basis, for a given start and stop date, typically a year or year-to-date. All the algorithms I can think of are messy, where I have to loop through all the breeding records for every day of the year, and count how many pairs are breeding by seeing if the date is between the start and stop dates, and then average that on a monthly basis. I can't see that scaling very well, as there might be several hundred breeding records for a given year, multiplied by 365 days. Has anyone any hints/pointers for an efficient way to do this? Regards, Garry. -- 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 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] RE: ensuring unique field value in MySQL using PHP
Janet,
MySQL (and indeed all multi-user databases) has a feature called Locking. What this
means is that whilst many
users/clients may access a database, apparently simultaneously, when one (or more) is
updating the data in some
way, everyone else is kept locked-out for the duration. Hopefully the period of time
required is so short that
other users don't particularly notice.
For example, let's say we have a joint bank account. The bank db will maintain a
balance figure (say 100). If
I'm at one branch of the bank and ask for the balance, it will be given. If at the
exact same moment in time,
you are at another branch, the SAME number will also be given to you. Now let's get
complicated. Having worked
out that there is some money, let's say I raid the piggy-bank and ask for 75. The bank
computer will say 100
less 75 leaves a balance of 25 and the teller will give me my loot. However if again,
at exactly the same point
in time) you try to withdraw (a more modest, caring and sharing) 50. If your teller's
computer reported a
balance of 100, and you got the 50, and the balance was updated to 50, what would
happen? I don't know about
you, but I don't often get the better of banks... Strangely enough, in database theory
this is called the
banker's problem.
So, when two tellers ask to update an account balance, only one will be given the
'lock' - the other will be
momentarily 'locked out'. MySQL is responsible for this timing/choice. (it's one of
the management parts of
DBMS) The other user/computer is locked out, and in certain situations can figure
that out. So what happens
next is that your teller does not subtract 50 from 100, but subtracts 50 from the
remaining balance, eg
UPDATE...SET balance = balance - 50 WHERE a/c nr=... (not SET balance = 100 - 50 ) at
which point in time you
get embarrassed by the teller, and I get into REALLY hot water!
I have really quick reactions: at the first sign of trouble I run away!
So yes it is possible that two of your clients will press 'submit' at the same moment
in time, but when the
processing scripts hit the database, the RDBMS will using a 'lock' to prioritise (even
inventing a priority if
necessary) one over the other without any intervention from you. You have nothing to
worry about (until you let
me operate your bank account).
Incidentally the 'level' at which a lock is applied varies from DBMS to DBMS. MySQL
'only' has table-level
locking. This means no one else can use a table whilst one user is updating. Depending
upon transaction
rates/response time requirements, the mix of transactions in the system, and the size
of the table(s); this
might be a problem (eg for our mythical bank). Other DBMS' allow locking right down to
the row level. However
locking takes time, and so imposes a speed penalty. MySQL is built for speed, doesn't
pay a high 'penalty', and
in this way gets away with higher level/more widely imposed locking. There is no one
'correct' answer to this
conundrum despite the widespread criticism/fear (or even FUD) - everybody's mileage
may vary!
Does that help to clear things up?
=dn
- Original Message -
From: Adam Royle [EMAIL PROTECTED]
To: Janet Valade [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 29 January 2002 03:49
Subject: [PHP-DB] RE: ensuring unique field value in MySQL using PHP
Checking a small database for username/password combination would happen so
quick, it would be nearly impossible for two usernames to be entered in.
Your script should work properly, but to make sure no duplicates are
entered, you can change the column definition using the ALTER columnName
command to make sure there are no duplicates. Look in the mySQL
documentation (www.mysql.com) to find the correct command.
Adam
-Original Message-
From: Janet Valade [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 29, 2002 1:24 PM
To: [EMAIL PROTECTED]
Subject: ensuring unique field value in MySQL using PHP
I have a form in which users create their own login name. The code for
storing the login name they created does something like the following:
$userlogin is the name the user typed in the form
$sql = select loginname from login where loginname='$userlogin';
$result=mysql_query($sql);
$num=mysql_num_rows($result);
if ($num 0)
{
echo Sorry, that login name is already taken, Try another;
shows form again;
}
else
insert new record into database;
echo okay, your new account is added;
I am wondering if it is possible that two people could hit the submit button
at the exact same time with the same login name so that the same login name
could get entered twice? Seems unlikely that this would happen by accident,
but is this something a hacker could do on purpose?
Thanks,
Janet
--
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
Re: [PHP-DB] UPDATE query to add to a field
Hello Adv. Systems Design, I need to be able to update a field in MySQL, the catch is that I have to add on to text that is already there and I have to be able to do it within MySQL (phpMyAdmin). My first idea was to do: SET prod_desc = prod_desc + more info to tack on to end but this is a mathematical function which returns 0! Any ideas? SET prod_desc = CONCAT( prod_desc, more info to tack on to end ) Manual: 6.3.2 String Functions Regards, =dn -- 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] counting with CURDATE() - please help!
Hello Sander, I want to add a variable numer of months to the current date CURDATE(). I thought it should be something like this: select (MONTH(CURRENT_DATE)+MONTH(2)) but i get NULL as a result. The purpose of this it to check which records with a experation date is earlier or later than today+2 months later. Can somebody please tell me how to do this? Would MySQL's ADD_DATE() suit? =dn -- 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] problem with LOAD DATA INFILE for date format
Hi Hendra An alternative would be to:- 1 LOAD DATA the table, but format each date field as a string. 2 add a date column 3 use UPDATE to convert/re-format the data from the string to date field 4 remove the string column This solution does not require you to tackle another tool and face the extra automated format changes; everything is within MySQL and can be visually scanned/carefully checked stage-by-stage. Regards, =dn There may be a better way, but in migrating from Filemaker to MySQL, I encountered similar problems. My solution was to pass the data through Excel and use that to convert the dates into the required format (CTRL-1, Custom, -mm-dd). Of course, you ned to watch out for those nasty ' ' ' that Excel puts around any cell containing a comma. So you might need to filter the output from excel in a text editor too but if its a one-off, its worth it. I have a sample data like below in text file (policy.txt). PolicyNo,DateOfBirth,PaidToDate 0003573607, 9/25/1973, 8/27/2001 708802,11/26/1959, 5/25/1998 0002776507, 3/19/1973,11/18/1999 0002776703, 3/13/1969,11/18/1999 Policy table structure: +---+-+ | Field | Type| +---+-+ | PolicyNumber | char(10)| | DateOfBirth | date| | PaidToDate| date| +---+-+ When I do: LOAD DATA INFILE policy.txt INTO TABLE Policy FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n' IGNORE 1 LINES; All data in field DateOfBirth PaidToDate become -00-00 I notice this is because mysql only accept date format in -mm-dd. However, in my text file, the date format is in mm/dd/ format. -- 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] # of Records in Table
Zach, What syntax can I use to determine how many records are in a MySQL table and then retrieve the value of the field password for the last record? I tried using some combinations of COUNT(*) and LIMIT with no success. SELECT COUNT(*) will answer the first part. What did you try that lacked success? How do you define last record? We might need to see the table definition to answer this. Regards, =dn -- 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] # of Records in Table
Zach, How can this last record be identified? Is there a key field or something similar? Again: We might need to see the table definition to answer this. Please advise, =dn - Original Message - From: Zach Curtis [EMAIL PROTECTED] To: DL Neil [EMAIL PROTECTED] Sent: 23 January 2002 20:49 Subject: RE: [PHP-DB] # of Records in Table The last record should be whatever the last record is in the table. For example, if there are 1000 records in the table which COUNT(*) will tell me, how can I get the password for record 1000. The only thing I can do that would probably work is two query statements, one selecting COUNT(*) and the other selecting password. What I don't like about this method is that I would have to retrieve all of the passwords into an array, which I would assume would be slow on larger tables. Ideally, I thought their would be a way to do this operation in one select statement. Any thoughts? Zach -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 23, 2002 1:11 PM To: Zach Curtis; [EMAIL PROTECTED] Subject: Re: [PHP-DB] # of Records in Table Zach, What syntax can I use to determine how many records are in a MySQL table and then retrieve the value of the field password for the last record? I tried using some combinations of COUNT(*) and LIMIT with no success. SELECT COUNT(*) will answer the first part. What did you try that lacked success? How do you define last record? We might need to see the table definition to answer this. Regards, =dn -- 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] # of Records in Table
Zach, We're going back and forth on this and getting no where... So far all you have informed us is that the table has one column, which contains a bunch of passwords (I also suggest that this is not really the case and there'll at least be some sort of userId field - but you haven't bothered to tell me/us that) The concept of last in relational terminology is ambiguous. Do you mean last entered, or do you mean the row with the field containing the highest value in the column - for example. Some people are used to the idea that there is also some 'phantom' rowId that counts/labels each row. In other file systems this might have been the case, but the physically last record in an RDBMS table-file may not conform to either of the last definitions mentioned above. There is no such 'highest' in an RDBMS unless you put it there - as mentioned by another correspondent, it's a good idea to use some sort of 'id' field in every table. This can be generated for you with the AUTO_INCREMENT feature. With appropriate design, eg first record has id=1 and the succeeding records have id-s with ascending values, then you can use SELECT MAX() against the 'id' or possibly against the AUTO_INCREMENT feature. These commands are well documented - RTFM. Many tutorials with plenty of examples of authentication techniques exist on the PHP/MySQL web sites. Next time, please don't be so (repeatedly) parsimonious in the provision of information, if you expect/hope that someone is going to give their (free) time to help you out. (grumble) =dn - Original Message - From: Zach Curtis [EMAIL PROTECTED] To: DL Neil [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: 23 January 2002 21:52 Subject: RE: [PHP-DB] # of Records in Table The key field is the password field, which is also the field that I would like to retrieve for that last record. Zach -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 23, 2002 2:08 PM To: Zach Curtis Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] # of Records in Table Zach, How can this last record be identified? Is there a key field or something similar? Again: We might need to see the table definition to answer this. Please advise, =dn - Original Message - From: Zach Curtis [EMAIL PROTECTED] To: DL Neil [EMAIL PROTECTED] Sent: 23 January 2002 20:49 Subject: RE: [PHP-DB] # of Records in Table The last record should be whatever the last record is in the table. For example, if there are 1000 records in the table which COUNT(*) will tell me, how can I get the password for record 1000. The only thing I can do that would probably work is two query statements, one selecting COUNT(*) and the other selecting password. What I don't like about this method is that I would have to retrieve all of the passwords into an array, which I would assume would be slow on larger tables. Ideally, I thought their would be a way to do this operation in one select statement. Any thoughts? Zach -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 23, 2002 1:11 PM To: Zach Curtis; [EMAIL PROTECTED] Subject: Re: [PHP-DB] # of Records in Table Zach, What syntax can I use to determine how many records are in a MySQL table and then retrieve the value of the field password for the last record? I tried using some combinations of COUNT(*) and LIMIT with no success. SELECT COUNT(*) will answer the first part. What did you try that lacked success? How do you define last record? We might need to see the table definition to answer this. Regards, =dn -- 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 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] Looping advice sought
George, I am about to start on a script page which will loop thru a MySQL table and update records depending on the actions of the user. I propose to loop through, row by row performing a separate UPDATE statement for each row, unless someone can advise me of a better way (2d array?). It would be more efficient to go to the database once, than to make multiple calls. However you are talking UPDATE (also applies to DELETE) and therefore due care and attention must be applied. UPDATE tblNm SET iq=0 WHERE teamsupported=Rangers; is likely to affect numerous records (to say nothing of your health!). Whereas if you only want to reflect a change in a single customer's order record UPDATE tblNm SET source=someuniversitylibrary WHERE requestId=1678 and component=part A; So to achieve a more useful answer can you expand on the description of the transaction and/or actions of the user - or maybe some pseudo-code, please? =dn -- 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] Re: Looping advice sought
George,
Please check out 6.4.5 UPDATE Syntax!
Rather than a complex series of partly nested IF statements, can you rationalise the
structure to a
SWITCH-CASE? - and with neater (than email) formatting improve
readability/comprehension as well?
$i=1;
while $i($howmany + 1)
{
$activity=($activity . $i);
$tr_id=($tr_id . $i);
switch ( $activity )
{
case '' : break; // do nothing
case 'Delete' : FnDeleteUpdate( 'Withdrawn' ); break;
case 'Submit' :FnWholePackUpdate( etc); break;
case 'Decline' :fnSingleUpdate( 'Declined' ); break;
case 'Withdraw' :FnSingleUpdate( 'Withdrawn' ); break;
default : DisplayToLog( '***Error' ); exit; //or break
// at the moment this condition is do nothing
} // end of switch activity
if ( $submit=='Submit Whole Pack') FnWholePackUpdate( etc);
// note this logic is not quite the same as yours - thus may not be appropriate
mysql_query($uquery1);
// or it might be tidier to include the MySQL call in the functions
Move:
$uquery1 =update Transactions ;
$uquery1.=SET (PHEIAction, PHEIAccepts)
$uquery1.=VALUES
$uquery1.=('$activity', '$d1')
$uquery1.= WHERE RecID= '$tr_id'
into FnSingleUpdate
Move:
$uquery1 =update Transactions ;
$uquery1.=SET (PRequestsubmitted, Packorder)
$uquery1.=VALUES
$uquery1.=('$d1', '$Packorder')
$uquery1.= WHERE RecID= '$tr_id'
into FnWholePackUpdate
Move:
$uquery1 =update Transactions ;
$uquery1.=SET CourseID
$uquery1.=VALUES
$uquery1.=0
$uquery1.= WHERE RecID= '$tr_id'
into FnDeleteUpdate
I actually prefer to space out the statements in a CASE, but as these are simple the
one-liners may be more
readable. Similarly I tend to space the separate clauses of a SQL query over several
lines, but you may find it
easier to put the SQL statements onto a single line, eg
$uquery1 =update Transactions SET CourseID = '0' WHERE RecID= '$tr_id' ;
- easier to read/visualise(?), you'll remove the risk of forgetting to put in spaces,
but you'll still have to
watch the convention of which quotation marks to use when and around PHP variables...
NB assuming CourseID is a string (and not an integer - quotes not nec).
NBB are we missing the end of the while loop?
Regards,
=dn
--
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] theme header query
Tables should be opened and closed - and (please) code should be readable! =dn - Original Message - From: Barry Rumsey To: [EMAIL PROTECTED] Sent: 21 January 2002 17:54 Subject: [PHP-DB] theme header query I'm trying toadd thefollowing codeto atheme: .td class=topmenu width='73' height='19' bgcolor='#ff'\n .$sqlhostname = localhost; $login = ; $password = ; $base = xplyrics; $db_connect = mysql_connect($sqlhostname,$login,$password); $base_selection = mysql_select_db($base,$db_connect);$query = SELECT COUNT(*) FROM xp_topics WHERE artist='artist' ORDER BY topicid DESC LIMIT 1; $numartist = mysql_query($query) or die(Select Failed!); $numartist = mysql_fetch_array($numartist); bTotal Artists:/b $numartist[0]br; /td\n But when I try and load that page it comes back blank. Am I missing something or it just wont work. IncrediMail - Email has finally evolved - Click Here
Fw: [PHP-DB] RE: LIMIT and get_num_rows
Apologies to the original post-er, I see that I was getting ahead of myself... http://www.mysql.com/doc/N/u/Nutshell_Other_features.html 1.5.4 Other Features Available From MySQL 4.0.0 ... Functions like SQL_CALC_FOUND_ROWS and FOUND_ROWS() makes it possible to know how many rows a query would have returned without a LIMIT clause. FYI/Coming soon/watch this space! Regards, =dn - Original Message - From: DL Neil [EMAIL PROTECTED] To: matt stewart [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 14 January 2002 14:17 Subject: Re: [PHP-DB] RE: LIMIT and get_num_rows Have you (original post-er) tested performing a SELECT ... LIMIT 0, 20 and then calling mysql_num_rows()? Does it return 20 (the LIMIT) or the number of rows SELECT-ed before/without regard to the limit? =dn - Original Message - From: matt stewart [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 14 January 2002 14:05 Subject: RE: [PHP-DB] RE: LIMIT and get_num_rows you've assumed correctly - so it's definitely faster to do the two queries? okey dokey, then that's the method i shall use. thanks to everyone who's added their thoughts! -Original Message- From: Peter Westergaard [mailto:[EMAIL PROTECTED]] Sent: 14 January 2002 14:01 To: [EMAIL PROTECTED] Subject: [PHP-DB] RE: LIMIT and get_num_rows but presumably if i'm doing a SELECT count(*) FROM Designs WHERE Keywords LIKE %sport% followed by a SELECT * FROM Designs WHERE Keywords LIKE %sport% LIMIT 0,20 it's still using nearly as much processing time as just returning all the designs and just using a get_num_rows and then only using the first 20? I absolutely think it will not. After all, the table record count should be able to reference the index, (you are running this query against an indexed 'Keywords' column yes?), while assembling the table of returned data will take quite a bit more memory, which is what you're really trying to cut down, I think. Cheers, -Peter -- 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] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.313 / Virus Database: 174 - Release Date: 02/01/02 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.313 / Virus Database: 174 - Release Date: 02/01/02 -- 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 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 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/msql
Hi Mike
while ($row = mysql_fetch_array($result)) {
echo trtd.$row[0]./td\n;
echo td.$row[1]./td\n;
$formatted=sprintf($%s,$row[2]);
echo td $formatted /td/trbr;
}
I get an error in the last line where I want to put $formatted into the last
cell of a table
if I // echo $formatted without the HTML tags it puts it at the top of the
page.
The error is : Parse error: parse error, expecting `','' or `';''
also: what does the \n mean at the end of the html tags?
I am new to PHP and have no seen that before.
Welcome to our little band!
Tuxedo Junction does that mean that there is a dress code on the Internet in Canada?
The echo command outputs one or more strings:
http://www.php.net/manual/en/function.echo.php.
If you have more than one string, they must be separated with commas.
Alternatively you could join the three strings together using the concatenation
operator:
http://www.php.net/manual/en/language.operators.string.php
The \n question (newline character) is answered/illustrated in the first link above
(and many other places -
try throwing it into the manual's search facility).
You sound as if you're just starting out. There are many PHP/MySQL tutorials on the
web. Start at the host sites
and check out links, otherwise chuck a few of the key words at Google.
Regards,
=dn
--
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] Querying MySQL using PHP - modify existing query
Andrea,
I am a serious newbie (like less than two weeks never programmed before),
so I hope my code doesn't suck too bad this isn't a really bad question...
but here goes...
I would like to modify this query so that it doesn't return the username,
realname, and office results each time. The way the query is now, I see the
username, realname office results duplicated for each 'alias' result that
is in the data. Instead, I'd like to see the username, realname office
only once a list of the associated 'alias' (es) underneath it. If anyone
can help w/ some semi-real code, that would be great. Since I'm new, it
really helps to have code that actually reflects my field names so that I
can learn from it. I appreciate any help you can provide. Thanks in
advance! ~Andrea
$query = select users.username, users.realname, users.office,
users.location, users.office_phone, users.alt_phone, users.password,
alias.alias from users, alias
where users.username like '%.$searchterm.%'
and users.username = alias.username;
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo pNumber of Users found: .$num_results./p;
for ($i=0; $i $num_results; $i++){
$row = mysql_fetch_array($result);
echo pstrong.($i+1).. Username: ;
echo htmlspecialchars( stripslashes($row[username]));
echo brReal Name: ;
echo htmlspecialchars( stripslashes($row[realname]));
echo brOffice: ;
echo htmlspecialchars( stripslashes($row[office]));
echo brAlias: ;
echo htmlspecialchars( stripslashes($row[alias]));
With respect to your request for code, in fact you need to take two steps: 1 to
understand the program/script
logic required to achieve your aims, then 2 code that logic in PHP. One of the tools
for the first step is
pseudo-code. This offers an answer to your (logic) question in pseudo-code (with
regrets that lack of time
precludes the next step):
If you think about it, your display has (probably) a main heading, and maybe a footer.
What lies in between
(apart from white space) is groups of lines, where each group is defined/identified by
the username (etc). You
want to display the groups so that the first line identifies the group, and any
ensuing lines within the group
do not duplicate the information.
So think about writing the first line in a group. Then how would you display the
ensuing lines in each group?
How would you know if the next line belongs with the same group, or starts a new one?
Apart from ensuring that
you use all of the rows in the resultset (and no more!?) that's about the nub of it...
retrieve first row from resultset
while not eod
note group's username
display (full) line
retrieve next row
while line's username = group's username and not eod
display line (without username etc)
retrieve next row
end while line
end while not eod
NB eod = end of data
(rather than working of num-rows it works off the boolean response of a call to
retrieve from the resultset, eg
mysql_fetch_array())
Regards,
=dn
--
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] Logicproblem in WHERE statement
=An alternative approach:- I am building a user search engine. Now I do not know how to gett following: a mandatory field and a voluntary field For example I would like to get all female users and those who are from canada. The results should show all users who are female regardless of nationality But it should also show all users from canada. How would this look like? SELECT * FROM table WHERE sex=1 AND country='CA' This is wrong I know. But how does it work??? SELECT * FROM TABLE WHERE sex = 1 OR country = 'CA'; Will select all users who are female as well as all users from Canada. You may get duplicate records (for people who are female and from Canada) so you may want to use DISTINCT as well. =If all you want to do is to 'highlight' people from Canada, whilst having a query that gives a worldwide resultset, try: SELECT *, IF( country = 'CA', 'Canuck', 'Normal' ) AS highlight FROM table WHERE sex=1; =Regards, =dn -- 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] sorting on the first 8 digits....
John, Ok, I did something stupid and now I am paying the price... =boy am I glad that I'm not the only one in the world! How come the 'people' who extract these prices don't accept Mastercard? In an internal database that tracks the time and date something happens on our site, I mistakenly forgot to use leading zeros in the 24 hour column(s). So, anything that happens between 1-9 am are all short by one digit. Now, I need to create a report that shows these transactions in the order that they happened. The answer to this problem should be as simple as adding a zero to the end of all records that are short one digit. But, for reasons that I won't explain here, that isn't possible. So, my question is this, is there a way to do a database call that would select all the records but allow me to order them based on the first 8 digits only? If you would like an example, I need the following two numbers to come up in the order listed. 20011012182929 200411322 =you don't mention the column format, so from the example data I have assumed that it is integer rather than string (or MySQL timestamp). =as you observe, if the values are sequenced according to string rules, it all works for you (left to right, byte-by-byte comparison), accordingly anything you do to cast the values as strings will solve the problem - until you get across to the hours 'columns'. =so using only the first eight character positions of all the records: SELECT * FROM tblNm ORDER BY LEFT( colNm, 8 ) =this will separate the days neatly, but all of the records for a particular day will be in a non-predictable sequence. =you could get really clever and replace the LEFT() with an IF(): if colNm contains 16 digits then use the (string) value, as is; else if colNm contains 15 digits then use a concatenation of the first 8 character-positions, a zero, and the remaining 7 character positions; else (you have more data integrity problems than your credit limit allows) =the effect of this would be to (logically) restore the missing zeros and sequence the records by day, hour, minute, and second. =Of course, it would be better to do that as an UPDATE to correct your data, and then use the 'normal' SQL query, but you did say... =thanks for this morning's brain-teaser to get the old grey cells lined up... =ok? =dn -- 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]
