Query create index

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Query create index

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why not count it with php?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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>
Post Reply