Query Results By Pages
I would like to display query results in pages of say 20 results each.
e.g. a table with two columns, serial# and descrition, with a few thousand entries.
What would be the SQL query to find a specific serial number, and display all 20 results within the "page" that row happens to be in?
e.g say serial#=3211 is in row 64, display rows 60->79.
View Complete Forum Thread with Replies
Related Forum Messages:
Search/Results Pages
My Search page works, My results page doesn't want to display the results properly and I think the problem lies with my select statements. I am searching for a match between a selected Event Name in the db to a user Event Selected. All one table called Events. The result only displays one row of information when there are 2 matches in the db. The Event Name selected on the Search page is a drop down menu of choices with a Submit button and Event Names do have spaces and hyphens in them which the db stores verbatim.
View Replies !
Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to display the results based on the user input. The results might be anywhere from 100 to 1000 per query and presently I am displaying them as 25 results per page. Problem: Each query is taking about 20-30 seconds. My solution: I have tried to optimize the table and also index the table. I have actually converted a MS access database to SQL database, so it wasn't previously indexed. Both optimization and indexing doesn't give any good results. I always get a timeout. ie. it takes longer after indexing and optimizing. 1. I was wondering if someone has a creative solution for this. ie. reduce the time from 20-30 seconds to atleast 10 seconds. 2. I have links of pages of results beneath the first page result. When each of these links are clicked it takes 20-30 seconds again. Is there a way I can reduce the time taken for the subsequent pages are reduced? I cannot use the LIMIT option in mysql, since I have a where clause which has to search through the whole table. I tried using views and using limits, but it takes as much time.
View Replies !
Display All Results Vs Spliting Up Over Number Of Pages
I have built a site for a local auction house. Each auction has around 2000 lots, which are held in the table auction_items with references to another 3 tables. I am trying to work out the best way of displaying all of the items for a given auction, whilst optimising performance. Each site visitor typically wants to see all of the items in the list and wants the option to "show all" items in a single view. From past hits we estimate approximately 700 hits a day, most of which will want to view the list of items. Are there performance benefits to be gained from displaying the results 20 to a page and having the users scroll through? Rather than loading the full list once per user? From a usability point of view they would be better off with one list that they can see "at a glance" and print.
View Replies !
Query Pages Printed By Percentage Of Total?
I'm very new to MySQL and have a query I'm trying to achieve, if anyone can point me in the right direction, I would greatly appreciate it. I have a table created to store print job logs from a print server: Table = printLogs logNum mediumint(8) logDateTime datetime docNum tinyint(3) docName varchar(200) owner varchar(25) printerName varchar(25) printerPort varchar(50) sizeInBytes varchar(50) pagesPrinted smallint(10) What I'm trying to do is find a query that, based on a 'printerName', will display 'owner' and percentage derived from the total number of pages printed to that 'printerName' for a given date range. Something like this: +--------+--------------------------+ | owner | Percentage of total jobs | +--------+--------------------------+ | Mary | 50% | | John | 30% | | Kim | 10% | | Sue | 10% | +--------+--------------------------+ Each row of the table is a different print job and can be one of about 20 different owners who have printed any number of pages to any of 10 printers. The main reason for the query is to be to determine which group/owner uses each printer the most.
View Replies !
Save Query Results
In other words, how do I build a new table from the results of a query, so I can access pieces of this data later without performing the same query (a very heavy one) over and over again?
View Replies !
Filter Results Of One Query
I'm looking for the syntax that would allow me to filter one querys results using another query i.e. QUERYA = SELECT * FROM table WHERE id < 100 QUERYB = SELECT * FROM QUERYA WHERE id <> 49 I understand that I can do the above example in one query - I'm just using that to simplify matters.
View Replies !
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
Formatting Query Results?
I have a shell script (sh) that queries a MySQL database and then formats the results to an html page. I realize shell is probably not the best choice for this but it's all I know. My problem is that I need to do a mysql query for every field in every record in order to put it into the html page. This is obviously very slow. This is an example of what I'm doing now: ProductLine=`mysql -u www -s support <<EOF SELECT ProductLine FROM Documents WHERE ID = '$ID'; EOF` DocType=`mysql -u www -s support <<EOF SELECT DocType FROM Documents WHERE ID = '$ID'; EOF` DocName=`mysql -u www -s support <<EOF SELECT DocName FROM Documents WHERE ID = '$ID'; EOF` FileName=`mysql -u www -s support <<EOF SELECT FileName FROM Documents WHERE ID = '$ID'; EOF` Like I said, I am doing this for every field in every record. It works but it is VERY slow. I thought if there was a way to tell the query to insert a special charater between each field then I could use awk to pick out the fields and only do one query per record... something like this: SELECT ProductLine, DocType, DocName, FileName FROM Documents WHERE ID = '$ID'; I don't know how to take that output and put it into my shell variables.
View Replies !
Saving Query Results
Is there any way to save the results of a query without the quote marks? When i try to import the text file that I saved the query to, I have to go thru and delete all of the quote marks.
View Replies !
Query Giving Me Results I Don't Want.
query: SELECT sales_reps.sr_id, sales_reps.order_id, sales_reps.name, sales_reps.job_number, UNIX_TIMESTAMP( shop_orders.date ) AS date, sales_reps.stage, sales_reps.status, pi.fname AS pfname, pi.lname AS plname, sales_reps.date_to_shop, users.builder, users.division, shop_assignments.editor_id, shopper.fname AS sfname, shopper.lname AS slname, ei.shop_result, ei.good_tape FROM sales_reps LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id LEFT JOIN users ON users.user_id = shop_orders.builder_id LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id WHERE sales_reps.status = 'Rejected' AND ( ei.shop_result != 'Trip x1' OR ei.shop_result != 'Trip x2' OR ei.shop_result != 'Non-Reg Trip x1' OR ei.shop_result != 'Non-Reg Trip x2' OR ei.shop_result != 'Office Closed' ) ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC It's not filtering out the records with the shop_result field matching what I've listed.
View Replies !
Usergroups On Pages
I am trying to make some type of simple CMS, and I have a table for pages, and I want it so that when the user is making the page, they have an option for what usergroups they want to be able to see it. The admin will be making the usergroups themself, so I don't think I would be able to use SET. ANy idea on what I would put in my "usergroup" table or my "page" table? Or any other method of doing this.
View Replies !
Pages With Mysql
i'm looking to do pages with stuff out of mysql. lets just say i have a long list of names in the database and i want to display 10 at a time and have "<<Prev 1 | 2 | 3 next>>" display for each page. like this: name1 name2 name 3 name4 name5 name6 name7 name8 name9 name10 <<Prev 1 | 2 | 3 next>> i've tried doing something like this before but it's never worked right.
View Replies !
Popular Pages
Is it possible to use data from the url, record it into MySQL or text data file so as to display the popular pages from a site. Any PHP+MySQL ideas or links are needed? ----------------------------- I had the idea started with no luck... the url is basic structure like: domain.com/tmplate.php/1038/BMW list($first, $last) = explode('/', substr($PATH_INFO,1)); $entry_line = "$first/$last "; $fp = fopen("/public/count.txt", "a"); fputs($fp, $entry_line); fclose($fp); the explode grabs the 1038/BMW from the url and records into a text file.
View Replies !
Exit Pages
I have a database that contains all the pages requested on a certain site and the ip of the requester and a timestamp. Is it possible in an sql statement to get a count of the exit pages. ie. count the number of times each page was the last page requested by that IP within a given time frame?
View Replies !
How To Get Distinct Hour(s) In Query Results?
I need to write a query that will count the number of records that have been written for each hour of the day. This must be done entirely in SQL, and I am not an SQL expert. I know how to break the hour out of a timestamp using the hour() function, but I'm unclear on how to get the breakdown like I described above.
View Replies !
Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times. I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.
View Replies !
Update Query Results To Another Table
So I'm learning, bear with me, this is probably pretty easy(but I am a bit perplexed) but I'm writing a php page for a cron. I am selecting a user name from a table basically selecting all row entries in that table with a certain username, and adding them together. $query=mysql_query(SELECT UserName, SUM(AcctOutputOctets) FROM radacct GROUP BY UserName) The output is this(when I run a query in mysql) UserName..............SUM(AcctOutputOctets) test..................... 345566 Basically i'm adding up all the users entries and totaling their Usage. Now the question. I want to write that to another table named mtotacct. format like this. Username..............TotalOctets. I'm just a little uncertain how to properly set this up to pass the results from the query to the UPDATE statement.
View Replies !
Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows: SELECT MIN(Freq_Min) FROM datatable; and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....
View Replies !
Use Query-results As Delayed Insert?
Can I use the results of one query like: -> SELECT user_id FROM tbl_customers And use the results to fill the VALUES-statement in another table like: -> INSERT INTO tbl_postal ('user-id') VALUES (<previous query results>) If so, could someone complete my second SQL-statement, since I'm staring at my CRT for over 2 evenings and I cannot get it to work.
View Replies !
Query Is Not Returning Desired Results.
My query is not returning desired results. I want to structure my query to return the Post Subject, Post Text, and Poster. Instead, it is returning the correct Post Subject, correct Post Text, but the Poster is just repeating the username from the '$_POST[username]' variable. How can I alter the query to return the correct name? the query PHP <?php$conn = mysql_connect( $domain, $user, $password ) or die("Err:Conn");$rs = mysql_select_db($db, $conn) or die("Err:Db");$sql = "SELECT `table_posts_text`.`post_subject`, `table_posts_text`.`post_text`, `table_users`.`username` FROM table_categories, table_forums, table_topics, table_posts, table_posts_text, table_users WHERE username='$_POST[student]' ANDcat_title='$_POST[category]' AND`table_categories`.`cat_id`=`table_forums`.`cat_id` AND `table_forums`.`forum_id`=`table_topics`.`forum_id` AND `table_topics`.`topic_id`=`table_posts`.`topic_id` AND `table_posts`.`post_id`=`table_posts_text`.`post_id`AND `table_topics`.`topic_poster`=`table_users`.`user_id`AND`table_posts`.`poster_id`=`table_users`.`user_id`ORDER BY `table_topics`.`topic_id`, `table_posts`.`post_id`"; $rs=mysql_query($sql,$conn);while($row=mysql_fetch_array($rs)){ echo($row['post_subject'] . $row['post_text'] . $row['username']);}?> There are six tables involved: 1. table_categories (cat_id medint, cat_title varchar) 2. table_topics (topic_id medint, forum_id smallint topic_title varchar topic_poster medint) 3. table_forums (forum_id smallint, cat_id medint, forum_name varchar) 4. table_posts (post_id medint, topic_id medint, forum_id smallint, poster_id medint) 5. table_posts_text (post_id medint, post_subject varchar, post_text text) 6. table_users (user_id medint, username varchar)
View Replies !
Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given. I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at. The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images. SELECT pic_id WHERE pic_id < $current_pic_id ORDER BY pic_id DESC LIMIT 3 I need the results to show like this: oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic I've got the new pics sorted out but the old pics are causing a little problem... So basically, is there a way that I can reverse the results of my query?
View Replies !
Mixed ORDER BY Query Results
Is there a way to order a query result a specific way? For instance, I'm querying using WHERE id IN(23, 25, 19) I would like the results to stay in that order instead of by ASC or DESC. Is this possible?
View Replies !
Case Insensitive Query Results?
I've been noticing that the MySQL 4.1.19 server on a client's Web server returns case-insensitive results for SELECT statements when using PHP's PDO extension. I didn't notice this happening on my XP dev server with MySQL 5.0, and have never seen it happen on any other server. Does anyone know why this happens and what can be done about it?
View Replies !
Limiting Results In Query Question
I've got a SQL query which i want to return one result from each development in the database. The result i want to be returned is the first image in the database related to a development. There are currently two developments in my database however all the images that are stored in the database are returned and not one per development. QUERY: select development_images.development_id, developments.title, developments.description, development_images.url, development_images.image_title from developments, development_images WHERE developments.id = development_images.development_id order by developments.id DESC LINK: http://demo2.pixel-room.net/developm...dex.php?page=1
View Replies !
Combine All Query Results In One Column
I got a query similar to that: Code: SELECT c_01, c_02, c_03, c_04, c_05 FROM table WHERE c_01 != '' OR c_02 != '' OR c_03 != '' OR c_04 != '' OR c_05 and I get results similar to: Code: +---------+ ---------+ ---------+ ---------+ ---------+ | c_01 | c_02 | c_03 | c_04 | c_05 | +---------+ ---------+ ---------+ ---------+ ---------+ | 0 | 0 | 0 | 0 | data_06 | | data_01 | data_02 | 0 | data_04 | 0 | | 0 | 0 | data_03 | data_05 | 0 | | data_07 | 0 | 0 | 0 | 0 | +---------+ ---------+ ---------+ ---------+ ---------+ But the result I need is something like: Code: +---------+ | c_ALL | +---------+ | data_01 | | data_02 | | data_03 | | data_04 | | data_05 | | data_06 | | data_07 | +---------+ I just can't figure out how to combine several column in 1 column. I could loop through everything in PHP but that data is also sometimes redundant so I'd like use GROUP BY first before it gets to PHP. I have no control over the architecture of the DB...
View Replies !
Query Exact Results And The Wildcard
I need to run a query that pulls 100% accurate data. For this I need to use the "equal" rather than "LIKE". To throw a monkey wrench in the equation, not all fields are mandatory. When the user does not choose to search by a field, I simply want the query to treat it as ALL. Normally I would use the wildcard for this, but the wildcard only works on LIKE. If I use EQUAL, MySQL wants to match the wildcard which of course returns no records. EXAMPLE DETAILS: Variables are retrieved and sanitized from POST. For the sake of this example, I will keep it simple. Each variable collects a the POST if it exists, and if it does not exist it will set the default to "%" (the mySql wildcard). The variables are as follows: $city, $state, $gender The Query would be: SELECT * FROM users WHERE city = '$city' AND state = '$state' AND gender = '$gender'
View Replies !
Navigate Pages By First Two Letters Of Name
I am creating a PHP/MySQL application (if you can call it that). The purpose of this part of the project is to display all of the records in the database ordered by first name. Right now I am selecting each record according to the first letter of their name: A, B, C, etc using a URL parameter. The flaw with this method is when there are only a few records for any given letter, and there are empty queries for letters like Z and X, but not all of the time, and as the list expands I wouldn't want to be changing the navigation. I believe a better method would be to create the query so that you navigate through the pages of the total records (ordered alphabetically ASC) by the first two letters of the first and last records on each page. I'm not advanced enough yet to know exactly how to do this and I was hoping I could get some help. I hope I am explaining this well enough. The other information that would help you help me: For each record, there is a "name_first" field which is what I need the first two letters from. There are three tables I am getting this information from: "men", "women" and "children". There are other variables in the query: "online" and "categoy". The query is currently: $query_men = sprintf("SELECT * FROM men WHERE name_first LIKE '%s%%' AND online LIKE '%%Y%%' AND category LIKE '%%Men%%' ORDER BY name_first ASC", $colname_men);
View Replies !
Pages Are Created On The Fly By Rows...
I have a MySQL database and I want to echo one field (Title) into a page so it can act as a link to that row. When someone clicks the title, I want that row's information to come up. For example Example Title Example Title 2 Example Title 3 User clicks Example Title 2, Example Title 2 | Example Author 2 Example Description. To be displayed. I know this requires "index.php?=". I just don't know what its called and I can't seem to find any tutorials about it. How is it done?:(
View Replies !
Connection :: Pages Slow
we have end site layout (really fast and made with css+xhtml) so we have insert the mysql connection The page load seems very very slow at middle of page. Page is load within middle, after there are pause of 1/2 second and after load continue .. we don't have meet this problem so, we have comment all last row code.. the row that make problem is mysql connection: $conn = mysql_connect($_CONFIG['host'], $_CONFIG['user'], $_CONFIG['pass']) or die('Impossibile stabilire una connessione'); mysql_select_db($_CONFIG['dbname']); Have you any idea?
View Replies !
Displaying Certain Dates For Certain Pages?
I am wanting to display certain dates on certain pages. For example: newsletters.php January 2007 Monday 15th January 2007 // link to another page to display the newsletter Tuesday 16th January 2007 // link to another page to display the newsletter February 2007 Saturday 10th February 2007 // link to another page to display the newsletter Sunday 11th February 2007 // link to another page to display the newsletter
View Replies !
Retreiving Large Query Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy. This means that records from the subsequent queries might have records that already appeared in the first query or that some records will be left out. For performance issues it is a problem to add a unique secondary sorting criteria (like id) to the query. Is there a clean way to force MySQL to relate to the first (initial) query result set?
View Replies !
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.
View Replies !
Running A 'Distinct' Query Returns Some Results TWICE !?
I've spent hours trying to find out why a perfectly simple query that uses the 'distinct' keyword such as: Select distinct(field1) From tablename; works fine based on eg. field1, but when running it on eg. field2 in the very same table, it returns 2 results (yet, only on some words!). Pls see example of result below: Eg of a 'faulty' result: .....
View Replies !
Query To Ignore Results If Condition True
I am having trouble defining a query that ignores rows if a query if true IE. Column 1 = 1,2,3,4,5 Column 2 = a,b,a,a,c For example: I search for results from column 1 and if '3=a', ignore all 'a' rows thus returning the result b=>2, c=>5.
View Replies !
How To Concat Sub-query Results In A Single Cell
I have an user table, a product table and a product_affect_user table. +------+------------------+ | user | other column.... | +------+------------------+ | 6478 | firstname, lastn | +------+------------------+ +------+-------+ | prod | descr | +------+-------+ | 0932 | blaah | +------+-------+ +------+----------+ | user | product | +------+----------+ | 6478 | 923 +------+----------+ That I try to do is to fetch all product affected to an user in the same cell, something like that: +------+------------------+ | user | affected product | +------+------------------+ + 6478 | 1,45,324,673,923 | +------+------------------+
View Replies !
Create URL From To Database Fields In Query Results
I have a MySQL database with table that works as document library. Table keeps details about docs on our server. There are bunch of fields but the key ones here are FileName, URL and location. I'm using PHP to work with the MySQL Database hosted on a Mac OS X Server. Basically, I have a query set up to pull certain records based on a field called Keywords. On the result page I want a field (or item) that combines the URL field and Filename field that ultimately points to the phycial location of the file. For every record thr url field contents is the same since the physical location of the files are in the same directory on the server. Right now the URL field has http://www.mysite.com/docs/. I'd like the result page to have the filename shown, but that filename would be a link to URL+filename. Is there a way to do this? .....
View Replies !
|
TRACKER
