CODEWITHSUNDEEP
phpmysql
Greedy
Greedy

Reputation: 115

MySQL returns only last inserted row

Ok, this is MySQL tables structure:

CREATE TABLE IF NOT EXISTS `users` (
`user_id` int(1) NOT NULL AUTO_INCREMENT,
`username` varchar(25) NOT NULL,
`email` varchar(35) NOT NULL,
`password` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

CREATE TABLE IF NOT EXISTS `papers` (
`p_id` int(1) NOT NULL AUTO_INCREMENT,
`user_id` int(1) NOT NULL,
`p_name` varchar(70) NOT NULL,
`p_authors` varchar(70) NOT NULL,
`p_cauthor` varchar(30) NOT NULL,
`p_jname` varchar(50) NOT NULL,
`p_date` varchar(10) NOT NULL,
`p_url` varchar(50) NOT NULL,
UNIQUE KEY `p_url` (`p_url`),
KEY `Foreign_Key` (`p_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;


ALTER TABLE `papers`
ADD CONSTRAINT `papers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);

And with this PHP/MySQL query, it only returns last inserted row, and i want to list all of them:

$result = mysql_query("SELECT u.username, p.p_url FROM users AS u INNER JOIN papers AS p ON p.user_id = u.user_id WHERE u.username='{$_SESSION['user']}'");
$output = mysql_fetch_assoc($result);
if(is_array($output)) { ...
    print_r ($output);

print_r reports only one array element, one that has been last inserted, and i need to list them all. Something is wrong with my query, but i can't figure out what. Thank you.

Upvotes: 1

Views: 88

Answers (1)

Ankur Tiwari
Ankur Tiwari

Reputation: 2782

Try this 

$result = mysql_query("SELECT u.username, p.p_url FROM users AS u INNER JOIN papers AS p ON p.user_id = u.user_id WHERE u.username='{$_SESSION['user']}'");
    while($output = mysql_fetch_assoc($result))
    {
        if(is_array($output)) 
            print_r ($output);
    }

Request You to use PDO or MYSLI to avoid SQL injection.

Upvotes: 1

Related Questions