Querying a query

[MargateSteve]

Is it possible to use a 'SELECT FROM' against a query in a new query?

 

For example (and I know this would be bad practice but it is just a simple example so I can understand!!!), if you had a  table called 'staff' with this sort of set up

 

ID | NAME | SEX | AGE | DEPARTMENT

 

and this query

$staffquery = SELECT * FROM staff

would it be possible to then use

$males = SELECT * FROM $staffquery WHERE sex = 'Male'
$females = SELECT * FROM $staffquery WHERE sex = 'Female'
$males_in_despatch = SELECT * FROM $staffquery WHERE sex = 'Male' AND department = 'Despatch'

or would I have to run the full query each time? The example I have given is not a good one as it only queries one table anyway but if it was querying several joined tables then it would obviously be a lot of repeated code.

 

I am trying to set up a page which does various counts and sums from a query that links several different tables and I am trying to work out the best way to set it all up before worrying about actually getting the data.

 

I can post the full query that I am actually using and some sample data if required.

 

Thanks in advance

Steve

[DavidAM]

You should be able to CREATE a VIEW in the database.  Then you can select from the VIEW as if it was a table.

 

Something like this

CREATE VIEW StaffList AS SELECT * FROM Staff

 

Then select from it

SELECT * FROM StaffList WHERE sex='Male'

[MargateSteve]

Cheers David. Yet again, another one of the clear and helpful answers that sets this forum aside from any of the others!!

 

I never knew about 'VIEWS' before but that will be immensely helpful in my attempts to improve my current code.

 

Thanks again

Steve

[MargateSteve]

Me again!

 

Had a go at this but could not get it to work. I have created a view

$squad = "CREATE VIEW StaffList AS SELECT * FROM player_season, players,  player_positions 
WHERE player_season.season = '104' 
    AND player_season.player = players.player_id 
    AND player_positions.player_position_id = players.position
ORDER BY player_positions.position_order, players.position, players.surname ASC ";

and then set up a second query that will just pull the rows that have data in the 'date_left' field

$squad2 = "SELECT * FROM StaffList WHERE date_left IS NOT NULL";

but the page comes up blank.

 

Presumably I have set this up wrong somewhere but as always have tried and failed to work it out! Any pointers would be gratefully received!

 

The full php code is

<?php
mysql_select_db($database_Test, $Test);
$squad = "CREATE VIEW StaffList AS SELECT * FROM player_season, players,  player_positions 
WHERE player_season.season = '104' 
    AND player_season.player = players.player_id 
    AND player_positions.player_position_id = players.position
ORDER BY player_positions.position_order, players.position, players.surname ASC ";
$squad2 = "SELECT * FROM StaffList";

$results = mysql_query($squad2); ?>

and the html is

<?php if($results)
{
    if(mysql_num_rows($results))
    {
        $last_player_postion_id = 0;

        while($row = mysql_fetch_assoc($results))
        {
            // when the players positon id changes output a new heading
            if($last_player_postion_id != $row['player_position_id'])
            {
                echo '<div class="Title_Lt_Blue_Bg">' . strtoupper($row['position']) . '</div>';
                
                $last_player_postion_id = $row['player_position_id'];
            }
            
            echo '<table width="590" border="0" align="center" cellspacing="0">
        
        <tr>
          <td colspan="2" class="opposition_name">' . $row_squad['surname'] . ', ' . $row['firstname'] . '</td><td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><img src="'.  $row['image'] .'"   width="120" height="120"  /></td>
        </tr>
        <tr>
          <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br />'; if ($row['date_of_birth'] == NULL)
echo  'TBA';
  else echo date('jS F Y',strtotime($row['date_of_birth']));
  echo   '<br />
            <strong>JOINED<br />
            </strong>'. date('F Y',strtotime($row['date_joined'])) .'</td>
          <td width="309" align="left" ><strong>HOME SPONSOR<br />
            </strong>'; 
if ($row['home_sponsor']<>"")
echo  $row['home_sponsor'];
  else	echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>'; echo' <br /><strong>AWAY SPONSOR<br />
            </strong>'; 
if ($row['away_sponsor']<>"")
echo  $row['away_sponsor'];
  else	echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>'; echo' <br /></td>
        </tr>
        <tr>
          <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID='. $row['player_id'] .'"> See '. $row['firstname'] .'\'s Profile</a>   </td>
        </tr>
       
      </table>';

        }
    }
    else
    {
        echo 'No results!';
    }
} ?>

 

