[PHP-DB] Re: Get ID of ROW when using aggregate functions
At 03:00 09/04/2009, you wrote: Message-ID: c8.a5.28416.3666c...@pb1.pair.com To: php-db@lists.php.net Reply-To: Ondrej Kulaty kopyto...@gmail.com From: Ondrej Kulaty kopyto...@gmail.com Date: Wed, 8 Apr 2009 10:54:55 +0200 Subject: Get ID of ROW when using aggregate functions INSERT INTO `test` (`id`, `name`, `company`, `sallary`) VALUES (1, 'Jane', 'Microsoft', 1), (2, 'Peter', 'Novell', 12000), (3, 'Steven', 'Microsoft', 17000); I want to select person from each company with a highest sallary. I run this SQL: SELECT id,name,company,MAX(sallary) FROM `test` GROUP BY company; And result is: id name company MAX( sallary ) 1 Jane Microsoft 17000 2 Peter Novell12000 Why it returned Jane (id 1) as a person with highest sallary (17000) when obviously Jane has sallary of 10 000? Are you expecting a person to have more than one sallary ? Your example rows don't indicate that. If the person is unique in this table, then you just need to order by salary : SELECT id, name, company, sallary FROM `test` ORDER BY sallary DESC LIMIT 1; If for some reason the person appears twice (perhaps you're paying them twice - I'd like their job please !) then SELECT id, name, company, MAX(sallary) AS top_salary FROM `test` GROUP BY sallary HAVING sallary = top_salary; You shouldn't really use LIMIT here though, because - though I didn't indicate it in the simple ORDER BY above, 2 people might have the same (top) salary of 17000 - rather than the person appearing twice, the salary appears twice (or more) and includes matching rows for the MAX() value. If you're considering microsoft and novell, probably those values need at least another zero on the end, including stock options ;-) It seems to me, if you intend the person to appear once, make the name column use a UNIQUE KEY. Since people will (eventually) have the same name, e.g 2x John Smiths or 2x Peters working at Novell, use some unique proxy for person, such as social security number or employee number + company, or similar. HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: php date/time zone
At 04:57 19/03/2009, you wrote: Message-ID: 49c1d0c8.30...@silverbackasp.com Date: Thu, 19 Mar 2009 12:57:44 +0800 From: Ron r...@silverbackasp.com Subject: php date/time zone Hi All, I have a form where in a user will enter a time and choose a timezone. based on those, i need to insert to db the time as the actual time on their timezone. how can i compute that on php? is it possible? You can either do it in MySQL, by storing the UTC time of the action, and a timezone offset : http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz Or you could take Keith Devens approach and handle it in PHP : http://keithdevens.com/weblog/archive/2004/Jul/28/Times.PHP.MySQL based on http://keithdevens.com/weblog/archive/2004/Jul/15/MySQL.time previous post To determine the users timezone *for* them, in the web page a little javascript would often be able to help set the timezone of a dropdown. http://www.webmasterworld.com/forum13/3922.htm then either use that to select the selected option of a select / dropdown menu, or put it directly into a hidden field and trust that it's probably as right as you can get it. The browser may also send header fields which could be used for this (in POST or PUT requests only) but it's not guaranteed or reliable either. So the best approach would probably be to automatically set the timezone selection using javascript, but allow the user to modify it before posting the form HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re : Problem with PDO exceptions
Message-ID: 49b2ce8f.1090...@theingots.org Date: Sat, 07 Mar 2009 20:44:15 +0100 From: Daniel Carrera daniel.carr...@theingots.org Hello, I have MySQL 5.1 and PHP 5.2. For some reason PDO is not throwing exceptions when I give it a broken SQL query. For example: try { $stmt = $db-prepare(SELECT * FROM foobar WHERE 1); } catch(PDOException $e) { error($e-getMessage()); } In this example there is no table called 'foobar', so this should give an error. Yet, it doesn't. When you create your DB connection $db, follow the connection line directly after with this : $db-setAttribute(PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION); The default is I believe PDO::ERRMODE_SILENT which is confusing to most people the first time. http://uk2.php.net/manual/en/pdo.setattribute.php HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: PDO buffered query problem
At 13:40 24/02/2009, you wrote: Message-ID: c4.82.23283.768e2...@pb1.pair.com To: php-db@lists.php.net,php-gene...@lists.php.net Date: Mon, 23 Feb 2009 18:16:01 + From: Stewart Duncan do...@gmx.net MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Subject: PDO buffered query problem Hi there, I'm having some serious problems with the PHP Data Object functions. I'm trying to loop through a sizeable result set (~60k rows, ~1gig) using a buffered query to avoid fetching the whole set. No matter what I do, the script just hangs on the PDO::query() - it seems the query is running unbuffered (why else would the change in result set size 'fix' the issue?). Just for completeness in understanding - the 60k rows does not correspond to 1Gb data - that's for the full rows right ? If not, that would imply your ID field below is ~18000 characters long which seems (cough) extreme ! If your ID field is still of substantial size, you could consider a surrogate key, eg an autoincrement integer to keep sizes small. IMO, requiring 6 rows in a result set might indicate that your application is configured to do much of the work otherwise done by the DB. Without any specifics it's hard to say, but be sure you actually ~need~ to fetch all the rows instead of returning a more limited resultset by better specifying the SQL. As one other poster noted, you *could* be reaching PHP's memory limit : It's not configured that high in default installs - often 2 or 8MB - and the script will terminate silently in that case, unless you have error_reporting(E_ALL) and/or error logging switched on in your application to debug this. If it's a production server, you'll need to check the error logs, as you won't be displaying the errors to screen ( -right - ? ;-)) Finally - try to make sure you're running the mysqldnd MySQL client driver with a recent (PHP 5.3+) install with MySQL4.1 http://dev.mysql.com/downloads/connector/php-mysqlnd/ http://uk.php.net/manual/en/mysqli.mysqlnd.php http://uk.php.net/manual/en/mysqli.installation.php That tends to use 1/2 the memory as the resultset is only stored in one place rather than 2 Oh - and I imagine that is repro code, but check if you had code in the script prior to those isolated lines which doesn't close resultsets or release statement resources, to make sure you're not consuming and not releasing memory during the script execution. On the MySQL side, if you're really returning large BLOBs and not the ID you specified below, read http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html One final top place to search for tips and ideas is always http://www.mysqlperformanceblog.com (buy the book if you get the chance, it's excellent) HTH Cheers - Neil Here is my code to reproduce the problem: ?php $Database = new PDO( 'mysql:host=localhost;port=3306;dbname=mydatabase', 'root', '', array( PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true ) ); $rQuery = $Database-query('SELECT id FROM mytable'); // This is never reached because the result set is too large echo 'Made it through.'; foreach($rQuery as $aRow) { print_r($aRow); } ? If I limit the query with some reasonable number, it works fine: $rQuery = $Database-query('SELECT id FROM mytable LIMIT 10'); I have tried playing with PDO::MYSQL_ATTR_MAX_BUFFER_SIZE and using the PDO::prepare() and PDO::execute() as well (though there are no parameters in the above query), both to no avail. Any help would be appreciated, Stewart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: New Table Creation with PHP Variables
At 12:43 29/12/2008, you wrote: Message-ID: 008b1179ce594ebea03cb06480182...@dragon From: Keith Spiller larent...@hosthive.com To: php_db php-db@lists.php.net Date: Sun, 28 Dec 2008 17:39:08 -0700 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=iso-8859-1; reply-type=original Content-Transfer-Encoding: 7bit Subject: New Table Creation with PHP Variables Hi, I'm trying to join multiple tables to then create a new table from the query. I've figured out that part, but some of the fields need to be evaluated and then compared to a php array to derive their data. In this example I am trying to populate the field4 column (from the $product_name array) after evaluating the product_type value on each row. CREATE TABLE $table[name] SELECT field1, field2, field3, IF(o.product_type='course', $product_name[$product_id], NULL) AS field4, field5, field6, field7 FROM table1 as a, table2 as o; Is this possible? Is there another way to accomplish this task? Thanks for your help. http://dev.mysql.com/doc/refman/5.1/en/create-table.html You can create one table from another by adding a http://dev.mysql.com/doc/refman/5.1/en/select.htmlSELECT statement at the end of the http://dev.mysql.com/doc/refman/5.1/en/create-table.htmlCREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; MySQL creates new columns for all elements in the http://dev.mysql.com/doc/refman/5.1/en/select.htmlSELECT. For example: mysql CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (a), KEY(b)) -ENGINE=MyISAM SELECT b,c FROM test2; HTHCheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP-DB] Building WHERE SQL clauses
At 03:46 16/09/2008, you wrote: Message-ID: [EMAIL PROTECTED] Date: Mon, 15 Sep 2008 18:33:04 +0100 From: Stephen Wellington [EMAIL PROTECTED] To: Mike Sullivan [EMAIL PROTECTED], php-db@lists.php.net MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Subject: Re: [PHP-DB] Building WHERE SQL clauses You probably want something like this: SELECT * FROM chico as c, harpo as h WHERE c.operator = Bill OR c.operator = Jessica OR h.operator = Bill OR h.operator =Jessica With all due respect, I think that's going to be a cause of major pain. You should try it out with the example table structures below. I've added a PK which doesn't ovelap, so you can see the result columns from both tables numerically It's going to end up doing a FULL JOIN on each row from chico which matches one of the 2 conditions, against the rows in harpo, which is definitely the wrong result here. I'm assuming the OP wants at most one row from either table which contains the other values from the table matching the result filter. On later consideration (he didn't make clear what the desired result was) it could well be the UNION result he's after, if both tables really are identical in structure, as mentioned by the later message (unless that was yours ;-) CREATE TABLE `chico` ( `item` smallint(6) NOT NULL AUTO_INCREMENT, `operator` char(32) NOT NULL, PRIMARY KEY (`item`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- Duplicate chico table structure exactly CREATE TABLE `harpo` LIKE `chico`; INSERT INTO `chico` (`item`, `operator`) VALUES (1, 'Bill'), (2, 'Jessica'), (3, 'Dave'), (4, 'Clara'), (5, 'Ally'), (6, 'Josh'), (9, 'Mark'), (10, 'Sophie'); INSERT INTO `harpo` (`item`, `operator`) VALUES (13, 'Mark'), (14, 'Sophie'), (15, 'Bill'), (16, 'Jessica'); HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Sqlite 3 pdo update query problem
At 09:15 20/08/2008, you wrote: Message-Id: [EMAIL PROTECTED] From: Amy Gibbs [EMAIL PROTECTED] Date: Wed, 20 Aug 2008 09:08:42 +0100 I've managed to connect to my database, and run a select query, but now I need to run an UPDATE query and it just doesn't seem to take effect. Things I'd try or check presuming the DB file it exists and returns rows from the SELECT : (1) Ensure the SQLite DB file is not set to read-only in the filesystem, which will prevent UPDATE (2) Echo out the $sql line below on each iteration, to ensure it contains what you intend, and not unclosed quotes (3) Set $sesdb-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); Step 3 should make your code barf when it can't execute a query and immediately indicate the problem. For some reason, PDO seems to default to silent errors at least when using the SQLite interface HTH Cheers - Neil $sesdb = new PDO('sqlite:file.sqlite3'); $query=SELECT ZNAME, ZQUANTITY, ZPRODUCTID FROM ZITEM WHERE ZCategory != 14; $statement= $sesdb-query($query); $result=$statement-fetchAll(); foreach ($result as $product) { $prodname=$product[0]; $prodqty = $product[1]; $prodid=$product[2]; $sql = UPDATE ZITEM SET ZQUANTITY='0' WHERE ZPRODUCTID='.$prodid.'; $sesdb-exec($sql); } [/code] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Query Across DBMS Boundaries (Linked Database Servers?)
At 14:12 21/03/2008, you wrote: Message-ID: [EMAIL PROTECTED] Date: Thu, 20 Mar 2008 11:05:22 -0500 From: Dee Ayy [EMAIL PROTECTED] To: php-db@lists.php.net MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Subject: Re: Query Across DBMS Boundaries (Linked Database Servers?) Wow! I just found the Federated Storage Engine. http://dev.mysql.com/tech-resources/articles/mysql-federated-storage.html It's not in our SHOW ENGINES though. I still welcome comments. You need MySQL MaxDB version (at least on windows), which is precompiled using --with-federated-storage-engine That article doesn't state it, and I think I had to stumble across this in the Federated engine discussion forum @ MySQL It's a very useful storage type, particularly if you have a lot of records with recent records (eg last 1 month) online, and a bunch compressed or as ARCHIVE tables on a near-line storage server for your older data (eg older than 1 month) HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: ordering date in varchar
Message-ID: [EMAIL PROTECTED] Date: Thu, 13 Mar 2008 14:05:39 +0530 From: Vaibhav Informatics [EMAIL PROTECTED] We have a problem. We have given the dates as date.month.year eg 27.12.2007 The field is named as varchar. We have to arrange ths list of dates in ascending order. Can anyone help? Varchar arranges the list by scanning from left side. Perhaps scanning from right side will do. How to do it? OK I'll take this one too then ;-) You're right that ORDER BY will treat that as a string, so the ordering will be broken. There are 2 options I can think of to handle this without changing the table structure : One is to split up the string, then re-join it and ORDER BY that as a calculated column, http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring SELECT CONCAT( SUBSTRING(datefield, 7, 4), SUBSTRING(datefield, 4, 2), SUBSTRING(datefield, 1, 2) ) AS dateresult FROM yourdatabase . . . ORDER BY dateresult ASC The other is to cast it to a DATE type using STR_TO_DATE, then order by that : http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date SELECT STR_TO_DATE(`datefield`, '%d.%m.%Y') AS dateresult FROM test . . . ORDER BY dateresult ASC TBH though, you have a big headache here because none of that is going to be amenable to indexing. If you end up with several thousand rows after the WHERE statement (eg you select all rows) then the performance is going to be dreadful. MySQL will have to build a temporary table to hold the ordering - it'll do it in memory at first, then to disk if the resultset is too large (which is *really* bad for performance). Your best option if you have any control over the table schema at all, is to LOCK TABLES to avoid unintended changes, then create a new column perhaps using STR_TO_DATE as above to populate a proper MySQL DATE type column. Modify your application (PHP) script to do the strung munging/rearranging on input, so that's a job you only have to do once. Now you should add an index to the DATE column to get reasonable performance if this is a highly trafficked table in which you intend to order by the date field a lot. HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re:Multiple values in SELECT query
At 19:34 09/03/2008, you wrote: From: Ron Piggott [EMAIL PROTECTED] Date: Sun, 09 Mar 2008 15:34:05 -0400 Message-Id: [EMAIL PROTECTED] What is the correct syntax for where the results may be 1 or 2? What have I done wrong? SELECT * FROM table WHERE name LIKE ABC AND listing_type = 1 or 2 C'mon Ron, this is basic SQL. The query you provided would have given all rows where name was like 'abc', listing type was 1, then returned all rows because `OR 2` results in a value of 'true' which matches everything (you could also have written OR 2 = 2 with the same effect) SELECT * FROM table WHERE name LIKE ABC AND (listing_type = 1 OR listing_type = 2); SELECT * FROM table WHERE name LIKE ABC AND listing_type IN (1, 2); Now I've spoon fed you, please read the manual on Operator Precedence which explains how to combine stuff like this so you don't get the wrong result http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html : To override this order and group terms explicitly, use parentheses (first query above) Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Ordering a varchar field in mysql
At 09:44 04/12/2007, you wrote: Message-ID: [EMAIL PROTECTED] Date: Tue, 4 Dec 2007 11:12:40 +0530 From: Vaibhav Informatics [EMAIL PROTECTED] *Questions on PHP* On PHP, OK sure In one of our tables, one of the field is acc_no, we had given the data type as varchar, since it could take any alpha-numeric values. In viewing this, Oh, wait on *MySQL* (or some other database) ? You said on PHP above... You really should specify which database in that case. we used 'order by acc_no.' The sequence of records shown was 1,10,100,1000,A1, A10, A100, etc. whereas we want the sequence to be 1,2,3,4,etc. for all the numeric values followed by alpha-numeric values A1, A2, A3, etc. Can someone please give us the code for this type of ordering? In MySQL : http://blog.feedmarker.com/2006/02/01/how-to-do-natural-alpha-numeric-sort-in-mysql/ SELECT acc_no FROM your_table ORDER BY acc_no + 0 ASC In PHP : http://uk3.php.net/natcasesort natcasesort($your_resultset_array); The MySQL version possibly has a collation issue in some character sets, so you'd have to check the results in whatever collation you've declared on that column. You might have to use iconv() in PHP to make sure natcasesort works in the expected manner with non ISO-8859-1 or UTF-8 character sets. Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Accessing last inserted record in a PSQL Database table
At 23:50 26/11/2007, you wrote: Date: Mon, 26 Nov 2007 18:19:07 -0600 Reply-to: [EMAIL PROTECTED] [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] I need to access the last inserted record in a postgresql database table from php. I connect to the db using pg_connect I construct the query string, and I use pg_query to send the query. I get the record inserted, of course. One of the fields, which is my primary key, is an autoincrement field. I need to fetch this value. Any idea? I would really apreciate your helping me. SELECT primary_key_name FROM database_table ORDER BY primary_key_name DESC Just use whatever cursor or LIMIT you can in Postgres to get a single record. If it's autoincrement, then the last record ordered in descending order is, the last record ;-) Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: backup of database record before update
At 09:46 01/11/2007, you wrote: Message-ID: [EMAIL PROTECTED] Date: Thu, 1 Nov 2007 10:45:57 +0100 Leo G. Divinagracia III wrote: Stephen Sunderlin wrote: Neil, Have you had success with triggers. I couldn't get one to work and then saw discussion ... ...snip... ... on a 5.0.20nt box. one thing i read recently is triggers and stored procedures do use more system resources though... Why depend on different MySql infrastructures, while you can easily implement a perfect fit for your needs in a PHP routine? Because it's the job of the database to implement data access, referential integrity and storage. It's the job of the PHP code to manage business and application logic, and some data sanitisation. Finally, it's the job of the templating engine / output layer to manage actual display of content. I tend to try to move as much as possible to the Database, because it's often running on a separate machine or cluster. That frees up resources on the web servers to get and send data as quickly as possible, rather than have it spending all day constructing SQL strings and managing stuff the great engineers at MySQL have already invented to make my life simpler ;-) TBH it *oughtta* be much faster than having PHP do that, since (a) you're saving the roundtrip of the query to the DB from the web server and (b) the trigger query will probably end up quite rapidly in MySQL query cache on the DB in most cases. Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Dealing with Namespace/Colon in SimpleXML
At 06:57 28/10/2007, you wrote: Message-ID: [EMAIL PROTECTED] From: Andrew Darby [EMAIL PROTECTED] Hello, all. I'm trying to use SimpleXML to loop through an .xml file, but when I try to do the following, it chokes on the colon: foreach ($xml_data-dc:title as $item) { [do whatever here] } The element in the XML has a colon in it, i.e., dc:titleInteresting Book/dc:title Anybody know how to deal with this? I must be missing something obvious Yes, but only slightly. The Zend site has a good article on setting SimpleXML to handle the namespaced nodes : http://devzone.zend.com/node/view/id/688#Heading3 NB this is really a database mailing list from the point of view of PHP, you might need to consider an XML list in future ;-) HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: backup of database record before update
At 06:57 28/10/2007, you wrote: Message-ID: [EMAIL PROTECTED] Date: Sun, 28 Oct 2007 14:56:12 +0800 From: Ronald Wiplinger [EMAIL PROTECTED] I need some hints how to do that. Maybe there is a template or an easy function available. I have a MySQL database with some tables. Everytime a table record changes, I want also put the old record to a history database. The history table and the original only differs that the key of the original will be just a field in the backup, while the new key is now the UNIX time stamp. How can I do that easy? Easy is to set a MySQL5 trigger which does the Insert on update : http://dev.mysql.com/doc/refman/5.0/en/triggers.html eg (from the manual page) : DELIMITER | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | HTHCheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: union/select statement number of columns
At 19:31 19/10/2007, you wrote: Message-ID: [EMAIL PROTECTED] Date: Fri, 19 Oct 2007 09:24:38 -0600 Subject: union/select statement number of columns Hello all, I receive an error of the following: The used SELECT statements have a different number of columns. Any help, pointers, tutorials are appreciated. That's *completely* wrong SQL for what you're trying to do. Reading between the lines (you didn't say what you *really* want to do), you seem to want one order_item row and the details about its order for each order item. Your query needs to be : SELECT orders.*, order_items.* FROM orders LEFT JOIN order_items ON orders.order_id = order_items.order_id UNION is completely the wrong thing here - it can only compare identical things, you're trying to join together two different data columns (order, and order_items details) HTH Cheers - Neil Here are the two tables structure I am trying to pull from: Table 1 mysql describe orders; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(255) | NO | PRI | | auto_increment | | ordernum | int(10) | NO | | || | date | varchar(60) | NO | | || | time | varchar(20) | NO | | || | group | varchar(20) | NO | | || | purpose| varchar(255) | NO | | || | tracking | varchar(120) | NO | | || | contact| varchar(255) | NO | | || | eta| varchar(50) | NO | | || | department | varchar(125) | NO | | || | notes | varchar(255) | NO | | || ++--+--+-+-++ 11 rows in set (0.01 sec) Table 2 mysql describe order_items; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | int(11) | NO | PRI | | auto_increment | | ordernum| int(124) | NO | | || | quantity| int(124) | NO | | || | description | varchar(124) | NO | | || | price | decimal(10,0) | NO | | || | partnum | varchar(255) | NO | | || | vendor | varchar(255) | NO | | || +-+---+--+-+-++ 7 rows in set (0.00 sec) And here is the statement I am using (PHP): $query = ( SELECT * FROM `orders` WHERE ( `ordernum` LIKE $var\ OR `purpose` LIKE \$var\ OR `tracking` LIKE \$var\ OR `contact` LIKE \$var\ OR `date` LIKE \$var\ OR `time` LIKE \$var\ OR `eta` LIKE \$var\ OR `department` LIKE \$var\ OR `notes` LIKE \$var\ ) AND `group` = \$group\ ) UNION ( SELECT * FROM `order_items` WHERE ( `ordernum` LIKE \$var\ OR `price` LIKE \$var\ OR `partnum` LIKE \$var\ OR `vendor` LIKE \$var\ OR `quantity` LIKE \$var\ OR `description` LIKE \$var\ ) ORDER BY `ordernum` ); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] [Correction] Re: [PHP-DB] PHP + PostgreSQL: invalid byte sequence for encoding
The solution is pretty simple once you hit it, and works in both MySQL and PGSQL because it's standard SQL-92 : $query=SET NAMES 'UTF-8'; Sorry - I meant $query=SET NAMES 'UTF8'; As you pointed out, it's UTF8 not UTF-8 ;-) Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subject: sql statement - complex order by
At 20:36 02/07/2007, you wrote: Message-ID: [EMAIL PROTECTED] To: php-db@lists.php.net Date: Mon, 02 Jul 2007 14:34:35 -0500 From: Bryan [EMAIL PROTECTED] SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? See http://dev.mysql.com/doc/refman/5.0/en/union.html (SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title) UNION (SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title) HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: auto_increment
At 03:04 14/04/2007, you wrote: From: Ron Piggott [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: PHP DB [EMAIL PROTECTED] Content-Type: multipart/alternative; boundary==-xK5L7HjLIUaeMS/bzYQr Date: Fri, 13 Apr 2007 22:03:59 -0400 Message-Id: [EMAIL PROTECTED] Mime-Version: 1.0 Subject: auto_increment Does anyone see anything wrong with the $query syntax? Ron mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( Unable to select database); $query=ALTER TABLE sessions auto_increment = '1'; mysql_query($query); mysql_close(); Is sessions table supposed to have a column which is auto increment ? That column must be a PRIMARY KEY for autoincrement to be applied. sighsThough as-ever you haven't posted the error message/sighs presumably you haven't got a PK yet. Since we don't magically know your table structure since you didn't post that either, it's kind of hard to tell. Ron - in future please could I ask you to post both table structure and error message, when asking what's wrong ? Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: ORDERing two UNION queries
At 03:04 14/04/2007, you wrote: Message-ID: [EMAIL PROTECTED] Date: Fri, 13 Apr 2007 17:48:11 +0100 From: ioannes [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] MIME-Version: 1.0 To: [EMAIL PROTECTED] Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: ORDERing two UNION queries: Non-Subquery UNION Execution http://dev.mysql.com/doc/internals/en/select-union.html You meant to refer to this page : http://dev.mysql.com/doc/refman/5.1/en/union.html Does anyone know what this means? Can it be implemented in the flow of php code without explicitly creating temp tables in the database? My query is like: $query = (SELECT Table.field, [other fields] FROM Table [other JOINs] ORDER BY Table.field ) UNION (SELECT Table2.field, [other fields] FROM Table2 [other JOINs] ORDER BY Table2.field ) ; and I want the ORDER to apply to the whole UNIONed result. So, after reading the correct documentation, you'll now know to write the query as (SELECT Table.field, [other fields] FROM Table [other JOINs] ) UNION (SELECT Table2.field, [other fields] FROM Table2 [other JOINs] ) ORDER BY field Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Syntax Problem (Was : Date problem)
At 16:26 21/01/2007, you wrote: I have a date field in mysql called event_end . I want to run a query to find all records where the event_and is greater than today's date. I have written the following code. It does not work. Please point out the mistake. $today = getdate(); $sql=select * from events where event_end'.$today.' order by event_start Asc ; Because PHP allows you to quote variables inside strings surrounded by characters, your string $sql passed to the database will actually be as follows select * from events where event_end'.array.' order by event_start which you would have seen if you had used print($sql) to debug this. The reason if prints array is because - well getdate() returns an array, which you would have seen if you'd RTFM : array getdate ( [int timestamp] ). And there are dots surrounding the word `array` because - well, you put them there and used single quotes to ensure they were output literally in the string, rather than closing the string using a matching double quote character then re-opening it with another double quote character after the $today variable. Probably I would use the date() function to generate that date as a *string*, and make sure I quoted that string correctly when making the $sql string, eg like $today = date('Y/m/d'); $sql = SELECT * FROM events WHERE event_end '.$today.' ORDER BY event_start ASC; Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: user directory from a form
Message-ID: [EMAIL PROTECTED] Date: Sun, 01 Oct 2006 17:45:03 +0200 From: Neil Jackson [EMAIL PROTECTED] MIME-Version: 1.0 To: php-db@lists.php.net Content-Type: multipart/mixed; boundary=000308070500010902000303 Subject: user directory from a form I apologise for using this mailing list but I cannot find another. I have a form echo FORM ACTION='$PHP_SELF' METHOD='POST'; Although you correctly used POST for that form you also require to set the ENCTYPE=multipart/form-data as shown in the example here http://www.w3.org/TR/html4/interact/forms.html#h-17.13.4.2 in order to post file attachments, Snipped useless HTML (please post complete but minimal examples when requesting help) echo TDINPUT TYPE='file' NAME='myfile' WIDTH='50'/TD; I pass this to another file. I am trying to read the directory that the users file is in. ie When you use the enctype setting as above, PHP will populate the $_FILES array. Specifically for your field here, it will populate the $_FILES[myfile] variable. NB : I changed the form element's name to make it clearer which variable is created. In turn that is an array, and the actual value you want is where PHP saved the uploaded file. That value is contained in the varialbe $_FILES[myfile][tmp_name]; tmp_name always points to the PHP upload directory, on unix systems it's often /tmp The actual file name will *not* be that which your user supplied, it will be a unique and often cryptic name So you understand, this is *not* a PHP+DB related question. Your first resource should always be the PHP website documentation, which you can find here : http://uk.php.net/features.file-upload Ensure you understand the security issues regarding file uploads, and especially testing *if* the file really was uploaded, using is_uploaded_file() function before moving it or acting on the data. /srv/www/htdocs/functions/email/file.txt, I want to read the /srv/www/htdocs/functions/email/. The variable $filename only gives the file.txt as a value. In general, *never* move uploaded files into your web server's file structure if you can avoid it. The security issue is that the user now has a route to upload say a virus file to your server, and then point other users to your known server address and path. Your web server will probably dish out this file on request from the evil-hacker's link to your server. You are now consuming resources on his behalf, as well as apparently being the source-of-all-evil. So - time to read up on PHP file upload security, budget 1/2-1 day ;-)) HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: making an array from data in textfile
Message-ID: [EMAIL PROTECTED] Date: Sat, 22 Jul 2006 21:31:35 -0400 From: Dave W [EMAIL PROTECTED] To: Php-Db php-db@lists.php.net MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_Part_157142_2227727.1153618295222 Subject: making an array from data in textfile OK, so I have this text file that holds a list of ip addresses like this: 127.0.0.1,127.0.0.1,127.0.0.1,127.0.0.1,127.0.0.1 Which are strings... You with me OK so far ? Numeric [float] values only have 1 period. There's no numeric datatype which has 3. Currently, here is my code: $file = 'ip.txt'; $fh = fopen($file, 'r'); $theData = fread($fh, filesize($file)); fclose($fh); $ips = array($theData); Since it's a numeric array, No, it isn't. It's an array of strings which has numeric indices (or keys depends what you call em) I shouldn't need quotes around the ip octets. Yes, you should : They're strings unless you use inet_pton to convert them to numeric values. When I try to echo certain parts of the array like: echo $ips[0]; It's returning the whole array. If you print_r($ips) you'll see the array structure (View - Source if you're looking at it in a browser). My guess is your lines aren't terminated in \r\n, which PHP uses to split the lines to array values. You'll probably have to use some other method such as explode(',', $ips) to break the individual comma separated entries into a set of values. Other methods such as file_get_csv are generally more efficient than reading line by line : http://uk.php.net/manual/en/function.fgetcsv.php The text file is exactly like an array so it shouldn't be a problem. It's exactly like a CSV, from the example data you provided. PS, please don't feel the need to prove it by posting the entire log file ;-) Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Using MAX with COUNT?
Message-ID: [EMAIL PROTECTED] Date: Sat, 22 Jul 2006 13:48:43 -0600 From: Skip Evans [EMAIL PROTECTED] MIME-Version: 1.0 To: Php-Db php-db@lists.php.net Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: Using MAX with COUNT? Hey all, I have a table like this: boroughID Area = 1 Chelsea 1 East Village 1 West Village 1 So Ho 2 Prospect Park 2 Brooklyn Heights 3 Prospect Heights What I want to know is which boroughID has the most area's assocated with it, and how many. So I tried this: SELECT max(count(*)) FROM `bsp_area` GROUP BY boroughID ...and got an Invalid use of group function error ... Because you need to do count(boroughID) not count(*) Anyone think of another way to do this in a single SQL statement, or some other simple method? SELECT count(boroughID) AS total FROM `bsp_area` GROUP BY boroughID ORDER BY total DESC LIMIT 1 .. ought to do it g Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Parse Problem
At 10:04 09/05/2006, you wrote: Message-ID: [EMAIL PROTECTED] From: Andy Rolls-Drew [EMAIL PROTECTED] To: php-db@lists.php.net Date: Tue, 9 May 2006 10:03:54 +0100 Message-ID: !~!UENERkVCMDkAAQACABgAGw7HZJPFmUmqr9/[EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_0038_01C6734F.E1250C30 Subject: Parse Problem I am fairly new to parsing XML using PHP and wonder if anyone has a solution to the following. I have a file eg:- This is not your exact file (or it's not XML and therefore not parseable with any XML parser). You may have some luck adapting SAX to handle this, but it will be quite hard work. The document must be 'well formed' to be XML data : see http://www.w3schools.com/xml/xml_syntax.asp If you were able to post a complete but minimal example, code could be provided to parse it correctly as XML. I'm very familiar and happy with working with XML data but it *has* to meet the requirements of XML data. ?xml version=1.0 ? Cannot begin with 2 characters, must begin with one. STOREITEMS CREATED value=Wed Mar 15 8:07:27 GMT 2006 ^^^ This tag is never closed. CATEGORY id='67' name='Herbal Pharmacy' Oh good, a Spam product - gra ! This tag is never closed, either. PRODUCT ITEM='603' Nor is this closed - you see the pattern here ? NAME/NAME MODEL/MODEL PRICE/PRICE PRODUCT ITEM='608' NAME/NAME MODEL/MODEL PRICE/PRICE CATEGORY id='69' name='Extras' And so on. I have managed to parse a little of it using Magic Parser but I Yes, you have pulled off a 'miracle' then! I don't know what magic parser is, but it's not an XML parser. still cant manage to split the products and categories correctly. If there weren't multiple products for each category I think I would have it. Correctly *how* ? What are you trying to achieve ? Without knowing that, we'll all waste our time trying to help. It would make sense to propose a problem and ask for solutions - the data above is pretty much unworkable without having to jump through all sorts of hoops. So lets' begin again : Your data is 'similar' to the above (what's the source ?) and what is the result you want to achieve ? HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query select value on MAX Date
At 08:47 25/02/2006, you wrote: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=_=_NextPart_001_01C63997.1A6B1B7D Date: Sat, 25 Feb 2006 08:07:36 +0900 Message-ID: [EMAIL PROTECTED] From: [EMAIL PROTECTED] To: 'php-db@lists.php.net' Subject: FW: [PHP-DB] Query select value on MAX Date Dear All, Sorry, I wrote the wrong result. You've posted 3x with different results so it's too confusing to work out what you *want* to get. However I woudl write that query like so : SELECT MAX(Date) as NewestDate, Value FROM table GROUP BY Category HAVING Date=NewestDate HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Sessions help needed !!!
At 22:19 17/02/2006, you wrote: From: Chris Payne [EMAIL PROTECTED] To: php-db@lists.php.net Date: Fri, 17 Feb 2006 17:18:57 -0500 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_000F_01C633E6.3C6626A0 Subject: Sessions help needed !!! Hi there everyone, OK this script worked perfectly on my own apache webserver and I had to move it to the main live server, but for some reason it's not passing session values in the same way and i'm positive it's something damn obvious. On my server I can use: echo $credits_system; echo $credits_left; echo $foldername; Does it work if you ask for $_SESSION[credits_system] etc ? The reason I ask is from the code above, you're using globally scoped variables, which would require you to be using either an old version of PHP on your test server, or have to manually enabled register_globals. Register globals is of course a major security risk in that it pollutes your variable namespace with whatever the user feels like sending to your server in $_GET, $_POST, $_COOKIE etc etc. So your code can be easily manipulated into undefined behaviours unless you declare and initialise every variable it uses. To display the information to make sure it is being passed, but it returns blank on their server (Same versions of everything except I didn't install it so it may have something turned off in the config - which i don't have I think you need to look at phpinfo() for that server. Check register_globals, it will be 'off'. Turn it 'off' on your test server so it mirrors the live environment and see if your code still works (it probably won't). The other check to make with the hosting company - I've only seen this once or twice on cheap hosting : See if they're using multiple servers. The session handler by default uses files, which are local to an individual server. If you visit the 'page' again, you may well be being server from another web server in a cluster. Of course that server knows nothing about the local session files on the server you initially got sent the page from, cause they're on another machines' filesystem, thus replicating your problem. The answer in that case is to register your own session handler (such as a DB) which resides on a known server. control over, sigh). The thing is, the last one - $foldername I MUST have access to as the database uses this as a reference for searches and without I don't understand what you mean by that, can you expand some more ? It's not clear how the database uses 'foldername' - is it concatenated as part of a query ? this working I can't pull the data I need ($foldername is an ID and also refers to physical folders/directories on the apache webserver for video If as noted above your host clusters servers, then you're SOL and will have to manage a central file repository or call across servers with fopen() etc to get at the [distributed] data in the $foldername path. Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: screen resolution!
At 08:41 05/02/2006, you wrote: Message-ID: [EMAIL PROTECTED] Date: Sun, 5 Feb 2006 02:44:19 +1100 (EST) From: JeRRy [EMAIL PROTECTED] To: php-db@lists.php.net MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=0-733704850-1139067859=:48594 Content-Transfer-Encoding: 8bit Subject: screen resolution! Hi, I have written a website in PHP using MYSQL. But I have come accross an un-common problem. Normally when I create a website it's done on a desktop PC. But this time for the first time I did it on laptop meaning the screen resolution is different. Is there any sort of script/code I can use to create another section of my site in a desired screen resolution without me having to do it all manually? I would really recommend that screen size / resolution detection is no longer the way forward. It's perfectly possible to use CSS +/- XHTML correctly to have the page reflow without all the stylesheet switching hacks that would be required in the 'old school' (read : 1999) way of doing things. You'd need to use structural markup rather than try to 'force to fit' with tables etc, but it's entirely possible to generate flexible layouts that can display on any device, even a PDA, without screen size detection. Search engines like sematic layout too, and your menu for example may not be the first thing they or a screen reader encounters in the page - though it may *appear* at the top of the page due to CSS telling it where to be displayed. Here are some links for you to consider : all are worth reading and digesting from end to end. http://glish.com/css/8.asp http://www.alistapart.com/stories/journey/ http://www.positioniseverything.net/articles/onetruelayout/ http://www.csszengarden.com/ I know of many scripts online that I can DETERMINE visitors screen resolutions and recommend the ** Assuming javascript is turned on, or even the display device supports that detection. And bear in mind, many users *may not wish* to have their browser maximised just to view your site. HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Freelancers, UK ?
Any UK based freelancers out there ? A contact at our sister company EPC Direct in Bristol is looking for somebody to tackle a couple of ECom sites. Need advanced PHP/MySQL, also expected skills in XML and XSLT - I don't have full details here of the brief. Duration likely to be one month over the two builds. Ideally in the South West UK, not expecting travel to be involved though apart from maybe an initial briefing and/or a live publish step near the end. Wave hands or email me off list and I'll send the contact details along. Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Storing Credit Cards, Passwords, Securely, two-way encryption
From: Dwight Altman [EMAIL PROTECTED] To: 'Peter Beckman' [EMAIL PROTECTED], 'Neil Smith [MVP, Digital media]' [EMAIL PROTECTED] Cc: php-db@lists.php.net Date: Mon, 9 Jan 2006 09:24:05 -0600 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Subject: RE: [PHP-DB] Storing Credit Cards, Passwords, Securely, two-wayencryption Did you actually SNIP the document[ation] how it can be done safely for all the world to see and learn! ??? Or are you saying go buy this book? No, I retyped the passage in its entirety from the book. Learn to SNIP your posts ! Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing Credit Cards, Passwords, Securely, two-way encryption
At 03:48 06/01/2006, you wrote: Date: Thu, 5 Jan 2006 22:48:24 -0500 (EST) From: Peter Beckman [EMAIL PROTECTED] To: John Meyer [EMAIL PROTECTED] cc: php-db@lists.php.net Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed Subject: Re: [PHP-DB] Storing Credit Cards, Passwords, Securely, two-way encryption On Thu, 5 Jan 2006, John Meyer wrote: Peter Beckman wrote: So I'm thinking about how to save credit card numbers in the DB, for re-charging cards for subscriptions, new orders, etc. Yes yes, lawsuits, scary, etc. I'm glad you're so blase about this and the threat of your business going under due to exposure to extortion. When you've got the site running, let me know the address, so I can advise my friends and colleagues to avoid it at any cost. I was looking for technical solutions, i.e. maybe someone knows how USPS.com or Amazon.com or GoDaddy.com (do they?) does it. Or if it is all security via obscurity. Security by obscurity is a myth. There is no such security, nor will there ever be (think of MS DRM cracks for example on a supposedly secure system). Any system that interacts with the outside world *can* and eventually *will be cracked. Best solution yet: Nope, no solution at all. *DO NOT* store any credit card numbers on any publically accessible system. Ever. Period. Public key encryption, with additional either secret word padding or using the users account password to pad/encrypt the card number (preventing a brute force attack, even if access to the DB is given). Prevents nothing. If somebody compromises your application server, then own your secret word padding and can reverse the process to extract some or all of the credit cards. Do not underestimate the resourcefulness of bored people. OK now to the candy : I've had this book a while, and it's one of the most insightful and well researched (from experience) books on security I've ever read. In fact - so good I'm going to go to the trouble to retype an excerpt of a section called One-Way Credit Card Data Path for Top Security ISBN 0-13-0281870-5 [Prentice-Hall publishers] Bob Toxen : Real World Linux Security [Now in 2nd edition] For many sites the most confidential information a customer can tell the site is the customers credit card number and expiry date. Several e-com sites (including some large ones) have had thousands of their customers cc data stolen by crackers and have then had to respond to extortion threats. Most e-com sites keep the database of customer information on the same system as the web server and CGI programs. This is begging for trouble. Simply putting the database on a separate system is not enough, because if CGI programs can attach to the database across the e-com sites LAN, security has not been improved. (Bob Toxen) have come up with the concept of a one-way credit card data path. By this I mean that the credit card data flows only one way, and that way is into the credit card server but data never flows out of the credit card server (my emphasis) except over a hardware path to the bank or service that is processing charge requests. The cc system would be a (linux) system dedicated to this one application. It would have NO other applications on it, because each application would be a potential security hole. It would be hardened for the highest security. It would have a separate private LAN to the web server, and the web server would have a separate dedicated NIC to this private LAN to prevent sniffing (snipped section about spot welded steel pipes encasing LAN cable !) There would be no request implemented that would allow another system to query for a complete CC number under any circumstance. Thus neither a cracker hacking your web server, nor a disgruntled employee could get the CC data from it. So long as there are no buffer overflow vulns, this should be very secure, since there are no services to crack, no passwords to crack, and spoofing would not work because the system doesn't trust any other system at all. When a customer establishes an account and specifies a CC, the CGI sends the following message to the CC server : ADD user name account number cc type, number, expiry date, CCV# When the customer wants to make a purchase, this is sent to the CC server CHARGE user name account number amount The CC server then contacts the processing bank through the private network to charge the amt, store the authorisastion number if successful and returns either Success or an appropriate error message Note that the comms link to the bank would be on a separate hardware to the rest of the network, so if a cracker broke into the web or order DB server, he could not sniff the network for these requests, because they wouldn't be on that network. If the user has
[PHP-DB] Re: Cannot connect to a MySQL DB using a Class
At 00:46 05/01/2006, you wrote: Message-ID: [EMAIL PROTECTED] To: php-db@lists.php.net Date: Wed, 04 Jan 2006 16:45:54 -0800 From: Todd Cary [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: CAnnot connect to a MySQL DB using a Class No doubt I am over looking something very simple, nevertheless I am baffled by not being able to connect to a MySQL DB. // Error produced here!! $this-db=mysql_connect($this-host,$this-user,$this-password); mysql_select_db($this-database); Surely you meant mysql_select_db($this-db); since that's what you connected to ? Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Scrolling News
At 20:39 22/12/2005, you wrote: Date: Thu, 22 Dec 2005 20:38:58 + From: Alex Major [EMAIL PROTECTED] To: php-db@lists.php.net Message-ID: [EMAIL PROTECTED] Mime-version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7bit Subject: Scrolling News Hi there. I'm trying to make a scrolling news box for my website. Basically I would like this news box to get information from a column in my database called 'news_title' and display it in a scrolling news box. Any suggestions for how to go about doing this? I'm still very new to php. Recommend : OrcaScroller (cross browser) http://www.greywyvern.com/javascript Block scroller and marquee Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re : Subject: using fsockopen to handle redirections
Date: Fri, 23 Dec 2005 10:04:16 + From: Yemi Obembe [EMAIL PROTECTED] To: php-db@lists.php.net Subject: using fsockopen to handle redirections Any way i can use fsockopen to detect url redirections? for example if ds: www.ex1.com redirects to www.ex2.com and i'm using: $fp = fsockopen(www.ex1.com, 80) to connect to the site, anyway i can know if there is a redirection to another url? Probably rather than fsockopen it would be helpful to use the CURL library to make the connection(s). You should install the CURL extension to PHP, more information at http://uk2.php.net/curl and http://www.zend.com/manual/function.curl-setopt.php (^Find : CURLOPT_FOLLOWLOCATION) This page http://curl.haxx.se/mail/curlphp-2002-02/0005.html contains useful discussion about that. Basically you'll want to set CURLOPT_FOLLOWLOCATION which will allow CURL to respond to the redirect header as if it was a browser. If you're not using CURL then you've got significant extra work to look at each individual header sent by the redirecting web server to determine which header is the redirect and which location it's sending you to (probably using a regex). Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: CLOSE button or a href link
At 07:18 22/12/2005, you wrote: From: Ron Piggott (PHP) [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: PHP DB php-db@lists.php.net Content-Type: text/plain Date: Thu, 22 Dec 2005 02:22:00 -0500 Message-Id: [EMAIL PROTECTED] Mime-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: CLOSE button or a href link What I really want to have happen is for the CLOSE WINDOW button to appear when this is loaded as a pop up window from our online_offering_basket.html file and if the web page is loaded directly then the link to our Online Offering Basket appear for the user to get back into our web site. Do you know how to do this? Yes, and this is a client side javascript problem unless you create the popup page using a PHP script. AFAIK from your file paths, it's HTML so PHP (and indeed, DB databases, the subject of this discussion group) don't apply here. You need to run a short snippet of javascript to determine if there's a window.opener object : That will point back (and have a .location.href property) of the opening page. I'd check for script language=javascript1.2 type=text/javascript if (window.opener != null) { // (Probably) sent here from the online_offering_basket.html page // Check that though to prevent spoofing : if (window.opener.location.href.indexOf('online_offering_basket.html' != -1) { document.write('a href=# onclick=self.close()Close Window/a'); } else { document.write('a href=http://www.actsministrieschristianevangelism.org;Back To Site/a'); } } else { // Linked directly from the how_we_operate_our_benevolent_fund.html page document.write('a href=http://www.actsministrieschristianevangelism.org;Back To Site/a'); } /script -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Date Formatting Question
Apart from anything, you need to supply an option value for each of those options, not just rely on the browser which will default to passing the option *text* back from the form, if you specify no value. So your option elements should read option value=1January/option Notwithstanding that, RE below - you guys are all nuts, this is a ridiculous way to do it. Let MySQL do the work, it has perfectly good functions to do this : SELECT DATE_FORMAT(date_field_name, '%c') AS intDate, DATE_FORMAT(date_field_name, '%M'') AS strMonth FROM yadda etc etc Then you use (assuming your're using $result = mysql_fetch_assoc() as your looped result variable name) $optionlist=''; $selected_month = (integer) $_GET[Smonth]; while ($result = mysql_fetch_assoc($link)) { $optionlist.=option; if ($selected_month==$result['intDate']) { $optionlist.=' selected=selected'; } $optionlist.=''.$result['intDate'].''.$result['strMonth']./option'\r\n\t; } // End while print($optionlist); HTH Cheers, Neil To: php-db@lists.php.net Date: Wed, 14 Dec 2005 15:52:31 +0100 From: El Bekko [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Subject: Re: Date Formatting Question Bomgardner, Mark A wrote: I am trying to format the month portion of a date that I am trying to pull from MySQL to be placed into a drop down menu to modify the date. ...snip... modify and it would be easier to do it in PHP Any pointers would be appreciated. Mark Bomgardner Technology Specialist KLETC make a function like this: function datenum2str($date) { $newdate = str_replace(01,Jan,$date); $newdate = str_replace(02,Feb,$date); $newdate = str_replace(03,Mar,$date); $newdate = str_replace(04,Apr,$date); $newdate = str_replace(05,May,$date); $newdate = str_replace(06,Jun,$date); $newdate = str_replace(07,Jul,$date); $newdate = str_replace(08,Aug,$date); $newdate = str_replace(09,Sep,$date); $newdate = str_replace(10,Oct,$date); $newdate = str_replace(11,Nov,$date); $newdate = str_replace(12,Dec,$date); return $newdate; } And then use this: echo select name=Smonth; echo option selected value=\$sDate[1]\$newdate/option; Hope it helps, El Bekko CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subject: GROUP BY [Was: SELECT]
From: Ron Piggott (PHP) [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: PHP DB php-db@lists.php.net Content-Type: text/plain Date: Sat, 10 Dec 2005 20:04:28 -0500 Message-Id: [EMAIL PROTECTED] Mime-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: SELECT I am trying to put together a SELECT syntax. I am querying a response database and I only want to obtain each user's name once even if they have given more than 1 response. $query=SELECT * FROM conversation_table WHERE conversation_reference = $conversation_currently_displayed; ... Add GROUP BY conversation_user_id or whatever column you use to identify the user. http://dev.mysql.com/doc/refman/5.0/en/select.html Note that GROUP BY comes *before* any ORDER BY in your select (which you aren't doing). By default, MySQL will order by the grouped columns initially. Other databases you'd have to include the GROUP BY column(s) in the select * statement, but MySQL allows you to do this with non-selected fields as well. Then spend some time on the MySQL site looking at the 'aggregate functions' area now you've grouped the results - you'll find some useful stuff you can do once that's happened : http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Login Auth help?
NO ! The headers have to be sent *after* you check the values of $_SERVER[PHP_AUTH_USER] , which you changed inexplicably to $PHP_AUTH_USER (which is no longer a global variable in recent versions of PHP 4.1). If they are not global variables within PHP then it'll treat them as local variables within that function (and they'll always be empty) Check those values first, then if they don't match you're here for the first time (and they're blank) or you haven't got a match from the submitted username / password, so send the authentication headers again. Then *exit your script* or you will loop. Use exit; to do this. Cheers - Neil At 21:15 11/11/2005, you wrote: Do you Yahoo!? Never miss an Instant Message - Yahoo! Messenger for SMS Message-ID: [EMAIL PROTECTED] Date: Fri, 11 Nov 2005 15:22:14 +1100 (EST) From: JeRRy [EMAIL PROTECTED] To: php-db@lists.php.net MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=0-976019534-1131682934=:5427 Content-Transfer-Encoding: 8bit Subject: Re: Login Auth help? Hi, Well I tried this code but it fails, if I enter a correct User and Pass set in the db it just prompts for the user/pass again. The error message that should apply if wrong three times appears but the sucessful message I can't get regardless of correct user pass or not, any help please? ?php function displayLogin() { header(WWW-Authenticate: Basic realm=\My Website\); header(HTTP/1.0 401 Unauthorized); echo h2Authentication Failure/h2; echo The username and password provided did not work. Please reload this page and try again.; exit; } $db = mysql_connect('localhost','db_user',db_pass') or die(Couldn't connect to the database.); mysql_select_db('db_name') or die(Couldn't select the database); if (!isset($PHP_AUTH_USER) || !isset($PHP_AUTH_PW)) { // If username or password hasn't been set, display the login request. displayLogin(); } else { // Escape both the password and username string to prevent users from inserting bogus data. $PHP_AUTH_USER = addslashes($PHP_AUTH_USER); $PHP_AUTH_PW = md5($PHP_AUTH_PW); // Check username and password agains the database. $result = mysql_query(SELECT count(id) FROM users WHERE password='$PHP_AUTH_PW' AND username='$PHP_AUTH_USER') or die(Couldn't query the user-database.); $num = mysql_result($result, 0); if (!$num) { // If there were no matching users, show the login displayLogin(); } } // All code/html below will only be displayed to authenticated users. echo Congratulations! You're now authenticated.; ? CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re:Login Auth help?
At 15:21 10/11/2005, you wrote: Message-ID: [EMAIL PROTECTED] Date: Thu, 10 Nov 2005 11:24:09 +1100 (EST) From: JeRRy [EMAIL PROTECTED] To: php-db@lists.php.net MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=0-1489996746-1131582249=:37621 Content-Transfer-Encoding: 8bit Subject: Login Auth help? | Handling pages help? (2 questions) Hi, Need some ideas/opinions on a project I have. The project is for a Radio Station (online one) where DJ's can login to the site and do stuff and listeners can listen in live to the live feeds. Now I downloaded a password protected code that uses MySQL to store the info but it fails each time. It wont sucessfully login despite setting the database up correctly etc. So instead of posting the code here I would like to know peoples ideas/links on a method of login. What I'd prefer to set is not a HTML login but more the traditional popup box login. Like used on CPANEL. The popup box is called HTTP authentication. There are some specific $_SERVER variables associated with this which you can use - AFAIK they weren't and still aren't reliably available on windows installs of Apache on PHP, but on BSD / Linux / OSX installs it should work fine. You should use code adapted from this, then when you have $username and $password, check both against your database (making sure they are made 'safe' by removing slashes and so forth) : I use a function called safestring() which does that stripping, else you can write something yourself (such as use addslashes() from PHP) if (isset($_SERVER[PHP_AUTH_USER])) { $username=safestring($_SERVER[PHP_AUTH_USER]); $password=safestring($_SERVER[PHP_AUTH_PW]); } else { header(WWW-Authenticate: Basic realm=\My Music Server\); header(HTTP/1.0 401 Unauthorized); echo You must enter a valid login ID and password to access this resource\n; exit; } The first time the script is hit, $_SERVER[PHP_AUTH_USER] is *not* set so it pops up the HTTP authenticate box using the headers shown. When they are set, you can check the database, and see if the login is valid. If it isn't a vaild username/password, send those headers again until the user gets it right - possibly locking out their IP for an hour after 3 unsuccessful attempts. The username/password (details) will be stored to a database. Any suggestions on fully working code? Go to www.phpbuilders.com and search for 'database login code', we won't duplicate it here. Also each DJ will have a webpage within a database. Why a webpage - did you mis-type that ? Better just to store some information about them in the database and create the page dynamically from that. Otherwise you manually have to edit the database entry each time you want to update their page ! So updates to their pages will be made on the database, as I am not really good at writing to a text file from PHP from a webpage. Never learnt it to be honest, what would be easiest/best? Writing to a database or a text file directly? Database, it's what they're designed for. That is it for now, if anyone is interested in the station itself to listen to or see progress please feel free to reply and I'll post the url. PS I'm working on the same system as you, online radio/webcam etc so maybe it'd be nice to see what work you've done so far. HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Specific order by MySQL statement
At 09:37 08/11/2005, you wrote: Message-ID: [EMAIL PROTECTED] Date: Mon, 07 Nov 2005 11:47:32 + From: Adrian Bruce [EMAIL PROTECTED] MIME-Version: 1.0 To: php-db@lists.php.net Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: Specific order by MySQL statement Hi I am trying to get results from a MySQL query in a specific order, just using ASC or DESC will not work. For Example I have a field that can contain 4 possible values: 'Current', 'Completed','Withdrawn' or 'Transferred', I would like to order the results specifically like: Current Completed Withdrawn Transferred Is there any way i can do this using MySQL? Any help will be much appreciated. Yes you can do this just fine. Make your transactiontype field an ENUM data type (i.e. a list of defined values). Add your ENUM elements in the order you want them to be retrieved (they're actually stored internally as bitwise mask values, i.e. as a number). Now, when you ORDER BY transactiontype, customerid , your results are ordered in the way the field was specified. So if you want this ordering, use ALTER TABLE `bankdetails` ADD `transactiontype` ENUM ('Current', 'Completed', 'Withdrawn', 'Transferred') Or to change the default ordering, ALTER TABLE `bankdetails` ADD `transactiontype` ENUM ('Current','Transferred', 'Completed', 'Withdrawn') If you do find there's a change needed to the ordering in future, probably the best way is to create a new column with the chosen ENUM ordering, then UPDATE bankdetails SET newfield=transactiontype , which makes sure MySQL re-maps the numeric representation correctly. HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Complex Left Join
At 20:28 31/10/2005, [EMAIL PROTECTED] wrote: Message-ID: [EMAIL PROTECTED] From: Keith Spiller [EMAIL PROTECTED] To: [PHP-DB] php-db@lists.php.net Date: Mon, 31 Oct 2005 13:29:18 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_0159_01C5DE1F.184D51C0 Subject: Complex Left Join Hi, Can anyone help me turn this into a left join? I want to get all of the records from the directors table whether matches exist in the members table or not. SELECT d.directorid, d.fname, d.mname, d.lname, d.title, d.suffix, d.active, d.function, m.id, m.directorid, m.committee, m.position, m.year FROM directors as d, members as m WHERE d.directorid = m.directorid AND m.year = '2006' ORDER BY d.lname, d.mname, d.fname; Thank you for your help... Keith - it should read as : SELECT d.directorid, d.fname, d.mname, d.lname, d.title, d.suffix, d.active, d.function, m.id, m.directorid, m.committee, m.position, d.year FROM directors as d LEFT OUTER JOIN members as m ON d.directorid = m.directorid WHERE d.year = '2006' ORDER BY d.lname, d.mname, d.fname; BTW Anything with no match, would have values of NULL for any fields of the m alias. You can't do m.year since if you have no match, there's no result to use for the WHERE so it would leave those rows out completely. You have to match on d.year where you're matching all results from d to m (including some with no matching record in the m table, but you still want the d. parts of the query). Does that make sense ? Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Searching remote web sites for content
At 06:26 23/10/2005, you wrote: Message-ID: [EMAIL PROTECTED] Date: Sat, 22 Oct 2005 13:21:26 -0400 From: Joseph Crawford [EMAIL PROTECTED] To: [PHP-DB] Mailing List php-db@lists.php.net MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_Part_33359_9054580.1130001686839 Subject: Re: [PHP-DB] Re: Subject: Searching remote web sites for content why do all that, Oh, it's far less work than the method you're proposing - you only have one site to fopen() not many dozens. There's no 'all that' to it - it's the same method we're discussing, but more optimal (see point 3) if you know the address of the page that the link will reside on just curl that page for the results and preg_match that. Ref the OP : I ask them to nominate where the link back page is, and I could check this manually. But is there a way to check whether the remote page links back using a php script, so that I could get a report and follow up on exceptions, without having to check all pages that say they link to my site? Three reasons : 1 is because the nomination process might be poorly understood by the nominee, or they could be inept and place the link somewhere other than where they specified (or move it about once nominated). You'd need to be able to crawl their entire site in order to automate the scan on a regular basis, or you're back to and I could check this manually 2 is that unless you want to write a very very robust parser, you may as well rely on google's hard work writing such a parser. You can't be sure *how* the referring webmaster has set up his links (re:inept) so they could occur in a wide range of formats. The results from google come in a regular format, so they're easy to parse - and you said yourself you're not too certain of the regex you'd need - why complicate it by having to cover dozens of eventualities ? 3 is that the point of the exercise is to ensure goos SE rankings by having referring links of high relevance. Only google knows how that relevance ranking results in a search index placement based on link popularity - and that includes using hidden links to 'spam' the search engine, whic you don't want. So, relying on google to spider the remote site is a way to ensure your QA process for the link referrals really does result in a usable link:mysite index in the search engine - which of course is *the whole point of the exercise* ! HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Searching remote web sites for content
At 16:17 22/10/2005, you wrote: Message-ID: [EMAIL PROTECTED] From: ioannes [EMAIL PROTECTED] To: php-db@lists.php.net Date: Sat, 22 Oct 2005 16:17:22 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=iso-8859-1; reply-type=original Content-Transfer-Encoding: 7bit Subject: Searching remote web sites for content I have a web site and google likes to count the inbound links. I have set up a way for people to add links from my site to theirs, however I would like to check whether they have linked back to my site. I ask them to nominate where the link back page is, and I could check this manually. But is there a way to check whether the remote page links back using a php script, so that I could get a report and follow up on exceptions, without having to check all pages that say they link to my site? Yes, you can - exploit Google's search to do this. You need to run a query for link:mysite.mydomain.com then screen-scrape the results. IE You'd curl or fopen() the pages with, for example http://www.google.co.uk/search?q=link:www.captionkit.comhl=enlr=start=10sa=N The for each page returned, use a regex to extract the HTML returned from Google, eg on p class=ga href=http://archive.netbsd.se/?ml=php-databasea=2004-10m=430433; onmousedown=return clk(this.href,'res','18','')archive.netbsd.se - NetBSD Sverige/a You just want a capture pattern to extract the href value, which you then store in your database. Before you accuse anybody of anything, ensure you've waited a few days for google to re-spider their site. If their site doesn't appear in the index at all, it may be because google doesn't or can't spider it, rather than the back link isn't there - but in that case their link popularity is ineffectual and may as well be ignored ! HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE : Subject: com and retrieve text from word
Message-ID: [EMAIL PROTECTED] Date: Wed, 31 Aug 2005 12:54:42 +0900 From: Yui Hiroaki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] MIME-Version: 1.0 To: php-db@lists.php.net Content-Type: text/plain; charset=ISO-2022-JP Content-Transfer-Encoding: 7bit Subject: com and retrieve text from word I would like to get text from MS word using COM. My plat form is Windows 2003 server. Do suggest me to retrieve text!! Yui Some example code here : http://www.phpbuilder.com/annotate/message.php3?id=1008686 Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: HTML layout and arrays (WAS : Brain not working, helpneeded :-). .
Date: Mon, 29 Aug 2005 07:46:52 -0500 Message-ID: [EMAIL PROTECTED] From: Norland, Martin [EMAIL PROTECTED] To: php-db@lists.php.net Reply-To: php-db@lists.php.net Subject: RE: [PHP-DB] Re: HTML layout and arrays (WAS : Brain not working, helpneeded :-).. -Original Message- From: Neil Smith [MVP, Digital media] [mailto:[EMAIL PROTECTED] The variable type you need is called an array, it has numerical indexes : In PHP you can just omit the index and it'll be automatically auto-incremented from zero, so there's no need to maintain a counter : $videos=array(); while ($row = mysql_fetch_array($sql_result)) { $videos[][id] = $row[id]; $videos[][video_link] = $row[video_link]; $videos[][video_thumbnail] = $row[video_thumbname]; $videos[][video_title] = $row[video_title]; $videos[][video_description] = $row[video_description]; }; print_r($videos); [snip] You either want to be putting a $count in there, or assigning it all in one go, otherwise you're left with: $videos[0] == $row[id] $videos[1] == $row[video_link] etc. Yeah LOL, /me slaps forehead ! It's been a long weekend, the correct solution is as Martin stated below : while ($row = mysql_fetch_array($sql_result) { $videos[] = array( 'id' = $row['id'], 'video_link' = $row['video_link'], 'video_thumbnail' = $row['video_thumbname'], 'video_title' = $row['video_title'], 'video_description' = $row['video_description'], ); } Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE : HTML Layout (WAS : DB formatting question)
Message-Id: [EMAIL PROTECTED] From: Chris Payne [EMAIL PROTECTED] To: php-db@lists.php.net Date: Sat, 27 Aug 2005 02:31:13 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000__01C5AAAF.64FE18E0 Subject: DB formatting question Hi there everyone, I'm converting a video website from CGI to use Databases and they want me to keep their existing layout. However, I have a slight formatting problem. This is a HTML problem (not PHP or MySQL or even video problem !) Each page displays 6 videos, 2 are on the first row (Which has 3 columns with the 2nd one being a hardcoded flash video) so the first 2 videos go on the left and on the right, then below that is another row with 4 columns which have to display the other 4 videos. How can I format the table so that when the MySQL data is read in the loop, MySQL is unrelated to this, just store the video in 6 variables and populate the table cells with the HTML from those. Any basic tutorial on HTML can show you how to layout rows and columns. but it needs to be 3 columns across on 1 row with the middle column not having any MySQL data and the 2nd row needs to have 4 videos, 1 in each of the 4 columns. So like this : (OK ok nested tables aren't elegant, you could use CSS and DIVs instead) !-- Start layout table -- table width=100% border=0 cellspacing=2 cellpadding=2 tr id=row1 td width=33%?=$video_1_html?/td td width=33%nbsp;/td td width=33%?=$video_2_html?/td /tr tr id=row2 td colspan=3 !-- Start inner video layout table -- table width=100% border=0 cellspacing=0 cellpadding=2 tr td width=25%?=$video_3_html?/td td width=25%?=$video_4_html?/td td width=25%?=$video_5_html?/td td width=25%?=$video_6_html?/td /tr /table !-- End inner video layout table -- /td /tr /table !-- End video layout table -- HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re : Subject: floats
Message-ID: [EMAIL PROTECTED] Date: Tue, 19 Jul 2005 15:16:47 -0400 From: blackwater dev [EMAIL PROTECTED] Reply-To: blackwater dev [EMAIL PROTECTED] To: php-db@lists.php.net Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Subject: floats I am running a query on MySQL: select * from cars where id =52 It returns the row and I can see that gas mileage is saved as a float with a value of 23.45. So I then do a query, select * from cars where id=52 and gas_mil=23.45 and the query doesn't retun anything. Why? Do I have to cast this as a float? Although it's possible that the float has a value of 23.4501, I wouldn't expect that to be an issue. Instead, check that your gas_mil name is *exactly* that and not followed by a space (I've done it before !) For example thisSELECT * FROM cars WHERE `gas_mil` = 23.45 is NOT equivalent toSELECT * FROM cars WHERE `gas_mil ` = 23.45 Notice the extra space in the column name. If that doesn't work do a query for SELECT * FROM cars WHERE id=52 AND gas_mil BETWEEN 23.4 AND 23.5 See what happens then, Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subject: RE: [FIX YOUR SUBJECT !] php-db Digest 20 Jun 2005 23:54:37 -0000 Issue 2992
Hi - Suggestions : 1) Turn on error_reporting in your PHP script : ?php error_reporting(E_ALL); ? 2) Load page, and go to browser's View-Source menu, to see if you actually got malformed HTML or anything at all. Cheers - Neil From: Cosman CUSCHIERI [EMAIL PROTECTED] To: php-db@lists.php.net Date: Wed, 22 Jun 2005 15:29:47 +0200 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Subject: RE: php-db Digest 20 Jun 2005 23:54:37 - Issue 2992 I make part of a team where we have created a system which produces reports. The reports are produced based on selection criteria which the user sets from an oracle database. The problem we are facing is that when the users selects a big time frame the report just freezes, the status bar displays 'done' the IE (our preferred browser) window flag stops animating and report does not show anything, only a blank page. I am suspecting a time out somewhere. I am using the function set_time_limit and some of the reports did improve but still facing the problem with others. Each report has a main SQL based on which the report is generated. I am guessing setting set_time_limit prolongs the timeout of the generation of the report by php, but i am suspecting, either: 1 - The sql is taking too much time to get back with some data on which php will still time out. 2 - Some other time out exists (e.g. Apache). if so 1 can somebody advice how to set this other time out if it exists or some other way to workaround this. if so 2 do somebody knows how to override this through php or some other way to workaround this. Any feedback / ideas are very much welcomed Cosman CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE : Subject: Com 1
From: Nandar [EMAIL PROTECTED] To: php-db@lists.php.net Message-ID: [EMAIL PROTECTED] Date: Tue, 14 Jun 2005 10:01:59 +0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_0007_01C570C8.1A876C60 Subject: Com 1 Hi,. any one to know, how to send ascci file to com1 port from php ?? thx, nandar $ascii_file_data='This is my test data'; // Your temp directory : Acquire filename handle $tmpdir=C:\temp\; $filename=tempnam($tmpdir,file_prefix_); // Write data to file $fp=fopen($filename); fwrite($fp, $ascii_file_data); fclose($fp); // Send file contents to COM1 $command=type .$filename. com1; exec($command, $result); // Remove temp file fclose($filename); unlink($filename); See also Output redirection in DOS : http://zone.ni.com/devzone/conceptd.nsf/webmain/822FA8FC01C3C0C086256A7100546D8E Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Creating an Associative Array
At 21:04 08/06/2005 +, you wrote: To: php-db@lists.php.net MIME-Version: 1.0 Message-ID: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Date: Wed, 8 Jun 2005 16:04:33 -0500 Content-Type: multipart/alternative; boundary==_alternative 0073C60C8625701A_= Subject: Creating an Associative Array This question will probably be off-topic for some, but it does relate to a database application so please bear with me. I'm pulling date-times out of a MySQL db: mysql select date_format(`date_time`, '%Y.%m.%d %H:%i:%s') as rightNow from location1 order by date_time desc limit 1; ++ | rightNow | ++ | 2005.06.08 14:24:11 | ++ This code snippet is producing an array of arrays rather than a simple associative array: ?php $now = time(); //$dateArray = array(); (output is the same with or without this line) for($counter = 0; $counter = 3600; $counter++) { $dateArray[] = array(date(Y.m.d H:i:s, $now - $counter) = (int)ceil(980 - ($counter * .2))); } Actually you meant to pass the $dateArray an index - as you didn't, PHP helpfully created sequential numeric indices. Try this instead, providing the date index string yourself : $dateArray[ date(Y.m.d H:i:s, $now - $counter) ] = array( (int)ceil(980 - ($counter * .2))); Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: How many rows for 100 questions?
At 04:51 29/05/2005 +, you wrote: Message-ID: [EMAIL PROTECTED] To: php-db@lists.php.net From: ...helmut [EMAIL PROTECTED] Date: Sat, 28 May 2005 12:19:02 -0500 Subject: How many rows for 100 questions? I have a questionary that has 100 questions, Should I create 2 tables 1 with the user information and the second one with the 100 questions? What is your recommendation? My recommendation is to save yourself a lot of time, and grab Moodle http://moodle.org/ with the Quiz Module. That should prevent you having to 'reinvent the wheel' and it will cover many things about quiz-question-answer which you hadn't thought of, including reporting, alternate quiz styles through an easy admin page and more. Cheers - Neil Links to tutorials or information are welcome. TIA CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: how do i fetch some text
At 04:51 29/05/2005 +, you wrote: Message-ID: [EMAIL PROTECTED] To: php-db@lists.php.net Date: Sun, 29 May 2005 09:54:50 +0530 From: chintan [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: how do i fetch some text how do i fetch some text from a webpage for some generated field? You would have to retrieve the whole web page. Look up fopen(url) in the PHP manual, and then the related fgets() and fclose() functions. Using these you can read the HTML of the web page into a string or array, and use regular expressions such as preg_match() to extract the string of numbers after the string score which is inside your generated field. like i want to fetch a score line from a sport site in which the line says score? actually i want to fetch an IP from my ISP's page which displays it with my user name. Actually ? You mean this was a bogus example and you are wasting our time ? Why not say what you *actually* want. If you want to retrive your IP addresss, then write code to do that. But it's a waste of time to go to your ISPs web page, to extract that, when you can do it as easily yourself. This code returns your current IP address : ?php function getUserIPAddr() { global $_SERVER; $privip=false; $remote=$_SERVER[REMOTE_ADDR]; $comes_from=array(HTTP_VIA, HTTP_X_COMING_FROM, HTTP_X_FORWARDED_FOR, HTTP_X_FORWARDED,HTTP_COMING_FROM, HTTP_FORWARDED_FOR, HTTP_FORWARDED); // By preference, replace the remote IP with the proxied-for IP foreach ($comes_from as $value) { if (ereg(^([0-9]{1,3}\.){3,3}[0-9]{1,3},$_SERVER[$value],$remote_temp)) { $remote=$remote_temp[0]; // Fish out IP match if ereg returns a value } } // Check range against private IP addresses if (ereg(^192\.168\.[0-9]{1,3}\.[0-9]{1,3},$remote,$remote_temp)) { $privip=true; $remote=$_SERVER[REMOTE_ADDR]; } if (ereg(^172\.([0-9]{1,3}\.){2}[0-9]{1,3},$remote,$remote_temp)) { // Extra check on range if ($remote_temp[1]=16 $remote_temp[2]32 ) { // is in class B private address range $privip=true; $remote=$_SERVER[REMOTE_ADDR]; } } if (ereg(^10\.([0-9]{1,3}\.){2}[0-9]{1,3},$remote,$remote_temp)) { $privip=true; $remote=$_SERVER[REMOTE_ADDR]; } return $remote; } // End function getUserIPAddr() ? Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: Subject: Form information
Message-Id: [EMAIL PROTECTED] From: Chris Payne [EMAIL PROTECTED] To: php-db@lists.php.net Date: Wed, 25 May 2005 00:53:59 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000__01C560C4.3CC5A710 Subject: Form information Hi there everyone, I'm pulling data from my MySQL DB with PHP 4.2, and I have a quantity box for multiple items, what I'm not sure of is this: BTW, I use quantity[] as the name in the form, so it's an array, I just need to know how to add an extra item called productid to it so I can carry that info over too :-) Just set each text field to have a name and ID like this : assume your product IDs are numbered 21-28 (to avoid confusion) for ($product_id=21; $product_id28; $product_id++) { print('input type=text name=quantity[?=$product_id?] name=quantity_?=$product_id? value= /'); } So each element of the POST array called quantity now has a numeric index, rather than the default array index of 0,1,2,3,4 which you'd get back from the browser, you now get back an array like this (assuming quantities of 10506, 45000, 550, 1000 and so on) quantity [21]=10506, [22]=45000, [23]=550, [24]=1000, .. And when you get that quantity as an array from your POST variables, just loop through it : foreach ($_POST[quantity] as $product_id=$amount) { print(Product ID : .$product_id. Amount : .$amount); } Do whatever you intend to do with the database, instead of the print statement Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE : Subject: Letters loop
for ($i=1; $i=26; $i++) { print(chr(64+$i)); } chr() prints the ASCII character corresponding to that number. Upper case A-Z starts at 64 : http://www.lookuptables.com Cheers - Neil Date: Wed, 25 May 2005 20:37:47 -0700 From: MIGUEL ANTONIO GUIRAO AGUILAR [EMAIL PROTECTED] To: php-db@lists.php.net Message-id: [EMAIL PROTECTED] MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT Subject: Letters loop Hi!! I wanna a do a for loop with letters, Is this possible? for ($i = 'A'; $i = 'Z'; $i++){ // code } -- MIGUEL GUIRAO AGUILERA Logistica R8 - Telcel Tel: (999) 960.7994 Cel: 9931-6 CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re : How to I get to a next entry
At 22:28 19/05/2005 +, you wrote: Message-Id: [EMAIL PROTECTED] From: John R. Sims, Jr. [EMAIL PROTECTED] To: php-db@lists.php.net, php_mysql@yahoogroups.com Date: Thu, 19 May 2005 12:20:24 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_0015_01C55C6D.22CC6B60 Subject: More problems with a script. How to I get to a next entry It allows the case manager to select the client and then it shows them the case note. Only problem is that it only shows them one case note, and most of the clients will have several case notes that have been entered over the period of the program. Yeah, you just need to loop your fetch_array request and build the HTML for the notes from that, so : $result = mysql_query($result); if ($result mysql_num_rows($result)0) { $row = mysql_fetch_array($result); } Becomes if ($result mysql_num_rows($result)0) { $result = mysql_query($result); $rownumber=0; $note_fields=''; $resultset=array(); while ($row = mysql_fetch_array($result)) { if ($rownumber==0) { // Copy the first result, it contains the summary data $resultset=$row; } // End if // And for all rows, concat the notes field as a list item : $note_fields .= li . $row['note'] . /li\r\n\t; // Now we increment the row number to keep track $rownumber++; } // End while } // End if !-- HTML modified to use lists for this sort of information -- tr tdSubject:/td td?=$resultset['subject']?/td /tr . . . . other HTML rows here using $resultset . . . tr tdNotes: /td td ol ?=$note_fields? /ol /td /tr Then just use CSS to style the ol list items however you like - bulletted, indented, surrounded by a nice border with a background margin / padding and so on. Although that's not the most efficient way to do it, because you're looping the entire recordset just to grab the note from each record - the other attributes of the client record are also sent from the SQL server to the PHP client. If you do that over a network then you've added a few % to the needed bandwidth. Say, if you had 1 notes per client, it might be something to worry about - though with 1 notes per client you'd also have *other* things to worry about ;-) Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Fwd: Subject: URL question
Easy enough, using Mod Rewrite on apache (you didn't say which server you use, so I'm afraid you'll have to upgrade if using IIS) : Create a file in a text editor. Save it as .htaccess (dot-htaccess as the filename) in the http://www.blablah.com/examples/ directory - not in the root folder or you'll route every page request on the site through your PHP script. If you're using [eg notepad], make sure it doesn't save it as .htaccess.txt ! The .htaccess file you create contains the following lines : RewriteEngine on RewriteBase /examples RewriteRule (.* ) /examples/exampleprocessor.php?mls=$1 [L] That short regex will take any character string and append it to the MLS query string, which you then process as normal in your PHP script (i.e. as if the user typed in a URL of http://www.blablah.com/examples/exampleprocessor.php?mls=blahblahblah Make sure your script works fully with the checked query string before you do this ;-) Cheers - Neil Message-Id: [EMAIL PROTECTED] From: Chris Payne [EMAIL PROTECTED] To: php-db@lists.php.net Date: Fri, 29 Apr 2005 23:00:27 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000__01C54D0F.3C23BB30 Subject: URL question Hi there everyone, My client needs to be able to have their url www.blahblah.com http://www.blahblah.com/ pickup the product number but I can't do it the way I'd want (which would be www.blahblah.com/?mls=examplenumber, instead they said it MUST be www.blahblah.com/examplenumber - how can I grab the number AFTER the .com in PHP so that I can process it with MySQL without it being assigned a variable name, just the number? Chris CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php3 and oracle9i client libraries doesn't compile..
hi Chris, Your sugesstion worked at least until the next flag lcore4, I changed the makefile to lcore9 then the next flag failed lnlsrtl13 could not be found. I looked in the oracel lib directory but found nothing resembling that name. any ideas? Here is a list of that directory. By the way its oracel 9.2.0.4 installed on redhat enterprise server. Thanks for your help. neil activation.jarlibgslssf9.a libodmd9.so libvpxoafnd.so AgentEmdDiscover.jar libgx9.a libolapapi9.solibvpxos.so classgen.jar libheteroxa9_g.so liboraawt.so libvsn9.a clntsh.maplibheteroxa9.soliboraioser.so libVxxxStackTrace.so clntst_1.lis libirc.a liboraolap9.a libwtc9.a clntst_2.lis libisqlplus.a libordim9.a libwtc9.so clntst.lislibjmisc.solibordim9.so libwtcserver9.a facility.lis libjox9.so libordimt9.a libwwg9.a http_client.jar libldapclnt9.a libordsdo9.a libwwjcache.so jdev-rt.zip libldapjclnt9.alibOsUtils.so libxdb.so jsse.jar libldapjclnt9.so libowm2.solibxml9.a lclasses11.ziplibmapdummy.so libplc9.a libxmlg9.a lclasses12.ziplibmgwagent.so libplp9.a libxsd9.a ldflags libmm.alibpls9.a libzx9.a ldflagsO libn9.alibpsa9.a mail.jar libagent9.a libnbeq9.a libqsmashr.so naect.o libagtsh.so libncrypt9.a libserver9.a naedhs.o libagtsh.so.1.0 libnhost9.alibsgsl_s9.a naeet.o libamd.so libnid.a libskgxn9.so nautab.o libclient9.a libnjni9.solibskgxns.so nigcon.o libclntsh.so libnjssl9.so libskgxp9.a nigtab.o libclntsh.so.9.0 libnk59.so libskgxp9.so nnfgt.o libclntst9.a libnk59.so0libskgxpd.a ntcontab.o libcommon9.a libnl9.a libskgxpd.so oraclexsql.jar libcore9.alibnldap9.alibskgxpu.a osntabst.o libcorejava.solibnls9.a libskgxpu.so s0main.o libcprts.so libnoname9.a libslax9.ascorept.o libcprts.so.3 libnrad9.solibsnls9.aservlet.jar libctx9.a libnrad9.so0 libsql9.a sscoreed.o libctxc9.alibnro9.a libsqlplus.a stubs libctxs9.alibnsgr9.a libtrace9.a sysliblist libctxx9.so libnsslb9.alibtracefe9.a transx.zip libcxa.so libntcp9.a libtracepls9.avbjapp.jar libcxa.so.3 libntcps9.alibtracepls9.so vbjorb.jar libdm9.a libntns9.a libtracept9.a vbjtools.jar libgeneric9.a libnus9.a libunls9.axmlcomp2.jar libgslavl9.a libnzjs9.a libunwind.so xmlcomp.jar libgslber_s9.alibocci9.a libunwind.so.3xmlmesg.jar libgsldb9.a libocci.so libVdbJdbcExt.so xmlparserv2.jar libgslec9.a libocci.so.9.0 libvdc.so xmlplsql.jar libgslmt9.a libocijdbc9_g.so libvdg.so xschema.jar libgslr9.alibocijdbc9.so libvppdc.so xsqlserializers.jar libgslsg9.a libocijdbcst9.alibvpxdba.so xsu111.jar libgslssb9.a libodm9.so libvpxeap.so xsu12.jar gt;From: Christopher Jones lt;[EMAIL PROTECTED]gt; gt;To: neil smith lt;[EMAIL PROTECTED]gt; gt;CC: php-db@lists.php.net gt;Subject: Re: [PHP-DB] php3 and oracle9i client libraries doesn't compile.. gt;Date: Fri, 29 Apr 2005 16:49:27 +1000 gt; gt;neil smith wrote: gt;gt; gt;gt;Hi Martin, gt;gt; gt;gt;It's a legacy application that is huge and sprawling and teh gt;gt;programmers who developed it left along time ago , apparently. It gt;gt;would probably be easier to write a whole new application then gt;gt;convert it to php 4 or 5 and thankfully thats not what I've been gt;gt;asked to do. gt;gt; gt;gt;psa - thanks for that!! gt;gt;would that be part of an oracle library? gt; gt;I can see Oracle 9.2 has a libpsa8.so and libpsa9.so gt; gt;Does your link work if the Makefile is changed to -lpsa9 or -lpsa8? gt; gt;Chris gt; gt;gt; gt;gt; gt;gt;thanks, gt;gt; gt;gt;neil gt;gt; gt;gt;amp;gt;From: Martin Norland amp;lt;[EMAIL PROTECTED]amp;gt; gt;gt;amp;gt;Reply-To: php-db@lists.php.net gt;gt;amp;gt;To: php-db@lists.php.net gt;gt;amp;gt;Subject: Re: [PHP-DB] php3 and oracle9i client libraries gt;gt;doesn't compile.. gt;gt;amp;gt;Date: Thu, 28 Apr 2005 09:31:33 -0500 gt;gt;amp;gt; gt;gt;amp;gt;neil smith wrote: gt;gt;amp;gt;amp;gt;I'm using this command to configure php3 gt;gt;amp;gt;amp;gt;./configure --with-apxs=/usr/local/apache/bin/apxs gt;gt;amp;gt;amp;gt;--with-config-file-path=/usr/local/lib --enable-versioning gt;gt;amp;gt;amp;gt;--enable-track-vars gt;gt;--with-oci8=/u01/app/oracle/product
[PHP-DB] php3 and oracle9i client libraries doesn't compile
hello people, I'm using this command to configure php3 ./configure --with-apxs=/usr/local/apache/bin/apxs --with-config-file-path=/usr/local/lib --enable-versioning --enable-track-vars --with-oci8=/u01/app/oracle/product/9.2.0.4 --with-oracle=/u01/app/oracle/product/9.2.0.4 --enable-sigchild that works ok. But when I type make it eventually fails with this error: gcc -shared -o libphp3.so mod_php3.o libmodphp3-so.a -L/usr/local/lib -L/u01/app/oracle/product/9.2.0.4/lib -lclntsh -lpsa -lcore4 -lnlsrtl3 -lclntsh -ldl -lm -lpthread -lnsl -lirc -lgdbm -lpam -lm -ldl -lcrypt -lresolv -Wl,--version-script=/usr/php/php-3.0.18/php.map -Wl,-rpath /u01/app/oracle/product/9.2.0.4/lib /usr/bin/ld: cannot find -lpsa collect2: ld returned 1 exit status apxs:Break: Command failed with rc=1 make: *** [libphp3.so] Error 1 I don't understand enough about this compilation process to know what lpsa is and why it can't find it. Does anybody know how I can get php3 to work with oracle 9i client libraries? thanks in advance to anyone who can help. neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php3 and oracle9i client libraries doesn't compile..
Hi Martin, It's a legacy application that is huge and sprawling and teh programmers who developed it left along time ago , apparently. It would probably be easier to write a whole new application then convert it to php 4 or 5 and thankfully thats not what I've been asked to do. psa - thanks for that!! would that be part of an oracle library? thanks, neil gt;From: Martin Norland lt;[EMAIL PROTECTED]gt; gt;Reply-To: php-db@lists.php.net gt;To: php-db@lists.php.net gt;Subject: Re: [PHP-DB] php3 and oracle9i client libraries doesn't compile.. gt;Date: Thu, 28 Apr 2005 09:31:33 -0500 gt; gt;neil smith wrote: gt;gt;I'm using this command to configure php3 gt;gt;./configure --with-apxs=/usr/local/apache/bin/apxs gt;gt;--with-config-file-path=/usr/local/lib --enable-versioning gt;gt;--enable-track-vars --with-oci8=/u01/app/oracle/product/9.2.0.4 gt;gt;--with-oracle=/u01/app/oracle/product/9.2.0.4 --enable-sigchild gt;gt; gt;gt;that works ok. But when I type make it eventually fails with this gt;gt;error: gt;gt; gt;gt;gcc -shared -o libphp3.so mod_php3.o libmodphp3-so.a gt;gt;-L/usr/local/lib -L/u01/app/oracle/product/9.2.0.4/lib -lclntsh gt;gt;-lpsa -lcore4 -lnlsrtl3 -lclntsh -ldl -lm -lpthread -lnsl -lirc gt;gt;-lgdbm -lpam -lm -ldl -lcrypt -lresolv gt;gt;-Wl,--version-script=/usr/php/php-3.0.18/php.map -Wl,-rpath gt;gt;/u01/app/oracle/product/9.2.0.4/lib gt;gt;/usr/bin/ld: cannot find -lpsa gt;gt;collect2: ld returned 1 exit status gt;gt;apxs:Break: Command failed with rc=1 gt;gt;make: *** [libphp3.so] Error 1 gt;gt; gt;gt; gt;gt; gt;gt;I don't understand enough about this compilation process to know gt;gt;what lpsa is and why it can't find it. Does anybody know how I can gt;gt;get php3 to work with oracle 9i client libraries? gt;gt;thanks in advance to anyone who can help. gt; gt;-lpsa means it's trying to link a library named 'psa' - off the top gt;of my head I can't say what that is, and a brief googling is proving gt;less than useful. gt; gt;--- end section where I am 'useful', on to questioning rant --- gt; gt;Can I ask why you're using such an old version of php? If it's for gt;a legacy application I can understand, but it seems like you're gt;giving up a LOT to stay on php3. In any case - looking at the gt;release dates for the two: gt; gt;http://www.oracle.com/technology/software/products/oracle9i/index.html gt; Oracle9i Database Release 2 Enterprise/Standard Edition for Linux gt;New (26-Mar-04) gt; gt;http://www.php.net/releases.php gt; 3.0.x (latest) gt; # Released: 20 Oct 2000 gt; gt;I'd say you'd be better off trying your legacy app under php4 and gt;moving to that. If it's simply a register globals issue, then gt;you're no safer in php3 than you would be in php4 with it turned on, gt;so that's not a reason to stay back. gt; gt;good luck determining what psa is, I have to run to a meeting but I gt;may try looking again later. gt; gt;cheers, gt;-- gt;- Martin Norland, Sys Admin / Database / Web Developer, gt;International Outreach x3257 gt;The opinion(s) contained within this email do not necessarily gt;represent those of St. Jude Children's Research Hospital. gt; gt;-- gt;PHP Database Mailing List (http://www.php.net/) gt;To unsubscribe, visit: http://www.php.net/unsub.php gt; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subject: Help wanted, Writing to the LPT Port.
Actually, php-db is a list for (wait for it!) PHP used with DB (databases) so you've got the wrong list, you meant to send it to PHP-general. Anyway to answer your question, you can send an 8 bit printer port a series of ASCII characters (0-255) which correspond to your values, so say chr(35) == 00100011 and chr(140) = 10011000. You can also use hex codes, which is easier to see the bit values. To do this, add 0x before the hex digits, for example 0xFF = , 0x3A = 00111010. Read a tutorial on hexadecimal notation if you are unsure how hex digits map to binary and decimal. See http://www.phpdig.net/ref/rn58re1156.html, http://en.wikipedia.org/wiki/Hexadecimal So to do this in Linux say you would use the system's lpr queue to send characters to the printer port. And in windows you'd send it to the lpt1 port or other printer port (select one of the $command depending on OS) : ?php $character=chr(0xBB); // Equivalent to binary 10111011 $command=echo .$character. | lpr -Pepson1; // Send command to Unix OS lpr command $command=type .$character. lpt1 ; // or ... Send command to windows LPT1 port exec($command); // Send the command string directly to the operating system ? HTH Cheers - Neil At 21:44 21/04/2005 +, you wrote: Message-ID: [EMAIL PROTECTED] To: php-db@lists.php.net Reply-To: JB [EMAIL PROTECTED] From: JB [EMAIL PROTECTED] Date: Thu, 21 Apr 2005 09:38:50 +0200 Subject: Help wanted, Writing to the LPT Port. Hello, I want to write a switch board in PHP. So that it is possible to write data 1 or 0 to the D0 - D7. So that It is possible to switch a relais wich I connected to the LPT port of my webserver. I could not find any examples or documentation on this theme. Could somebody help me with this problem? P.S. I am sure that it is possible because it is also possible to print with PHP. So why should we not be able to write a specific value to the LPT port. I saw 1 example with a serial port. So. Kind regards, J Bosch CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php4 can't find tnsnames.ora oracle9 but can connectif description
Hello chris, I've modified the variables in the apache user environment (in my case root at the moment) but that didn't amke any difference to php not being able to find tnsnames.ora. So the work around I've used is to use ORACLE_SID=desciption(...etc. SID = ))); ie the full description of the database alias within tnsnames.ora However this is not good, so if you have any other idea please let me know. I'm flumuxed. thanks, neil gt;From: Christopher Jones lt;[EMAIL PROTECTED]gt; gt;To: neil smith lt;[EMAIL PROTECTED]gt; gt;CC: php-db@lists.php.net, [EMAIL PROTECTED] gt;Subject: Re: [PHP-DB] php4 can't find tnsnames.ora oracle9 but can connectif description hardcoded gt;Date: Wed, 20 Apr 2005 09:25:12 +1000 gt; gt;Make sure the environment variables are set in the shell that starts gt;Apache. See gt;http://www.oracle.com/technology/tech/opensource/php/php_troubleshooting_faq.html#envvars gt; gt;Try changing your OCILogon command to gt; gt; $odbc = OCILogon(quot;yourusernamequot;,quot;yourpasswordquot;,$db); gt; gt;i.e. remove quot;/testquot; from the username parameter. gt; gt;Chris gt; gt;neil smith wrote: gt;gt;Hello, gt;gt; gt;gt;I have test script that can connect to the oracle database. It gt;gt;doesn't gt;gt;require tnsnames.ora because I define the database alias in the gt;gt;script gt;gt;itself. It looks like this: gt;gt; gt;gt; gt;gt; gt;gt;echo quot;TWO_TASK=quot;.getenv(quot;TWO_TASKquot;).quot;lt;brgt;quot;; gt;gt;echo quot;LD_LIBRARY_PATH=quot;.getenv(quot;LD_LIBRARY_PATHquot;).quot;lt;brgt;quot;; gt;gt;echo quot;ORACLE_BASE=quot;.getenv(quot;ORACLE_BASEquot;).quot;lt;BRgt;quot;; gt;gt;echo quot;ORACLE_HOME=quot;.getenv(quot;ORACLE_HOMEquot;).quot;lt;BRgt;quot;; gt;gt;echo quot;TNS_ADMIN=quot;.getenv(quot;TNS_ADMINquot;).quot;lt;BRgt;quot;; gt;gt;echo quot;NLS_LANG=quot;.getenv(quot;NLS_LANGquot;).quot;.lt;brgt;quot;; gt;gt; gt;gt;# this allows a successful connection gt;gt; gt;gt;$db = quot; (DESCRIPTION = gt;gt; (ADDRESS = (PROTOCOL = TCP) (HOST =testdb.boo.com)(PORT = 1521)) gt;gt; (CONNECT_DATA= (SID = testdb)) gt;gt; )quot;; gt;gt; gt;gt;// these don't work.. php can't seem to find tnsnames.ora gt;gt;# $db = quot;testdb.boo.comquot;; gt;gt;//$db = quot;testdb.boo.comquot;; gt;gt;# $db = quot;testdbquot;; gt;gt;//echo $testdb; gt;gt; gt;gt; gt;gt;$odbc = OCILogon(quot;test/testquot;,quot;testquot;,$db); gt;gt; gt;gt;if ($odbc == false){ gt;gt; $msg = OCIError($odbc).quot;lt;BRgt;quot;; gt;gt;} else { gt;gt;echo quot;success!!!lt;brgt;quot;; gt;gt;} gt;gt; gt;gt; gt;gt; gt;gt;My problem is is that when I try to use the database alias in gt;gt;tnsnames.ora I gt;gt;get an ora-12154 error. I get this despite the facts that gt;gt;oracle_home, gt;gt;tns_admin and everything else is defined in the script and the gt;gt;tnsnames.ora gt;gt;contains exactly the same alias as I use in the hardcoded version gt;gt;(see the gt;gt;uncommented $db above). gt;gt;sqlnet.ora contains a value name_domain that apparently is appended gt;gt;to the gt;gt;alias in tnsnames.ora but accounting for this still doesn't let me gt;gt;connect gt;gt;to oracle . It's as if tnsnames.ora cannot be found. I've changed gt;gt;the gt;gt;permissions to 777 for the whole directory structure and I can gt;gt;successfully gt;gt;use tnsnames.ora with sqlplus or tnsping. gt;gt; gt;gt;Does anybody have any ideas what is going wrong? Is this a bug? gt;gt; gt;gt;thanks, gt;gt; gt;gt;neil gt;gt; gt; gt; gt;-- gt;Christopher Jones, Oracle Corporation, Australia. gt;Email: [EMAIL PROTECTED] Tel: +61 3 8616 3622 gt; gt;-- gt;PHP Database Mailing List (http://www.php.net/) gt;To unsubscribe, visit: http://www.php.net/unsub.php gt; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] php4 can't find tnsnames.ora oracle9 but can connect if description hardcoded
Hello, I have test script that can connect to the oracle database. It doesn't require tnsnames.ora because I define the database alias in the script itself. It looks like this: echo TWO_TASK=.getenv(TWO_TASK).br; echo LD_LIBRARY_PATH=.getenv(LD_LIBRARY_PATH).br; echo ORACLE_BASE=.getenv(ORACLE_BASE).BR; echo ORACLE_HOME=.getenv(ORACLE_HOME).BR; echo TNS_ADMIN=.getenv(TNS_ADMIN).BR; echo NLS_LANG=.getenv(NLS_LANG)..br; # this allows a successful connection $db = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST =testdb.boo.com)(PORT = 1521)) (CONNECT_DATA= (SID = testdb)) ); // these don't work.. php can't seem to find tnsnames.ora # $db = testdb.boo.com; //$db = testdb.boo.com; # $db = testdb; //echo $testdb; $odbc = OCILogon(test/test,test,$db); if ($odbc == false){ $msg = OCIError($odbc).BR; } else { echo success!!!br; } My problem is is that when I try to use the database alias in tnsnames.ora I get an ora-12154 error. I get this despite the facts that oracle_home, tns_admin and everything else is defined in the script and the tnsnames.ora contains exactly the same alias as I use in the hardcoded version (see the uncommented $db above). sqlnet.ora contains a value name_domain that apparently is appended to the alias in tnsnames.ora but accounting for this still doesn't let me connect to oracle . It's as if tnsnames.ora cannot be found. I've changed the permissions to 777 for the whole directory structure and I can successfully use tnsnames.ora with sqlplus or tnsping. Does anybody have any ideas what is going wrong? Is this a bug? thanks, neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subject: Web Printing
At 10:27 31/03/2005 +, you wrote: Message-ID: [EMAIL PROTECTED] From: Ng Hwee Hwee [EMAIL PROTECTED] To: PHP DB List php-db@lists.php.net Date: Thu, 31 Mar 2005 18:24:41 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_00CD_01C5361E.E80297D0 Subject: Web Printing Hi all, i know i can print out a header and footer using HTML thead and tfoot. However, the tfoot doesn't always stay at the bottom of the page. For example, if the the last page only have 2 records, the tfoot will appear pretty high on the page. i am wondering how can i use PHP to fix the position of my header and footer PHP cannot help you here, it's the wrong tool for the job (it's a hammer not a screwdriver so to speak). You need to (*must* !!!) use CSS to position and specify the height and dimensions of the thead and tfoot thus : thead { display: table-header-group; position: absolute; top: 0; height: 36pt; width: 100%; } tfoot { display: table-footer-group; position: absolute; bottom: 0; height: 36pt; width: 100%; } These should apply *reasonably* well to position your thead and tfoot row groups in the same place on each printed page (adjust as desired). You *cannot* use PHP to guess the height of the page areas reliably unless you are using a fixed pitch font such as a dot matrix line printer. And even then it's somewhat guesswork (I've done both). But if we're assuming that you're users have laser printers you *must* use CSS to do the print layout, PHP line height guessing will not work for you. Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Forms...
At 07:52 10/03/2005 +, you wrote: Message-ID: [EMAIL PROTECTED] Date: Wed, 09 Mar 2005 20:37:36 +0100 From: Jochem Maas [EMAIL PROTECTED] if your into XHTML: input name=right_eye type=checkbox value=1 checked=checked value=1 / Actually that's invalid XHTML (it won't validate) due to a typo I guess. Each XML (aka XHTML) DOM element can only have a ~single~ attribute with a particular name. The typo above has two copies if the 'value' attribute which would prevent the XHTML being valid XML. HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re:data grabbing and mathematics!
At 09:48 08/03/2005 +, you wrote: Message-ID: [EMAIL PROTECTED] Date: Mon, 07 Mar 2005 13:01:45 -0600 From: Martin Norland [EMAIL PROTECTED] Reply-To: php-db@lists.php.net MIME-Version: 1.0 To: php-db@lists.php.net Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: Re: [PHP-DB] Re:data grabbing and mathematics! 1) use a single table, with 23 columns instead of 2. Then sum the columns in your query. Actually I disagree strongly unless your intention is to be able to read the rows yourself ! As the stated intention is to add up scores (doing the maths) in fact much more efficiently you want to have just 3 columns in a single table, tblBlah Using multiple tables, or even multiple columns is deeply inefficient for this. Using multiple columns is the most space/time efficient - assuming players aren't only playing a few rounds. Hi Martin - again I'd disagree with the space constraint : In the case of 3 columns I suggested, you keep *only* the data you require. If using 23 columns, you've now got to maintain 23 values (even if they have never been used). And worse, you have to make those values NULL by default - another 1 bit per item stored, and slightly slower query, which affects the time constraint if you grow to a very large system. This is because as they're player scores - they have to be NULL initially to indicate the user never played the round. If you set them to zero by default, you have no idea if the user played the round, or just scored zero. Using 3 columns as above, if you get a count(*) of zero for a query filtering by user and round, then your player has never played it. If you get a sum(score) of zero, he's played but scored zero. It is, however, not the most *flexible*, as you're saying. You need to modify the number of columns if things ever change, though you aren't limited to the number of players. I think both scenarios though, would require the number of columns to change if anything*significant* changed. But with 3 column solution proposed, to add another round you just insert the data ;-) With 23 columns you have to modify the database schema to add that extra column. Both solutions probably require you to modify your php code a tiny bit for that 'future' mod. Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subject: mysql_real_escape_string for HTML p???
You actually need (it's in the manual) nl2br for this. It converts newline (nl) to (2) break (br) so this text (\n indicates a newline character or 'enter' as you've called it) This\n is\n a\n test\n Would become Thisbr /\n isbr /\n abr /\n testbr /\n Make sure you strip those br / if you intend to put the content back in to a database though, or you'll double up the line count. Cheers - Neil At 04:47 06/03/2005 +, you wrote: Message-ID: [EMAIL PROTECTED] From: Ron Piggott [EMAIL PROTECTED] To: PHP DB php-db@lists.php.net Date: Sat, 5 Mar 2005 23:21:14 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: mysql_real_escape_string for HTML p??? I know of the $variable = mysql_real_escape_string($variable); It works just nicely to allow some of the wild cards to be saved to a mySQL table. Is there something like this for HTML that when you retrieve from a variable in the table an ENTER would be converted to P I am mainly talking about when the variable is defined as longtext and you are expecting the user to write a few sentences or where they may s/he may press ENTER to start a new line. Ron CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] timestamp
Balwant - according to http://php.planetmirror.com/manual/en/ref.datetime.php shouldn't you be using http://php.planetmirror.com/manual/en/ref.datetime.phpfunction.strtotime.phpstrtotime rather than strftime for this ? strftime needs input of a timestamp a large Int32 Unix Timestamp), where strtotime can format most textual time formats recognised by the OS. Cheers - Neil From: Balwant Singh [EMAIL PROTECTED] i am facing a problem i have timestamp in string format -- Fri Jan 28 19:53:09 2005 now i want to get the date from it. is it possible? pls. help. on using strftime(%D,$timestamp) it is giving 01/01/70. May pls. help me. Also pls. let me know whether calculation can be done in mysql on this string timestamp. pls. inform. with best wishes balwant CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Subject: mysql - image storing
At 09:48 19/01/2005 +, mel list_php wrote: Ok Neil, I'll try this as well.I thought a readfile would be also forbidden by the htaccess, No, .htaccess affects public viewing of the web root and child folders. PHP operates on the web server's file system (in as far as your host/ISP permits that) so the two areas are complementary. Infact, you could store the files entirely outside of the web root if you have access to such space on the machine - using readfile, as long as you have filesystem permission to traverse to the desired directory, then you can read and pass through the file using PHP. Then you'd not need .htaccess at all, it only comes into play when you're file-hiding beneath the web root. and I agree this would be a good solution.I don't really want to store the picture in the database but the fact is tha I should be able to do it if I want! No, really really , don't do this. Store the path to the image. You then load the path into PHP and use header(Content-type: image/jpeg); readfile($path_from_database); exit; Then .htaccess the *actual directory* you store the images in so it's hidden from browsing. Only allow authorised people access to their own images, using a standard authentication system, and retrieve the image filenames they're allowed from the DB, append that to your protected directory path, and use readfile() to pass through the file data directly to the browser. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: mysql - image storing
No, really really , don't do this. Store the path to the image. You then load the path into PHP and use header(Content-type: image/jpeg); readfile($path_from_database); exit; Then .htaccess the *actual directory* you store the images in so it's hidden from browsing. Only allow authorised people access to their own images, using a standard authentication system, and retrieve the image filenames they're allowed from the DB, append that to your protected directory path, and use readfile() to pass through the file data directly to the browser. Really, don't store them in the database ;-) Cheers - Neil At 18:59 18/01/2005 +, you wrote: Message-ID: [EMAIL PROTECTED] From: mel list_php [EMAIL PROTECTED] To: php-db@lists.php.net Date: Tue, 18 Jan 2005 11:30:12 + Mime-Version: 1.0 Content-Type: text/plain; format=flowed Subject: mysql - image storing Hi list, I try to store/retrieve pictures into MySQL. I know that a lot of people will say this is not a good practice, so here are briefly my reasons: -I want to protect that pictures (restricted access) -I don't want to use htaccess as I want my users to be able to modify their password whenever they want, and I don't want to modify dynamically an htaccess file. -I could store them on the filesystem (my actual solution), but I have only few pictures, so I would like to give the MySQL option a trial. CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: How to process a query form with CHECKBOX RADIO not checkbox !
At 01:23 28/12/2004 +, you wrote: Date: Mon, 27 Dec 2004 17:22:49 -0800 (PST) From: S Kumar [EMAIL PROTECTED] To: php-db@lists.php.net, php-general@lists.php.net MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Subject: How to process a query form with CHECKBOX Please help DIVAge group/DIV DIVYounginput type=checkbox name= value=/DIV DIVMiddleinput type=checkbox name= value=/DIV DIVOldinput type=checkbox name= value=/DIV Problem: I want to capture the user options and create an SQL statement: You need to make your form action=post You also need to use a radio button because a patient may not be young *and* old for example. Radio buttons offer an 'either-or' selection, where a checkbox offers an 'and' selection. You also need to give the radio buttons a name (the same for each one in a group of options) eg input type=radio name=age id=age1 value=young / Young input type=radio name=age id=age2 value=middle / Middle input type=radio name=age id=age3 value=old / Old When you submit the form, PHP will have a variable in $_POST array which contains the value of the selection. It will be called the same as the name attribute in your input (radio) button : $allowed_ages = array(young, middle, old); $age_group = $_POST[age]; // We should check that the value is of the types we allow into the database // this will prevent form spoofing because we're checking for an exact match if (in_array($age_group, $allowed_ages)) { // At this point we should make a connection to the database. // Well make the resource called $link and it will be used below $link=mysql_connect(my_server, my_username, my_password); // There may be multiple *databases* on the server, each with lots of *tables* mysql_select_db(my_database_name, $link); $query=INSERT INTO patient_data (age_group) VALUES ('.$age_group.'); // Execute query mysql_query($query, $link); // Check for success if (mysql_error($link)) { // Something went wrong with the query print(Error in query : .$query); } else { // Print success print(Your record was added); } } else { // We were receiving a spoofed form entry which could break the database print(Value is not acceptable); exit; } Select age_group from patient_data where age_group = young and old ; Just use SELECT * FROM patient_data WHERE age_group IN (young, old) There would be no point in selecting age_group because all you'll get is a list of young-old-old-old-young etc without any useful information. If you wanted to know *how many* young and old (a summary) you'd use GROUP BY eg SELECT age_group, COUNT(age_group) FROM patient_data WHERE age_group IN (young, old) GROUP BY age_group so you'd get == young 15 old 24 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: [PHP-DB] KEYBOARD
It's all client-side Javascript. I'll send you by email (off-list) an example I wrote this week for touchscreen number data entry so you can see how to do it. Then you're on your own ! This is *not* a PHP *or* a DB question, please keep general web-development questions to a more appropriate newsgroup. Evolt.org is a good place to start, or www.alistapart.com Cheers - Neil At 18:21 01/12/2004 +, you wrote: Reply-To: [EMAIL PROTECTED] From: balwantsingh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Wed, 1 Dec 2004 14:39:15 +0530 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: [PHP-DB] KEYBOARD May pls. help me. Can anybody guide me whether virtual keyboard can be made using PHP. Actually i am developing a site for our internal use where we will not give keyboard to user, the data can be entered by help of touchscreen pasted on the monitor. with best wishes balwant CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE : Subject: Excel - merging cells with PHP
Sure, you'd output to the Excel XML spreadsheet format, which allows you to do colspans and such, although with a different syntax to HTML. (I posted this answer to the microsoft.public.xsl list the other day ;-) The documentation is here : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xmlss.asp Some Examples : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffpro01/html/XMLOfficeXPPartI.asp http://www.simonstl.com/articles/officeXML/spreadsheetML.html and http://www.simonstl.com/articles/officeXML/spreadsheetMLsample.html But the easiest way by far is this : Save your XML file in the desired format but choose Save As, then pick XML Spreadsheet as the file type to use (its in the same dropdown as Lotus1-2-3 and CSV files). Open the XML spreadsheet in a text editor and immediately you will see how it is formatted. Now, ignore the HTML, and create an XML file which matches the XML spreadsheet. Really you only need to generate Row and Cell Data tags, the Style tags can be added to the XSLT file. You can define almost any style used in excel such as borders, shading, number format (so no need to pre-convert numbers to currency for example_ Just use standard XSL to convert your XML data output from the DB to either of HTML or Excel spreadsheet (or even CSV for old Excel 97 !). I've done this in production environments and it works very very well indeed. It should take about 2-3 hours the first time, and you'll make a couple of mistakes. Examine the excel error log produced to find out the error (the error log is saved in your temporary internet files). It will be much fasted once you've tried it once. Message-ID: [EMAIL PROTECTED] From: Perry, Matthew (Fire Marshal's Office) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Mon, 29 Nov 2004 08:48:52 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=_=_NextPart_001_01C4D622.8B3E44C4 Subject: Excel - merging cells with PHP When you export a table to excel, is there a way to merge cells and create borders? I want my excel file to look exactly like my html table looks. Matthew Perry CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Javascript Question (Was PHP / Javascript question)
Yes it can be a bit tricky first time. You'll be using numeric indexes (it's the easiest) so probably like this : Iterate through the form's elements array, check if each one is a [checkbox / radio / other] and then check the name for the element against a regex. If it matches then you need to use this element to validate eg script language=javascript1.2 type=text/javascript function validate_form() { valid=true; df=document.forms[myform]; var regex=del\[\d+\]; for (i=0; idf.elements.length) { if (df[i].type==checkbox df[i].name.match(regex)) { if (df[i].checked=true) { valid = false; break; } } } } /script So you're checking to see if each element is a checkbox form type, then if it matches the field name you want to validate against (there may be more than one set of checkboxes with different array names), and finally you're seeing if it's checked. Since there's no point carrying on with the loop if we've failed one, we then use break; to jump out of the loop rather than bash any other form elements. The odd 'escapes' in the regex before the [ and ] are because [ and ] are used in regular expressions to denote character classes eg [0-9*£$] or [a-zA-Z0-9] and so on. The \d+ denotes 'any digits' and is a shorthand way to write [0-9] Cheers - Neil At 09:46 16/11/2004 +, you wrote: Message-Id: [EMAIL PROTECTED] From: Chris Payne [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Mon, 15 Nov 2004 22:03:10 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000__01C4CB5E.E5407E70 Subject: PHP / Javascript question Hi there everyone, I have a form with check boxes and with PHP I use the array feature for the form name. Now I need to check if the tickboxes are ticked and if not return an error, normally with javascript I would use: script language=JavaScript function validate_form ( ) { valid = true; if ( document.removeitems.del.value == ) { alert ( You cannot remove an item if it is not selected. ); valid = false; } return valid; } /script BUT because the tickboxes information is stored in a PHP Array called del[] I am having major problems getting it to work. Does anyone know how to determine with javascript if any boxes are ticked, when the name for the box is an Array such as del[] ??? Im grabbing the data from my MySQL DB without a hitch etc . And I know technically this is more MySQL / PHP list, but it is related and Ive looked online and cant seem to find anything, so thought Id try here as usually you are all very helpful. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Javascript Question (Was PHP / Javascript question)
That if (df[i].checked=true) { should of course read if (df[i].checked==true) { sigh ! Cheers - Neil script language=javascript1.2 type=text/javascript function validate_form() { valid=true; df=document.forms[myform]; var regex=del\[\d+\]; for (i=0; idf.elements.length) { if (df[i].type==checkbox df[i].name.match(regex)) { if (df[i].checked=true) { valid = false; break; } } } } /script -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: MySQL Data Not Returned
My guess (and it will be correct) is that you've got a not visible character on the end of your email address in the database. Such a character could be a space, newline, tab or other similar character. Make sure you use trim($email_address) to create the field to insert into the database ! Check the actual string you get returned from the query on the DB with the query SELECT * FROM mailinglist WHERE email like '$email%' Use strlen($resul[email]); and you'll see that the length of the email address is not the same as the number of characters in the email address if you count them manually. Cheers - Neil At 11:54 13/11/2004 +, you wrote: I search the list like so: $email=[EMAIL PROTECTED]; $query=mysqli_query($cnn,SELECT * FROM mailinglist WHERE email='$email'); When I do: Echo mysqli_num_rows($query); I get 0 back, The address I am searching is in the database, I have triple checked it with phpmyadmin. The only way I can get it to return the record I am looking for is to do this. $email=[EMAIL PROTECTED]; $query=mysqli_query($cnn,SELECT * FROM mailinglist WHERE email like '$email%'); Then this finally returns 1. I have tried many different email addresses; nothing comes back, not even from the SQL query window within phpmyadmin v. 2.6.0-rc2 This has been puzzling me for hours on end. If anyone can provide any help, that would be great -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: php-db Digest 13 Nov 2004 11:54:41 -0000 Issue 2681
Brilliant, nice one. You've just given me the opportunity to run my counter-spam signup script. I wa swaiting for a good opportunity like this, as it's a little difficult to test on myself !You have now been automatically subscribed to 14,500 newsletter distribution lists and opt-out mailings. Congratulations on your new payload, scum. I hope you have fun unsubscribing from them all. Cheers - Neil At 11:54 13/11/2004 +, you wrote: From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: [EMAIL PROTECTED] Mime-Version: 1.0 Content-Type: multipart/related; boundary== Multipart Boundary 1112041232 Date: Fri, 12 Nov 2004 12:32:55 - Reply-To: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Your Business Cards -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re:Subject: Finding the highest number in a column?
Surely some monday morning light headedness Chris ! SELECT MAX(column_name) AS max_number FROM table Make sure the column's indexed if you do it often ! Cheers - Neil At 03:32 28/10/2004 +, you wrote: Message-Id: [EMAIL PROTECTED] From: Chris Payne [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Wed, 27 Oct 2004 23:33:02 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000__01C4BC7D.4D8C22E0 Subject: Finding the highest number in a column? Hi there everyone, I am working with a very complex system where not all numbers are assigned 1, 2, 3 etc so there could be a row with the number 1 and then the next row could be 40 and so on Is there a way with PHP and MySQL to find out what the highest number is in a particular column easily when the numbers are auto-incremented so arent necessarily one after the other? I would really appreciate any help on this as its really bugging me. Chris --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.783 / Virus Database: 529 - Release Date: 10/25/2004 CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Form Processing
Sure you can Ron : Just give each submit button the same name, but a different ID in the html eg submit name=submit id=delete value=Delete / submit name=submit id=update value=Update / submit name=submit id=manage value=Management / When the user presses one of the buttons, look into the $_POST array and check if the value is set, for example ?php if (isset($_POST[submit])) { // Check that the form was submitted using the submit button switch ($_POST[submit])) { case 'Delete' : // Delete actions here break; case 'Udate' : // Update actions here break; case 'Management' : // Management actions here break; default: // Any catch-all actions if the forms submit button was spoofed } // End switch block ? So, the submit value you're checking for should match that on the button. Each block after the 'case' statement is followed by a 'break' statement. If you *don't* do this, the code continues to execute all the other statements in the switch block. Sometimes this is desirable but generally it ain't and can cause a headache if you leave it out. You also have a 'default' statement which happens if none of the 'case's match - for example if somebody is trying to 'hotwire' your form to perform undesirable actions. NB : You need to give them buttons different HTML *ID's* so that any javascript you use can tell the buttons apart in the DOM (otherwise the HTML DOM would be invalid, as you can't have more than one element on your web page with the same ID) HTH Cheers - Neil At 12:52 16/10/2004 +, Ron wrote: Message-ID: [EMAIL PROTECTED] From: Ron Piggott [EMAIL PROTECTED] To: PHP DB [EMAIL PROTECTED] Date: Fri, 15 Oct 2004 23:05:14 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: Form Processing I would like to know if you are able to create a PHP web form that has different SUBMIT buttons --- or if you used the HTML command SELECT NAME=approval_command OPTIONDelete Record OPTIONUpdate And Approve For Listing OPTIONFor Management /SELECT Basically I want the delete record to find the record in the mySQL database --- I added the auto_increment yesterday to help develop the editing module --- I want the Update and Approve For Listing to change a variable value from 0 to 1 and to accept any changes the pre-viewer has made --- for example writing in United States instead of US and For Management to change the variable value to 2. Are any of you able to help me with this question? Sincerely thank you. It would again help me if a copy of your response is sent to my e-mail [EMAIL PROTECTED] CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Print
This is NOT a PHP question, or even a Javascript question - it's basic HTML and CSS. You must read here http://www.dillonsoftware.com/essays/printing.jsp point #3 And here on A ListApart : http://www.alistapart.com/discuss/goingtoprint/6/ But please do not post basic HTML questions to the PHP lists unless you intend to use PHP/Windows native print functions. A little web research will also help. Cheers - Neil At 08:05 09/09/2004 +, you wrote: From: Ng Hwee Hwee [EMAIL PROTECTED] To: PHP DB List [EMAIL PROTECTED] Date: Thu, 9 Sep 2004 16:05:55 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_021B_01C49686.E3359460 Subject: Print Hi, I have a pressing problem with regards to printing. My situation is as follows: 1) I have a form in a table format with the following headers: | No. | Date | Contents | Remarks | 2) the fields under Contents and Remarks are textareas to allow users to type as much as they want. 3) after submitting the form, user will click on a button that will activate window.print() to print the form 4) however, sometimes user types too much and the information spills over to two or more pages. 5) user thus wish to also have the headers of the table repeated on the subsequent pages. So, my problem is, how can I know when the information will be more than a page? is there a way in PHP to calculate the number of lines output and then I can force a page-break? please help me. thank you soo soo much! regards, hwee hwee CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: php Form Validation help
At 11:09 07/09/2004 +, you wrote: From: Vincent Jordan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Mon, 6 Sep 2004 19:20:16 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_0004_01C49446.8C688C60 Subject: php Form Validation help have a form that I would like to validate before it is submitted to the DB. I am having trouble with a couple of sections. I have a select box with a listing of all the states, the first option is Choose One with a value of 0. Would this be the correct way to validate this field? if ($_POST[state]==0) { // Display error message that no state is selected print 'pPlease select your State/p'; } Second, another part of the same form I have fields for phone numbers, once the field is submitted I would like to join the 3 sections of areacode, nxx and prefix. I have done this to join them, is this correct? Almost there : Put quotes around parts of your output that aren't variables (ie quote literal text strings) $phone = $_POST['p-areacode'] . - . $_POST['p-prefix']. - .$_POST['p-suffix']; The next question id for both phone number and area code. If I want to validate the zip code and ensure a minimum of X characters in the field, numbers only how would I do this? if (strlen($zip_str) 5) { print 'pPlease enter your Zip Code (EX. 77662)/p'; } HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] functions via event handlers
Or you could use XMLHTTP on the browser side (supported by IE and Mozilla variants) You'd send a GET request to the page, it would return - often - an XML record set, or you can return text, and indeed you can even query page status codes from the client side. The point is, you no longer need to unload the current page to get information form the server. Useful examples here : http://jibbering.com/2002/4/httprequest.html Cheers - Neil At 11:09 07/09/2004 +, you wrote: Message-ID: [EMAIL PROTECTED] Date: Mon, 6 Sep 2004 19:55:51 -0400 From: Joseph Crawford [EMAIL PROTECTED] Reply-To: Joseph Crawford [EMAIL PROTECTED] To: [EMAIL PROTECTED] Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Subject: Re: [PHP-DB] functions via event handlers the closest you could come is to make javascript functions call php pages basically redirects that would call the php functions. CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re Displaying Text From A Data File
It's actually very simple : Just use SELECT * FROM tbl_name ORDER BY RAND() LIMIT 1 Cheers - Neil At 17:35 04/09/2004 +, you wrote: Message-ID: [EMAIL PROTECTED] From: Ron Piggott [EMAIL PROTECTED] To: PHP DB [EMAIL PROTECTED] Date: Sat, 4 Sep 2004 14:00:56 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: Displaying Text From A Data File I am still new to PHP and I wanted to ask a question. I am wanting to create a PHP script to randomly display one of many greeting messages on my web site. I was going to store them in a text based file. I wanted PHP to randomly select one of them and display it each time the script was loaded. I am still new at PHP and I am not sure where to get started. But I was thinking about this and I am sure this has already been done many times ... I am not sure where to look on the web. Can any of you help me get started? Ron CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: LOAD DATA LOCAL INFILE
I often had trouble getting the syntaxt just right with PHP, it's easy to end up with the wrong number of slashes around the -lines terminated by '- parts of this. When you ran your query, what did mysql_error() say about it ? That would be a line you add directly after the line mysql_query($link,$query) Usually this will tell you immediately if you have an error in your SQL, and echoing the $query string you passed to MySQL is a good idea too, it can be more obvious to diagnose when it's printed on screen. Cheers - Neil At 12:41 16/08/2004 +, you wrote: From: Daniel Kradolfer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Mon, 16 Aug 2004 14:40:49 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Subject: LOAD DATA LOCAL INFILE Hello I'm new to this list. I diden't found anything in the archive and by googleing to solve my problem. We have updated our server to PHP 4.3.8 and MySQL 4.0.2. Some of our clients run a shop software that need 'LOAD DATA LOCAL INFILE' to store its data to MySQL. MySQL itself supports the command. I can run it from mysql-client. That works. I have read about the fix on php.net (http://www.php.net/ChangeLog-4.php / http://bugs.php.net/28632). Until now, i couldn't find a solution to get it work. Is there way to get it work? Regards Dani CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Enabling LDAP support
I have copied the two required .dlls (libeay32 ssleay32) into the system folder. No, you need to copy the dll's to \winnt\system32, not \winnt\system thinking that that might make it work... but it didn't *How* didn't it work ? What did you try ? If it didn't connect, are you letting TCP port 389 in/out of your network ? Try connecting to ils.kencomp.net and get some results, it's usually up. HTH Neil At 19:03 02/08/2004 +, you wrote: From: Philip Thompson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Message-id: [EMAIL PROTECTED] MIME-version: 1.0 Content-type: text/plain; charset=US-ASCII; format=flowed Content-transfer-encoding: 7BIT Subject: Enabling LDAP support Hi again all. Sorry to bug you again. But I thought I would throw this out here again and see if anyone has any input. It would be greatly appreciated. I am running PHP on a Windows 2000 Server, and I need to enable LDAP support. So I have viewed the http://us3.php.net/manual/en/ref.ldap.php page on how to get LDAP running. However, I have run into a snag. I have copied the two required .dlls (libeay32 ssleay32) into the system folder. I also put the configuration option 'ldap.max_links = -1' in my php.ini file. Is there anything else I need to add to the .ini file? I tried uncommenting the extension for php_ldap.dll, thinking that that might make it work... but it didn't. Anyone have any suggestions b/c I'm stuck? Thanks, ~Philip CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: Subject OUTER JOIN ( WAS Subject: Multiple SELECT)
This answer is quite involved, see below. Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=ISO-8859-1 MIME-Version: 1.0 To: [EMAIL PROTECTED] Date: Sun, 11 Jul 2004 19:31:21 -0700 From: Marcjon [EMAIL PROTECTED] Message-Id: [EMAIL PROTECTED] I know this issue comes up alot, I've looked through the archives looking for a solution, but none of them are what I want. I have two tables with share a common coloumn, username. One is the user table. It's used to store the user's password, username, email and so on. The other table is for the user's profile (if they choose to have one). It contains msnm, aim, yahoo etc address, birthdate and other stuff like that. So far I have this query: SELECT forumusers.email AS email,forumusers.username AS username,forumusers.level AS level, forumusers.lastlogon1 AS lastlogon1,forumusers.lastlogon2 AS lastlogon2,forumusers.settings1 AS settings1,forumusers.confirmationcode AS confirmationcode, forumuserprofiles.sex AS sex, forumuserprofiles.birthdate AS birthdate, forumuserprofiles.address_msnm AS address_msnm, forumuserprofiles.address_aim AS address_aim, forumuserprofiles.address_yahoo AS address_yahoo, forumuserprofiles.address_icq AS address_icq FROM .$godlyness['database_database']..forumusers,.$godlyness['database_database']..forumuserprofiles .$filterbu. ORDER BY username You need to use LEFT OUTER JOIN syntax which will match rows on a key, and return NULL where there is no match for the same key in another table. BTW How does your query below work ? There's no 'WHERE' filter ! So - lets try a stripped down version of your 1st query : SELECT forumusers.*, forumuserprofile.* FROM forumusers, forumuserprofile WHERE forumusers.username = '.$selected_username.' AND forumusers.username = forumuserprofile.username This query matches all forumusers with the specified $selected_username, and all forumuserprofiles which *also* match that username. Now, you need to modify this query to recover the NON-MATCHING (ie, empty) user profiles as well, this is where LEFT OUTER JOIN comes in : SELECT forumusers.*, forumuserprofile.* FROM forumusers LEFT OUTER JOIN forumuserprofile ON forumusers.username = forumuserprofile.username WHERE forumusers.username = '.$selected_username.' So, here we're still selecting the forumuser with username $selected_username, but we *also* do a left join and include any rows where there is a match as well as there is not a match for forumusers.username = forumuseprofile.username. If there is *no* match, all the columns from forumuserprofile will be NULL, if there *is* a matching forumusers.username = forumuserprofile.username, then the columns from forumuserprofile will contain values. HTH Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE : validate 2 password fields
Sorry, my typo, the else line should have read (note the missing brace) return true; } else { alert(Passwords did not match); Please note you need to turn on error reporting for scripts in IE or your chosen browser - doing so would have told you the line which had the syntax error. Developing websites with error reporting turned off is a terribly bad idea ;-) Cheers - Neil At 23:11 29/06/2004 -0400, Vincent Jordan wrote: I have the following code and it is not working. it will pass the info along. hwew is a copy of the entire code:. ?php ? script function Validate() { if (document.UserAddNew.username.value == '') { alert('Warranty Database:\nPlease Enter a Username\!'); document.UserAddNew.username.select(); return false; } if (document.UserAddNew.password.value == '') { alert('Warranty Database:\nPlease Enter a Password\!'); document.UserAddNew.password.select(); return false; } } function checkpasswords() { df=document.forms['UserAddNew']; if (df['password'].value==df['password2'].value) { // Passwords matched, clear password2, allow form to submit df['password2'].value=''; return true; else { alert('Passwords did not match'); return false; } } /script form method='post' action='' name='UserAddNew' onSubmit='return checkpasswords()' Username : INPUT TYPE='TEXT' name='username' title='username' id='username'br Password : INPUT TYPE='TEXT' name='password' title='password' id='password'br Re Type Password: INPUT TYPE='TEXT' name='password2' title='password2' id='password2'br Department: SELECT name='department' title='department' id='department' OPTION value='Managment'Managment/OPTION OPTION value='Sales'Sales/OPTION OPTION value='Tech Support'Tech Support/OPTION OPTION value='Returns'Returns/OPTION OPTION value='Other'Other/OPTION/SELECTbr input type=submit name=submit value='Add' input type=reset name=reset value='Reset' onClick=if (confirm('Warranty Database:\nAre you sure you want to reset the form\?')); /FORM - Original Message - From: Neil Smith [MVP, Digital media] [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 6:25 AM Subject: [SPAM] RE : validate 2 password fields Attach onsubmit=return checkpasswords() to your form tag. function checkpasswords() { df=document.forms[DisplayUser]; if (df[password1].value==df[password2].value) { // Passwords matched, clear password2, allow form to submit df[password2].value=; return true; else { alert(Passwords did not match); return false; } } Cheers - Neil Message-ID: [EMAIL PROTECTED] From: Vincent Jordan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Sun, 27 Jun 2004 20:49:47 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_0029_01C45C88.48576050 Subject: 2 requests: validate 2 password fields as equal and rewrite data Could someone instruct me or point me in the right direction. I would like to have a input type=password name=password input type=password name=password2 when submit it will check to se if password and password2 are = ( i do not want password2 to end up in POST on submit.) CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Image Resize
Kenny - you will find there are no native PHP handlers for TIFF. If you're on a unix system you'll be able to use the imagemagick function 'convert' at a command line to do this. Something like this I developed for a client will work, you'll need to look up the passthru function in the PHP manual. This is an advanced technique, I'm not planning on discussing it in detail so you'll need to figure out how to get it to work on your own system. Please don't mail me saying 'it doesn't work'. $binpath is the path to the system binaries, often /usr/bin. You're using the 'convert' command whlch you'll need to read up on in the imagemagick documentation, there's no space to put it here as it's very extensive in what image formats it can convert. PNG, PSD, GIF and other formats are also available. In this case, $image_handle-username was just a user object which I used as part of the file name for the created file, as was $imgid. The file name ends in .jpeg in this case, convert decides what to convert to by the file extension of the created file name you give it. passthru($binpath/convert -quality 100 tiff:$filename $temppath.$image_handle-username.$imgid.jpeg,$imageraw); $i=0; while (! is_file($imgdir/.$image_handle-username.$imgid.jpeg) filesize($temppath.$image_handle-username.$imgid.jpeg)==0) { // Wait 0.2s increments for image creation to complete (if system is slow) $i++; if ($i = 25) { // 5 seconds elapsed, still no file created break; } clearstatcache(); usleep(20); } You'll need to check to see if the created file is available after a few milliseconds as this is an out-of-band process, that is PHP can't know when the convert has finished in the case of a very large TIFF, So it polls the directory for the file becoming available. Clearstatcache is needed to purge the cached directory information, otherwise the loop will lock out assuming the file is never there. It breaks the loop at 5 seconds anyway if a hang has occurred. HTH Neil At 02:46 30/06/2004 +, you wrote: To: Kenny [EMAIL PROTECTED], [EMAIL PROTECTED] Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Subject: Re: [PHP-DB] Image Resize Kenny wrote: Hi All, Does anyone know if you can resize a tif image? I have created a photo gallery and the client want to be able to upload Jpeg and Tiff files, Unfortunately I have never worked with Tiff Here is my code for Jpg files, But tiff has me stumped tiff is not good graficformat. The browsers aren´ t able to display this. Some browsers, f. e. IE are not able to display the PNG-format always properly. The best you take only jpg. The snipplet you send seams to work. Regards, CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE : validate 2 password fields
Attach onsubmit=return checkpasswords() to your form tag. function checkpasswords() { df=document.forms[DisplayUser]; if (df[password1].value==df[password2].value) { // Passwords matched, clear password2, allow form to submit df[password2].value=; return true; else { alert(Passwords did not match); return false; } } Cheers - Neil Message-ID: [EMAIL PROTECTED] From: Vincent Jordan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Sun, 27 Jun 2004 20:49:47 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_0029_01C45C88.48576050 Subject: 2 requests: validate 2 password fields as equal and rewrite data Could someone instruct me or point me in the right direction. I would like to have a input type=password name=password input type=password name=password2 when submit it will check to se if password and password2 are = ( i do not want password2 to end up in POST on submit.) CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subject: Re: Javascript forms [2]
At 22:20 28/06/2004 +, you wrote: From: Vincent Jordan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Sun, 27 Jun 2004 20:35:52 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: Re: Php form row delete problems I have tried this but somehow can not seem to get it working. I have incuded copy of the pages. thanks in advance for the assistance. As usual you didn't say what's not working. Separately there's a bug in your javascript, this line : a href=DelUser.php onClick=if (confirm('Warranty Database:\nAre you sure you like to delete the selected users\(s\)\?')); Will always return true in javascript - that is, the default action of a link is to return true, and the link is followed. So you will *always* delete the user even if they say no. You need to write return confirm() instead of if (confirm()) a href=DelUser.php onClick=return (confirm('Warranty Database:\nAre you sure you like to delete the selected users\(s\)\?')); Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date help needed
No, it's actually very easy to do the autocomplete once you get the hang of it. Actually the way I've done it is to populate a multi-select box but you could also use a DIV and write out the values Dump the email addresses as an XML file (generate this dynamically) then use XSLT to read out matching rows on each keyup ... basically you filter the XML file each time till you get down to one value Yes, it's javascript but it works really well as long as you have some control over your client browser (in the case of your boss, probably IE but it can be made to work in mozilla / firefox too) Mail me offlist if you want a working example. Cheers - Neil At 11:07 25/06/2004 +, you wrote: Message-Id: [EMAIL PROTECTED] From: Chris Payne [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Thu, 24 Jun 2004 22:53:13 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Subject: RE: [PHP-DB] Date help needed One thing he wanted which I didn't know how to do (Javascript I guess which I don't know much about) was to preload a database of email address, and as he started to type an email address it would do a sort of auto-complete, but have no clue how to go about that so just told him not viable ATM. CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Retrieve data, add new fields and enter it in new ta
Look at the INSERT... SELECT syntax in the MySQL manual Basically you can do this but only between 2 different tables (which is what you specified) : Generate and *test* the SELECT statement you want to use to recover your data set. Then in PHP try a query like this : $query=INSERT INTO table2 ( id, name, address1, address2, address3, password ) SELECT id, name, address1, address2, address3, ' .$your_password_key. ' FROM table1; So here you have selected the 4 fields form table 1 and added a password key added from a PHP variable Possibly not the best example but you get the idea. CHeers -Neil At 02:06 22/06/2004 +, you wrote: Date: Tue, 22 Jun 2004 12:06:41 +1000 Message-ID: [EMAIL PROTECTED] From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Retrieve data, add new fields and enter it in new table Hi, I am trying to do the following: - retrieve data from a table based on the $id field (easy enough) - add a few more fields to the data set, then - enter the data (and new fields) into a new table (based on the selected $id field) I'm just not sure how to structure this, do I use a temporary table, add the new fields, then move the data set into the target table? Or is there a better way? Thanks Justin CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re:Subject: big/small letters with oracle No2
You can just use the PHP function ucwords(strtolower()) on the results as you loop through them to create the Select list ;-) So, this would convert NA to Na, and MG to Mg (as well as mG to Mg and MG to Mg ;-) Cheers - Neil At 10:41 01/06/2004 +, you wrote: Date: Tue, 01 Jun 2004 12:41:11 +0200 From: Torsten Lange [EMAIL PROTECTED] To: [EMAIL PROTECTED] Message-id: [EMAIL PROTECTED] MIME-version: 1.0 Content-type: text/plain; charset=us-ascii; format=flowed Content-transfer-encoding: 7BIT Subject: big/small letters with oracle No2 But when using queries on the USER_... data dictionary, Oracle delivers always big letters, which is for chemical elements (NA vs. Na) or location names (ALICE SPRINGS vs. Alice Springs) and location codes often uncomfortable to read. It happend in the heat of the moment... The above is only the case for column names, i.e. the parameter columns (Na, Mg...). Locations etc. belonging of course to data sets. Torsten CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Table Def error WAS Where Error
It's probably a table definition thing. Is your 'netreceipts' column an ENUM ? How about your status column - is it a CHAR ? ENUM ? Send us the table definition for the misbehaving table. Also, send us the error message before asking what's the solution, we're not mind readers :-)) Your query below unless it's in some kind of pseudocode, needs quotes around the i, vis : WHERE status='i' not as stated WHERE status=i Because 'i' is a string and must be quoted. The same happens for ENUM columns, even though the values might appear numeric (100, 400, 500) they must be quoted and treated like strings, as they are not internally numbers, but indexes into a list of strings. In the same way, you can return the index directly by asking for status+0 which converts the status index into a number rather than returning the string '100' you will get the number 1 (which is useful when creating checkboxes from ENUMs) HTH Neil At 14:36 29/05/2004 +, you wrote: To: [EMAIL PROTECTED] [EMAIL PROTECTED] From: [EMAIL PROTECTED] Content-Type: text/plain; format=flowed; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Date: Sat, 29 May 2004 10:35:40 -0400 Message-ID: [EMAIL PROTECTED] Subject: Where Error This should be ultimately simple; it just doesn't work. I want to limit display to rows from a single table in which the status column contains i. Only options are i, c, o. After the select line I have where status = i (no quote marks). I get an error message to the while line that calls the table. When I limit by another column where netreceipts 500 I get the desired result. When I change it to where netreceipts = 500 (and there is a 500) I get the error message above. What's the solution? Ken CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: SQL Statement
You're missing a closing quote at the end of the line, following the group by `calls`, it should end as , `calls`; Since you are passing a string to PHP to hand off to the DB engine. Also, PHP function mysql_error($connection) is useful as this will return any errors generated by your database (if present). Cheers - Neil At 00:57 19/05/2004 +, you wrote: $sqlwrk .= WHERE (`pk_phone_number` = . $fk_phone”) AND (`date` BETWEEN “'$my_startdate'” AND “'$my_enddate'”)”; $sqlwrk .= GROUP BY `pk_phone_reports`, `fk_ph_num`, `date`, `calls`; CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: question on select
** Or send XML files up and down dynamically using xmlhttp in IE and Mozilla ;-) Cheers - Neil At 04:06 13/05/2004 +, you wrote: To make a client-side solution possible, you'd have to send ALL POSSIBLE data to the page all at the same time then manipulate it with JavaScript. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re:RE: [PHP-DB] question on select
That *should* read : document.fcountry.newcountry.value = document.fcountry.country[document.fcountry.country.selectedIndex].value; Skip the 'options' object - I'm surprised you're not getting a javascript error, maybe you have error reporting turned off in your browser ? In any case, always 'alert' that value when you create it, so you know what you're actually submitting during testing. At 04:06 13/05/2004 +, you wrote: From: hengameh [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Date: Wed, 12 May 2004 12:22:20 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: [EMAIL PROTECTED] Subject: RE: [PHP-DB] question on select !-- function changeMenu() { document.fcountry.newcountry.value = document.fcountry.country.options[document.fcountry.country.selectedIndex].v alue; } -- /script CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Edit with notepad
Just offer them a link to the next page. Put amp;format=word in as the query string if they choose the 'view word document' link or no query string if they choose the plain text view as html page. Then go for if ($_GET[format]=='word') { header(Content-type: application/vnd-ms.word); } Really, offering your customers what they want is what we're all about - you have to take some responsibility for implementing snippets of code like this ;-) Cheers - Neil At 10:39 11/05/2004 +, you wrote: Message-ID: [EMAIL PROTECTED] From: Ng Hwee Hwee [EMAIL PROTECTED] To: DBList [EMAIL PROTECTED] Date: Tue, 11 May 2004 09:25:08 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: Re: [PHP-DB] Edit with notepad hi, header(Content-type: application/vnd-ms.word); is not what my customers want. They may or may not want to save it in their harddisk.. but having the header at the beginning of my page will force them to save it in their harddisk. Some of the customers just want to view it on the web while others want to view it in Word and possibly edit it and then save it.. how can i achieve such flexibility for them? thank you!! regards, hwee CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE :I thought 4 didn't equal 456456456%20%20OR%2012
It doesn't. What you're seeing is an SQL injection attack. If you *trust* the SQL code you allow from POST or GET requests, your SQL server will be own3d in due course. That URL actually translates to 456456456 OR 12 Which is always true. So If you use this verbatim, you'll get a true result (if you were using it as part of a login process, the user would be in without providing a login and password :-p ) I always, *always* apply $result=(integer) $_GET[uid] to these strings : That way you are guaranteed it's a number not a string. Cheers - Neil Message-ID: [EMAIL PROTECTED] From: Dan Bowkley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Sun, 9 May 2004 15:17:19 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: Re: [PHP-DB] supernoob strikes again I thought 4 didn't equal 456456456%20%20OR%2012 CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: Edit with notepad
Send before any content, an MS-Word header : header(Content-type: application/vnd-ms.word); Then output your HTML. Cheers - Neil. At 07:41 10/05/2004 +, you wrote: Message-ID: [EMAIL PROTECTED] From: Ng Hwee Hwee [EMAIL PROTECTED] To: DBList [EMAIL PROTECTED] Date: Mon, 10 May 2004 15:40:27 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary==_NextPart_000_00D2_01C436A5.1E3B4100 Subject: Edit with notepad Hi My customers would like to save my php outputs in a word document file and so I have added a meta tag META NAME=ProgID CONTENT=word.document in my php files. However, my File-Edit button in IE6 is greyed out. why? i see that other php files on other websites allow Edit, why is it that my programs don't allow it?? thanx for any insights!! regards, Hwee Hwee CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subject: Struggling [Is : With database normalisation]
Probably something as simple as changing the structure to a more normalised form would help : By adding a table which links evt_details to evt_sponsors, you do away with the need to have (up to 4) distinct fields to hold the evt_sponsor in your evt_details table. So add something like table : `evt_sponsors_list` `evt_detail_link_id`INT 6 `evt_sponsor_link_id` INT 6 Then your query becomes more like : SELECT evt_detail_id, evt_detail_title, evt_sponsor_name FROM evt_details LEFT JOIN evt_sponsors_list ON evt_detail_id=evt_detail_link_id LEFT JOIN evt_sponsors ON evt_sponsor_link_id=evt_sponsor_id WHERE evt_detail_date = DATE_SUB(NOW(), INTERVAL 1 DAY) The left join evt_sponsors_list will pull any number (1-many) of results where there is one or more matching sponsor. The left join evt_sponsors will then match those results to the evt_sponsors table, resulting in a series of rows for each event. You could change the 1st join to read 'LEFT OUTER JOIN' if you wanted to get events where there are *no* sponsors (what you were previously doing with `OR e.evt_sponsor_id_n =0`) This is a one-to-many relationship and allows you to have flexibility for any number of event sponsors (not just 4) without adding more and more fields to your event_detail table. Make sure to index both columns in the evt_detail_link table for good performance, and the storage overhead is almost neglible. You can then run a simple loop in PHP, cache the new row each time the evt_detail_id changes (containing the event title, the ID is useful so you can link to full details by event_id if you need to). Then for the 1st and subsequent rows, just use the evt_sponsor_name. Output the previous row when the evt_detail_id changes, and output the final row when there are no more results. Cheers - Neil. At 22:24 04/05/2004 +, you wrote: PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php From: Erik Meyer [EMAIL PROTECTED] To: PHP-DB [EMAIL PROTECTED] Date: Tue, 4 May 2004 17:24:59 -0500 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: Struggling Hope someone can help me. I have two tables: evt_details evt_sponsors The structure is as follows: evt_details evt_sponsors evt_detail_id (INT)(PK) evt_sponsor_id (INT)(PK) evt_detail_title(CHAR) evt_sponsor_name (CHAR) evt_sponsor_id1 (INT) NON-NULL evt_detail_date (date) evt_sponsor_id2 (INT) NULL evt_sponsor_id3 (INT) NULL evt_sponsor_id4 (INT) NULL Now, the event can have either 1 sponsor, 2 sponsors, 3 sponsors, or 4 sponsors. Is there a way where I can return a result whether it has 1, 2, 3, or 4 since some of the events will not have a 2d,3d, or 4th sponsor, a combination of 1 2, 1,2,3, or all 4 depending on the values in the table? Here is the query I have: SELECT e.evt_detail_title, a.evt_sponsor_name, b.evt_sponsor_name, c.evt_sponsor_name, d.evt_sponsor_name FROM evt_sponsors a, evt_sponsors b, evt_sponsors c, evt_sponsors d, evt_details e WHERE e.evt_detail_date = DATE_SUB(NOW(), INTERVAL 1 DAY) AND e.evt_sponsor_id1=a.evt_sponsor_id AND (e.evt_sponsor_id2=b.evt_sponsor_id OR e.evt_sponsor_id2=0) AND (e.evt_sponsor_id3 =c.evt_sponsor_id OR e.evt_sponsor_id3=0) AND (e.evt_sponsor_id4=d.evt_sponsor_id OR e.evt_sponsor_id4 =0) Thanks for the help, Erik W. Meyer CaptionKit http://www.captionkit.com : Production tools for accessible subtitled internet media, transcripts and searchable video. Supports Real Player, Quicktime and Windows Media Player. VideoChat with friends online, get Freshly Toasted every day at http://www.fresh-toast.net : NetMeeting solutions for a connected world. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php