Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Listing lines twice on MySql Query

Status
Not open for further replies.
timsayles1

timsayles1

MIS
Nov 18, 2002
121
US
Another long day.... Here is my problem,

I have a php page which queries the MySql database on my local server. This page works correctly when applied to similar queries on other database tables. I have recreated the table to no avail.

Here is what it is doing:

When you execute the php file, it will list the records, but it will show the same records twice and count them as two. When looking at MySql database, there is only one record, which is correct.

Screen output looks similar to this:

Records 1-2 of 2

Visit # customer id customer name charge
1 555 Joe Smith $35.00
1 555 Joe Smith $35.00
2 556 Pam Smith $40.00
2 556 Pam Smith $40.00

A copy of the PHP file is below:

<?php session_start();

if (isset($_POST["filter"])) $filter = @$_POST["filter"];
if (isset($_POST["filter_field"])) $filterfield = @$_POST["filter_field"];
$wholeonly = false;
if (isset($_POST["wholeonly"])) $wholeonly = @$_POST["wholeonly"];
if (!isset($filter) && isset($_SESSION["filter"])) $filter = $_SESSION["filter"];
if (!isset($filterfield) && isset($_SESSION["filter_field"])) $filterfield = $_SESSION["filter_field"];

?>

<html>
<head>
<title>db -- encounters</title>
<meta name="generator" http-equiv="content-type" content="text/html">
<style type="text/css">
body {
background-color: #FFFFFF;
color: #004080;
font-family: Arial;
font-size: 12px;
}
.bd {
background-color: #FFFFFF;
color: #004080;
font-family: Arial;
font-size: 12px;
}
.tbl {
background-color: #FFFFFF;
}
a:link {
color: #FF0000;
font-family: Arial;
font-size: 12px;
}
a:active {
color: #0000FF;
font-family: Arial;
font-size: 12px;
}
a:visited {
color: #800080;
font-family: Arial;
font-size: 12px;
}
.hr {
background-color: #336699;
color: #FFFFFF;
font-family: Arial;
font-size: 12px;
}
a.hr:link {
color: #FFFFFF;
font-family: Arial;
font-size: 12px;
}
a.hr:active {
color: #FFFFFF;
font-family: Arial;
font-size: 12px;
}
a.hr:visited {
color: #FFFFFF;
font-family: Arial;
font-size: 12px;
}
.dr {
background-color: #FFFFFF;
color: #000000;
font-family: Arial;
font-size: 12px;
}
.sr {
background-color: #FFFFCF;
color: #000000;
font-family: Arial;
font-size: 12px;
}
</style>
</head>
<body>
<table class="bd" width="100%"><tr><td class="hr"><h2>Database lookup</h2></td></tr></table>
<?php
$conn = connect();
$showrecs = 20;
$pagerange = 10;

$a = @$_GET["a"];
$recid = @$_GET["recid"];
$page = @$_GET["page"];
if (!isset($page)) $page = 1;

$sql = @$_POST["sql"];

switch ($sql) {
case "insert":
sql_insert();
break;
case "update":
sql_update();
break;
case "delete":
sql_delete();
break;
}

switch ($a) {
case "add":
addrec();
break;
case "view":
viewrec($recid);
break;
case "edit":
editrec($recid);
break;
case "del":
deleterec($recid);
break;
default:
select();
break;
}

if (isset($filter)) $_SESSION["filter"] = $filter;
if (isset($filterfield)) $_SESSION["filter_field"] = $filterfield;
if (isset($wholeonly)) $_SESSION["wholeonly"] = $wholeonly;

mysql_close($conn);
?>

</body>
</html>

