|
PHP - User Generated Selections
I'm not sure of the appropriate subject line for this feature, but hopefully I'm close. I'll try to describe what I'm trying to do. The most common place you see this is on web based file uploads so I'll use it as my example.
Let's say I want to allow my user to upload files so there is a text box where they enter the file name. The user is allowed to upload as many files as he wants with 1 push of the submit button. Upon the loading of the form, there will be 5 textboxes for file upload, but if the user wants to upload more than 5 files, there is a link that is clicked and I think via ajax or javascript another textbox will be added. The user can do this for as many files he wants to upload. This is what it looks like to the user, but then when the form is submitted how are these variables referenced? So how is this setup in the main form and how are the variables referenced after form submission? If anyone can point me to a good web tutorial on this type of thing I would appreciate it. Thanks Carl Similar TutorialsHi. I have a rating site where users fill in a name of what they are rating, comments about it and a rating and submit it to my database. I then have another page which shows all of these entries but only the name and overall rating using php. What I want is for users to click on the name and it take them to a separate page which displays all the comments and ratings for that item. As users can add items to rate I cant set up a page for each manually so was wondering how to do it dynamically. Everything i throw into google leads me in the wrong direction though. If someone could point me to a good tutorial or give me an idea about what to search id be very grateful. Cheers. I’m writing a small application that allows me/users to add their own inputs so they can store their own data. It’s kind of like, a user defined database. Think of a super simple “filemaker” kind of application. The way I’ve set this up is like this:
Table: item fields: item_id, user_id Table item_attribute Fields: item_attribute_id, item_id, name, valueAfter my query I end up with stuff like this: $data = array( 0=>array( '123'=>array( 'name'=>'Year', 'value'=>'1985' ), '7'=>array( 'name'=>'Title', 'value'=>'Title For 1985' ), '25'=>array( 'name'=>'Length', 'value'=>'60' ) ), 1=>array( '123'=>array( 'name'=>'Year', 'value'=>'1990' ), '7'=>array( 'name'=>'Title', 'value'=>'Title For 1990' ), '25'=>array( 'name'=>'Length', 'value'=>'44' ) ), 2=>array( '123'=>array( 'name'=>'Year', 'value'=>'1965' ), '7'=>array( 'name'=>'Title', 'value'=>'Title For 1965' ), '25'=>array( 'name'=>'Length', 'value'=>'122' ) ) );This seems to work great as there is no limit to what a user can add - it's working like I would like it to... But now that I’m trying to add sorting/filtering/pagination to results things have gotten extremely difficult on the back end. The only thing I can think of is to pull all the data and then use php algorithms to sort/filter/paginate before sending over to web page. I can see this getting slow if I eventually have to pull 1000s of records. Does anyone have advice in these matters? Should I rethink my db design? Can the results be used to create a temporary table and then sort on it? (don'n know how or if this is even possible) I need some help Thanks. Edited by fivestringsurf, 16 November 2014 - 12:28 PM. Hi everyone, I need to retain the checkbox selections after the form is submitted, but I'm stuck and wondering if anyone can offer some assistance? Thank you.
<?php
$categories = mysqli_query($db,'SELECT id, category FROM category');
if (!$categories) {
exit('<p>Unable to obtain category list from the database.</p>');
}
// Check if there are any categories and if so, list them.
if (mysqli_num_rows($categories) != 0){
echo '<p>Select the related categories:<br />';
while ($category = mysqli_fetch_array($categories)) {
$categoryid = $category['id'];
$categoryname = htmlspecialchars($category['category']);
$checked= ($category['id']==$categories) ? ' checked="checked"' : '';
echo '<input type="checkbox" name="categories[]" value="'.$categoryid.'" '.$checked.' > '.$categoryname.' <br/>';
}
}
?>
This select box below does remember and highlight -one- selection after submitting the form. But when i make it multiple, it doesn't highlight any of the selections after submitting. Any idea about how to achieve this? Thanks in advance. Code: [Select] <?php $options_amount = array("0","1","2","3","4","5","6","7","8","9","10+"); $no_way = $_GET['no_way']; ?> <select class="postform" name="no_way[]" multiple size="5"> <option <?php if ($no_way == 'all') { ?>selected="selected"<?php }?> value="all">Any</option> <?php foreach ($options_amount as $option) { ?><option <?php if ($no_way == $option) { ?>selected="selected"<?php }?> value="<?php echo $option; ?>"><?php echo $option; ?></option><?php }?> </select> Hey all, Another question for you. How does one make it so that the selections (choices) a user selects for dynamic selection boxes remain after the form is posted, which does post back to itself? I've tried JavaScript using the "name" of the selection box as the id but I must be doing something wrong because it's not working..as usual lol. The values in the selection boxes are populated dynamically so I'm not sure how to do this. Thank you. Hi Guys, I have successfully managed to get my select drop down boxes to populate from a DB which i am very proud of as I'm still learning PHP but I really need some help on the next part please... I want to display a price based on the drop down selections so in my DB i have this... id colours size quantity sides price stock variations 1 1 A6 0-99 SINGLE 200 100g GLOSS 1 2 1 A6 100-199 SINGLE 300 100g GLOSS 1 What would i need to add to my PHP so that it checks the selections made by the user and displays the relevant price in the price filed in my page????? Hopefully someone can help. thanks Craig Hi I have tried the mysql forum but have had no joy with an answer to my problem so wondered if php would be better. I want my users to be able to select from 5 different drop down lists where they can chose any combination from 1 up to all 5, I have attached the front end. These lists are being populated from mysql tables. Code for the drop down lists is as follows Code: [Select] <form action="horse-events-devon.php?url_countyid=<?php echo urlencode ($url_countyid ['url_countyid']) ; ?>&go" method="POST"> <table id="searchtable"> <tr> <th>Find By Discipline</th> <th>Find By Venue</th> <th>Find By Championship</th> <th>Find By Organiser</th> <th>Equine Association</th> <th>Submit Your Selections</th> </tr> <tr> <td><select name="dis_id"> <?php $upcomingdis = upcomingdis($url_countyid); $upcoming_dis_bycounty = mysql_fetch_assoc ($upcomingdis); ?> <?php do { ?> <option value="<?php echo $upcoming_dis_bycounty ['dis_id']; ?>" > <?php echo $upcoming_dis_bycounty ['dis_description']; ?></option> <?php } while ($upcoming_dis_bycounty = mysql_fetch_assoc ($upcomingdis)); ?></select></td> <td><select name="ven_id"> <?php $upvenbycounty_set = upcoming_venevents_bycounty($url_countyid); $upcoming_ven_bycounty = mysql_fetch_assoc ($upvenbycounty_set); ?> <?php do { ?> <option value="<?php echo $upcoming_ven_bycounty ['ven_id']; ?>" > <?php echo $upcoming_ven_bycounty ['ven_name']; ?></option> <?php } while ($upcoming_ven_bycounty = mysql_fetch_assoc ($upvenbycounty_set)); ?></select></td> <td><select name="champ_id"> <?php $championship_set = findchampionships(); $champlist = mysql_fetch_assoc ($championship_set); ?> <?php do { ?> <option value="<?php echo $champlist ['champ_id']; ?>" > <?php echo $champlist ['champ_description']; ?></option> <?php } while ($champlist = mysql_fetch_assoc ($championship_set)); ?></select></td> <td> <select name="org_id"> <?php $uporgbycounty_set = upcoming_organevents_bycounty($url_countyid); $upcoming_org_bycounty = mysql_fetch_assoc ($uporgbycounty_set); ?> <?php do { ?> <option value="<?php echo $upcoming_org_bycounty ['org_id']; ?>" ><?php echo $upcoming_org_bycounty ['org_name']; ?></option> <?php } while ($upcoming_org_bycounty = mysql_fetch_assoc ($uporgbycounty_set)); ?></select> </td> <td><select name="ass_id"> <?php $upassbycounty_set = upcoming_assevents_bycounty($url_countyid); $upcoming_assbycounty = mysql_fetch_assoc ($upassbycounty_set); ?> <?php do { ?> <option value="<?php echo $upcoming_assbycounty ['ass_id']; ?>" > <?php echo $upcoming_assbycounty ['ass_description']; ?></option> <?php } while ($upcoming_assbycounty = mysql_fetch_assoc ($upassbycounty_set)); ?></select></td> <td><input name="submit" type="submit" /><input name="countyid" type="hidden" value="<?php echo $url_countyid ['url_countyid']; ?>" /></td> </tr> </table> </form> My search processing is as follows Code: [Select] <?php if (isset($_POST['submit'])){ if (isset($_GET['go'])){ $countyid = $_POST['countyid']; $ven_id = $_POST['ven_id']; $dis_id = $_POST['dis_id']; $champ_id = $_POST['champ_id']; $org_id = $_POST['org_id']; $event_id = $row['event_id']; $sql = "SELECT DATE_FORMAT (events.startdate, '%a, %d, %b') AS stdate, events.event_id, events.title, events.ven_id, events.org_id, venue.county_id, venue.ven_id, eventdisciplines.event_id, eventdisciplines.dis_id, county.county_id, discipline.dis_id \n" . "FROM events \n" . "LEFT OUTER JOIN eventdisciplines \n" . "ON events.event_id = eventdisciplines.event_id \n" . "LEFT OUTER JOIN discipline \n" . "ON eventdisciplines.dis_id = discipline.dis_id \n" . "LEFT OUTER JOIN venue \n" . "ON events.ven_id = venue.ven_id \n" . "LEFT OUTER JOIN county \n" . "ON venue.county_id = county.county_id \n" . "WHERE events.ven_id = ({$ven_id} OR events.org_id = {$org_id})\n" . "AND events.startdate > NOW()\n" . "AND venue.county_id = {$countyid} \n" . "ORDER BY startdate ASC"; $result = mysql_query ($sql, $connection); ?> How am I best to do this please? my OR within the mysql does not work, should I not be doing this with php in the search processing? someones help would really be appreciated, just to point me in the right direction. [attachment deleted by admin] I would appreciate your assistance, there are tons of login scripts and they work just fine. However I need my operators to login and then list their activities for the other operators who are logged in to see and if desired send their clients on the desired activity. I have the login working like a charm and the activities are listed just beautifully. How do I combine the two tables in the MySQL with PHP so the operator Logged in can only make changes to his listing but see the others. FIRST THE ONE script the member logges in here to the one table in MSQL: <?php session_start(); require_once('config.php'); $errmsg_arr = array(); $errflag = false; $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } function clean($str) { $str = @trim($str); if(get_magic_quotes_gpc()) { $str = stripslashes($str); } return mysql_real_escape_string($str); } $login = clean($_POST['login']); $password = clean($_POST['password']); if($login == '') { $errmsg_arr[] = 'Login ID missing'; $errflag = true; } if($password == '') { $errmsg_arr[] = 'Password missing'; $errflag = true; } if($errflag) { $_SESSION['ERRMSG_ARR'] = $errmsg_arr; session_write_close(); header("location: login-form.php"); exit(); } $qry="SELECT * FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'"; $result=mysql_query($qry); if($result) { if(mysql_num_rows($result) == 1) { session_regenerate_id(); $member = mysql_fetch_assoc($result); $_SESSION['SESS_MEMBER_ID'] = $member['member_id']; $_SESSION['SESS_FIRST_NAME'] = $member['firstname']; $_SESSION['SESS_LAST_NAME'] = $member['lastname']; session_write_close(); header("location: member-index.php"); exit(); }else { header("location: login-failed.php"); exit(); } }else { die("Query failed"); } ?> ................................................. ................................ Now I need the person who logged in to the table above to be able to make multiple entries to the table below <? $ID=$_POST['ID']; $title=$_POST['title']; $cost=$_POST['cost']; $activity=$_POST['activity']; $ayear=$_POST['aday']; $aday=$_POST['ayear']; $seats=$_POST['special']; $special=$_POST['seats']; mysql_connect("xxxxxx", "xxx350234427", "========") or die(mysql_error()); mysql_select_db("xxxx") or die(mysql_error()); mysql_query("INSERT INTO `activity` VALUES ('ID','$title', '$cost','$activity', '$aday', '$ayear', '$special', '$seats')"); Print "Your information has been successfully added to the database!" ?> Click <a href="member-profile.php">HERE</a> to return to the main menu <?php ?> hi, i have made a website where people resgister their details of them and products. they have to enter the following details in form Name of company name of the product company address email id password mobile number contact and brief details about their company
user can then login with email id and pwd. now after login ..user will get a page where he can upload the photos of products images and their price, so now my question is that when he finishes uploading (|by clicking on upload button) the product images and price text box ..then on final uploaded webspage it should show all other things which he registerd before (company name , mobile number etc) along with images and price...hence the main question that user does not need to enter mobile and address while uploading images and filling proce ..but on the final page it should show mobile and address along with price and images..as user is not going to enter mobile and address again and again as he will have multiple products to upload.
Hi, so far I have managed to set up a somewhat basic login website with a mysql database backend. Once they have logged on they go to a "main menu" page. What I need to define is that user A sees button A but only that button, etc. (Then of course that same rule would have to apply if they tried to directly go to the page, but I am guessing I can do that in the same way that I currently do to force a login). If anyone has any tutorials or sample code I would much appreciate it. Thanks, Actually, what i want to do is to use the email to fetch the $email,$password and $randomnumber from database after On a website currently under development the URL does not change when navigating around on the page. It always displays the root URL. This is the script that should generate the links: $URLvars=substr($_SERVER["PHP_SELF"], strrpos($_SERVER["PHP_SELF"],"/")+1); $vatLink=$URLvars; $thisPageURL=$_SERVER["PHP_SELF"]; if(strpos($thisPageURL, "secure/locktech/")!==false)$thisPageURL=substr($_SERVER["PHP_SELF"], 16); $prodLink="http://www.lock-tech.co.uk".$thisPageURL; It used to. However, the server has changed and it does not any more. Is it maybe because the 2 paths (http://www.lock-tech.co.uk and secure/locktech/) must be changed according to the new server? Does anyone know what exactly do these paths stand for? I understand that one of them is the root path but what does the path in the line Code: [Select] if(strpos($thisPageURL, "secure/locktech/")!==false)$thisPageURL=substr($_SERVER["PHP_SELF"], 16); stand for? I'm trying to generate RSS by using PHP and MySQL but I keep getting an error message that I don't know how to solve. Here's the error message: <b>Deprecated</b>: Function mysql_db_query() is deprecated in <b>G:\xampp\htdocs\xampp\dsa\class_rss.php</b> on line <b>32 Here's line 32: Code: [Select] $result = mysql_db_query (DB_NAME, $query, LINK); Here's the entire code from the class: Code: [Select] <? // code followed from http://www.webreference.com/authoring/languages/xml/rss/custom_feeds/ // images sourced: http://www.inyourbasket.com/search/usb-flash-drives/09A?cm_mmc_o=7BBTkw*Vzbp+mwzygt*JmP+4wFByY+mfbgLl*JmP+4wFByY+mfbgL+C+7wEwybg&gclid=CKuGh8DwyKcCFYFB4QodyGRvBg class RSS { // function to include the file which holds global variables. public function RSS() { require_once ('connectvars.php'); } // function derived from both private functions. public function GetFeed() { return $this->getDetails() . $this->getItems(); } // function to connect to the database private function dbConnect() { DEFINE ('LINK', mysql_connect (DB_HOST, DB_USER, DB_PASSWORD)); } // function to retreive data from the table, and insert between set xml tags which loops through the rows in the table. private function getDetails() { $stick = "stick,"; $location = "location,"; $identification = "identification"; $this->dbConnect($stick, $location, $identification); $query = "SELECT * FROM ". $stick . $location . $identification; $result = mysql_db_query (DB_NAME, $query, LINK); while($row = mysql_fetch_array($result)) { $details = '<?xml version="1.0" encoding="ISO-8859-1" ?> <rss version="2.0"> <channel> <title>'. $row['stick.make'] .'</title> <description>'. $row['stick.colour'] . $row['location.street'] . $row['location.town'] . $row['location.city'] .'</description> <image> <url>'. $row['identification.image_url'] .'</url> <width>'. $row['identification.image_width'] .'</width> <height>'. $row['identification.image_height'] .'</height> </image>'; } return $details; } // function to iterate through the table, retreiving data each time and outputting an end result of an RSS structured page. private function getItems() { $stick = "stick,"; $location = "location,"; $identification = "identification"; $this->dbConnect($stick, $location, $identification); $query = "SELECT * FROM ". $stick . $location . $identification; $result = mysql_db_query (DB_NAME, $query, LINK); $items = ''; while($row = mysql_fetch_array($result)) { $items .= '<item> <title>'. $row["stick.make"] .'</title> <description><![CDATA['. $row["stick.colour"] .']]></description> </item>'; } $items .= '</channel> </rss>'; return $items; } } ?> Could anyone help me out here? Thanks Hi, I am getting frustrated beyond belief at the moment with trying to get a very simple script to run, I am using PHP 5.3.3 and MySQL 5.1 on a Win2k8 server with IIS7.5. Basically my script is connecting to a local database, running a single select query, returning those rows and building up a string from them. The problem is that I am receiving complete BS responses from PHP that the access is denied for the user being specified. This is complete rubbish since the user can connect via mysql, sqlyog, ASP.NET MVC without issue but for some bizarre reason it is not working via PHP. The code for the script is here : Code: [Select] <?php $mysql = mysql_connect('127.0.0.1:3306', 'myuser', 'mypass', 'mydatabase'); if (!$mysql) { die(mysql_error()); $content = "<nobr></nobr>"; } else { $result = mysql_query('SELECT * FROM tblEventGroup'); $content = "<nobr>"; if ($result) { while($row = mysql_fetch_assoc($result)) { $content .= "<span>"; $content .= $row['GroupName']; $content .= "</span>"; $content .= "<a href=\"../Event/EventSearch?groupid="; $content .= $row['GroupId']; $content .= "\" target=\"_blank\">Book here</a> "; } } mysql_close($mysql); $content .= "</nobr>"; } ?> I cannot for the life of me understand what the problem is, the return error is Access denied for user 'myuser'@'localhost' (using password: YES) Hi guys, I am trying to put together a little system that allows users to log onto my website and access there own personal page. I am creating each page myself and uploading content specific to them which cannot be viewed by anyone else. I have got the system to work up as far as: 1/ The user logs in 2/ Once logged in they are re-directed to their own page using 'theirusername.php' Thats all good and working how I need it too. The problem I have is this. If I log onto the website using USER A details - I get taken to USER A's page like I should but - If I then go to my browser and type in USERBdetails.php I can then access USER B's page. This cannot happen!! I need for USER A not to be able to access USER B profile - there is obviously no point in the login otherwise! If you are not logged in you obviously cannot access any secure page. That much is working! Please find below the code I am using: LOGIN <?php session_start(); function dbconnect() { $link = mysql_connect("localhost", "username", "password") or die ("Error: ".mysql_error()); } ?> <?php if(isset($_SESSION['loggedin'])) { header("Location:" . strtolower($username) . ".php"); if(isset($_POST['submit'])) { $username = mysql_real_escape_string($_POST['username']); $password = mysql_real_escape_string($_POST['password']); $mysql = mysql_query("SELECT * FROM clients WHERE username = '{$username}' AND password = '{$password}'"); if(mysql_num_rows($mysql) < 1) { die("Password or Username incorrect! Please <a href='login.php'>click here</a> to try again"); } $_SESSION['loggedin'] = "YES"; $_SESSION['username'] = $username; $_SESSION['name'] header("Location:" . strtolower($username) . ".php"); } ?> HEADER ON EACH PHP PAGE <?php session_start(); if(!isset($_SESSION['loggedin'])) { die(Access to this page is restricted without a valid username and password); ?> --------------------------------------------------- Am I right in thinking it is something to do with the "loggedin" part? The system I have here is adapted from a normal login system I have been using for years. The original just checks the details and then does a 'session start'. This one obviously has to re-direct to a user specific page. To do this I used the <<header("Location:" . strtolower($username) . ".php");>> line to redirect to a page such as "usera.php" or "userb.php" Any help would be greatly appreciated! Ta I have something like this on one of my pages: $name = 'Bob'; Code: [Select] if ((isset($_POST['name'])) || ($_POST['name'] == '')) { $name = $_POST['name']; //update database } echo '<form action='' method='POST'>'; echo '<table><tr><td width="125">Name: </td><td><input type="text" name="signature" style="width: 270px;" value="' . $name . '" /></td></tr></table>'; echo '</form>'; Here's the problem: Initially, because the form data has not been sent the input box says: 'Bob'. If I remove the name Bob from the input box by manually clicking in it and deleting the name, and I submit the form, it keeps showing 'Bob', whereas I would like it to be empty. What am I doing wrong? Hello I've spent the last few hours writing a small php file that creates a signature for Steam (a gaming platform) and saves it as a png. It basically just pulls the neceassary info and images from the user's Steam XML stream and uses GD to arrange it nicely. A link to an example is here. Now, the trouble I have is that it saves the image as a .php file (it can be downloaded as a png but not linked to as one, images.php.png doesn't exist). I can navigate to the webpage and save as a png but for obvious reasons can't link to it as an image (making the forum signature part kind of hard to pull off). Is there any easy way (I'm unspeakably bad with php) that I can save the php graphic as a PNG to an external page (for example /sig_name.png or /images.php?id=name.png)? Thanks for any help I'm wanting to make a sitemap for my website but as it's quite big there are a lot of url's I need to include. I was wondering if anyone know's of a way this can be done using PHP. e.g. using PHP to generate a list of URL's. I had a think myself but wasn't sure how to do it. Thanks for any help. I currently have a query that compiles a League Standings Table (the full code is below) and generate the ranking with Code: [Select] @rownum := @rownum+1 AS rank This works fine for the main standings page but I want to use the row numbers elsewhere. Each team has it's own page which uses 'team.team_id' as its 'recordID'. On these pages I would like to show that particular teams ranking in the standings. The two options I see would be to run the query filtering by 'team.team_id' but that would only return the one record so ranking would always be '1' or run the whole query and then somehow find which ranking relates to 'team.team_id' (something that may be possible with VIEWS but the database is running on MySQL4) but I cannot figure out how to get around this. For example, if the standings were Rank Team 1 Rovers 2 United 3 City 4 Rangers 5 Town 6 Athletic 7 Wanderers 8 Hotspur On the 'Rangers' page I would want it to show 'RANKING: 4' and on the 'Athletic' page it would show 'RANKING: 6'. On top of this I would want to show a small version of the rankings with one team above and one team below (it would actually be two teams but I will keep it simple until I understand it!) so one again given the two examples above I would get RANGERS PAGE Rank Team 3 City 4 Rangers 5 Town ATHLETIC PAGE Rank Team 5 Town 6 Athletic 7 Wanderers The query is $i = 1; $ht = "g.home_team = t.team_id"; $at = "g.away_team = t.team_id"; $hw = "g.home_goals > g.away_goals"; $aw = "g.home_goals < g.away_goals"; $d = "g.home_goals = g.away_goals"; $hg ="g.home_goals"; $ag ="g.away_goals"; $table = mysql_query("SELECT t.team_name as Tm, @rownum := @rownum+1 AS rank , (sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS P , (sum(CASE WHEN (".$ht." AND ".$hw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$ht.") AND ".$d." THEN 1 ELSE 0 END)) AS HP , (sum(CASE WHEN (".$at." AND ".$aw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS AP , count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP , sum(CASE WHEN (".$ht." ) THEN 1 ELSE 0 END) as HGP , sum(CASE WHEN ".$at." THEN 1 ELSE 0 END) as AGP , sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W , sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW , sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW , sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D , sum(CASE WHEN (".$ht." AND ".$d.") THEN 1 ELSE 0 END) AS HD , sum(CASE WHEN (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS AD , sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L , sum(CASE WHEN (".$ht." AND ".$aw.") THEN 1 ELSE 0 END) AS HL , sum(CASE WHEN (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS AL , SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF , SUM(CASE WHEN (".$ht.") THEN ".$hg." END) as HGF , SUM(CASE WHEN (".$at.") THEN ".$ag." END) as AGF , SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA , SUM(CASE WHEN (".$ht.") THEN ".$ag." END) as HGA , SUM(CASE WHEN (".$at.") THEN ".$hg." END) as AGA , (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD , (SUM(CASE WHEN (".$ht.") THEN ".$hg." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." END)) as HGD , (SUM(CASE WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$at.") THEN ".$hg." END)) as AGD from teams t left join all_games g on t.team_id in (g.home_team,g.away_team) WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' GROUP BY t.team_id ORDER BY P desc, GD desc, GF desc The html is Code: [Select] <table width="" border="0" cellpadding="0" cellspacing="0" BORDER=1 RULES=ROWS FRAME=BOX> <tr> <td></td><td></td> <td colspan="9" align="center" bgcolor="#00FF99">ALL</td> <td colspan="9" align="center" >Home</td> <td colspan="9" align="center">Away</td> </tr> <tr> <td class="hdcell" >POS</td> <td class="hdcell" >Team</td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> <td width="30" class="hdcell"></td> <td></td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> <td></td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> </tr> <?php while ($row_table = mysql_fetch_assoc($table)){ echo '<tr> <td style="text-align:left" width="30">'.$i.'</td>'; echo '<td style="text-align:left">'.$row_table['Tm'].'</td> <td style="text-align:left">'.$row_table['GP'].'</td> <td style="text-align:left">'.$row_table['W'].'</td> <td style="text-align:left"> '.$row_table['D'].'</td> <td style="text-align:left"> '.$row_table['L']. '</td> <td style="text-align:left"> '.$row_table['GF']. '</td> <td style="text-align:left"> '.$row_table['GA']. '</td> <td style="text-align:left"> '.$row_table['GD']. '</td> <td style="text-align:left"> '.$row_table['P']. '</td> <td style="text-align:left"></td> <td style="text-align:left"></td> <td style="text-align:left">'.$row_table['HGP'].'</td> <td style="text-align:left">'.$row_table['HW'].'</td> <td style="text-align:left">'.$row_table['HD'].'</td> <td style="text-align:left"> '.$row_table['HL']. '</td> <td style="text-align:left"> '.$row_table['HGF']. '</td> <td style="text-align:left"> '.$row_table['HGA']. '</td> <td style="text-align:left"> '.$row_table['HGD']. '</td> <td style="text-align:left"> '.$row_table['HP']. '</td> <td style="text-align:left"></td> <td style="text-align:left">'.$row_table['AGP'].'</td> <td style="text-align:left">'.$row_table['AW'].'</td> <td style="text-align:left">'.$row_table['AD'].'</td> <td style="text-align:left"> '.$row_table['AL']. '</td> <td style="text-align:left"> '.$row_table['AGF']. '</td> <td style="text-align:left"> '.$row_table['AGA']. '</td> <td style="text-align:left"> '.$row_table['AGD']. '</td> <td style="text-align:left"> '.$row_table['AP']. '</td> </tr>'; $i++; } ?> </table> As always, thanks in advance for any tips or suggestions, even if they are telling me to scrap what I have so far and start again!! Steve PS. Can someone explain why @rownum := @rownum+1 AS rank causes $i to increment by one? I found the code while searching and do not understand why $i is necessary unless it is a reserved reference. The way the code looks, I should be able to output the ranking using 'rank' but that does not work. I have no problem with how it does things, it would just be nice to understand! Hey all, I have a relationship where a zone has many posts and a category has many zones but a zone can also have many categories. The zones are basically parts of a page, such as the main area and sidepanel stored in database as records with name fields such as 'panel' and 'main'. There's a serious flaw right now and I would like to know the best solution to address it. Right now I access the posts of a zone of a category like this: Code: [Select] $category->zone->get()->post->get(); The above ends up displaying all the posts in the database for any given category because when category is selected, it searches for the associating zones, and then selects all posts related to those zones, which is wrong. i want to select all posts of the zones associated with that particular category. Current database: Code: [Select] posts id zone_id zones id name categories_zones id zone_id category_id categories id What should I do? Add a category_id column to the posts table and relate the post directly to the category as well as directly to the zones table so the post table would have two foreign keys: category_id and zone_id Would this be the most effective thing to do given my requirements? Thanks for response. |
|||||||