Problem with Foreign Key in MySQL!

[cinnamonsuiParticipant]

I’ve googled like crazy to find out what I’m doing wrong, but with no luck. I’m trying to set a Foreign Key on belongs_to_cat in the posts-table that should reference to the cat_id in the category-table. At first I didn’t use INDEX because it has always worked without it before (in older versions?), but from what I understand you should have and INDEX on the column that should be referenced?

Anyway, I get this error:

“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘REFERENCES category (cat_id) )ENGINE = InnoDB’”

The SQL:

CREATE TABLE IF NOT EXISTScategory(

cat_id int(3) NOT NULL AUTO_INCREMENT,

cat_name varchar(255) NOT NULL,

PRIMARY KEY (cat_id)

)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS posts (

post_id int(10) NOT NULL AUTO_INCREMENT,

title varchar(255) NOT NULL,

post_date date NOT NULL,

post text NOT NULL,

belongs_to_cat int(3) NOT NULL,

PRIMARY KEY (post_id),

INDEX (belongs_to_cat),

FOREIGN KEY belongs_to_cat REFERENCES category (cat_id)

)ENGINE = InnoDB;

What am I doing wrong?

[cinnamonsuiParticipant]

Alright, so I tried to do it through the phpmyadmin UI instead. I imported this code:

CREATE TABLE IF NOT EXISTSposts (

post_id int(10) NOT NULL AUTO_INCREMENT,

title varchar(255) NOT NULL,

post_date date NOT NULL,

post text NOT NULL,

belongs_to_cat int(3) NOT NULL,

PRIMARY KEY (post_id)

) ENGINE=InnoDB;

And the foreign key and the reference was set with the UI-options. When I export the SQL to look at the code, it produces this;

ALTER TABLEposts

ADD CONSTRAINT posts_ibfk_1 FOREIGN KEY (belongs_to_cat) REFERENCES category (cat_id);

So my first attempt didn’t work because I didn’t have a CONSTRAINT?

Well it doesn’t work anyway. I tried to add a new row with;

INSERT INTOposts (title, post_date, post, belongs_to_cat)

VALUES (‘Your first post’, ’2012-05-06′, ‘This is your first post’, 1);

But then I get this error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (pixelit_nova.posts, CONSTRAINT posts_ibfk_1 FOREIGN KEY (post_id) REFERENCES category (cat_id))

Im going nuts!

[VeraParticipant]

According to the official MySQL documentation, yeah you need CONSTRAINT as well. http://dev.mysql.com/doc/refman/5.1/en/create-table.html (there’s an example in one of the comments).

As for your second error, do you have the category with id 1, in the categories table?

[cinnamonsuiParticipant]

I deleted the whole database and started over I added the CONSTRAINTs and now it all works!

[cinnamonsuiParticipant]

Now I have another problem!

I’m displaying all blogposts in one page and I want to display the category each post belongs to. I’m trying to do it with the code below, but somehow it doesn’t work. It only shows the cat_id number and not the category name!

<?php

$cat = mysql_query("SELECT * FROM posts, category WHERE posts.belongs_to_cat = $category");

if($catrow = mysql_fetch_array($cat)) {

?>

<span class="cat"><b>Category:</b> <?php echo $catrow; ?></span>

<?php } else {

echo '<b>Category:</b> Couldnt find a category!';

}

?>

What could be wrong?

[endrohatParticipant]

fetch category name from category table since belongs_to_cat is an integer

[endrohatParticipant]

that would be $catrow;

[cinnamonsuiParticipant]

I’ve tried $catrow; as well, but then it’s the first category (the name) in the table that shows up on every post. So that didn’t work either

[Anonymous]

Try editing your code to this

while($catrow = mysql_fetch_array($cat)) {

?>

<span class="cat"><b>Category:</b> <?php echo $catrow['cat_name']; ?></span>

<?php } else {

echo '<b>Category:</b> Couldnt find a category!';

}

