|
|
Posted by J.O. Aho on 10/14/39 11:53
Frankly wrote:
> "J.O. Aho" <user@example.net> wrote in message
> news:4i7uh0F2i36dU1@individual.net...
>
>> I'll make a simple example on how to use a join in your own php script,
>> but you have to wait to tomorrow, as I should be sleeping right now.
>
> it will be a long night :)
Nights never long, always shorter than what you want them to be :)
Here is the script, it's quite long, I mail you the script too, so you don't
have to get problems with line breaks in the wrong place.
//Aho
--- test.php ---
<html>
<head>
<title>Join Test</title>
</head>
<body>
<h2>Join test</h2><br>
<form>
<select name="join">
<?PHP
// The login name you use for your database
$db_login="yourusername";
// The password you need for loggin in on the database
$db_pass="yoursecretpassowrd";
// The database name
$db_name="yourdatabasename";
// Location of the mysql server, usually localhost
$db_server="localhost";
// name of the first table
$db_table1="Apartments";
//name of the key column in table 1 where to join
$db_column1="ApartmentID";
// name of the first table
$db_table2="NewTable";
//name of the key column in table 1 where to join
$db_column2="ApartmentID";
echo "<option value=\"0\">join $db_table1 - $db_table2</option>\n";
echo "<option value=\"1\">inner join $db_table1 - $db_table2</option>\n";
echo "<option value=\"2\">cross join $db_table1 - $db_table2</option>\n";
echo "<option value=\"3\">left join $db_table1 - $db_table2</option>\n";
echo "<option value=\"4\">right join $db_table1 - $db_table2</option>\n";
echo "<option value=\"5\">left outer join $db_table1 - $db_table2</option>\n";
echo "<option value=\"6\">right outer join $db_table1 - $db_table2</option>\n";
echo "<option value=\"7\">join $db_table2 - $db_table1</option>\n";
echo "<option value=\"8\">inner join $db_table2 - $db_table1</option>\n";
echo "<option value=\"9\">cross join $db_table2 - $db_table1</option>\n";
echo "<option value=\"10\">left join $db_table2 - $db_table1</option>\n";
echo "<option value=\"11\">right join $db_table2 - $db_table1</option>\n";
echo "<option value=\"12\">left outer join $db_table2 - $db_table1</option>\n";
echo "<option value=\"13\">right outer join $db_table2 - $db_table1</option>\n";
?>
</select>
<input type="submit" name="submit" value="Select">
</form>
<?PHP
// We connect to the database
$link = mysql_connect($db_server, $db_login, $db_pass);
if (!$link) {
// The connection failed
die('Could not connect: ' . mysql_error());
}
// we select which database to use
if(!mysql_select_db($db_name, $link)) {
// we couldn't select the wanted database
die ("Can't use $db_name : " . mysql_error());
}
switch($_REQUEST['join']) {
case 0:
$query="SELECT * FROM $db_table1, $db_table2 WHERE $db_table1.$db_column1 =
$db_table2.$db_column2";
echo "Join was selected<br>\n$query<br>\n";
break;
case 1:
$query="SELECT * FROM $db_table1 INNER JOIN ($db_table2) ON
($db_table1.$db_column1 = $db_table2.$db_column2)";
echo "Inner join was selected<br>\n$query<br>\n";
break;
case 2:
$query="SELECT * FROM $db_table1 CROSS JOIN ($db_table2) ON
($db_table1.$db_column1 = $db_table2.$db_column2)";
echo "Cross join was selected<br>\n$query<br>\n";
break;
case 3:
$query="SELECT * FROM $db_table1 LEFT JOIN ($db_table2) ON
($db_table1.$db_column1 = $db_table2.$db_column2)";
echo "Left join was selected<br>\n$query<br>\n";
break;
case 4:
$query="SELECT * FROM $db_table1 RIGHT JOIN ($db_table2) ON
($db_table1.$db_column1 = $db_table2.$db_column2)";
echo "Right join was selected<br>\n$query<br>\n";
break;
case 5:
$query="SELECT * FROM $db_table1 LEFT OUTER JOIN ($db_table2) ON
($db_table1.$db_column1 = $db_table2.$db_column2)";
echo "Left outer join was selected<br>\n$query<br>\n";
break;
case 6:
$query="SELECT * FROM $db_table1 RIGHT OUTER JOIN ($db_table2) ON
($db_table1.$db_column1 = $db_table2.$db_column2)";
echo "Right outer join was selected<br>\n$query<br>\n";
break;
case 7:
$query="SELECT * FROM $db_table2, $db_table1 WHERE $db_table2.$db_column2 =
$db_table1.$db_column1";
echo "Join was selected<br>\n$query<br>\n";
break;
case 8:
$query="SELECT * FROM $db_table2 INNER JOIN ($db_table1) ON
($db_table2.$db_column2 = $db_table1.$db_column1)";
echo "Inner join was selected<br>\n$query<br>\n";
break;
case 9:
$query="SELECT * FROM $db_table2 CROSS JOIN ($db_table1) ON
($db_table2.$db_column2 = $db_table1.$db_column1)";
echo "Cross join was selected<br>\n$query<br>\n";
break;
case 10:
$query="SELECT * FROM $db_table2 LEFT JOIN ($db_table1) ON
($db_table2.$db_column2 = $db_table1.$db_column1)";
echo "Left join was selected<br>\n$query<br>\n";
break;
case 11:
$query="SELECT * FROM $db_table2 RIGHT JOIN ($db_table1) ON
($db_table2.$db_column2 = $db_table1.$db_column1)";
echo "Right join was selected<br>\n$query<br>\n";
break;
case 12:
$query="SELECT * FROM $db_table2 LEFT OUTER JOIN ($db_table1) ON
($db_table2.$db_column2 = $db_table1.$db_column1)";
echo "Left outer join was selected<br>\n$query<br>\n";
break;
case 13:
$query="SELECT * FROM $db_table2 RIGHT OUTER JOIN ($db_table1) ON
($db_table2.$db_column2 = $db_table1.$db_column1)";
echo "Right outer join was selected<br>\n$query<br>\n";
break;
default:
echo "hello\n";
break;
}
// we make the query to the database
$result=mysql_query($query);
if(!$result) {
echo "The query failed<br>\n";
exit;
}
// we type out the result
$color=0;
echo "</p><table border=\"0\" cellpadding=\"5\" cellspacing=\"0\">\n";
$numfields = mysql_num_fields($result);
echo "<tr bgcolor=\"#eeeeee\">\n";
for ($i=0; $i < $numfields; $i++) {
echo '<td>'.mysql_field_name($result, $i)."</td>\n";
}
echo "</tr>\n";
while($row=mysql_fetch_array($result)) {
if($color%2) {
$col="#dddddd";
} else {
$col="#bbbbbb";
}
echo "<tr bgcolor=\"$col\">\n";
for($i=0;$i<count($row);$i++) {
echo "<td>".$row[$i]."</td>\n";
}
echo "</tr>\n";
$color++;
}
echo "</table>\n";
// we close the connection to the database
mysql_close($link);
?>
</body>
</html>
--- end of file ---
Navigation:
[Reply to this message]
|