doodlebug

[quote]Why can't you do this all in a single pass?[/quote] 'cos i don't know how to. I don't fully understand joins  in queries yet. I am looking at them, but i haven't got my head around them. Any help you could give would be greatly appreciated. ;)

Hi Guys, First of all, here is the query i am running: [code=php:0] $query = "select * from plmatchdata where lid='$lid' and matchdate>='$todaysdate' and week='$ww' order by matchdate"; $result = $dbase->query($query); while ($l = mysql_fetch_array($result)) {       $matchid = $l["matchid"];       $nextmatch = $matchid; $querystand="SELECT userid FROM pluserlids WHERE lid='$lid' AND userid NOT IN (SELECT DISTINCT userid FROM plpredictiondata WHERE matchid='$nextmatch')"; $resultstand = mysql_query($querystand,$db); while ($latest_array = mysql_fetch_array($resultstand)) {       $user= $latest_array["userid"];         $query2 = "SELECT email FROM pluserdata WHERE userid='$user'";         $res2 = mysql_query($query2,$db);         $l2 = mysql_fetch_array($res2);         $name2 = $l2["email"];         $query3 = "SELECT username FROM pluserdata WHERE userid='$user'";         $res3 = mysql_query($query3,$db);         $l3 = mysql_fetch_array($res3);         $name3 = $l3["username"]; print("<tr><td class='TBLROW'><font class='TBLROW'>$name3</font></td><td class='TBLROW'><font class='TBLROW'>$user</font></td><td class='TBLROW'><font class='TBLROW'>$name2</font></td></tr></font>"); } } [/code] Based on the structure of the query as it stands at the moment, it is returning the correct results. I am getting a list of userids, usernames and emails where the criteria is met. The problem i have, is that it is returning a list of names, emails etc.. for every criteria met in the first part of the query aswell, so i am getting duplicates. What i would like to know is how to structure the query so it will only return a username etc.. once if it comes up anywhere in the second part of the query.......but i'm stuck  :-( Hope some one you knowledgable chaps can help me out :-) Best regards Doodlebug