[endrohatParticipant]

<?php

$cat = mysql_query(“SELECT * FROM posts, category WHERE posts.belongs_to_cat = $category”);

if(mysql_num_rows($cat))

{

while($catrow = mysql_fetch_array($cat)) {

?>

<span class=”cat”><b>Category:</b> <?php echo $catrow; ?></span>

<?php }

}

else {

echo ‘<b>Category:</b> Couldnt find a category!’;

}

?>

[cinnamonsuiParticipant]

If I try your code, it prints out this on each post on the page:

“Category: Random Category: Portfolio Category: Random Category: Portfolio Category: Random Category: Portfolio Category: Random Category: Portfolio Category: Random Category: Portfolio”

so that didn’t work either!

The whole page looks like this:

<?php include ("includes/includes.php"); ?>

<?php include ("header.php"); ?>

<div id="blogPosts">

<?php

$query = "SELECT * FROM posts ORDER BY post_date DESC LIMIT $from, $post_restr";

if ($results = mysql_query ($query)) {

?>

<h2>Post-admin</h2>

<?php

while ($row = mysql_fetch_array($results)) {

$title = $row;

$category= $row;

$id = $row;

$post = $row;

$post = nl2br ($post);

$post_date = date_create($row[post_date]);

$post_time = date_create($row[post_date]);

?>

<div class="postadmin">

<div class="apost">

<h3><?php echo $title ?></h3>

<div class="postinfo">

<?php

$cat = mysql_query("SELECT * FROM posts, category WHERE posts.belongs_to_cat = $category");

if(mysql_num_rows($cat))

{

while($catrow = mysql_fetch_array($cat)) {

?>

<span class="cat"><b>Category:</b> <?php echo $catrow;

?></span>

<?php }

}

else {

echo '<b>Category:</b> Couldnt find a category!';

}

?>

<span class="date"><b>Created:</b> <?php echo date_format($post_date, 'Y-m-d'); ?> - <?php echo date_format($post_time, 'H:i'); ?></span>

</div>

<div class="postadmin_post">

<?php echo substr($post,0,150); ?>...

</div>

<div class="postadminbtns">

<table class="edittable">

<tr>

<td>

<form action="edit.php" method="post">

<fieldset class="fieldseteditdelete">

<input type="hidden" name="post_id" value="<?echo $id; ?>">

<input type="submit" name="submit" class="editbtn" value="Edit">

</fieldset>

</form>

</td>

<td>

<form action="delete.php" method="post">

<fieldset class="fieldseteditdelete">

<input type="hidden" name="post_id" value="<?echo $id; ?>">

<input type="submit" name="submit" class="deletebtn" value="Delete">

</fieldset>

</form>

</td>

</tr>

</table>

</div>

</div>

</div>

<?php

}

?>

<?php

} else {

die ("<p>Query-error. Error: <b>" . mysql_error() . "</b></p>n");

}

?>

<div class="pagination">

<ul id="pagination-posts">

<?php

$total_results = mysql_fetch_array(mysql_query("SELECT COUNT(*) as num FROM posts"));

$total_pages = ceil($total_results / $post_restr);

if ($page > 1) {

$prev = ($page - 1);

echo "<li class='previous'><< Nyare";

}

for($i = 1; $i <= $total_pages; $i++) {

if ($page == $i) {

echo "<li class='active'>$i";

}

else {

echo "<li class='num'>$i ";

}

}

if ($page < $total_pages) {

$next = ($page + 1);

echo "<li class='next'>Äldre >>";

}

?>

</div>

</div>

<?php include ("footer.php"); ?>

[endrohatParticipant]

<?php include (“includes/includes.php”); ?>

<?php include (“header.php”); ?>

<div id=”blogPosts”>

<?php

$query = “SELECT * FROM posts ORDER BY post_date DESC LIMIT $from, $post_restr”;

