displaying data from two mysql tables in dreamweaver php

Question

Here are my two mysql table with code and output

Case Date table is like this.:

 CREATE TABLE `casedt` (
  `SlNo` bigint(7) NOT NULL,
  `AdvUser` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `FileNo` int(5) NOT NULL,
  `NextDt` date NOT NULL,
  `Comments` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 mysql_select_db($database_MyDatabase, $MyDatabase);
$query_casedate = sprintf("SELECT * FROM casedt WHERE AdvUser = %s", GetSQLValueString($colname_casedate, "text"));
$casedate = mysql_query($query_casedate, $MyDatabase) or die(mysql_error());
$row_casedate = mysql_fetch_assoc($casedate);
$totalRows_casedate = mysql_num_rows($casedate);
<p>Cause List for Advocate: <?php echo $row_AdvUser['FullName']; ?> for Date: <?php echo $row_casedate['NextDt']; ?></p>
      <table width="600" border="1">
        <tr>
          <td>File No.</td>
          <td>Case Title</td>
          <td>Client Side</td>
          <td>Court Name</td>
          <td>Case Type</td>
         </tr>
        <tr>
          <td>          <?php echo $row_casedate['FileNo']; ?></td>
          <td>            <?php echo $row_CaseStatic['CaseTitle']; ?></td>
          <td>            <?php echo $row_CaseStatic['ClientSide']; ?></td>
          <td>            <?php echo $row_CaseStatic['CourtName']; ?></td>
          <td>  <?php echo $row_CaseStatic['CaseType']; ?></td>
          </tr>
      </table>

The record displayed is only one for a particular date. I want to display all records of that particular date and particular advuser. These code are generated in dreamweaver cs6 for php files. Please guide and help

If you want multiple rows, instead of a single row, you need to this in a loop. ie. change `$row_casedate = mysql_fetch_assoc($casedate);` to `while($row_casedate = mysql_fetch_assoc($casedate)){ .... }` — Sean, Feb 13 '16 at 06:44

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

See this statement here,

$row_casedate = mysql_fetch_assoc($casedate);

You're fetching only one row from the result set. Remove this line and loop through the entire result set inside <table>, like this:

// your code    

<table width="600" border="1">
<tr>
  <td>File No.</td>
  <td>Case Title</td>
  <td>Client Side</td>
  <td>Court Name</td>
  <td>Case Type</td>
</tr>
<?php
    while($row_casedate = mysql_fetch_assoc($casedate)){
        ?>
        <tr>
            <td><?php echo $row_casedate['FileNo']; ?></td>
            <td><?php echo $row_CaseStatic['CaseTitle']; ?></td>
            <td><?php echo $row_CaseStatic['ClientSide']; ?></td>
            <td><?php echo $row_CaseStatic['CourtName']; ?></td>
            <td><?php echo $row_CaseStatic['CaseType']; ?></td>
        </tr>
        <?php
    }
?>
</table>

Sidenote: Don't use mysql_* functions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or pdo instead. And this is why you shouldn't use mysql_* functions.

edited May 23 '17 at 10:28

Community

1
1

answered Feb 13 '16 at 06:46

Rajdeep Paul

16,887
3
18
37

Dear Paul, The result is not as expected. Please advise. I have revised my query by giving the table records and its output after your suggestion. – Dharam Jindal Feb 13 '16 at 08:08
@DharamJindal You didn't delete this line `$row_casedate = mysql_fetch_assoc($casedate);` from your code, that's why it starts from file no. 2. And use `DISTINCT` or `GROUP BY` with your SELECT query to remove duplicate rows. – Rajdeep Paul Feb 13 '16 at 08:38
Dear Paul, have you gone through the new jpg files of records in two tables. The displayed record from the table casestatic is not that of the required fileno. Displayed result shows fileno 5 while the other fields from the casestatic table does not show that of file no 5 – Dharam Jindal Feb 13 '16 at 11:47
In my query, there are static data of users in user_reg each has many files numbers in casestatic table and each file has many hearing dates in casedt table. I want to show the files of an individual user with its dates of hearing for a particular date from casedt and the related static data of that fileno from the casestatic table of a user for a particular date – Dharam Jindal Feb 13 '16 at 12:03
@DharamJindal I'm not sure what exactly you're trying to do. Have you used `DISTINCT` or `GROUP BY` clause with your SELECT query, like I suggested? And please don't edit the answer, edit your question instead, or write it in the comment section. – Rajdeep Paul Feb 13 '16 at 13:25
i THINK, I HAVE MENTIONED WHAT I WANT TO DO. THERE ARE 3 TABLES: ADVUSER, CASESTATIC AND CASEDT. ADVUSER HAS STATIC DATA OF USERS, CASESTATIC TABLES HAVE THE STATIC DATA OF COURT CASES WITH ADVUSER AND FILE NO. THE THIRD TABLE IS HAVING ADVUSER AND FILE NOS OF DIFFERENT USERS WITH THEIR DIFFERENT FILE NO AND THE COURT CASE DATE OF HEARING. AN ADOVATE WANT TO KNOW WHAT CASES TODAY IS TO HERE AND IN WHICH COURT AND THE CASE TITLE FROM THE CASESTATIC TABLE. THIS REPORT IS TO BE DISPLAYED FOR A PARTICULARS USER HAVING DIFFERENT FILE NOS WHICH HAVE DIFFERENT DATES OF HEARING IN CASEDT TABLE. – Dharam Jindal Feb 13 '16 at 17:03
Dear Paul, have you read my yesterday post. Please go through it, if you have any solution, please advise me. – Dharam Jindal Feb 15 '16 at 12:36

displaying data from two mysql tables in dreamweaver php

1 Answers1