Use the result of one Mysql command in Another Mysql Command??

Question

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.

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

Answer 1

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):

//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'];

Answer 2

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

Answer 3

//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

Answer 4

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

Answer 5

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

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)";

Answer 6

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'];

Answer 7

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.

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