Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Stemming from the data provided by the OP, I am assuming, that <code>$departures</code> has a 1:n relationship with <code>$userinfo</code>, <code>$userinfo</code> being the 1 containing the pilotid.</p> <p>So, in oder to find out how many departures that pilot had in total, there's two possible ways, one by using a subquery, which would mean something like this:</p> <pre><code>SELECT (SELECT COUNT(*) FROM `departures` WHERE `pilot_id` = ID) as total, * FROM pilots; </code></pre> <p>In this case, your total would be in the <code>total</code> column of your <code>$userinfo</code> query.</p> <p>The second attempt makes use of actual PHP. In this scenario, you do the counting yourself.</p> <p>First step: Getting the pilot information:</p> <pre><code>$userinfo = array(); while($row = fetch()) { $row-&gt;total = 0; $row-&gt;departures = array(); $userinfo[$row-&gt;pilotid] = $row; } </code></pre> <p>These lines will give you the pilot data keyed to their IDs in an array. Step two. Glueing the departures to the pilots.</p> <pre><code>while($row = fetch()) { if(isset($userinfo[$row-&gt;pilotid])) { $userinfo[$row-&gt;pilotid]-&gt;departures[] = $row; ++$userinfo[$row-&gt;pilotid]-&gt;total; } } </code></pre> <p>If this isn't what you're looking for, I will be needing more information from you, however like this you will be able to get the departures of the pilots either by making use of the <code>total</code> variable in the <code>$userinfo</code> object, or by simply calling <code>count</code> on the <code>departures</code> array.</p> <p>Another variant, which keeps the actual departures and the pilots apart would look like this:</p> <p>First step: Getting the pilot information:</p> <pre><code>$userinfo = array(); while($row = fetch()) { $row-&gt;total = 0; $userinfo[$row-&gt;pilotid] = $row; } </code></pre> <p>These lines will give you the pilot data keyed to their IDs in an array. Step two. Glueing the departures to the pilots.</p> <pre><code>$departures = array(); while($row = fetch()) { if(isset($userinfo[$row-&gt;pilotid])) { $departures[] = $row; ++$userinfo[$row-&gt;pilotid]-&gt;total; } } </code></pre> <p>I hope you will find these suggestions useful.</p> <p>Edit: After a few additional information from the OP, I suggest changing the query used to access the information in question.</p> <p>This is the original code by the OP</p> <pre><code>$dep_query = "SELECT COUNT(pilotid) as total, depicao, pilotid FROM phpvms_pireps GROUP BY depicao, pilotid ORDER BY total DESC LIMIT 5"; $fav_deps = DB::get_results($dep_query); foreach($fav_deps as $departure) { $dep_airport = OperationsData::getAirportinfo($departure-&gt;depicao); $pilotid = Auth::$userinfo-&gt;pilotid; ?&gt; &lt;tr class="awards_table1"&gt; &lt;td width="10%"&gt;&lt;?php echo $departure-&gt;depicao; ?&gt;&lt;/td&gt; &lt;td width="10%"&gt;&lt;img src="&lt;?php echo Countries::getCountryImage($dep_airport-&gt;country); ?&gt;" /&gt;&lt;/td&gt; &lt;td width="60%"&gt;&lt;?php echo $dep_airport-&gt;name; ?&gt;&lt;/td&gt; &lt;td width="20%"&gt;&lt;?php echo $pilotid-&gt;{$departures-&gt;total}; ?&gt;&lt;/td&gt; &lt;/tr&gt; &lt;?php } ?&gt; </code></pre> <p>First thing we'll change is the query used to get the departures. Why fetch all the information, if we actually only want the one of the pilot in question?</p> <pre><code>$pilotid = $userinfo-&gt;pilotid; //As per Chat discussion $dep_query = "SELECT COUNT(depicao) as total, depicao FROM phpvms_pireps WHERE pilotid = $pilotid GROUP BY depicao ORDER BY total DESC LIMIT 5"; </code></pre> <p>This query will return the Top 5 of the departures from the different airports, which have been run by the pilot in question. As for the rest:</p> <pre><code>$fav_deps = DB::get_results($dep_query); if(is_array($fav_deps)) { //For the general use foreach($fav_deps as $departure) { $dep_airport = OperationsData::getAirportinfo($departure-&gt;depicao); ?&gt; &lt;tr class="awards_table1"&gt; &lt;td width="10%"&gt;&lt;?php echo $departure-&gt;depicao; ?&gt;&lt;/td&gt; &lt;td width="10%"&gt;&lt;img src="&lt;?php echo Countries::getCountryImage($dep_airport-&gt;country); ?&gt;" /&gt;&lt;/td&gt; &lt;td width="60%"&gt;&lt;?php echo $dep_airport-&gt;name; ?&gt;&lt;/td&gt; &lt;td width="20%"&gt;&lt;?php echo $departure-&gt;total; ?&gt;&lt;/td&gt; //Here is the actually changed Layout code &lt;/tr&gt; &lt;?php } } else echo "This pilot didn't have any departures yet."; </code></pre> <p>?></p> <p>With these alterations, your code should output the desired result. It is completely untested though. However it should give you the right idea.</p>
    singulars
    1. This table or related slice is empty.
    1. POLinking two PHP objects together
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRefugnic Eternium
    1. USRefugnic Eternium
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POLinking two PHP objects together
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks for your reply. To fill you in a bit more, `$userinfo` is already setup in the structure of my CMS. Here's my full code: http://pastebin.com/eiQAwxEg. Let me give you an example. If I echo just `$pilotid`, it will give me 5. Because it is on the profile page of the pilot, and his `pilotid` is 5. Now, all I'm trying to achieve is get the total of departures from a given airport, but assigned to a specific `pilotid` which we have just told the code to find.
      singulars
      1. PO
      1. USzzwyb89
    2. CO@zzwyb89 Do you want the depatures of ALL pilots, or just the one that's currently accessing the CMS?
      singulars
      1. PO
      1. USRefugnic Eternium
    3. COThe one currently accessing CMS. The code is being sticked on a profile page, so it assigns the `pilotid` to pilot's profile. Can't explain this really well, but if you enter ID 4's profile, you'll get his departures if you catch what I'm saying.
      singulars
      1. PO
      1. USzzwyb89
 

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