Page 1 of 1

Linking things in muliple database tables

Posted: Sat Sep 20, 2008 9:44 pm
by CoolAsCarlito
I have a table of events and each event has its own unique id. I have a second table of matches.

What I want to know is how do I link each match in that table to the event id, and also give it a position entry as well.

Re: Linking things in muliple database tables

Posted: Sun Sep 21, 2008 3:26 am
by Punkis
well this is easy. I had the same problem. Here is the solution.

Code: Select all

<?
$TIME = mktime(date("H")+$SETTINGS['timecorrection'],date("i"),date("s"),date("m"), date("d"),date("Y"));
$NOW = date("YmdHis",$TIME);
 
function num_space($num){
  $ret ="";
  for($i=0;$i<$num;$i++)
    $ret .= "&nbsp;";
  return $ret;
}
 
if(empty($_GET[page]) && $_GET[PAGE]){
  $page = intval($_GET[PAGE]);
}
/*
 * Recursive categories tree visit;
 * It returns a list of all not-labeled subcategories
 */
function getsubtree($catsubtree,$i) {
    global $catlist;    
    $res=mysql_query("select * FROM PHPAUCTIONXL_categories WHERE parent_id=".intval($catsubtree[$i]));
    while($row=mysql_fetch_assoc($res)) {
        // get info about this parent
        $catlist[]=$row['cat_id'];
        $catsubtree[$i+1]=$row['cat_id'];
        getsubtree($catsubtree,$i+1);
    }
}
 
if(empty($_GET)) {
    unset($_SESSION['category']);
    unset($_SESSION['catlist']);
}
 
if ($_GET["title"]) {
    if ($_GET['desc'])  $wher .= "((au.description like '%".htmlentities($_GET["title"],ENT_QUOTES)."%') ";
    else $wher .= "((1=0) ";
    if ($_GET["title"]) $wher .= " OR (au.title like '%".htmlentities($_GET["title"],ENT_QUOTES)."%' OR au.id=".intval($_GET["title"]).")) AND ";
    else $wher .= ") AND";
}
if ($_GET["seller"]) {
    $query = "select id from PHPAUCTIONXL_users where nick ='".htmlentities($_GET["seller"],ENT_QUOTES)."'";
    $res = mysql_query($query);
    if(!$res) {
        print "Error: $query<br />".mysql_error();
        exit;
    }
    
    if(mysql_num_rows($res) > 0) {
        $SELLER_ID = mysql_result($res,0,"id");
        $wher .= "(au.user=$SELLER_ID) AND ";
    } else {
        $wher .= "(au.user like '%-------------%') AND ";
    }
}
if ($_GET["buyitnow"]=='y') {
    $wher .= "(au.buy_now > 0) AND ";
}
if ($_GET["buyitnowonly"]=="y") {
    $wher .= "(au.bn_only='y') AND ";
}
if ($_GET["zipcode"]) {
    $wher .= "(au.location_zip like '%".htmlentities($_GET["zipcode"],ENT_QUOTES)."%') AND ";
}
if ($_GET["closed"]) $wher .= "(au.closed IN ('0','1')) AND ";
else $wher .="(au.closed = '0') AND ";
if ($_GET["category"]) {
        $catlist=array();
        $catsubtree[0]=$_GET["category"];
        $catlist[]=$catsubtree[0];
        getsubtree($catsubtree,0);
        $catalist="(";
        $catalist.=join(",",$catlist);
        $catalist.=")"; 
    $wher .= "(au.category IN $catalist) AND ";
}
if (isset($_GET['maxprice'])&&!empty($_GET['maxprice'])) $wher .= "(au.minimum_bid<=".doubleval($_GET['maxprice'])."  ) AND ";
if (isset($_GET['minprice'])&&!empty($_GET['maxprice'])) $wher .= "(au.minimum_bid>=".doubleval($_GET['minprice']).") AND ";
$type = intval($_GET['type']);
if ($type && ($type=='1' || $type == '2')) $wher .= "(au.auction_type='$type') AND ";
$ending = intval($_GET['ending']);
if ($ending && ($ending=='1' || $ending=='2' || $ending=='4' || $ending=='6')) {
    $data=date('YmdHms',$TIME+($ending*86400));
    $wher .="(au.ends<=$data) AND";
}
if ($_GET["country"]) $wher .= "(au.location='".addslashes($_GET["country"])."') AND ";
$payment = $_GET['payment'];
if (is_array($payment)) {
    reset($payment);
    $pri=false;
    foreach($payment as $key=>$val) {
        if (!$pri) $ora .= "AND ((au.payment like '%".addslashes($val)."%')";
        else $ora .= " or (au.payment like '%".addslashes($val)."%')";
        $pri=true;
    }
    $ora .= ") ";
}
$SortProperty = htmlentities($GET['SortProperty'],ENT_QUOTES);
 
if ($SortProperty=='starts'){$by='au.starts DESC';}
else if ($SortProperty=='min_bid'){$by='au.minimum_bid';}
else if ($SortProperty=='max_bid'){$by='au.minimum_bid DESC';}
else {$by='au.ends ASC';}
 
