Solved

Mysql find and incrementally number matching rows?

Posted on 2010-11-14
7
267 Views
Last Modified: 2012-06-27
I have a table, 'answers' and a field on that table 'q_id', and another field 'num' (nothing is in 'num' right now).

Is there a way to run a query that will find all rows with matching 'q_id' and number them by putting 1 or 2 or 3 or 4 or 5, ... in the 'num' field?  That way I can identify them even though the q_id is the same.

If possible, can you put it together for me please?

Thank you,  Chris

0
Comment
Question by:St_Aug_Beach_Bum
7 Comments
 
LVL 3

Accepted Solution

by:
xeonol earned 500 total points
ID: 34134856
try
set @tmpid =-1; set @tmp = 1; update answers set num = (select @tmp := if(@tmpid<>q_id, 1, @tmp + 1)), q_id=(select @tmpid := q_id) order by q_id;
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 34135915
Good news - "num" does not appear to be a reserved word.  Still it's not very descriptive so you might want to choose a word with more meaning.
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

This is pidgin code, but hopefully the design pattern will be clear.  It depends on something you did not mention - namely a UNIQUE field (perhaps an autoincrement key).  If you don't already have one of these, use ALTER TABLE to add one.
// PIDGIN CODE
error_reporting(E_ALL);


$old = mysql_query("SELECT q_id, unique_id FROM answers ORDER BY q_id");
$old_qid = '?';

while ($row = mysql_fetch_assoc($old))
{
    if ($row["q_id"] != $old_qid)
    {
        $old_qid = $row["q_id"];
        $num = 1;
    }
    $uql = mysql_query("UPDATE answers SET num = $num WHERE unique_id = '{$row["unique_id"]}' LIMIT 1");
    $num++;
}

Open in new window

0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 34136354
oh, hmmm, I do have a unique field, time_stamp, from the time the questions came in. I can even order by that.

Unfortunitly - I've got to be somewhere in about 20 minutes, so I can't try these solutions out right now. I will later this afternoon though :)

Chris
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 34136868
OK - just make sure that the time_stamp column is immutable - once set, it stays set forever and that it is unique - no collisions whatsoever.  Then if those two conditions are true, you can use it for the unique id
0
 
LVL 4

Expert Comment

by:Fugas
ID: 34137719
You should better add an autoincrement field and base your updates on this fields. It will be more efficient when you have more records in your table.

In oracle there you can select the unique rowid, but this option is not implemented in mysql.
0
 

Author Closing Comment

by:St_Aug_Beach_Bum
ID: 34139101
Thank you xeonol, Ray and Fugas.

Xeonol's solution... I was a little hesitant on that one, looked - to - easy... but I ran it first since he posted the first solution and it ran right, with each matching row being numbered correctly, and on the first try!

Thank you!

0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 34139115
And thank you Fugas and Ray for the extra tips on the use of 'num' and incrementing.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP Curl to output a url 7 46
jQuery force form POST 7 44
simple html dom php accessing table/cell  values 4 18
AJAX, If No returns don't let the AJAX Call run again 2 20
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question