Sub Select Query. Whats the Right Syntax??

[text]I created two tables in a database called ‘members’ and ‘blogs’. The blogs table uses the primary key of the members table (member_id), as a foreign key. The member_id is an auto incremented column in the members table and when I query and print out the rows of this table, the member_id values for the two members I created, turn out to be 1 and 2 as expected. Now when I use a subselect query in an insert statement, to input member_id values into the blogs table, and then query the rows of this table, both member_id values show up as 0. I will display both tables and the insert query for the blogs table below. Can anyone identify the problem? I’m convinced there is something about the subselect query that I’m not getting right. Ill also include the select query that displays the results of the blogs table just in case. [/text]

<?php $query = "CREATE TABLE members ( member_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , username VARCHAR( 50 ) NOT NULL UNIQUE, firstname VARCHAR( 50 ) NOT NULL , lastname VARCHAR( 50 ) NOT NULL , title VARCHAR(10), password VARCHAR( 50 ) NOT NULL , primary_email VARCHAR(100), secondary_email VARCHAR(100), register_date DATE, ip VARCHAR( 50 ) NOT NULL , UNIQUE (username) )"; ?> <?php $query = "CREATE TABLE blogs ( blog_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , member_id INT UNSIGNED, like_id INT UNSIGNED, title VARCHAR( 500 ) NOT NULL, entry VARCHAR( 2000 ) NOT NULL , blog_date DATE )"; ?> <?php $query = "INSERT INTO blogs ( member_id, title, entry, blog_date) VALUES ( 'SELECT member_id FROM members', '{$_POST['title']}', '{$_POST['entry']}', NOW())"; ?> <?php $query= 'SELECT * FROM blogs'; if($r = mysql_query ($query)) {//Run the query. //Retrieve and print every record. while ($row = mysql_fetch_array ($r)) { print " {$row['title']}" ; print " {$row['entry']}" ; print " {$row['blog_date']}" ; print " {$row['member_id']}" ; } } else {//Query didn't run. die (' Could not retrieve the data becasue: .mysql_error(). '); }//End of query IF. mysql_close(); //Close the database connection. ?>

Any help is appreciated.

that was not the right syntax. coz, the outer query expects a number for member_id and the inner query returns a result set instead.

here, you should the id of the member who posted the blog… that can probably be attained thru a session of the logged in member or some other means. but if u write a query, you are actually trying to fit all the member ids at the place of one.

hope you understood what i meant

@byraja. I actually understand what you mean. The users in order to gain access to the form that handles the insert query, are authenticated users. In that case, how do I obtain the member id from the members table, that would correspond to the session id of the current logged in user?

Ok guys, thank you all for the insights. I didn’t realize that in the login script, I had passed the member_id as the session id. I simply used this and bingo, my problem was solved.

Sponsor our Newsletter | Privacy Policy | Terms of Service