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 .= " ";
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) == " " ) {
$num++;
$i+=5;
}
$i++;
}
$cat_nuevas[$v]= num_space($num).$cat_arrayaname2[$v];
}
$TPL_categories_list = "<select name =\"category\" onChange='javascript: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.