Editing data of multiple tables

nhaydock

Hello everyone, great forum! - I am a newbie and trying to learn PHP and MYSQL and have run into a prob! - Wonder if you guys and girls would help me out?

Ok I have a tourism accomodation database consisting of several tables. I would like to display the information in a table with the option to edit. The edit link will take the user/admin to a form where they can do their edits and update the tables.

Right - so far I have no problem achieving this with just a single table, its when I try to edit data from multiple tables that the prob comes in..............

Take a look at http://www.swazinet.com/accomoadmin8.php For the bottom table (one that works) the code is:

<? 
// connect to the db server
$dbcnx = @mysql_connect ("localhost", "uname", "pword");
if (!$dbcnx) {
echo ("<P>Unable to connect to the " .
"database server at this time. </P>" );
exit ();
}
// Select the accomodation db
if (! @mysql_select_db("swazine_tour") ) {
echo ("<P>Unable to connect to the tourism" .
"database server at this time. </P>" );
exit ();
}

if(isset($_GET['del'])) 
{ 
    $query = "DELETE FROM accomodation WHERE id = '{$_GET['del']}'"; 
    mysql_query($query) or die('Error, cannot delete accomodation); 
} 

$query = "SELECT id, name, info FROM accomodation ORDER BY id"; 
$result = mysql_query($query) or die('Error, cannot get accomodation from database'); 


?> 
<table width="600" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#CCCCCC"> 
  <tr> 
    <td width="500"><strong>Name</strong></td> 
	<td width="500"><strong>Info</strong></td> 
    <td width="100"><strong>Action</strong></td> 
  </tr> 
  <? 
    while(list($id, $title, $info) = mysql_fetch_array($result, MYSQL_NUM)) 
    { 

?> 
  <tr> 
    <td width="500"> 
      <?=$title;?> 
    </td> 
     <td width="500"> 
      <?=$info;?> 
    </td> 
    <td width="100"><a href="../editaccomo.php?id=<?=$id;?>">edit</a> <a href="javascript:delArticle('<?=$id;?>', '<?=$title;?>')">delete</a></td> 
  </tr> 
  <? 
    } 
?> 
  <tr> 
    <td width="500">&nbsp;</td> 
	<td width="500">&nbsp;</td> 
    <td width="100">&nbsp;</td> 
  </tr> 
</table> 
<? 

?>

I got this code from a tutorial I worked through and it works great! However I was taught via another tutorial to display data from multiple tables (like the top table in the above webpage) in a different way - the code I used is:

<?php
// connect to the db server
$dbcnx = @mysql_connect ("localhost", "uname", "pword");
if (!$dbcnx) {
echo ("<P>Unable to connect to the " .
"database server at this time. </P>" );
exit ();
}
// Select the accomodation db
if (! @mysql_select_db("swazine_tour") ) {
echo ("<P>Unable to connect to the tourism" .
"database server at this time. </P>" );
exit ();
}
?>

<p>Here is the accomodation listing</P>
<TABLE BORDER=1>
<TR><TH>ID</TH><TH>Name</TH><TH>Classification</TH><TH>Star Rating</TH><TH>Catered/Self-Catering</TH><TH>Info</TH><TH>Directions From</TH><TH>Author/Admin</TH><TH>Edit</TH></TR> 
<?php

$thome = mysql_query(
"SELECT name, info, cid, sid, caid, aid, did, category, rating, catering, author, place " .
"FROM accomodation, classification, rating, catering, authors, directions WHERE cid=classification.id AND sid=rating.id AND caid=catering.id AND aid=authors.id AND did=directions.id");
If (!$thome) {
echo("</TABLE>");
echo("<p> Error retreiving!<BR>".
"Error: " . mysql_error());
exit();
}
while ($tresult = mysql_fetch_array($thome)) {
echo("<TR>\n");
$id = $tresult["id"];
$info = $tresult["info"];
$name = $tresult["name"];
$category = $tresult["category"];
$rating = $tresult["rating"];
$catering = $tresult["catering"];
$author = $tresult["author"];
$place = $tresult["place"];

// Display the  information
echo( "<TD>$id</TD>\n" );
echo( "<TD>$name</TD>\n" );
echo( "<TD>$category</TD>\n" );
echo( "<TD>$rating</TD>\n" );
echo( "<TD>$catering</TD>\n" );
echo( "<TD>$info</TD>\n" );
echo( "<TD>$place</TD>\n" );
echo( "<TD>$author</TD>\n" );
echo( "<TD>test</TD>\n" );
echo("</TR>\n");
}
?>

I guess ideally I would like to use the first code but everytime I try to add tables to:

    $query = "SELECT id, name, info FROM accomodation ORDER BY id"; 
    $result = mysql_query($query) or die('Error, cannot get accomodation from database');

Using the same format as the econd code ie

$thome = mysql_query(
"SELECT name, info, cid, sid, caid, aid, did, category, rating, catering, author, place " .
"FROM accomodation, classification, rating, catering, authors, directions WHERE cid=classification.id AND sid=rating.id AND caid=catering.id AND aid=authors.id AND did=directions.id");

I get the error message 'cannot get accomodation from database'

I have also tried it vice versa - ie tried to put

"<a href="../editaccomo.php?id=<?=$id;?>"

into the second code table format with no success!

Can someone help me out?

Thanks

MrPixel

Originally posted by nhaydock

$thome = mysql_query(
"SELECT name, info, cid, sid, caid, aid, did, category, rating, catering, author, place " .
"FROM accomodation, classification, rating, catering, authors, directions WHERE cid=classification.id AND sid=rating.id AND caid=catering.id AND aid=authors.id AND did=directions.id");

That's a lot of tables! You may want to try to reduce that number.

In any case, you'll need to use table aliases, to avoid ambiguous column references, as well as to make your code easier to read. You will also need JOINs.

I can help you better if I know what fields in the above query are in which tables ...

nhaydock

Hi - thanks for your help

Here are my tables and fields:

accomodation table - id, name, cid, sid, caid, info, address, did, directions, map, phone, fax, email, website, pictures, aid

authors table - id, name, email

catering table - id, catering

classification table - id, category

directions table - id, place

rating table - id, rating

cid=classification.id, sid=rating.id, caid=catering.id, did=directions.id, aid=authors.id

I think the JOINS issue is the thing I am having the most problems with - no matter what I try I cannot edit more than one table at a time via a form.............................?

Thanks again

MrPixel

One thought: perhaps you might merge several of those table. A good rule of thumb would be: if there is only one row in Table B that corresponds to any given row in Table A, and this is always the case, you should probably merge Table A and Table B. Data integrety is more important than short rows.

Originally posted by nhaydock
I think the JOINS issue is the thing I am having the most problems with - no matter what I try I cannot edit more than one table at a time via a form.............................?

heh ... JOINs certainly take a while to grok.

Assuming you follow the suggestion above, I'm guessing you'll end up with 3, or maybe just 2 tables: accomodation, rating, and authors, and the extra *id fields will be in authors and rating (in column called, say, accom_id), and not accomodation. I also assume that rating is a separate table because you have many ratings for each accomodation.

This has the nice effect of making your job getting the info much easier:
SELECT a.* FROM DB_NAME.accomodation a WHERE id ="$id"

But you're still not satisfied, right? You also want to display the average rating (rounded off, no less!) of a given accomodation ... So:
SELECT a.*, ROUND(AVG(r.rating)) AS rating FROM DB_NAME.accomodation a
LEFT JOIN DB_NAME.rating r ON a.id = r.accom_id
WHERE a.id ="$id"
GROUP BY a.id

OK ... so now you got the data, how to update? Merging the tables facilitates this, too ... because each table now contains information that is, when it comes to updating, independent of the other tables: a user clicks on "I loved this place!" and you
INSERT INTO DB_NAME.ratings VALUES ('', $accomodation_id, 5)

not forgetting, of course, to include a hiddden <input> tag in your form with the correct accomodation id ....

And so on, am I making sense?