Mysql query

kennyola · #1 (**permalink**) 05-29-08, 11:02

I am working on a project that requires me to select data from two tables
The first select statement select courses and topics, then i need to get another data from the subtopic table to display the subtopic data under each topic display. Pls i am stuck someone to help me

r937 · #2 (**permalink**) 05-29-08, 11:29

when's the assignment due? helping you with this may take some time

kennyola · #3 (**permalink**) 05-29-08, 11:38

Thank you very much for the quick response, the assignment is due sunday evening .If you like i can send the files to you thank you once again

r937 · #4 (**permalink**) 05-29-08, 11:47

no, don't send them to me

instead, here's how to proceed

1. show us all the table layouts -- table names, column names, and datatypes

easiest would be to use the SHOW CREATE TABLE command for each table

2. show us the query you've tried, and explain why you think it isn't working

kennyola · #5 (**permalink**) 05-29-08, 12:11

for my topics table the table names are excode datatypes= varchar(20), tpcode datatypes varchar(20),cocode datatypes varchar(20) and topic varchardatatypes(80)
for my subtopics the table names are id datatypes(int),tpcode varchar(20) and name datatypes varchar(60).

my table are working perfectly, i was able to get my data from the topics table to appear on my php page what i need now is to show my subtopics data on the same page with my topics data like a collapsible table under my topics data

thanks

kennyola · #6 (**permalink**) 05-29-08, 12:18

<?php require_once('../Connections/ecertConn.php'); ?>
<?php

$exam = $_GET['ex'];
$cur = $_GET['cur'];

mysql_select_db($database_ecertConn, $ecertConn);
$query_Recordset1 = "SELECT topics.topic FROM topics WHERE excode='$exam' AND cocode='$cur'";
$Recordset1 = mysql_query($query_Recordset1, $ecertConn) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<link href="../admin/style.css" rel="stylesheet" type="text/css" /></head>

<body topmargin="0" leftmargin="0">
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td valign="top" height="8"></td>
</tr>
<tr>
<td>  <span class="menuup">SYLLABUS</span></td>
</tr>
<tr>
<td height="3" bgcolor="#295593"></td>
</tr>
<tr>
<td height="8"></td>
</tr>
<tr>
<td width="69%" valign="top" class="textonly4"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top"><table width="98%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td class="menuup">Topics</td>
</tr>
<tr>
<td><?php do { ?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><?php echo $row_Recordset1['topic']; ?></td>
</tr>
<tr>
<td height="6"></td>
</tr>
</table>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?></td>
</tr>
</table></td>
</tr>
<tr>
<td valign="top"> </td>
</tr>
<tr>
<td valign="top"><div align="justify" class="textonly3"><br />
<br />
</div></td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
That is the code i wrote to get my topics data from my topics table into the page

This is the place where i am stuck, to open my get my query to open my topics table and subtopics table and get the data into the same page.

<?php
//initialize the session
if (!isset($_SESSION)) {
session_start();
}

// ** Logout the current user. **
$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";
if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){
$logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){
//to fully log out a visitor we need to clear the session varialbles
$_SESSION['MM_Username'] = NULL;
$_SESSION['MM_UserGroup'] = NULL;
$_SESSION['PrevUrl'] = NULL;
unset($_SESSION['MM_Username']);
unset($_SESSION['MM_UserGroup']);
unset($_SESSION['PrevUrl']);

$logoutGoTo = "index.php";
if ($logoutGoTo) {
header("Location: $logoutGoTo");
exit;
}
}
?>
<?php require_once('../Connections/ecertConn.php');

