1 form, 2 record sets

russlock

any help with this problem would be great.

i have a form and i want the info from 2 seperate tables, which makes up the record. when i have 2 seperate recordsets, the artist portion never changes, no-matter which record i choose to delete. i have attached a screenshot.

some code.

<?php require_once('Connections/RecordstorePHP.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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 ((isset($POST['delrecord'])) && ($POST['delrecord'] != "")) {
$deleteSQL = sprintf("DELETE FROM album WHERE AlbumID=%s",
GetSQLValueString($_POST['delrecord'], "int"));

mysql_select_db($database_RecordstorePHP, $RecordstorePHP);
$Result1 = mysql_query($deleteSQL, $RecordstorePHP) or die(mysql_error());

$deleteGoTo = "admincatalog.php";
if (isset($SERVER['QUERY_STRING'])) {
$deleteGoTo .= (strpos($deleteGoTo, '?')) ? "&" : "?";
$deleteGoTo .= $SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $deleteGoTo));
}

$colname_rs_del = "1";
if (isset($GET['AlbumID'])) {
$colname_rs_del = (get_magic_quotes_gpc()) ? $GET['AlbumID'] : addslashes($_GET['AlbumID']);
}
mysql_select_db($database_RecordstorePHP, $RecordstorePHP);
$query_rs_del = sprintf("SELECT * FROM Album WHERE AlbumID = %s", $colname_rs_del);
$rs_del = mysql_query($query_rs_del, $RecordstorePHP) or die(mysql_error());
$row_rs_del = mysql_fetch_assoc($rs_del);
$totalRows_rs_del = mysql_num_rows($rs_del);

mysql_select_db($database_RecordstorePHP, $RecordstorePHP);
$query_rs_del_2 = "SELECT * FROM artist";
$rs_del_2 = mysql_query($query_rs_del_2, $RecordstorePHP) or die(mysql_error());
$row_rs_del_2 = mysql_fetch_assoc($rs_del_2);
$totalRows_rs_del_2 = mysql_num_rows($rs_del_2);
?>

<body bgcolor="#bfd5ba" marginheight="0" marginwidth="0" topmargin="0" leftmargin="0">

<td height="87%" align="left" valign="top">

      <td><img src="assets/logo.gif" width="260" height="43" border="0"></td>
					</tr>
  </table>
			<table width="100%" border="0" cellpadding="0" cellspacing="0">
		<tr height="20">
			<td height="20" width="43"></td>
			<td height="20" width="119"></td>
			<td height="20" width="10"></td>
			<td height="20" width="33"></td>
			<td height="20" width="19"></td>
			<td height="20" width="154"></td>
			<td height="20" width="45"></td>
			<td height="20" width="45"></td>
			<td height="20" width="81"></td>
			<td height="20" width="252"></td>

		</tr>
		<tr height="20">
			<td height="20" width="43"></td>
			<td height="20" width="119">&nbsp;</td>
			<td height="20" width="10"></td>
			<td height="20" width="33">&nbsp;</td>
			<td height="20" width="19"></td>
			<td height="20" width="154">&nbsp;</td>
			<td height="20" width="45"><a href="javascript:history.go(-1)"><img src="assets/back.jpg" alt="Back" width="45" height="43" border="0"></a></td>
			<td height="20" width="45"><a href="index.php"><img src="assets/home.jpg" alt="Home" width="45" height="43" border="0"></a></td>
			<td height="20" width="81"><a href="javascript:history.go(1)"><img src="assets/forward.jpg" alt="Forward" width="45" height="43" border="0"></a></td>
			<td height="20" width="252">&nbsp;</td>

		</tr>
		<tr height="20">
			<td height="20" width="43"></td>
			<td height="20" width="119"></td>
			<td height="20" width="10"></td>
			<td height="20" width="33"></td>
			<td height="20" width="19"></td>
			<td height="20" width="154"></td>
			<td height="20" width="45"></td>
			<td height="20" width="45"></td>
			<td height="20" width="81"></td>
			<td height="20" width="252"></td>

		</tr>
  </table>
	<table border="0" cellpadding="0" cellspacing="0" width="100%">
		<tr>
			<td width="10">&nbsp;</td>

      <td width="180" valign="top" align="left"> 
        <table border="0" cellpadding="0" cellspacing="0" width="100%">
					<tr height="30">
						<td colspan="2" rowspan="2" valign="top" align="left"><img src="assets/cc1.gif" width="10" height="33" border="0"></td>

            <td height="30" width="160" bgcolor="#89AA80"><font face="Verdana" size="2" color="white"><b>Deleting</b></font></td>
						<td colspan="2" rowspan="2" valign="top"><img src="assets/cc2.gif" width="10" height="33" border="0"></td>
					</tr>
					<tr height="3">
						<td height="3" width="160" bgcolor="white"></td>
					</tr>
					<tr>
						<td width="5" bgcolor="#89AA80">&nbsp;</td>
						<td width="5" bgcolor="white">&nbsp;</td>

            <td width="160" bgcolor="white">                  <p><strong><u>IMPORTANT !!!</u></strong></p>                  <p class="style1">MAKE SURE THIS IS THE CORRECT ALBUM AS THERE IS NO GOING BACK.</p>                  <p><strong>DOUBLE CHECK !!!!   </strong></p> </td>
						<td width="5" bgcolor="white">&nbsp;</td>
						<td width="5" bgcolor="#89AA80">&nbsp;</td>
					</tr>
					<tr height="3">
						<td colspan="2" rowspan="2" valign="top" align="right"><img src="assets/cc3.gif" width="10" height="13" border="0"></td>
						<td nowrap height="3" width="160" bgcolor="white" ></td>
						<td colspan="2" rowspan="2" valign="top" align="left"><img src="assets/cc4.gif" width="10" height="13" border="0"></td>
					</tr>
					<tr height="10">
						<td nowrap height="10" width="160" bgcolor="#89AA80"></td>
					</tr>
		</table>

      </td>
			<td width="10">&nbsp;</td>

      <td width="610" valign="top" align="left"> 
        <table border="0" cellpadding="0" cellspacing="0" width="95%">
					<tr height="30">
						<td colspan="2" rowspan="2" valign="top" align="left"><img src="assets/cc1.gif" width="10" height="33" border="0"></td>

            <td height="30" width="590" bgcolor="#89AA80"><font face="Verdana" size="2" color="white"><b>Album Details </b></font></td>
						<td colspan="2" rowspan="2" valign="top"><img src="assets/cc2.gif" width="10" height="33" border="0"></td>
					</tr>
					<tr height="3">
						<td height="3" width="590" bgcolor="white"></td>
					</tr>
					<tr>
						<td width="5" bgcolor="#89AA80"></td>
						<td width="5" bgcolor="white"></td>

            <td width="590" bgcolor="white">                  <form name="form1" method="post" action="">
