Sub Select Query. Whats the Right Syntax??

[drayarms]

[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.

 

 

 

 

 

 

[Maq]

(I put

tags around your code, please use them next time)

[Psycho]

Well, the problem is that you aren't running a sub query.

 

$query = "INSERT INTO blogs
              ( member_id, title, entry, blog_date)
          VALUES
              ( 'SELECT member_id FROM members', '{$_POST['title']}', '{$_POST['entry']}', NOW())";

 

That query is attempty to put the literal text string 'SELECT member_id FROM members' into the "member_id" field. Since that field only accepts integers it won't accept the text and uses 0 instead.

 

I really don't understand what you are trying to accomplish here, but you can't do what you are trying to do. The problem is that you must have separate, "complete" records to input. It seems you are trying to one record for each user into theblogs table using the same values for the title, entry and blog_date. I guess that is what is confusing me. It doesn't make sense that you would create duplicate blog entries for all users.

 

Anyway, if that is what you really, really want to do I think the only way is with two queries. One to get the list of users, then use that data to create a second query to insert the records into the blog table:

//Query the members table for the IDs
$query = "SELECT member_id FROM members";
$result = mysql_query($query);

//Create array to hold the blog records to be inserted
$insertValues = array();
$title = mysql_real_escape_string($_POST['title']);
$entry = mysql_real_escape_string($_POST['entry']);

//Loop through members resutls to populate the values array
while($member = mysql_fetch_assoc($result))
{
    $insertValues[] = "('{$member['member_id']}', '{$title}', '{$entry}', NOW())";
}

//Create and run insert query with all values
$query = "INSERT INTO blogs
              ( member_id, title, entry, blog_date)" . implode(', ', $insertValues);
$result = mysql_query($query);

 

Note, you could also set up the blog_date field so the default value will be the current timestamp, that way you don't need to include that in the INSERT query.

[Psycho]

OK, I don't know what I was thinking. You CAN do this with a single query. Not tested, but it should go something like this:

$title = mysql_real_escape_string($_POST['title']);
$entry = mysql_real_escape_string($_POST['entry']);

$query = "INSERT INTO blogs
              (member_id, title, entry, blog_date)
          SELECT member_id, {$title}, {$entry}, NOW()
          FROM members";

[drayarms]

@mjdamato,  let me explain to you what I'm trying to accomplish.  The aim is to create a site with several users(whose attributes are stored in the members table), who can contribute entries into a blog.  Each blog entry would have a title and an entry.  I made the member_id a foreign key in the blogs table for the purpose of database normalization.  The idea is to somehow link every member to their blog entries so that each blog entry can be referenced to its contributing author(member) in a select query.  So hopefully you get what I'm driving at.  With this in mind, what would be your recommended approach?  Note that all members are authenticated before being able to access the form that handles the insert into blogs query. 

[drayarms]

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.

[Psycho]

The idea is to somehow link every member to their blog entries so that each blog entry can be referenced to its contributing author(member) in a select query.

 

My confusion comes from the fact that your original code appeared to be attempting to associate every member with the blog entry being created in the INSERT query.

 

Since you state that you've resolved this I am making the post as solved.