Page 1 of 1

select only certain data

Posted: Mon Aug 30, 2010 8:44 am
by driacg06
I have written some php code for a local theatre's iphone app, and i have a question. I have the php code generate an xml file so the iphone can grab the data from their web server, but I want to filter out any dates that have already past. I've tried using a WHILE statement in the SELECT function, but I couldn't get it to work. Here's my code for selecting the database and the generation portion of the xml statements:

Code: Select all

$query = "SELECT showname, Details, ShowDate, ShowTime, URLText FROM $tb_name WHERE ShowDate >= GETDATE() ORDER BY showname";
$result = @mysql_query($query, $connection);

//Create XML Document
$dom = new DOMDocument("1.0");
$dom->formatOutput = true;

//Create root node
$root = $dom->createElement("list");
$dom->appendChild($root);

while($row = @mysql_fetch_assoc($result))
{
	$occ = $dom->createElement($tb_name);
	$root->appendChild($occ);
	foreach ($row as $fieldname => $fieldvalue)
	{
		$child = $dom->createElement($fieldname);
		$occ->appendChild($child);

		$value = $dom->createTextNode($fieldvalue);
		$child->appendChild($value);		
		
	}
}

echo $dom->saveXML();
$dom->save("xml.xml");


The xml file is being generated correctly, but its showing all the records in the table which is not what I want, especially with it being over 300 records.

Thanks for taking time to give me advice. :)

Re: select only certain data

Posted: Mon Aug 30, 2010 9:02 am
by Gargoyle
there is no getdate() function in mysql. use now().

I can only suspect that mysql is treating getdate() as 0000-00-00 (mysql has a very error tolerant default setting) which is why you're getting all rows returned.