heraldic2

First let me say sorry for the confusing post. The setup is confusing and is very difficult to put into words. However thank you for the answers as combined they gave me the perfect answer. Put the products into a multidimensional array and then remove the current product and pull out a random product afterwards. As always you folks rock! Thank you very much

Greetings all, I have four products that can be either version 1 or version 2. I will never know before hand which version they are. It is entered into the database by an intranet page. I am trying to take these 4 products, sort them by their version and then I need to take a product and pair it with another product of the same version, but not itself. EXAMPLE product 1 v1 product 2 v2 product 3 v1 product 4 v1 array(product 1, product 3, product 4) product 1 needs a random pairing from the array that is not itself. The way the company has the database setup this can not be done in the query. table has product1_name, product1_version, product2_name, product2_version and so on. So I pull each product name and product version out of the database and put them in variables Product1=product1_name product1version=product1_version I have hit a wall though on how best to go about this. other than re-doing the database table can anyone lend a hand? Thanks for your time.

Thanks to all. I will go re-design the code.

Thank you PFMaBiSmAd for your advice. I tried what you said and I still get the same results, Team E is left off the list. Thank you very much for trying though. Twas a mighty effort.

Thank you for your response PFMaBiSmAd As for $row and $row1 I was working on a different idea for something not dealing with the current problem. As I am a total n00b at this I hate to question you, but if the result were getting a row before the while loop starts then wouldn't the problem be that the first result is not being displayed not the last result?

Thank you smerny for your reply. I understand what you are telling me about my while loop logic, however I am not at all sure as to how to fix it. I am thinking that I need to re-write the query so that: $i = 0 while $row < $i run code $i = $i + 1 Am I at least in the ball park?

I have a database that contains results of fantasy football games. I have a query that returns the results of the games team X played against the other teams in the league. The query works perfect in the database. Results are the query and the php code cycle thru each team that team X has played and brings up team X's record and the results of the last game played. My problem is that the code stops one team short. So Team X played teams A, B, C, D, E but the code only returns teams A, B, C, D E is left off. I can't for the life of me figure out what silly mistake I made. <?php include_once('../other/functions.php'); $con = mysql_connect($hostname, $username, $password) OR DIE ('Unable to connect to database! Please try again later.'); $db = mysql_select_db($dbname, $con); $thing = $_GET['thing']; $query = "select selected.teamname AS selected_team, selected_score.score AS selected_score, week.week, year, home_id, ". "target_score.score as target_score, target.teamname as targetname, week.ID ". "from owners as selected ". "JOIN game_scores AS selected_score ON selected.owner_id = selected_score.team_id ". "JOIN game_setup ON game_setup.game_id = selected_score.game_id ". "JOIN game_scores AS target_score ON target_score.game_id = game_setup.game_id AND target_score.team_id != selected_score.team_id ". "JOIN owners AS target ON target.owner_id = target_score.team_id ". "JOIN week ON week.week = game_setup.week ". "WHERE selected.owner_id = $thing ". "and target.active = 1 ". "GROUP BY target.teamname, year, week.ID "; $result = mysql_query($query); $row = mysql_fetch_array($result); $result1 = mysql_query($query); $row1 = mysql_fetch_array($result1); if (!$result) { die('Invalid query: ' . mysql_error()); } $wins=0; $losses=0; $draws=0; $last_target = false; echo '<h2>' . $row1['selected_team'] . ' vs. Active Teams</h2>'; while ($row = mysql_fetch_assoc($result)) { if ($last_target['targetname'] != $row['targetname']) { if ($last_target) { printf(' <table border="1" width="600"> <tr> <th>Opponent Name:</th> <th colspan="2">%s</th> </tr> <tr> <th>Wins</th> <th>Losses</th> <th>Draws</th> </tr> <tr> <td align="center">%d</td> <td align="center">%d</td> <td align="center">%d</td> </tr> <tr> <th>Last Game:</th> <td colspan="2">%s of %s: %.1f - %.1f</td> </tr> </table><br/>', $last_target['targetname'], $wins, $losses, $draws, $last_target['week'], $last_target['year'], $last_target['selected_score'], $last_target['target_score'] ); } $wins = $losses = $draws = 0; } if ($row['selected_score'] < $row['target_score']) ++$losses; elseif ($row['selected_score'] == $row['target_score']) ++$draws; else ++$wins; $last_target = $row; } ?> Any ideas on how to make the magic elvish be good??? Thank you for your time.

Thank you very much for the advice. I didn't even think that joining a table onto itself would cause that. I will see what I can do to get the condition working. Thanks again.

Greetings, You folks have helped me many times in the past and I have come, once again, seeking the wisdom of the elders. I have a fantasy football league. I have unsuccessfully created a query that will return the 5 all time lowest winning scores (all time being after 2008 due to a scoring system over haul). Query as it is now: select setup.game_id, home.teamname as Home_Team, max(home_score.score) as Home_Score, away.teamname as Away_Name, max(away_score.score) as Away_Score, ABS(Min(home_score.score) - min(away_score.score)) as Diff, week, year from owners as home join game_scores as home_score on home_score.team_id = home.owner_id join game_setup as setup on setup.game_id = home_score.game_id join game_scores as away_score on away_score.game_id = setup.game_id AND away_score.team_id != home_score.team_id join owners as away on away.owner_id = away_score.team_id where year > 2008 and home_score.score < 300 and away_score.score < 300 group by home.teamname, away.teamname order by setup.game_id limit 10 Results are: game_id Home_Team Home_Score Away_Name Away_Score Diff week year 276 Team McCarthy 299.4 Vanilla Gorillaz 287.4 12.0 Week 1 2009 276 Vanilla Gorillaz 287.4 Team McCarthy 299.4 12.0 Week 1 2009 331 Indian Trail Sillynannies 243.1 Carolina Mighty Mackerel 189.2 53.9 Week 8 2009 331 Carolina Mighty Mackerel 189.2 Indian Trail Sillynannies 243.1 53.9 Week 8 2009 403 Pink Panthers 291.3 Speed Demons 272.0 19.3 Round 3 2009 403 Speed Demons 272.0 Pink Panthers 291.3 19.3 Round 3 2009 406 Team Super Trojans 225.7 Indian Trail Sillynannies 276.0 50.3 Round 3 2009 406 Indian Trail Sillynannies 276.0 Team Super Trojans 225.7 50.3 Round 3 2009 596 Squad Sweepers 217.4 America Enforcers 287.8 70.4 Week 7 2011 596 America Enforcers 287.8 Squad Sweepers 217.4 70.4 Week 7 2011 As you can see the query does return the correct games, however it lists the games twice and flips the home team and away team. Any clue as to what I did incorrectly? As always thank you very much for your time.

