Obtaining values from 2 recordsetquery

contactparijat · January 17, 2014, 1:48am

My DB has 2 tables mp_details & pic_gal
unique id fro mp_details ‘id’ & for pic_gal ‘pic_id’ pic_gal has a field ‘mp_name’ which recieves the value of mp_details’id’
in the master admin page when the link to visit is clicked the result is supposed to be like this
localhost/win-bengal/test-mp-details-new.php?id=[‘id’]&mp_name=[‘mp_name’]
e.g. localhost/win-bengal/test-mp-details-new.php?id=1&mp_name=1 it works fine for the first link but then onwards the problem starts localhost/win-bengal/test-mp-details-new.php?id=2&mp_name=1 how ever when this 1 is changed to 2 manualy the desired result is achieved. So in other words ‘mp_name’ is returning just the first value. Please help


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

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $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;
}
}

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $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;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_mp_db, $mp_db);
$query_Recordset1 = "SELECT * FROM mp_details";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $mp_db) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

mysql_select_db($database_mp_db, $mp_db);
$query_Recordset2 = "SELECT * FROM pic_gal";
$Recordset2 = mysql_query($query_Recordset2, $mp_db) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_Recordset1") == false && 
        stristr($param, "totalRows_Recordset1") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);

$query = "SELECT mp_details.*, pic_gal.* ".
 "FROM mp_details, pic_gal ".
    "WHERE mp_details.id = pic_gal.mp_name";
     
$result = mysql_query($query) or die(mysql_error());  
?>


<!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=utf-8" />
<title>ADMIN HOME</title>
<link href="../css/css-admin.css" rel="stylesheet" type="text/css" />
</head>

<body>
<p> <a href="add_data.php">Add Record</a> | <a href="../photo-gallery/admin/index.php">upload Image</a></p>
<table width="75%" border="0" align="center" cellpadding="5" cellspacing="0">
  <tr>
    <td bgcolor="#A9C8CB"><strong>id</strong></td>
    <td bgcolor="#A9C8CB"><strong>Name</strong></td>
    <td bgcolor="#A9C8CB"><strong>District</strong></td>
    <td bgcolor="#A9C8CB"><strong>Phone</strong></td>
    <td bgcolor="#A9C8CB"><strong>Email</strong></td>
    <td bgcolor="#A9C8CB">&nbsp;</td>
    <td bgcolor="#A9C8CB">&nbsp;</td>
    <td bgcolor="#A9C8CB">&nbsp;</td>
    <td bgcolor="#A9C8CB">&nbsp;</td>
  </tr>
  <?php do { ?>
    <tr>
      <td><a href="mp_details.php?recordID=<?php echo $row_Recordset1['id']; ?>"> <?php echo $row_Recordset1['id']; ?></a></td>
      <td><a href="../test-mp-details-new.php?id=<?php echo $row_Recordset1['id']?>&mp_name=<?php echo $row_Recordset2['mp_name']; ?>" target="_blank"><img src="../images/mp_photos/<?php echo $row_Recordset1['id']; ?>.jpg" alt="<?php echo $row_Recordset1['name']; ?>" width="" height="" border="0" title="<?php echo $row_Recordset1['name']; ?>" /><br /><?php echo $row_Recordset1['name']; ?></a></td>
      <td><a href="../district.php?district=<?php echo $row_Recordset1['district']; ?>"><?php echo $row_Recordset1['district']; ?></a></td>
      <td><?php echo $row_Recordset1['phone']; ?></td>
      <td><?php echo $row_Recordset1['mail_id']; ?></td>
      <td><a href="upload_mp_picture.php?id=<?php echo $row_Recordset1['id']; ?>">Edit Picture</a></td>
      <td><a href="edit_data.php?id=<?php echo $row_Recordset1['id']; ?>"><img src="../images/edit.png" width="31" height="31" border="0" /></a></td>
      <td><a href="delete_data.php?id=<?php echo $row_Recordset1['id']; ?>"><img src="../images/delete.png" width="31" height="31" border="0" /></a></td>
      <td><?php echo $row_Recordset1['id']; ?>=<?php echo $row_Recordset2['mp_name']; ?></td>
    </tr>
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
<br />
<table border="0">
  <tr>
    <td><?php if ($pageNum_Recordset1 > 0) { // Show if not first page ?>
        <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, 0, $queryString_Recordset1); ?>">First</a>
    <?php } // Show if not first page ?></td>
    <td><?php if ($pageNum_Recordset1 > 0) { // Show if not first page ?>
        <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>">Previous</a>
    <?php } // Show if not first page ?></td>
    <td><?php if ($pageNum_Recordset1 < $totalPages_Recordset1) { // Show if not last page ?>
        <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, min($totalPages_Recordset1, $pageNum_Recordset1 + 1), $queryString_Recordset1); ?>">Next</a>
    <?php } // Show if not last page ?></td>
    <td><?php if ($pageNum_Recordset1 < $totalPages_Recordset1) { // Show if not last page ?>
        <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, $totalPages_Recordset1, $queryString_Recordset1); ?>">Last</a>
    <?php } // Show if not last page ?></td>
  </tr>
</table>
<p>Records <?php echo ($startRow_Recordset1 + 1) ?> to <?php echo min($startRow_Recordset1 + $maxRows_Recordset1, $totalRows_Recordset1) ?> of <?php echo $totalRows_Recordset1 ?>
</p>
<p>&nbsp;</p>
</body>
</html>


<?php
mysql_free_result($Recordset1);

mysql_free_result($Recordset2);
?>

SpacePhoenix · January 17, 2014, 7:28am

Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.

Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.