Get more than 1 data entry from DB using PHP

tomasa · June 12, 2018, 7:11am

Hi all,

I am trying to get multiple rows data which has same id. So the same id can be allocated to different entries and I need to display this.

The code as follows:

<?php require_once('Connections/eam.php'); ?>
<?php 
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_rsMaintenanceAsset = "-1";
if (isset($_GET['recordID'])) {
  $colname_rsMaintenanceAsset = $_GET['recordID'];
}
mysql_select_db($database_eam, $eam);
$query_rsMaintenanceAsset = sprintf("SELECT * FROM maintenance_history WHERE maint_id = %s", GetSQLValueString($colname_rsMaintenanceAsset, "int"));
$rsMaintenanceAsset = mysql_query($query_rsMaintenanceAsset, $eam) or die(mysql_error());
$row_rsMaintenanceAsset = mysql_fetch_assoc($rsMaintenanceAsset);
$totalRows_rsMaintenanceAsset = mysql_num_rows($rsMaintenanceAsset);

mysql_select_db($database_eam, $eam);
$query_rsAssetDetail = sprintf("SELECT serialnumber FROM assets_hardware");
$rsAssetDetail = mysql_query($query_rsAssetDetail, $eam) or die(mysql_error());
$row_rsAssetDetail = mysql_fetch_assoc($rsAssetDetail);
$totalRows_rsAssetDetail = mysql_num_rows($rsAssetDetail);
 
$pageTitle="Maintenance History"; ?>



<?php include('includes/header.php'); ?>
    <h3>Maintenance history for WSL Tool Number: <?php echo $row_rsAssetDetail['serialnumber']; ?>   </h3>
    <table class="table1">
      <tr>
        <th>Maintenance ID</th>
        <th>Date Completed</th>
        <th>By</th>
        <th>Checklist</th>
        <th>Comments</th>
        <th>SPARE</th>

      </tr>
      <?php do { ?>
        <tr onmouseover="this.bgColor='#F2F7FF'" onmouseout="this.bgColor='#FFFFFF'";>
          <td> <?php echo $row_rsMaintenanceAsset['id']; ?>&nbsp; </td>
          <td> <?php echo $row_rsMaintenanceAsset['date_completed']; ?>&nbsp; </td>
          <td> <?php echo $row_rsMaintenanceAsset['by_staff']; ?>&nbsp; </td>
          <td> <?php echo $row_rsMaintenanceAsset['maint_checklist']; ?>&nbsp; </td>
          <td> <?php echo $row_rsMaintenanceAsset['maint_comm']; ?>&nbsp; </td>
          <td> <?php echo $row_rsMaintenanceAsset['status']; ?>&nbsp; </td>


    
 
        </tr>
      <?php } while ($row_rsMaintenanceAssets = mysql_fetch_assoc($rsMaintenanceAssets)); ?>
    </table>
	
	
	
<!-- <table class="pagination">
      <tr>
	  <td>Records <?php echo ($startRow_rsMaintenanceAssets + 1) ?> to <?php echo min($startRow_rsMaintenanceAssets + $maxRows_rsMaintenanceAssets, $totalRows_rsMaintenanceAssets) ?> of <?php echo $totalRows_rsMaintenanceAssets ?></td>
	  <td>
	  <table>
	  <tr>
	  <?php if ($pageNum_rsMaintenanceAssets > 0) { // Show if not first page ?>
        <td width="23%" align="center">
            <a href="<?php printf("%s?pageNum_rsMaintenanceAssets=%d%s", $currentPage, 0, $queryString_rsMaintenanceAssets); ?>">First</a>
        </td>
		<?php } // Show if not first page ?>

 		<?php if ($pageNum_rsMaintenanceAssets > 0) { // Show if not first page ?>		
        <td width="31%" align="center">
            <a href="<?php printf("%s?pageNum_rsMaintenanceAssets=%d%s", $currentPage, max(0, $pageNum_rsMaintenanceAssets - 1), $queryString_rsMaintenanceAssets); ?>">Previous</a>
         </td>
		 <?php } // Show if not first page ?>
        
		
         <?php if ($pageNum_rsMaintenanceAssets < $totalPages_rsMaintenanceAssets) { // Show if not last page ?>
        <td width="23%" align="center">
            <a href="<?php printf("%s?pageNum_rsMaintenanceAssets=%d%s", $currentPage, min($totalPages_rsMaintenanceAssets, $pageNum_rsMaintenanceAssets + 1), $queryString_rsMaintenanceAssets); ?>">Next</a>
         </td>
		 <?php } // Show if not last page ?>
        
		<?php if ($pageNum_rsMaintenanceAssets < $totalPages_rsMaintenanceAssets) { // Show if not last page ?>
        <td width="23%" align="center">
            <a href="<?php printf("%s?pageNum_rsMaintenanceAssets=%d%s", $currentPage, $totalPages_rsMaintenanceAssets, $queryString_rsMaintenanceAssets); ?>">Last</a> 
        </td>
		<?php } // Show if not last page ?>		
      </tr>
    </table>
	</td>
	</tr>
	</table>
   -->
  
  
