mysql result laid out by column value

[hyster]

i have a database with a column for countrys.

ive been trying to google a way to display the results so that each country is in its own column but i can only find results for sorting the results by column.

 

 

so id like something like

 

uk usa russia france

data data data data

data data data

data data

 

 

i can do it by having a query for each country but im guessing theres a way to do it easier.

id be gratefull if some1 can point me in the right direction

thx

[Jessica]

I wrote a tutorial about this that should help you. The way I displayed the data was different, but you should be able to take the idea and figure it out from there. Give it a shot and let me know if you need help with it. 

 

http://thewebmason.com/tutorial-parent-child-lists/

 

The basic concept is using an ORDER in your query, and then a variable to hold the country name of the previous row.

[hyster]

the 2nd part is exactly what im after.

 

big diffrence is that im pulling from 1 table . tanks_owned, then i need country and name.

country=>name.

 

the below code errors at line 10 with Call to a member function fetch_assoc()

 

(im on pain killers atm so im not thinking clearly enough :$)

 

 

 

<?php
$locations = array();
$var = "hyster";//comes from a diffrent query

$sql = "SELECT * from tanks_owned where player = '$var'";

$res=mysql_query($sql);
while ($row = $res->fetch_assoc()){
$name = $row['name'];
$country = $row['country'];
$tank_id = $row['tank_id'];
$locations[$country][$tank_id] = $row['name'];
}
?>

<h1>Our Locations</h1>
<?php
foreach($locations AS $country=>$name){
echo "<div>
<h3>{$country}</h3>
<ul>";
foreach($country AS $name){
echo "<li>{$name}</li>";
}
echo "</ul></div>";

}
?>

[Barand]

mydata

INSERT INTO countrydata (country, item) VALUES
('uk', 'item 1'),
('usa', 'item 2'),
('uk', 'item 3'),
('russia', 'item 4'),
('france', 'item 5'),
('france', 'item 6'),
('usa', 'item 7'),
('uk', 'item 8'),
('france', 'item 9'),
('russia', 'item 10');

 

 

my method

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$sql= "SELECT country, GROUP_CONCAT(item ORDER BY item SEPARATOR '<br>') as items
   FROM countrydata
   GROUP BY country";
$res = $db->query($sql);

while (list($country, $items) = $res->fetch_row()) {
   echo "<div style='width:150px; padding:10px; float:left;'>
   <h4>$country</h4>$items
   </div>";
}

[Jessica]

Yeah if you only ever need that one column, Barand's way works great too.

[hyster]

im getting Call to a member function fetch_row()

if i understand the error it means theres no data

i echo'd the sql and copied into phpmyadmin and the query works

i echo'd the result and got Resource id #5

 

mysql_connect("localhost", "$username", "$password") or die(mysql_error());
mysql_select_db("$dbname") or die(mysql_error());
$sql= "SELECT country, GROUP_CONCAT(name ORDER BY country SEPARATOR '<br>') as name	
 FROM tanks_owned	
 GROUP BY country";
$result=mysql_query($sql);
while (list($country, $name) = $result->fetch_row()) {	
	 echo "<div style='width:150px; padding:10px; float:left;'>	
	 <h4>$country</h4>$name
			 </div>";
}

[Jessica]

You need to check for MySQL errors.

 

And stop putting your variables in strings. 

[Barand]

I was using object mysqli.

 

change

while (list($country, $name) = $result->fetch_row()) { 

 

to

while (list($country, $name) = mysql_fetch_row($result)) { 

[hyster]

cheers barand that worked a treat.

im trying to alter the sql a bit now to only pull for a certain user.

 

 

SELECT country, GROUP_CONCAT(name ORDER BY country SEPARATOR '<br>') as name

FROM tanks_owned

GROUP BY country where player = 'hyster'

 

i tried to put this into a sub query but my sql skills are basic

 

SELECT * from tanks_owned where player = 'hyster'

(SELECT country, GROUP_CONCAT(name ORDER BY country SEPARATOR '<br>') as name

FROM tanks_owned

GROUP BY country )

[Barand]

Bone up on SELECT syntax and the positions of the various clauses. WHERE goes before GROUP BY

 

SELECT country, GROUP_CONCAT(name ORDER BY name SEPARATOR '<br>') as name
FROM tanks_owned
   where player = 'hyster'
GROUP BY country

Edited February 14, 2013 by Barand

[hyster]

once again big thanks barand