CODEWITHSUNDEEP
phpmysql
user655355
user655355

Reputation: 163

PHP MysqlI - How to Select 1 Row? (from select)

I am a bit stumped on how i should be able to Define 1 post ID, and only select from that row my query:

SELECT
    posts.post_id,
    posts.title,
    COUNT(post_likes.id) AS likes,
    GROUP_CONCAT(user.name SEPARATOR '|') AS liked
FROM
    posts
LEFT JOIN post_likes ON post_likes.post_id = posts.post_id
LEFT JOIN user ON post_likes.user = user.id
GROUP BY
    posts.post_id

my end goal is to get all from row 3 (only row 3), not get all rows. when i run the query above, i get every row from mysql.

example:

 $query = "  
SELECT
    posts.post_id,
    posts.title,
    COUNT(post_likes.id) AS likes,
    GROUP_CONCAT(user.name SEPARATOR '|') AS liked
FROM
    posts
LEFT JOIN post_likes ON post_likes.post_id = posts.post_id
LEFT JOIN user ON post_likes.user = user.id
GROUP BY
    posts.post_id 
 ";  
 $result = mysqli_query($connect, $query);  
 while($row = mysqli_fetch_array($result))  
 {  
      echo '<h3>'.$row["title"].'</h3>';  
      echo '<a href="index.php?type=article&id='.$row["post_id"].'">Like</a>';  
      echo '<p>'.$row["likes"].' People like this</p>';  
      if(count($row["liked"]))  
      {  
           $liked = explode("|", $row["liked"]);  
           echo '<ul>';  
           foreach($liked as $like)  
           {  
                echo '<li>'.$like.'</li>';  
           }  
           echo '</ul>';  
      }  
 }

returns with all rows,

what i need to do is just return the 3rd row (from the post_id, and only that row)

CREATE TABLE IF NOT EXISTS `posts` (  
  `post_id` int(11) NOT NULL AUTO_INCREMENT,  
  `title` text,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;    
 --  posts
 INSERT INTO `posts` (`id`, `title`) VALUES  
 (1, 'Ajax Jquery Drag and Drop Shopping Cart using PHP Mysql'),  
 (2, 'Make PHP Hashtag system by using Regular Expression'),  
 (3, 'Ajax Jquery Column Sort with PHP & MySql'),  
 (4, 'Drag and drop Upload multiples File By Ajax JQuery PHP');  
 --  
 CREATE TABLE IF NOT EXISTS `post_likes` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `user` int(11) NOT NULL,  
  `post` int(11) NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=46 ;  
 --table
 CREATE TABLE IF NOT EXISTS `user` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` text NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;   
 --  
 INSERT INTO `user` (`id`, `name`) VALUES  
 (1, 'john'),  
 (2, 'jack');  


CREATE TABLE `post_likes` (
  `id` int NOT NULL,
  `user` int NOT NULL,
  `post_id` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `post_likes`
--

INSERT INTO `post_likes` (`id`, `user`, `post_id`) VALUES
(11111, 3, 3),
(91, 2, 3);

final edit, i kinda just said screw it and just did this:

 $query1 = 'SELECT * FROM posts WHERE post_id = "$page_id"';
 $result1 = mysqli_query($connect, $query1);
 $query2 = 'SELECT * FROM post_likes WHERE post_id = "$page_id"';
 $result2 = mysqli_query($connect, $query2);
 if ($result3 = $connect->query('SELECT * FROM post_likes WHERE post_id = "'.$page_id.'"')) {
     $row_cnt = $result3->num_rows;
     printf("Result set has %d rows.\n", $row_cnt);
 }

thanks to all who helped though, i really appreciate it.

Upvotes: 1

Views: 229

Answers (1)

nbk
nbk

Reputation: 49373

You can use the function ROW_NUMBER() (MySQL 8 MariaDB 10.4)

The principal thing is, that MySQL needs an order to determine which row number is what

I selected

OVER(ORDER BY COUNT(post_likes.id))

But it can be every column from the from clause

CREATE TABLE IF NOT EXISTS `posts` (  

  `post_id` int(11) NOT NULL AUTO_INCREMENT,  
  `title` text,  
  PRIMARY KEY (`post_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;    
 --  posts
 INSERT INTO `posts` (`post_id`, `title`) VALUES  
 (1, 'Ajax Jquery Drag and Drop Shopping Cart using PHP Mysql'),  
 (2, 'Make PHP Hashtag system by using Regular Expression'),  
 (3, 'Ajax Jquery Column Sort with PHP & MySql'),  
 (4, 'Drag and drop Upload multiples File By Ajax JQuery PHP');  
 --  

 CREATE TABLE IF NOT EXISTS `post_likes` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `user` int(11) NOT NULL,  
  `post` int(11) NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=46 ;  
 --table

 CREATE TABLE IF NOT EXISTS `user` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` text NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;   
 --  
 INSERT INTO `user` (`id`, `name`) VALUES  
 (1, 'john'),  
 (2, 'jack');  

WITH CTE AS (SELECT
    posts.post_id,
    posts.title,
    COUNT(post_likes.id) AS likes,
    GROUP_CONCAT(user.name SEPARATOR '|') AS liked
    ,ROW_NUMBER() OVER(ORDER BY COUNT(post_likes.id)) rn
FROM
    posts
LEFT JOIN post_likes ON post_likes.post = posts.post_id
LEFT JOIN user ON post_likes.user = user.id
GROUP BY
    posts.post_id)
SELECT post_id,title, likes,liked FROM CTE WHERE rn = 3

post_id | title                                    | likes | liked
------: | :--------------------------------------- | ----: | :----
      3 | Ajax Jquery Column Sort with PHP & MySql |     0 |      

SELECT post_id,title, likes,liked FROM
(SELECT
    posts.post_id,
    posts.title,
    COUNT(post_likes.id) AS likes,
    GROUP_CONCAT(user.name SEPARATOR '|') AS liked
    ,ROW_NUMBER() OVER(ORDER BY COUNT(post_likes.id)) rn
FROM
    posts
LEFT JOIN post_likes ON post_likes.post = posts.post_id
LEFT JOIN user ON post_likes.user = user.id
GROUP BY
    posts.post_id) t1
 WHERE rn = 3

post_id | title                                    | likes | liked
------: | :--------------------------------------- | ----: | :----
      3 | Ajax Jquery Column Sort with PHP & MySql |     0 |

db<>fiddle here

Upvotes: 1

Related Questions