14 Replies - 784 Views - Last Post: 15 August 2009 - 03:47 PM

#1 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

need some help with a sql query (resolved )

Posted 13 August 2009 - 03:52 PM

i am having a little bit of trouble with this one
as it is the code i am going to pup up is all working (( but i want a line to select just the best time from each track distance ground ))

heres the code

<?php require_once('../Connections/stablemaster.php'); ?>
<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 

{

  if (PHP_VERSION < 6) {

	$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }



  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);



  switch ($theType) {

	case "text":

	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

	  break;	

	case "long":

	case "int":

	  $theValue = ($theValue != "") ? intval($theValue) : "NULL";

	  break;

	case "double":

	  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

	  break;

	case "date":

	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

	  break;

	case "defined":

	  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

	  break;

  }
  return $theValue;
}

}
$currentPage = $_SERVER["PHP_SELF"];



$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
	if (stristr($param, "pageNum_Recordset1") == false && 
		stristr($param, "totalRows_Recordset1") == false) {
	  array_push($newParams, $param);
	}
  }
  if (count($newParams) != 0) {
	$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);

?>
<?php
$maxRows_Recordset1 = 30;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = "SELECT RaceResults.Track_name, RaceResults.Race_date, RaceResults.Ground_cond, RaceResults.Race_length, RaceResults.userid, RaceResults.horseid, RaceResults.finish FROM RaceResults ORDER BY RaceResults.Track_name, RaceResults.Race_length, RaceResults.finish DESC ";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $stablemaster) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
	if (stristr($param, "pageNum_Recordset1") == false && 
		stristr($param, "totalRows_Recordset1") == false) {
	  array_push($newParams, $param);
	}
  }
  if (count($newParams) != 0) {
	$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Best Jocks</title>
<script src="../Extras/sorttable.js"></script>
<style type="text/css" media="screen">
	  .clsHeads {
	background: transparent;
	width: 100%;
	border-style: none;
	cursor: pointer;
	background-color: #3F0;
	font-weight: bold;
	font-size: 12px;
}

	</style>

<style type="text/css">

<!--

body 
{
	font-size: 16px;
	background-image: url(../Images/grass-natural.jpg);
	border-color: #000000;
}
.links {
	font-size: 18px;
}
.links {
	color: #0CF;
}
.links td table {
	color: #00F;
}

-->

</style>
<link href="../Style Sheets/opacy.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.style9 {border-style: none; cursor: pointer; background-color: #3F0; background: transparent; width: 100%;}
-->
</style>
<style type="text/css">
<!--

.style1 {font-size: 16px}
a:link {
	text-decoration: none;
}
a:visited {
	text-decoration: none;
}
a:hover {
	text-decoration: underline;
	color: #00F;
}
a:active {
	text-decoration: none;
}
body,td,th {
	font-family: Comic Sans MS, cursive;

	font-weight: bold;

}
-->
</style>
</head>

<body text="#000000" link="#0066FF" vlink="#FF0000" alink="#FFFFFF">
<td><div align="center">
  <table width="100%" border="0" align="center" cellpadding="2" cellspacing="3" class="sortable">
		<tr>
	<td width="193" bgcolor="#00FF00"><div align="center"><input type=button class=style9 " value="Track" /></div></td>
	<td width="193" bgcolor="#00FF00"><div align="center"><input type=button class=style9 " value="Date" /></div></td>
	<td width="193" bgcolor="#00FF00"><div align="center"><input type=button class=style9 " value="Ground" /></div></td>
	<td width="193" bgcolor="#00FF00"><div align="center"><input type=button class=style9 " value="Distance" /></div></td>
	<td width="193" bgcolor="#00FF00"><div align="center"><input type=button class=style9 " value="Stable" /></div></td>
	<td width="193" bgcolor="#00FF00"><div align="center"><input type=button class=style9 " value="Horse" /></div></td>
	<td width="193" bgcolor="#00FF00"><div align="center"><input type=button class=style9 " value="Time" /></div></td>
   </tr>
 <?php do { 
$horsequery = "SELECT name_id FROM horse_stats WHERE `Key id`='".$row_Recordset1['horseid']."'";
$horse1 = mysql_query($horsequery, $stablemaster) or die(mysql_error());
$horse = mysql_fetch_assoc($horse1);

$stablequery = "SELECT stable_name FROM users WHERE `id`='".$row_Recordset1['userid']."'";
$stable1 = mysql_query($stablequery, $stablemaster) or die(mysql_error());
$stable = mysql_fetch_assoc($stable1);
  
?>
		  <tr>
	<td nowrap="nowrap" class="setOpacity style1"><?php echo $row_Recordset1['Track_name']; ?></td>
   <td nowrap="nowrap" class="setOpacity style1"><?php echo $row_Recordset1['Race_date']; ?></td>
	<td nowrap="nowrap" class="setOpacity style1"><?php echo $row_Recordset1['Ground_cond']; ?></td>
	<td nowrap="nowrap" class="setOpacity style1"><?php echo $row_Recordset1['Race_length']; ?></td>
	<td nowrap="nowrap" class="setOpacity style1"><?php echo $stable['stable_name']; ?></td>
	<td nowrap="nowrap" class="setOpacity"><a href="../Horse Finance/Horse_history.php?horse=<?php echo $horse['name_id']; ?>" class="style1"><?php echo $horse['name_id']; ?></a></td>
	<td nowrap="nowrap" class="setOpacity style1"><?php echo $row_Recordset1['finish']; ?></td>
  </tr>
  <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table><table width="56%" border="0" align="center" cellpadding="5" cellspacing="5">
		<tr>
			   
		  <td align=center bgcolor="#FFFFFF"><strong><a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, 0, $queryString_Recordset1); ?>">First</a></strong></td>
	<td align=center bgcolor="#FFFFFF"><strong><a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>">Previous</a></strong></td>
	<td align=center bgcolor="#FFFFFF"><strong><a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, min($totalPages_Recordset1, $pageNum_Recordset1 + 1), $queryString_Recordset1); ?>">Next</a></strong></td>
	<td width="16%" align=center bgcolor="#FFFFFF"><strong><a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, $totalPages_Recordset1, $queryString_Recordset1); ?>">Last</a></strong></td>
  </tr>
	</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>




