Re: [PHP-DB] $_SESSION issues - possible security hole?
If it changes the value of $_SESSION['rights'], then how come if ($_SESSION['rights'] != super) on line 14 doesn't exit()? Or does that happen when you hit refresh the second time? Or does the user it changes to also have 'super' rights? Why use extract()? Try commenting it out... apart from it being overhead in this case, it seems like a bad idea importing variable names into the symbol table for the global scope. It works since you're skipping the $type parameter and so EXTR_OVERWRITE is used, but this also doesn't seem anything to trust. Debugging PHP with XDebug usually helps me in cases like this. If you can, install it to your test server and do a trace. On Sun, Aug 10, 2008 at 7:34 PM, Darron Butler [EMAIL PROTECTED] wrote: I have built an application where users have to log in (the user data is stored in MySQL). I have 3 levels of rights for users; 1-normal rights, 2-admin rights, and 3-super user rights. When a user logs in, I set the $_SESSION['rights'] variable accordingly so I can reference it thru out the site. I have around a dozen pages where the login session information and the user rights piece work just fine. However, I'm creating an administration page where admins have some functionality but super users have all functionality and I've run into issues. I was using the session rights variable in a number of different IF statements to display and during a simple test I hit the refresh button on my browser and found that when I did so it CHANGED my session rights to those of a different user!?!? I have looked thru my code until I'm blue in the face and have even boiled the code down to the simpest display possible, yet when I hit refresh, it still changes the user and rights info (note all the other pages work fine). I have been able to determine that there must be something in my USERS query that is causing the issue. I changed the query to get data from a completely different table and show it in a select list and hitting refresh does NOT change the user or the user rights! So.does this mean querying the very table that lists users information (with data like rights and user in it) somehow can change the $_SESSION variables?? That appears to be what's going on, but I thought you had to explicitly assign the $_SESSION variables before they could be changed. Help!!! I have included my code and the database table structure below: ?php /*** Program is only available to admins and super users. This page allows for the adding/editing of user accounts and priveledges as well as allowing for application messages, application lockdown/shutdown, and standard parts administration. / include (php_header.inc); if ($_SESSION['rights'] != super) { header(location: landing.php); exit (); } /** ADMINISTER USERS */ $query1 = select * from USERS order by user; $result1 = mysql_query($query1) or die (Couldn't select all users - super); include (html_header.inc); echo p{$_SESSION['user']}, {$_SESSION['rights']}/p\n pform action='edit_user.php' method='post'Choose a user to edit: select name='user'\n; while ($row = mysql_fetch_array($result1, MYSQL_ASSOC)) { extract($row); echo option value='$user'$fname $lname - $user\n; } echo /select input type='submit' value='Edit User'/form/p\n /body\n /html; ? and here is the included file: ?php / This is the standard header for every page and should be included after the PHP opening tag on each page. This program ensures that all pages can only be accessed by users that are legitimately logged on. This program also allows for administratively disabling the application by super users. After disabling, only superusers can make changes. / session_start(); include (special_characters.inc); /** AUTHORIZATION CHECK */ if ($_SESSION['auth'] != yes) { header (location: index.php); exit(); } if ($_SESSION['rights'] != super) { $query = select * from APP_STATUS; $result = mysql_query($query) or die (Couldn't select APP_STATUS); $app = mysql_fetch_array ($result,MYSQL_ASSOC); if ($app['app_status'] == 2) { $msg = $app['app_message']; die($msg); } } ? my database table for the users looks like this: Field Type user varchar(20) PRIMARY KEY pwd varchar(255) fname varchar(20) lname varchar(20) email varchar(40) rights varchar(20) level varchar(20) credentials text admin varchar(20) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] $_SESSION issues - possible security hole?
Why use extract()? Try commenting it out... apart from it being If you use 'register globals' there's a good chance that a variable $rights exists because it's a key in your $_SESSION array (don't shoot me if I'm wrong, I've never worked with 'register globals'). By using extract() without the $type parameter (so with EXTR_OVERWRITE set), the $type variable is overwritten. So do try commenting it out. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] $_SESSION issues - possible security hole?
Thanks for your thoughts. To answer your first question, I'm using extract() because this is a page where admins and super users can edit the permissions of others for the site. Therefore, I have to query the database to create a listing of all users, and then have the admin/super user select one to modify (I was planning to serve the 'rights' informatin from the selected user via POST to another page where changes could be made). sometimes the user and rights that I get assigned when I hit refresh are another user with super rights and sometimes one with less than super rights and then I get sent to the 'die' landing page. I'm a real newbie at PHP/MySQL, so if there is a better/easier/more efficient way of creating the select list, I'm just not aware of how to do it. I just tried removing the extract statement and the select list is now empty... I'm using a free PHP/MySQL host online so I don't have access to make register_global changes, but I did find in the documentation that they have it set to on. On a similar note, the variable $_SESSION['rights'] does certainly exist, it exists for the admin/super user logged in and accessing the administration page. What's interesting about this whole thing is that I have changed the query to include non session variables I have set and everything works fine. For instance (to clarify) since I set $_SESSION['user'] and $_SESSION['rights'] when the user logs in, if my query to create the selection list is based on any other table columns (for instance, fname and lname and NOT user or rights) then the weird behavior does not show up. Having gone thru that...somehow, someway, the query of all user info seems to change the session variables. I appreciate your brain power thinking thru this! Any new thoughts? drb On Sun, Aug 10, 2008 at 2:33 PM, Evert Lammerts [EMAIL PROTECTED]wrote: Why use extract()? Try commenting it out... apart from it being If you use 'register globals' there's a good chance that a variable $rights exists because it's a key in your $_SESSION array (don't shoot me if I'm wrong, I've never worked with 'register globals'). By using extract() without the $type parameter (so with EXTR_OVERWRITE set), the $type variable is overwritten. So do try commenting it out.
Re: [PHP-DB] $_SESSION issues - possible security hole?
On Sun, Aug 10, 2008 at 4:23 PM, Darron Butler [EMAIL PROTECTED] wrote: Thanks for your thoughts. To answer your first question, I'm using extract() because this is a page where admins and super users can edit the permissions of others for the site. Therefore, I have to query the database to create a listing of all users, and then have the admin/super user select one to modify (I was planning to serve the 'rights' informatin from the selected user via POST to another page where changes could be made). sometimes the user and rights that I get assigned when I hit refresh are another user with super rights and sometimes one with less than super rights and then I get sent to the 'die' landing page. I'm a real newbie at PHP/MySQL, so if there is a better/easier/more efficient way of creating the select list, I'm just not aware of how to do it. I just tried removing the extract statement and the select list is now empty... I'm using a free PHP/MySQL host online so I don't have access to make register_global changes, but I did find in the documentation that they have it set to on. On a similar note, the variable $_SESSION['rights'] does certainly exist, it exists for the admin/super user logged in and accessing the administration page. What's interesting about this whole thing is that I have changed the query to include non session variables I have set and everything works fine. For instance (to clarify) since I set $_SESSION['user'] and $_SESSION['rights'] when the user logs in, if my query to create the selection list is based on any other table columns (for instance, fname and lname and NOT user or rights) then the weird behavior does not show up. Having gone thru that...somehow, someway, the query of all user info seems to change the session variables. I appreciate your brain power thinking thru this! Any new thoughts? drb On Sun, Aug 10, 2008 at 2:33 PM, Evert Lammerts [EMAIL PROTECTED] wrote: Why use extract()? Try commenting it out... apart from it being If you use 'register globals' there's a good chance that a variable $rights exists because it's a key in your $_SESSION array (don't shoot me if I'm wrong, I've never worked with 'register globals'). By using extract() without the $type parameter (so with EXTR_OVERWRITE set), the $type variable is overwritten. So do try commenting it out. You may want to consider not saving the data for the user rights in the session if its getting funky. Do a general query to the table on each page load where you want to check the data and rely just on the session cookie. Then you can make a simpler check to see if the user still has those permissions. Aslo judging from your post, the biggest hole will likely be that you are referencing the auto number of the user's id that is being changed. Changing that parameter would all changes to another users account easily... I would suggest using a md5 or sha1 hash to offset that possibility. -- Bastien Cat, the other other white meat
Re: [PHP-DB] $_SESSION issues - possible security hole?
There's your answer. With register_globals on $_SESSION['rights'] becomes $rights and when you do extract($row) you are overwritting the $_SESSION variable. A safer way of using your code would be: while ($row = mysql_fetch_array($result1, MYSQL_ASSOC)) { ? option value=?=$row['user']??=$row['fname']? ?=$row['lname']? - ?=$row['user']? ? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Darron Butler wrote: Thanks for your thoughts. To answer your first question, I'm using extract() because this is a page where admins and super users can edit the permissions of others for the site. Therefore, I have to query the database to create a listing of all users, and then have the admin/super user select one to modify (I was planning to serve the 'rights' informatin from the selected user via POST to another page where changes could be made). sometimes the user and rights that I get assigned when I hit refresh are another user with super rights and sometimes one with less than super rights and then I get sent to the 'die' landing page. I'm a real newbie at PHP/MySQL, so if there is a better/easier/more efficient way of creating the select list, I'm just not aware of how to do it. I just tried removing the extract statement and the select list is now empty... I'm using a free PHP/MySQL host online so I don't have access to make register_global changes, but I did find in the documentation that they have it set to on. On a similar note, the variable $_SESSION['rights'] does certainly exist, it exists for the admin/super user logged in and accessing the administration page. What's interesting about this whole thing is that I have changed the query to include non session variables I have set and everything works fine. For instance (to clarify) since I set $_SESSION['user'] and $_SESSION['rights'] when the user logs in, if my query to create the selection list is based on any other table columns (for instance, fname and lname and NOT user or rights) then the weird behavior does not show up. Having gone thru that...somehow, someway, the query of all user info seems to change the session variables. I appreciate your brain power thinking thru this! Any new thoughts? drb On Sun, Aug 10, 2008 at 2:33 PM, Evert Lammerts [EMAIL PROTECTED]wrote: Why use extract()? Try commenting it out... apart from it being If you use 'register globals' there's a good chance that a variable $rights exists because it's a key in your $_SESSION array (don't shoot me if I'm wrong, I've never worked with 'register globals'). By using extract() without the $type parameter (so with EXTR_OVERWRITE set), the $type variable is overwritten. So do try commenting it out. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] $_SESSION issues - possible security hole?
Thanks! In fact I just updated my code to your exact example below and it works! Its a good thing you all fixed this for me...I have very few hairs left! Thanks again everyone...drb On Sun, Aug 10, 2008 at 4:53 PM, Micah Gersten [EMAIL PROTECTED] wrote: There's your answer. With register_globals on $_SESSION['rights'] becomes $rights and when you do extract($row) you are overwritting the $_SESSION variable. A safer way of using your code would be: while ($row = mysql_fetch_array($result1, MYSQL_ASSOC)) { ? option value=?=$row['user']??=$row['fname']? ?=$row['lname']? - ?=$row['user']? ? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Darron Butler wrote: Thanks for your thoughts. To answer your first question, I'm using extract() because this is a page where admins and super users can edit the permissions of others for the site. Therefore, I have to query the database to create a listing of all users, and then have the admin/super user select one to modify (I was planning to serve the 'rights' informatin from the selected user via POST to another page where changes could be made). sometimes the user and rights that I get assigned when I hit refresh are another user with super rights and sometimes one with less than super rights and then I get sent to the 'die' landing page. I'm a real newbie at PHP/MySQL, so if there is a better/easier/more efficient way of creating the select list, I'm just not aware of how to do it. I just tried removing the extract statement and the select list is now empty... I'm using a free PHP/MySQL host online so I don't have access to make register_global changes, but I did find in the documentation that they have it set to on. On a similar note, the variable $_SESSION['rights'] does certainly exist, it exists for the admin/super user logged in and accessing the administration page. What's interesting about this whole thing is that I have changed the query to include non session variables I have set and everything works fine. For instance (to clarify) since I set $_SESSION['user'] and $_SESSION['rights'] when the user logs in, if my query to create the selection list is based on any other table columns (for instance, fname and lname and NOT user or rights) then the weird behavior does not show up. Having gone thru that...somehow, someway, the query of all user info seems to change the session variables. I appreciate your brain power thinking thru this! Any new thoughts? drb On Sun, Aug 10, 2008 at 2:33 PM, Evert Lammerts [EMAIL PROTECTED]wrote: Why use extract()? Try commenting it out... apart from it being If you use 'register globals' there's a good chance that a variable $rights exists because it's a key in your $_SESSION array (don't shoot me if I'm wrong, I've never worked with 'register globals'). By using extract() without the $type parameter (so with EXTR_OVERWRITE set), the $type variable is overwritten. So do try commenting it out.
Re: [PHP-DB] is this possible in one query?
u can have the query as $query = select * from $table where nameId = 31 ; $result = mysql_query ( $query ) ; $i = 0 ; while ( $row = mysql_fetch_array( $result , MYSQL_NUM ) ) { $array[$i][0] = $row[0] ; $array[$i][1] = $row[1] ; $array[$i][2] = $row[2] ; $i++ } // now $array has ur required names :-) rajesh On Sat, 14 Jan 2006, Sjef Janssen wrote: Hi there, I have a table that keeps names for different language codes. In a short example: nameId name languageCode 31 House EN 31 Wohnung DE 32 Piece En 32 Stuck De 33 Car EN 33 PKW DE What I would like is to have a query that returns for example: nameId = 31 Names = House - Wohnung Maybe I can even have a result that consists of an array with nameIds and Names. Or should I fire a bunch of queries after each other to have this result? I use mysql and php 4.3.8 Tnxs!! -- Your absence should be long enough so that someone miss you , But it shouldn't be so long enough that Someone learns to live without you So keep in touch ! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is it possible to access MySQL table on server A from server B?
even if the domain name expired, most likely you will be able to access the site database using the ip address. but just that you have to give / have permissions to connect from the new domain or ip. Also, if you can login to the server with ftp, i guess you can ssh / telnet to server and take a mysql_dump (backup). You can write a ftp code in php. get all the source code in the old server and write it in the new server. -ramki - Original Message - From: Trisha [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 12:23 PM Subject: [PHP-DB] Is it possible to access MySQL table on server A from server B? Someone let me use their extra web space so I would have a banner-free place to put my site. After their domain name expired, they didn't re-register it, and didn't let me know ahead of time. I wasn't thinking properly, so I had not created a backup of any of the tables in my database. I have purchased my own domain now, and am wondering if there is any kind of code I can execute from my current domain to copy or back up the tables from the old domain. I believe the database has not yet been deleted, since my user account has not been deleted, and all of the files I've uploaded through FTP are still intact. I've sent several e-mails to the company that was providing the shared hosting package, but did not receive any response, so I don't think they will help in any way. If anyone could me know if accessing the database on the old server from the new server is possible and how to do it, or if I'm just plain old SOL, I would greatly appreciate it. Thanks in advance :) Trisha __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is it possible to open a .txt file into a recordset for validation?
[EMAIL PROTECTED] wrote: Want to allow users to upload .txt file and then loop through it's contents to validate and reject/accept based on results. Is this possible? Got any links, code, etc? Yes. http://us2.php.net/manual/en/features.file-upload.php and then http://us2.php.net/manual/en/ref.filesystem.php specifically http://us2.php.net/manual/en/function.fopen.php -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ODBTP, a possible solution for MS-SQL and other databases
Sounds interesting. Perhaps toss it into PEAR/PECL and see if anybody is interested in pitching in with maintenance. You never know, someone might pop up and add a bunch of improvements that will benefit you directly. -Rasmus On Sat, 2 Nov 2002, Robert Twitty wrote: Hello I have been using PHP for about 9 months, and have chosen it as my primary scripting language for web applications. I have and still use ASP and JSP. IMHO, PHP is superior and easier to use than those languages except in one area that is important to me, which is the ability to access MS SQL Server 7.0 / 2000 databases from a UNIX box. Out of the box PHP provides great support for MySQL and PostgreSQL, and at this time I have no desire to use them because I do not believe that they are ready for prime time. The open source solution that is always recommended for UNIX-based PHP / MS-SQL connectivity is freeTDS, and unfortunately I found it to be quite lacking in its capabilities and useless in certain situations. Another alternative was to use a commercial ODBC driver management system on UNIX. Sadly, it was not in the budget for this endeavor, and the PHP odbc extensions could use some work in terms of ease of use. Because I was determined to use PHP (I really dislike using JSP / JDBC on UNIX, and IIS / ASP is out of the question), I decided to create my own solution. Since I have a substantial amount of experience in programming directly with the Win32 ODBC API and TCP/IP, I decided to create a service that runs on a Win32 platform that can communicate with any platform via TCP/IP. The service uses a home grown protocol that allows a client to access any database that the service can see via the ODBC drivers that are installed on the computer which it resides. In other words, it allows a PHP client on UNIX to access a database using the ODBC drivers installed on a Windows NT / 2000 server. It is nothing more than a middle man service for Win32 ODBC. The name of the service is called ODBTP (Open Database Transport Protocol), and no there is not a RFC for this protocol. Thus far, I have successfully accessed MS-SQL, Oracle and Sybase databases via ODBTP. ODBTP consists of a Windows NT / 2000 service application, an ODBTP client library that can be used to create Win32 or UNIX clients, and a PHP extension module that was created with the library. ODBTP has the following features: * Multi-client servicing * True connection pooling (not persistent connections) * Client reserved connections (virtual connections for stateless web clients) * Supports all data types, including nvarchar, ntext, varchar(255), char(255), datetime, and bigint. * No big-endian / little-endian problems. * Server-side data binding. * Stored procedure execution, parameter passing (including NULL's) and output retrieval. * Transactions, i.e., supports commits and rollbacks under any transaction isolation level. * UNICODE data is processed using UTF-8 encoding (important since PHP strings are not UNICODE) * Can retrieve query results sent in XML format. * Verbose error reporting, all ODBC error messages are sent to client. * No discovered memory leaks or buffer overflow possibilities. * Designed to be as easy as possible to use with PHP I am new to this mailing list, and it appears that PHP is predominantly used for MySQL and PostgreSQL, and thus I am not sure if ODBTP is of any interest to most people on this list. My original intent was not to release ODBTP to the public (I really don't have the time to maintain freeware), but if there is a substantial interest I will release it to the public. I am curious to see how well it performs in other environments. -- bob The following is a table, stored procedures and a php script that uses ODBTP to initialize the table with data. CREATE TABLE dbo.Employees ( Id int IDENTITY (1, 1) NOT NULL , ExtId numeric (15,0) NOT NULL , LastName varchar (50) NOT NULL , FirstName varchar (50) NOT NULL , Title varchar (256) NOT NULL , Salary money NOT NULL , JobDesc varchar (3000) NULL , Notes ntext NULL , Active bit NOT NULL , DateEntered datetime NOT NULL , DateModified datetime NOT NULL , CONSTRAINT PKCL_Employees_Id PRIMARY KEY CLUSTERED ( Id ) ) CREATE PROCEDURE AddEmployee ExtId numeric(15,0), LastName varchar(50), FirstName varchar(50), Title varchar(256), Salary money, JobDesc varchar(3000) = 'Job not defined' AS SET NOCOUNT ON INSERT INTO Employees( ExtId, LastName, FirstName, Title, Salary, JobDesc ) VALUES( ExtId, LastName, FirstName, Title, Salary, JobDesc ) IF ERROR 0 RETURN 0 RETURN IDENTITY GO CREATE PROCEDURE SetEmployeeNotes Id int, Notes ntext AS SET NOCOUNT ON UPDATE Employees SET Notes = Notes, DateModified = getdate()
Re: [PHP-DB] Is this possible in mysql?
list($sum)=mysql_fetch_assoc(mysql_query('SELECT SUM(salary') FROM employes;')); Best regards, Andrey Hristov On Friday 08 March 2002 09:17 pm, you wrote: I need to add up the (integer) values of columns, and return the added up value as one column to php. Is this possible? Or is there something else to do it? (I'd rather not have to mysql_fetch_array() in a while loop and add it up there, it might be a lot of rows) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is it possible ?
On Saturday 16 February 2002 07:35 pm, you wrote: Is it possible to get the AUTO-INCREMENTed ID's value while doing an INSERT and have the value INSERTed to another table in the same FORM ? Raju mysql_query(insert whatever); $insertid=mysql_last_insert(); Then you use this value for your other insert. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Is this possible?
Yes, you can do that easily. It is easier to answer your question if you show us your table structure. -Original Message- From: Raymond Lilleodegard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 11:16 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Is this possible? Hi! I have this tricky case, at lest for me : ) I'm trying to get some data out of two tables and listing the data in a product/price site. But. : I have one table with productinfo and one with prices. And it is several columns with the same id in the pricetable, because every product have several sizes. So... how do I get only one row from the product table and two rows from the price table in one line in a page? Is it possible? Best regards Raymond -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this possible?
My table look like this: Pricetable: (varetabell) (varenr, type, pris) VALUES (1, '6inch', 29), (1, '6inch meny', 51), (1, 'footlong', 45), (1, 'footlong meny', 66), (1, 'salat', 39), (1, 'salat meny', 51), (2, '6inch', 49), (2, '6inch meny', 69), (2, 'footlong', 75), (2, 'footlong meny', 96), (2, 'salat', 49), (2, 'salat meny', 69), Product table: (pristabell) --- (varenr, varenavn, innhold) VALUES ('1','Veggie Delite','Grønnsaker og ost'), ('2','Subway Club', 'Kalkun, skinke og roasbeef'), ('3','Classic Italian BMT', 'Skinke, salami og pepperoni'), And the query that I have tried looks like this: SELECT varetabell.varenavn, varetabell.varenr, varetabell.innhold, pristabell.pris FROM varetabell, pristabell WHERE pristabell.varenr=varetabell.varenr AND pristabell.type='6inch' AND pristabell.type='footlong' - Original Message - From: Rick Emery [EMAIL PROTECTED] To: 'Raymond Lilleodegard' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 6:23 PM Subject: RE: [PHP-DB] Is this possible? Yes, you can do that easily. It is easier to answer your question if you show us your table structure. -Original Message- From: Raymond Lilleodegard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 11:16 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Is this possible? Hi! I have this tricky case, at lest for me : ) I'm trying to get some data out of two tables and listing the data in a product/price site. But. : I have one table with productinfo and one with prices. And it is several columns with the same id in the pricetable, because every product have several sizes. So... how do I get only one row from the product table and two rows from the price table in one line in a page? Is it possible? Best regards Raymond -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this possible?
Hi Raymond, I have this tricky case, at lest for me : ) I'm trying to get some data out of two tables and listing the data in a product/price site. But. : I have one table with productinfo and one with prices. And it is several columns with the same id in the pricetable, because every product have several sizes. So... how do I get only one row from the product table and two rows from the price table in one line in a page? Is it possible? No, not as such. Code one query to do the join and SELECT the data, grouped and ordered by (say) ProdCode and within that PackSize. Then use PHP to cycle (outer loop) though each product (group), continuing the 'output' across the line when consecutive records are for the same ProdCode (but not re-displaying the ProductInfo data). Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this possible?
It looks to me like you should be dividing the data differently; something like (quantity, item, option, price) VALUES (1, '6inch', '', '29), (1, '6inch', 'meny', 51), (1, 'footlong', '', 45), (1, 'footlong', 'meny', 66), and so forth... Raymond lilleødegård [EMAIL PROTECTED] wrote in message 001d01c1af34$621b96e0$31c7d450@amd">news:001d01c1af34$621b96e0$31c7d450@amd... My table look like this: Pricetable: (varetabell) (varenr, type, pris) VALUES (1, '6inch', 29), (1, '6inch meny', 51), (1, 'footlong', 45), (1, 'footlong meny', 66), (1, 'salat', 39), (1, 'salat meny', 51), (2, '6inch', 49), (2, '6inch meny', 69), (2, 'footlong', 75), (2, 'footlong meny', 96), (2, 'salat', 49), (2, 'salat meny', 69), -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this possible?
Raymond, See complaint elsewhere as a result of cross-posting. Add my criticism because I'm on both lists and keep them in separate InBox folders and now feeling schizoid... SELECT * [whatever] FROM pristabell P, varetabell V1, varetabell V2 WHERE P.varenr = V1.varenr AND P.varenr = V2.varenr AND V2.type = concat(V1.type, meny) [sorry, machine is busy on something, so haven't prototyped it] The last AND condition might have to move to a HAVING clause. This conditional on the larger product always being called name + meny - absolutely NOT a good idea! Alternative: AND V2.pris V1.pris (better than what I said before) Have you tried any of these? Please advise, =dn Pricetable: (varetabell) (varenr, type, pris) VALUES (1, '6inch', 29), (1, '6inch meny', 51), (1, 'footlong', 45), (1, 'footlong meny', 66), (1, 'salat', 39), (1, 'salat meny', 51), (2, '6inch', 49), (2, '6inch meny', 69), (2, 'footlong', 75), (2, 'footlong meny', 96), (2, 'salat', 49), (2, 'salat meny', 69), Product table: (pristabell) --- (varenr, varenavn, innhold) VALUES ('1','Veggie Delite','Grønnsaker og ost'), ('2','Subway Club', 'Kalkun, skinke og roasbeef'), ('3','Classic Italian BMT', 'Skinke, salami og pepperoni'), And the query that I have tried looks like this: SELECT varetabell.varenavn, varetabell.varenr, varetabell.innhold, pristabell.pris FROM varetabell, pristabell WHERE pristabell.varenr=varetabell.varenr AND pristabell.type='6inch' AND pristabell.type='footlong' - Original Message - From: Rick Emery [EMAIL PROTECTED] To: 'Raymond Lilleodegard' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 6:23 PM Subject: RE: [PHP-DB] Is this possible? Yes, you can do that easily. It is easier to answer your question if you show us your table structure. -Original Message- From: Raymond Lilleodegard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 11:16 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Is this possible? Hi! I have this tricky case, at lest for me : ) I'm trying to get some data out of two tables and listing the data in a product/price site. But. : I have one table with productinfo and one with prices. And it is several columns with the same id in the pricetable, because every product have several sizes. So... how do I get only one row from the product table and two rows from the price table in one line in a page? Is it possible? Best regards Raymond -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Is this possible?
SELECT * FROM pristabell LEFT JOIN varetabell USING(varenr) WHERE pristabell.varenr=$item This statement will get all prices and quantities fro a given item ($item). Simply interate through the recordset and display the info in whatever format you wish. -Original Message- From: Raymond Lilleødegård [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 11:33 AM To: Rick Emery; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Is this possible? My table look like this: Pricetable: (varetabell) (varenr, type, pris) VALUES (1, '6inch', 29), (1, '6inch meny', 51), (1, 'footlong', 45), (1, 'footlong meny', 66), (1, 'salat', 39), (1, 'salat meny', 51), (2, '6inch', 49), (2, '6inch meny', 69), (2, 'footlong', 75), (2, 'footlong meny', 96), (2, 'salat', 49), (2, 'salat meny', 69), Product table: (pristabell) --- (varenr, varenavn, innhold) VALUES ('1','Veggie Delite','Grønnsaker og ost'), ('2','Subway Club', 'Kalkun, skinke og roasbeef'), ('3','Classic Italian BMT', 'Skinke, salami og pepperoni'), And the query that I have tried looks like this: SELECT varetabell.varenavn, varetabell.varenr, varetabell.innhold, pristabell.pris FROM varetabell, pristabell WHERE pristabell.varenr=varetabell.varenr AND pristabell.type='6inch' AND pristabell.type='footlong' - Original Message - From: Rick Emery [EMAIL PROTECTED] To: 'Raymond Lilleodegard' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 6:23 PM Subject: RE: [PHP-DB] Is this possible? Yes, you can do that easily. It is easier to answer your question if you show us your table structure. -Original Message- From: Raymond Lilleodegard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 11:16 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Is this possible? Hi! I have this tricky case, at lest for me : ) I'm trying to get some data out of two tables and listing the data in a product/price site. But. : I have one table with productinfo and one with prices. And it is several columns with the same id in the pricetable, because every product have several sizes. So... how do I get only one row from the product table and two rows from the price table in one line in a page? Is it possible? Best regards Raymond -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this possible?
I was thinking something similar, but I couldn't figure out what it meant (was it in German?). Why would you need quantity as a field? If buying two, you get a 10% discount, why not put that in a formula? Would make it a little more dynamic. Without knowing all the information: (id, item_name, price, option, option_cost, quantity_discount) ? -- William Fong - [EMAIL PROTECTED] Phone: 626.968.6424 x210 | Fax: 626.968.6877 Wireless #: 805.490.7732| Wireless E-mail: [EMAIL PROTECTED] - Original Message - From: Hugh Bothwell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 06, 2002 10:00 AM Subject: Re: [PHP-DB] Is this possible? It looks to me like you should be dividing the data differently; something like (quantity, item, option, price) VALUES (1, '6inch', '', '29), (1, '6inch', 'meny', 51), (1, 'footlong', '', 45), (1, 'footlong', 'meny', 66), and so forth... Raymond lilleødegård [EMAIL PROTECTED] wrote in message 001d01c1af34$621b96e0$31c7d450@amd">news:001d01c1af34$621b96e0$31c7d450@amd... My table look like this: Pricetable: (varetabell) (varenr, type, pris) VALUES (1, '6inch', 29), (1, '6inch meny', 51), (1, 'footlong', 45), (1, 'footlong meny', 66), (1, 'salat', 39), (1, 'salat meny', 51), (2, '6inch', 49), (2, '6inch meny', 69), (2, 'footlong', 75), (2, 'footlong meny', 96), (2, 'salat', 49), (2, 'salat meny', 69), -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php