if ($results = mysql_query ($query)) {

?>

<h2>Post-admin</h2>

<?php

while ($row = mysql_fetch_array($results)) {

$title = $row;

$category= $row;

$id = $row;

$post = $row;

$post = nl2br ($post);

$post_date = date_create($row[post_date]);

$post_time = date_create($row[post_date]);

?>

<div class=”postadmin”>

<div class=”apost”>

<h3><?php echo $title ?></h3>

<div class=”postinfo”>

<?php

$cat = mysql_query(“SELECT * FROM posts, category WHERE posts.belongs_to_cat = $category and posts.id=$id “);

if(mysql_num_rows($cat))

{

while($catrow = mysql_fetch_array($cat)) {

?>

<span class=”cat”><b>Category:</b> <?php echo $catrow;

?></span>

<?php }

}

else {

echo ‘<b>Category:</b> Couldnt find a category!’;

}

?>

<span class=”date”><b>Created:</b> <?php echo date_format($post_date, ‘Y-m-d’); ?> – <?php echo date_format($post_time, ‘H:i’); ?></span>

</div>

<div class=”postadmin_post”>

<?php echo substr($post,0,150); ?>…

</div>

<div class=”postadminbtns”>

<table class=”edittable”>

<tr>

<td>

<form action=”edit.php” method=”post”>

<fieldset class=”fieldseteditdelete”>

<input type=”hidden” name=”post_id” value=”<?echo $id; ?>”>

<input type=”submit” name=”submit” class=”editbtn” value=”Edit”>

</fieldset>

</form>

</td>

<td>

<form action=”delete.php” method=”post”>

<fieldset class=”fieldseteditdelete”>

<input type=”hidden” name=”post_id” value=”<?echo $id; ?>”>

<input type=”submit” name=”submit” class=”deletebtn” value=”Delete”>

</fieldset>

</form>

</td>

</tr>

</table>

</div>

</div>

</div>

<?php

}

?>

<?php

} else {

die (“<p>Query-error. Error: <b>” . mysql_error() . “</b></p>n”);

}

?>

<div class=”pagination”>

<ul id=”pagination-posts”>

<?php

$total_results = mysql_fetch_array(mysql_query(“SELECT COUNT(*) as num FROM posts”));

$total_pages = ceil($total_results / $post_restr);

if ($page > 1) {

$prev = ($page – 1);

echo “<li class=’previous’><< Nyare”;

}

for($i = 1; $i <= $total_pages; $i++) {

if ($page == $i) {

echo “<li class=’active’>$i”;

}

else {

echo “<li class=’num’>$i “;

}

}

if ($page < $total_pages) {

$next = ($page + 1);

echo “<li class=’next’>Äldre >>”;

}

?>

</div>

</div>

<?php include (“footer.php”); ?>

[JemKeymaster]

No need to do 2 queries where 1 would be sufficient… replace first query with

SELECT posts.*, category.cat_name FROM posts LEFT JOIN category ON posts.belongs_to_cat = category.cat_id ORDER BY post_date DESC LIMIT $from, $post_restr

This joins the category table on to posts via the belongs_to_cat column using the cat_id

You should then be able to get the category through $row

(we use LEFT JOIN instead of a normal JOIN so that posts are selected even if no category is assigned)

[cinnamonsuiParticipant]

I tried this;

<?php

$cat = mysql_query("SELECT posts.*, category.cat_name FROM posts LEFT JOIN category ON posts.belongs_to_cat = category.cat_id ORDER BY post_date DESC LIMIT $from, $post_restr");

if(mysql_num_rows($cat))

{

while($catrow = mysql_fetch_array($cat)) {

?>

<span class="cat"><b>Category:</b> <?php echo $catrow['cat_name'];

?></span>

<?php }

}



else {

echo '<b>Category:</b> Couldnt find a category!';

}

?>

The output becomes this (when it should be only one of them);

“Category: Portfolio Category: Random Category: Random”

It’s been a while since I looked at this but I still can’t figure it out!

[Author]

Posts