在PHP中，如何将MYSQL行数减为特定行?

I have a table that acts as a dropbox for files that get automatically queued.

我有一个表格，作为一个dropbox文件自动排队。

I order the priority of files based on two things, the date and time, and if it has been verified or not.

我根据两件事，日期和时间，以及它是否被验证，来确定文件的优先级。

Here is my query:

这是我的查询:

$dropcount = mysql_query("SELECT COUNT(*) FROM queue WHERE status=2 OR status=3 
OR status=4 ORDER BY authorised DESC, timestamp DESC;");

What I need to do is count how many rows are ahead of the users current file because this will count every row, including the files behind the current users as well.

我需要做的是计算用户当前文件前面有多少行，因为这将计算每一行，包括当前用户后面的文件。

Thank you.

谢谢你！

My table looks like this:

我的桌子是这样的:

http://tabbidesign.com/table.png

The way this works is if a file is added its added to the queue. However if that file is authorised, it take priority over non verified files which means it needs to be counted even though it was added after some of the non verified flies.

这种方法的工作原理是如果一个文件被添加到队列中。但是，如果该文件被授权，它将优先于未经验证的文件，这意味着它需要被计数，即使它是在一些未经验证的苍蝇之后添加的。

The status column states if the file is: -Being processed

状态列状态，如果文件是:-正在处理。

-Queueing in the dropbox

dropbox的排队

-Cancelled by the user

用户取消了

-Cancelled by the admin

取消的管理

EDIT: Ok @curtisdf's answer would work if I did the following:

编辑:Ok @curtisdf的回答如果我做了以下的事情将会奏效:

$countauth =
SELECT COUNT(*)
FROM queue
WHERE (status=2 OR status=3 OR status=4)
AND authorised=1
AND timestamp < $timestamp

$countnorm =
SELECT COUNT(*)
FROM queue
WHERE (status=2 OR status=3 OR status=4)
AND authorised=0
AND timestamp < $timestamp

$actualcount = $countnorm + $countauth;

How could I merge this into one query, or is this not possible?

我如何将它合并到一个查询中，或者这是不可能的?

THE SOLOUTION

的SOLOUTION

SELECT COUNT(*)FROM queue WHERE (status=2 OR status=3 OR status=4)AND 
timestamp < $timestamp OR authorised=1

Found by @curtisdf.

由@curtisdf发现的。

2 个解决方案

#1

Assuming your idqueue column is the primary key and it's auto-incremented, what about using it to filter out later ID's? Something like:

假设idqueue列是主键并且它是自动递增的，那么使用它来过滤后面的ID呢?喜欢的东西:

SELECT COUNT(*) 
FROM queue 
WHERE status=2 OR status=3 OR status=4 
AND idqueue < $currentFileId
ORDER BY authorised DESC, timestamp DESC

EDIT: It's a little difficult to know how to answer because your table has so many columns and it's not clear what you're using all of them for. What does the status column mean, for example, in contrast to the authorized column? But anyway, since your ORDER BY doesn't matter for this query, and since you're looking for authorised files that came in prior to a given file, how about this:

编辑:知道如何回答有点困难，因为您的表有很多列，而且不清楚您使用它们的目的是什么。状态列与授权列相比意味着什么?但是不管怎样，既然你的订单对这个查询无关紧要，既然你在寻找在给定文件之前的授权文件，那么这个呢?

SELECT COUNT(*)
FROM queue
WHERE (status=2 OR status=3 OR status=4)
AND authorised=1
AND timestamp < $timestamp

EDIT 2: Okay, now I see what you're after. :-) If you just removed the "AND authorised=X" parts, I believe you could do it in one query. This assumes your authorised column only would have values of 0 or 1. But if it has more than that, then you could do it by wrapping the WHERE ... AND ... parts of each query in their own parentheses, like this:

编辑2:好的，现在我知道你想要什么了。:-)如果你只是删除了“和authorized =X”部分，我相信你可以在一次查询中完成。这假定您的授权列的值只有0或1。但是如果它有更多的东西，那么你可以把WHERE……和…每个查询的部分在它们自己的圆括号中，比如:

SELECT COUNT(*)
FROM queue
WHERE (
    (status=2 OR status=3 OR status=4)
    AND authorised=1
    AND timestamp < $timestamp
) OR (
    (status=2 OR status=3 OR status=4)
    AND authorised=0
    AND timestamp < $timestamp
)

This could be simplified even further:

这可以进一步简化:

SELECT COUNT(*)
FROM queue
WHERE (status=2 OR status=3 OR status=4)
AND timestamp < $timestamp
AND (authorised=1 OR authorised=0)

#2

You need to add in a where statement:

你需要加上一个where语句:

$dropcount = mysql_query("SELECT COUNT(*) FROM queue WHERE (status=2 OR status=3
OR status=4) AND authorised=1 and timestamp<'$currentUserTimeStamp'  ORDER BY 
authorised DESC, timestamp DESC;");

If you don't know the current user timestamp, you'll need to select that first.

如果您不知道当前的用户时间戳，则需要先选择它。

$result = mysql_query("select `timestamp` from queue where (status=2 or status=3 or 
status=4) and `userid`='$user'");
$row = mysql_fetch_row($result);
$currentUserTimeStamp = $row[0];

Of course, I've made up the column name for the user id.

当然，我已经为用户id创建了列名。

#1