SQL Query - Using variable from another SQL Query
SQL Query - Using variable from another SQL Query
am 12.02.2007 17:14:32 von Matthew Ferry
------=_NextPart_000_0026_01C74E96.F8951810
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello Everyone....
Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.
The query "event_time" brings back the calendar id for each event that =
is pending in the future.
ie.... 12, 13, 14, 26 (There could be 100 of them out there)
The second query "events" needs to meet both reqirements. =20
1 - cal_category=3D'501'=20
2 - cal_id=3D a number from the "event_time" query
I think i need to do a loop inside of a loop
Thanks...
Matt=20
Here is my code:=20
$todays_year =3D date("Y");
$todays_month =3D date("m");
$todays_day =3D date("d");
$tstamp =3D mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time =3D mysql_query("SELECT cal_id FROM egw_cal_dates where =
cal_start > $tstamp", $db);
$events =3D mysql_query("SELECT * FROM egw_cal WHERE =
cal_category=3D'501' and cal_id=3D'$event_time'\n", $db);
if ($event =3D mysql_fetch_array($events)) {
echo "
\n";
echo "
\n";
do {
echo "
Size=3D'10'>$event[cal_title] - $=
event[cal_location]\n";
echo "
\n";
echo "$event[cal_description]";
echo "
\n";
echo "
\n";
} while ($event =3D mysql_fetch_array($events));
} else {
echo "No Public Events Are Currently Scheduled...";
}
?>
------=_NextPart_000_0026_01C74E96.F8951810--
Re: SQL Query - Using variable from another SQL Query
am 12.02.2007 17:26:35 von Brad Bonkoski
Matthew Ferry wrote:
> Hello Everyone....
>
> Got a simple / stupid question.
> Worked on this all night. I'm over looking something very basic here.
>
> The query "event_time" brings back the calendar id for each event that is pending in the future.
> ie.... 12, 13, 14, 26 (There could be 100 of them out there)
>
> The second query "events" needs to meet both reqirements.
> 1 - cal_category='501'
> 2 - cal_id= a number from the "event_time" query
>
> I think i need to do a loop inside of a loop
>
> Thanks...
>
> Matt
>
>
> Here is my code:
>
>
>
> $todays_year = date("Y");
>
> $todays_month = date("m");
>
> $todays_day = date("d");
>
> $tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
>
> $event_time = mysql_query("SELECT cal_id FROM egw_cal_dates where cal_start > $tstamp", $db);
>
This returns a mysql result set...not the actual data...
search php.net for the function mysql_fetch_array or others to actually
*get* the data.
(Some good examples there will help you sort this out!)
> $events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db);
>
>
>
> if ($event = mysql_fetch_array($events)) {
>
> echo "
\n";
>
> echo "
\n";
>
> do {
>
> echo "$event[cal_title] - $event[cal_location]\n";
>
> echo "
\n";
>
> echo "$event[cal_description]";
>
> echo "
\n";
>
> echo "
\n";
>
> } while ($event = mysql_fetch_array($events));
>
> } else {
>
> echo "No Public Events Are Currently Scheduled...";
>
> }
>
> ?>
>
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: SQL Query - Using variable from another SQL Query
am 12.02.2007 17:31:31 von Trevor Gryffyn
Try this as your SQL. It should give you all the results, then you can use PHP to sort it all out.
SELECT * FROM egw_cal WHERE cal_category='501' and cal_id in (SELECT cal_id FROM egw_cal_dates where cal_start > $tstamp)
-TG
= = = Original message = = =
Hello Everyone....
Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.
The query "event_time" brings back the calendar id for each event that is pending in the future.
ie.... 12, 13, 14, 26 (There could be 100 of them out there)
The second query "events" needs to meet both reqirements.
1 - cal_category='501'
2 - cal_id= a number from the "event_time" query
I think i need to do a loop inside of a loop
Thanks...
Matt
Here is my code:
$todays_year = date("Y");
$todays_month = date("m");
$todays_day = date("d");
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time = mysql_query("SELECT cal_id FROM egw_cal_dates where cal_start > $tstamp", $db);
$events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db);
if ($event = mysql_fetch_array($events))
echo "
\n";
echo "
\n";
do
echo "$event[cal_title] - $event[cal_location]\n";
echo "
\n";
echo "$event[cal_description]";
echo "
\n";
echo "
\n";
while ($event = mysql_fetch_array($events));
else
echo "No Public Events Are Currently Scheduled...";
?>
___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: SQL Query - Using variable from another SQL Query
am 12.02.2007 18:19:38 von Micah Stevens
This is a join - Read up on them, they're very useful and don't require
the overhead of a sub-query.
SELECT egw_cal.* FROM egw_cal_dates
LEFT JOIN egw_cal using (cal_id)
where egw_cal_dates.cal_start > $tstamp
AND egw_cal.cal_category = '501'
-Micah
On 02/12/2007 08:14 AM, Matthew Ferry wrote:
> Hello Everyone....
>
> Got a simple / stupid question.
> Worked on this all night. I'm over looking something very basic here.
>
> The query "event_time" brings back the calendar id for each event that is pending in the future.
> ie.... 12, 13, 14, 26 (There could be 100 of them out there)
>
> The second query "events" needs to meet both reqirements.
> 1 - cal_category='501'
> 2 - cal_id= a number from the "event_time" query
>
> I think i need to do a loop inside of a loop
>
> Thanks...
>
> Matt
>
>
> Here is my code:
>
>
>
> $todays_year = date("Y");
>
> $todays_month = date("m");
>
> $todays_day = date("d");
>
> $tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
>
> $event_time = mysql_query("SELECT cal_id FROM egw_cal_dates where cal_start > $tstamp", $db);
>
> $events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db);
>
>
>
> if ($event = mysql_fetch_array($events)) {
>
> echo "
\n";
>
> echo "
\n";
>
> do {
>
> echo "$event[cal_title] - $event[cal_location]\n";
>
> echo "
\n";
>
> echo "$event[cal_description]";
>
> echo "
\n";
>
> echo "
\n";
>
> } while ($event = mysql_fetch_array($events));
>
> } else {
>
> echo "No Public Events Are Currently Scheduled...";
>
> }
>
> ?>
>
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: SQL Query - Using variable from another SQL Query
am 12.02.2007 19:43:51 von Matthew Ferry
------=_NextPart_000_004E_01C74EAB.D4927290
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks Everyone...
After I sent that...I got thinking about doing both queries in one =
statement.
So thats what I did.
Its working fine...
Here is the updated code:=20
$todays_year =3D date("Y");
$todays_month =3D date("m");
$todays_day =3D date("d");
$tstamp =3D mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$events =3D mysql_query("SELECT DISTINCT * FROM egw_cal, egw_cal_dates =
WHERE egw_cal.cal_category=3D'501'=20
and egw_cal_dates.cal_start > '$tstamp' and =
egw_cal.cal_id=3Degw_cal_dates.cal_id", $db);
if ($event =3D mysql_fetch_array($events)) {
echo "
\n";
echo "
\n";
do {
echo "
Face=3D'Times'>$event[cal_title] - &nbs=
p;$event[cal_location]\n";
echo "
\n";
$start =3D date('F jS\, Y \a\t g:ia', $event[cal_start]);
echo "Starting Date/Time: $start";
echo "
\n";
echo "
\n";
echo "$event[cal_description]";
echo "
\n";
echo "
\n";
} while ($event =3D mysql_fetch_array($events));
} else {
echo "No Public Events Are Currently Scheduled...";
}
?>
----- Original Message -----=20
From: "Matthew Ferry"
To:
Sent: Monday, February 12, 2007 11:14 AM
Subject: [PHP-DB] SQL Query - Using variable from another SQL Query
Hello Everyone....
Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.
The query "event_time" brings back the calendar id for each event that =
is pending in the future.
ie.... 12, 13, 14, 26 (There could be 100 of them out there)
The second query "events" needs to meet both reqirements. =20
1 - cal_category=3D'501'=20
2 - cal_id=3D a number from the "event_time" query
I think i need to do a loop inside of a loop
Thanks...
Matt=20
Here is my code:=20
$todays_year =3D date("Y");
$todays_month =3D date("m");
$todays_day =3D date("d");
$tstamp =3D mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time =3D mysql_query("SELECT cal_id FROM egw_cal_dates where =
cal_start > $tstamp", $db);
$events =3D mysql_query("SELECT * FROM egw_cal WHERE =
cal_category=3D'501' and cal_id=3D'$event_time'\n", $db);
if ($event =3D mysql_fetch_array($events)) {
echo "\n";
echo "
\n";
do {
echo "
Size=3D'10'>$event[cal_title] - $=
event[cal_location]\n";
echo "
\n";
echo "$event[cal_description]";
echo "
\n";
echo "
\n";
} while ($event =3D mysql_fetch_array($events));
} else {
echo "No Public Events Are Currently Scheduled...";
}
?>
------=_NextPart_000_004E_01C74EAB.D4927290--
Re: SQL Query - Using variable from another SQL Query
am 13.02.2007 07:16:28 von Mike Morris
--Alt-Boundary-12029.1202493359
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
I think you don't need to break this into two queries... this is really a SQL question,
not a PHP question...
Just do a "join" on the two tables:
* where table1.cal_id = table2.cal_id
and then have a where clause that does all your filtering:
* and table1.Date > now() and table2.cal_category='501'
Using SQL instead of code is almost always the right thing to do. Learning how to
do more sophisticated queries will pay off big time in the long run... and good
tutorials are widely and freely available...
From: "Matthew Ferry"
To:
Date sent: Mon, 12 Feb 2007 11:14:32 -0500
Subject: SQL Query - Using variable from another SQL Query
> Hello Everyone....
>
> Got a simple / stupid question.
> Worked on this all night. I'm over looking something very basic here.
>
> The query "event_time" brings back the calendar id for each event that is pending in the future.
> ie.... 12, 13, 14, 26 (There could be 100 of them out there)
>
> The second query "events" needs to meet both reqirements.
> 1 - cal_category='501'
> 2 - cal_id= a number from the "event_time" query
>
> I think i need to do a loop inside of a loop
>
> Thanks...
>
> Matt
>
>
> Here is my code:
>
>
>
> $todays_year = date("Y");
>
> $todays_month = date("m");
>
> $todays_day = date("d");
>
> $tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
>
> $event_time = mysql_query("SELECT cal_id FROM egw_cal_dates where cal_start > $tstamp", $db);
>
> $events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db);
>
>
>
> if ($event = mysql_fetch_array($events)) {
>
> echo "\n";
>
> echo "
\n";
>
> do {
>
> echo "$event[cal_title] - $event[cal_location]\n";
>
> echo "
\n";
>
> echo "$event[cal_description]";
>
> echo "
\n";
>
> echo "
\n";
>
> } while ($event = mysql_fetch_array($events));
>
> } else {
>
> echo "No Public Events Are Currently Scheduled...";
>
> }
>
> ?>
>
>
--Alt-Boundary-12029.1202493359--