not getting correct query result

[dflow]

i have a report i created and i want in the ShiftsName to get the name from the shiftslist filtered by the existing shiftid from the records table

 

<?php require_once('Connections/shifts.php'); ?>
<?php mysql_query("SET NAMES 'UTF8'");  ?>
<?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;
}
}

$maxRows_RsLastRecord = 10;
$pageNum_RsLastRecord = 0;
if (isset($_GET['pageNum_RsLastRecord'])) {
  $pageNum_RsLastRecord = $_GET['pageNum_RsLastRecord'];
}
$startRow_RsLastRecord = $pageNum_RsLastRecord * $maxRows_RsLastRecord;

mysql_select_db($database_shifts, $shifts);
$query_RsLastRecord = "SELECT * FROM records ";
$query_limit_RsLastRecord = sprintf("%s LIMIT %d, %d", $query_RsLastRecord, $startRow_RsLastRecord, $maxRows_RsLastRecord);
$RsLastRecord = mysql_query($query_limit_RsLastRecord, $shifts) or die(mysql_error());
$row_RsLastRecord = mysql_fetch_assoc($RsLastRecord);

if (isset($_GET['totalRows_RsLastRecord'])) {
  $totalRows_RsLastRecord = $_GET['totalRows_RsLastRecord'];
} else {
  $all_RsLastRecord = mysql_query($query_RsLastRecord);
  $totalRows_RsLastRecord = mysql_num_rows($all_RsLastRecord);
}
$totalPages_RsLastRecord = ceil($totalRows_RsLastRecord/$maxRows_RsLastRecord)-1;

mysql_select_db($database_shifts, $shifts);
$query_RsShiftsList = sprintf("SELECT * FROM shifts WHERE ShiftID = %s", GetSQLValueString($row_RsLastRecord['ShiftID'], 'int'));
$RsShiftsList = mysql_query($query_RsShiftsList, $shifts) or die(mysql_error());
$row_RsShiftsList = mysql_fetch_assoc($RsShiftsList);
$totalRows_RsShiftsList = mysql_num_rows($RsShiftsList);
?>


<?php
mysql_free_result($RsLastRecord);

mysql_free_result($RsShiftsList);
?>

[Brian W]

I'm unclear as to what you want. Please be more detailed.

Do you plan on just getting the first result or list all of the results?

[dflow]

I'm unclear as to what you want. Please be more detailed.

Do you plan on just getting the first result or list all of the results?

 

i want in the report to present the records from the records table and in the shiftname column

  to get the shiftname from the shift table and to filter that by the shiftID in the records table

[Brian W]

query could look like this

SELECT

records.ShiftID as ID, shifts.* as *

FROM records

LEFT JOIN shifts

ON ID = ShiftID

Not sure, but that should get your the records you want all in one query.

BTW, you aren't going to get any output with that page... Dreamweaver allows you to use the recordsets you build to create dynamic tables, but its very dirty so you need to get away from using Dreamweavers gadgets.

[dflow]

query could look like this

SELECT

records.ShiftID as ID, shifts.* as *

FROM records

LEFT JOIN shifts

ON ID = ShiftID

Not sure, but that should get your the records you want all in one query.

BTW, you aren't going to get any output with that page... Dreamweaver allows you to use the recordsets you build to create dynamic tables, but its very dirty so you need to get away from using Dreamweavers gadgets.

 

thanks ill try it out

indeed DW is dirty and im on the verge of upgrading to cakephp MCV

just starting out, need more order