"Combining" MySQL Queries for Results

msmith29063

Any help will be greatly appreciated!

I need to "combine" two queries to pull results from two different tables in a list. The first query is already working:

SELECT * FROM phpCalendar_Details WHERE ".$LocQuery." AND (".$query.") ORDER BY CalendarDetailsID

The second one (that I need to add) needs to pull a field called StartDate from a table called phpCalendar_Daily. (The data is associated to each other by a field called CalendarDetailsID in every table in the database.)

FYI: $query is pulling CalendarDetailsID from an array:

// walk through the array and form a query set
for ($j = 0; $ID[$j]; $j++) {
$query .= " OR CalendarDetailsID = '$ID[$j]'";
}

I'm generating an HTML table of information.

StartDate*** (from phpCalendar_Daily) - Title (from phpCalendar_Details) - Location (from phpCalendar_Details)

1.
2.
3.

If you need me to post the PHP code for this page, let me know. Thank you for any help.

tomhath

All you need is a simple JOIN. You'll also have to tell the dbms which table the column(s) should be pulled from. I usually use an alias for each table to save some typing (in this case det and dai):

SELECT dai.StartDate, det.Title, det.Location 
FROM phpCalendar_Details det
INNER JOIN phpCalendar_Daily dai
ON det.CalendarDetailsID = dai.CalendarDetailsID
WHERE ".$LocQuery." AND (".$query.") 
ORDER BY det.CalendarDetailsID 
...
for ($j = 0; $ID[$j]; $j++) { 
$query .= " OR det.CalendarDetailsID = '$ID[$j]'"; 
}

msmith29063

I'm now returning an 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 '.CalendarDetailsID = dai.CalendarDetailsID WHERE det.CalendarD"

Here's the revised PHP and MySQL query:

// walk through the array and form a query set
for ($j = 0; $ID[$j]; $j++) {
$query .= " OR det.CalendarDetailsID = '$ID[$j]'";
}
...
SELECT dai.StartDate, det.Title, det.Location FROM phpCalendar_Details det INNER JOIN phpCalendar_Daily_dai ON_det.CalendarDetailsID = dai.CalendarDetailsID WHERE (".$query.") ORDER BY det.CalendarDetailsID

Any ideas? I did away with the $LocQuery. It's not even being used. Thank you for any advice.

tomhath

If this is a cut/paste of your code, there are a couple of typos in there (extra underscores before dia and after ON). Store the entire query in a variable, then echo it and look very closely at the SQL:

phpCalendar_Daily_dai ON_det.CalendarDetailsID

SELECT dai.StartDate, det.Title, det.Location 
FROM phpCalendar_Details det 
INNER JOIN phpCalendar_Daily_dai 
ON_det.CalendarDetailsID = dai.CalendarDetailsID 
WHERE (".$query.") ORDER BY det.CalendarDetailsID

msmith29063

Apparently, I had some invisible "gremlins" that only showed up when I cut and pasted... Now, I'm not returning an error -- the table looks like it's pulling the correct number of lines, but nothing will display. Here's more of the PHP code on the page:

$i = 0;
// get all pending items and load them into an array
$result = mysql("$DBName","SELECT CalendarDetailsID FROM phpCalendar_Daily WHERE Active = '1'") or die(mysql_error());
while ($row = mysql_fetch_row($result)) {
$ID[$i] = $row[0];
$i++;
}

// walk through the array and form a query set
for ($j = 0; $ID[$j]; $j++) {
$query .= " OR det.CalendarDetailsID = '$ID[$j]'";
}

$query = ereg_replace("^ OR ","",$query);

if (!$query) {  
	echo "No events were found";
	exit;
}

<?
$result = mysql("$DBName","SELECT dai.StartDate, det.Title, det.Location, det.CalendarDetailsID FROM phpCalendar_Details det
INNER JOIN phpCalendar_Daily dai ON det.CalendarDetailsID = dai.CalendarDetailsID
WHERE (".$query.") ORDER BY det.CalendarDetailsID") or die(mysql_error());

while ($row = mysql_fetch_row($result)) {

	$CeCI = $row['CalendarDetailsID'];
	$CeSt = $row['StartDate'];
	$CeTi = $row['Title'];
	$CeLo = $row['Location'];

	if (strlen($CeTi) > 15) {
		$CeTi = substr($CeTi,0,14)."...";
	}

	echo "<tr>
	<td valign=\"top\" class=small>$CeCI</td>
	<form action=\"editcalendar.php\" method=\"post\" target=\"main\"><input type=\"hidden\" name=\"CalendarDetailsID\" value=\"$CeCI\">
	<td valign=\"top\"><input type=\"submit\" value=\"Edit\"></td></form>
	<form action=\"deletecalendar.php\" method=\"post\" target=\"main\"><input type=\"hidden\" name=\"CalendarDetailsID\" value=\"$CeCI\">
	<td valign=\"top\"><input type=\"submit\" value=\"Delete\"></td></form>
	<td valign=\"top\" class=small>$CeSt</td>
	<td valign=\"top\" class=small>$CeTi</td>
	<td valign=\"top\" class=small>$CeLo</td>
	</tr>\n\n";
}

</table>

tomhath

I don't see anything else. Can you run the query from a command line or inside phpMyAdmin? That would tell you the query is working. Once the query is right, echo the lines out in php, without all the fancy HTML so you know the fetch is working.