peterjc

Ok Barand. By the way, after i got the value and processed in php, NOw, i need to delete or remove all those records. I am using the sql like below, it is a bit slow, any suggestion to improve it? DELETE FROM table WHERE idtable IN ( SELECT * FROM ( SELECT idtable FROM table AS t INNER JOIN ( SELECT DISTINCT filename FROM table WHERE takendate <= '2012-05-03' ) AS X USING ( filename ) ) AS p ) Thanks in advance.

No freezing. Forgot the execution time, but i think less than 1 sec. By the way, regarding the sql SELECT * FROM table WHERE number IN (SELECT number FROM table WHERE date <= '2012-05-03') I think this sql is correct also right? why it is so slow? Is it because of using the "IN" clause that cause mysql to compare with too many records? Thanks.

Tried, and it work. but could i know why need to use UNION? because i tried without union, it will return the same result. SELECT t.id, t.date, t.number FROM tablename as t INNER JOIN ( SELECT DISTINCT number FROM tablename WHERE date <= '2012-05-03' ) as X USING (number) Thanks.

Tried and it will freeze my PC! If without the subquery, it's fast, but if add in the sub-query, it will freeze my PC There are around 100K of records. Just for info only, there are indexing for the table Anyway to solve this? Thanks.

Hi Here is example:of my question. id-----date------ number 1 2012-05-01 100 2 2012-05-01 101 3 2012-05-01 102 4 2012-05-02 103 5 2012-05-03 104 6 2012-05-04 105 7 2012-05-04 101 8 2012-05-04 102 9 2012-05-05 106 First, I want to select all records <= 2012-05-03 So, the result will look like below: id------date------ number 1 2012-05-01 100 2 2012-05-01 101 3 2012-05-01 102 4 2012-05-02 103 5 2012-05-03 104 But, now, i also want to include the records below. Because the number 101 and 102 also exist in the query above. id------date------ number 7 2012-05-04 101 8 2012-05-04 102 So, The final result that i want is: id------date------ number 1 2012-05-01 100 2 2012-05-01 101 3 2012-05-01 102 4 2012-05-02 103 5 2012-05-03 104 7 2012-05-04 101 8 2012-05-04 102 Could anyone please help how to write the sql query? There are about 100 thousand records in the database table. Hope you guys understand what i means, thanks in advance.

Thanks for that jazzman1. By the way, i don't really know how the "or" statement work. Example: We usually use: mysql_query($sql) or trigger_error() and so on could we actually use it for our own function as well? Example own_function() or trigger_error(); Thanks

Usually we would use mysql_query($sql) or trigger_error(mysql_error()); So instead of using trigger_error() directly, could i use our own function like below? mysql_query($sql) or showError(); function showError() { trigger_error(mysql_error()); die(); } The purpose is i want to end the script if errors were found. Thanks in advance.

Hi, Example from mysql.com SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id; SELECT * FROM table1 LEFT JOIN table2 ON table2.id= table1.id LEFT JOIN table3 ON table2.id=table3.id; So is it the same to write table1.id=table2.id or table2.id= table1.id Another example: SELECT * FROM table1 t1, table2 t2 WHERE t1.idtable1=t2.idtable1 SELECT * FROM table1 t1, table2 t2 WHERE t2.idtable1=t1.idtable1 Thanks

Thank for the reply. Eventhough i won't use the above techniques, but this look interesting, will do more searching about it. And if anyone know which site does provide good information about the techniques, hope you could provide the link to the site Thank

I would like to know how do you guy handle the database table primary key when it reach the maximum limit? Example, a program need to insert a lot of records, but after insert, may delete it after a few days, so the primary key field will increase very fast, and of course after a few months or years, will reach the maximum limit right. So how do you guy handle it in this kind of case? Thank.

Thank for the help. Problem solved.

thankl for your reply, fenway Could you show me an example on how to split the first 3 character and sort it? I am not really good at advance SQL. thank.

I have a column to store the following serial no. Serial No. aa/0001 bb/0005 cc/0002 dd/0004 ee/0003 Could someone help me how to sort it by ignoring the aphabet and slash infront (Example: ignore the : "aa/" while sorting) Example the returned result will be: aa/0001 cc/0002 ee/0003 dd/0004 bb/0005 Thank in advance

Thank for your reply. But the problem is, if i set the primary key that get from the database to the id property, after send the form, in php, i cannot identify which text box value belong to which name. Example: If all text box with name 'num' only, after send, the query string will be num=12&num=8&num=15&num=5 Is there any way to get the text box value even the text box name property with the primary key? Any idea? Thank.

Some javascript help needed. The input textbox like example below were generated using php. The 1, 3,6,8 inside the num[] textbox is actually the dabase table primary key id. the problem is i need a way to calculate the total number for all of the textbox. Example: 12 + 8 +15 +5 = 40. I does not know how to get all of the textbox value because it is in array. Could someone please help me how to get the textbox value using javasript? Example: Name A <input name="num[1]" type="text" id="num[]" value="12" /> Name B <input name="num[3]" type="text" id="num[]" value="8" /> Name C <input name="num[6]" type="text" id="num[]" value="15" /> Name D <input name="num[8]" type="text" id="num[]" value="5" /> Thank in advance