Get The Row Number
I have a site that allows searching for names in an address book. Search terms typed by the user become elements in a where clause for mySQL. So, if you search for "Jason", the query would be: (select * from addresses where first like '%Jason%'). Paginating the results to be displayed n at a time on a page is handled by php.
However, what if I want this: to search for "Jason" and have ALL the records in the records in the address book returned, but to jump to the first page that contains "Jason". In other words, instead of filtering the results with a search, I want ALL the results and the STARTING POSITION in the list of the result I'm interested in. Can this be done with mySQL? Can I form a query that would give me ALL the address book records (select * from addresses), while at the same time giving me the position in the list of ALL records that matches the first row from a search (select * from addresses where first like '%Jason%')
I can think of a convoluted way to do it in php, but I'd much rather do it with mySQL.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How To Add Count Number Of Words With Showing Limited Number
SELECT SUBSTRING_INDEX(classDescription,' ',13) as classDescription, listing.inv_company, listing.inv_addr2, listing.inv_state, listing.classcatid, listing.industryid, listing.listingID, listing.inv_country, listing.adscreation_date
View Replies !
View Related
Formatted Number Column Cannot Be Sorted By Number
Columns come in and are built based on XML doc and the HTML (smarty mysqllate) does not know weather this is a number to format it nicely such as 2345.993 to 2,346.99. So I do my formatting in the XML which gives the dynamic query developer something similar this: FORMAT(price * qty,2) AS sales All was fine and dandy until i wanted to sort it by number. Turns out Format makes it as string and it does a string sort, in other words it would sort it like this: 1223 21 90 So a genius (/sarcasm) told me to "add a zero" to it and it would work. it turned 2,345.00 into 2.00. (had to explain to the client why his biggest sale was showing up as 2.00) Now i've converted it to ROUND() but is there any other way to have the comma (thousands delimiter) as well has have mySQL treat it as a number?
View Replies !
View Related
Number Of Row
I have 2 columns. When I do sql query: SELECT username FROM users ORDER BY level + 0 DESC so it returns me something like this: username ----------- level john------------------99 paul------------------90 tom------------------80 nick------------------60 I want php to tell me at what row specific user is located in. Let's say I choose user JOHN, so php would tell me 1 because john has the highest level, so he's the first in the sorted. If I were to choose nick, php would tell me #4
View Replies !
View Related
ID Number
I have a field called post_titles and the primary key is called post_id. Let's say I want to retrieve 5 random entries from this table, but I want to be able to sort the 5 entries by their post_id. How can I do so? I've tried the following query but it doesnt work. $sql2 = "select * from postings order by RAND() order by post_id DESC";
View Replies !
View Related
Getting To Row Number
if there's a way to returning the row number of a particular row based from a WHERE clause. So if I were to do a "SELECT * FROM dbname WHERE id='foo'" how can I find out what row number it is using a different SQL query? Or maybe a PHP function?
View Replies !
View Related
How To Get The Row Number
I'm new to MySQL, I started two weeks ago. I always run in to problems, everyone does, but I often find the solutions via Google. This time I didn't, I've tried to find a solution for more than an hour now but without success. It's a simple problem, how do you get the row number from a mysql_query() result? This is my code (PHP), stripped down a little: $result = mysql_query( "SELECT * FROM articles WHERE id='$id'" ); mysql_result( $result, <need the row number here>, "name" ); I can't just use the id field, because when you delete a row, the rows are moved up but the id value isn't changed.
View Replies !
View Related
Getting A Non-zero Number
A table has a series of fields (e.g. id1, id2, id3, id4, etc) that can contain an unsigned integer. The default value for these fields is 0. I am trying to find some way to use built in MySQL functions to determine which is the last field in that series to have a non-zero number and return that number within the results instead of having to do it in the results using a PHP loop. In other words, if id4 is 0 and id3 is greater than zero then I want id3 but not id2 or id1 regardless of their values, and so on and so forth.
View Replies !
View Related
Row Number
I am using PHPmyAdmin. If I create a table and the rows x,y,z.. How do I know what row number x,y and z is? I have a update script but it work with the rows? So I need to know the rows numbers.
View Replies !
View Related
Auto Number With My SQL
I have just moved over to mysql as the back end and keeping access as front end. My database is for a ordering system. I use autonumber as the Order Number. as mysql does not display the order number (autonumber) until the form has been saved. Is there a way on the order form that i can have a button that will save the data then reopen the form so the order number ( autonumber is displayed).
View Replies !
View Related
Progressive Number
Consider two tables: book(id, title, date_written , author_id) author(id, name) the date_written field is a DATE field ad rappresents the date the book was written. i would like to make a query like this: SELECT name , title , date_written FROM book INNER JOIN author ON ( book.author_id = author.id ) ORDER BY author.id , time_written the result would be King , Running Man , 1978-9-12 King , Salem's Lot , 1980-3-29 [...] Shakespeare, Romeo and Juliet , 1214-6-13 Shakespeare , Amlet , 1216-11-6 Shakespeare , ... , 1226-5-22 [...] but I would like to add a column with the "sequence number" of the book, related to the author, like King , Running Man , 1 , 1978-9-12 King , Salem's Lot , 2 , 1980-3-29 [...] Shakespeare, 1 , Romeo and Juliet , 1214-6-13 Shakespeare , 2 , Amlet , 1216-11-6 Shakespeare , 3 , ... , 1226-5-22 [...]
View Replies !
View Related
Row Number In SELECT
I currently have a fairly complex query that returns a variable number of rows, sorted by various criteria. I am trying to insert the result of this query into an additional table, along with a row number. Ideally, I would like to be able to do this with an INSERT SELECT statement, although I'm not sure quite how to achieve this. An example of what I'm trying to achieve might be: INSERT INTO tblfinishingpositions (raceid, personid, position) SELECT raceid, personid, XXXXXXXXX FROM tblfinishingtimes WHERE raceid = XYZ At present, I'm just reading the rows one at a time, and issueing multiple insert statements, but I would rather have something all server side if possible. I have considered usign a temporary HEAP table, with an autonumber field, but this seems like a bit of a kludge, and I would prefer a single statement to do the whole lot. Another problem that I have now overcome by use of a tempory table, but would prefer to tidy is the following:Say one had a table of cars: Manufacturer, Model, Price. How would one answer the question, what is the model of the cheapesest car for each manufacturer. Obtaining the price is obviously very straightforward: SELECT MIN(price) FROM cars GROUP BY manufacturer However, I couldn't find an easy way to find the corresponding model. In the end, I had to insert the results into a temporary table, and join back on manufacturer, and price (or their equivalents, which can be assumed to be unique)
View Replies !
View Related
Number Values
I need to add up the total of the value of people. In other words if Family 1 = 3 people Family 2 = 2 people I need total 5 people PHP Code: $result1 = mysql_query( "SELECT *, DATE_FORMAT(time,'%b %d, %r') AS time FROM rsvp ORDER BY people DESC "); $number_people = mysql_fetch_array( $result1 );
View Replies !
View Related
Adding Up Number
I have a diary table that looks like this did | dcompanyid | duid | ddate | dtime | dtimefinish did = p key dcompanyid = the companies unique id duid = the users unique id ddate = date format dtime = time format dtimefinish = time format I want to add up the number of hours in a particular day, or week an employee works for. so if he worked betweek 9 and 12, 12.30 and 3, 4 and 7 he would work 8.5 hours. I can do this using php and mysql but was wondering if it can be done soley in mysql.
View Replies !
View Related
Random Number
I have a list of email addresses in a text file. I want to create a table and have them added to a new table with 3 columns/fields - ID, Email, and UID ID - Integer - auto increment Email - varchar 40 UID - Unique random value 15 characters (varchar) How do I get the UID added such that it is random and unique. Can MySQL do this?
View Replies !
View Related
Counting The Number
I'm having difficulty figuring out how count the number of each distinct value in a column.Say I have a column called 'score' that contains a score value of 1-4, how would I could how many 1s, 2s, 3s and 4s there were in this column?
View Replies !
View Related
Get Number Of Rows
I'm developing a hr system which will display Staff that are currently in the financial year(regardless of resigned or not resigned). Our financial year is from Oct - Sep (Oct 2006 - Sep 2007) For staff that have not resigned (@ the pt of query), the dateTerminated field is NULL. I have a database that stores resignation date and startdate (both columns are date type). I tried with the following query but was not successful. PLease help. Select * from (select name,dateTerminated,dept from user_info,hr_users where user_info.id=hr_users.sn or dateTerminated is null AND (year(dateTerminated)<=2007 AND year(dateTerminated)>=8) )as table2 dunno how to continue ??? order by table2.name ASC
View Replies !
View Related
Number Of Votes
This is how my db looks: id service type ip rating 109 Share Ad Space te 212.123.141.157 4 108 Traffic Syndicate 25 te 212.123.141.157 4 114 Profoundhits as 212.123.141.157 5 118 Profoundhits as 213 5 A user rates a service and for every vote he makes an entry goes into the db. With this: Code: SELECT service, AVG(rating) AS rating FROM rate WHERE type = 'as' GROUP BY service ORDER BY rating DESC LIMIT 3 i get the average but i also want the number of votes and the service with the most votes and the highest rating should be placed 1st. How do i do this?
View Replies !
View Related
Number Of Queries
My team is working on transfering old db data to mysql. Its been configured somehow where it caps at 1 type of query at a time. Anyway to increase this? At one point it did around 700 at one time, but some configuration messed this up, how can we incrase this?
View Replies !
View Related
Limit Number
4671 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.60 mysqld 4672 mysql 8 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4673 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4674 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4675 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.07 mysqld 4676 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4677 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.01 mysqld 4678 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4679 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4680 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.07 mysqld I really don't need all these, they're just eating all my ram. I think 2-3 would suffice for my low-traffic website. Where can I limit the number of processes spawned by mysql?
View Replies !
View Related
Number Format
Does mysql have a builtin number_format function. In the below statement I would like to format 'average' and 'extended' only out 2 decimal places. Is this possible to do within the statement. SELECT cost, qty, cost / qty AS 'average', ( ( cost / qty ) * onhand ) AS 'extended' FROM equipment
View Replies !
View Related
Number Of Times
I have a query that grabs 500 rows from 1 field within 1 table. In this table. Each user will have 500 rows associated to them from within that table. So if there are 2 users then there will be 1000 rows. Each time 1 user executes the query, it grabs their 500 rows. If 2 users execute the query simultaneously, then this means that there are 1000 rows, all of which will be selected; 500 by one user and 500 by the other. If 3 users access the table simultaneously, then there are 1500 rows and so on. My question is how many users can execute this query simultaneously before we see performance loss?
View Replies !
View Related
Number Range
I have weight ranges of 100,300,500,1000. The user will enter the package weight in the form and the query will return the price per pound. How can I select the correct weight in the query?? $weight=400; $getprice="SELECT * from priceMatrix WHERE VENDOR='$vendorArray[$i]' AND SERVICE=$serviceArray[$s] AND WEIGHT<=$weight";
View Replies !
View Related
Number Of Rows
Is there a quick query (without just asking to query the entire database) that will return the number of rows in a given table? I know I can do SELECT * FROM table but I don't really care about the data, I just want to know how many entries there are.
View Replies !
View Related
Number Issue
I have this table: CREATE TABLE `items` ( `no` tinyint(4) NOT NULL auto_increment, `image` tinytext NOT NULL, `title` tinytext NOT NULL, `description` mediumtext NOT NULL, `cost` tinyint(10) NOT NULL default '0', `given` tinyint(10) NOT NULL default '0', `priority` varchar(4) NOT NULL default '', `active` char(1) NOT NULL default '', PRIMARY KEY (`no`) ) TYPE=MyISAM AUTO_INCREMENT=4 ; and I was manually inserting some test data via phpmyadmin, and in the cost item, i put 309, and it switches to 127. Is there something about tinyint that I don't know that would cause this?
View Replies !
View Related
Number In Red Overhead
i am using phpmyadmin to make and configure my databases..but there is often a number in red called "Overhead" can anyone please tell me what this is? and if it is important how to reduce the overhead
View Replies !
View Related
How To See Row Number In Mysql
i am working on mysql platform for a new project. i wanted to know how can i display or see the row no. or the row id. as we have in oracle like "select rownum,ename,dept from employee" or "select rowid,ename,dept from employee" in mysql i am using mysql ver 5.0 with mysql query browser ver 1.1.2. i have table in which there will frequent addtion of row of values and it has unique primary key but the values are not in the incremental order lik 0,1,2 so when the i query the table using the select command the rows are displayed in the order of values not the order in which they were inserted like eg. --------------------------------- name dateofinsertion --------------------------------- ABC 2006-07-15 CDS 2006-07-12 BZS 2006-07-12 XYZ 2006-07-14 so i not able to know which was inserted first if i order by date then if there are more than one values the same qusetion arises, so can any one tell me how can i know the row no. or row id as we have in oracle, and does mysql have option to see that if not is there any other method bu which i can do this.
View Replies !
View Related
Auto Number Id
I need to store the autogenerated Id value used in the insert in another column in the table. Just like the Employee example where the Employee table has a column Manager where a reference is stored to the Employee's Manager which is a record in the same table. When inserting a new Employee the default value for the Manager colomn must be identical to the Id value that was automatically assigned during the insert. Employee(Id,Name,Manager) Insert Into Employee(NAME,MANAGER) Values ("John Doe",THIS_RECORDS_ID_VALUE???) I guess the LAST_INSERT_ID() returns the ID of the insert statement executed prior to this insert? Is this possible?
View Replies !
View Related
Number Of Mysqld
i want to limit the number of mysqld daemons running on my linux system. despite my best efforts to search/find the configuration option for this i haven't been able to find a single bit of info on this topic, including searching the configuration options for the my.cnf file. right now my server is running twelve copies of mysqld, and i only need 3 at the most. i'm sure this is possible, i just don't know how. can anyone help me?
View Replies !
View Related
FileId Name Or Number?
I am creating a database that is going to have a list of all the files associated with a particular porject. At the moment I have a fileId which is an int and a filename column... Should I change the type of the fileId column, which is the primary key, to a char to categorize it? Is there a standard convention for such things?
View Replies !
View Related
How To Return A Row Number?
Using MySQL 5.0 CF8 DW CS3 I guess as usual this is a really simple question requiring a really simple answer but my head has a black hole at the moment so if someone could help me out I would be greatful I have a query: <cfquery name="rstrdt" datasource="cdsl"> SELECT * FROM players ORDER BY winpercentage DESC,played DESC,banktotal DESC </cfquery> the table the query creates has many colums but 2 of the columns are 'player' & 'club thus my question is: how would I return the row number where the player and his club are in the table, the primary key 'playerindex' identifies the player uniquely so that is of no use to me in this instance.
View Replies !
View Related
Number Of Columns
Do you think a table having more columns affects the efficiency when inserting/selecting/deleting? I mean like 100-150 columns for one table. Is it wise to make two tables and spliting the columns or it's better to keep it this way
View Replies !
View Related
Auto_increment To Next Available Number
I have a table with an auto_increment field. It is a simple list of image filenames and each filename has a corresponding id number. If I delete an entry from the table, then insert a new entry, the new entry's id skips one. For example: I delete an entry with the id# 35. I then insert a new entry and auto_increment sets the id# to 36. I would like it to increment the new id# to the next available number (which in this example would be 35).
View Replies !
View Related
Max Number Of Entries
I am having a problem with a query that I have been trying to solve for the past 2 hours and I can't seem to get it. I have a table called Listing with a field MemberID and I am trying to print out the MemberID that occurs the most times in the table. The following query returns the count of each MemberID in the table, all I need to do is get the maximum value out of there: SELECT Listing.MemberID, COUNT(Listing.MemberID) FROM Listing GROUP BY Listing.MemberID; I am trying to do something along the lines of MAX(COUNT(Listing.MemberID)) but that does not seem to work.
View Replies !
View Related
Select A Certain Number
Maybe this is not a common use case but I need it for the following reason: I have a fairly high number of rows in my table, e.g. 1 or 2 million. I am working with Java to handle them. I dont want all this data in my memory at the same time so I am trying to get rows in steps of thousands, keeping the result set of a query small. Do some work with it, and then getting the next 1000 rows. Maybe there is functionality implemented that already handles this. If so please point me in the right direction. Every comment is welcome. Also let me know if this is the wrong forum to post to.
View Replies !
View Related
List And Number
I have two tables in my database: tbl_games tbl_cheats I want to get all the records from tbl_games, and for each game, get the number of records for it in tbl_cheats. In tbl_games the key field is game_id, and in the tbl_cheats table the field cheats_game_id would be the same (for that particular game - if that makes sense).
View Replies !
View Related
Auto Id Number
in a database i have a field for an id number. i've seen in other databases i've updated, where the id automatically fills in the field wiht the next highest number.
View Replies !
View Related
|
TRACKER
