Help!!!!
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