RE: [PHP-DB] SQL guru needed!
Cheers for helping me out guys. It proved to fiddly and kludge-like to do what I wanted in SQL (I'll wait until MySQL 4 stable version is about) So I did some stuff with PHP arrays which worked fine. Thanks a lot anyway. Russ -Original Message- From: Ignatius Reilly [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 4:13 PM To: Russ Subject: Re: [PHP-DB] SQL guru needed! Is TourCatID a table? you can't do that, this is a subquery Build first a string $string with all the values of TourCatID in it such as ('a', 'b', ...) And replace your query by IN $string Also quotes needed around $_GET[CatID] Ignatius _ - Original Message - From: Russ <mailto:[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Sent: Tuesday, August 20, 2002 9:59 AM Subject: [PHP-DB] SQL guru needed! Hiya folks: I wish to be able to select items from MySQL if a variable passed via the $_GET method to my PHP4 page is introduced thus: $sql = "SELECT TourName,TourID,$_GET[CatID] IN (TourCatID) FROM tour"; When I echo $sql to the page, the query looks fine but doesn't work, in as far as the wrong results are returned. MySQL doesn't throw up an error either. I'm using MySQL 3.23.49-nt-log. Can anyone enlighten me please ?? Cheers Mr Russ Michell Web Applications Developer Itomic.com Email: [EMAIL PROTECTED] Tel: +61 (0)8 9321 3844 Fax: +61 (0)8 6210 1364 Post: PO Box 228, Innaloo, WA 6918, Australia Street: Suite 24, 158 William St, Perth, WA 6000, Australia "No proof of existence is not proof of non-existence." (Physicist: Stanton T. Friedman on Debunking Ufology) -- PHP Database Mailing List ( http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL guru needed!
Hiya folks: I wish to be able to select items from MySQL if a variable passed via the $_GET method to my PHP4 page is introduced thus: $sql = "SELECT TourName,TourID,$_GET[CatID] IN (TourCatID) FROM tour"; When I echo $sql to the page, the query looks fine but doesn't work, in as far as the wrong results are returned. MySQL doesn't throw up an error either. I'm using MySQL 3.23.49-nt-log. Can anyone enlighten me please ?? Cheers Mr Russ Michell Web Applications Developer Itomic.com Email: [EMAIL PROTECTED] Tel: +61 (0)8 9321 3844 Fax: +61 (0)8 6210 1364 Post: PO Box 228, Innaloo, WA 6918, Australia Street: Suite 24, 158 William St, Perth, WA 6000, Australia "No proof of existence is not proof of non-existence." (Physicist: Stanton T. Friedman on Debunking Ufology) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL guru needed: Is this query possible ?
I've been racking my brain for too long trying to figure out how to do this query, I don't think it should be too hard, but I just can't get it right. I've searched and read about every type of join I could find to figure out a way of doing this, but I think I'm either overlooking something simple or need a different table structure. I'm trying this in MySQL. I've got the following tables broken down for simplicity. procedure id name 1 abc 2 def provider id name 1 ghi 2 jkl procedure_equipment (each procedure may require more than one equipid) - id procedureid equipid - 1 1 1 2 1 2 3 2 1 provider_equipment (each provider may have more than one eqiupid - id providerid equipid - 1 1 1 2 1 2 3 2 1 What I am trying to do is get procedure.name from procedure where provider.id = say 1 and the equipid's from both the procedure_equipment and provider_equipment tables need to match. Basically I want to get the equipid's from provider_equipment where providerid = 1, then get the procedureid from procedure_equipment where the provider_equipment's equipid's equal that of the procedure_equipments equipid's (1 and 2). The problem I have is the fact that each equipid will be from their own row and I don't know how to equate provider_equipment.equipid 1 AND 2 and procedure_equipment.equpiid 1 AND 2. At first I tried the following query. Just to quickly realize that I would of course retrieve procedure.name for each equipid In the db. SELECT procedure.name from procedure, procedure_equipment, provider_equipment WHERE provider_equipment.providerid = '1' AND provider_equipment.equipid = procedure_equipment.equipid. Although I'd like to do this with one query, I've tried to think of a way to do it with a couple of queries and again found short comings doing it that way as well. I'm beginning to think that the way I have the tables set up won't allow me to query the way I want to and that I may need to change the table structure. Although it seems (to me anyway) that I should be able to get this query to work if I can figure out the correct way of running the query. I'm hoping someone can offer some incite on this, if a query like this is possible or not. I don't normally post to the list as I like to try to figure these things out on my own, but I'm really stumped on this one. I'm hoping there is a way of joining the tables, in a way I don't yet know of or understand, to get the results I want :) I'd really appreciate any help you can offer. Thanks in advance Max Sullivan