Two queries on a page?

7724

Morning,

I want to run two queries on one page:

<?php

// Start the connection to the database        

blah
// End the connection to the database  

// Include functions to get the various values from the dropdown boxes
include('**************); 

// ** If Submit is hit do your stuff **
    if (isset($_POST['Submit'])) {

  $team_one_id = $_POST['team_one_id'];
  $team_two_id = $_POST['team_two_id'];

// ** If all of the statements are true then **
    } else {	
    $result = mysql_query("Insert into results(team_one_id ,team_two_id) values('$team_one_id', '$team_two_id')");

$result2 = mysql_query("SELECT th.team_name AS home_team, ta.team_name As away_team
                            FROM results r 
                            INNER JOIN teams th ON r.team_one_id = th.team_id 
                            INNER JOIN teams ta ON r.team_two_id = ta.team_id");

$msg = "Thank you the fixture " . $row['home_team'] ." v's " . $row['away_team'] ." has been added to the database.  Please continue or <a href='../index.php'>Return to the index </a>";
} 
}
?>

The first query enters a numerical value in the database results table for team_one_id and team_two_id – fine.

But then I want it to look in the table teams for these values entered,

so if team_one_id value was 1 it would find the value for 1 and it matched the team Arsenal
and if team_two_id value was 2 it would find the value for 2 and it matched the team Wigan

So then I want to produce the $msg, after I hit submit but nothing appears? I know both my queries are right as I’ve tested them in mysql. Any ideas?

Thanks

Chris

alimadzi

In your second query, without a where clause, it's going to pull all the records from the 'results' table (which is probably not what you want). Change to this...

$result2 = mysql_query( "SELECT team_id, team_name FROM teams WHERE team_id IN ('$team_one_id', '$team_two_id')" );

while ( $row = mysql_fetch_assoc( $result2 ) ) {
  $teams[$row['team_id']] = $row['team_name'];
}

$msg = "Thank you the fixture " . $teams[$team_one_id] ." v's " . $teams[$team_two_id] ." has been added to the database.  Please continue or <a href='../index.php'>Return to the index </a>";

Probably not the most elegant solution, but it works. Looking forward to somebody improving on this...

bradgrafelman

I would probably tack on a "LIMIT 2" at the end of the query, but other than that... I think that solution should work fine (other than the fact that the entire script is vulnerable to SQL injection attacks).

alimadzi

bradgrafelman wrote:
I would probably tack on a "LIMIT 2" at the end of the query, but other than that... I think that solution should work fine (other than the fact that the entire script is vulnerable to SQL injection attacks).

Yes, good point. Add "LIMIT 2" to the query and escape your input to guard against SQL injection. I should have mentioned that in my response. Thanks for pointing it out.

7724

Cheers Guys,

Thanks for the advice ;-)