query shortening?? help

[mattm1712]

is there a better way of doing this set off queries?

 

$homequery = mysql_query("SELECT * FROM footballodds WHERE gameid='$fixturesarray[id]' AND type='Match Betting' AND outcome='Home' ORDER BY price DESC LIMIT 0, 1");

$homeprice = mysql_fetch_assoc($homequery);

$companyhome = $homeprice['companyid'];

$companyhomequery = mysql_query("SELECT * FROM sitenames WHERE id='$companyhome'");

$homeid= mysql_fetch_assoc($companyhomequery);

$homenamev = $homeid['name'];

$drawquery = mysql_query("SELECT * FROM footballodds WHERE gameid='$fixturesarray[id]' AND type='Match Betting' AND outcome='Draw' ORDER BY price DESC LIMIT 0, 1");

$drawprice = mysql_fetch_assoc($drawquery);

$companydraw = $drawprice['companyid'];

$companydrawquery = mysql_query("SELECT * FROM sitenames WHERE id='$companydraw'");

$drawid= mysql_fetch_assoc($companydrawquery);

$drawnamev = $drawid['name'];

$awayquery = mysql_query("SELECT * FROM footballodds WHERE gameid='$fixturesarray[id]' AND type='Match Betting' AND outcome='Away' ORDER BY price DESC LIMIT 0, 1");

$awayprice = mysql_fetch_assoc($awayquery);

$companyaway = $awayprice['companyid'];

$companyawayquery = mysql_query("SELECT * FROM sitenames WHERE id='$companyaway'");

$awayid= mysql_fetch_assoc($companyawayquery);

$awaynamev = $awayid['name'];

 

it has to do this about 40 times everytime a page loads and it takes ages is there a way off cutting it down?

 

cheers matt

[mattm1712]

this is my full script

 

<?

include 'inc/connect.inc';

$getfixtures = mysql_query("SELECT * FROM footballfixtures");

$count = mysql_num_rows($getfixtures);

while($fixturesarray=mysql_fetch_assoc($getfixtures))

{

$match = $fixturesarray['id'];

$hometeam = $fixturesarray['home'];

$homeselect = mysql_query("SELECT * FROM footballteams WHERE id='$hometeam'");

$homeselect2 = mysql_fetch_assoc($homeselect);

$homename = $homeselect2['name'];

$awayteam = $fixturesarray['away'];

$awayselect = mysql_query("SELECT * FROM footballteams WHERE id='$awayteam'");

$awayselect2 = mysql_fetch_assoc($awayselect);

$awayname = $awayselect2['name'];

$datenew = $fixturesarray['date'];

$matchdaytime = $fixturesarray['date'];

$maindate = date("G:ia",$matchdaytime);

echo "<tr class='rowmouseover'><td width='160' class='greymatchbar'>".$maindate."</td><td width='300' class='greymatchbar'><a href='/test/Betting/".$var2."/".$var3."/".$var4."/".$fixturesarray['id']."/".$homename."-".$awayname."/Win-Draw-Win'>";

echo $homename."  v  ".$awayname."</a></td>";

$homequery = mysql_query("SELECT * FROM footballodds WHERE gameid='$fixturesarray[id]' AND type='Match Betting' AND outcome='Home' ORDER BY price DESC LIMIT 0, 1");

$homeprice = mysql_fetch_assoc($homequery);

$companyhome = $homeprice['companyid'];

$companyhomequery = mysql_query("SELECT * FROM sitenames WHERE id='$companyhome'");

$homeid= mysql_fetch_assoc($companyhomequery);

$homenamev = $homeid['name'];

$drawquery = mysql_query("SELECT * FROM footballodds WHERE gameid='$fixturesarray[id]' AND type='Match Betting' AND outcome='Draw' ORDER BY price DESC LIMIT 0, 1");

$drawprice = mysql_fetch_assoc($drawquery);

$companydraw = $drawprice['companyid'];

$companydrawquery = mysql_query("SELECT * FROM sitenames WHERE id='$companydraw'");

$drawid= mysql_fetch_assoc($companydrawquery);

$drawnamev = $drawid['name'];

$awayquery = mysql_query("SELECT * FROM footballodds WHERE gameid='$fixturesarray[id]' AND type='Match Betting' AND outcome='Away' ORDER BY price DESC LIMIT 0, 1");

$awayprice = mysql_fetch_assoc($awayquery);

$companyaway = $awayprice['companyid'];

$companyawayquery = mysql_query("SELECT * FROM sitenames WHERE id='$companyaway'");

$awayid= mysql_fetch_assoc($companyawayquery);

$awaynamev = $awayid['name'];

echo "</td>

<td width='60' class='greymatchbar2' align='center' title='".$homenamev."'>".$homeprice['price']."</td>

<td width='60' class='greymatchbar2' align='center' title='".$drawnamev."'>".$drawprice['price']."</td>

<td width='60' class='greymatchbar2' align='center' title='".$awaynamev ."'>".$awayprice['price']."</td>

<td width='80' class='greymatchbar'><a href='/test/Betting/".$var2."/".$var3."/".$var4."/".$fixturesarray['id']."/".$homename."-".$awayname."/Win-Draw-Win'>See All</a></td></tr>";

}