Bath 2009-07-21 16:30:00 hard 6f ADMIN Summer Lodge 97.262 
Bath 2009-07-21 16:30:00 hard 6f ADMIN Royal Defence 97.174 
Cartmel 2009-07-21 14:30:00 firm 2.4m walkers Its Perfect 321.46 
Cartmel 2009-07-21 14:30:00 firm 2.4m walkers full nine 319.21 
Cartmel 2009-07-21 14:30:00 firm 2.4m walkers Im White As A Ghost 317.75 
Cartmel 2009-07-23 06:45:00 soft 2f ADMIN Golden Groom 29.43 
Cartmel 2009-07-23 06:45:00 soft 2f ADMIN Summer Lodge 28.568 
Cartmel 2009-07-27 03:45:00 heavy 3.1m ADMIN Gulloavn 384.622 
Cartmel 2009-08-05 12:15:00 firm 3.3m ADMIN Summer Lodge 404.298 
Cartmel 2009-08-05 12:15:00 firm 3.3m ADMIN Sporting Gesture 396.765 
Catterick 2009-07-24 02:45:00 dirt 1.4m Special Edition Personal Ensign 182.364 
Catterick 2009-07-24 02:45:00 dirt 1.4m ADMIN  145.104 
Catterick 2009-07-24 02:45:00 dirt 1.4m ADMIN  145.104 
Catterick 2009-07-24 05:00:00 heavy 1.5m Special Edition Genuine Risk 201.966 


as u can see from the output it selects all the results i just need one per track distance ground

This post has been edited by peter yianni: 14 August 2009 - 02:12 PM


Is This A Good Question/Topic? 0
  • +

Replies To: need some help with a sql query (resolved )

#2 smacdav  Icon User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: need some help with a sql query (resolved )

Posted 14 August 2009 - 08:41 AM

As I understand the question, you just need to know what query to run to get the information you want.

Looking at your queries to get field and table names, I'm thinking something like this ought to get what you want:

SELECT Track_name, Ground_cond, Race_length, MIN(finish) as Best_time
FROM RaceResults
GROUP BY Track_name, Ground_cond, Race_length



The columns returned by the query then would be Track_name, Ground_cond, Race_length, and Best_time.
Was This Post Helpful? 1
  • +
  • -

#3 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

Re: need some help with a sql query (resolved )

Posted 14 August 2009 - 01:49 PM

View Postsmacdav, on 14 Aug, 2009 - 07:41 AM, said:

As I understand the question, you just need to know what query to run to get the information you want.

Looking at your queries to get field and table names, I'm thinking something like this ought to get what you want:

SELECT Track_name, Ground_cond, Race_length, MIN(finish) as Best_time
FROM RaceResults
GROUP BY Track_name, Ground_cond, Race_length



The columns returned by the query then would be Track_name, Ground_cond, Race_length, and Best_time.


ok have done that now not showing the time but its starting to look correct if i get the times in i think its going to be ok

$query_Recordset1 = mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = "SELECT Track_name,Race_date,Ground_cond,Race_length,userid,horseid, MIN(finish) as Best_time FROM RaceResults GROUP BY  Track_name,Ground_cond,Race_length DESC ";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $stablemaster) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);


Was This Post Helpful? 0
  • +
  • -

#4 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

Re: need some help with a sql query (resolved )

Posted 14 August 2009 - 02:01 PM


mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = "SELECT Track_name,Race_date,Ground_cond,Race_length,userid,horseid,finish FROM RaceResults GROUP BY  Track_name,Ground_cond,Race_length DESC ";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $stablemaster) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);




i have fixed it thanks for the help it seems to be ok like this i would do some more testilng thats again
Was This Post Helpful? 0
  • +
  • -

#5 smacdav  Icon User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: need some help with a sql query (resolved )

Posted 14 August 2009 - 02:21 PM

I'm glad to help. :)
Was This Post Helpful? 0
  • +
  • -

#6 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

Re: need some help with a sql query (resolved )

Posted 14 August 2009 - 06:05 PM

i have noticed somthing odd lol
all the rows seem to be corect but
its puting the best time with the wrong horse
its not getting the horse that got the best time strange lol anyway heres the code
mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = "SELECT Track_name,Race_date,Ground_cond,Race_length,userid,horseid,MIN(finish) as Best_time
 FROM RaceResults GROUP BY  Track_name,Race_length,Ground_cond DESC ";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $stablemaster) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);


This post has been edited by peter yianni: 14 August 2009 - 10:10 PM

Was This Post Helpful? 0
  • +
  • -

#7 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 11:37 AM

View Postpeter yianni, on 14 Aug, 2009 - 05:05 PM, said:

i have noticed somthing odd lol
all the rows seem to be corect but
its puting the best time with the wrong horse
its not getting the horse that got the best time strange lol anyway heres the code
mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = mysql_select_db($database_stablemaster, $stablemaster);
$query_Recordset1 = "SELECT Track_name,Race_date,Ground_cond,Race_length,userid,horseid,MIN(finish) as Best_time
 FROM RaceResults GROUP BY  Track_name,Race_length,Ground_cond DESC ";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $stablemaster) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);



not resolved any more sudgestions
Was This Post Helpful? 0
  • +
  • -

#8 smacdav  Icon User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 02:08 PM

The query I gave you was only intended to find the best time on a given track at a given distance in given conditions. I didn't realize you also wanted to match the horse. That makes it a little more complicated, but we can swing it. I confess I'm not a SQL expert, but I think the following will give you what you want now.

SELECT Track_name,Race_date,Ground_cond,Race_length,userid,horseid,finish
FROM RaceResults 
WHERE finish=(SELECT MIN(finish) FROM RaceResults R WHERE R.Track_name=Track_name AND R.Ground_cond=Ground_cond AND R.Race_length=Race_length
GROUP BY  Track_name,Race_length,Ground_cond DESC)



Note that the selection of MIN(finish) and grouping are done in the subquery now. Also, MIN(finish) must be the only thing selected in the subquery.

I'm not sure where the userid fits into the whole thing, but since you're more familiar with your schema, hopefully you can tweak it if necessary to get that to work right.
Was This Post Helpful? 0
  • +
  • -

#9 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 02:36 PM

View Postsmacdav, on 15 Aug, 2009 - 01:08 PM, said:

The query I gave you was only intended to find the best time on a given track at a given distance in given conditions. I didn't realize you also wanted to match the horse. That makes it a little more complicated, but we can swing it. I confess I'm not a SQL expert, but I think the following will give you what you want now.

SELECT Track_name,Race_date,Ground_cond,Race_length,userid,horseid,finish
FROM RaceResults 
WHERE finish=(SELECT MIN(finish) FROM RaceResults R WHERE R.Track_name=Track_name AND R.Ground_cond=Ground_cond AND R.Race_length=Race_length
GROUP BY  Track_name,Race_length,Ground_cond DESC)



Note that the selection of MIN(finish) and grouping are done in the subquery now. Also, MIN(finish) must be the only thing selected in the subquery.

I'm not sure where the userid fits into the whole thing, but since you're more familiar with your schema, hopefully you can tweak it if necessary to get that to work right.


just kept bringing a error about subquery to many rows

is there anyway or having a where the horse = time
Was This Post Helpful? 0
  • +
  • -

#10 smacdav  Icon User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 02:50 PM

I said I wasn't an expert. :P

That error makes sense: we want the subquery to have only one row. Try moving the "GROUP BY" clause out of the subquery by moving the parenthesis to just before "GROUP BY". I don't know what I was thinking putting it in the subquery.
Was This Post Helpful? 0
  • +
  • -

#11 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 03:02 PM

View Postsmacdav, on 15 Aug, 2009 - 01:50 PM, said:

I said I wasn't an expert. :P

That error makes sense: we want the subquery to have only one row. Try moving the "GROUP BY" clause out of the subquery by moving the parenthesis to just before "GROUP BY". I don't know what I was thinking putting it in the subquery.



what if i add a limit to this at the moment its showing all the results if we put a limit it should just pick the best time and group the rest

SELECT Track_name,Race_date,Ground_cond,Race_length,userid,horseid,MIN(finish) as Best_time
 FROM RaceResults GROUP BY  Track_name,Race_length,Ground_cond,userid,horseid DESC


Was This Post Helpful? 0
  • +
  • -

#12 smacdav  Icon User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 03:11 PM

That still has the same problem. There's nothing matching MIN(finish) with the horse that got that time. The purpose of my subquery approach is to match the minimum time with the horse.

You could do it in two steps: perform the subquery on its own, extract the result, and put that value in place of the subquery in the main query.
Was This Post Helpful? 0
  • +
  • -

#13 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 03:19 PM

View Postsmacdav, on 15 Aug, 2009 - 02:11 PM, said:

That still has the same problem. There's nothing matching MIN(finish) with the horse that got that time. The purpose of my subquery approach is to match the minimum time with the horse.

You could do it in two steps: perform the subquery on its own, extract the result, and put that value in place of the subquery in the main query.


ok i will try this again later give my head a rest
can u look at the other post i put the one with the funds coming up with a e+7 where it should be numbers there
Was This Post Helpful? 0
  • +
  • -

#14 smacdav  Icon User is offline

  • D.I.C Head

Reputation: 56
  • View blog
  • Posts: 177
  • Joined: 06-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 03:22 PM

I'm actually just heading off line for the evening, but I'll be happy to look at it later if you can provide me a link to the other topic (or at least tell me the subject and which forum to look in).
Was This Post Helpful? 0
  • +
  • -

#15 peter yianni  Icon User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 310
  • Joined: 04-June 09

Re: need some help with a sql query (resolved )

Posted 15 August 2009 - 03:47 PM

View Postsmacdav, on 15 Aug, 2009 - 02:22 PM, said:

I'm actually just heading off line for the evening, but I'll be happy to look at it later if you can provide me a link to the other topic (or at least tell me the subject and which forum to look in).


its the post just below this one

4.94488e+07 showing up wrong
mysql 1 peter yianni 23 Today, 10:43 AM
by: peter yianni
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1