if (!isset($_SESSION)) {
session_start();
}
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {
// For security, start by assuming the visitor is NOT authorized.
$isValid = False;

// When a visitor has logged into this site, the Session variable MM_Username set equal to their username.
// Therefore, we know that a user is NOT logged in if that Session variable is blank.
if (!empty($UserName)) {
// Besides being logged in, you may restrict access to only certain users based on an ID established when they login.
// Parse the strings into arrays.
$arrUsers = Explode(",", $strUsers);
$arrGroups = Explode(",", $strGroups);
if (in_array($UserName, $arrUsers)) {
$isValid = true;
}
// Or, you may restrict access to only certain users based on their username.
if (in_array($UserGroup, $arrGroups)) {
$isValid = true;
}
if (($strUsers == "") && true) {
$isValid = true;
}
}
return $isValid;
}

$MM_restrictGoTo = "index.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {
$MM_qsChar = "?";
$MM_referrer = $_SERVER['PHP_SELF'];
if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0)
$MM_referrer .= "?" . $QUERY_STRING;
$MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
header("Location: ". $MM_restrictGoTo);
exit;
}

mysql_select_db($database_ecertConn, $ecertConn);
$checkdetails = "select * from users where pin='$_SESSION[MM_Username]'";
$resultcheckd = mysql_query($checkdetails, $ecertConn) or die("Could not get database now try later ".mysql_error());
$row = mysql_fetch_assoc($resultcheckd);

$man = $row['fulname'];
$men = $row['pin'];

mysql_select_db($database_ecertConn, $ecertConn);
$checkdetailsp = "select * from pin where pin='$_SESSION[MM_Username]'";
$resultcheckdp = mysql_query($checkdetailsp, $ecertConn) or die("Could not get database now try later ".mysql_error());
$rowp = mysql_fetch_assoc($resultcheckdp);

$manp = $rowp['exam'];
$menp = $rowp['level'];

mysql_select_db($database_ecertConn, $ecertConn);
$checkdetailsex = "select * from exams where levels='$menp'";
$resultcheckdex = mysql_query($checkdetailsex, $ecertConn) or die("Could not get database now try later ".mysql_error());
$rowex = mysql_fetch_assoc($resultcheckdex);

$cour = $rowex['code'];

mysql_select_db($database_ecertConn, $ecertConn);
$checkdetailsexd = "select * from courses where level='$cour'";
$resultcheckdexd = mysql_query($checkdetailsexd, $ecertConn) or die("Could not get database now try later ".mysql_error());
$rowexd = mysql_fetch_assoc($resultcheckdexd);
$totalRows_Recordseted = mysql_num_rows($resultcheckdexd);

$topic = $rowexd['coname'];
$topico = $rowexd['cocode'];

mysql_select_db($database_ecertConn, $ecertConn);
$checkdetailsexds = "select * from topics where tpcode='$topic'";
$resultcheckdexds = mysql_query($checkdetailsexds, $ecertConn) or die("Could not get database now try later ".mysql_error());
$rowexds = mysql_fetch_assoc($resultcheckdexds);
$totalRows_Recordseted = mysql_num_rows($resultcheckdexds);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>:: Students Console | E-certified ::</title>
<style type="text/css">

</style>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>

<body topmargin="6">
<table width="750" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
<tr>
<td colspan="2"><?php include 'head.html'; ?></td>
</tr>
<tr>
<td colspan="2" height="5"></td>
</tr>
<tr>
<td width="181" valign="top"><table width="98%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><?php include 'nav_mode.php'; ?></td>
</tr>
</table></td>
<td width="569" valign="top"><table width="98%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td valign="top"><?php include 'module_content.php'; ?></td>
</tr>
</table></td>
</tr>
<tr>
<td colspan="2" height="8"></td>
</tr>
<tr>
<td colspan="2"><?php include 'foot.html'; ?>
</td>
</tr>
</table>
</body>
</html>

r937 · #7 (**permalink**) 05-29-08, 12:22

try a JOIN query

healdem · #8 (**permalink**) 05-29-08, 14:31

so is your problem MySQL related or PHP related (or is it even Javascript related
what are you stuck on

there are plenty of exmaples on t'net of that show sub selects