Buchead

Is there any way of achieving the required result in a single query then? Thanks.

Hello, Been given a database that I'm trying to extract data from 3 of the fields. The ones in question are: `bookings` - bookID, pitchID, gDate, gTime `pitches` - pitchID,name,location `pitch_tree` - pitchID, sectionID The result needed is dependant upon the search criteria entered. If the end-user only searches on a pitch name it's easy, however they could search on a section (there are a few, each one a different sport and on a given date. Each pitch has got 10 time slots throughout the day, so I need to return all the pitches that are in a certain section and either have no bookings or free slots on that date. If no bookings have been made then there will be no enteries in `booking`. Built up the following query: SELECT p.* FROM `pitches` AS p LEFT JOIN `bookings` AS b ON p.pitchID=b.pitchID LEFT JOIN `pitch_tree` AS t ON p.pitchID=t.pitchID WHERE (b.gDate != '2007-11-05' OR COUNT(b.gDate) = 0) AND t.sectionID='2' Clearly it doesn't work. Have tried grouping by gDate values and pitchID to no avail. Different joins and orders of the tables also has no effect. Can someone please point out where I've messed up. Thanks, Clive.

Thanks!

Hello all, I'm having some trouble obtaining a order a result based on a count. I have 2 tables - customers (id, name) and bookings (bookid, custID, date) - and want to order the output depending upon a request. The problem lies in that not all the customers may have placed a booking, yet they are still required to be shown with a count of 0. I can get the query working via phpmyadmin using the command: SELECT count(b.date) AS theCount, c.* FROM customers AS c LEFT JOIN booking AS b ON b.custID = c.id GROUP BY b.custID ORDER BY theCount ASC (or changing this to DESC as required). Yet when I put it into the php it doesn't work. Clearly I'm missing something obvious but can't see it myself. In php I've tried the following just in case (in fact tried swapping line around a lot) but it also (unsurprisingly) fails: SELECT c.* FROM customers AS c LEFT JOIN booking AS b ON b.custID = c.id GROUP BY b.custID ORDER BY COUNT(b.date) ASC Thanks for any pointers, Clive.

Yes, addslashes simply adds a "\" before the ' in the column entry. If by escaping the quotes you mean using \" then I've tried that. I've had the query so it reads: [code]SELECT * FROM `table` WHERE `name` LIKE \"".$search."\"[/code] with $search being the string. I've had the output looking like SELECT * FROM `table` WHERE `name` LIKE "%this \'n that%" SELECT * FROM `table` WHERE `name` LIKE "%this 'n that%" SELECT * FROM `table` WHERE `name` LIKE '%this \'n that%' None of which actually work. I suppose that removing the slash so it's not even written to the DB is one possibly solution, but it was also instructed to me to do that. Is this really necessary or will it not cause any problems by not having it there? Thanks very much.

This is probably something obvious but I can't figure it out...... Prior to writing to the DB, I use the addslashes function. All works fine. However, when a search is entered, if the search string also has a ' in it then it doesn't find the match in the DB. ie.  The DB has the entry:  this \'n that. if the user types in the search string of " this 'n that " then it doesn't return a match. I've tried using the addslashes on the search string before comparing to the DB but that makes no difference. Tried using wildcards without success. What am I doing wrong? Thanks. 

Not the live data, but something like this: `products` table: id      name 1       Socket set 2       Sledge hammer 3       hard hat 4       3" nails 5       flat-head screwdriver basically consists of items that can be ordered. 'id' being a unique, incremental number. `orders` table: orderid     productid 1                 2 2                 3 2                 4 3                 1    4                 2 4                 3 4                 5 5                 4 Got it slightly wrong before. The 'orderid' column relates to another table, but the 'productid' relates directly to an item in the `products` table. `cancelled` table is the same structure as `orders`. an 'orderid' relates to an order that had been cancelled, while 'productid' relates to the `products` table. What I want to achieve is an individual count of all the products from both tables. I can get a successful count by only doing a count on `orders` or `cancelled`, but what to know the total count for a product from `products` from both tables. The more enteries there are in both tables, the higher the count is. I've got around it by performing 2 individual counts and combining the results, however, hoped to achieve it directly from one query. Hopefully this makes sense.

Thanks, but how can I achieve the output required?  Other than performing 2 separate counts on the tables and combining the results outside the query. Or is that the best solution? Thanks.

Hello, I'm getting odd results from a count and wonder if someone could point out what I'm doing wrong.... Have 3 tables: Products - containing 'id' and 'product' Orders - containing 'orderid' and 'productid' Cancelled - also containing 'orderid' and 'productid' In both tables, orderid is a unique number, and productid relates to the 'products' table. I'm trying to get a count of products from both tables by using: [code]SELECT p.name,COUNT(o.productid)+COUNT(c.productid) AS total FROM `products` AS p LEFT JOIN `orders` AS o ON o.productid=p.id LEFT JOIN `cancelled` AS c ON c.productid=p.id GROUP BY p.id ORDER BY total DESC,p.name ASC[/code] If a product has 4 references in orders and 2 in cancelled it's coming back with a total of 16!  By only having one other table in the query it returns the correct value. What am I doing wrong? Thanks, Clive.

Cool. Thanks for that but 2 questions: 1) any idea what problems I could run into, and 2) what's a better way to store the IDs? I could have an undetermined number of IDs to store, and rather than create a large number of columns, of which many would remain empty, I thought it best to be stored in one column. Or is it the ';' that will cause the problems? Thanks.

