php and mysql not supplying join

rninja · May 21, 2003

Heres the diagram of the way I've got the database:

DATABASE--> itsafaq
| topics posts
------ -----
(pkey/autoi) topic_id post_id (pkey/autoi)
topic_title topic_id
topic_owner post_text
post_owner

When a php page is generated in the faq, it displays the topic_title and the post_text/post_owner. I am getting a "0" in the database where the topic_id (posts table), is supposed to have the same topic_id that was set in topic_id (topics table). Does anyone know how I can go about doing that? I am unsure as to whether I need to have that coded in php or if that can be addressed in mysql.

I appreciate your help!

Rninja

http://www.rninja.com

DRJ478 · May 22, 2003

Would you mind showing us your SQL statement?

rninja · May 22, 2003

Sure. Here is the code for the page:

<?php

// Check for required fields from the form
if((!$_POST[topic_owner]) || (!$_POST[topic_title]) || (!$_POST[post_text])){
header("Location: add.html&quot

;
exit;
}

// Connect to DB
include('dbconf.php');

// Create and issue the first query
$add_topic = "insert into Faq_Topics values ('','$_POST[topic_title]',now(),'$_POST[topic_owner]')";
mysql_query($add_topic,$conn) or die(mysql_error());

// Create and issue the second query
$add_post = "insert into Faq_Posts values ('','$_POST[topic_id]','$_POST[post_text]',now(),'$_POST[topic_owner]')";
mysql_query($add_post,$conn) or die(mysql_error());

// Create nice message for user
$msg = "<p>The <b>$_POST[topic_title]</b> topic has been created.</p>";
?>

<html>
<head>
<title>New Topic Added</title>
</head>
<body>
<h3>New Topic Added</h3>
<?php print $msg; ?>
<br><br><a href="topics.php">Return to topic list</a>
</body>
</html>

Rninja

http://www.rninja.com

DRJ478 · May 22, 2003

Please explain:

Code:

// Create and issue the first query
$add_topic = &quot;insert into Faq_Topics values ('','$_POST[topic_title]',now(),'$_POST[topic_owner]')&quot;;
mysql_query($add_topic,$conn) or die(mysql_error());

In the above snippet you use

Code:

$_POST[topic_title]

Code:

// Create and issue the second query
$add_post = &quot;insert into Faq_Posts values ('','$_POST[topic_id]','$_POST[post_text]',now(),'$_POST[topic_owner]')&quot;;
mysql_query($add_post,$conn) or die(mysql_error());

In this SQL statement you are using

Code:

$_POST[topic_id]

There is no other occurrence of topic_id, not even in your check for the POSTed vars.

I suspect this is what you want:
Ascertain the new topic_id that was generated by the previous INSERT statement and use it in the Faq_Posts table.
I think you are mixing up the 'post' and POST vars, which come with the HTTP request.

Put the following line right after the creation of the first record in the topics table:

Code:

# get the last inserted ID generated by AUTOINCREMENT
$thisTopicID = mysql_insert_id($conn);

You can use that value in the next SQL statement.

OK?

rninja · May 27, 2003

Thanks, but I decided to build one from scratch to learn how best to write one securely. Thanks for all of your help though!

Rninja

http://www.rninja.com

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

php and mysql not supplying join

rninja

Technical User

DRJ478

IS-IT--Management

rninja

Technical User

DRJ478

IS-IT--Management

rninja

Technical User

Similar threads

Part and Inventory Search

Sponsor