Results 1 to 8 of 8

Thread: Mysql query

  1. #1
    Join Date
    May 2008
    Posts
    6

    Unanswered: Mysql query

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when's the assignment due? helping you with this may take some time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2008
    Posts
    6
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2008
    Posts
    6
    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

  6. #6
    Join Date
    May 2008
    Posts
    6
    <?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>&nbsp;&nbsp;<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">&nbsp;</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">
    <!--
    body {
    background-color: #99CCCC;
    }
    -->
    </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>

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try a JOIN query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •