MySQL Forums
Forum List  »  Newbie

Help!!!!
Posted by: Gabor Vojnar
Date: July 21, 2005 09:16AM

Hi,

Im studying PHP and MySQL so I'm pretty new to this so my question may not make sense but please help.
I have made a database with tables, queries and forms in MS Acces and I'm trying to convert it to PHP and MySQL. In access sometimes I was making queries after queries joining them together, sometimes queries with tables to get the results. The queries worked just as they were tables. And I belive in some cases I need to join query results with tables in MySQL statements but I do not see how that be possible. Lets say I have a table with numerous columns ie. article, SupplierID, X1, X2, Price etc.. and I want to find the min price for those instances where the same article's appearing more than once. If I do a group by on article and a min on Price than that will not give the right results because there are other columns which will effect the outcome.
So in Access I was doing a query with article and Price (group and min) and then a second query where I rejoined the results back with the original table to find the rest of the columns. Now in access I was able to make queries with queries or queries with tables just as a query was a table. Now in mysql all I get is lets say two array pairs which if I do a loop within a loop than I can eco out to an html page to be a table filled with data. But what if I need to use that result in a new query???? Can I implement arrays into an mySQL select statement somehow?? Or I have to write query results into tables all the time when I need to run other queries on the result query to be able to get the wanted results but imaginig that, that would make quite lot extra tables. (in Acces I had 200 tables and 200 queries about)
Yesterday I was able to work around on the on finding the min price using a function find on this website under "3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field"
------------
The first example did not worked:

SELECT article, Dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s1
WHERE s1.article = s2.article);
Maybe because I had the articles in 1 table instead of two. Or do I need mySQL 5.0??

--------
Second example just did not work giving error althoug it was copied and pasted, but I was expecting this to work because somehow I like the idea of using temp table, but maybe some extra settings needed to be done in order to make it work??The bottom of the exapmle it says: "If you don't use a TEMPORARY table, you must also lock the tmp table" which is not clear at all. No explonation found on msql.com nor php.net on temp tables. Example 2nd below :

CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);

LOCK TABLES shop READ;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;
------------------------------------
3rd example was working and I got the perfect results but I do not even know what the function is about:
SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;

But this example was only working if I had 3 coulms:article, price, dealer and it was not working if I had more columns.
Once againg the question is:
Is it possible to embed arrays directly to a mySQL select statement or query from PHP or I have to update the arrays to tables in order to run the query? Or maybe using temp tables or subqueries or how???

Thanks for your time!!
Best regards,
Gabor

Options: ReplyQuote


Subject
Written By
Posted
Help!!!!
July 21, 2005 09:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.