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
– davidhigh

2014-05-11 14:02:30 +00:00
Commented May 11, 2014 at 14:02 — davidhigh
– davidhigh, Commented May 11, 2014 at 14:02
Just use a loop in php.

Strawberry
– Strawberry

2014-05-12 13:28:02 +00:00
Commented May 12, 2014 at 13:28 — Strawberry
– Strawberry, Commented May 12, 2014 at 13:28

Community · Accepted Answer · 2017-05-23 10:25:06Z

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 · Accepted Answer · 2014-05-12 13:03:47Z

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>

Collectives™ on Stack Overflow

mysql select nth column from table

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related