Trying to identify row number

[Vel]

Hi guys, I'm new to php and mysql and loving it so far, however have stumbled upon a query that google hasn't been able to solve. First off MYSQL version reads as 5.0.92-log Protocol Revision 10. I have created a queue, where characters (avatars in a game if you will) are stored in a table called queue. Queue has 6 columns, key (unique ID), charName, charID, shipType, inviteSent, inviteAccepted. I'm trying to identify what position in the queue they are in, but only with people of the same ship type (I'd like to keep the queue to 1 database for all ship types if possible, but can split to 1 table per ship type if it won't work). After a little googling I came up with this code:

<?php ...
if($_SESSION['charIsInQueue'] == TRUE) { //Check queue flag
	$charShipType = $_SESSION['charShipType'];
	$rowNumber = mysql_query("SELECT @rank:=@rank+1 AS rank, inviteSent, inviteAccepted COUNT(*) as charId 
	FROM queue WHERE shipType = '$charShipType';") or die("Error: " . mysql_error()); //Get row number
	$sqlQuery = mysql_query("SELECT * FROM queue"); //Set sql query
	$numberOfRows = mysql_num_rows($sqlQuery); //Count total number of rows
	echo "You are number " . $rowNumber . " of " . $numberOfRows;
}

However this throws up the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(*) as charId FROM queue WHERE shipType = '1'' at line 1

 

I have no idea what the code is even doing, let alone where to begin to get it working. If someone could explain what is happening as well as the correct code needed I'd be very grateful.

[requinix]

Don't use the order of rows in the table to determine age. Have a dedicated date/time field, for when they entered the queue, and make your query ORDER BY it.

 

As for the error, I think you're missing a comma. But having a COUNT(*) in there doesn't make sense, so maybe you should be getting rid of that instead.

[Vel]

One quick question with that, will CURRENT_TIMESTAMP use the servers timestamp or request it from the browser's local machine?

[Vel]

Editing it has gotten rid of the error, but is not giving the expected result. The code now reads:

<?php ...
if($_SESSION['charIsInQueue'] == TRUE) { //Check queue flag
	$charShipType = $_SESSION['charShipType'];
	$rowNumber = mysql_query("SELECT @rank:=@rank+1 AS rank, entryTime, charId, inviteSent, inviteAccepted 
	FROM queue WHERE shipType = '$charShipType ' ORDER BY entryTime;") or die("Error: " . mysql_error()); //Get row number
	$sqlQuery = mysql_query("SELECT * FROM queue"); //Set sql query
	$numberOfRows = mysql_num_rows($sqlQuery); //Count total number of rows
	echo "You are number " . $rowNumber . " of " . $numberOfRows;
}

And the page output is: You are number Resource id #10 of 3

[mens]

One quick question with that, will CURRENT_TIMESTAMP use the servers timestamp or request it from the browser's local machine?

 

Server time.

 

But as said, use timestamps for age of entries; you could also set a trigger to update the time of the entry automatically.

Creating a field called "date_added", and assigning it as INT(integer) with eight to ten lengths.

 

Only then, would your query be:

SELECT `charId` FROM queue WHERE shipType = '$charShipType ' ORDER BY entryTime

...and don't use the MySQL delimiter(;), it will spew errors as PHP doesn't support multiple queries in that fashion.

Also, you then need to select it: eg.

$rowNumber = mysql_fetch_assoc($rowNumber);/php]

That would return an array of charId[i]s[/i], ordered by the time they were inserted.

 

[Vel]

One quick question with that, will CURRENT_TIMESTAMP use the servers timestamp or request it from the browser's local machine?

 

Server time.

 

But as said, use timestamps for age of entries; you could also set a trigger to update the time of the entry automatically.

Creating a field called "date_added", and assigning it as INT(integer) with eight to ten lengths.

 

Only then, would your query be:

SELECT `charId` FROM queue WHERE shipType = '$charShipType ' ORDER BY entryTime

...and don't use the MySQL delimiter(;), it will spew errors as PHP doesn't support multiple queries in that fashion.

Also, you then need to select it: eg.

$rowNumber = mysql_fetch_assoc($rowNumber);/php]

That would return an array of charId[i]s[/i], ordered by the time they were inserted.

 

Thanks for the info, however I don't want it to return an array of charId's, only the position in the queue of the current character.

[mens]

This is ..."different"... and poor,, but it should suffice for your needs.

<?php ...
if($_SESSION['charIsInQueue'] == TRUE) { //Check queue flag
	$charShipType = $_SESSION['shipType'];
	$rowNumber = mysql_query("SELECT `shipType` FROM queue ORDER BY entryTime;") or die("Error: " . mysql_error()); //Get row number
	$sqlQuery = mysql_query("SELECT * FROM queue"); //Set sql query
	$numberOfRows = mysql_num_rows($sqlQuery); //Count total number of rows
$i = 0;
while($row = mysql_fetch_assoc($rowNumber)) {
$i++;
if ($row['shipType'] == $charShipType) break;
} 

	echo "You are number " . $i . " of " . $numberOfRows;
}

?>

 

Sorry it's so messy.

[Vel]

Mess was OK, it was enough to allow me to complete it and get it working . Thank you for the help. For anyone interested final code to get this working was:

