visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Languages :: PHP :: help sorting my results
By: roe1and Great Britain  Date: 17/10/2007 18:10:46  English  Points: 20 Status: Answered
Quality : Excellent
if i have a table that looks like this:

year num id
1970 1 1
1970 2 2
1970 3 3
1970 4 4
1970 5 5
1970 6 6
1970 7 7
1970 8 8
1970 9 9
1970 10 10
1970 11 11
1970 12 12
1971 1 13
1971 2 14
1971 3 15
1971 4 16
1971 5 17
1971 6 18


my output has to look like this:

1970/00/1
1970/00/2
1970/00/3
1970/00/4
1970/00/5
1970/01/6
1970/01/7
1970/01/8
1970/01/9
1970/01/10
1970/02/11
1970/02/12
1971/00/1
1971/00/2
1971/00/3
1971/00/4
1971/00/5
1971/01/6

they have to be group by fives. id is just an auto_increment. please help. i'm too dumb
By: VGR Date: 17/10/2007 19:59:48 English  Type : Comment
OK, having tried, I think this is quite impossible using only one SQL statement. Perhaps a very loooong one with user defined variables, @@ everywhere and at the end a non portable solution ; I therefore suggest you use either a scripting language at the DB level (eg, T-SQL, stored procedure, PL/SQL etc) ***or*** that you use a front-end script.

This is solveable in 20 lines of PHP code IMHO.

this is what I tried :

mysql> select concat(layear,'/0', id DIV 5,'/','01') from test70 order by id asc; +----------------------------------------+ | concat(layear,'/0', id DIV 5,'/','01') | +----------------------------------------+ | 1970/00/01 | | 1970/00/01 | | 1970/00/01 | | 1970/00/01 | | 1970/01/01 | | 1970/01/01 | | 1970/01/01 | | 1970/01/01 | | 1970/01/01 | | 1970/02/01 | | 1970/02/01 | | 1970/02/01 | | 1971/02/01 | | 1971/02/01 | | 1971/03/01 | | 1971/03/01 | | 1971/03/01 | | 1971/03/01 | +----------------------------------------+ 18 rows in set (0.00 sec)



IMHO the solution is to use DIV, but with more caution (stopping on a year change) while incrementing the last part inside the same year... this is NOT solveable in one statement.
By: roe1and Date: 17/10/2007 20:27:08 English  Type : Comment
i am looking for a php solution. i'm quite desperate for help. my php knowledge is not that great and i am quite stuck at the moment
By: VGR Date: 17/10/2007 20:52:19 English  Type : Comment
got it ;-)

is this what you want ?

1970/00/01 1970/00/02 1970/00/03 1970/00/04 1970/00/05 1970/01/06 1970/01/07 1970/01/08 1970/01/09 1970/01/10 1970/02/11 1970/02/12 1971/00/01 1971/00/02 1971/00/03 1971/00/04 1971/00/05 1971/01/06 done.

By: VGR Date: 17/10/2007 20:53:13 English  Type : Answer
25 lines with tags and DB stuff ;-)

<?php @session_start(); require_once('../zarma.php'); GetMyStuff($dbHost,$dbLogin,$dbPassword); $linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect : ".mysql_error()); $dbName = 'test'; mysql_select_db($dbName,$linkID) or die ("bad select DB : ".mysql_error()); $query="select * from test70 order by id asc;"; $result=mysql_query($query,$linkID) or die ("bad query get site status. ".mysql_error()); $prevyear='xxx'; $count=0; while ($res=mysql_fetch_array($result)) { $layear=$res['layear']; $id=$res['id']; if ($layear<>$prevyear) { // Y break $count=0; $prevyear=$layear; } $modulo=floor($count / 5); $count++; // output echo "$layear/0$modulo/".((($count<10)?'0':'').$count).' '; } // while echo "done."; ?>



regards
By: VGR Date: 17/10/2007 20:57:18 English  Type : Comment
just for completeness and to explain the $query above, here are my test data :

create table test70 (id integer unique auto_increment,layear char(4),num integer not null); insert into test70(layear,num,id) values('1970',1,1); insert into test70(layear,num,id) values('1970',2,2); insert into test70(layear,num,id) values('1970',3,3); insert into test70(layear,num,id) values('1970',4,4); insert into test70(layear,num,id) values('1970',5,5); insert into test70(layear,num,id) values('1970',6, 6); insert into test70(layear,num,id) values('1970',7, 7); insert into test70(layear,num,id) values('1970',8, 8); insert into test70(layear,num,id) values('1970',9, 9); insert into test70(layear,num,id) values('1970',10, 10); insert into test70(layear,num,id) values('1970',11, 11); insert into test70(layear,num,id) values('1970',12, 12); insert into test70(layear,num,id) values('1971',1, 13); insert into test70(layear,num,id) values('1971',2, 14); insert into test70(layear,num,id) values('1971',3, 15); insert into test70(layear,num,id) values('1971',4, 16); insert into test70(layear,num,id) values('1971',5, 17); insert into test70(layear,num,id) values('1971',6, 18);