<?php function select()
{
global $a;
global $showrecs;
global $page;
global $filter;
global $filterfield;
global $wholeonly;


if ($a == "reset") {
$filter = "";
$filterfield = "";
$wholeonly = "";
}

$checkstr = "";
if ($wholeonly) $checkstr = " checked";
$res = sql_select();
$count = sql_getrecordcount();
if ($count % $showrecs != 0) {
$pagecount = intval($count / $showrecs) + 1;
}
else {
$pagecount = intval($count / $showrecs);
}
$startrec = $showrecs * ($page - 1);
if ($startrec < $count) {mysql_data_seek($res, $startrec);}
$reccount = min($showrecs * $page, $count);
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr><td>Table: encounters</td></tr>
<tr><td>Records shown <?php echo $startrec + 1 ?> - <?php echo $reccount ?> of <?php echo $count ?></td></tr>
</table>
<hr size="1" noshade>
<form action="encounters.php" method="post">
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><b>Custom Filter</b>&nbsp;</td>
<td><input type="text" name="filter" value="<?php echo $filter ?>"></td>
<td><select name="filter_field">
<option value="">All Fields</option>
<option value="<?php echo "Encounternumber" ?>"<?php if ($filterfield == "Encounternumber") { echo "selected"; } ?>><?php echo htmlspecialchars("Trans Id") ?></option>
<option value="<?php echo "lp_Customer" ?>"<?php if ($filterfield == "lp_Customer") { echo "selected"; } ?>><?php echo htmlspecialchars("Customer") ?></option>
<option value="<?php echo "Date" ?>"<?php if ($filterfield == "Date") { echo "selected"; } ?>><?php echo htmlspecialchars("Date") ?></option>
<option value="<?php echo "lp_Servicecodes" ?>"<?php if ($filterfield == "lp_Servicecodes") { echo "selected"; } ?>><?php echo htmlspecialchars("Service code") ?></option>
<option value="<?php echo "lp_Charge" ?>"<?php if ($filterfield == "lp_Charge") { echo "selected"; } ?>><?php echo htmlspecialchars("Charge") ?></option>
<option value="<?php echo "Payment" ?>"<?php if ($filterfield == "Payment") { echo "selected"; } ?>><?php echo htmlspecialchars("Payment") ?></option>
<option value="<?php echo "lp_Paymenttype" ?>"<?php if ($filterfield == "lp_Paymenttype") { echo "selected"; } ?>><?php echo htmlspecialchars("Payment type") ?></option>
<option value="<?php echo "Refnumber" ?>"<?php if ($filterfield == "Refnumber") { echo "selected"; } ?>><?php echo htmlspecialchars("Reference") ?></option>
</select></td>
<td><input type="checkbox" name="wholeonly"<?php echo $checkstr ?>>Whole words only</td>
</td></tr>
<tr>
<td>&nbsp;</td>
<td><input type="submit" name="action" value="Apply Filter"></td>
<td><a href="encounters.php?a=reset">Reset Filter</a></td>
</tr>
</table>
</form>
<hr size="1" noshade>
<?php showpagenav($page, $pagecount); ?>
<br>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="100%">
<tr>
<td class="hr">&nbsp;</td>
<td class="hr">&nbsp;</td>
<td class="hr">&nbsp;</td>
<td class="hr"><?php echo "Trans Id" ?></td>
<td class="hr"><?php echo "Customer" ?></td>
<td class="hr"><?php echo "Date" ?></td>
<td class="hr"><?php echo "Service code" ?></td>
<td class="hr"><?php echo "Charge" ?></td>
<td class="hr"><?php echo "Payment" ?></td>
<td class="hr"><?php echo "Payment type" ?></td>
<td class="hr"><?php echo "Reference" ?></td>
</tr>
<?php
for ($i = $startrec; $i < $reccount; $i++)
{
$row = mysql_fetch_assoc($res);
$style = "dr";
if ($i % 2 != 0) {
$style = "sr";
}
?>
<tr>
<td class="<?php echo $style ?>"><a href="encounters.php?a=view&recid=<?php echo $i ?>">View</a></td>
<td class="<?php echo $style ?>"><a href="encounters.php?a=edit&recid=<?php echo $i ?>">Edit</a></td>
<td class="<?php echo $style ?>"><a href="encounters.php?a=del&recid=<?php echo $i ?>">Delete</a></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Encounternumber"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["lp_Customer"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Date"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["lp_Servicecodes"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["lp_Charge"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Payment"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["lp_Paymenttype"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Refnumber"]) ?></td>
</tr>
<?php
}
mysql_free_result($res);
?>
</table>
<br>
<?php showpagenav($page, $pagecount); ?>
<?php } ?>