?>

 

 

[ZulfadlyAshBurn]

It looks messy. Do remember to place codes in the codes tag.

[PFMaBiSmAd]

In your footballodds table, are there only three rows for each gameid AND type='Match Betting'? I.e. one for outcome='home', one for outcome='draw', and one for outcome='away'? Edit: I visited your site (url in a previous thread) and found the answer - you have ~14 different rows for each.

 

Short-answer: By using joins, you can do all that in one query. The reason for the footballodds question is, if there are only the three rows for each gameid/type='Match Betting', the query is simpler, since you don't need to find the maximum price for each outcome.

[PFMaBiSmAd]

The following single query will replace all those queries -

 

$query = "
SELECT f.date, th.name home_name, ta.name away_name, o1.outcome, s.name site_name, o1.price FROM footballfixtures f
INNER JOIN footballteams th ON th.id = f.home
INNER JOIN footballteams ta ON ta.id = f.away
INNER JOIN footballodds o1 ON o1.gameid = f.id
INNER JOIN sitenames s ON s.id = o1.companyid
WHERE  price=(SELECT MAX(o2.price)
              FROM footballodds o2
              WHERE o1.outcome = o2.outcome)
		  AND o1.type='Match Betting'
";

[PFMaBiSmAd]

Here's code, based on the abbreviated code that you posted -

 

<?php

$query = "
SELECT f.id, f.date, th.name home_name, ta.name away_name, o1.outcome, s.name site_name, o1.price FROM footballfixtures f
INNER JOIN footballteams th ON th.id = f.home
INNER JOIN footballteams ta ON ta.id = f.away
INNER JOIN footballodds o1 ON o1.gameid = f.id
INNER JOIN sitenames s ON s.id = o1.companyid
WHERE  price=(SELECT MAX(o2.price)
	FROM footballodds o2
	WHERE o1.outcome = o2.outcome)
	AND o1.type='Match Betting'
	ORDER BY f.id, FIELD(o1.outcome,'Home','Draw','Away')
";
$result = mysql_query($query);

echo "<table>";
$last_fixture = null; // remember fixture (to detect when it changes)
while($row = mysql_fetch_assoc($result)){
if($last_fixture != $row['id']){
	// fixture changed (or is the first one)
	if($last_fixture != null){
		// not the first one, close out the previous fixture
		echo "<td width='80' class='greymatchbar'><a href='/test/Betting/".$var2."/".$var3."/".$var4."/".$row['id']."/".$homename."-".$awayname."/Win-Draw-Win'>See All</a></td></tr>";
	}
	// output the start of a new fixture
	$maindate = date("G:ia",$row['date']);
	$homename = $row['home_name'];
	$awayname = $row['away_name'];
	echo "<tr class='rowmouseover'><td width='160' class='greymatchbar'>".$maindate."</td><td width='300' class='greymatchbar'><a href='/test/Betting/".$var2."/".$var3."/".$var4."/".$row['id']."/".$homename."-".$awayname."/Win-Draw-Win'>";
	echo $homename."  v  ".$awayname."</a></td>";   

	$last_fixture = $row['id']; // remember the fixture
}
// output the data under each fixture (Home, Draw, Away)
echo "<td width='60' class='greymatchbar2' align='center' title='".$row['site_name']."'>".$row['price']."</td>";
}
// close out the last fixture (if any)
if($last_fixture != null){
echo "<td width='80' class='greymatchbar'><a href='/test/Betting/".$var2."/".$var3."/".$var4."/".$row['id']."/".$homename."-".$awayname."/Win-Draw-Win'>See All</a></td></tr>";
}
echo "</table>";
?>

 

