Page 1 of 1

Query create index

Posted: Sun Sep 18, 2005 2:52 pm
by hawleyjr
I have the following table:

ID | COMPANY_ID | EVENT_ID


Each are numeric, ID is auto inc.

I'm trying to use the following query:

Code: Select all

SELECT ID,COMPANY_ID,EVENT_ID FROM my_table WHERE COMPANY_ID = 2

Code: Select all

Data in Table
ID	COMPANY_ID	EVENT_ID
1		1			3
2		1			4
3		2			3
4		1			1
5		2			3
6		3			3
7		2			1
Result would be:

Code: Select all

ID	COMPANY_ID	EVENT_ID
3		2			3
5		2			3
7		2			1
Without creating another field. Is it possible to get a numeric counter to count for each occurrence?

*Field COUNTER would increment with each row.

My desired result:

Code: Select all

ID	COMPANY_ID	EVENT_ID	COUNTER
3		2			3				1
5		2			3				2
7		2			1				3

Posted: Sun Sep 18, 2005 10:31 pm
by feyd
why not count it with php?

Posted: Sun Sep 18, 2005 11:48 pm
by s.dot
Hmm, I'm a bit confused by your desired result. mysql_num_rows() or using GROUP BY in your query should provide you with a 'counter' of each occurence, depending on how you wanted them counted.

Posted: Mon Sep 19, 2005 1:05 am
by hawleyjr
The end page I wanted was to display a result such as....

Code: Select all

$qry = "SELECT ID,COMPANY_ID,EVENT_ID,COUNTER  FROM my_table WHERE COMPANY_ID = 2 and EVENT_ID = $x";

$qry = "SELECT count(ID) as total FROM my_table WHERE COMPANY_ID = 2 and EVENT_ID = $x";
//field total is used as variable $total below
In my display:

Code: Select all

echo "Event is $event_id <br />"; //$event_id  from above query

//echo Next or previous...
if($counter > 1)
 echo '<a href="javascript:viewEvent('.($counter-1).');">Previous</a><br />';

if($counter < $total )
 echo '<a href="javascript:viewEvent('.($counter+1).');">Next</a><br />';

<script language="javascript">

function viewEvent( eventID ){

//js to call next query
}

</script>