[PHP-DB] Problem with file() function
Dear sirs, Please help with the follows:
When I'm trying to execute this code:
$fcontents = file ('http://localhost/');
while (list ($line_num, $line) = each ($fcontents)) {
echo bLine $line_num:/b; . htmlspecialchars ($line) . br\n;
}
it runs perfectly, but when I try to change to:
$fcontents = file ('http://www.php.net/');
while (list ($line_num, $line) = each ($fcontents)) {
echo bLine $line_num:/b; . htmlspecialchars ($line) . br\n;
}
it dosn't work, Can you tell why please...
Best Regards
Hayan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] How to make logout from database server (ORACLE for example) when user closes its session
Do not use persistent connections to the database. Use non-persistent connections and make sure to close them. Persistent connections will linger around for a while then close. Also, check the documentation on oracle concerning persistent connections http://otn.oracle.com . Ray Hunter Firmware Engineer ENTERASYS NETWORKS -Original Message- From: Vlad A. Chernikhoff [mailto:[EMAIL PROTECTED]] Sent: Monday, February 18, 2002 11:10 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] How to make logout from database server (ORACLE for example) when user closes its session Hi all Is it possible to track such situations when http user closes connection to HTTP server to close opened persistent connections to the database? This connections is still active all the time (if using php not as CGI but as module for Apache) and when user trying to login next time PHP uses previously opened (by OCIPLogin for example) persistent connection (the same connection resource) - but this connection may be broken from the server side if its idle time is too long. So I found only way to restart my Apache + modphp from time to time. :( Thanks, Vlad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Problem with file() function
If you are behind a firewall or proxy, you will not be able to connect to
www.php.net. If you are not behind a firewall or proxy and you are getting
php_hostconnect errors then you need to make sure that you can get to the
php.net other than with your browser.
You can also try using fopen() as well...
Ray Hunter
Firmware Engineer
ENTERASYS NETWORKS
-Original Message-
From: Hayan Al Mamoun [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 3:58 AM
To: PHPList (E-mail)
Subject: [PHP-DB] Problem with file() function
Dear sirs, Please help with the follows:
When I'm trying to execute this code:
$fcontents = file ('http://localhost/');
while (list ($line_num, $line) = each ($fcontents)) {
echo bLine $line_num:/b; . htmlspecialchars ($line) . br\n; }
it runs perfectly, but when I try to change to:
$fcontents = file ('http://www.php.net/');
while (list ($line_num, $line) = each ($fcontents)) {
echo bLine $line_num:/b; . htmlspecialchars ($line) . br\n; }
it dosn't work, Can you tell why please...
Best Regards
Hayan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Problem displaying dates
Hi all, I've just moved my MySQL db and php scripts over from W2K to Linux and for some reason my dates are coming across as '-00-00' even though, viewing the db in MySQLFront, they look fine (actual dates). When I hosted this on Win2K it worked fine. I use a function to convert the date format from '-00-00' to 'dd/mm'' but have tried displaying the dates with and without the use of this function with no success. Any suggestions? George in Edinburgh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Problem displaying dates
Mate, what code/function are you using to convert the dates? what do your data look like in the table (structure, examples)? -Original Message- From: George Pitcher [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 8:01 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Problem displaying dates Hi all, I've just moved my MySQL db and php scripts over from W2K to Linux and for some reason my dates are coming across as '-00-00' even though, viewing the db in MySQLFront, they look fine (actual dates). When I hosted this on Win2K it worked fine. I use a function to convert the date format from '-00-00' to 'dd/mm'' but have tried displaying the dates with and without the use of this function with no success. Any suggestions? George in Edinburgh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: mysql_connect() and phpmyadmin
Dear Martin,
Why don't you try this :
?
$link = mysql_connect(localhost,phpcoder_dk,pdw );
if (!$link) {
print(Connection fail);
exit();
}else{
if (!mysql_select_db(phpcoder_dk,$link)) {
print(Error);
exit();
}else{
$query = SELECT * FROM hits;
$result = mysql_query($query,$link);
}
}
?
Please note that the IP address for localhost is 127.0.0.0 not 127.0.0.10.
Regards,
Noppanan Arunvongse Na Ayudhaya
Martin Allan Jensen [EMAIL PROTECTED] wrote in message
005901c1b40d$6143c390$040a@IceSystems">news:005901c1b40d$6143c390$040a@IceSystems...
I just don't understand why phpmyadmin works with it when i can't call
it.! I did try with localhost
but it won't still work..
PLEASE help i just can't understand it
Kind regards
Martin
-Original Message-
From: Martin Allan Jensen [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 12, 2002 10:44 AM
To: Rick Emery
Subject: Re: [PHP-DB] mysql_connect()
It wasen't me hwo did it! but why do phpmyadmin work fine then??
Martin
- Original Message -
From: Rick Emery [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, February 12, 2002 5:29 PM
Subject: RE: [PHP-DB] mysql_connect()
Re-read my previous email. You do not have mysql functionality compiled
into PHP and you web server. The clue is the phrase undefined function:
mysql_connect().
How did you load PHP, MYSQL, etc?
-Original Message-
From: Martin Allan Jensen [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 12, 2002 10:26 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] mysql_connect()
Sorry folks, last time i wrote it to fast! Well here is the hole
story
We got an organisation to install a new version of phpmyadmin, mySQL and
PHP
4.0.6 on our Cobalt Raq 4r server (Linux)
They got it installed and phpmyadmin is working finebut when i call
fro
a script souch as
%
?
// Connecting, selecting database
$link = mysql_connect(127.0.0.10, phpcoder_dk, pdw)
or die(Could not connect);
print Connected successfully;
mysql_select_db(phpcoder_dk)
or die(Could not select database);
// Performing SQL query
$query = SELECT * FROM hits;
$result = mysql_query($query)
or die(Query failed);
?
%
The script writes this.
%
Fatal error: Call to undefined function: mysql_connect() in
/home/sites/site2/web/sql.php on line 3
%
You can test it at http://www.phpcoder.dk/sql.php
I really hope that someone will help me cause i really can't find the
problem by myself...!
Kind regards
Martin Allan Jensen
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] starting odbc for w2k and iis
Hello everybody, i'm new to the list, and i want to know where i get started with and odbc connection to sql server 2k any tutorial or manual, would be appreciated Thanks in advance. -- Eduardo Cancino [EMAIL PROTECTED] --- Coordinador de Desarrollo Web CENTRO DE INFORMACION Y COMUNICACION AMBIENTAL DE NORTEAMERICA A.C. --- www.ciceana.org.mx --- 56 59 05 09 Av. Progreso #3, Col. del Carmen, Viveros de Coyoacan, 04010, Mexico D.F. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] A good tutorial
If anyone knows of one, would someone please point me towards a good tutorial on storing images in a database then fetching them out again. I would really appreciate it and thanks in advance! Jen Downey -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] starting odbc for w2k and iis
Eduardo, Under Windows, you can simple configure a System DSN in the ODBC Administrator, and then use the ODBC functions in PHP: www.php.net/odbc, as ODBC support should be enabled by default. You can also connect to SQL Server on Windows from a *nix platform, using the PHP-ODBC-HOWTO at www.iodbc.org as a starting place. You might want to read it for an ODBC architecture overview even if using Windows - just skip the installation steps. As far as the examples go, they are also transferable (that's the point of ODBC) but you will not need to set any environment variables with a Windows-based PHP client. Best regards, Andrew Hill Director of Technology Evangelism OpenLink Software http://www.openlinksw.com Universal Data Access Data Integration Technology Providers -Original Message- From: Eduardo Cancino [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 10:31 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] starting odbc for w2k and iis Hello everybody, i'm new to the list, and i want to know where i get started with and odbc connection to sql server 2k any tutorial or manual, would be appreciated Thanks in advance. -- Eduardo Cancino [EMAIL PROTECTED] --- Coordinador de Desarrollo Web CENTRO DE INFORMACION Y COMUNICACION AMBIENTAL DE NORTEAMERICA A.C. --- www.ciceana.org.mx --- 56 59 05 09 Av. Progreso #3, Col. del Carmen, Viveros de Coyoacan, 04010, Mexico D.F. -- 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] A good tutorial
It depends on the database that you are using. One of the best databases I have used for storing images has been with oracle. You need to look up the documentation on there web site. http://otn.oracle.com. As for the other databases, I have not used images with them. On oracle I have used BLOBS and CLOBS. BLOBS are binary large objects. You need to be a member of Oracle Tech Network...Here is the url for the LOBs section: http://technet.oracle.com/doc/server.815/a68004/toc.htm Hope this helps... Ray Hunter Firmware Engineer ENTERASYS NETWORKS -Original Message- From: Jennifer Downey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 9:26 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] A good tutorial If anyone knows of one, would someone please point me towards a good tutorial on storing images in a database then fetching them out again. I would really appreciate it and thanks in advance! Jen Downey -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Secure Sybase DB connection
Hi Everyone, I am relatively new to php. I am currently developing a php 4.x application running on an apache 1.3.x web server which connects to a remote sybase SQL ASE 12 server. My main concern is with the command sybase_connect (server, username,password) on the web server. Obviously, the password field is in clear text. In the event of a possible security compromise of the web server (be it through bugs in the O/S, php code or apache server), a hacker may change file permissions and gain access to the php file containing the database password. One machine compromised is bad enough but I would like to protect the integrity of the database server. Is there a way to set up some kind of encryption to protect the database password? Thanks. Dennis -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL Server script conversion tool?
Anyone have a tool that takes a SQL Server generated script and converts it to a mySQL script? -- __ Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Need smoe code examples on creating tables for mysql in php
Need some code examples on creating tables for mysql in php can any one help me on this . I searched the php.net site and only found info on creating a database I just want to create a php script that will make 2 tables with lets say 5 fields each in them.
RE: [PHP-DB] Need some code examples on creating tables for mysql in php
Huh $query = CREATE TABLE mytable (. field1 int,. field2 varchar(50),. field3 decimal(6,2),. field4 int,. field5 int ); mysql_query($query) or die(Error: .mysql_error()); -Original Message- From: CrossWalkCentral [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:26 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Need smoe code examples on creating tables for mysql in php Need some code examples on creating tables for mysql in php can any one help me on this . I searched the php.net site and only found info on creating a database I just want to create a php script that will make 2 tables with lets say 5 fields each in them. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Need smoe code examples on creating tables for mysql in php
Need some code examples on creating tables for mysql in php can any one help me on this . I searched the php.net site and only found info on creating a database I just want to create a php script that will make 2 tables with lets say 5 fields each in them. Hi Maybe you wanna head on over to mysql.com and read the docs over there. This will give you the info you need on working with mysql. also try phpbuilder.com and devshed.com m: -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need smoe code examples on creating tables for mysql in php
Hate to cheat, but have you tried phpmyadmin? It will do a lot of that work for you and show you the SQL statement. I wouldn't rely on it for complex actions, since it doesn't seem to be 100% perfect, but, it'll give you the idea on what you should be looking towards and a starting place to look if you run into problems. I'd also suggest picking up an SQL book. Knowing SQL is very important for writing 'good' PHP / DB apps. -w -- William Fong - [EMAIL PROTECTED] Phone: 626.968.6424 x210 | Fax: 626.968.6877 Wireless #: 805.490.7732| Wireless E-mail: [EMAIL PROTECTED] - Original Message - From: CrossWalkCentral [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 10:26 AM Subject: [PHP-DB] Need smoe code examples on creating tables for mysql in php Need some code examples on creating tables for mysql in php can any one help me on this . I searched the php.net site and only found info on creating a database I just want to create a php script that will make 2 tables with lets say 5 fields each in them. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: A good tutorial
Hi Jen, here's some code I posted up in the general ng for storing in db and
retrieving images from db for display on your page.
It's recommended that you stay away from storing actual images in the db. If
you are storing a path the code below might help you out. It select a
firstname, and
an image according to an id. You'll have to change the youridentityfield
and youridentity values to suit your needs.
# display image from db
#
#connect to db
$connectionToDBid = odbc_connect(ceo34deesnr43ipits, joeyscode,
joeyscode);
// sql statement
$sqlb = SELECT imagepath, firstname FROM IMAGES WHERE youridentityfield =
. $youridentity;
# execute the query
$resultset = odbc_do($connectionToDBid, $sqlb);
$image = odbc_result($resultset,1);
$firstname = odbc_result($resultset,2);
print brbrbrdiv align='center';
print table width='400' border=0 cellpadding=2 ;
print trtd align='left' ;
print font color='#ff' face='verdana'h4 . Welcome back .
$firstname . /h4/font;
print /td/tr;
print trtd align='center' ;
print img src=' . $image.' align='center' border='0'br;
print /td/tr;
print /table;
# close the db connection here
# end of display image from db
#
#photo upload form
Here's a common form that allows you to upload a file (say an image) to the
db
form action='upload.php' method='post' enctype='multipart/form-data'
input type='hidden' name='MAX_FILE_SIZE' value='102400'
Pfont color='#ff' face='verdana' size=1Upload Photo:/fontinput
type='file' name='userfile'input type='submit'
value='Upload!!!'/form/p
end of photo upload form
##
start of upload process to db
?php
# $userfile is the file being uploaded
# print $userfile . BR;
# print $userfile_name . BR;
#use a time stamp plus the original file name to uniquely name the image
before storing in db to prevent two files with the same name
$timestamp = time();
$userfile_name = $timestamp.$userfile_name ;
// copy the file being posted -- remember to escape backslashes!!!
if(copy($userfile, /ez/codesnipits/consultant/tempimages/.
$userfile_name)){
print font face='Verdana, Arial, Helvetica, sans-serif' color='#663399'
size='2'Your picture has been uploaded successfully and has been made
available for
online users to view./fontbrbrbr ;
}
else
{
print font face='Verdana, Arial, Helvetica, sans-serif'
color='#663399'Error encountered during resume upload process./fontbr;
}
$patharola = tempimages/. $userfile_name;
// insert path into database here
# connect to db
$connectionToDBid = odbc_connect(cdefc5onwesulertt, joeyscon,
joeyscon);
session_register(consultantid);
$consultantid = $consultantid;
# create query statement -- update image field in db using path
$sqlr = UPDATE CONSULTANT SET image= '$patharola' WHERE consultantid= .
$consultantid;
# execute the sql statement (query) on the connection made
$resultset = odbc_do($connectionToDBid, $sqlr);
?
end of image to db process
##
Hope this helps you out :)
Cheers Joe :)
Jennifer Downey [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
If anyone knows of one, would someone please point me towards a good
tutorial on storing images in a database then fetching them out again.
I would really appreciate it and thanks in advance!
Jen Downey
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: A good tutorial
Ammendment: Make sure you test for the proper file extension before you actually upload it. The last thing you want is someone uploading a text file instead of the type of file you are looking for! I forgot to add that in the code. Grab the last 4 characters off of the end of the filename and determine if it is of type '.jpg' or '.gif' --completely up to you. Cheers Joe :) Jennifer Downey [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... If anyone knows of one, would someone please point me towards a good tutorial on storing images in a database then fetching them out again. I would really appreciate it and thanks in advance! Jen Downey -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: A good tutorial
I am just going to interject a second here... sorry :P
About storing images on a server... I did that for awhile, but once i hit
1000+ images, my scripts started timing out.(from reading the directory)
Storing them in the DB seems to actually improve performance for php...
Another issue is that I have one copy of a php script in my apache site root
folder, and use aliases to install the same script on many domains (Using
VirtualHost containers in httpd.conf)
I have gone back and forth from one system to another, and haven't found a
perfect solution yet :)
JD
-Original Message-
From: Lerp [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 12:20 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Re: A good tutorial
Hi Jen, here's some code I posted up in the general ng for storing in db and
retrieving images from db for display on your page.
It's recommended that you stay away from storing actual images in the db. If
you are storing a path the code below might help you out. It select a
firstname, and
an image according to an id. You'll have to change the youridentityfield
and youridentity values to suit your needs.
# display image from db
#
#connect to db
$connectionToDBid = odbc_connect(ceo34deesnr43ipits, joeyscode,
joeyscode);
// sql statement
$sqlb = SELECT imagepath, firstname FROM IMAGES WHERE youridentityfield =
. $youridentity;
# execute the query
$resultset = odbc_do($connectionToDBid, $sqlb);
$image = odbc_result($resultset,1);
$firstname = odbc_result($resultset,2);
print brbrbrdiv align='center';
print table width='400' border=0 cellpadding=2 ;
print trtd align='left' ;
print font color='#ff' face='verdana'h4 . Welcome back .
$firstname . /h4/font;
print /td/tr;
print trtd align='center' ;
print img src=' . $image.' align='center' border='0'br;
print /td/tr;
print /table;
# close the db connection here
# end of display image from db
#
#photo upload form
Here's a common form that allows you to upload a file (say an image) to the
db
form action='upload.php' method='post' enctype='multipart/form-data'
input type='hidden' name='MAX_FILE_SIZE' value='102400'
Pfont color='#ff' face='verdana' size=1Upload Photo:/fontinput
type='file' name='userfile'input type='submit'
value='Upload!!!'/form/p
end of photo upload form
##
start of upload process to db
?php
# $userfile is the file being uploaded
# print $userfile . BR;
# print $userfile_name . BR;
#use a time stamp plus the original file name to uniquely name the image
before storing in db to prevent two files with the same name
$timestamp = time();
$userfile_name = $timestamp.$userfile_name ;
// copy the file being posted -- remember to escape backslashes!!!
if(copy($userfile, /ez/codesnipits/consultant/tempimages/.
$userfile_name)){
print font face='Verdana, Arial, Helvetica, sans-serif' color='#663399'
size='2'Your picture has been uploaded successfully and has been made
available for
online users to view./fontbrbrbr ;
}
else
{
print font face='Verdana, Arial, Helvetica, sans-serif'
color='#663399'Error encountered during resume upload process./fontbr;
}
$patharola = tempimages/. $userfile_name;
// insert path into database here
# connect to db
$connectionToDBid = odbc_connect(cdefc5onwesulertt, joeyscon,
joeyscon);
session_register(consultantid);
$consultantid = $consultantid;
# create query statement -- update image field in db using path
$sqlr = UPDATE CONSULTANT SET image= '$patharola' WHERE consultantid= .
$consultantid;
# execute the sql statement (query) on the connection made
$resultset = odbc_do($connectionToDBid, $sqlr);
?
end of image to db process
##
Hope this helps you out :)
Cheers Joe :)
Jennifer Downey [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
If anyone knows of one, would someone please point me towards a good
tutorial on storing images in a database then fetching them out again.
I would really appreciate it and thanks in advance!
Jen 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
[PHP-DB] SQL
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? Thanx -Jonathan
RE: [PHP-DB] SQL
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? -Original Message- From: Jonathan Underfoot [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 2:57 PM To: [PHP-DB] Subject: [PHP-DB] SQL 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? Thanx -Jonathan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL
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..) Thanx, -Jonathan - Original Message - From: Rick Emery [EMAIL PROTECTED] To: 'Jonathan Underfoot' [EMAIL PROTECTED]; [PHP-DB] [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 4:06 PM Subject: RE: [PHP-DB] SQL 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? -Original Message- From: Jonathan Underfoot [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 2:57 PM To: [PHP-DB] Subject: [PHP-DB] SQL 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? Thanx -Jonathan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql LIMIT and ORDER BY problem
I have a strange problem with LIMIT. When I use it like SELECT * FROM table LIMIT 0, 10 it works fine. When I add a sort and write the query like SELECT * FROM table ORDER BY col ASC LIMIT 0, 10 I get no results. What am I missing? /Fredrik -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: A good tutorial
perfect solution yet :)
is there such a thing? :)
Jd Daniels [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
I am just going to interject a second here... sorry :P
About storing images on a server... I did that for awhile, but once i hit
1000+ images, my scripts started timing out.(from reading the directory)
Storing them in the DB seems to actually improve performance for php...
Another issue is that I have one copy of a php script in my apache site
root
folder, and use aliases to install the same script on many domains (Using
VirtualHost containers in httpd.conf)
I have gone back and forth from one system to another, and haven't found a
perfect solution yet :)
JD
-Original Message-
From: Lerp [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 12:20 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Re: A good tutorial
Hi Jen, here's some code I posted up in the general ng for storing in db
and
retrieving images from db for display on your page.
It's recommended that you stay away from storing actual images in the db.
If
you are storing a path the code below might help you out. It select a
firstname, and
an image according to an id. You'll have to change the youridentityfield
and youridentity values to suit your needs.
# display image from db
#
#connect to db
$connectionToDBid = odbc_connect(ceo34deesnr43ipits, joeyscode,
joeyscode);
// sql statement
$sqlb = SELECT imagepath, firstname FROM IMAGES WHERE youridentityfield
=
. $youridentity;
# execute the query
$resultset = odbc_do($connectionToDBid, $sqlb);
$image = odbc_result($resultset,1);
$firstname = odbc_result($resultset,2);
print brbrbrdiv align='center';
print table width='400' border=0 cellpadding=2 ;
print trtd align='left' ;
print font color='#ff' face='verdana'h4 . Welcome back .
$firstname . /h4/font;
print /td/tr;
print trtd align='center' ;
print img src=' . $image.' align='center' border='0'br;
print /td/tr;
print /table;
# close the db connection here
# end of display image from db
#
#photo upload form
Here's a common form that allows you to upload a file (say an image) to
the
db
form action='upload.php' method='post' enctype='multipart/form-data'
input type='hidden' name='MAX_FILE_SIZE' value='102400'
Pfont color='#ff' face='verdana' size=1Upload Photo:/fontinput
type='file' name='userfile'input type='submit'
value='Upload!!!'/form/p
end of photo upload form
##
start of upload process to db
?php
# $userfile is the file being uploaded
# print $userfile . BR;
# print $userfile_name . BR;
#use a time stamp plus the original file name to uniquely name the image
before storing in db to prevent two files with the same name
$timestamp = time();
$userfile_name = $timestamp.$userfile_name ;
// copy the file being posted -- remember to escape backslashes!!!
if(copy($userfile, /ez/codesnipits/consultant/tempimages/.
$userfile_name)){
print font face='Verdana, Arial, Helvetica, sans-serif' color='#663399'
size='2'Your picture has been uploaded successfully and has been made
available for
online users to view./fontbrbrbr ;
}
else
{
print font face='Verdana, Arial, Helvetica, sans-serif'
color='#663399'Error encountered during resume upload
process./fontbr;
}
$patharola = tempimages/. $userfile_name;
// insert path into database here
# connect to db
$connectionToDBid = odbc_connect(cdefc5onwesulertt, joeyscon,
joeyscon);
session_register(consultantid);
$consultantid = $consultantid;
# create query statement -- update image field in db using path
$sqlr = UPDATE CONSULTANT SET image= '$patharola' WHERE consultantid= .
$consultantid;
# execute the sql statement (query) on the connection made
$resultset = odbc_do($connectionToDBid, $sqlr);
?
end of image to db process
##
Hope this helps you out :)
Cheers Joe :)
Jennifer Downey [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
If anyone knows of one, would someone please point me towards a good
tutorial on storing images in a database then fetching them out again.
I would really appreciate it and thanks in advance!
Jen 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:
[PHP-DB] formating problem
I feel kinda dumb for posting this but here it is... I am trying to query a
table then format the results so it looks like the rest of the site and the
darn delete button to remove db entries keeps showing up at the top. Here
is the code... Thanks in advance.
Jas
?php
require '../scripts/db.php';
$result = mysql_query(SELECT * FROM cur_inv,$dbh) or die(Could not
execute query, please try again later);
echo table border=\0\ class=\table-body\ width=\100%\form
name=\rem_inv\ method=\post\ action=\done2.php3\
trtd align=\center\ colspan=\2\font size=\4\BCurrent
Inventory/B/fonthr color=\33\/td/tr;
$count = -1;
while ($myrow = mysql_fetch_row($result)) {
$count ++;
echo trtd width=\30%\BType Of Car: /B/tdtd;
printf(mysql_result($result,$count,car_type));
echo /tdtdinput type=\checkbox\ name=\car_type\
value=\checkbox\remove/td
/tr\n;
echo trtd width=\30%\BModel Of Car: /B/tdtd;
printf(mysql_result($result,$count,car_model));
echo /td/tr\n;
echo trtd width=\30%\BYear Of Car: /B/tdtd;
printf(mysql_result($result,$count,car_year));
echo /td/tr\n;
echo trtd width=\30%\BPrice Of Car: /B/tdtd$;
printf(mysql_result($result,$count,car_price));
echo /td/tr\n;
echo trtd width=\30%\BVIN Of Car: /B/tdtd;
printf(mysql_result($result,$count,car_vin));
echo /td/trtrtd colspan=\3\hr color=\33\/td/tr\n;
}
echo input type=\submit\ name=\delete\
value=\delete\/form/table;
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: A good tutorial
Hi again JD...I got to thinkin':)
Just out of curiosity...how many images were you bringing back from the db
when your script timed out?
I store the path of my image in the db and echo it out when I need to.
Cheers Joe:)
Jd Daniels [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
I am just going to interject a second here... sorry :P
About storing images on a server... I did that for awhile, but once i hit
1000+ images, my scripts started timing out.(from reading the directory)
Storing them in the DB seems to actually improve performance for php...
Another issue is that I have one copy of a php script in my apache site
root
folder, and use aliases to install the same script on many domains (Using
VirtualHost containers in httpd.conf)
I have gone back and forth from one system to another, and haven't found a
perfect solution yet :)
JD
-Original Message-
From: Lerp [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 12:20 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Re: A good tutorial
Hi Jen, here's some code I posted up in the general ng for storing in db
and
retrieving images from db for display on your page.
It's recommended that you stay away from storing actual images in the db.
If
you are storing a path the code below might help you out. It select a
firstname, and
an image according to an id. You'll have to change the youridentityfield
and youridentity values to suit your needs.
# display image from db
#
#connect to db
$connectionToDBid = odbc_connect(ceo34deesnr43ipits, joeyscode,
joeyscode);
// sql statement
$sqlb = SELECT imagepath, firstname FROM IMAGES WHERE youridentityfield
=
. $youridentity;
# execute the query
$resultset = odbc_do($connectionToDBid, $sqlb);
$image = odbc_result($resultset,1);
$firstname = odbc_result($resultset,2);
print brbrbrdiv align='center';
print table width='400' border=0 cellpadding=2 ;
print trtd align='left' ;
print font color='#ff' face='verdana'h4 . Welcome back .
$firstname . /h4/font;
print /td/tr;
print trtd align='center' ;
print img src=' . $image.' align='center' border='0'br;
print /td/tr;
print /table;
# close the db connection here
# end of display image from db
#
#photo upload form
Here's a common form that allows you to upload a file (say an image) to
the
db
form action='upload.php' method='post' enctype='multipart/form-data'
input type='hidden' name='MAX_FILE_SIZE' value='102400'
Pfont color='#ff' face='verdana' size=1Upload Photo:/fontinput
type='file' name='userfile'input type='submit'
value='Upload!!!'/form/p
end of photo upload form
##
start of upload process to db
?php
# $userfile is the file being uploaded
# print $userfile . BR;
# print $userfile_name . BR;
#use a time stamp plus the original file name to uniquely name the image
before storing in db to prevent two files with the same name
$timestamp = time();
$userfile_name = $timestamp.$userfile_name ;
// copy the file being posted -- remember to escape backslashes!!!
if(copy($userfile, /ez/codesnipits/consultant/tempimages/.
$userfile_name)){
print font face='Verdana, Arial, Helvetica, sans-serif' color='#663399'
size='2'Your picture has been uploaded successfully and has been made
available for
online users to view./fontbrbrbr ;
}
else
{
print font face='Verdana, Arial, Helvetica, sans-serif'
color='#663399'Error encountered during resume upload
process./fontbr;
}
$patharola = tempimages/. $userfile_name;
// insert path into database here
# connect to db
$connectionToDBid = odbc_connect(cdefc5onwesulertt, joeyscon,
joeyscon);
session_register(consultantid);
$consultantid = $consultantid;
# create query statement -- update image field in db using path
$sqlr = UPDATE CONSULTANT SET image= '$patharola' WHERE consultantid= .
$consultantid;
# execute the sql statement (query) on the connection made
$resultset = odbc_do($connectionToDBid, $sqlr);
?
end of image to db process
##
Hope this helps you out :)
Cheers Joe :)
Jennifer Downey [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
If anyone knows of one, would someone please point me towards a good
tutorial on storing images in a database then fetching them out again.
I would really appreciate it and thanks in advance!
Jen Downey
--
PHP Database Mailing
RE: [PHP-DB] Re: A good tutorial
It has never timed out bringing back from the db... only when reading from
disk.
I think the big difference is I need to create both fancy web pages and neat
printable pages. So I need to open up an image stream to manipulate the
photo.
I had a top level dir called photos, then a sub folder named from the row id
in the MySQL table.. so the path was always something like
/photos/1066/photo.jpg
once I hit 1000+ records (thusly, 1000+ sub dirs in photos/) ... it would
time out opening the 1066 directory. (displaying 15 records at a time) It is
much faster now with a separate table with binary data and storing just the
id for an image in the original table... then just select using an id. ( a
separate select staement seems to be faster than joining the tables) The
biggest problem tho was that I have two servers working as web farms. if one
domain needed access to the photos from another domain located on the other
server, I could not open the photos. (For resizing and such) When they are
in the db, I can get them from anywhere.
JD
-Original Message-
From: Joe Van Meer [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 3:04 PM
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Re: A good tutorial
Hi again JD...I got to thinkin':)
Just out of curiosity...how many images were you bringing back from the db
when your script timed out?
I store the path of my image in the db and echo it out when I need to.
Cheers Joe:)
Jd Daniels [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
I am just going to interject a second here... sorry :P
About storing images on a server... I did that for awhile, but once i hit
1000+ images, my scripts started timing out.(from reading the directory)
Storing them in the DB seems to actually improve performance for php...
Another issue is that I have one copy of a php script in my apache site
root
folder, and use aliases to install the same script on many domains (Using
VirtualHost containers in httpd.conf)
I have gone back and forth from one system to another, and haven't found a
perfect solution yet :)
JD
-Original Message-
From: Lerp [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 12:20 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Re: A good tutorial
Hi Jen, here's some code I posted up in the general ng for storing in db
and
retrieving images from db for display on your page.
It's recommended that you stay away from storing actual images in the db.
If
you are storing a path the code below might help you out. It select a
firstname, and
an image according to an id. You'll have to change the youridentityfield
and youridentity values to suit your needs.
# display image from db
#
#connect to db
$connectionToDBid = odbc_connect(ceo34deesnr43ipits, joeyscode,
joeyscode);
// sql statement
$sqlb = SELECT imagepath, firstname FROM IMAGES WHERE youridentityfield
=
. $youridentity;
# execute the query
$resultset = odbc_do($connectionToDBid, $sqlb);
$image = odbc_result($resultset,1);
$firstname = odbc_result($resultset,2);
print brbrbrdiv align='center';
print table width='400' border=0 cellpadding=2 ;
print trtd align='left' ;
print font color='#ff' face='verdana'h4 . Welcome back .
$firstname . /h4/font;
print /td/tr;
print trtd align='center' ;
print img src=' . $image.' align='center' border='0'br;
print /td/tr;
print /table;
# close the db connection here
# end of display image from db
#
#photo upload form
Here's a common form that allows you to upload a file (say an image) to
the
db
form action='upload.php' method='post' enctype='multipart/form-data'
input type='hidden' name='MAX_FILE_SIZE' value='102400'
Pfont color='#ff' face='verdana' size=1Upload Photo:/fontinput
type='file' name='userfile'input type='submit'
value='Upload!!!'/form/p
end of photo upload form
##
start of upload process to db
?php
# $userfile is the file being uploaded
# print $userfile . BR;
# print $userfile_name . BR;
#use a time stamp plus the original file name to uniquely name the image
before storing in db to prevent two files with the same name
$timestamp = time();
$userfile_name = $timestamp.$userfile_name ;
// copy the file being posted -- remember to escape backslashes!!!
if(copy($userfile, /ez/codesnipits/consultant/tempimages/.
$userfile_name)){
print font face='Verdana, Arial, Helvetica, sans-serif' color='#663399'
size='2'Your picture has been uploaded successfully and
Re: [PHP-DB] Re: A good tutorial
Thanks for the info JD , will keep this in mind for projects that retrieve higher number of images . Currently I'm only bringing back 1-3 images from the dir (sorry...meant dir instead of db in previous post:)at time and it doesn't seem too bad. Cheers, Joe:) Jd Daniels [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... It has never timed out bringing back from the db... only when reading from disk. I think the big difference is I need to create both fancy web pages and neat printable pages. So I need to open up an image stream to manipulate the photo. I had a top level dir called photos, then a sub folder named from the row id in the MySQL table.. so the path was always something like /photos/1066/photo.jpg once I hit 1000+ records (thusly, 1000+ sub dirs in photos/) ... it would time out opening the 1066 directory. (displaying 15 records at a time) It is much faster now with a separate table with binary data and storing just the id for an image in the original table... then just select using an id. ( a separate select staement seems to be faster than joining the tables) The biggest problem tho was that I have two servers working as web farms. if one domain needed access to the photos from another domain located on the other server, I could not open the photos. (For resizing and such) When they are in the db, I can get them from anywhere. JD -Original Message- From: Joe Van Meer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 3:04 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Re: A good tutorial Hi again JD...I got to thinkin':) Just out of curiosity...how many images were you bringing back from the db when your script timed out? I store the path of my image in the db and echo it out when I need to. Cheers Joe:) Jd Daniels [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... I am just going to interject a second here... sorry :P About storing images on a server... I did that for awhile, but once i hit 1000+ images, my scripts started timing out.(from reading the directory) Storing them in the DB seems to actually improve performance for php... Another issue is that I have one copy of a php script in my apache site root folder, and use aliases to install the same script on many domains (Using VirtualHost containers in httpd.conf) I have gone back and forth from one system to another, and haven't found a perfect solution yet :) JD -Original Message- From: Lerp [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:20 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: A good tutorial Hi Jen, here's some code I posted up in the general ng for storing in db and retrieving images from db for display on your page. It's recommended that you stay away from storing actual images in the db. If you are storing a path the code below might help you out. It select a firstname, and an image according to an id. You'll have to change the youridentityfield and youridentity values to suit your needs. # display image from db # #connect to db $connectionToDBid = odbc_connect(ceo34deesnr43ipits, joeyscode, joeyscode); // sql statement $sqlb = SELECT imagepath, firstname FROM IMAGES WHERE youridentityfield = . $youridentity; # execute the query $resultset = odbc_do($connectionToDBid, $sqlb); $image = odbc_result($resultset,1); $firstname = odbc_result($resultset,2); print brbrbrdiv align='center'; print table width='400' border=0 cellpadding=2 ; print trtd align='left' ; print font color='#ff' face='verdana'h4 . Welcome back . $firstname . /h4/font; print /td/tr; print trtd align='center' ; print img src=' . $image.' align='center' border='0'br; print /td/tr; print /table; # close the db connection here # end of display image from db # #photo upload form Here's a common form that allows you to upload a file (say an image) to the db form action='upload.php' method='post' enctype='multipart/form-data' input type='hidden' name='MAX_FILE_SIZE' value='102400' Pfont color='#ff' face='verdana' size=1Upload Photo:/fontinput type='file' name='userfile'input type='submit' value='Upload!!!'/form/p end of photo upload form ## start of upload process to db ?php # $userfile is the file being uploaded # print $userfile . BR; # print $userfile_name . BR;
[PHP-DB] Problems with creating a report
I've been struggling with creating a rate report based on data extracted from a Mysql table. I had written a piece of PHP code for creating the type of report I needed but it has turned out buggy and prone to errors. I then tried grouping the rates data in an sql statement but this omits lines and leaves my dates out of wack. Would someone be able to point me in the right direction with how the following table could be created with the below data. Thanks for any suggestions on how to go about this. Rod 2002-05-01 | 2002-05-03 | 60 | 70 2002-05-04 | 2002-05-05 | 100 | 120 2002-05-06 | 2002-05-09 | 80 | 100 2002-05-10 | 120 | 140 2002-05-11 | 2002-05-14 | 60 | 70 2002-05-15 | 2002-05-18 | 40 | 60 CREATE TABLE rates ( id int(11) DEFAULT '0' NOT NULL auto_increment, ratedate date DEFAULT '-00-00' NOT NULL, rate1 smallint(6) DEFAULT '0' NOT NULL, rate2 mediumint(9) DEFAULT '0' NOT NULL, PRIMARY KEY (id) ); INSERT INTO rates VALUES ( '1', '2002-05-01', '60', '70'); INSERT INTO rates VALUES ( '2', '2002-05-02', '60', '70'); INSERT INTO rates VALUES ( '3', '2002-05-03', '60', '70'); INSERT INTO rates VALUES ( '4', '2002-05-04', '100', '120'); INSERT INTO rates VALUES ( '5', '2002-05-05', '100', '120'); INSERT INTO rates VALUES ( '6', '2002-05-06', '80', '100'); INSERT INTO rates VALUES ( '7', '2002-05-07', '80', '100'); INSERT INTO rates VALUES ( '8', '2002-05-08', '80', '100'); INSERT INTO rates VALUES ( '9', '2002-05-09', '80', '100'); INSERT INTO rates VALUES ( '10', '2002-05-10', '120', '140'); INSERT INTO rates VALUES ( '11', '2002-05-11', '60', '70'); INSERT INTO rates VALUES ( '12', '2002-05-12', '60', '70'); INSERT INTO rates VALUES ( '13', '2002-02-13', '60', '70'); INSERT INTO rates VALUES ( '14', '2002-05-14', '60', '70'); INSERT INTO rates VALUES ( '15', '2002-05-15', '40', '60'); INSERT INTO rates VALUES ( '16', '2002-05-16', '40', '60'); INSERT INTO rates VALUES ( '17', '2002-05-17', '40', '60'); INSERT INTO rates VALUES ( '18', '2002-05-18', '40', '60'); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] formating problem
htat's because the delete button is in the wrong place in your code.
if you put an HTML element between the table and /table tags, but don't
enclose it in at least tr and td tags (pretty loose about closing them,
but of course you should always close your tags!) then the browser will
display that element above the table - since it doesn't really know where
you want it placed.
long story short, replace the last little section of your code with
echo tr\ntd colspan=\2\ align=\center\input type=\submit\
name=\delete\
value=\delete\/td\n/tr\n\n/form/table;
?
and PS. i don't know if you are doing it by mistake or intentionally, but
you may have noticed that form elements cause a certain amount of space to
be taken up because the browser decides to render them as a br or
something for some reason. if you put the form and /form elements
BETWEEN a tr and a td or a table and a tr or something (i.e. not
properly located in a td/td pair) then this formatting is not displayed
for some reason - go figure.
enjoy
beau
// -Original Message-
// From: jas [mailto:[EMAIL PROTECTED]]
// Sent: Tuesday, 19 February 2002 7:13 PM
// To: [EMAIL PROTECTED]
// Subject: [PHP-DB] formating problem
//
//
// I feel kinda dumb for posting this but here it is... I am
// trying to query a
// table then format the results so it looks like the rest of
// the site and the
// darn delete button to remove db entries keeps showing up at
// the top. Here
// is the code... Thanks in advance.
// Jas
// ?php
// require '../scripts/db.php';
// $result = mysql_query(SELECT * FROM cur_inv,$dbh) or die(Could not
// execute query, please try again later);
// echo table border=\0\ class=\table-body\ width=\100%\form
// name=\rem_inv\ method=\post\ action=\done2.php3\
// trtd align=\center\ colspan=\2\font size=\4\BCurrent
// Inventory/B/fonthr color=\33\/td/tr;
// $count = -1;
// while ($myrow = mysql_fetch_row($result)) {
// $count ++;
// echo trtd width=\30%\BType Of Car: /B/tdtd;
// printf(mysql_result($result,$count,car_type));
// echo /tdtdinput type=\checkbox\ name=\car_type\
// value=\checkbox\remove/td
// /tr\n;
// echo trtd width=\30%\BModel Of Car: /B/tdtd;
// printf(mysql_result($result,$count,car_model));
// echo /td/tr\n;
// echo trtd width=\30%\BYear Of Car: /B/tdtd;
// printf(mysql_result($result,$count,car_year));
// echo /td/tr\n;
// echo trtd width=\30%\BPrice Of Car: /B/tdtd$;
// printf(mysql_result($result,$count,car_price));
// echo /td/tr\n;
// echo trtd width=\30%\BVIN Of Car: /B/tdtd;
// printf(mysql_result($result,$count,car_vin));
// echo /td/trtrtd colspan=\3\hr
// color=\33\/td/tr\n;
// }
// echo input type=\submit\ name=\delete\
// value=\delete\/form/table;
// ?
//
//
//
// --
// 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, You didn't mention which DBMS you're using (Oracle, MySQL, MS SQL, ...) and the are each a little different but I'll try to solve with fairly generic SQL: My personal preference is keep it simple, break up the SQL if it helps (and I think it would help here). I'd use a third table, it will make this very simple. /* not sure what datatype cardnumber is but it doesn't matter */ /* just make this match */ CREAT TABLE TroubleItems (cardnumber int, numAuth int, numCharg int) INSERT INTO TroubleItems SELECT cardnumber, count(*), 0 FROM authtable GROUP BY cardnumber UPDATE TroubleItems set numCharg = (SELECT count(*) FROM chargetable WHERE TroubleItems. cardnumber = chargetable. cardnumber) /* delete the ones that do match (they aren't trouble) */ DELETE TroubleItems WHERE numAuth = numCharg /* Now the report you want is easy */ SELECT * FROM TroubleItems /* then drop or empty TroubleItems - or delete the rows as they are fixed */ /* but don't run this again unless you empty TroubleItems */ I did test this on MS SQL server and it works fine there. The only thing I'm the least fuzzy on is will this update statement work in MySQL (MySQL is a little weak with sub-queries). Even if that command fails you can walk the list by selecting the cardnumber and doing a for each loop inside of PHP - this would take a little longer but it would give you valid results. Good Luck, Frank On 2/19/02 6:16 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: From: Jonathan Underfoot [EMAIL PROTECTED] Date: Tue, 19 Feb 2002 16:28:32 -0500 To: Rick Emery [EMAIL PROTECTED], [PHP-DB] [EMAIL PROTECTED] Subject: Re: [PHP-DB] SQL 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..) Thanx, -Jonathan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Problems with creating a report
Rod,
I hope I've got what you're trying to do.
You have a table with a record for each day and columns rate1 and rate2 -
You are trying to collapse the single days into a range where the rates are
the same?
So if I added column titles to your sample report:
Start Endrate1 rate2
2002-05-01 | 2002-05-03 | 60 | 70
2002-05-04 | 2002-05-05 | 100 | 120
2002-05-06 | 2002-05-09 | 80 | 100
2002-05-10 | 120 | 140
2002-05-11 | 2002-05-14 | 60 | 70
2002-05-15 | 2002-05-18 | 40 | 60
Now one problem I notice right off is line 4 is missing one column (perhaps
because there is only one day where these rates were in effect) - you cannot
do this in SQL. SQL insists on very square results, that is a SELECT
statement will return the same number of columns in each row, some may be
NULL but each row will have the same number. In your case it's easy to put
the same date in as Start and End.
The more interesting problem is you can't really use GROUP BY for what you
seem to want to do. If you tried:
SELECT min(ratedate), max(ratedate), rate1, rate2
FROM rates
GROUP BY rate1, rate2
It would not work because the rates would get grouped together _anywhere_
they are the same, not just where the ratedate are contiguous. In your
example data 5/1 - 5/3 would be grouped with 5/11 - 5/14 and the above SQL
would return:
Start Endrate1 rate2
2002-05-01 | 2002-05-14 | 60 | 70
I think the solution is not in SQL, it's in PHP.
Your select would be:
SELECT ratedate, rate1, rate2
FROM rates
WHERE whatever you want here
ORDER BY ratedate
And you assign this to $result
/* set the report titles */
$start = 'Start';
$end = 'End';
$rate1 = 'Rate 1';
$rate2 = 'Rate 2';
while( odbc_fetch_row( $result ))
{
$newRate1 = odbc_result( $result , 2 );
$newRate2 = odbc_result( $result , 3 );
if (($rate1 != $newRate1) || ($rate2 != $newRate2 )) /* rates changed*/
{
print $start | $end | $rate1 | $rate2 \n; /* print old rates */
$start = odbc_result( $result , 1 );
$end = odbc_result( $result , 1 );
$rate1 = odbc_result( $result , 2 );
$rate2 = odbc_result( $result , 3 );
} else {
$end = odbc_result( $result , 1 ); /* no rate change - $end gets
older */
}
}
print $start | $end | $rate1 | $rate2 \n; /* print last row */
There are perhaps better ways to do this but you see that the variables
$start, $end, $rate1 and $rate2 are printed whenever the rates change then
set to the new values, $end gets set to the new 'ratedate' each time the
rates do not change so when it is printed it will be the last day of the old
rates.
You could also add some code to only have one date column if $start == $end
if you wanted to do this - PHP couldn't care less about 'square' results.
Good Luck,
Frank
On 2/19/02 6:16 PM, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
From: Rod Wilkinson [EMAIL PROTECTED]
Date: Wed, 20 Feb 2002 08:09:46 +0700
To: [EMAIL PROTECTED]
Subject: Problems with creating a report
I've been struggling with creating a rate report based on data extracted
from a
Mysql table. I had written a piece of PHP code for creating the type of
report I needed but it has turned out buggy and prone to errors. I then
tried grouping the rates data in an sql statement but this omits lines and
leaves my
dates out of wack. Would someone be able to point me in the right direction
with how the following table could be created with the below data.
Thanks for any suggestions on how to go about this.
Rod
2002-05-01 | 2002-05-03 | 60 | 70
2002-05-04 | 2002-05-05 | 100 | 120
2002-05-06 | 2002-05-09 | 80 | 100
2002-05-10 | 120 | 140
2002-05-11 | 2002-05-14 | 60 | 70
2002-05-15 | 2002-05-18 | 40 | 60
CREATE TABLE rates (
id int(11) DEFAULT '0' NOT NULL auto_increment,
ratedate date DEFAULT '-00-00' NOT NULL,
rate1 smallint(6) DEFAULT '0' NOT NULL,
rate2 mediumint(9) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO rates VALUES ( '1', '2002-05-01', '60', '70');
INSERT INTO rates VALUES ( '2', '2002-05-02', '60', '70');
INSERT INTO rates VALUES ( '3', '2002-05-03', '60', '70');
INSERT INTO rates VALUES ( '4', '2002-05-04', '100', '120');
INSERT INTO rates VALUES ( '5', '2002-05-05', '100', '120');
INSERT INTO rates VALUES ( '6', '2002-05-06', '80', '100');
INSERT INTO rates VALUES ( '7', '2002-05-07', '80', '100');
INSERT INTO rates VALUES ( '8', '2002-05-08', '80', '100');
INSERT INTO rates VALUES ( '9', '2002-05-09', '80', '100');
INSERT INTO rates VALUES ( '10', '2002-05-10', '120', '140');
INSERT INTO rates VALUES ( '11', '2002-05-11', '60', '70');
INSERT INTO rates VALUES ( '12', '2002-05-12', '60', '70');
INSERT INTO rates VALUES ( '13', '2002-02-13', '60', '70');
INSERT INTO rates VALUES ( '14', '2002-05-14', '60', '70');
INSERT INTO rates VALUES ( '15', '2002-05-15', '40', '60');
INSERT INTO rates VALUES ( '16', '2002-05-16', '40', '60');
INSERT INTO rates VALUES (