<?php function showrow($row, $recid)
{
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="50%">
<tr>
<td class="hr"><?php echo htmlspecialchars("Trans Id")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Encounternumber"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Customer")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["lp_Customer"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Date")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Date"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Service code")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["lp_Servicecodes"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Charge")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["lp_Charge"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Payment")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Payment"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Payment type")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["lp_Paymenttype"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Reference")."&nbsp;" ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Refnumber"]) ?></td>
</tr>
</table>
<?php } ?>

<?php function showroweditor($row, $iseditmode)
{
global $conn;
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="50%">
<tr>
<td class="hr"><?php echo htmlspecialchars("Trans Id")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="Encounternumber" value="<?php echo str_replace('"', '&quot;', trim($row["Encounternumber"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Customer")."&nbsp;" ?></td>
<td class="dr"><select name="Customer">
<?php
$sql = "select `Companyname`, `Companyname` from `customer`";
$res = mysql_query($sql, $conn) or die(mysql_error());

while ($lp_row = mysql_fetch_assoc($res)){
$val = $lp_row["Companyname"];
$caption = $lp_row["Companyname"];
if ($row["Customer"] == $val) {$selstr = " selected"; } else {$selstr = ""; }
?><option value="<?php echo $val ?>"<?php echo $selstr ?>><?php echo $caption ?></option>
<?php } ?></select>
</td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Date")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="Date" value="<?php echo str_replace('"', '&quot;', trim($row["Date"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Service code")."&nbsp;" ?></td>
<td class="dr"><select name="Servicecodes">
<?php
$sql = "select `Servicecode`, `Servicecode` from `servicecodes`";
$res = mysql_query($sql, $conn) or die(mysql_error());

while ($lp_row = mysql_fetch_assoc($res)){
$val = $lp_row["Servicecode"];
$caption = $lp_row["Servicecode"];
if ($row["Servicecodes"] == $val) {$selstr = " selected"; } else {$selstr = ""; }
?><option value="<?php echo $val ?>"<?php echo $selstr ?>><?php echo $caption ?></option>
<?php } ?></select>
</td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Charge")."&nbsp;" ?></td>
<td class="dr"><select name="Charge">
<?php
$sql = "select `Servicefee`, `Servicefee` from `servicecodes`";
$res = mysql_query($sql, $conn) or die(mysql_error());

while ($lp_row = mysql_fetch_assoc($res)){
$val = $lp_row["Servicefee"];
$caption = $lp_row["Servicefee"];
if ($row["Charge"] == $val) {$selstr = " selected"; } else {$selstr = ""; }
?><option value="<?php echo $val ?>"<?php echo $selstr ?>><?php echo $caption ?></option>
<?php } ?></select>
</td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Payment")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="Payment" value="<?php echo str_replace('"', '&quot;', trim($row["Payment"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Payment type")."&nbsp;" ?></td>
<td class="dr"><select name="Paymenttype">
<?php
$sql = "select `Paymenttype`, `Paymenttype` from `paymenttypes`";
$res = mysql_query($sql, $conn) or die(mysql_error());

while ($lp_row = mysql_fetch_assoc($res)){
$val = $lp_row["Paymenttype"];
$caption = $lp_row["Paymenttype"];
if ($row["Paymenttype"] == $val) {$selstr = " selected"; } else {$selstr = ""; }
?><option value="<?php echo $val ?>"<?php echo $selstr ?>><?php echo $caption ?></option>
<?php } ?></select>
</td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Reference")."&nbsp;" ?></td>
<td class="dr"><input type="text" name="Refnumber" maxlength="7" value="<?php echo str_replace('"', '&quot;', trim($row["Refnumber"])) ?>"></td>
</tr>
</table>
<?php } ?>

<?php function showpagenav($page, $pagecount)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="encounters.php?a=add">Add Record</a>&nbsp;</td>
<?php if ($page > 1) { ?>
<td><a href="encounters.php?page=<?php echo $page - 1 ?>">&lt;&lt;&nbsp;Prev</a>&nbsp;</td>
<?php } ?>
<?php
global $pagerange;

if ($pagecount > 1) {

if ($pagecount % $pagerange != 0) {
$rangecount = intval($pagecount / $pagerange) + 1;
}
else {
$rangecount = intval($pagecount / $pagerange);
}
for ($i = 1; $i < $rangecount + 1; $i++) {
$startpage = (($i - 1) * $pagerange) + 1;
$count = min($i * $pagerange, $pagecount);

if ((($page >= $startpage) && ($page <= ($i * $pagerange)))) {
for ($j = $startpage; $j < $count + 1; $j++) {
if ($j == $page) {
?>
<td><b><?php echo $j ?></b></td>
<?php } else { ?>
<td><a href="encounters.php?page=<?php echo $j ?>"><?php echo $j ?></a></td>
<?php } } } else { ?>
<td><a href="encounters.php?page=<?php echo $startpage ?>"><?php echo $startpage ."..." .$count ?></a></td>
<?php } } } ?>
<?php if ($page < $pagecount) { ?>
<td>&nbsp;<a href="encounters.php?page=<?php echo $page + 1 ?>">Next&nbsp;&gt;&gt;</a>&nbsp;</td>
<?php } ?>
</tr>
</table>
<?php } ?>

<?php function showrecnav($a, $recid, $count)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="encounters.php">Index Page</a></td>
<?php if ($recid > 0) { ?>
<td><a href="encounters.php?a=<?php echo $a ?>&recid=<?php echo $recid - 1 ?>">Prior Record</a></td>
<?php } if ($recid < $count - 1) { ?>
<td><a href="encounters.php?a=<?php echo $a ?>&recid=<?php echo $recid + 1 ?>">Next Record</a></td>
<?php } ?>
</tr>
</table>
<hr size="1" noshade>
<?php } ?>

<?php function addrec()
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="encounters.php">Index Page</a></td>
</tr>
</table>
<hr size="1" noshade>
<form enctype="multipart/form-data" action="encounters.php" method="post">
<p><input type="hidden" name="sql" value="insert"></p>
<?php
$row = array(
"Encounternumber" => "",
"Customer" => "",
"Date" => "",
"Servicecodes" => "",
"Charge" => "",
"Payment" => "",
"Paymenttype" => "",
"Refnumber" => "");
showroweditor($row, false);
?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php } ?>

<?php function viewrec($recid)
{
$res = sql_select();
$count = sql_getrecordcount();
mysql_data_seek($res, $recid);
$row = mysql_fetch_assoc($res);
showrecnav("view", $recid, $count);
?>
<br>
<?php showrow($row, $recid) ?>
<br>
<hr size="1" noshade>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="encounters.php?a=add">Add Record</a></td>
<td><a href="encounters.php?a=edit&recid=<?php echo $recid ?>">Edit Record</a></td>
<td><a href="encounters.php?a=del&recid=<?php echo $recid ?>">Delete Record</a></td>
</tr>
</table>
<?php
mysql_free_result($res);
} ?>

<?php function editrec($recid)
{
$res = sql_select();
$count = sql_getrecordcount();
mysql_data_seek($res, $recid);
$row = mysql_fetch_assoc($res);
showrecnav("edit", $recid, $count);
?>
<br>
<form enctype="multipart/form-data" action="encounters.php" method="post">
<input type="hidden" name="sql" value="update">
<input type="hidden" name="xEncounternumber" value="<?php echo $row["Encounternumber"] ?>">
<?php showroweditor($row, true); ?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php
mysql_free_result($res);
} ?>

<?php function deleterec($recid)
{
$res = sql_select();
$count = sql_getrecordcount();
mysql_data_seek($res, $recid);
$row = mysql_fetch_assoc($res);
showrecnav("del", $recid, $count);
?>
<br>
<form action="encounters.php" method="post">
<input type="hidden" name="sql" value="delete">
<input type="hidden" name="xEncounternumber" value="<?php echo $row["Encounternumber"] ?>">
<?php showrow($row, $recid) ?>
<p><input type="submit" name="action" value="Confirm"></p>
</form>
<?php
mysql_free_result($res);
} ?>

<?php function connect()
{
$conn = mysql_connect("localhost", "root", "default");
mysql_select_db("db");
return $conn;
}

function sqlvalue($val, $quote)
{
if ($quote)
$tmp = sqlstr($val);
else
$tmp = $val;
if ($tmp == "")
$tmp = "NULL";
elseif ($quote)
$tmp = "'".$tmp."'";
return $tmp;
}

function sqlstr($val)
{
return str_replace("'", "''", $val);
}

function sql_select()
{
global $conn;
global $filter;
global $filterfield;
global $wholeonly;

$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
$sql = "SELECT * FROM (SELECT t1.`Encounternumber`, t1.`Customer`, lp1.`Companyname` AS `lp_Customer`, t1.`Date`, t1.`Servicecodes`, lp3.`Servicecode` AS `lp_Servicecodes`, t1.`Charge`, lp4.`Servicefee` AS `lp_Charge`, t1.`Payment`, t1.`Paymenttype`, lp6.`Paymenttype` AS `lp_Paymenttype`, t1.`Refnumber` FROM `encounters` AS t1 LEFT OUTER JOIN `customer` AS lp1 ON (t1.`Customer` = lp1.`Companyname`) LEFT OUTER JOIN `servicecodes` AS lp3 ON (t1.`Servicecodes` = lp3.`Servicecode`) LEFT OUTER JOIN `servicecodes` AS lp4 ON (t1.`Charge` = lp4.`Servicefee`) LEFT OUTER JOIN `paymenttypes` AS lp6 ON (t1.`Paymenttype` = lp6.`Paymenttype`)) subq";
if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
$sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
} elseif (isset($filterstr) && $filterstr!='') {
$sql .= " where (`Encounternumber` like '" .$filterstr ."') or (`lp_Customer` like '" .$filterstr ."') or (`Date` like '" .$filterstr ."') or (`lp_Servicecodes` like '" .$filterstr ."') or (`lp_Charge` like '" .$filterstr ."') or (`Payment` like '" .$filterstr ."') or (`lp_Paymenttype` like '" .$filterstr ."') or (`Refnumber` like '" .$filterstr ."')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
return $res;
}

function sql_getrecordcount()
{
global $conn;
global $filter;
global $filterfield;
global $wholeonly;

$filterstr = sqlstr($filter);
if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
$sql = "SELECT COUNT(*) FROM (SELECT t1.`Encounternumber`, t1.`Customer`, lp1.`Companyname` AS `lp_Customer`, t1.`Date`, t1.`Servicecodes`, lp3.`Servicecode` AS `lp_Servicecodes`, t1.`Charge`, lp4.`Servicefee` AS `lp_Charge`, t1.`Payment`, t1.`Paymenttype`, lp6.`Paymenttype` AS `lp_Paymenttype`, t1.`Refnumber` FROM `encounters` AS t1 LEFT OUTER JOIN `customer` AS lp1 ON (t1.`Customer` = lp1.`Companyname`) LEFT OUTER JOIN `servicecodes` AS lp3 ON (t1.`Servicecodes` = lp3.`Servicecode`) LEFT OUTER JOIN `servicecodes` AS lp4 ON (t1.`Charge` = lp4.`Servicefee`) LEFT OUTER JOIN `paymenttypes` AS lp6 ON (t1.`Paymenttype` = lp6.`Paymenttype`)) subq";
if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
$sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
} elseif (isset($filterstr) && $filterstr!='') {
$sql .= " where (`Encounternumber` like '" .$filterstr ."') or (`lp_Customer` like '" .$filterstr ."') or (`Date` like '" .$filterstr ."') or (`lp_Servicecodes` like '" .$filterstr ."') or (`lp_Charge` like '" .$filterstr ."') or (`Payment` like '" .$filterstr ."') or (`lp_Paymenttype` like '" .$filterstr ."') or (`Refnumber` like '" .$filterstr ."')";
}
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($res);
reset($row);
return current($row);
}

function sql_insert()
{
global $conn;
global $_POST;

$sql = "insert into `encounters` (`Encounternumber`, `Customer`, `Date`, `Servicecodes`, `Charge`, `Payment`, `Paymenttype`, `Refnumber`) values (" .sqlvalue(@$_POST["Encounternumber"], false).", " .sqlvalue(@$_POST["Customer"], true).", " .sqlvalue(@$_POST["Date"], true).", " .sqlvalue(@$_POST["Servicecodes"], true).", " .sqlvalue(@$_POST["Charge"], false).", " .sqlvalue(@$_POST["Payment"], false).", " .sqlvalue(@$_POST["Paymenttype"], true).", " .sqlvalue(@$_POST["Refnumber"], true).")";
mysql_query($sql, $conn) or die(mysql_error());
}

function sql_update()
{
global $conn;
global $_POST;

$sql = "update `encounters` set `Encounternumber`=" .sqlvalue(@$_POST["Encounternumber"], false).", `Customer`=" .sqlvalue(@$_POST["Customer"], true).", `Date`=" .sqlvalue(@$_POST["Date"], true).", `Servicecodes`=" .sqlvalue(@$_POST["Servicecodes"], true).", `Charge`=" .sqlvalue(@$_POST["Charge"], false).", `Payment`=" .sqlvalue(@$_POST["Payment"], false).", `Paymenttype`=" .sqlvalue(@$_POST["Paymenttype"], true).", `Refnumber`=" .sqlvalue(@$_POST["Refnumber"], true) ." where " .primarykeycondition();
mysql_query($sql, $conn) or die(mysql_error());
}

function sql_delete()
{
global $conn;

$sql = "delete from `encounters` where " .primarykeycondition();
mysql_query($sql, $conn) or die(mysql_error());
}
function primarykeycondition()
{
global $_POST;
$pk = "";
$pk .= "(`Encounternumber`";
if (@$_POST["xEncounternumber"] == "") {
$pk .= " IS NULL";
}else{
$pk .= " = " .sqlvalue(@$_POST["xEncounternumber"], false);
};
$pk .= ")";
return $pk;
}
?>
 
Sort by date Sort by votes
It's difficult to read in this format, but I'd guess it has something to do with one of the left outer joins.

Maybe
LEFT OUTER JOIN `servicecodes` AS lp3 ON (t1.`Servicecodes` = lp3.`Servicecode`)
LEFT OUTER JOIN `servicecodes` AS lp4 ON (t1.`Charge` = lp4.`Servicefee`)

Try changing to a select * in the subq and
echo '<pre>';print_r($rows);echo "</pre>";die;
to see the array after your for loop. That might lead you in the right direction.

Mark
 
Upvote 0 Downvote
As the lazy man that I am, I would use "SELECT DISTINCT" if I knew there were no duplicates in the db ;).

-Geeeeeeeeeeeeeeeeeeeeeeee-
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
228
SashaBuilder3
SashaBuilder3
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
180
spamjim
spamjim
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
279
ersatz
ersatz
lexer
  • Locked
  • Question
Fetching Data from Mysql and filling Inputs in a Form
Replies
0
Views
102
lexer
lexer
Rutsagig
  • Locked
  • Question
mysql_insert_id returns 0 after SELECT query
Replies
0
Views
106
Rutsagig
Rutsagig

Part and Inventory Search

Sponsor

Back
Top