send("cmpg_booklibraray.xls"); // Add a worksheet to the file, returning an object to add data to $sheet =& $xls->addWorksheet('Books CMPG Library'); //Prapre mySQL Connection $connection=mysql_connect('webdb.zooblythii.unibe.ch:3309', 'cmpg', 'popgen'); mysql_select_db('booklibrary', $connection); $error=''; //Write Legend $sheet->write(0,0,'Label'); $sheet->write(0,1,'Title'); $sheet->write(0,2,'Authors'); $sheet->write(0,3,'Year'); $sheet->write(0,4,'Publisher'); //Write Books $i=0; foreach($_SESSION['booklist'] as $key=>$val){ if(isset($_POST['export'.$key])){ ++$i; $sql="select b.id, b.label, b.title, b.year, p.publisher from book b, publisher p WHERE b.publisher_id=p.id AND b.id=".$val; $query=mysql_query($sql,$connection); $book=mysql_fetch_object($query); $sheet->write($i,0,$book->label); $sheet->write($i,1,$book->title); $sql="SELECT * from author WHERE book_id=".$book->id." order by id asc"; $query=mysql_query($sql,$connection); $text=''; while($author=mysql_fetch_object($query)){ $text=$text.$author->author.';'; } $sheet->write($i,2,$text); $sheet->write($i,3,$book->year); $sheet->write($i,4,$book->publisher); } } // Finish the spreadsheet, dumping it to the browser $xls->close(); } else { if(isset($_POST['export_endnote'])){ header("Content-Type: text/plain; charset=utf-8"); header("Content-Disposition: attachment; filename=cmpg_booklibraray.xml"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0,pre-check=0"); header("Pragma: public"); //Prapre mySQL Connection $connection=mysql_connect('webdb:3309', 'cluster', 'fromcluster'); mysql_select_db('booklibrary', $connection); //Prpare string replacement $char= array('&','ü','ä','ö', 'Ä','Ö','Ü','è','é','à','ç'); $char_ok=array('&','ü','ä','ö', 'Ä', 'Ö', 'Ãœ','è','é','�','ç'); //Write Beginning echo ''; //Write Books $i=0; foreach($_SESSION['booklist'] as $key=>$val){ if(isset($_POST['export'.$key])){ ++$i; $sql="select b.id, b.label, b.title, b.year, p.publisher from book b, publisher p WHERE b.publisher_id=p.id AND b.id=".$val; $query=mysql_query($sql,$connection); $book=mysql_fetch_object($query); echo 'CMPG_Booklibrary.enl'; echo 'EndNote'; echo ''.$book->id.''; echo '6'; echo ''; $sql="SELECT * from author WHERE book_id=".$book->id." order by id asc"; $query=mysql_query($sql,$connection); while($author=mysql_fetch_object($query)){ echo ''; } echo ''; echo '<style face="normal" font="default" size="100%">'.str_replace($char, $char_ok, $book->title).'</style>'; echo ''; echo ''; echo ''; echo ''; } } echo ''; } else { header("cache-control: private"); //insert into database //------------------------------------------------------------------------------ function insert($sql){ global $connection; $query=mysql_query($sql,$connection); if(mysql_affected_rows()==1) return mysql_insert_id(); } //Display a red line //------------------------------------------------------------------------------ function redline(){ ?> '; global $connection; foreach($_SESSION['booklist'] as $key=>$val){ if(isset($_POST['export'.$key])){ $sql="select b.id, b.label, b.title, b.year, p.publisher from book b, publisher p WHERE b.publisher_id=p.id AND b.id=".$val; $query=mysql_query($sql,$connection); $book=mysql_fetch_object($query); $text=$text.'"'.$book->label.'";"'.$book->title.'";"'; $sql="SELECT * from author WHERE book_id=".$book->id." order by id asc"; $query=mysql_query($sql,$connection); while($author=mysql_fetch_object($query)){ $text=$text.$author->author.';'; } $text=$text.'";"'.$book->year.'";"'.$book->publisher.'"
'; } } return $text; } //colors for tables //------------------------------------------------------------------------------ $bgcolor[1]='bgcolor=#ffffff'; $bgcolor[0]='bgcolor=#f3f3ff'; //read search //------------------------------------------------------------------------------ if(isset($_POST['search_button']) && isset($_POST['combo_search']) && isset($_POST['search_text'])){ if(isset($_SESSION['search_text'])) unset($_SESSION['search_text']); if(isset($_SESSION['combo_search'])) unset($_SESSION['combo_search']); if(isset($_SESSION['search'])) unset($_SESSION['search']); $char=array('"',';',"'"); $search_text=str_replace($char, '', $_POST['search_text']); if($search_text!=''){ $_SESSION['search']=1; $_SESSION['search_text']=$search_text; $_SESSION['combo_search']=$_POST['combo_search']; } } if(isset($_POST['show_all_button'])){ if(isset($_SESSION['search_text'])) unset($_SESSION['search_text']); if(isset($_SESSION['search'])) unset($_SESSION['search']); } //check sort //------------------------------------------------------------------------------ if(!isset($_SESSION['old_sort'])) $_SESSION['old_sort'] = 'title'; //check search direction //------------------------------------------------------------------------------ if(isset($_SESSION['sort_direction']) && !isset($_POST['show_all_button'])){ if(isset($_GET['sort']) && $_SESSION['old_sort']==$_GET['sort']){ if($_SESSION['sort_direction']=='desc'){ $_SESSION['sort_direction']='asc'; } else { $_SESSION['sort_direction']='desc'; } } else { if(isset($_GET['sort']) && ($_GET['sort']=='author' || $_GET['sort']=='label' || $_GET['sort']=='title' || $_GET['sort']=='year' || $_GET['sort']=='publisher')){ $_SESSION['old_sort']=$_GET['sort']; } } } else { if(!isset($_POST['show_all_button'])) $_SESSION['sort_direction']='asc'; } //Write new book into database... //------------------------------------------------------------------------------ if(isset($_POST['enter_new_book'])){ $char=array('"',';'); if(isset($_POST['new_label']) && str_replace($char, '', $_POST['new_label'])!='' && strlen(str_replace($char, '', $_POST['new_label']))=='5' && isset($_POST['new_title']) && str_replace($char, '', $_POST['new_title'])!='' && isset($_POST['new_year']) && $_POST['new_year']!='' && isset($_POST['new_publisher_combo']) && ($_POST['new_publisher_combo']!=0 || (isset($_POST['new_publisher']) && $_POST['new_publisher']!='')) && isset($_POST['new_Author_1']) && str_replace($char, '', $_POST['new_Author_1'])!=''){ $sql="SELECT count(*) as num from book WHERE label='".str_replace($char, '', $_POST['new_label'])."'"; $query=mysql_query($sql,$connection); if(mysql_num_rows()==0){ $pub_id=0; if($_POST['new_publisher_combo']==0){ $pub_id=insert('INSERT into publisher (publisher) values("'.str_replace($char, '', $_POST['new_publisher']).'")'); } else { $pub_id=$_POST['new_publisher_combo']; } if($pub_id > 0){ $book_id=insert("INSERT into book (year, title, label, publisher_id, date) values(".str_replace($char, '', $_POST['new_year']).", '".str_replace($char, '', $_POST['new_title'])."', '".str_replace($char, '', $_POST['new_label'])."', ".$pub_id.", now())"); if($book_id > 0){ $author_id=0; for($i=1;$i<10;++$i){ if(isset($_POST['new_Author_'.$i]) && str_replace($char, '',$_POST['new_Author_'.$i])!=''){ $author_id=$author_id+insert("INSERT into author (author, book_id) values('".str_replace($char, '', $_POST['new_Author_'.$i])."', ".$book_id.")"); } } } else { $error=$error."Failed to insert new book!
"; } } else { $error=$error."Failed to insert new publisher!
"; } } else { $error=$error."Label exists!
"; } } else { $error=$error."Data missing or data not in the correct format!
"; } } // Head //------------------------------------------------------------------------------ ?> CMPG Booklibrary

CMPG Booklibrary

Search:
' name='search_text' type='text'>

Title:

 '>

Label:

 '>

Year:

 '>

Publisher:

 '>

Author :

 '>
", chr(10), $text); ?>

Found books!

label==$book_label){ echo "
".$book->author; } else { if($i>=0){ echo "

" ?> >
Label Title Author Year Publisher

id; ?>'>

label; $book_label=$book->label; ?>

title; ?>

author; $old_year=$book->year; $old_publisher=$book->publisher; } } echo "

" ?>