connect one table to another in mysql?

Daisy · Post #1 (**permalink**) 07-20-2005, 08:38 PM

I'm writing a script for my layout database admin center. Basically, it's where admin can log in and add new layouts.

Within a database, i have 2 tables. Table 1 is "series". Table 2 is "layouts".

In "series" table, i have "serid" (id of series) and "sername" (name of series). Examples of series are nature, celebrities...

In "layouts" table, i have "id", "name" (name of layout), "artist", and "viewhtml" (the URL to view the layout).

I add new layouts through a form on "add.php" and it inserts data right into the table "layouts" .

Now, i want to add the series which the layout belongs to (nature, celebrities, blah...) by connecting table "layouts" to table "series". I'm trying to insert the row "serid" into table "layouts". The options of the series should appear on add.php.

How can i do that? I hope you're not lost. I've tried my best to explain it...

ANMMark · Post #2 (**permalink**) 07-20-2005, 10:19 PM

Quote:

Now, i want to add the series which the layout belongs to (nature, celebrities, blah...) by connecting table "layouts" to table "series". I'm trying to insert the row "serid" into table "layouts".

Why not simply add the row serid to the layouts table and use that to determine which series the layout belongs to?

I know you have tried to explain the best you can, but I'm slightly lost as to what you want to do, because I'm unable to see the code that you have, nor can I see a clear idea of what you're trying to accomplish in the script, by the description you have provided (a lot of twists and turns)

In the "layouts" table you currently have
"id", "name", "artist", and "viewhtml"

Why not also add serid to that table?

Daisy · Post #3 (**permalink**) 07-21-2005, 08:26 AM

Ok, i've got it all working now. What i'm trying to do now is to make the series name show up on the layouts.php but it doesn't. Row "serid" has been added to table "layouts".

Code:

<?php

include ('connection.php');

if ($category=="") {

$query = "SELECT * from layouts order by id desc LIMIT 0, 10";
$result= mysql_query($query, $connection) or die
("Could not execute query : $query." . mysql_error());

while ($row=mysql_fetch_array($result))
{
$id=$row["id"];
$namedesign=$row["namedesign"];
$artist=$row["artist"];
$artisturl=$row["artisturl"];
$sername=$row["sername"];
$serid=$row["serid"];
$size=$row["size"];
$previewimage=$row["previewimage"];
$download=$row["download"];
$viewhtml=$row["viewhtml"]; 
$dcounter=$row["dcounter"]; 
$pcounter=$row["pcounter"]; 
$date = $row['date'];
echo "<table border=\"1\" bordercolor=\"#000000\" width=\"100%\"><tr><td colspan=\"2\" align=\"center\"><strong>$name</strong></td></tr>
<tr><td><img src=\"$previewimage\"></td> <td valign=\"top\">
<b>ID:</b>$id<BR>
<b>Name:</b> $name<BR>
<b>Artist:</b> $artist<br>
<b>Series:</b> $sername<br>
<b>Date:</b> $date<br>
</td></tr>
<tr><td colspan=\"2\" align=\"center\">| <A href=\"design.php?id=$id\" target=\"_blank\">Preview</a> ($pcounter) | <a href=\"download.php?id=$id\">Download</a> ($dcounter) | </td></tr>
</table><br>";
}
}

else {

$query = "SELECT * from layouts where category='$category' order by id desc ";
$result= mysql_query($query, $connection) or die
("Could not execute query : $query." . mysql_error());
// dynamic navigation variables
$screen = $_GET['screen'];
$PHP_SELF = $_SERVER['PHP_SELF'];

$rows_per_page=10;
$total_records=mysql_num_rows($result);
$pages = ceil($total_records / $rows_per_page);

if (!isset($screen))
$screen=0;
$start = $screen * $rows_per_page;
$query .= "LIMIT $start, $rows_per_page";
$result= mysql_query($query) or die
("Could not execute query : $query." . mysql_error());

while ($row=mysql_fetch_array($result))
{
$id=$row["id"];
$namedesign=$row["namedesign"];
$artist=$row["artist"];
$artisturl=$row["artisturl"];
$sername=$row["sername"];
$serid=$row["serid"];
$size=$row["size"];
$previewimage=$row["previewimage"];
$download=$row["download"];
$viewhtml=$row["viewhtml"]; 
$dcounter=$row["dcounter"]; 
$pcounter=$row["pcounter"]; 
$date = $row['date'];

echo "<table border=\"1\" bordercolor=\"#000000\" width=\"100%\"><tr><td colspan=\"2\" align=\"center\"><strong>$name</strong></td></tr>
<tr><td><img src=\"$previewimage\"></td> <td valign=\"top\">
<b>ID:</b>$id<BR>
<b>Name:</b> $namedesign<BR>
<b>Artist:</b> $artist<br>
<b>Series:</b> $sername<br>
<b>Date:</b> $date<br>
</td></tr>
<tr><td colspan=\"2\" align=\"center\">| <A href=\"design.php?id=$id\" target=\"_blank\">Preview</a> ($pcounter) | <a href=\"download.php?id=$id\">Download</a> ($dcounter) | </td></tr>
</table><br>";
}
?>

ANMMark · Post #4 (**permalink**) 07-21-2005, 10:46 AM

awesome! Glad you have it working