<?php ...
if($_SESSION['charIsInQueue'] == TRUE) { //Check queue flag
	$charShipType = $_SESSION['charShipType'];
	$sqlSelectQueue = mysql_query("SELECT @rank:=@rank+1 AS rank, entryTime, charId, inviteSent, inviteAccepted 
	FROM queue WHERE shipType = '$charShipType ' ORDER BY entryTime") or die("Error: " . mysql_error()); //Set sql to load queue
	$numberOfRows = mysql_num_rows($sqlSelectQueue); //Count total number of rows
	$rowNumber = 0;
	//Increase row number and check to see if this is the character, if not repeat.
	while($getSelectQueue = mysql_fetch_assoc($sqlSelectQueue)) {
		$rowNumber++;
		if($getSelectQueue['charId'] == $_SESSION['charId']) //Check if this is the character
			break;
	}
	echo "You are number " . $rowNumber . " of " . $numberOfRows;
}

 

Also, because I'd like to learn, can someone please explain what @rank=@rank+1 AS rank is for?

[kickstart]

Hi

 

@rank:=@rank + 1 is incrementing a variable within SQL.

 

Might be able to do it without the loop:-

 

mysql_query("SELECT rank, entryTime, charId, inviteSent, inviteAccepted FROM (SELECT @rank:=@rank+1 AS rank, entryTime, charId, inviteSent, inviteAccepted FROM queue WHERE shipType = '$charShipType ' ORDER BY entryTime) subselectname WHERE charId = ".$_SESSION['charId']);

 

All the best

 

Keith

[Vel]

Hi Keith, thanks for the extra info. How do I get the result out? If I try outputting the query directly I get 'Resource id #10', if I use mysql_fetch_row I get the results in an array, and would expect 0 to be the rank, however 0 is empty.

[Vel]

Bump, can anyone tell me how to get the rank out?

[Muddy_Funster]

Assuming you kept the same variables, does this not work?

while($getSelectQueue = mysql_fetch_assoc($sqlSelectQueue)
$rank = $getSelectQueue['rank'];

[Vel]

Nope, that still comes back with nothing in rank.

[Muddy_Funster]

Try a

print_r($getSelectQueue)

to see what it's got in it.

[Vel]

Nothing again.

[Muddy_Funster]

nothing in the array at all? 

 

could you post up your new code with the update query in it?

[Vel]

Sure, although I'm thinking it may just be easier to go back to the loop in the php code to identify the position in the queue.

<?php
if($_SESSION['charIsInQueue'] == TRUE) { //Check queue flag
	$charShipType = $_SESSION['charShipType'];
	$sqlSelectQueue = mysql_query("SELECT rank, entryTime, charId, inviteSent, inviteAccepted
	FROM (SELECT @rank:=@rank+1 AS rank, entryTime, charId, inviteSent, inviteAccepted 
	FROM queue WHERE shipType = '$charShipType ' ORDER BY entryTime) subselectname 
	WHERE charId = ".$_SESSION['charId']) or die("Error: " . mysql_error()); //Set sql to load queue
	$numberOfRows = mysql_num_rows($sqlSelectQueue); //Count total number of rows
	$rowNumber = mysql_fetch_row($sqlSelectQueue);
	//Increase row number and check to see if this is the character, if not repeat.
/*		while($getSelectQueue = mysql_fetch_assoc($sqlSelectQueue)) {
		$rowNumber++;
		if($getSelectQueue['charId'] == $_SESSION['charId']) //Check if this is the character
			break;
	}*/
	while($getSelectQueue = mysql_fetch_assoc($sqlSelectQueue))
		print_r($getSelectQueue);
	echo $rowNumber[0] . "<br>";
	echo $rowNumber[1] . "<br>";
	echo $rowNumber[2] . "<br>";
	echo $rowNumber[3] . "<br>";
	echo $rowNumber[4] . "<br>";
	echo "You are number " . $rank . " of " . $numberOfRows;
}

Output is:

 

2011-04-20 00:28:45

359320675

0

0

You are number of 1

[kickstart]

Hi

 

That SQL will only bring back a single row which should have the users rank, hence you checking the number of rows isn't useful to get the total (could be modified to bring that back as well or you can go back to using a separate select for that)

 

Also, sorry but you need to initialise @rank as well.

 

Give this a try (not tested so please excuse any typos)

 

<?php
if($_SESSION['charIsInQueue'] == TRUE) 
{ //Check queue flag
	$charShipType = $_SESSION['charShipType'];
	$sqlSelectQueue = mysql_query("SET @rank:=0;SELECT rank, entryTime, charId, inviteSent, inviteAccepted, TotalRecs
	FROM (SELECT @rank:=@rank+1 AS rank, entryTime, charId, inviteSent, inviteAccepted 
	FROM queue WHERE shipType = '$charShipType ' ORDER BY entryTime) subselectname 
	INNER JOIN (SELECT COUNT(*) AS TotalRecs FROM queue) subselectname2
	WHERE charId = ".$_SESSION['charId']) or die("Error: " . mysql_error()); //Set sql to load queue

	if ($getSelectQueue = mysql_fetch_assoc($sqlSelectQueue))
	{
		echo "You are number " . $getSelectQueue['rank'] . " of " . $getSelectQueue['TotalRecs'] ;
	}
	else
	{
		echo "Sorry you aren't even on the queue";
	}
}
?>

 

All the best

 

Keith