Page 1 of 1

Sub Select Query. Whats the Right Syntax??

Posted: Thu Jan 27, 2011 1:09 pm
by 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]


Code: Select all

$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)
      
          )";


Code: Select all

$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  

          )";


Code: Select all

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


Code: Select all

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



[text]Any help is appreciated.[/text]

Re: Sub Select Query. Whats the Right Syntax??

Posted: Thu Jan 27, 2011 1:57 pm
by Darhazer

Code: Select all

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

Code: Select all

$query = "INSERT INTO blogs ( member_id, title, entry, blog_date) VALUES ( SELECT member_id FROM members, '{$_POST['title']}', '{$_POST['entry']}', NOW())";
But you are missing WHERE in the subselect and it will return multiplerows, so definitely this is not what you want to do.
You have to submit the member_id via POST as well or if this query is executed for the current user, retrieve member_id from session

Re: Sub Select Query. Whats the Right Syntax??

Posted: Thu Jan 27, 2011 2:10 pm
by drayarms
And any ideas about how I retrieve the member id from the session? Would I have to use some cookie?

Re: Sub Select Query. Whats the Right Syntax??

Posted: Fri Jan 28, 2011 1:44 am
by Darhazer
http:/php.net/manual/en/book.session.php

PHP will handle the cookie for you.
You need to use session_start() and $_SESSION superglobal variable. But do you have a login at all?

Re: Sub Select Query. Whats the Right Syntax??

Posted: Fri Jan 28, 2011 1:48 am
by drayarms
I do have a login. The form that handles the insert query is only accessible upon successful authentication of a user.

Re: Sub Select Query. Whats the Right Syntax??

Posted: Fri Jan 28, 2011 2:59 am
by 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.

Re: Sub Select Query. Whats the Right Syntax??

Posted: Fri Jan 28, 2011 5:55 am
by Darhazer
if the session_id equals to the member_id, the login is insecure. I can just send a cookie with session-id = 1 and login as the first user in the system