Sorting

[Kayls]

Can anyone help me with sorting this query by exhibits.exhibit_id. When I added it to the main $query it doesn't allow the following if conditions to work. I couldn't put it on the end of the if condition 'AND' line as it only sorted the data from the condition.

<?php

// Check that the form has been submitted
if ($_POST) {

	//Store the form data in variables
	$form_library_id = $_POST['library_id'];
	$form_earliest_date = $_POST['earliest_date'];
	$form_latest_date = $_POST['latest_date'];

	if (array_key_exists('etype', $_POST)) {
		$form_exhibit_type = $_POST['etype'];
	}
	else {
		$form_exhibit_type = "";
	}

	//Trim any trailing and leadin spaces from the form data
	$form_library_id = trim($form_library_id);
	$form_exhibit_type = trim($form_exhibit_type);
	$form_earliest_date = trim($form_earliest_date);
	$form_latest_date = trim($form_latest_date);

	//Convert earliest and latest dates to integers
	$form_earliest_date = intval($form_earliest_date);
	$form_latest_date = intval($form_latest_date);

	//Open a connect to the database
	$link = mysqli_connect('localhost', 'student', 'mmst12009', 'assignment3');

	//Define a query
	$query = "SELECT libraries.library_name, exhibits.exhibit_date, exhibits.exhibit_type, exhibits.exhibit_description, exhibits.exhibit_id
	FROM libraries, exhibits WHERE (libraries.library_id = exhibits.library_id) ORDER BY exhibits.exhibit_id";


	//Restrict the SQL query with an AND clause if the library ID is not 'any'
	if ($form_library_id != 'any') {
	 $query .= " AND (libraries.library_id = $form_library_id)";
	}

	//Restrict the SQL query with an AND clause if the library ID is not 'any'
	if ($form_exhibit_type != "") {
	 $query .= " AND (exhibits.exhibit_type = '$form_exhibit_type')";
	}

	//Restrict the SQL query with an AND clause if the library ID is not 'any'
	if ($form_earliest_date != 0 && $form_latest_date != 0) {
	 $query .= " AND (exhibits.exhibit_date BETWEEN $form_earliest_date AND $form_latest_date)";
	}

	//Run the query and store the link
	$result = mysqli_query($link, $query);

	//Close the connection to the database
	mysqli_close($link);

	//Display the table headings
	echo <<<END
	<p>Search results are presented below</p>
	<table border="0">
		<tr>
			<th>ID Number</th>
			<th>Library</th>
			<th>Date</th>
			<th>Type</th>
			<th>Description</th>
		</tr>

END;

	//Assign each record in the result to an array
	While ($row = mysqli_fetch_array($result)) {
		//Assign each array element to a variable
		$exhibit_id = $row['exhibit_id'];
		$library_name = $row['library_name'];
		$exhibit_date = $row['exhibit_date'];
		$exhibit_type = $row['exhibit_type'];
		$exhibit_description = $row['exhibit_description'];


		//Display a table row to each record
		echo <<<END
			<tr>
				<td>$exhibit_id</td>
				<td>$library_name</td>
				<td>$exhibit_date</td>
				<td>$exhibit_type</td>
				<td>$exhibit_description</td>
			</tr>

END;
}
echo "</table>";
}
?>

[Lars_Berg]

The order by should be the last clause in the query, so you need to add it after you have added the and conditions.

[Kayls]

Do you mean like this, as I still can't get it to work

//Define a query
$query = "
SELECT
libraries.library_name,
exhibits.exhibit_date,
exhibits.exhibit_type,
exhibits.exhibit_description,
exhibits.exhibit_id
FROM libraries, exhibits
WHERE libraries.library_id = exhibits.library_id";

	//Restrict the SQL query with an AND clause if the library ID is not 'any'
	if ($form_library_id != 'any') {
	 $query .= " AND libraries.library_id = $form_library_id";
	}

	//Restrict the SQL query with an AND clause if the library ID is not 'any'
	if ($form_exhibit_type != "") {
	 $query .= " AND exhibits.exhibit_type = '$form_exhibit_type'";
	}

	//Restrict the SQL query with an AND clause if the library ID is not 'any'
	if ($form_earliest_date != 0) {
	 $query .= " AND exhibits.exhibit_date >= $form_earliest_date";
	}

	//Restrict the SQL query with an AND clause if the library ID is not 'any'
	if ($form_latest_date != 0) {
	 $query .= " AND exhibits.exhibit_date <= $form_latest_date";
	}

	//Restrict the SQL query to sort by exhibit_id
	if (exhibits.exhibit_id != 0) {
		$query .= " ORDER BY exhibits.exhibit.id";
	}

I have also tried to make the condition as
if (exhibits.exhibit_id) {
but get a parse and boolean error message.

K.

[Lars_Berg]

Yes, that was what I meant. What error do you get?

[Kayls]

It was one of those boolean error messages. I ended up taking the whole thing out and retyping and it worked, so I must have left a space somewhere or something like that. Sometimes it's hard to know what you've done. I'm having trouble with the number of rows now, so it seems like it never ending.... Thanks.

[leatherback]

Kayls: These things are best started off by making one working query, and then breaking them up in chunks. That way you know the query in itself works, and you worry about the building of it later..

[Kayls]

Thanks for the advice. I usually try to build my code up, but you still seem to get problems, but hoepfully I will learn more as I go along!

[laserlight]

Remember to mark this thread as resolved (if it is) using the thread tools.