<table class="pagination">
<tr>
  <td><div style="float:left;">Records <?php echo ($startRow_rsMaintenanceAssets + 1) ?> to <?php echo min($startRow_rsMaintenanceAssets + $maxRows_rsMaintenanceAssets, $totalRows_rsMaintenanceAssets) ?> of <?php echo $totalRows_rsMaintenanceAssets ?></div>
  <div style="float:right;">
    <table class="pagination1">
      <tr>
        <?php if ($pageNum_rsMaintenanceAssets > 0) { // Show if not first page ?>
          <td> <a href="<?php printf("%s?pageNum_rsMaintenanceAssets=%d%s", $currentPage, 0, $queryString_rsMaintenanceAssets); ?>"><img src="images/First.gif" alt="First Page" title="First Page" /></a> </td>
            <?php } // Show if not first page ?>
        
          <td> <a href="<?php printf("%s?pageNum_rsMaintenanceAssets=%d%s", $currentPage, max(0, $pageNum_rsMaintenanceAssets - 1), $queryString_rsMaintenanceAssets); ?>"><img src="images/Previous.gif" alt="Previous Page" title="Previous Page" /></a> </td>
            
        <?php if ($pageNum_rsMaintenanceAssets < $totalPages_rsMaintenanceAssets) { // Show if not last page ?>
          <td> <a href="<?php printf("%s?pageNum_rsMaintenanceAssets=%d%s", $currentPage, min($totalPages_rsMaintenanceAssets, $pageNum_rsMaintenanceAssets + 1), $queryString_rsMaintenanceAssets); ?>"><img src="images/Next.gif" alt="Next Page" title="Next Page" /></a> </td>
            <?php } // Show if not last page ?>
        <?php if ($pageNum_rsMaintenanceAssets < $totalPages_rsMaintenanceAssets) { // Show if not last page ?>
          <td> <a href="<?php printf("%s?pageNum_rsMaintenanceAssets=%d%s", $currentPage, $totalPages_rsMaintenanceAssets, $queryString_rsMaintenanceAssets); ?>"><img src="images/Last.gif" alt="Last Page" title="Last Page" /></a> </td>
            <?php } // Show if not last page ?>
        </tr>
    </table>
	</div>
  </td>
  </tr>
</table>

Gandalf · June 12, 2018, 8:09am

I realise you probably don’t want to hear this, but you really shouldn’t be using the mysql_ functions. They do not exist in current versions and were deprecated many years ago now. You should be using mysqli_ or better still PDO.

There is a good tutorial for PDO here.

chorn · June 12, 2018, 8:16am

Before doing anything else you should start refactoring your code to use PDO, or at least MySQLi, instead of the mysql_* functions to prevent your code breaking in newer versions.

And i don’t see what your exact problem is. There is the code, it does not do what you want, but you have a task to perform. What’s your question? How far have you come by yourself? What have you tried?

In PDO you can just perform $statement->fetchAll() to get all values from your query.

droopsnoot · June 12, 2018, 10:31am

Spot the difference:

$row_rsMaintenanceAsset = mysql_fetch_assoc($rsMaintenanceAsset);

and

<td> <?php echo $row_rsMaintenanceAsset['id']; ?>&nbsp; </td>

and

<?php } while ($row_rsMaintenanceAssets = mysql_fetch_assoc($rsMaintenanceAssets)); ?>

Your while() loop doesn’t use the same results object or array as the first one.

But, lose all the no-longer-supported mysql calls as others have said above, and switch to PDO.

How does your second query work? Does that table only have one row?

tomasa · June 12, 2018, 10:51am

Thanks for all replies folks,

I won’t switch to other mysql functions because I am not familiar with them (last time I have programmed was ~8 years ago) and at the moment I am only modifying existing system to suit our needs.

So current code returns FIRST entry with associated ID (If I go to item ID 70, click on “maintenance history” which takes me to maintenance history page, which, requests data from maintenance_history table, collecting only data related to ID which is same as Item ID) but I need to fetch all entries with the same ID. Hope this makes sense?

EDIT:
droopsnoot has pointed the mistake. Fixed and works fine now. Thank you very much!

John_Betong · June 12, 2018, 10:56am

I hope your internet service provider does not upgrade to PHP 7 and make all your script fail.

Edit:
If PHP is upgraded to version 7, here is one possible solution/kludge which may enable the existing script to run slowly

tomasa · June 12, 2018, 11:12am

My internet service provider already offers new php version, but at the same time offers old version support. If I got it right.

By the way, if you run it only in local server, this shouldn’t be a concern, right?

And side note, how big job would be to modify this system to work on new versions?

Thanks for all replies.

John_Betong · June 12, 2018, 11:30am

…and if you have not got it right?

As long as you or your operating system does not upgrade to PHP version 7

It all depends on how many mysql calls being used.

[offtopic]
May i suggest looking at a reliable PHP Framework because security upgrades usually and seamlessly handle mysql versions.
[/offtopic]

tomasa · June 12, 2018, 11:44am

If I not got it right, will have to set it up locally. We still run old software for other systems so I don’t see strong reason to be afraid of future.

Obviously, would like to have it compatible with new versions, but I then may end up building it from scratch. At the moment my skills limited in this area and will stick with current version.

Not sure about your last statement.

John_Betong · June 12, 2018, 12:14pm

My PHP and MySql requirements are simple and do not require complicated PHP Frameworks which take a long time to learn. I have tried numerous other PHP Framework recommendations and always return to the one which I have used for about ten years because:

it satisfies my simple requirements
has very good and clear documentation
has very good tried and tested tutorials that work!
has an active and helpful forum
has security upgrades whenever necessary activated with a single config script line
has numerous database alternatives selected with a single line statement
is simple, effective and fast
has a very good Query Builder
has validation, caching, profiling and many other useful utilities
will hopefully continue to be free for many more years

Try this:
https://www.codeigniter.com/user_guide/database/examples.html

system · September 11, 2018, 7:14pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.