mysql select nth column from table

Question

My DB Table is like this

   id | name | image
--------------------------------
   1  | aa   | a.jpg
--------------------------------
   2  | aa   | b.jpg
--------------------------------
   3  | bb   | c.jpg
--------------------------------
   4  | bb   | d.jpg
--------------------------------
   5  | aa   | e.jpg
--------------------------------
   6  | bb   | f.jpg
--------------------------------
   7  | aa   | g.jpg
--------------------------------
   8  | aa   | h.jpg
--------------------------------
   9  | aa   | i.jpg

And this is my html

<div id="first">
</div>
<div id="second">
</div>
<div id="third">
</div>

First I need to Select * from the table where name="aa"
I know how to do this..
but I want to separate the columns to each div..
1st,4th,7th... ( 1 + (n-1)*3 ) images of name=aa GOES to div#first
2nd,5th,8th... ( 2 + (n-1)*3 ) images of name=aa GOES to div#second
3rd,6th,9th... ( 3 + (n-1)*3 ) images of name=aa GOES to div#third

Finally I need the result like this

<div id="first">
  a.jpg
  g.jpg
</div>
<div id="second">
  b.jpg
  h.jpg
</div>
<div id="third">
  e.jpg
  i.jpg
</div>

Please let me know , how to do this with PHP , MYSQL Sorry For My Bad English

In `div#third`, there should be `ee.jpg`, I guess? – davidhigh May 11 '14 at 14:02 — davidhigh, May 11 '14 at 14:02

score 3 · Answer 1 · edited May 23 '17 at 10:25

The main point here seems to be the data selection. For this, one can use the modulo-operation. So, in the first div, insert all rows with

SELECT image FROM your_table WHERE name='aa' AND MOD(id,3)=1

In the second div insert the result of

SELECT image FROM your_table WHERE name='aa' AND MOD(id,3)=2

And in the third this:

SELECT image FROM your_table WHERE name='aa' AND MOD(id,3)=0

EDIT: wait, it doesn't work directly like this (because the id is not increasing by 1 in the subset with name='aa'). Then you could additionally insert a ROW_NUMBER()-like step, see here. So here is a trial (untested):

SELECT images
FROM (SELECT t.*,
       @rownum := @rownum + 1 AS rownum
       FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r
       WHERE t.name='aa')
WHERE MOD(rownum,3)=1

Try it similarly for the others.

"SELECT * FROM (SELECT t.*, @rownum := @rownum + 1 AS rownum FROM `n3_i` t, (SELECT @rownum := 0) r) WHERE by='1' AND MOD(rownum,4)=0"; I changed the query to this,, n3_i is the table , and I need separate it to 4.. But the result is Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\keralap\a.php on line 68 — Shameel Kadannamanna, May 11 '14 at 15:31

score 0 · Answer 2 · answered May 12 '14 at 13:03

The answer of this question is not working.. So I made another way with PHP..

$a = "";
while($row = mysql_fetch_array(mysql_query("Select * from table where name='aa' "))){
    $a . = $row['image'] . "<!--h-->";
}
$b = explode("<!--h-->",$a);
$c = count($B);
$_a = "";$_b  = "";$_c  = "";$_d = "";
for($i = 1;$i <= $c;$i++ ){
    $rem = $i % 3;
    if($rem == 1){
        $_a .= $b[$num - 1];
    }
    elseif($rem == 2){
        $_b .= $b[$num - 1];
    }
    elseif($rem == 0){
        $_c .= $d[$num - 1];
    }
}

AND MY HTML CODE IS

<div id="first">
  <?=$_a?>
</div>
<div id="second">
  <?=$_b?>
</div>
<div id="third">
  <?=$_c?>
</div>

mysql select nth column from table

2 Answers2