Query from multiple tables

[draven77]

Hi all,
I know this is super easy, but I'm a designer mostly. Trying to learn something here

I'm writing this script for the band I'm in with my church. I want to write something that will look to see who's playing and send them a text message. I have most of it worked out, but can't seem to get the initial query working. Anything look immediately wrong with this?

Thanks in advanced for any tips!:

<?
// database connect function
mydbconnect();
// setup date
$today = date("Ymd");
$row_count = 0;
$color1 = " class=\"altrow\"";
$color2 = "";

if ($orderby=="") $orderby = "dates.dateDate";
	
	// Start query
	$result = mysql_query("
		SELECT *
		FROM roster, dates, members
		WHERE (
			(roster.rosterMemberID = members.memberID)
		AND
			(roster.rosterDateID = dates.dateID)
		
			)
		ORDER BY 
		".$orderby." DESC") or die ("no DB connection<br /><br />".mysql_error() );
	$number = mysql_numrows($result);
	
	if ($number < 1){print "<tr><td class=\"altrow\" colspan=\"7\">There are no items available at this time.</td></tr>";}
	else {
		echo "
		<table cellpadding=\"2\" cellspacing=\"0\" border=\"0\" class=\"table1\" width=\"540\">
		<tr>
			<th>Service</th>
			<th>Who's On</th>
			<th>Send a reminder</th>
		</tr>";
		
		while($r=mysql_fetch_array($result))
		{    
			// ESTABLISH FIELD NAMES
			$rosterID=$r["roster.rosterID"];
			$rosterDate=$r["dates.dateDate"];
			$firstname=$r["members.memberFname"];
			
			
			// Date formatting from yyyy-mm-dd
			$year = substr($dateDate, 2, 2); 
			$month = substr($dateDate, -5, 2);
			$day = substr($dateDate, 8, 9); 
			$formatted_date = $month ."/". $day ."/". $year;
			
			// This is the code for alternating row colors
			$row_color = ($row_count % 2) ? $color1 : $color2;
			
			// loop here
			echo "
				<tr>\n
					<td>".$rosterDate."</td>\n
					<td>".$firstname."</td>\n
					<td><img src=\"images/button.gif\" /></td>\n
				</tr>\n";
			
			$row_count ++;
		}
		echo "</table>";
	}

?>

[Belisarius]

What happens when you perform the query?

[draven77]

It doesn't put anything into the <td> cells at all. so it looks like the query's logic might just be off.

I'm storing users in "members"
playing dates in "dates"
and "roster" holds all the joining IDs

[draven77]

I should add that I've only written JOIN queries like this in MSSQL. This is my first attempt at mySQL queries with multiple tables.

[Belisarius]

I use the MDB2 package when I work with databases in PHP, so I'm not terribly familiar with the included MySQL functions, but it appears that if the query fails mysql_query() returns false. Check to see if $results is false, and if it is, get the error message from MySQL by using mysql_error(). That should give you a clue as to why it's failing.

[draven77]

I have this line in there to check for that I believe.

 or die ("no DB connection<br /><br />".mysql_error() );

[sde]

do you have access to a mysql manager like phpmyadmin? if so, print out your sql and run it in phpmyadmin. it should point you to the error or at least show you that you are getting blank results.

$sql = "
		SELECT *
		FROM roster, dates, members
		WHERE (
			(roster.rosterMemberID = members.memberID)
		AND
			(roster.rosterDateID = dates.dateID)
		
			)
		ORDER BY 
		".$orderby." DESC";
echo $sql;
exit;

now looking at the query, i don't see any where clause which specifies a date. as i understand it, this query would return all rows in your database.

i don't know your db structure well, but you could always try a left join:

SELECT *
		FROM roster
LEFT JOIN members on members.memberID=roster.rosterMemberID
LEFT JOIN dates on dates.dateID=roster.rosterDateID
		ORDER BY 
		".$orderby." DESC";

so now, if i'm on the right track anyway, we would want to add a clause to only select values with a specific date. unfortunately i have no idea what field it comes from. you have a variable: $dateDate in your code which is never assigned, yet you're using substr on it.

you need to take this down to smaller chunks and then put it together. work on a good query, then work on the logic to print it to the screen.

[draven77]

Did that, and got my result!

So I must be setting up the returned variables incorrectly.

// ESTABLISH FIELD NAMES
			$rosterID=$r["roster.rosterID"];
			$rosterDate=$r["dates.dateDate"];
			$firstname=$r["members.memberFname"];

[draven77]

Awesome, got it =) I didn't need the "tablename." in front of the returned value. Changed to:

$rosterID=$r["rosterID"];