if ((!empty($wher) || !empty($ora)) && isset($_GET['go'])) {
    /* retrieve records corresponding to passed page number */
    $page = (int)$page;
        if (intval($page)==0)  $page = 1;
    $lines = (int)$lines;
    if ($lines==0)  $lines = 50;
    
    /* determine limits for SQL query */
    $left_limit = ($page-1)*$lines;
    
    /* get total number of records */
    $query="select count(*) as total FROM PHPAUCTIONXL_auctions au
            WHERE (au.suspended='0') 
            AND ($wher au.private='n' $ora)
            AND au.starts<=".$NOW." 
            ORDER BY $by";
    $sql = mysql_query($query);
    if ($sql) {
        $hash = mysql_fetch_array($sql);
        $total = (int)$hash['total'];
    } else
    $total = 0;
    
    /* get number of pages */
    $pages = (int)($total/$lines);
    if (($total % $lines)>0)
    ++$pages;
    
    /* get records corresponding to this page*/
    $query="select au.* FROM PHPAUCTIONXL_auctions au
            WHERE (au.suspended='0') 
            AND ($wher au.private='n' $ora)
            AND au.starts<=".$NOW." 
            ORDER BY $by LIMIT ".intval($left_limit).",".intval($lines);
  $sql2 = mysql_query($query);
    // to be sure about items format, I've unified the call
    if(@mysql_num_rows($sql2) > 0) {
        include $include_path."browseitems.inc.php";
        $TPL_auctions_list_value=browseItems($sql2);
        $auctions_count=count($TPL_auctions_list_value);
        
        $TPL_auctions_total_value .= "".
        "<br />".
        "$MSG_290 $total<br />".
        "$MSG_289 $pages ($lines $MSG_291)<br />".
        "$MSG_25_0229";
        parse_str ($_SERVER['QUERY_STRING'], $newpage);
        // reconstruction of the query, with added parameters
        $hrefp="adsearch.php?";
        foreach($newpage as $k=>$v) {
            if(!is_array($v) && $v!='page') $hrefp.=$k.'='.$v.'&';
            else {
                foreach($v as $vk=>$vv) {
                    if(!is_array($vv)) $hrefp.=$k.'['.$vk.']='.$vv.'&';
                    else {
                        foreach($vv as $vvk=>$vvv) {
                            $hrefp.=$k.'['.$vk.']['.$vvk.']='.$vvv.'&';
                        }
                    }
                }
            }
        }
        for ($i=1; $i<=$pages; ++$i) {
            $TPL_auctions_total_value .=
            ($page==$i) ?
            " $i "  :
            "<a href=\"$hrefp"."page=$i\">$i</a> ";
        }
 
    $PAGE  = $page;
    $PAGES = $pages;
        
        $TPL_auctions_total_value .="";
        if ($auctions_count==0) {
            $TPL_auctions_total_value = ""."$ERR_114";
        }
        include phpa_include("template_browse_php.html");
        include "footer.php";
        exit;
    }else{
        $ERR = $ERR_122;
    }
//added parameters for number of pages in the template browse
    $id = "&";
    foreach($_GET as $k => $v){
      if($k!='PAGE' && $k!='page' && $k!='id'){
      $id .= $k."=".$v."&";
      }
    }
    
}
// -------------------------------------- payment
$qurey = "select * from PHPAUCTIONXL_payments";
$res_payment = mysql_query($qurey);
if(!$res_payment) {
    MySQLError($qurey);
    exit;
}
$num_payments = mysql_num_rows($res_payment);
$TPL_payments_list="";
$i = 0;
while($i < $num_payments) {
    $payment_descr = mysql_result($res_payment,$i,"description");
    $TPL_payments_list.="<input type=checkbox name=\"payment[]\" value=\"$payment_descr\"";
    if($payment_descr == $payment[$i]) {
        $TPL_payments_list .= " checked=true";
    }
    $TPL_payments_list .= " />$payment_descr<br />";
    $i++;
}
// -------------------------------------- category
$categories = mysql_query("SELECT * FROM PHPAUCTIONXL_categories_plain");
if($categories){
  while($cat_array=mysql_fetch_array($categories)){
    $cat_arrayaid[]=$cat_array['cat_id'];
    $cat_arrayaname[$cat_array['cat_id']]=$cat_array['cat_name'];
  }
  $sqll="SELECT * FROM PHPAUCTIONXL_cats_translated WHERE lang='".$language."'";
 
  $categ = mysql_query($sqll);
  while($cat_array=mysql_fetch_array($categ)){
    $cat_arrayaname2[$cat_array['cat_id']]=$cat_array['cat_name'];
  }
  $j = 0;
  foreach($cat_arrayaid as $k=>$v){
    $category2 = $cat_arrayaname[$v];
    $num = 0;
    $i = 0;
    while ($i < strlen($category2))
    {
      if (substr($category2,$i,6) == "&nbsp;" ) {
        $num++;
        $i+=5;
      }
      $i++;
    }
    $cat_nuevas[$v]= num_space($num).$cat_arrayaname2[$v];
  }
  $TPL_categories_list = "<select name =\"category\" onChange='javascript&#058;document.adsearch.submit()'>\n";
  $TPL_categories_list.="<option value=''></option>";
  foreach($cat_nuevas as $k=> $v){
    $TPL_categories_list.="<option value=\"".$k."\" ".(($k==intval($_GET[category]))?"selected":"").">".$v."</option\n>";
  }
  $TPL_categories_list.="</select>\n";
}
// Variant fields construction
$cattree=array();
// -------------------------------------- country
$TPL_countries_list="<select name=\"country\">\n";
reset($countries);
foreach($countries as $key=>$val) {
    $TPL_countries_list.=
    "        <option value=\"".
    $val.
    "\" ".
    (($val==$_GET["country"])?" selected=true":"")
    .">".$val."</option>\n";
}
$TPL_countries_list.="</select>\n";
include phpa_include("template_advanced_search.html");
include "footer.php";
?>
 

Re: Linking things in muliple database tables

Posted: Thu Sep 25, 2008 9:39 am
by Skoalbasher
Will this work for a user IP table as well? I have a table of users, and I'd like to log all of their IP addresses whenever they log in. If I could make a table that just held IP addresses and have a link to the user table with the user ID, that'd be awesome. I think I could rig something up, just not sure how efficient it would be.