Hello... This is more of a question to find out if a query is possible. In one table I have columns for ID (unique, incremental) and name. In another table I have another column for ID (unique, incremental) and one the called info. This contains various IDs from the first table in the format of ;1;20;23; etc..... What I'm attempting to do is pull the names from the first table where that ID is in the info column of the 2nd table, depending upon the ID of the 2nd table specified. I have the query: [code]SELECT a.name FROM `table1` AS a INNER JOIN `table2` AS b ON a.ID=b.info WHERE b.ID='$ID'[/code] The problem I'm experiencing is how to specify a LIKE in the a.ID-b.info part. If I wanted to simply select date from table2 I could use WHERE b.info LIKE '%$value%', but can't get wildcards to work around b.info (if that's possible at all). I can't really specify where any in table2 is like table1 ID as if searching on an ID of 1 it would give any result containing 1. Is what I want to achieve possible? Thanks.

That's perfect. Thanks very much.    :)

That almost works, except it gives the error: Error - 1054: Unknown column 'num_shows' in 'order clause' If I remove either num_show or num_fests from the ORDER BY section it works, except for separating out the venues for shows and fests into 2 parts. Thanks for the help so far though.

I've been trying with unions, but clearly am putting it in the wrong place within the query. I've tried: [code]SELECT COUNT(v.name) AS n,v.name FROM `venues` AS v INNER JOIN `shows` as s UNION SELECT COUNT(v.place) AS n,v.name FROM `venues` AS v INNER JOIN `fests` as s WHERE s.venue=v.vID GROUP BY v.name ORDER BY n DESC,name ASC[/code] and this gives the error: Error - 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause And clutching at straws I tried: [code]SELECT COUNT(v.name) AS n,v.name FROM `venues` AS v INNER JOIN `shows` as s UNION `fests` as s WHERE s.venue=v.vID GROUP BY v.name ORDER BY n DESC,name ASC[/code] but clearly that doesn't work as there's no real query behind the union. The columns in the two tables (shows and fests) match exactly in title. Suppose I could have simply combined all the data into one table but wanted to keep them separate for some reason. Any hints as to the correct direction to head are most appreciated!

Many thanks for that. Too simple I missed it! But would it be possible to throw another table into the mix?  So still with the VENUES and SHOWS tables, but adding the table FESTS that contains fID and VENUE. In the same way, fID is a unique number and VENUE corresponds to a vID held in VENUES. So somehow I need to combine SHOWS and FESTS into one. Thought I could use UNION but that just throws up errors. Could the best way to proceed be to run one query to create the list and pass that into the 2nd query?  I thought that maybe running an inner query could do the trick. Alas not. Perhaps I'm simply trying anything and getting too complicated. Thanks for any advice.

