Linking related table data with php/mysql

[cjkeane]

I have a few tables in a project and i'm linking pages together via PK/FK's.

 

i have a main table with client names,

ID, CLIENT NAME, ETC

 

i have a table with communications

COMMID, ID, CONTACT NAMES, ETC

 

and another of communication history.

COMMHISTORYID, COMMID, DATE, TIME, ETC

 

I'm not sure how to write the query so that i can view/update information from all 3 tables on the same webpage page using php/mysql. I'd appreciate any assistance you may provide. thanks.

[Muddy_Funster]

What type of database are you using, because this isn't really a PHP question.

[cjkeane]

i'm using a mysql database. and it was a php / mysql question.

 

i'm just not sure how to use a php/sql query to join the data to display it on the screen after its been posted.

i use an insert query to post clients.id to communications.id then another query to insert the communications.commid to communicationshistory.commid. the commid isnt inserted and i am not getting any data echo'd back to the screen after the post so i'm hoping someone can help me. thanks.

 

 

 <?php
}

// connect to the database
$dbcnx = @mysql_connect('localhost', 'root', 'pw'); 
if (!$dbcnx) {
  exit('<p>Unable to connect to the database server at this time.</p>');
}

if (!@mysql_select_db('db')) {
  exit('<p>Unable to locate the Mega Assistance database at this time.</p>');
}

// check if the form has been submitted. If it has, process the form and save it to the database
if (isset($_POST['submit']))
{ 


// confirm that the 'IDNumber' value is a valid integer before getting the form data
if ($_POST['IDNumber'])
{

// get form data, making sure it is valid
$IDNumber = $_POST['IDNumber'];
$Notes = $_POST['Notes'];
$Contact = $_POST['Contact'];
$ContactFrom = $_POST['ContactFrom'];

//error, display form
renderForm($IDNumber, $CommId,$Contact, $ContactFrom, $error);

{

//mysql_free_result($result);
mysql_query("INSERT INTO communications (IDNumber, Contact, ContactFrom) 
VALUES ('$IDNumber', '$Contact', '$ContactFrom')") or die(mysql_error()); 

// if status notes are updated, post results to casehistory
// mysql_query("SELECT CommID FROM communications WHERE IDNumber = $IDNumber") or die(mysql_error()); 


if($_POST['Notes'] == ""){
//do nothing if no notes entered


}else { 
mysql_query("INSERT INTO communicationshistory (CommID, DateRecorded, TimeRecorded, Notes) 
              VALUES ('communications.CommID', CURDATE(), NOW(), '$Notes')") or die(mysql_error()); 

}


// once saved, redirect back to the view page
  
header("Location: er_communicationrecord.php?IDNumber=" . $IDNumber);  
echo "<h2>Client's record has been successfully saved!</h2><br />";

}
}
else
{
// if the 'IDNumber' isn't valid, display an error
echo 'Error!';
}
}
else
// if the form hasn't been submitted, get the data from the db and display the form
{

// get the 'IDNumber' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
if (isset($_GET['IDNumber']))
{
// query db
$IDNumber = $_GET['IDNumber'];
$result = mysql_query("SELECT * FROM records inner join communications on records.IDNumber='$IDNumber'")
or die(mysql_error()); 
$row = mysql_fetch_array($result);

// check that the 'IDNumber' matches up with a row in the databse
if($row)
{

// get data from db
$Notes = $row['Notes'];
$Contact = $row['$Contact'];
$ContactFrom = $row['$ContactFrom'];

// show form
renderForm($IDNumber, $CommId, $Contact, $ContactFrom, $Notes, '');

}
else
// if no match, display result
{
echo "No results!";
}
}
else
// if the 'IDNumber' in the URL isn't valid, or if there is no 'IDNumber' value, display an error
{
echo 'Error!';
}
}
?>

 

[Muddy_Funster]

problem here is the SQL

VALUES ('communications.CommID', CURDATE(), NOW(), '$Notes')") or die(mysql_error()); 

you can't just tell point it at a table field and expect it to know what it's looking for (or even what it's looking at) try this and let's see how you get on

VALUES ((SELECT DISTINCT commid FROM communications where communications.IDNumber = '$IDNumber' LIMIT 1), CURDATE(), NOW(), '$Notes')") or die(mysql_error()); 

But the biggest problem is that you have error reporting turned off in the php.ini file.

[cjkeane]

its getting closer. i am now getting the data echoed. for 'contact' and contactfrom, but i also get this error message.

 

Column count doesn't match value count at row 1