<table width="60%"  border="1" cellspacing="2" cellpadding="2">
  <tr>
    <th scope="row">Artist</th>
    <td><?php echo $row_rs_del_2['ArtistGroupName']; ?></td>
  </tr>
  <tr>
    <th scope="row">Album</th>
    <td><?php echo $row_rs_del['AlbumName']; ?></td>
  </tr>
  <tr>
    <th scope="row">Location</th>
    <td><?php echo $row_rs_del['AlbumLoc']; ?></td>
  </tr>
  <tr>
    <th scope="row">&nbsp;</th>
    <td><input type="submit" name="Submit" value="Delete record"></td>
  </tr>
</table>
<input name="delrecord" type="hidden" id="delrecord" value="<?php echo $row_rs_del['AlbumID']; ?>">
                            </form> </td>
						<td width="5" bgcolor="white"></td>
						<td width="5" bgcolor="#89AA80"></td>
					</tr>
					<tr height="3">
						<td colspan="2" rowspan="2" valign="top" align="right"><img src="assets/cc3.gif" width="10" height="13" border="0"></td>
						<td height="3" width="590" bgcolor="white"></td>
						<td colspan="2" rowspan="2" valign="top" align="left"><img src="assets/cc4.gif" width="10" height="13" border="0"></td>
					</tr>
					<tr height="10">
						<td height="10" width="590" bgcolor="#89AA80"></td>
					</tr>
		</table>

      </td>
		</tr>
  </table>    </td>
		</tr>
		<tr>

<td height="13%" valign="bottom">

      <td><img src="assets/logosmall.gif" width="149" height="28" border="0"></td>
					</tr>
  </table>    </td>
		</tr>
</table>
</body>

</html>
<?php
mysql_free_result($rs_del);

mysql_free_result($rs_del_2);
?>

rachel2004

Originally posted by russlock
i have a form and i want the info from 2 seperate tables, which makes up the record. when i have 2 seperate recordsets, the artist portion never changes, no-matter which record i choose to delete.

Here are the SQL statements that I found in your code:

$deleteSQL = sprintf("DELETE FROM album WHERE AlbumID=%s",
$query_rs_del = sprintf("SELECT * FROM Album WHERE AlbumID = %s", $colname_rs_del);
$query_rs_del_2 = "SELECT * FROM artist";

The problem with each of them is that you are only dealing with records from one table at a time. If you want to pull records from multiple records, you first need to identify what is considered the linking identifiers (usually a unique record ID) between each of the tables and then use a JOIN and/or WHERE clauses to match one table to another table.

If you are interested in a some good online articles on how to do this, just let me know.

russlock

I wouldn't mind, as i have no idea where to start with this one.
I have the album name, location and artist ID on the one table and the Artist name on the other. i can put the artist iID on the same form, but it only shows the number and not the artist name, which is on the other table.

anyway, any help is great, thanks.