Product Details
I am currently developing an e-commerce website. Each product comes in many colours and sizes. I have therefore created the following tables:
1. product (contains basic information about each product)
2. colour (contains a list of all possible colours)
3. size (contains a list of all possible sizes)
4. product_colour (contains matching product and colours ids)
5. product_size (contains matiching product and size ids)
I now need to create a query which returns the basic information, the available colours, and the available sizes for a particular product id.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Getting All Details For Minimum Value
I'm sure this can't be that hard, but suppose I have tables for category and product, and a link table, and I want to get the minimum priced product for each category: select c.categoryID,c.categoryName, MIN(price) as min_price FROM cat c JOIN category_product cp ON c.categoryID=cp.categoryID JOIN product p ON cp.productID=p.productID GROUP BY c.categoryID That's fine, but how about if I wanted to get further information about that minimum priced product? I've been doing what looks like awkward things with subqueries, like this: SELECT cat.*,(SELECT productName FROM product WHERE product.price=cat.min_price LIMIT 1) as prodname FROM( select c.categoryID,c.categoryName, MIN(price) as min_price FROM cat c JOIN category_product cp ON c.categoryID=cp.categoryID JOIN product p ON cp.productID=p.productID GROUP BY c.categoryID ) as cat or this : SELECT cat.*,p.productID, p.productName FROM( select c.categoryID,c.categoryName, MIN(price) as min_price FROM cat c JOIN category_product cp ON c.categoryID=cp.categoryID JOIN product p ON cp.productID=p.productID GROUP BY c.categoryID ) as cat LEFT JOIN product p ON cat.min_price=p.price GROUP BY cat.categoryID
How To Add Several Details To One Field In Table?
I have been adding my info into my database so far like this: INSERT INTO mytable SET Name = "Harry Bucket", Products = "chair", Suburb = "sydney", ResLink = "harrys_page.php"; BUT, harry has more then just a chair under his product listing... how would i add several products that relate to the 1 field?
Get Last Records Details With A Subselect?
I have a ticketsystem where each ticket belongs to an user and each user can insert a couple of messages to one ticket. Therefore I have implemented a date field (used as primary key). Now I want to get details from the last entry belongs to a ticketid. kdn_message: updated (date) ticketid (int) kdnr (int) detail state select * from kdn_message t where updated in (select max(updated) as updated from kdn_message group by ticketid where ticketid=t.ticketid order by updated desc) what is wrong i this statement?
Entering Session Details
I want to get the session details (username) of a person loged in. And want that details to be entered to a separate table in he same data base. how can i do that. A coding for that would e perfect.
Concrete Data & Details
I'm reading available documentations and whitepapers, but can't reach specific answers to my questions: 1. What is the minimum requirements (cpu,memeory..) for mySql. 2. What is the advatanges in using the embedded version over the regular? And I'm looking for specific data, not 'global statements', like - how small is the embedded version's footprint?
Sharing Login Details
There is no decent image gallery that can be put with Joomla or Mambo CMS so I am wanting to add a separate pop up page to use a 4images (http://4homepages.de) image gallery. HOWEVER, I don't want my users to log in twice to two different databases! I'm sure there is a way of doing this. (both the CMS and the Gallery are on the same server, and they can both use the same Database.
Sharing Login Details
There is no decent image gallery that can be put with Joomla or Mambo CMS so I am wanting to add a separate pop up page to use a 4images (http://4homepages.de) image gallery. I don't want my users to log in twice to two different databases! I'm sure there is a way of doing this. (both the CMS and the Gallery are on the same server, and they can both use the same Database. )
Deciphering Connection Details From PhpMyAdmin
I have no details as to how to connect to my database although I am able to create one through phpmyadmin. While I twiddle my thumbs waiting for my host to send me some connection details is it possible to maybe work out what they are? I'd guess my username and password are ones I chose and are the same for the whole kaboodle so it's the address of the db I'm looking for, any clues?
How To Save People's Details Using MySQL?
Basically i want people to enter in there name and contact details and an answer to a simple question. I then want that information to be stored somewhere, so i can retrieve it once a month. Do i use Microsoft access?
Finding User Details Inside UDF
i heared that its possible to get user details inside the UDf by adding the mysql_priv.h header file. But where will this file be available. i am using MySql 3.23.52 on linux8.0 when i try to compile it says :"mysql_priv" file not found. If i can include this file, i can get the thread constructor called...
Can MySQL Return Details On What Specifically Trigged A Match?
Can MySQL return details on what specifically matched in each row for a FULLTEXT search or WHERE clause? Say the query "SELECT id FROM users WHERE (name LIKE '%Sarah%') AND (age BETWEEN 19 AND 29)" returns 12 rows. Is there a way for mysql to tell me whether name or age triggered the match?
Product Categories. The Right Way?
I am creating a database that will handle a lot of different products in many different categories. I have a "PRODUCTS" table which holds many different columns and "CATEGORIES" table which holds product category information (ex: TV, TV->Plasma, etc) My products table looks something like (not complete): p_idINTEGER AUTO_INCREMENT, p_nameVARCHAR(225), p_categoryINTEGER, p_statusVARCHAR(155), My categories table looks something like: cat_idINTEGER AUTO_INCREMENT, cat_nameVARCHAR(225), cat_parentINTEGER, The p_category on the products table holds the cat_id of the category, so for example "Digital Cameras" would be cat_id=25, but would be a child category of say "Cameras" which is cat_id=24. However, the above approach is a bit tricky when it comes to creating lists of current categories and their children (in perl) and also big problems arise when a category is deleted because you'd have to check which children the category had and delete/change the children. what I want to know, from anyone who has done this in the past, is what is the best way of storing product categories which may need to be changed or removed at times without a lot of work. What is the "by the book" way. This needs to be fairly dynamic and flexible.
Product Of A Field
It *sounds* like a really easy thing that *should* be available as a standard feature. Basically, I'm looking to do something that works exactly like SUM(), except that it returns the product instead of the sum. I'd imagine that there's some common workaround for this, but unfortunately googling for `product' with `MySQL' only returns oodles of results for `product-as-in-something-you-sell', rather than `product-as-in-the-multiplication-of-numbers'.
Strange Cartesian Product
I have a strange cartesian product error. I am doing a query from a db I did not create. I can see the tables in it and have a search query that searches in 2 tables. If I search for "name LIKE ó“%' OR model LIKE '%670%' " I get the cartesian product error. However, if I search for "name LIKE ó“%' " by itself I get an empty query set, 0 rows returned. And model LIKE '%670%' pdoduces an expected query of 7 rows. This makes absolutely no sense to me. If it does not match anything in the name column, how the heck is it creating a cartesian product result??? Furthermore, if I change the query to something that is contained in the name column, then I don't get the error. This is driving me nuts. To compund the matter further there is a third column involved which is doing the same thing. But I am currently testing this with just the two columns to narrow things down a bit.
Complex Search Or Product Name
I am writing search for a shop. The user enter a word or words into a input box and then the presses the search button. I then want it to search the product list and return the products. I want the search to search for both words then the first then the second. Like this. $query = “SELECT * FROM products WHERE (product_name LIKE ‘$first_word’ AND product_name LIKE ‘$second_word’) OR (product_name LIKE ‘$first_word’ AND product_name NOT LIKE ‘$second_word’) OR (product_name NOT LIKE ‘$first_word’ AND product_name LIKE ‘$second_word’) Then print it out in that order. I have the query sorted but its not pulling the data off in any particular order.
Packing MySQL 4.0.13 Along With Our Product...
We are building Applications Manager product which monitors servers (Application servers,web server,..), DB servers, Port monitoring, URL monitoring and much more... For this at the back-end we are planning to use MySQL DB with versions greater than 4.0.13. We don't want to force the users to install MySQL 4.0.13 for trying / running our product, also we don't want users to uninstall older versions (lesser than 4.0.13) of MySQL already installed in their machines. For windows we are packing MySQL (all the data dir, binaries) along with our product. We like to do the same for linux version also. Even if the older version is running, we will start MySQL 4.0.13 (bundling with our product) in a different PORT, so in no case it doesn't disturbs or interrupts the version that is already installed/running in their machines. Has anyone tried similar to this by packing MySQL, if so kindly help us in resolving any discripencies you faced.
Intelligent Product Search
I have a site with a bunch of products. Searchable fields are in various tables. Ie, main table is say product, which has 1 text field that's searchable. There's also a product_extra table with another text field. There's a 1 to many relationship between those two. When doing a search, I'd like it to poll all that data, and then return results in some intelligent order based on a match ratio. I would like words like 'the' 'a' etc, basically all articles, ignored. I have a feeling using fulltext columns, and the match function are what I need, but wondering if others would agree. Would I basically then have to create a new field in the main product table full of keywords based on all the pollable fields across all associated tables? And create the fulltext field based on that? Is there an easy way to strip out words like 'the' and such? Or am I left to my own devices there?
Store Product Description
what should be the best datatype to store the (details) description of the product in retail website. it should be stored in seprate file and filename linked should be given in the prod_description field having varchar datatype or description should be stored inseide the database in prod_description field having blob datat type.
A Partial Cartesian Product
I have a rather streight forward SELECT statement. SELECT p.propid, p.email, p.Dona, p.donationAmt, p.DonNote, p.numOfClasses, p.eLastDay, p.propDate, p.VenuName, p.instruc, CONCAT(p.POC_First, ' ',p.POC_Last) as cName, p.POC_Email, p.eventStage, p.eventStatus, p.OtherExp, p.venuInvReq, p.Notes, p.PS2Host, p.startDate, v.attendance FROM proposal p LEFT JOIN VenuName v ON p.VenuName = v.VenuName ORDER BY p.propDate DESC; The result set looks good until about the third row, which repeats about twenty times, and then resumes with the rest of the expected result set.
Select Product Released In Range..
I have a product table (I've posted recently, but I will post basic structure again) which has a ReleaseDate (datetime), and I would need to select list of products coming up in release date order from 2 weeks prior to todays date up to 3 months in advance. For example today it would display releases that have been released 2 weeks ago and all releases up to the 3 months in advance. Ideally I would be able to show results in branches. For example Week Commencing 15th May 2006 Release A Release B Release C Week Commencing 22nd May 2006 Release A Release B Release C and so on. My problem is how to determine this week Monday and select from 2 previous Mondays and 3 months upfront. This is the table CREATE TABLE mns_product ( ProductID INTEGER UNSIGNED NOT NULL, SupplierID INTEGER UNSIGNED NOT NULL, ArtistID INTEGER UNSIGNED NOT NULL, FormatID INTEGER UNSIGNED NOT NULL, Type TINYINT UNSIGNED NULL, Title VARCHAR(255) NULL, Description TEXT NULL, ReleaseDate DATETIME NULL, PRIMARY KEY(ProductID), INDEX Product_FKIndex1(FormatID), INDEX mns_product_FKIndex2(ArtistID) )TYPE=InnoDB; Query will join 4 more tables but that is not the problem, main problem is WHERE part for this times.
Ordering By The Product Of A Left Join
I have a query which performs exceptionally poorly. I've isolated the problem as being the ORDER BY clause... SELECT table_a.item_id, IFNULL(table_b.priority, 0) AS priority FROM table_a LEFT JOIN table_b ON table_a.item_id=table_b.item_id WHERE <<..various conditions...>> ORDER BY priority DESC ('priority' is a signed integer, and probably 1% of rows in table_a have a matching row in table_b, in the other 99% of cases I default it to 0 using that IFNULL). So you see, what I'm doing there is ordering by a column that, for the most part, doesn't really exist, making indexing kind of tricky. And so it's very, very slow to order by that 'virtual' column.
Error - Problem In Product Selection:
I had just updated my website, however when I click on any subcategory on the Index.php page I get the following error message: Problem in Product Selection: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay. I have reviewed individual .php files and cannot find anything that could link me to this error message, I also looked through the MYSQL and could not find anything. I have been adding new products to the site but can see it except if it is though the main index page. I was told by my hosting company to do the following, I have minimal knowledge of the MYSQL. The Developer that worked on my site, did a crappy job and I am just fixing so far all his bad work. ***************************************************** Regarding the large MySQL query you are creating, please use an ON or USING clause to each table join so that you limit or decrease the amount of permutations the MySQL database needs to examine (thus decreasing the SELECT size). Please be advised that the code you are using should have this in front of the main query. mysql_query("set sql_big_selects=1");
Table Structure For A Product Database
I'm working on a website for my business and have a few questions about how best to design the MySQL database. First of all we have products (art prints) that have lots of options, and the user has to be able to select which one he wants for the particular print. So we'll have one basic art print but it can have up to 21 permutations (example: blue mat gold frame, red mat gold frame, green mat gold frame, etc.). The other thing is that each item may have different quantities and different values of permutations. Basically, some of the items would have the same permutations but other wouldn’t follow any pattern at all. We have less than 150 prints but with the different permutations we have somewhere around 2500 different items. I would like to have just the prints be items instead of having 2500 rows in the product’s table. My question is: how would I best store these values (permutations) in a table so they could relate back to the products table? I would like to have a page where I could select an item, and be able to have it load, from the database, the options for that print in a select box. So I would have one php script that could view any item.
DB Design Question (Do I Need Different Tables For Different Product Types?)
I'm trying to set up a database of products and I'm at the planning stage at the moment. I'm completely new to this so please bare with me. Currently I have one table called products: This contains fields like price, description, image etc. Then I have another table called brands: This contains brand_name, brand_logo etc the products are linked through a foreign key to the appropriate brand. The problem I have is that some products have different storage requirements depending on their type. For example, each product of type 1 has a number of sizes and a different product image for each size. Each product of type 2 has a couple of pieces of info that the other types don't have (A flex rating and a performance rating) and product type 3 has a list of bullets that the others don't have. My question is how do I deal with this type of scenario within the database design? Do I need another field in the products table which distinguishes the product types and acts as a foreign key to other tables which contain the extra info? Or should this all be done within PHP? i.e Have the various info in separate tables and the have product classes which know where to select the info from based on their class type? A bit of help would be appreciated because I'm a bit out of my depth. (Another question: How do you guys store lists of info like a features list? Do you store it it with the associated HTML i.e <li>Item 1</li><li>Item 2</li> or is this classed as bad form?)
Product Price Selection Through Mysql Query
I have following data in table named "productprice" in mysql. id minqty maxqty price 1 10 20 10.00 $ 2 21 30 19.00 $ 3 31 40 28.00 $ My question is that what will be the query to find the price Case #1 : If quantity is greater than 30 [maxqty] then it should select record # 3. Case #2 : If quantity is less than 10 [minqty] then it should select record # 1 .
Process/Product To Display Contents Of Database Via PHP
I am designing a MYSQL database. I am looking for a simple process or product to display the contents of the database via PHP. To be specific: It will contain a list of articles and their URLs. I want to be able to select by the subject, then all articles by that subject will appear (perhaps 20 at a time). There should be a "NEXT" and a "PREVIOUS", and if you should click on the article (i.e. its hyperlink), you will go to that article. Any thoughts about this?
Calculating The PRODUCT Of A Field Using The GROUP BY Clause...?
Is it possible to calculate the PRODUCT of field values as they are combined using a GROUP BY clause? For example: MyID MyValue 1 2 1 3 1 3 2 2 2 3 2 1 SELECT MyID, PRODUCT(MyValue) AS MyValue2 FROM MyTable GROUP BY MyID; To give MyID MyValue2 1 18 2 6 So far I can only do it by taking the SUM of the logarithms i.e. LN(MyValue), then exponentiating these back out. I'd much rather do it in one step i.e. the select statement rather than add a couple of new steps of additional calculations.
|
TRACKER