By: roe1and Date: 17/10/2007 22:51:52 English  Type : Comment
thanks a million. it works perfectly. i don't understand all of it, but it works. thanks again.
By: VGR Date: 18/10/2007 07:03:43 English  Type : Comment
Don't forget to "A"ccept an Answer then, by clicking the "A" button on the top-right of the relevant comment ;-)

as for the "all of it", it's quite simple. The algorithm is :

//1 get data in "id" ascendant order $query="select * from test70 order by id asc;"; //2 initialize counters : current year and counter inside that year $prevyear='xxx'; $count=0; //3 while there are results loop while ($res=mysql_fetch_array($result)) { //3.1 get data $layear=$res['layear']; $id=$res['id']; //3.2 if year changed, reset counters if ($layear<>$prevyear) { // Y break $count=0; $prevyear=$layear; } //3.3 compute position in the year's results, apply "group by five" rule $modulo=floor($count / 5); $count++; //3.4 output echo "$layear/0$modulo/".((($count<10)?'0':'').$count).' '; } // end of while



As you may see, the variable $id is read for nothing : it's not used .
By: roe1and Date: 18/10/2007 22:11:25 English  Type : Comment
i finally got it and successfully applied it to my problem. unfortunately i have no programming background so it usually takes me a while to "get" it. i have another question though, if you don't mind. i think i struggled with the loop part.

here is the code:
$query="select * from marc_db1.images order by images.stront asc;";
$result=mysql_query($query) or die ("bad query get site status. ".mysql_error());
$bliksem = 'xxx';
$p_mod= $bliksem;
$prevyear='xxx';
$count=0;
$count1=0;
while ($res=mysql_fetch_array($result)) {
$layear=$res['year'];
$id=$res['id'];
if ($layear<>$prevyear) { // Y break
$count=0;
$prevyear=$layear;
$count1++;
?>mkdir&nbsp;./send_out/<? echo $res['year'];?>/
<?
}
$modulo=floor($count / 100);
$c_mod=$modulo;
if (ereg("^[0-9]+$", $p_mod)) {
if ($c_mod<>$p_mod) {
?>mkdir&nbsp;./send_out/<?
$n_mod= sprintf("%04d",$c_mod);
echo $prevyear;?>/<?echo $n_mod;?>/
<?
$p_mod=$c_mod;
}
}
else {

?>mkdir&nbsp;./send_out/<?
echo $prevyear;?>/<?echo "0000";?>/
<?
$p_mod='0'; }

?>mv&nbsp;<?
$page1 = sprintf("%06d",$count);
echo $res['stront'];?>&nbsp;<? echo "./send_out/$layear/000$modulo/"; echo $page1; ?>.tif
<?
// echo "$layear/0$modulo/".((($count<10)?'0':'').$count).' ';
$count++;
}


you'll see i tried to create another break, i think you called it. :if ($c_mod<>$p_mod), but this didn't work for the first cycle. while yours did, perfectly. that's why i had to determine if its and integer first so it would work the first time. i think the code is completely sloppy but it's the only way i could get it to work.
example: the output looks something like this:
mkdir year1 // this is what if ($layear<>$prevyear) does
mkdir year1/000 // this is what if ($c_mod<>$p_mod) is supposed to do -!!!!-
mv currentdir/file000 newfolder/000
mv currentdir/file001 newfolder/000
etc
etc
mv currentdir/file098 newfolder/000
mv currentdir/file099 newfolder/000
mkdir year/001
mv currentdir/file100 newfolder/001
mv currentdir/file101 newfolder/001
etc
etc
mv currentdir/file198 newfolder/001
mv currentdir/file199 newfolder/001
mkdir year/002
mv currentdir/file200 newfolder/002
mv currentdir/file201 newfolder/002
etc
etc
mv currentdir/file298 newfolder/002
mv currentdir/file299 newfolder/002
if i just use if ($c_mod<>$p_mod) without the if (ereg("^[0-9]+$", $p_mod)) nonsense. i don't get the line marked -!!!!!!- above. everything else works fine.
do you have any idea why this is so?
By: VGR Date: 18/10/2007 23:39:22 English  Type : Comment
I will try to have a look soon
By: roe1and Date: 19/10/2007 15:15:45 English  Type : Comment
don't worry i got it working that's all that matters. thanks again for your help

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 352.300880 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page