The query statement in this code has two minor additions from the post above this one.

[PFMaBiSmAd]

P.S. The INNER JOIN footballodds o1 ON o1.gameid = f.id in the above should be a LEFT JOIN since you might not have data in the footballodds table for every fixture.

[PFMaBiSmAd]

Here's another correction to the query (that's what you get when you don't test with enough sample data).

 

The line - WHERE o1.outcome = o2.outcome)

 

Needs to be -

 

WHERE o1.outcome = o2.outcome AND o1.gameid = o2.gameid)

[PFMaBiSmAd]

Here's some 'final answer' code that works correctly for any amount of matching data in the footballodds table. This also correctly selects the newest 20 matches and displays them in ascending order -

 

<?php

$outcomes = array('Home','Draw','Away'); // list of outcomes and the order to display them in the html table

$query = "
SELECT * FROM (
SELECT f.id, f.date, th.name home_name, ta.name away_name,
GROUP_CONCAT(CONCAT_WS('|',o1.outcome,o1.price,s.name) SEPARATOR '||') as result
FROM footballfixtures f
INNER JOIN footballteams th ON th.id = f.home
INNER JOIN footballteams ta ON ta.id = f.away
LEFT JOIN footballodds o1 ON o1.gameid = f.id
LEFT JOIN sitenames s ON s.id = o1.companyid
WHERE  (o1.price=(SELECT MAX(o2.price)
	FROM footballodds o2
	WHERE o1.outcome = o2.outcome AND o1.gameid = o2.gameid)
	AND o1.type='Match Betting') or o1.price <=> null
GROUP BY o1.gameid
ORDER BY f.date DESC LIMIT 20
) t ORDER BY date ASC
";

$result = mysql_query($query) or die(mysql_error());
echo "<table>";
while($row = mysql_fetch_assoc($result)){
$id = $row['id'];
$maindate = date('Y-m-d g:ia',$row['date']); // full date/time
$homename = $row['home_name'];
$awayname = $row['away_name'];
echo "<tr class='rowmouseover'><td width='160' class='greymatchbar'>".$maindate."</td><td width='300' class='greymatchbar'><a href='/test/Betting/".$var2."/".$var3."/".$var4."/".$id."/".$homename."-".$awayname."/Win-Draw-Win'>";
echo $homename."  v  ".$awayname."</a></td>";

// extract data
$data = explode('||',$row['result']); // 0-3 entries of outcome|price|site i.e. "Home|2.34|site1||Away|1.23|site1||Draw|6.00|site2"
$data2 = array();
foreach($data as $str){
	if($str != ''){
		list($outcome,$price,$site) = explode('|',$str);
		$data2[$outcome] = array('price'=>$price,'site_name'=>$site);
	}
}

foreach($outcomes as $key){
	$site_name = 'none';
	$price = 'none';
	if(isset($data2[$key])){
		$site_name = $data2[$key]['site_name'];
		$price = $data2[$key]['price'];
	}
	echo "<td width='60' class='greymatchbar2' align='center' title='".$site_name."'>".$price."</td>";
}
echo "<td width='80' class='greymatchbar'><a href='/test/Betting/".$var2."/".$var3."/".$var4."/".$id."/".$homename."-".$awayname."/Win-Draw-Win'>See All</a></td></tr>\n";
}
echo "</table>"; 
?>