在另一个Mysql命令中使用一个Mysql命令的结果？

Excuse my ignorance but I'm having a tough time figuring this out.

请原谅我的无知,但我很难搞清楚这一点。

I'm trying to take the result from one mysql command, and use it in another command.

我试图从一个mysql命令获取结果,并在另一个命令中使用它。

Here's my code, it doesnt work.

这是我的代码,它不起作用。

//select the event end date of event ID
$sql = "SELECT enddate FROM mm_eventlist_dates WHERE id = $id";
$result = mysql_query($sql);

//plug in the event end date, find event that starts the next day
$sql = "SELECT id FROM mm_eventlist_dates WHERE startdate = date_add($result, INTERVAL 1 DAY)";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
echo "Next Event ID" . $row['id'];

I'm so lost.

我迷路了。

Please help!

Thanks, Nick

7 个解决方案

#1

If I understand what you're trying to accomplish, it looks like you want to find all the events that start the day after a given event. Correct? In that case, what you want to do is a self-join, that is, join a table to itself. You need to give at least one occurrence of the table an alias so SQL can tell them apart.

如果我理解你要完成的任务,看起来你想要找到在给定事件发生后一天开始的所有事件。正确?在这种情况下,您要做的是自联接,即将表连接到自身。您需要为表至少出现一个别名,以便SQL可以区分它们。

So maybe something like this:

也许是这样的:

SELECT e2.id
FROM mm_eventlist_dates e1
join mm_eventlist_dates e2 on e2.startdate = date_add(e1.enddate, INTERVAL 1 DAY)
where e1.id=$id

#2

Is there a reason that you can't combine them in to one query?

有没有理由不能将它们合并到一个查询中?

SELECT m1.id FROM mm_eventlist_dates m1  
JOIN mm_eventlist_dates m2 ON m1.startdate = date_add(m2.enddate, INTERVAL 1 DAY)  
WHERE m2.id = $id

#3

mysql_query() returns a result set, not an actual database item. To do what you want above, do something similar to this (doesn't include error checking etc):

mysql_query()返回结果集,而不是实际的数据库项。要做你想做的事情,做一些类似的事情(不包括错误检查等):

//select the event end date of event ID
$sql = "SELECT enddate FROM mm_eventlist_dates WHERE id = $id";
$result = mysql_query($sql);

$enddateRow = mysql_fetch_array($result);

//plug in the event end date, find event that starts the next day
$sql = "SELECT id FROM mm_eventlist_dates WHERE startdate = date_add('" . $enddateRow["enddate"] . "', INTERVAL 1 DAY)";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
echo "Next Event ID" . $row['id'];

#4

You cannot use $result directly in date_add. Call mysql_fetch_array (as you do a few lines later), and use $row['enddate'].

您不能直接在date_add中使用$ result。调用mysql_fetch_array(之后你会做几行),并使用$ row ['enddate']。

#5

//select the event end date of event ID
$sql = "SELECT enddate FROM mm_eventlist_dates WHERE id = $id";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$enddate = $row['enddate'];

//plug in the event end date, find event that starts the next day
$sql = "SELECT id FROM mm_eventlist_dates WHERE startdate = date_add($enddate, INTERVAL 1 DAY)";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
echo "Next Event ID" . $row['id'];

I think

#6

You can not use the result of mysql_query directly in another query, you need to fetch the value first.

您不能在另一个查询中直接使用mysql_query的结果,您需要先获取该值。

Instead of

$result = mysql_query($sql);

//plug in the event end date, find event that starts the next day
$sql = "SELECT id FROM mm_eventlist_dates WHERE startdate = date_add($result, INTERVAL 1 DAY)";

Try

   $result = mysql_query($sql);
   $enddate = mysql_fetch_assoc($result);

    //plug in the event end date, find event that starts the next day
    $sql = "SELECT id FROM mm_eventlist_dates WHERE startdate = date_add($enddate, INTERVAL 1 DAY)";

#7

try this

//select the event end date of event ID
$sql = "SELECT enddate FROM mm_eventlist_dates WHERE id = $id";
$result = mysql_query($sql);

$row = mysql_fetch_assoc($result)
//plug in the event end date, find event that starts the next day
$sql = "SELECT id FROM mm_eventlist_dates WHERE startdate = date_add(".$row['enddate'].", INTERVAL 1 DAY)";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
echo "Next Event ID" . $row['id'];

#1