Page 1 of 1

Having some problems with a query

Posted: Wed Aug 02, 2006 5:14 pm
by Apophis
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


hope my english is good enough:

These are only parts of the script as i still working at the script

url contains number pedigree?Id=7036&Gen=4

Code: Select all

$Id = (isset($_GET['Id'])) ? intval($_GET['Id']) : 0 ; 
$where = ($Id != 0) ? " WHERE Id=".$Id : " ";

Code: Select all

$Parentquery = 
		"SELECT	FatherId, MotherId, DOB FROM $table WHERE Id='".$_GET['Id']."'";

$result_Parentquery = mysql_query($Parentquery)or die(mysql_error());

if ($result_Parentquery) {
			$row = mysql_fetch_assoc($result_Parentquery);
			$FatherId = $row['FatherId'];
			$MotherId = $row['MotherId'];
			$DOB      = $row['DOB'];
}
		else {
			$Parentquery;
	}
This is the query that don't work (gives NO problem at screen and the output is NULL).

Code: Select all

$query_Littermates =
		"SELECT * FROM $table WHERE FatherId = $FatherId and MotherId = $MotherId and DOB = $DOB and Id !=$Id ORDER BY DogName ASC";
Result has to come here

Code: Select all

print    "<td colspan=\"2\" align=\"left\">";
			while($row = mysql_fetch_array($NameResultSetLittermates,MYSQL_BOTH)){
    $field=$row['DogName'];
	$id=$row['Id'];
    print "<a href=./pedigree.php?Id=$id"."&"."Gen=4>$field</a><br>";
}
mysql_free_result($NameResultSetLittermates);
print  "</tr>";
ps

If the query is like this it works fine but it is not what i want:

Code: Select all

$query_Littermates =
		"SELECT * FROM $table WHERE FatherId = $FatherId and MotherId = $MotherId and Id !=$Id ORDER BY DogName ASC";
Hope you understand


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Aug 02, 2006 6:03 pm
by feyd
so apparently, $DOB is wrong. What does it contain?

Posted: Wed Aug 02, 2006 6:12 pm
by Apophis
DOB contains dates inthis case: 2000-01-01

Posted: Wed Aug 02, 2006 6:16 pm
by feyd
That is a string. You're giving MySQL math. 2000-01-01 without quotes is 1998 to MySQL.

How can i run a query that select rows with the same date

Posted: Wed Aug 02, 2006 7:39 pm
by Apophis
Very simple question but i do not find a answer:

What for a query do I have to run to get all records with the same date. In this case 2000-01-01 (2000 january 01)

Only if i use >= or <= it will give me result but if the expression is = it will see 2000-01-01 as 1998


Need HELP for this thing

Posted: Wed Aug 02, 2006 7:59 pm
by bdlang
Apart from feyd's comment about using quotes to surround the date value, your SQL statements and code don't make alot of sense. What exactly are you trying to do?

Here is the logic of the statements you have, as best as I can tell:
  • Take a value, match to the `Id `column, and return the FatherId (presumably an INT type column), MotherId (also presumed to be an INT type column) an DOB (a date column)
  • Take the result of that (assuming the `Id` value matches exactly one record, and actually returns a result) then fashion a query for SELECT #2, that
  • Attempts to return all columns from a match on the FatherID, MotherID and DOB values that we just grabbed (maybe), where the `Id` column doesn't match, then
  • Forget to use a call to mysql_query() (unless you've simply left it out of the post) that assumes the variable $NameResultSetLittermates.
  • Start a while() loop and attempt to get TWO sets of arrays (associative and numerically indexed) from each row of a non-existant result.
Your query can likely be distilled into an efficient self join, why not try something l ike this, see if it gives you the result you expect.

Code: Select all

SELECT
 a.Id
, a.DogName
FROM table AS a
INNER JOIN table AS b
ON (
 (a.FatherId = b.FatherId)
 AND
 (a.MotherId = b.MotherId)
 AND
 (a.DOB = b.DOB)
)
WHERE a.Id != ID_VALUE_FROM_PHP_GET_STRING
If not, let us know your table schema and the exact result you intend to produce from the SQL.


Please take some time to understand SQL and specifically, MySQL statement syntax prior to adding it to your script; use the 'mysql' command line application or phpMyAdmin to work out your queries, make certain they return a value you expect. Once you put this working SQL in your PHP script, you can troubleshoot any errors based on the script, not the SQL.

MySQL Manual: Tutorial
MySQL Manual: SELECT syntax
MySQL Manual: JOIN syntax