Query To Display The Rows As Colums Is Not Working!!
I want a select query to get two columns. but i need to get these columns one below the other that is consider that i have a table student in that i have 2 columns name mark.
Now i want the result as
Name Raj Rina Tina
Marks 80 90 70
I tried the --vertical option of mysql and G option in the query.
for example when i tried
select *from studentsG
it displayed
*********** 1. row ***********
name: Raj
marks : 80
********** 2. row ************
name: Rina
marks : 90
********** 3. row ************
name: Tina
marks : 70
but i want to display
Name Raj Rina Tina
Marks 80 90 70
View Complete Forum Thread with Replies
Related Forum Messages:
How Would I Merge 2 Colums In A Query
I have 2 colums in a database that I want to merge while calling it. Is this possible? One column type is Integer The other type is Float If column A has a value, column B will be null, and vice versa. Code: Column A | Column B | 1| NULL | NULL| 0.34 | etc.| etc. thanks in advance
View Replies !
Shuffle And Display Top X Rows
Using MySQL 4.1.10 with Coldfusion MX 6.1 on top. A table of data gets regular inserts of hundreds of rows at a time in the same category. A categoryID field (foreign key) is assigned to each row. So the table consists of sequential blocks of data in each category--200 to 1,000 rows of category1 then 200 to 1,000 rows of category2, then some more category 1, and then some category3, etc. I want to extract a small (and most recent) subset of the data using a select query in a way that shuffles and returns the top rows for each category like this: Row1 Category1 Row1 Category2 Row1 Category3 Row2 Category1 Row2 Category2 Row2 Category3 Row3 Category1 Row3 Category2 Row3 Category3 Row4 Category1 This example returns 10 rows. I'd like that quantity to be dynamic so I can send the query a variable from CF. The quantity of categories is known (although does grow infrequently) so I can plug that in if necessary but I'm trying to get that quantity on the fly. That, of course, takes a simple count(*) function of the category table. I just can't figure out how to incorporate that with the rest of what is needed. Am I the only one working this weekend? I think I'm close but I have a maze of nested loops and other CF goofiness and I'm still not there. Hoping to trash all that and find a purer database solution, if possible.
View Replies !
COUNT And GROUP BY Display All Counted Rows
have a table as follows ID | ORG | Subject ----------------------- 100 | 14 | Some text 101 | 18 | Text1 102 | 18 | Text3 103 | 14 | Text4 104 | 18 | More text What query would I need to get the following output? (when I use COUNT(ID) and GROUP BY ORG it only returns 1 ID per ORG) ORG 14 - 2 results 100 103 ORG 18 - 3 results 101 102 104
View Replies !
Specify A Value For At Least One Of The Colums
i have created a database for a form to hold peoples details who come to my marquee site. i have done most of the entrys as varchar and set null to not null and dont know what to put int he default so left it blank! i have connected to teh db in dreamweaver thts all fine but when i try to insert record i get please specify a value for at least one column.
View Replies !
Add New Colums To Existing View
Is it possible to add new column to existing view? For example: let us have three tables: table1 with columns(c1,c2 ,c3 ) table2 with columns (cc1,cc2, cc3) table3 with columns(ccc1,ccc2) we have a view "View1" with columns say c1, cc1. Now is it possible to add new column ccc2 to the existing view "View1"
View Replies !
Show Colums" With A Where
I would need to know if there is a way to filter a Show Columns. The thing is I want to let my customers modify all the fields except the primary key. But since I may not always be the DBA I cannot create the field with the same name. So I need to determine if it's the primary key and if so, I will not show it. Also I cannot work with views because I will not always be the DBA and also because it would require to create as much views as there will be tables. So is there a way to inlcude a Where in the statement? If not, is there a command to determine if the field is the primary key?
View Replies !
MySQL Query Display
Have anybody ever see this display problem, my "SELECT * FROM..." is blur, why is that? The chipset I'm using is Intel 855GME with Integrated 3D AGP featuring Intel Extreme 2
View Replies !
Query To Display Duplicates?
I've looked online but haven't been able to find what I'm looking for. I just need a simple MySQL query to display all the records from a database if one of their fields (products_model) is being used by more than one record. Something like... ID products_name products_model =========================== 12 Product A 1345 237 Product G 1345 83 Product L 211 901 Product X 211 68 Product S 211
View Replies !
Display Result From Query In More Then 1 Column
I want to make a query to a database, and display it on my php page either through php code or with 2 queries to my mysql database. This is how i want it displayed: Data 1 Data 2 Data 3 Data 4 Data 5 Data 6 Data 7 Data 8 ... .... Data X Data X This is my current code which i use and get it displayed in 1 row:
View Replies !
Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script, my table having the details of uploading time, file name, & uploader name. i need to find the recent uploaded file for all uploaders. i tried with this query, select file_name,uploded_by,MAX(date_time) from upload group by uploded_by; it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.
View Replies !
ANDs In Query Not Working
I am working with this query: SELECT * FROM reviews WHERE gID = 'nothing' OR gID = '825' OR gID = '864' OR gID = '632' OR gID = '880' OR gID = '839' OR gID = '534' OR gID = '680' OR gID = 'x' AND type = 'online' AND status = '1' ORDER BY RAND() LIMIT 0, 10 With everything going on here, the most important is that only rows with status=1 are selected. However, I am getting some rows with status=0 in my results. Is there something wrong with the ANDs in my query?
View Replies !
Alias Not Working In Query
I want to have an alias for the long CONCAT statement, but if I insert the section "AS searchstring" (as seen below) then anything I reference in the WHERE clause fails. I get this error: QUERY: SELECT b.id, CONCAT(b.street,b.nums,IF(b.name IS NULL,'',b.name),IF(b.name IS NULL,'',b.blurb)) AS searchtext FROM buildings AS b WHERE b.distance<='20' ERROR: ErRoR: Unknown column 'b.distance' in 'where clause' ANOTHER EXAMPLE: SELECT b.id, CONCAT(b.street,b.nums,IF(b.name IS NULL,'',b.name),IF(b.name IS NULL,'',b.blurb)) AS searchtext FROM buildings AS b WHERE (searchtext LIKE "%purple%") ERROR: ErRoR: Unknown column 'searchtext' in 'where clause'
View Replies !
General Query Log Not Working 5.0.27
I am unable to get the general query log to work. I've added entries to the my.cnf file: [mysqld] general_log=on general_log_file=/var/log/general.log After modifying the my.conf file, I've stopped and restarted the service. >>/etc/init.d/mysqld stop >>/etc/init.d/mysqld start I've created the file manually and also tried letting mysql create it but either way it was not logging. When I manually created the file, I changed the ownership to the mysql user. Still didn't work.
View Replies !
Query Not Working In 5.0 That Worked In 4.0
I've created a development site for my website that uses mysql 5.0.18 and my live site uses 4.0.16. I have a query I can run without problems on the live site but is giving me errors on the development site and I was hoping someone might know what the problem is. It must be some change to the syntax standard I suppose. Thanks for your help!!! 1054 - Unknown column 'p.products_id' in 'on clause' select count(p.products_id) as total from products_to_products as pp, products p, products_description pd, manufacturers m left join specials s on p.products_id = s.products_id where pp.products_id=�' and p.products_id = pp.related_products_id and pd.products_id=p.products_id and p.products_status = Ƈ' and pd.language_id = Ƈ' and m.manufacturers_id = p.manufacturers_id
View Replies !
Simple Mysql Query Not Working
I have a simple table set up listing stockists and there details, included in this table I have a name column and a website column. I wish to select the name and the website of the stockists only if a website has been entered into the website column. Here is the query I have tried to use Quote: SELECT name, website FROM stockists WHERE website IS NOT NULL ORDER BY name ASC This query is returning stockists with no website as well as the ones with websites and I don’t know why!
View Replies !
Query Cache Not Working: MySQL 5 / Windows XP
I just installed MySQL 5, and its running great except that the query cache is not working, despite the configuration excerpt from my "my.ini" below: query_cache_size=5M query_cache_limit=2M query_cache_min_res_unit=4096k query_cache_type=1 From the MySQL command line, if I enter: SHOW VARIABLES LIKE 'have_query_cache'; I get: +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ Also, doing a: SHOW STATUS LIKE 'Qcache%'; gives me: +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 5234168 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 450 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ I figure the "Qcache_free_blocks" and "Qcache_total_blocks" values of 1 are creating the problem; it's basically telling me that there are no (okay, 1) free blocks to put my query. However, these values never change. The query cache is not vital to me, but it does improve performance, and I'd like to get it going....
View Replies !
Query Of Gmdate Not Working... Is My Syntax Correct?
$query="SELECT distinct(id),subject,view, postdate FROM threads where gmdate(Y-m-d H:i:s, postdate + 3600) >= date_sub('".$current_date."', interval 7 day) order by view desc limit 0 , 5 "; I tried to get my postdate to compare with the current date in order to display the most view topic in last 7 days in my page... however, the above coding is not working...
View Replies !
Order By Not Working Correctly, Query Problem?
I am using the following query to get a list of anime names and related info from my mysql db. As anime are sometimes known by several names (the actual japanese kantakana/kanji, the romanji translation and the english word). So the info_anime table has all the info about the anime and the info_animename just has the relevent animeid and the name. The query below is ment to check all the names in info_animename and join them to info_anime. SQL SELECT n.*, a.* FROM info_animename AS n, info_anime AS a WHERE a.animeid = n.animeid ORDER BY ".$_GET[orderby]." ".$_GET[order]." Now all the names are displayed properly with the various info but if you look here: Live Anime - Anime List You will see that the names are not in alphabetical order, for the most part they are just anime with several names are not working correctly if you look at the bottom of the page you will see: Yakusoku no Basho Kumo no Mukou followed by Beyond The Clouds, The Promised Place Which are the same anime under different names.
View Replies !
Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc). When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query. I tried it under phpmyadmin, and once again I get zero rows. Why do I get inconsistent results? Here's the query:
View Replies !
Last N Rows Of The Query
SELECT expensive query ORDER BY field ASC; It generates somewhere around 2.9m rows. I want the last 10: SELECT expensive query ORDER BY field DESC LIMIT 10 But I want them the other way around. Sure, I can do that programatically, but for "application reasons" I want that done in the query, so what I want to do is along the grounds of SELECT expensive query ORDER BY field LIMIT 10 OFFSET rows()-10 We're using MySQL 4.0. Is there a way to achieve the above without using a temporary table?
View Replies !
Several Rows From One Query
I've this sql-query... $sql = "INSERT INTO database (some_kind_of_id, names) VALUES $xxx, $_POST['xxx2']"; The thing is that my post xxx2 are several values that I want inserted on several rows with the values xxx in front...
View Replies !
Eliminate Rows In A Query (without Many OR)
This is my query : SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas There must be a more efficeint way to find what i want than this : AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 Something like cas.id is not (list of values)...
View Replies !
Query Help, Comparing Rows
Suppose I have the following data: +----+----------+-----+-----+-----+-----+-----+-----+-----+ | Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat | +----+----------+-----+-----+-----+-----+-----+-----+-----+ | 11 | 11:20:00 | F | T | T | T | F | F | F | | 12 | 11:45:00 | F | T | T | T | F | F | F | | 14 | 12:10:00 | F | T | T | T | F | F | F | | 15 | 12:35:00 | F | T | T | T | T | T | F | | 17 | 13:00:00 | F | T | T | T | T | T | T | | 18 | 13:25:00 | F | T | T | T | T | T | T | | 19 | 13:50:00 | F | T | T | T | T | T | T | | 20 | 11:28:00 | F | T | T | T | T | T | F | | 21 | 11:53:00 | F | T | T | T | T | T | F | | 22 | 12:18:00 | F | T | T | T | T | T | F | +----+----------+-----+-----+-----+-----+-----+-----+-----+ I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.
View Replies !
How Many Rows Were Affected By Query?
How do I retrieve the number of affected rows by an UPDATE query with SQL? The C API exposes mysql_affected_rows() but I can't find documentation of the SQL equivalent... I'm trying to find if an UPDATE had an effect within a stored procedure, so I can do an INSERT if there's no row to update. Doing it the other direction to generate an error to act on would be quite wasteful... one INSERT and millions of errors per day.
View Replies !
SQL Query Not Outputing All Rows.
My query doesn't seem to output all 6 rows in the database only two queries and i'm unsure why. I do know it is the query as when i do SELECT * FROM case_studies It outputs all rows. The query i am usign is: select programs.program_title, programs.id, case_studies.id, case_studies.title, case_studies.author, case_studies.timestamp from case_studies, programs WHERE programs.id = case_studies.id Any ideas?
View Replies !
Average Query With 2 Rows From Same Table
My table: "answer" answerID answer(int) questionID(int) userID(int) answer1 is questionID = 1 answer2 is questionID = 2 WHERE userID is the same for both answer1 and answer2 I want the average of answer1/answer2: AVG(ans1/ans2), but how?
View Replies !
Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out. The query I'm running is too slow...can anyone tell me what I can do to speed it up..if I can at all? I was wondering if because ZipListMatrix has 3.3 million rows that 8 seconds is all the faster it's going to be. Any help is greatly appreciated! I have already "optimized" the tables.
View Replies !
Get Rows From Multiple Tables In One Query
Is there a way to select rows from multiple tables in one query? Say I have the following tables and columns:Storestore_idItemitem_iditem_store_id I want to get a store by it's id + all the items associated with that store id. Do I have to make two separate queries for this? One to get the store, and another to get all the items for that store.
View Replies !
Query Browser :: How To View Rows?
I'm having a hard time with the Query Browser. I can't figure out how to view the rows. When I click on the table in the Browser, nothing shows up and at the bottom there is a message saying Library Error Number 3. I am sure this is something simple but it's making me tear my hair out.
View Replies !
Performace Question. 525,600 Rows In One Query
I am wondering which would be the best option. I have a database holding prices for every minute of trading. This means that getting a year of price data for graphing would mean that I am potentially requesting 525,600 (more on a leap year) rows. The graph only needs to have a resolution of one point per day, so should I: 1. query 525600 rows and then take one price per day 2. create a query to only take one price per day ?
View Replies !
Getting Rows Multiple By List Query
I have a list of values (constants) that I use to find matching rows in a single table as follows: "select id, name from articles where id in(1,2,3,2,3,2,2)" seems very simple, but my problem is, the query delivers only 3 lines, of course, because the last four numbers are redundant. But my need is, to get a row for each number in the list, wheather it is doubled or not.
View Replies !
Query Delete All Rows From The Table?
I have a table called "repairs" with multiple rows (let's say for the purposes of this post - from repair_id=1 through to repair_id=10) Why does this query delete all rows from the table? DELETE FROM repairs WHERE repair_id=3 OR 4
View Replies !
Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'") set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'") Is there any way to do this in one SQL query?
View Replies !
How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work) $sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";
View Replies !
Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers. OK, here is the table table test ------------------------ | test_id | test_order | ------------------------ | 1 | 1 | ------------------------ | 2 | 2 | ------------------------ | 3 | 3 | ------------------------ I'm trying to change the orders in one query, but not sure how to do that. phpMyAdmin shows me the code like this Quote: $sql = 'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;' 'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;' 'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;' . ' ' I'v tried that but got a syntax error. MySQL version is 4.0.26, can anyone help please?
View Replies !
Query Pulls Out Multiple Rows Even Though Theres Only One
Ive got a query thats selecting info about a product from a table called items and joining on a table called itemimages to get its associated images. The product can have more than one image. If i run the query on an item with 2 images i get 2 results for one item.....when theres only one item.....it seems to duplicate the item for each of its images.... SQL SELECT items.*, itemimages.* FROM items INNER JOIN itemimages ON (items.itemID = itemimages.itemID) WHERE categoryID = '$category' AND active = 'yes' LIMIT $start, $limit"
View Replies !
Update Multiple Rows Using One Query
I have a product table, when one product is out of stock, I want to set the stock status to "0". I'm using the following form to do it. PHP Code: <?php if(isset($_POST['update'])) { $code = $_POST['code']; $stock = $_POST['stock']; $sql= "UPDATE product SET stock=$stock WHERE itemcode=$code";Â Â Â Â $query = mysql_query($sql,$conn); if ($query) {echo "<H1>UPDATED</H1>";} else {echo "<H1>Update Failed</H1>";} } else { ?> <div> <form method="POST" action="<?php echo $PHP_SELF ?>"> Â Â Â Item Code: <input type="text" name="code"><br> Â Â Â <br> Â Â Â Stock Status: Â Â Â Â <select name="stock"> Â Â Â Â <option>Please Select</option> Â Â Â Â <option value="0">Out of Stock</option> Â Â Â Â <option value="1">In Stock</option> Â Â Â Â </select>Â Â Â Â Â Â Â <br> Â Â Â <br> Â Â Â <input type="submit" value="Update" name="update"> </form> </div> <? } ?>
View Replies !
Insert Multiple Rows Single Query
I create a table with 7 columns id Mdate col1 col2 col3 col4 col5 as above shown id is small int, mdate is date and col1 to 5 are tinyint and i want to insert the values . id is auto increment and i want mdate a day for one row. and other column set to zero what i want is to enter in single query.
View Replies !
Reverse Order Of Rows In A LIMIT Query
I have the following query: SELECT * FROM stocks WHERE ticker = 'CBU' AND `date` < '2009-01-31' ORDER BY `date` DESC LIMIT 3 This returns the 3 most recent dates out of a total of 20 rows. I would like to use the 3 most recent dates BUT IN REVERSE order. if I use the ASC command, it will not work as it will return the 3 oldest dates in the 20 rows, not the 3 most recent in reverse order. How can I reverse the order of the 3 most recent dates returned with the LIMIT command in the query above?
View Replies !
Select Double Rows In One Table In One Query
I try to select double rows in the same table within one query. I can do it with 2 query's but i wonder if it's possible with one? The table 'admin_category: Quote: id name sub 1 cd 0 2 dvd 0 3 action 1 4 new 1 5 general 2 The current query which i use: PHP Code:  SELECT a.name AS sub, b.name AS category FROM admin_category a LEFT JOIN admin_category b ON a.sub = b.id WHERE a.id = '4' What i try to reach with above query is that row 4 will be selected, take the name of it (new) and also the main category (b.name as category) by doing a left join on the same table where the sub id = the id. The output of above query is: sub = new category = NULL (Category should be 'cd' but it's not)
View Replies !
Update Numerous Rows In A Single Query
Let's say I have 6 rows in my MySQL table ID RANK ============= 1 1 2 2 3 3 4 4 5 5 6 6 I want to delete ID #3, that's ok, BUT I want to downgrade all the rows that has a RANK value higher than him (3). So my table would look like this afterwards; ID RANK ============= 1 1 2 2 4 3 5 4 6 5 I want to be able to update a field within the query, something like this $deleted_rank=3; //the rank number that got deleted mysql_query = UPDATE tablename SET rank='rank--' WHERE rank>'$deleted_rank' the field RANK is an INT I know the rank-- thing doesn't work, but I want to know how I can achieve this in a single query, I don't want to do a WHILE on this.
View Replies !
|
TRACKER
