Select For A Report
I have a table with this fields :
ID (integer)
Date (datetime)
Name (char)
I want select count of items that add in each month of year
For example a report like this :
2 Items add to database in january of 2006
9 Items add to database in march of 2006
3 Items add to database in january of 2007
(I can change the numbric month to it names with PHP)
View Complete Forum Thread with Replies
Related Forum Messages:
Report Creator
I am now using Open Office to connect to the MySQL thru ODBC. So that I can retrieve some data from MySQL and generate report in open office. I have already installed MyODBC and unixODBC. A file called odbc.ini created as below: [MySQL-test] Description = MySQL database test Driver = MySQL Server = localhost Database = test Port = 3306 When I tried to test this ODBC connection using isql MySQL-test test, an error occured: [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified [ISQL]ERROR: Could not SQLConnect
View Replies !
Report Tool
Is there any MySQL reporting tool that is open source and doesn't require programming knowledge? I need a front end for MySQL that will allow a user to input a couple parameters and will produce a report, including graphs. I haven't been able to find an adequate open source solution for this.
View Replies !
Mysqladmin 1.4 Report
when i try to create a report in mysqladmin 1.4 it a message box displays "the server must be connected" Under the enviroment tab the myODBC box has "not found Driver 3.51 not found I am using xampp can you tell me what i'm doing wrong?
View Replies !
Report Writer
couldn't really find a spot to put this, but basically I need a MySQL report writer. I have been using SQuirrel, but it isn't properly representing BLOB and DOUBLE values, looks like the values come out the other end as hexadecimal. Rather than spend time sortign it out I'm just gong to move to another report writer. It is unfortunate as I have really enjoyed using SQuirrel.
View Replies !
Report Generation
I have the MySQL server and the Query Browser up and working but I don't see anything about generating reports and I can't find anything on reports in the online documentation. What do MySQL users use for generating customized reports? Is there some GUI tool from MySQL or do people use other products like Crystal Reports?
View Replies !
Weekly Report
I am creating weekly reports for a application that I have. I'm using the Date column ( YYYY-MM-DD) for my mysql database. Is there a query that brings up totals for Monday through Sunday for the month of April/June etc??
View Replies !
Client Side Report
i hav a few reports that generate at server side i would like to copy the tables needed for the report to client's temp file, and perform the calculation and filtering there. may i know is this way possible? for 1 table, there's .frm, .myd and .myi files, which files should i copy? and how should the connection string looks like when connecting to client's own temp file?
View Replies !
Generate Report Using Group By
I have few invoices which have due_date as one of its field. Now I want to generate a report like the following CName --------0-30 -------- 31 - 60 --------61 - 90 -------- over 90 abc --------$45 -------- $34 -------- NO -------- No Xyz --------No -------- $78 -------- $76 -------- No bcd -------- $897 -------- $456 -------- No --------No Totals: ---- --- --- --- Where those days are overdue period (today - due date). I want to retrieve the whole in one (max 2 not a buch in a loop) queries.
View Replies !
Parameters In SP When Calling A Report
Set up the connection to the DB in Crystal using MyODBC. I used the Database expert to call my Stored Procedure which has one Parameter and the code is real simple going like this: call SuccessLetter (AppNo) From the wizard i can see and place all the fields from the SP onto the report as long as i hard code an application number into my parameter. But as soon as i change it to a variable (as above) i get the following error from Crystal: Unknown Fields AppNo in order clause MySQL ODBC 3.51 error 42S21 On the VB code side i used Success.SetParameterValue("AppNo", GeneralFunctions.ApplicationNo) to set up the parameter and this does get passed thru so the error is comming from the database expert - almost as if it dont recognise that my stored procedure has a parameter. Which it does and it does work if i use the SP outside of Crystal. I know the SP is working fine cause i have written another SP and then called SuccessLetter with the new SP using a parameter and the results are perfecr.
View Replies !
INSERT IGNORE - Get A Report?
I'm creating a shopping cart-esq type system with a primary key on two fields. My app builds an insert query that can insert many rows at once. When the user tries to add something already in their cart (not allowed by the primary key structure) the IGNORE statement allows the query to go ahead, and the other rows to be inserted. My question therefore is: Can I get a response from MySQL as to whether or not the IGNORE statement affected the query ? i.e would there have been a duplicate row ? Ideally I could even get info about WHICH row would have caused a conflict, but I'd be surprised if I could get this info.
View Replies !
Report Query Load
Is there a way in PHP to have the MYSQL report the load that is placing on the mysql server. I am trying to optimize some queries and I was hoping the server could give me some feedback so I know if I'm going in the right direction.
View Replies !
Structure Of Database To Prepare Report
I'm attempting to create a database which will take information from one (perhaps two) tables and utilize that information to return queries to a report designed in Excel. The general idea I have is this: The "primary" or "source" table is easy. This is a trial balance (account number as a primary key, description, amount). I'll need table(s) to denote which account numbers belong to which page/line on the report. I'm not sure how many pages the report will be, but let's say my Excel workbook would have 50 sheets. My question is this: What would you recommend for a table structure. The way I see it, I could have a single table listing each page and each line on each page (each identified by a unique id), then have another table listing each account and each page/line id. The other option I see is to have a separate table for each "page," but I'm not sure I see a benefit in this. My first instinct is to use the first method I mentioned.
View Replies !
Crystal Report, MySql, And Images
I am creating a report using Crystal Report and I want to upload an image coming from the mysql database to the crystal report. Can I upload the image to crystal report without actually storing the image in the database?
View Replies !
Multiple Count Queries -> One Report
I'm getting a bunch of data out of a database in the form of a lot of COUNT statements eg: SELECT COUNT (*) AS "Value1" FROM tablename WHERE x=1; SELECT COUNT (*) AS "Value2" FROM tablename WHERE y=5; etc ... there are about 30 queries. I'm using PHPmyAdmin to do this. Currently I have to put each statement in separately: If I put the whole load of them, PHPadmin only displays the last value. I don't need a great deal of detail, but if someone can suggest a way I can accomplish this so I run one multi-line query and get all my values at once that would be great. Perhaps there is a better tool than phpMyadmin? Or maybe I should be looking at VIEWs or STORED PROCEDURES? If you can paint the broad outlines, I can fill in the gaps.
View Replies !
How To Show Records In A Report View
my table contains the following columns : news_date (date) news (tinytext) i need a query which would display the table records in the following way: 09-11-06 the news item------------- --------------------------- --------------------------- 10-11-06 the news item------------- ------------------------- ------------------------- thats is each record should be displayed as seperate and not in a tabular view.
View Replies !
Report Of Query Execution Times
Does mysql have a tool to record and report a list of all queries executed and how long it took to execute each query? If not can anyone suggest a tool I can use to do this? I've heard of mysql query profiler but does it capture queries from all sessions?
View Replies !
Crystal Report Connection Problems
ODBC ERROR:[MySQL][ODBC 3.51 Driver][MySQL-5.0.27 community-nt] You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version for the right syntax to use neer 'ahmsys','tenants' limit 0 at line 1. While trying to connect to this tabel (tenants) I get this error, however I'm able to connect to the next tabel (teansactions) in the database (ahmsys). What is this... limit 0 at line 1.
View Replies !
How Make Stock Card Report
i want the query can show stock card report like : Item Code QtyIn QtyOut Final Date A-001 2/3/2008 10 5 5 <------------ line 1 4/3/2008 2 0 7 <------------ line 2 10/3/2008 20 2 25 <------------ line 3 15/3/2008 4 0 29 <------------ line 4 item code,date,qtyin and qtyout are my fields in databse, but "Final" are operation result from 10 - 5 = 5, 5 (final line 1) + 2 + 0 = 7, 7 (final line 2) + 20 + 2 = 25, 25 (final line 3) + 4 + 0 = 29
View Replies !
How To Report Changes In Rows Before And After Multiple Transactions
I am trying to uncover what a specific tool is doing to my database. What is your favorite way to compare database contents before and after an operation? I would like to know what rows were modified in which tables. Hopefully, such a tool or method could report the "before" and "after" states of affected rows only, where applicable. I don't expect there to be any changes to any table structures. I am using a LAMP setup, if it matters. I am hoping for an open-source solution.
View Replies !
Viewing Error In Crystal Report
i am trying to make a report using crysatl report in c#.net for mysql database. 1. when i use a dsn (odbc) the error occurs when the form containing the viewer is loaded 'could not load rowset'. however i checked the connection and it worked fine 2. i tried again using the project data sets when building the crystal report but this time i could not log on when the form containing the viewer was openend
View Replies !
Scribe Report Generator For MySQL And Other RDBMS
We are looking for beta-testers for our new product - Scribe Report Generator. At this moment, Scribe supports 6 relational databases, including Oracle, MS SQL Server 2000, and Sybase. Very briefly: Scribe is written in Java, is based on visual programming and can be extended by adding user-written Java classes; it comes with the API library to let other Java-based apps run Scribe reports programmatically. The details are on our website: www.gestalt.com/Scribe (temporary location). User: guest Password: star We offer a free license to everyone who provides us with a feedback on bugs, inconsistencies, or constructive criticism that helps us refine Scribe.
View Replies !
Stored Procedure For Creating Daily Report
how i can create a stored procedure that do the select into the OUTFILE for a day or two from the time of excution. the aim is to generate a report to the shared folder say /home/reports/ The created file should have the unixtime to identify itself
View Replies !
Report Writing Query Problem (joins Again...)
Hola database type folks, I have been trying to get this right for sometime but now must admit defeat I have 3 tables artists -> holds details on the artists card_gallery -> hold details of card available related to artists via artist_name scart -> shopping basket related to card_gallery via card_image_id I am writing a reporting facility that allows the administrator to view all the artists in a table along with their number of cards online and cards sold figures. Currently I have something like select card_gallery.artist_name as cg_an, count(card_gallery.artist_name) as cg_ca, count(scart.card_image_id) as s_ci, from card_gallery left join scart on card_gallery.card_gallery_id=scart.card_image_id group by card_gallery.artist_name but that doesnt give the correct number of cards sold. Cards sold are registered in the scart table by setting the processed field to 1 Ideally I would like to be able to view all the artists on one table with the number of cards online and the number sold even if it's 0
View Replies !
Adding User Defined Date Range For Report
Hello I have a very simple query that list customer info within a date range. I would like to have a interactive form that would allow the customer to choose between two dates and query the DB for information between the two dates entered. it is coded like (WHERE `date_purchased` > '2005-08-01 00:00:00' AND `date_purchased` < '2005-09-01 00:00:00') below is the code but I need help or guidence on how to get the customer input into the query instead of the hard coded area that is there now....
View Replies !
Full Yearly Weekly Group By Group Report
I have table called tblTransactions: TblTransactions Group | Months | Year | Transaction A | 3 | 2007 | 45 B | 2 | 2007 | 89 A | 7 | 2007 | 50 A | 8 | 2007 | 34 B | 4 | 2007 | 55 A |12 | 2007 | 10 A | 1 | 2008 | 88 B | 3 | 2008 | 34 B | 5 | 2008 | 70 A | 5 | 2008 | 45 A | 9 | 2008 | 88 B | 7 | 2008 | 99 A |10 | 2008 | 77 A |11 | 2008 | 99 How is the T-SQL to make the result like this (the result will display all of the week (FULL), the target is I can compare week by week between years and group. A | 1 | 2007 | 0 B | 1 | 2007 | 0 A | 2 | 2007 | 0 B | 2 | 2007 | 89 A | 3 | 2007 | 45 B | 3 | 2007 | 0 A | 4 | 2007 | 0 B | 4 | 2007 | 55 A | 5 | 2007 | 0 B | 5 | 2007 | 0 A | 6 | 2007 | 0 B | 6 | 2007 | 0 A | 7 | 2007 | 50 B | 7 | 2007 | 0 A | 8 | 2007 | 34 B | 8 | 2007 | 0 A | 9 | 2007 | 0 B | 9 | 2007 | 0 A | 10| 2007 | 0 B | 10| 2007 | 0 A | 11| 2007 | 0 B | 11| 2007 | 0 A | 12| 2007 | 10 B | 12| 2007 | 0 A | 1 | 2008 | 88 B | 1 | 2008 | 0 A | 2 | 2008 | 0 B | 2 | 2008 | 0 A | 3 | 2008 | 0 B | 3 | 2008 | 34 A | 4 | 2008 | 0 B | 4 | 2008 | 0 A | 5 | 2008 | 45 B | 5 | 2008 | 70 A | 6 | 2008 | 0 B | 6 | 2008 | 0 A | 7 | 2008 | 0 B | 7 | 2008 | 99 A | 8 | 2008 | 0 B | 8 | 2008 | 0 A | 9 | 2008 | 88 B | 9 | 2008 | 0 A |10 | 2008 | 77 B |10 | 2008 | 0 A |11 | 2008 | 99 B |11 | 2008 | 0 A |12 | 2008 | 0 B |12 | 2008 | 0
View Replies !
INSERT INTO SELECT Taking Forever But SELECT Part Is Fast?
I have the following mysql code to insert about a million rows. This takes quite a long time (over 50 min). However, when I just run the query alone without the INSERT command, the query itself only takes under 5 minutes. Is there a way I could speed up the inserting?? insert into tblc select tbla.* from tblb inner join tbla on tbla.issuer_id = tblb.issuer_id;
View Replies !
SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity. Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ? How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?
View Replies !
Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.
View Replies !
Speed Diff Between Select * And Select Column
I have this question that I cannot find a right answer among my friends. Will like to know: Assuming I have a table with 8 columns. When I am querying these records with specific WHERE conditions, will SELECT * be faster, or SELECT (3-5 x columns) be faster?
View Replies !
Combining SELECT Statements Into One SELECT Statement.
I want to take the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN authuser ON name.empnumber = authuser.uname AND authuser.team = 'PHQ' ORDER BY name.lastname, name.firstname; and the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN crew_attendance_6QJ ON name.empnumber = crew_attendance_6QJ.empno ORDER BY name.lastname, name.firstname; And combine them into one query that outputs all of the results both queries would output. Then order those results. So far I have come up with:
View Replies !
Select Within Select Needed To Get This Query Going?
I'm not sure how to go about this, but I am thinking I need some sort of a select query within a select query to get this properly displayed... My table structure is as follows (simplified, of course): payment_id | member_id | completed | amount | trans_added and I am trying to get the following data out of the table - a daily sum of all the transactions that were 1) completed, 2) by a member who already has an existing completed transaction (completed = 1) on a different date (or members with a count of all completed transactions > 1 will be included only). so far I've got this query, but I don't know where/how to plug in the condition for the members/completed/etc: .....
View Replies !
SELECT Doesnt Select Anything WHERE Doule
I am running the next script SELECT * FROM flcc_Tickets WHERE 'Match No'=1; but dont get any results although there are lots of resords with 'Match No'=1 in a table. 'Match No' is DOUBLE . if run it with 'Match No'=0 i get all the table.
View Replies !
Nested SELECT :: Select All Years Except Last
I'm new to MySQL and want to do a nested SELECT statement. i want to get all years stored in the database except the last year: (content is the table and null values exists) SELECT DISTINCT YEAR(content.date) FROM content WHERE(((YEAR(content.date)) IS NOT null)) AND YEAR(content.date) < (SELECT YEAR(MAX (content.date)) FROM content)) ORDER BY YEAR(content.date) DESC What is wrong
View Replies !
What's The Best SELECT In This Case / How Can I Make This SELECT ?
well, step 1, I need to get a few ids like: PHP SELECT id FROM $table_one WHERE this_field=$that_value // this is OK I will get multiple rows from this command Then, I need my main SELECT command (that i need to use) like: PHP SELECT * FROM $table_two AS t2 LEFT JOIN $table_three AS t3 ON t2.pid = t3.pid WHERE t2.pid = (all the ids that I have got from my first SELECT)
View Replies !
Select In Select Statement
Could any give me an example of how this works please as for the life of me cant get any results.Basically I have 3 tables, I record home team and away team results in the RESULTS table and up and coming fixtures in the FIXTURES table. Im wanting to get all my home teams previous results where they are playing at home in an upcoming fixture but also want to return for the same fixture the away teams previous results, I dont even need TEAM table to be honest but thought may be easier to create a join between the two tables.
View Replies !
Select With Select In Where Clause
This is a very simplified SQL statement intended to reproduce an error, not do anything interesting: select * from value where id in (select id from value where id=1000000); It fails with: ERROR 1064: 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 'select id from value where id=1000000)' at line 1 However, the following works select * from value where id in (1000000);
View Replies !
Select Constraint (select ... Where X) If 'where X' Isn't Specified
I'm working on a site where records (say articles) can exist in one of three states: active, archive, trash. I'm doing this so that I can implement soft delete and non-destructive action. For some brief background, we used to simply have an 'archive' column with enum('n','y'). Which meant that every query had to be appended with "where archive='n'". Later we modified the architecture of the application be more robust so that you could publish records (basically duplicate the records into a table called articles_instance; so you can publish as many versions of a master copy as you want). This worked well enough that when I decided to implement soft-delete, I decided to move the deleted record into the articles_instance table. I created a new column ('state' again) with 'publish', 'archive', and 'trash'. So whenever I query the instance table, I opt in "where state='publish'" instead of having to opt-out, like before ("where archive='n' and trash='n'". This was better, but I realized that I had to save the old record id in a column 'old_id' because I wanted to be able to send the row back into the regular 'articles' table if the user chose to undo the action or unarchive an article. I decided to keep the old id, rather then send it back with a newly generated id because there were certain relations I needed to keep, like associated audio files with the article (temporary broken links aren't as much of a problem because you can only see the audio when you look at the article; when the article is permanently deleted, then I can remove the relations). This approach works, but I feel it's messy, and with all the code I'm writing to shuttle the rows back and forth, I feel as if there's only more chances for error. I like the simplicity of simply setting a state in the master 'articles' table. But then I have the problem of having to specify "where state='active'" in all of my queries. If I don't do that, I risk pulling all possible articles, regardless of state, causing strange problems. So here's the latest breakdown: The articles table has a 'state' column with enum('active', 'archive', 'trash'). The articles_instance is now called 'articles_publish' and no longer has a state column. So it is used exclusively for published articles. It boils down to this: Is there a way, in MySQL 4 (I don't know the exact version, but I can find it if necessary) to constrain/modify all of my select statements to have an implicit "where state='active'" if a state isn't explicitly specified? If I could do this, I feel I could have the best of both worlds: have two tables with specific needs, where one table has data that can exist in different states, and I wouldn't need to move rows back and forth, possible causing problems. And if I implement the constraint, I don't have to worry about going through and changing a lot of existing code and making sure the problem doesn't crop up in the future. I am providing all this background in the hope that someone might just suggest a better and more efficient way of doing this and tell me that a select constraint is entirely unnecessary . I might also be convinced that using MySQL as a crutch in this case might be a poor idea and could potentially confuse programmers down the line (although there's only two of us). If it's not already clear, I'm new to all of this, so I am not really familiar with best or standard practices when it comes to these sort of things. If anyone wants to refer me to any resources (books or articles) about versioning/publishing records or database design in general, I'd love to hear them. Thanks!
View Replies !
How To Use Previous Select Query Results In New Select Query?
I have tested everything and it works OK in its current form. However, what I need help with is the part of the query highlighted in red. As it currently stands, the PHP while statement loops through the rows and echos out the html. The issue I have is that it echos out the same speaker name and subject type for each iteration. Now I know this is happening because I have set subject.event_id = 1, so what I need is to use the event_id of the current iteration as the clause and that is what I need help with....
View Replies !
Row Count Mismatch In Select Count(*) And Explain Select Count(*) From Table
mysql> explain select * from parameter; +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | parameter | ALL | NULL | NULL | NULL | NULL | 3354 | | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> select count(*) from parameter; +----------+ | count(*) | +----------+ | 97 | +----------+ 1 row in set (0.00 sec)
View Replies !
Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost: SELECT *,count(*) FROM cancons c, musics m, discos d, r_discos_cancons rdc WHERE c.c_id_music = m.m_id AND rdc.rdc_id_disc = d.d_id AND d.d_id_music = m.m_id AND m.m_id = 24 GROUP BY c_id note: cancons (ca) = songs (en) discos (ca) = cd's (en) music (ca) = musician (en) don't worry for the WHERE part. i need it because of the foreign keys. this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think. I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.
View Replies !
|
TRACKER