Thanks in advance

Steve

[MargateSteve]

Sorry, left the "WHERE date_left IS NOT NULL" from '$squad2' on the full php code I posted but it is actually there.

 

Steve

[DavidAM]

A VIEW is an object in the database, just like a TABLE is.  You only have to create it once, so you don't want it in a script that will be executed repeatedly.

 

In the script you posted, you never executed the SQL you wrote to create the VIEW. So the VIEW does not exist, so the second query, which you did execute, will fail.

[MargateSteve]

Following a couple of days of reading up on VIEWS and executing queries I am still stumped!!!

 

I do understand that the query has to executed and as far as I can see (and please correct me if I am wrong) this can be done in two ways, either directly in the query with

$first_query = mysql_query("SELECT....)

or afterwards with

first_query =  "SELECT...."
$query_results = mysql_query ($first_query)

so have tried

$squad = mysql_query("CREATE VIEW StaffList AS SELECT * FROM player_season, players,  player_positions 
WHERE player_season.season = '104' 
    AND player_season.player = players.player_id 
    AND player_positions.player_position_id = players.position
ORDER BY player_positions.position_order, players.position, players.surname ASC LIMIT 0,$squad_limit");
$squad2 = "SELECT * FROM StaffList WHERE date_left IS NOT NULL";

$results = ($squad2);

to no avail.

I have also tried to change the last line to

$results = ($squad);

so it is pulling data directly from the view but that still brings up a blank screen.

 

If I change the VIEW to a direct query and pull $results from that directly everything works

$squad = mysql_query ("SELECT * FROM player_season, players,  player_positions 
WHERE player_season.season = '104' 
    AND player_season.player = players.player_id 
    AND player_positions.player_position_id = players.position
ORDER BY player_positions.position_order, players.position, players.surname ASC LIMIT 0,$squad_limit");
$squad2 = "SELECT * FROM StaffList";

$results = ($squad); 

but as soon as I try to use a VIEW it all falls over!!!

 

Could someone please point me in the direction of how to get this done in laymens terms? Instructions at places like php.net hurt my head!

 

Thanks for your patience.

Steve

[MargateSteve]

Aaaaaaah. After extremely exhaustive reading up to try to find why it will not work I eventually discovered that CREATE VIEW does not work with MySQL4, which is what the database is on.

I will export all data, delete the database and create a MySQL 5 one.

Steve

[DavidAM]

Sorry about that, Steve. It's funny that once we find something great we forget that it didn't exist before. Honestly, though, I did not realize VIEWs did not exist in version 4.  You can also use a query as a psuedo-table in a query (I think this requires mySql 5 as well).  For instance:

 

SELECT * 
FROM (SELECT * FROM player_season, players,  player_positions 
    WHERE player_season.season = '104' 
        AND player_season.player = players.player_id 
        AND player_positions.player_position_id = players.position
    ORDER BY player_positions.position_order, players.position, players.surname ASC ) AS stafflist
WHERE date_left IS NOT NULL

 

which might be better than creating a VIEW.  I suggested a VIEW in the first place, because your original question seemed to be talking about a static (non-changing) query.  But looking at the followup posts, it looks like your "VIEW" is going to change on every page load. Different page users hitting the page at the same time are going to have a problem trying to create a view that already exists; or dropping the VIEW that is being used by another user.

[MargateSteve]

Cheers David.

I will take a look at psuedo queries but to be perfectly honest I like the looks of the way CREATE VIEW works in regard to some of the other pages I have so am going to try to set up a MySQL5 database. The fact that the account is at it's limit of databases means I just have to make sure I carefully export everything before deleting the database and creating a new one.

 

As you suggest, a VIEW may not be the right thing for this page anyway. In all fairness there would only be two variations on the query

WHERE date_left IS NOT NULL

WHERE date_left IS NULL

so there is not an enormous amount of replication anyway. But as I said, I think views could be useful in other pages.

Thanks again

Steve