Thank you for your advice. With a bit of working I was able to take what you told me and fix the problem. It works great now. Thanks again

I have a MySQL query that works, as it displays the results I want in the database, however when I put that query into some php code it ignores the first row and I was wondering if someone could point out what I am doing wrong. This is for a fantasy football league. You choose your team name from a drop down list and hit submit. It is then supposed to produce a list of your choosen teams performance against all other active teams AND the results of the last game played. QUERY: <?php //the connection information is located in a function include_once('call function'); $con = mysql_connect($hostname, $username, $password) OR DIE ('Unable to connect to database! Please try again later.'); $db = mysql_select_db($dbname, $con); $thing = $_GET['thing']; $query = "select selected.teamname AS selected_team, selected_score.score AS selected_score, week.week, year, home_id, ". "target_score.score as target_score, target.teamname as targetname, week.ID ". "from owners as selected ". "JOIN game_scores AS selected_score ON selected.owner_id = selected_score.team_id ". "JOIN game_setup ON game_setup.game_id = selected_score.game_id ". "JOIN game_scores AS target_score ON target_score.game_id = game_setup.game_id AND target_score.team_id != ". "selected_score.team_id ". "JOIN owners AS target ON target.owner_id = target_score.team_id ". "JOIN week ON week.week = game_setup.week ". "WHERE selected.owner_id = $thing ". "and target.active = 1 ". "GROUP BY target.teamname, year, week.ID "; $result = mysql_query($query); $row = mysql_fetch_array($result); if (!$result) { die('Invalid query: ' . mysql_error()); } Now I know the query produces the correct results as I have run it many times in my database software and it always gives me all the rows. QUERY RESULTS WITH LIMIT TOP 6: selected_team|selected_score|week|year|home_id|target_score|targetname|ID Xanadu Dragons|397.4|Week 9|2010|12|394.7|America Enforcers|9 Xanadu Dragons|357.4|Round 1|2010|1|361.6|America Enforcers|22 Xanadu Dragons|416.6|Week 2|2011|12|369.8|America Enforcers|2 Xanadu Dragons|417.0|Week 5|2010|11|301.9|Battle Masters|5 Xanadu Dragons|360.8|Week 4|2011|11|459.1|Battle Masters|4 Xanadu Dragons|99.0|Week 1|2006|1|93.0|Camelot Fluffy Butts|1 So the problem must be when I put the query results into the php code below: $wins=0; $losses=0; $draws=0; $last_target = false; echo '<h3>' . $row['selected_team'] . '</h3>'; while ($row = mysql_fetch_assoc($result)) { if ($last_target['targetname'] != $row['targetname']) { if ($last_target) { printf(' <table class="style1" border="1" width="400"> <tr> <th>Opponent Name:</th> <th colspan="2">%s</th> </tr> <tr> <th>Wins</th> <th>Losses</th> <th>Draws</th> </tr> <tr> <td align="center">%d</td> <td align="center">%d</td> <td align="center">%d</td> </tr> <tr> <th>Last Game:</th> <td colspan="2">%s of %s: %.1f - %.1f</td> <tr> </table><br/>', $last_target['targetname'], $wins, $losses, $draws, $last_target['week'], $last_target['year'], $last_target['selected_score'], $last_target['target_score'] ); } $wins = $losses = $draws = 0; } if ($row['selected_score'] < $row['target_score']) ++$losses; elseif ($row['selected_score'] == $row['target_score']) ++$draws; else ++$wins; $last_target = $row; } if ($last_target) { } ?> The display however gives me the following: Opponent Name: America Enforcers Wins Losses Draws 1 1 0 Last Game: Week 2 of 2011: 416.6 - 369.8 as you can see from the above database results this team played the America Enforcers 3 times, however the php code only displays 2 games. All other match ups are correct, it is just this first row. Any ideas? Please and thank you for your time and effort.

Thank you very much. It is exactly what I needed.

This might be completely off, but have you tried $_POST instead of $_GET? $_POST is more secure and does not display data like $_GET does. Seriously though let a smart person verify this.

Greetings, I need a bit of php coding help. I have a query that gives me results. What I need to do is take those results and, I think, use php to compare the results and add a 1 to the correct section. Example: 3 tables table name- retail_sales id sales_id amount week table name- online_sales id sales_id amount week table name- sales_person sales_id name I have the query that breaks down results by week: week 1 | retail_sales.amount | online_sales.amount What I need is some php code that will then compare the two together and: if retail_sales.amount > online_sales.amount +1 to retail OR if retail_sales.amount < online_sales.amount +1 to online so you would end up with a result like a sports record (6-2) Any ideas Thank you for any help

I would be very interested in whatever that will point me in the right direction.