Page 2 - How to know which one is primary column

'Show' does not support 'where' clause. Run this query SHOW KEYS FROM tbl_name WHERE Key_name = 'PRIMARY' you will get MySql error.

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Key_name = 'PRIMARY'' at line 1 '

Posted On: Wednesday 24th of October 2012 08:59:56 PM Total Views:  221
View Complete with Replies

Related Messages:

Resolved Average page display from field query   (239 Views)
hope is having a nice holiday weekend column type is (double), storing a microtime value I am trying to get the average time from a field named "prctime" from a table TEST, but I keep getting errors. PHP Code: require"connect.php"; //IgetsuppliedargumentisnotavalidMySQLresultresourcewhenIrunthis $query=mysql_query("SELECTavg(prctime)FROM`TEST'"); $result=mysql_query($query); $row=mysql_fetch_array($result); echo$row[prctime]; echo$row[0];
Best way to save HTML page in MySQl   (239 Views)
What is the best way to save a HTML page in MySQL I just need the text not the images. I intend to save several 1,000 pages. Should I create a field or referance the pages which would be saved as html files to a directory. I'm not sure what the pros/cons of each method or if there are any other ways to do this.
Custom MySQL results printing in web page   (205 Views)
Hi all, If have a table with the following fields (and a few example records): Name Age Country -------------------- David 34 UK Julia 41 USA Timothy 26 UK Andrew 21 CAN Tina 36 CAN Fiona 28 UK Matthew 19 USA Lauren 21 UK Kate 45 USA Neil 39 USA Mike 42 CAN Now, I want to print out the contents of this table in a web page to list all users but the format I want is: UK -- David 34 Timothy 26 Fiona 28 Lauren 21 USA ---- Julia 41 Matthew 19 Kate 45 Neil 39 CAN --- Andrew 21 Tina 36 Mike 42 Of course I can ge tthe whole table and sort via the country field to get: David 34 UK Timothy 26 UK Fiona 28 UK Lauren 21 UK Julia 41 USA Matthew 19 USA Kate 45 USA Neil 39 USA Andrew 21 CAN Tina 36 CAN Mike 42 CAN But, I need the country as a heading then the users printed out as I showed earlier. Hope someone can help. I have a feeling this is quite easy but I'm stumped! David
Multiple pages to one..   (182 Views)
i have a litle question.. i have a catalogue database with a order-order details and products tables. now when an order is passt it goes true an xml flash page.. but i need to mode it so i want to drop the flash and only use php. so in the databse in the order table there is orderID and user info.then in orderDetails theres the orderID and a product id so what i did up to know is extract all orders so i get orderID name price.. but know i need details so i linked it to a OrderDetails page that gives me OrderId ProductId QTY.. so thats cool but know i need to get the product details from Product Table.. so Product id gives me a descrition of the products... ahhh is there a way to make it onelol! or at least to see the order page but when you click more details or print you get the product descritption... thx !!
MySQL data insertion and page need to be refresh   (186 Views)
Hi Guys, I am new here and find this forum very interesting and helpful. I need a help from you regarding page refresh. I got a auction script, and i want that everytime someone bid and data goes into the MYSql the page refresh shows the current bid, Like once the data updated in mysql through someone place a bid and page refreshes itself, where the bid field is present on the page. I hope you understand what i mean. Thx in advance Lezzo
How can i connect my asp page to mysql database   (169 Views)
hello, i am raghvendra khandal . i am making a asp file and want to connect with mysql database. i have created a system dsn called mysql_new with myodbc created successfully. my asp is on remote machine. and my database is on local machine where i created dsn. i have added a line in asp for connecting mysql using dsn "DSN=mysql_new;uid=root;pwd=raghu;" but it is giving error like this: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified /forMySQL.asp, line 11 i have also used connection string like "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;port=8080;DATABASE=mysql;uid=root;PWD=raghu;OPTION=3;" but it gives same error in browser could any body help me
Deriving a count for a field in a VIEW which requires a select statement on a table   (125 Views)
I have the following problem: table_A -------- prod_id prod_name table_B ------- prod_id colour price table_C ------- item_id part_no prod_no CREATE VIEW inventory AS SELECT A.prod_id, (SELECT sum(part_no) FROM table_c WHERE prod_no=A.prod_id) no_parts, A.prod_name, B.colour, B.price FROM table_A A, table_B B, table_C C WHERE A.prod_id = B.prod_id AND B.prod_id = C.prod_no GROUP BY A.prod_id Disregarding the fact that the above tables are not completely normalised, I know perfectly well the above query will fail as a SELECT statement within a SELECT statement is obviously invalid. How then do I proceed to create a view like above, where one or more fields need to be derived by using an expression i.e. SELECT statement, in order to obtain the necessary details Having investigated this problem, the only alternative formulation I have found though still invalid is: CREATE VIEW inventory AS SELECT A.prod_id, sum(C.part_no) no_parts, A.prod_name, B.colour, B.price FROM table_A A, table_B B, (SELECT sum(C.part_no) FROM table_c WHERE table_c.prod_no=A.prod_id) C WHERE A.prod_id = B.prod_id AND B.prod_id = C.prod_no GROUP BY A.prod_id Any ideas
Mysql, simple list from table over multiple pages   (182 Views)
Ok, will keep this simple. On my site i want to print out the list of entries in my database in Descending order latest entry first etc... and down the line till theres no more entries using the date field, that isn't a problem until i try to span this over multiple pages. ie flash1.php = print out the latest 3 entries (easy) flash2.php = print out the next 3 entries 4,5,6 (This is where im stuck) in flash1 i just use a while loop to step through my row array and print out 3 entries. in flash 2, i don't understand how you tell your php to print out the next 3 or however many u need to, using a simple loop from where u left off on the last page. This is the code im using on flash1 Code: $result = mysql_query("SELECT * FROM flashs ORDER BY date DESC") or die("Error! 3" . mysql_error()); $i=1; while($i
Need Help in getting only data which can suffix the total Qty   (124 Views)
Hi... I have a table: bom fields: Comp = P28 ReqQty = 31968 and I have table table: dipping fields needed to get data LOT_CODE OUTPUT_QTY DATE_ENTRY now I only want to get LOT_CODE and OUTPUT_QTY where can suffix the ReqQty ORDER BY DATE_ENTRY I tried this query: Code: SELECT SUBSTR(LOT_CODE, 9,4) AS Comp, LOT_CODE, OUTPUT_QTY, DATE_ENTRY FROM dipping WHERE SUBSTR(LOT_CODE, 9,4) = 'P28' ORDER BY DATE_ENTRY ASC; I attach the result of this query. I want only query or get is the LOT_CODE where the OUTPUT can suffix the ReqQty. Simple Example: ReqQty = 100 Comp = P28 LOT_CODE--OUTPUT_QTY---DATE_ENTRY 00001P28--- 50------------2012-05-15 00002P28----25------------2012-05-16 00003P28----50------------2012-05-17 00004P28----25-----------2012-05-18 in query I need to get only the 00001P28--- 50------------2012-05-15 00002P28----25------------2012-05-16 00003P28----50------------2012-05-17 because it suffix my ReqQty as you can see in this LOT_CODE 00003P28----50------------2012-05-17 i only need is 25 to suffix my Req Qty so I need to display only: 00001P28--- 50------------2012-05-15 00002P28----25------------2012-05-16 00003P28----25------------2012-05-17 Feel free to ask me if theirs any question for better understanding of my problem Thank you
all the records which has no relation with the key   (180 Views)
Code: query1 select t from tag result1 1 2 3 4 5 7 9 The query1 above produces the result1 above. The query2 below produces the result2 below. Code: query2 select t1,t2 from relate result2 (t1) (t2) (1) (2) (4) (5) (7) (4) I like to produce like the following. when key is (1), it produces all the records which has no relation with the key (1) in relate table. It will be (3),(4),(5),(7),(9). when key is (2), it produces all the records which have no relation with the key (2) in relate table. It will be (3),(4),(5),(7),(9). when key is (3), it produces all the records which has no relation with the key (3) in relate table. It will be (1),(2),(4),(5),(7),(9). when key is (4), it produces all the records which has no relation with the key (4) in relate table. It will be (1),(2),(3),(9). when key is (5), it produces all the records which has no relation with the key (5) in relate table. It will be (1),(2),(3),(7),(9). when key is (7), it produces all the records which has no relation with the key (7) in relate table. It will be (1),(2),(3),(5),(9). when key is (9), it produces all the records which has no relation with the key (9) in relate table. It will be (1),(2),(3),(5),(6),(7).
How to handle references to multiple tables which exclude one another?   (190 Views)
I am designing a product catalog of sorts. For now there will be three product types, and it is unlikely that there will significantly more different types of products. Because these products share very few properties, I thought it would be best to use a different table for each product. I'm too lazy to type the actual details of these products (sorry), but one can think of them as different sized boxes whose types are named A, B and C, and A is the biggest and C the smallest So let's talk about boxes. These boxes have specific relations: 1. only a box of type A can be by itself, other boxes must be inside a bigger box 2. ergo, a B must always be inside an A 3. a C can be inside an A or a B 4. but an A can immediately contain only either a B or a C, so if it contains both, the C must be inside a B 4. a single A can contain multiple Bs, and a single B can contain multiple Cs 5. but there can be only one C immediately inside an A So at the moment I have these tables (database is MySQL): Code: Product_A - id - properties... Product_B - id - product_a_id - properties... Product_C - id - product_a_id - product_b_id - properties... This "works". It's simple and easy to handle. I can maintain integrity outside database. I can make sure that Product_C.product_a_id is NULL (or zero) if Product_C.product_b_id is not, and vice versa. But I don't really like it. It seems clumsy, amateurish and I would really like the database to take care of data integrity. I thought about creating just one Products table and marking relationships with 'parent_id' and separating the properties into either several tables (one for each product) or just one big table (with references to Property_types table, or something like that), but it seems like overkill and would unnecessarily complicate the queries. (Or at least the way I thought I'd do it would.) There would also still be problems with data integrity. I could easily have an A contain Bs and Cs side to side. in all, to me it seems like even worse a solution. So I'm asking for some pointers here. I think I'm happy with having the three tables, but not the way Product_C table stands at the moment. I just don't have any idea how to make it better. To me it seems like I'd need to have one foreign key pointing to multiple tables (this is a recurring problem in this database, actually), and that smells like a design error.
Speed of page loading. Limitations of the COUNT() / GROUP BY?   (164 Views)
On my home page I would like to display 5 drop down lists containing 100-400 geographical locations. Each location will have a set number of attributes which I would like to display in brackets next to each location in the drop down list. Because I am new to mysql and php my first thought was have a field in my database table on the same row as the locations that would represent the number of attributes (which i expect to grow over time). This number would be updated after delete or insert queries. I imagined that this method would be the quickest way to achieve the deed outcome as far as page loading is concerned. However, after reading kevin yanks chapter on advanced sql queries I am now aware that, in following this method, I would need to have tables locking whenever an insert or delete query is executed (in order to maintain synchronisation). This point made me question the speed advantage I anticipated with my primary solution. Kevin yank explains how to use the COUNT() / LEFT JOIN and GROUP BY clauses to avoid this method without the unacceptable slowness that would result from using the COUNT() query for every entry in the table. My concern however is that by incorporating a query where counting is involved on such a large scale could result in a very tediously slow loading homepage. Could anyone with experience of large databases advise me on the relative pros and cons of either method please
Joining tables which aren't directly related   (105 Views)
I'm struggling to get my head round a SQL query and wonder if you might be able to help (I'm using MySQL version 4.1.20-1). I have three tables: accounts, items and invoices. Code: CREATE TABLE accounts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (id) ) ENGINE = InnoDB; CREATE TABLE items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, accounts_id INT UNSIGNED, ... PRIMARY KEY (id), FOREIGN KEY (accounts_id) REFERENCES accounts (id) ON DELETE SET NULL ) ENGINE = InnoDB; CREATE TABLE invoices ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, items_id INT UNSIGNED, ... PRIMARY KEY (id), FOREIGN KEY (items_id) REFERENCES items (id) ON DELETE CASCADE ) ENGINE = InnoDB; Each account can have multiple items and each item can have multiple invoices. Hope you're with me so far So here's the question. If I have an account ID, how do I get a list of invoice IDs for that account when accounts and invoices aren't directly related
Search Rows from a table which has two words   (213 Views)
to find two words in a row of table when the first word must precede the second word by a specified number of words. (NOTE: PRE represents precedes and n represents the number of words from 1 to 255.) EXAMPLE: behavioral PRE/3 disturbances would find articles in which behavioral precedes disturbances by three or fewer words. Kindly reply me ASAP.
Order by field, which lists duplicate value?   (162 Views)
The following sql returns the 'tid' and 'pos' for only 4 out of the 5 passed in words - it misses out the duplicate OR between password and about: email OR password about What I actually want is it to return all 5 words in the same order as given to the sort by field function, so... email OR password OR about Does anyone know how you write this properly so that ALL 5 words are returned with their 'tid' and 'pos' Including the duplicate OR word which seems to get ignored
Page 2 - How to query about all entries which occur less than n times   (138 Views)
Quote: Originally Posted by cafelatte We're not concerned with words that don't appear in the table.... nice backpedal , To understand why my query works, it might help to rewrite it without the aggregation: Code: SELECT * FROM tablename t1 JOIN tablename t2 ON t1.word = t2.word ORDER BY; I can't comment on the clumsiness or otherwise of my query vs. r937's - I don't know enough about it (although, it could be argued that a query that works on older versions of MySQL seems more versatile). I doubt that either solution scales well - although I suspect that on a large dataset, even with appropriate indexes, my query will become agonisingly slow. Rewritten with a LEFT JOIN, I think it will be palpably slower still. I'm gonna guess that r937's solution slots somewhere in between the two - but I could be way off.
Group by, determine which record to show   (150 Views)
I have the following table with data Code: Answer Id | User Id | Answer | Date_Created 75 1 earliest 2009-01-01 75 1 middle 2009-01-02 75 1 latest 2009-01-03 76 1 earliest 2009-01-01 76 1 middle 2009-01-02 76 1 latest 2009-01-03 76 2 earliest 2009-01-02 76 2 middle 2009-01-03 76 2 latest 2009-01-04 75 2 earliest 2009-01-02 75 2 middle 2009-01-03 75 2 latest 2009-01-04 I'm trying to pull the last answer (determined by the date) each user has on record for each question. So my ideal data for this data set would be Code: 75 1 Latest 2009-01-03 75 2 Latest 2009-01-04 76 1 latest 2009-01-03 76 2 latest 2009-01-04 I tried to do it with this sql (note I'm using IN as this will be dynamic eventuall): Code: SELECT * FROM `test` WHERE question_id IN ( 75, 76 ) AND user_id IN ( 1,2 ) GROUP BY question_id, user_id ORDER BY date_created DESC But instead it comes up with earliest not the latest. Is there a way to force the GROUP BY to choose the latest TIA, Mark And as always table and data to play with. Code: CREATE TABLE `test` ( `question_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `answer` text NOT NULL, `date_created` date NOT NULL ) -- -- Dumping data for table `test` -- INSERT INTO `test` (`question_id`, `user_id`, `answer`, `date_created`) VALUES (75, 1, 'earliest', '2009-01-01'), (75, 1, 'middle', '2009-01-02'), (75, 1, 'latest', '2009-01-03'), (76, 1, 'earliest', '2009-01-01'), (76, 1, 'middle', '2009-01-02'), (76, 1, 'latest', '2009-01-03'), (76, 2, 'earliest', '2009-01-02'), (76, 2, 'middle', '2009-01-03'), (76, 2, 'latest', '2009-01-04'), (75, 2, 'earliest', '2009-01-02'), (75, 2, 'middle', '2009-01-03'), (75, 2, 'latest', '2009-01-04');
Query For Functionality: Users who viewed this page also viewed this page   (212 Views)
, I'm trying to figure out the best way to do a "Users who viewed this page, also viewed this page" based off data from a mysql DB. I should be able to make this work with just the PageID and UserID which are both columns in the same table. So far I was thinking I could query all the users who viewed PageID 15. Lets say that returns UserID's 1,2,4,6. I Could then run a separate query and do the totals in PHP. So if user 1 viewed page 3,4,5 and user 2 viewed 3,7,8. Then so far the results should report back that page 3 is the most relevant page so far to return. Obviously VERY inefficient. But I hope this helps to explain what I'm trying to do. If anyone could get me started on what command or type of join to use for this then I could probably do the rest. Maybe I'm over complicating thing.
Displaying username in in user page   (150 Views)
Hi all, I've labelled my textfield name as "u_username" and after filling up the text and password fields, it will passthrough an authentication process, User.php: // here i put the username into a variable $username = $_POST['u_username']; > in the index.php, i've stated the code but i tried out many times it just can't display the username. Is there something i miss out or i've done any mistakes Seeking guidance from all of you out there
Show a record one time which is more than one   (122 Views)
i can show all user like following PHP Code: SELECTuserFROMtable; but sometimes duplicate object can be in record like following Code: x y z zodehala zodehala a b c d i want to show just one time all duplicate in record like this Code: x y z zodehala a b c d how can i do using query