Hello, Hopefully someone can point me where I'm going wrong.... I have 2 tables: venues - containing 'vID' and 'name' shows - containing 'sID' and 'venue' vID and sID are unique numbers for each venue/show. venues 'name' is the name of the venue, and shows 'venue' is vID value. So: VENUES                                    SHOWS vID  name                                sID  venue 1      Fleece                              1      2 2      Academy                            2      1 3      Croft                                3      2                                               4      3                                               5      1 What I want to do is pull out the names and counts of the top 5 venues in alphabetical order. I can extract the data so that it orders on the count, but the name isn't in order. From the above I can get it listing as: Fleece      2 Academy  2 Croft        1 but Fleece and Academy should be the other way around. I'm using the command: [code]SELECT COUNT(v.name) AS n,v.name FROM venues AS v INNER JOIN shows AS s WHERE s.venue=v.vID GROUP BY v.name ORDER BY n DESC[/code] What am I missing? Thanks, Clive.

Fantastic, that worked perfectly. Thanks very much for the assistance.

At the moment there's only 1 level of sub-category, and there are no plans for any further levels. Hope this helps. Thanks.

That does the job (thanks), however I also need to do a check in the sections table for any parent/child relationships. products_tree lists all the products and which section they go in, while in sections, it lists all the sections, their sectionID and the sectionID of their parent. So if main = 1, sub1 = 2, sub2 = 3, and sub3 is beneath sub1 it will have a sectionID of 4 but with a parent of 2 - the others will have a parent of 1. Thanks!

Hello......... I've been presented with a query that works and have been asked to expand what it does. Unfortunately I know what it should do but not how to code it. The database has 4 tables: "products" which contains product info. "product_tree" which lists the productID from "products" and a sectionID. "advancedpricing" which contains info regarding pricing. "sections" which contain the sectionID and relationships between sections. The currect query is: select products.* ,advancedpricing.price1 as advPrice1 from products LEFT JOIN advancedpricing ON products.productID=advancedpricing.productID and advancedpricing.priceType=0 and (advancedpricing.accTypeID=0 or advancedpricing.accTypeID=1) ) where (products.productID > 1) and (accTypes like '%;1;%' or accTypes like '%;0;%') and (visible = 'Y') and visible = "Y" and ((code like "%$search%" or name like "%$search%" or description like "%$search%" or keywords like "%$search%")) group by products.productID order by name LIMIT 0,10 This works, but I've been asked to have it so it only searches for products in a given section, and it's sub-sections, so adding the products_tree table into the mix. I've tried adding "products_tree.sectionID=$section" after the where identifier, and also products_tree before and after the LEFT JOIN, but it fails. I've resorted to seemingly adding obviously incorrect code into the query in a bid to get it working! Can anyone point out where I'm going wrong? Many thanks, Clive.

Thank you very much.  Hopefully it'll all start making sense soon.......

Oops, jumped the gun a little. This command correctly selects the required records from products_tree but is it then possible to pull the corresponding records from the products table within the same command?  Those are the actual records than I'm after. Thanks.

Fantastic. That worked perfectly. Many thanks for all your help......and putting up with my incompetence!

Yes, it came close. That retrieved all the productIDs from products_tree that had multiple enteries, however it extracts all of them. For example, productID 2 may be in sectionIDs 1 and 3, while productIDs 3 and 4 are in 1 and 4. What I want to do is pull the productIDs that are only in sectionIDs 1 and 4. Thanks.

What I have is a table that contains product information. The productID corresponds to a field in products_tree table that also has a field for the sectionID the product can be displayed in. It's possible for a product to be in 2 sections, and what I want to do is pull out all the records from the products table that correspond to 2 given sectionIDs. I have written a long-winded - and not the best - way of doing this, but was wondering if there was an sql command that could pull all the records from the products table in one easy go. Hopefully this makes sense.