Changeset 625 for trunk/admin/include/functions.php
- Timestamp:
- Nov 30, 2004, 9:26:44 PM (19 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/admin/include/functions.php
r614 r625 467 467 ;'; 468 468 $result = pwg_query( $query ); 469 $datas = array(); 469 470 while ( $row = mysql_fetch_array( $result ) ) 470 471 { 471 472 array_push($cat_ids, $row['category_id']); 473 array_push($datas, array('id' => $row['category_id'], 474 'date_last' => $row['date_last'], 475 'count' => $row['count'])); 476 } 477 $fields = array('primary' => array('id'), 478 'update' => array('date_last', 'count')); 479 mass_updates(CATEGORIES_TABLE, $fields, $datas); 480 481 if (count($cat_ids) > 0) 482 { 483 // find all categories where the setted representative is not possible 472 484 $query = ' 473 UPDATE '.CATEGORIES_TABLE.' 474 SET date_last = \''.$row['date_last'].'\' 475 , nb_images = '.$row['count'].' 476 WHERE id = '.$row['category_id'].' 477 ;'; 478 pwg_query($query); 479 } 480 481 if (count($cat_ids) > 0) 482 { 483 $query = ' 484 SELECT id, representative_picture_id 485 FROM '.CATEGORIES_TABLE.' 485 SELECT id 486 FROM '.CATEGORIES_TABLE.' LEFT JOIN '.IMAGE_CATEGORY_TABLE.' 487 ON id = category_id AND representative_picture_id = image_id 486 488 WHERE representative_picture_id IS NOT NULL 487 489 AND id IN ('.implode(',', $cat_ids).') 490 AND category_id IS NULL 488 491 ;'; 489 492 $result = pwg_query($query); 490 493 while ($row = mysql_fetch_array($result)) 491 494 { 495 // set a new representative element for this category 492 496 $query = ' 493 SELECT image_id494 FROM '.IMAGE_CATEGORY_TABLE.'495 WHERE category_id = '.$row['id'].'496 AND image_id = '.$row['representative_picture_id'].'497 ;';498 $sub_result = pwg_query($query);499 if (mysql_num_rows($sub_result) == 0)500 {501 // set a new representative element for this category502 $query = '503 497 SELECT image_id 504 498 FROM '.IMAGE_CATEGORY_TABLE.' … … 507 501 LIMIT 0,1 508 502 ;'; 509 $sub_sub_result = pwg_query($query);510 if (mysql_num_rows($sub_sub_result) > 0)511 512 list($representative) = mysql_fetch_array(pwg_query($query));513 503 $sub_result = pwg_query($query); 504 if (mysql_num_rows($sub_result) > 0) 505 { 506 list($representative) = mysql_fetch_array($sub_result); 507 $query = ' 514 508 UPDATE '.CATEGORIES_TABLE.' 515 509 SET representative_picture_id = '.$representative.' 516 510 WHERE id = '.$row['id'].' 517 511 ;'; 518 519 520 521 522 512 pwg_query($query); 513 } 514 else 515 { 516 $query = ' 523 517 UPDATE '.CATEGORIES_TABLE.' 524 518 SET representative_picture_id = NULL 525 519 WHERE id = '.$row['id'].' 526 520 ;'; 527 pwg_query($query); 528 } 521 pwg_query($query); 529 522 } 530 523 } … … 791 784 * 792 785 * @param string table_name 793 * @param array db ields786 * @param array dbfields 794 787 * @param array inserts 795 788 * @return void 796 789 */ 797 function mass_inserts($table_name, $dbfields, $ inserts)790 function mass_inserts($table_name, $dbfields, $datas) 798 791 { 799 792 // inserts all found categories … … 802 795 ('.implode(',', $dbfields).') 803 796 VALUES'; 804 foreach ($ inserts as $insert_id => $insert)797 foreach ($datas as $insert_id => $insert) 805 798 { 806 799 $query.= ' … … 833 826 pwg_query($query); 834 827 } 828 829 /** 830 * updates multiple lines in a table 831 * 832 * @param string table_name 833 * @param array dbfields 834 * @param array datas 835 * @return void 836 */ 837 function mass_updates($tablename, $dbfields, $datas) 838 { 839 // depending on the MySQL version, we use the multi table update or N 840 // update queries 841 $query = 'SELECT VERSION() AS version;'; 842 $row = mysql_fetch_array(pwg_query($query)); 843 if (version_compare($row['version'],'4.0.4') < 0) 844 { 845 // MySQL is prior to version 4.0.4, multi table update feature is not 846 // available 847 echo 'MySQL is prior to version 4.0.4, multi table update feature is not available<br />'; 848 foreach ($datas as $data) 849 { 850 $query = ' 851 UPDATE '.$tablename.' 852 SET '; 853 foreach ($dbfields['update'] as $num => $key) 854 { 855 if ($num >= 1) 856 { 857 $query.= ",\n "; 858 } 859 $query.= $key.' = '; 860 if (isset($data[$key])) 861 { 862 $query.= '\''.$data[$key].'\''; 863 } 864 else 865 { 866 $query.= 'NULL'; 867 } 868 } 869 $query.= ' 870 WHERE '; 871 foreach ($dbfields['primary'] as $num => $key) 872 { 873 if ($num > 1) 874 { 875 $query.= ' AND '; 876 } 877 $query.= $key.' = \''.$data[$key].'\''; 878 } 879 $query.= ' 880 ;'; 881 pwg_query($query); 882 } 883 } 884 else 885 { 886 // creation of the temporary table 887 $query = ' 888 DESCRIBE '.$tablename.' 889 ;'; 890 $result = pwg_query($query); 891 $columns = array(); 892 $all_fields = array_merge($dbfields['primary'], $dbfields['update']); 893 while ($row = mysql_fetch_array($result)) 894 { 895 if (in_array($row['Field'], $all_fields)) 896 { 897 $column = $row['Field']; 898 $column.= ' '.$row['Type']; 899 if (!isset($row['Null']) or $row['Null'] == '') 900 { 901 $column.= ' NOT NULL'; 902 } 903 if (isset($row['Default'])) 904 { 905 $column.= " default '".$row['Default']."'"; 906 } 907 array_push($columns, $column); 908 } 909 } 910 $query = ' 911 CREATE TEMPORARY TABLE '.$tablename.'_temporary 912 ( 913 '.implode(",\n", $columns).', 914 PRIMARY KEY (id) 915 ) 916 ;'; 917 pwg_query($query); 918 mass_inserts($tablename, $all_fields, $datas); 919 // update of images table by joining with temporary table 920 $query = ' 921 UPDATE '.$tablename.' AS t1, '.$tablename.'_temporary AS t2 922 SET '.implode("\n , ", 923 array_map( 924 create_function('$s', 'return "t1.$s = t2.$s";') 925 , $dbfields['update'])).' 926 WHERE '.implode("\n AND ", 927 array_map( 928 create_function('$s', 'return "t1.$s = t2.$s";') 929 , $dbfields['primary'])).' 930 ;'; 931 pwg_query($query); 932 $query = ' 933 DROP TABLE '.$tablename.'_temporary 934 ;'; 935 pwg_query($query); 936 } 937 } 938 939 /** 940 * updates the global_rank of categories under the given id_uppercat 941 * 942 * @param int id_uppercat 943 * @return void 944 */ 945 function update_global_rank($id_uppercat = 'all') 946 { 947 $query = ' 948 SELECT id,rank 949 FROM '.CATEGORIES_TABLE.' 950 ;'; 951 $result = pwg_query($query); 952 $ranks_array = array(); 953 while ($row = mysql_fetch_array($result)) 954 { 955 $ranks_array[$row['id']] = $row['rank']; 956 } 957 958 // which categories to update ? 959 $uppercats_array = array(); 960 961 $query = ' 962 SELECT id,uppercats 963 FROM '.CATEGORIES_TABLE; 964 if (is_numeric($id_uppercat)) 965 { 966 $query.= ' 967 WHERE uppercats REGEXP \'(^|,)'.$id_uppercat.'(,|$)\' 968 AND id != '.$id_uppercat.' 969 '; 970 } 971 $query.= ' 972 ;'; 973 $result = pwg_query($query); 974 while ($row = mysql_fetch_array($result)) 975 { 976 $uppercats_array[$row['id']] = $row['uppercats']; 977 } 978 979 $datas = array(); 980 foreach ($uppercats_array as $id => $uppercats) 981 { 982 $data = array(); 983 $data['id'] = $id; 984 $global_rank = preg_replace('/(\d+)/e', 985 "\$ranks_array['$1']", 986 str_replace(',', '.', $uppercats)); 987 $data['global_rank'] = $global_rank; 988 array_push($datas, $data); 989 } 990 991 $fields = array('primary' => array('id'), 'update' => array('global_rank')); 992 mass_updates(CATEGORIES_TABLE, $fields, $datas); 993 } 835 994 ?>
Note: See TracChangeset
for help on using the changeset viewer.