Databases
StackOverflow2013
Postsdbo
POPHP loop using foreach
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP loop using foreach
    primarykey
    data
    text
    <p>Here is the problem:-</p> <p>I've got a table of results from sports matches called 'MatchResults' with the following fields</p> <p>Player1 Player2 Player1GamesWon Player2GamesWon Player1Pts Player2Pts league_id</p> <p>First I am running a query which will filter this table using a stored variable I already have, giving me a shorter collection of rows:-</p> <pre><code>$qry = "SELECT * FROM BadmintonMatchResults WHERE league_id = '$leagueid'"; </code></pre> <p>I need to run through each row that is produced from the above query and use the values to update another table called 'LeagueTable'. The structure of LeagueTable is as follows:-</p> <p>member_id Played Won Lost GamesWon GamesLost Difference Points</p> <p>These updates take the form of several SQL queries, for example:-</p> <pre><code>$qry = mysql_query("UPDATE LeagueTable SET Played = Played + 1 WHERE member_id = '$p1id'"); $qry = mysql_query("UPDATE LeagueTable SET Played = Played + 1 WHERE member_id = '$p2id'"); if($p1pts &gt; $p2pts) { $qry = mysql_query("UPDATE LeagueTable SET Won = Won + 1 WHERE member_id = '$p1id'"); $qry = mysql_query("UPDATE LeagueTable SET Lost = Lost + 1 WHERE member_id = '$p2id'"); } else if($p2pts &gt; $p1pts) { $qry = mysql_query("UPDATE LeagueTable SET Won = Won + 1 WHERE member_id = '$p2id'"); $qry = mysql_query("UPDATE LeagueTable SET Lost = Lost + 1 WHERE member_id = '$p1id'"); } $qry = mysql_query("UPDATE LeagueTable SET GamesWon = GamesWon + '$p1won' WHERE member_id = '$p1id'"); $qry = mysql_query("UPDATE LeagueTable SET GamesWon = GamesWon + '$p2won' WHERE member_id = '$p2id'"); $qry = mysql_query("UPDATE LeagueTable SET GamesLost = GamesLost + '$p1won' WHERE member_id = '$p2id'"); $qry = mysql_query("UPDATE LeagueTable SET GamesLost = GamesLost + '$p2won' WHERE member_id = '$p1id'"); $qry = mysql_query("UPDATE LeagueTable SET Difference = GamesWon - GamesLost"); $qry = mysql_query("UPDATE LeagueTable SET Points = Points + '$p1pts' WHERE member_id = '$p1id'"); $qry = mysql_query("UPDATE LeagueTable SET Points = Points + '$p2pts' WHERE member_id = '$p2id'"); </code></pre> <p>Ignore any $variables I have as I'm currently doing this off data entered in a form, but want to alter it so that it uses rows from another database instead. I also know that my queries can be condensed and combined, my main problem is looping round this collection of queries multiple times for different sets of values.</p> <p>My questions are:-</p> <ol> <li>How to I reference the fields from the first table in the queries I need to run? Obviously just listing the field name 'Player1' isn't going to work.</li> <li>How do I loop through each row and run the above 2 queries along with 8 more similar ones.</li> </ol> <p>Note: Some of these queries are already nested inside an IF statement so any loop would have to go outside of that.</p> <p>Any help would be much appreciated!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USChris
    1. USChris
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhy not simplify your 2 queries into 1 query by using `IN` (or `OR`) rather than `=` in your `where` clause
      singulars
      1. POPHP loop using foreach
      1. USMark Baker
    2. COThere's no need to loop here, you can use joined queries. Also, avoid using the `mysql_*` family of functions. They're deprecated now.
      singulars
      1. POPHP loop using foreach
      1. USBenM
    3. COThanks for the idea about combining the queries. I can combine these two however the other 8 deal with different variables, so I'll look to see which of those I can combine as well. The reason I thought I needed to loop is because my first query on the MatchResults tables produces over 30 lines, and my 10 queries that update the LeagueTable table need to be run on each of the 30 returned rows from the MatchResults table
      singulars
      1. POPHP loop using foreach
      1. USChris
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload