Having some problems with a query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Apophis
Forum Newbie
Posts: 13
Joined: Fri Jul 28, 2006 7:56 am

Having some problems with a query

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

so apparently, $DOB is wrong. What does it contain?
Apophis
Forum Newbie
Posts: 13
Joined: Fri Jul 28, 2006 7:56 am

Post by Apophis »

DOB contains dates inthis case: 2000-01-01
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

That is a string. You're giving MySQL math. 2000-01-01 without quotes is 1998 to MySQL.
Apophis
Forum Newbie
Posts: 13
Joined: Fri Jul 28, 2006 7:56 am

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

Post 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
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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
Post Reply