@businessman332211 - Many thanks for the link. Read it a couple of times, it made sense.......and best of all it worked. Thanks again!! Neil [url=http://football-prediction-league.a22.biz]http://football-prediction-league.a22.biz[/url]

Too right, lesson learned. As for changing it around, i am still fairly new to php and mysql, and the pre-built part of the script is all defined by variables, and at the moment, i don't think i'm quite up to altering the structure and being able to keep the site working. In the future, once i have a better handle on things, then it pretty much near the top of my list to amalgamate all "17" different tables into something a lot more manageable. For now though, i have to live with what i have  :(

[quote]But football season hasn't even started! Do you mean that cute little sport where the guys throw themselves to the ground in hopes of drawing a foul? On topic, I may have to reread your post to see the logic you're trying to achieve. The complication is probably due to poor database planning.[/quote] :D Thats the sport :D :D I know the season hasn't started but i already have plenty of active users, who always want something different. Regarding the database, i quite agree that it's not the most logically laid out, but i cheated........the basic bare bones and database structure was a pre-built module, so i can't change it unfortunately. I hope my 'logical' explanation may be of some help. That's the way i can see what i need to do.......in (my) laymans terms  ;)  I can post some urls (if i'm allowed) to show what the site looks like, and the example of the script that i wrote that works (and the one that doesn't  :() Neil

Hi guys, I think this sounds complicated, but there may be an easy solution to it. I run a football prediction league which has various leagues and within those leagues, mini leagues as well. I have been asked if it is possible to show the mini league table standings on a separate page, so it can be incorporated into other websites as an <iframe> (or preferable rss, but i haven't got that far yet!!). I have written a piece of code which extracts the top ten standings for the leagues, but i cannot get it to work for the mini leagues. The problem i can't seem to get around, is that the user id's for the mini leagues is stored in a different table to the main league. With me so far??? Here's my code so far: [code] $querystand="select lid, userid, position, pld, won, drawn, lost, gfor, gagainst, points from plstandings where lid='2' order by position asc limit $num_fixtures"; $resultstand = mysql_query($querystand,$db); while ($latest_array = mysql_fetch_array($resultstand)) { $leaugeid= $latest_array["lid"]; $user= $latest_array["userid"]; $position= $latest_array["position"]; $played= $latest_array["pld"]; $won= $latest_array["won"]; $drawn= $latest_array["drawn"]; $lost= $latest_array["lost"]; $gfor= $latest_array["gfor"]; $gagainst= $latest_array["gagainst"]; $points= $latest_array["points"]; [/code] This code works fine for extracting the necessary information just for league id 2. Unfortunately, the private league ids and the private members user ids are in a separate table "plprivmembers". What i am trying to get it to do (in logical english) is: If the league id(lid - in plstandings) =2 and the private league id(pid - in plprivmembers) =6 then select the user ids (userid - from plprivmembers) and extract the users stats from plstandings (as per my code above) I hope i have made some sort of sense, and am not talking complete gobbledegook!!!!! Thanks in advance Neil

Yes, you are right in your assumption, i removed them from the post....sorry, should have mentioned that. Ok, i have amended the code as per the previous post, bow i get an output but it's not parsing the database information in to it. The output can be viewed here [a href=\"http://football-prediction-league.a22.biz/PredictionLeague/test.php\" target=\"_blank\"]http://football-prediction-league.a22.biz/...League/test.php[/a] As i see it, it is connecting to the database ok, running the script ok, but not generating the required results. Any other ideas?? P.S. Thanks for your help already, much appreciated

Oooops, that was quite an obvious error, changed it, but i still get the same error message: Parse error: syntax error, unexpected T_STRING in /xxxxx/xxxxxxx/public_html/PredictionLeague/test.php on line 9

Hi Board, I am a newbie to php mysql (hence the posting in this forum). I have done a tiny snippet of code....that does not work, surprise, surprise!!!! Here's the code [code] <? //connect to mysql //change user and password to your mySQL name and password mysql_connect("localhost","username","password");      //select which database you want to edit mysql_select_db("dbase name"); $result = my_sql query(SELECT COUNT(DISTINCT user_id));    AS how_many_users_have_predicted FROM plpredictionsdata $result = my_sql query(SELECT COUNT(user_id));    AS total_users FROM pluserdata {        $title=$r["pluserdata"];    $who=$r["plpredictionsdata"];       echo "$title <br> users have predicted out of <br> $who "; } ?> [/code] The table names are 'plpredictionsdata' & 'pluserdata', and i want it to parse a string like the last line on the code (above). One problem is that the table 'plpredictionsdata' currently has 48 lines per user (hence the (DISTINCT user_id)), but i keep getting : Parse error: syntax error, unexpected T_STRING in /xxxxx/xxxxxxx/public_html/PredictionLeague/test.php on line 9 Any ideas where i have gone wrong?

Hi Board, I'm afraid i'm pretty much a total newbie regarding php. I have aquired a link management script for which i can find no assistance with. I have installed it but i keep getting the following error message when i try to view the output page (links.php): Warning: Invalid argument supplied for foreach() in /*******/local/home/****/loulousbijouterie.com/silver jewelry/FS_0930B/pages/linkman/links.php on line 44 The code for links.php is this: error_reporting(E_ALL ^ E_NOTICE); require "settings.php"; require_once("header.txt"); $lines = array (); $lines=file($settings['linkfile']); echo "<p class=\"linkman\">"; foreach ($lines as $thisline) { $thisline=trim($thisline); if (!empty($thisline)) { list($name,$email,$title,$url,$recurl,$description)=explode($settings['delimiter'],$thisline); if ($settings['clean'] != 1) {$url="go.php?url=".$url;} echo "<a href=\"$url\" target=\"_new\" class=\"linkman\">$title</a> - $description<br>\n"; } } Line 44 is the 'foreach ($lines as $thisline)' line, but i can't see the problem. Any ideas anyone??