Jump to content

SELECT clause problem


kurt.santo
 Share

Recommended Posts

Trying to make a select clause working, but do not spot my mistake. The clause is:$q = 'SELECT task_id, parent_id, task FROM tasks WHERE date_completed="0000-00-00 00:00:00" ORDER BY date_added ASC';The column date_completed is type DATETIME and I would have assumed that the entry for a record in this column would display "0000-00-00 00:00:00", but it displays "NULL" (both without the ""). Therefore, I asume I made the mistake in the database.Also, I forgot the add the extra "not null" to another column. Really struggle with right syntac for update query to just add that bit to the column.Kurt

Link to comment
Share on other sites

What does the rest of that PHP page look like, and do you really have a record / records where date_completed="0000-00-00 00:00:00"?

Link to comment
Share on other sites

What's wrong with doing this:$q = 'SELECT task_id, parent_id, task FROM tasks WHERE date_completed=NULL ORDER BY date_added ASC';
Tried this already, with same result: nothing displays (neither drop-down nor dispay list).The code for the page is in total (actually taken from a book of Larry Ullman I am using to get to grip with MySQL and PHP:<!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" xml:lang="en" lang="en"><head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <title>Add a Task</title></head><body><?php # Script 1.2 - add_task.php/* This page adds tasks to the tasks table. * The page both displays and handles the form. */error_reporting (E_ALL); // Connect to the database:$dbc = @mysqli_connect ('localhost', 'root', 'bowie13', 'test') OR die ('<p>Could not connect to the database!</p></body></html>'); // Check if the form has been submitted:if (isset($_POST['submitted']) && !empty($_POST['task'])) { // Sanctify the input... // The parent_id must be an integer: if (isset($_POST['parent_id'])) { $parent_id = (int) $_POST['parent_id']; } else { $parent_id = 0; } // Escape the task: // Assumes Magic Quotes are off! $task = mysqli_real_escape_string($dbc, $_POST['task']); // Add the task to the database. $q = "INSERT INTO tasks (parent_id, task, date_added) VALUES ($parent_id, '$task', NOW())"; $r = mysqli_query($dbc, $q); // Report on the results: if (mysqli_affected_rows($dbc) == 1) { echo '<p>The task has been added!</p>'; } else { echo '<p>The task could not be added!</p>'; }} // End of submission IF.// Display the form:echo '<form action="add_task.php" method="post"><fieldset><legend>Add a Task</legend><p>Task: <input name="task" type="text" size="60" maxlength="100" /></p><p>Parent Task: <select name="parent_id"><option value="0">None</option>';// Retrieve all the uncompleted tasks:$q = 'SELECT task_id, parent_id, task FROM tasks WHERE date_completed="0000-00-00 00:00:00" ORDER BY date_added ASC'; $r = mysqli_query($dbc, $q);// Also store the tasks in an array for use later:$tasks = array();while (list($task_id, $parent_id, $task) = mysqli_fetch_array($r, MYSQLI_NUM)) { // Add to the select menu: echo "<option value=\"$task_id\">$task</option>\n"; // Add to the array: $tasks[] = array('task_id' => $task_id, 'parent_id' => $parent_id, 'task' => $task);}echo '</select></p><input name="submitted" type="hidden" value="true" /><input name="submit" type="submit" value="Add This Task" /></fieldset></form>';// Sort the tasks by parent_id:function parent_sort ($x, $y) { return ($x['parent_id'] > $y['parent_id']);}usort ($tasks, 'parent_sort');// Display all the tasks:echo '<h3>Current To-Do List</h3><ul>';foreach ($tasks as $task) { echo "<li>{$task['task']}</li>\n";}
<!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" xml:lang="en" lang="en"><head>	<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />	<title>Add a Task</title></head><body><?php # Script 1.2 - add_task.php/*	This page adds tasks to the tasks table. *	The page both displays and handles the form. */error_reporting (E_ALL); // Connect to the database:$dbc = @mysqli_connect ('localhost', 'root', 'bowie13', 'test') OR die ('<p>Could not connect to the database!</p></body></html>'); // Check if the form has been submitted:if (isset($_POST['submitted']) && !empty($_POST['task'])) {	// Sanctify the input...		// The parent_id must be an integer:	if (isset($_POST['parent_id'])) {		$parent_id = (int) $_POST['parent_id'];	} else {		$parent_id = 0;	}		// Escape the task:	// Assumes Magic Quotes are off!	$task = mysqli_real_escape_string($dbc, $_POST['task']);		// Add the task to the database.	$q = "INSERT INTO tasks (parent_id, task, date_added) VALUES ($parent_id, '$task', NOW())"; 	$r = mysqli_query($dbc, $q);		// Report on the results:	if (mysqli_affected_rows($dbc) == 1) {		echo '<p>The task has been added!</p>';	} else {		echo '<p>The task could not be added!</p>';	}} // End of submission IF.// Display the form:echo '<form action="add_task.php" method="post"><fieldset><legend>Add a Task</legend><p>Task: <input name="task" type="text" size="60" maxlength="100" /></p><p>Parent Task: <select name="parent_id"><option value="0">None</option>';// Retrieve all the uncompleted tasks:$q = 'SELECT task_id, parent_id, task FROM tasks WHERE date_completed="0000-00-00 00:00:00" ORDER BY date_added ASC'; $r = mysqli_query($dbc, $q);// Also store the tasks in an array for use later:$tasks = array();while (list($task_id, $parent_id, $task) = mysqli_fetch_array($r, MYSQLI_NUM)) {	// Add to the select menu:	echo "<option value=\"$task_id\">$task</option>\n";		// Add to the array:	$tasks[] = array('task_id' => $task_id, 'parent_id' => $parent_id, 'task' => $task);}echo '</select></p><input name="submitted" type="hidden" value="true" /><input name="submit" type="submit" value="Add This Task" /></fieldset></form>';// Sort the tasks by parent_id:function parent_sort ($x, $y) {	return ($x['parent_id'] > $y['parent_id']);}usort ($tasks, 'parent_sort');// Display all the tasks:echo '<h3>Current To-Do List</h3><ul>';foreach ($tasks as $task) {	echo "<li>{$task['task']}</li>\n";}echo '</ul>';?></body></html>

echo '</ul>';?></body></html>Really do not understand why it does not work as justsomeguy also suggests. As I said it adds tasks, but you cannot select parent task from drop-down. All records show in date_completed column "NULL" (without ""). Am really confused here...KurtReason for edit: spelt author's name wrong

Link to comment
Share on other sites

If the select statement isn't returning any records then that means that no records match the WHERE clause. Run the SQL statement in phpMyAdmin or something to see what's going on, and check the table to make sure there is data that will match your SQL statement.

Link to comment
Share on other sites

If the select statement isn't returning any records then that means that no records match the WHERE clause. Run the SQL statement in phpMyAdmin or something to see what's going on, and check the table to make sure there is data that will match your SQL statement.
Will do as advised, might take a day or so. Finally installing phpMyAdmin and having trouble (other thread in this section)...Kurt
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...