Jump to content
Can't remember your login details? Read more... ×
Sign in to follow this  
Invicta

PHP and mySQL help

Recommended Posts

I am currently working on a website for a Tafe assignment.

 

It's a basic website that stores recipes in a database, with an admin that logs in to add and update recipes.

 

Now I have a page that shows all of the recipes formatted in a table.

Shown here:

 

http://seven.bf.rmit.edu.au/~s3196397/Pear...owserecipes.php

 

<h4>Here are all the Recipe details from the database:</h4>
<!-- Table to display the results -->
<table>
<!-- Print the field names -->
<tr>
	<th>Recipe Name</th><th>Cuisine Name</th><th>Course Name</th>
</tr>
<?php
	//create the query
	$selectAll = makeSelectQuery('recipe_name, cuisine_name, course_name', 'recipes');

	//send query to the database and store the results in an array
	//the result array will be multi-dimensional: rows and columns
	$allRecipes = executeMultiSelect($selectAll);

	//print the contents of the array using for loops
	for($rows = 0; $rows < count($allRecipes); $rows++)
	{
		//create a variable for CSS formatting of odd and even rows
		//on the display table
		$rowClass = 'even';
		if($rows%2!=0)
		{
			$rowClass = 'odd';
		}

		echo '<tr class="'.$rowClass.'">';
		for($cols = 0; $cols < count($allRecipes[$rows]); $cols++)
		{
			echo '<td>'.$allRecipes[$rows][$cols].'</td>';
		}
		echo '</tr>';
	}
?>
</table>

Want I would like it to do is when one the headings is clicked to sort via that heading.

 

For example if the Course heading is clicked it will group them by Desserts then Mains etc.

 

I would also like to make the Recipe Name of each a link that will tkae the user to a serperate page displaying all of the data saved in the fields.

There are a few more fields, but only 4 are showing as it is a little impractical to show the method and ingredients in a recipe list. =P

 

Anyway, any help would be appreciated.

 

Cheers!

Share this post


Link to post
Share on other sites

Send what you want to sort by as a GET value. If there is no GET value, sort by ID by default (e.g. ID from the database).

 

When you get your data from the database, you should be able to ORDER BY so use that based on what you get from the GET.

 

So, what you'd need to do is this:

 

add a link to the top of each column that tells it how to sort when clicked like this:

<a href="browserecipes.php?sort=recipe">
Then, you'll need to write a little bit of PHP as a switch statement to change the query.

So you want to set the query:

$query = "SELECT * FROM myrecipes ORDER BY "

and then have a switch to transform the data recieved via get to a field name to sort by:

switch($GET['sort'])
{
  case "recipe":  $query .=  "recipe name";  break;
  etc
  etc
  default:  $query .= "id"; break;
}

and then finally, and whether to sort by ascending or descending.

 

$query .= " ASC";

This may or may not work (tired and haven't tested -any- of it) but should give you a general idea of what to do.

Share this post


Link to post
Share on other sites

So i've altered my code to:

 

<?php
	include('resources.php');
	echo getStrictDoctype();
	echo getHTMLtag();
	$stylesheetRef = 'default.css';
	$title = 'View Recipes';
	echo getHeadAndTitleTag($title, $stylesheetRef);
?>

<div id="outer">

<?php
	echo getNavigationHeader();
?>

<h4>Here are all the Recipe details from the database:</h4>

<!-- Table to display the results -->

<table>

<!-- Print the field names -->

	<tr>
		<th><a href="browserecipes.php?sort=Recipe Name">Recipe Name</a></th>
		<th><a href="browserecipes.php?sort=Cuisine Name">Cuisine Name</a></th>
		<th><a href="browserecipes.php?sort=Course Name">Course Name</a></th>
	</tr>

	<?php

	//create the query
	//$selectAll = makeSelectQuery('recipe_name, cuisine_name, course_name', 'recipes');
	$selectAll = "SELECT recipe_name, cuisine_name, course_name FROM recipes";


	$selectAll .= "ORDER BY ";

	switch($_GET['sort'])
	{
		case "Recipe Name": $selectAll .= "recipe_name"; break;
		case "Cuisine Name": $selectAll .= "cuisine_name"; break;
		case "Course Name": $selectAll .= "course_name"; break;
		default: $selectAll .= "recipe_id"; break;
	}

	$selectAll .= " ASC";

	//send query to the database and store the results in an array
	//the result array will be multi-dimensional: rows and columns
	$allRecipes = executeMultiSelect($selectAll);

	//print the contents of the array using for loops
	for($rows = 0; $rows < count($allRecipes); $rows++)
	{
		//create a variable for CSS formatting of odd and even rows
		//on the display table
		$rowClass = 'even';
		if($rows%2!=0)
		{
			$rowClass = 'odd';
		}

		echo '<tr class="'.$rowClass.'">';
		for($cols = 0; $cols < count($allRecipes[$rows]); $cols++)
		{
			echo '<td>'.$allRecipes[$rows][$cols].'</td>';
		}
		echo '</tr>';
	}
?>
</table>

<?php
	echo getFooterYo();
?>

</div>

</body>
</html>

and this is the output:

http://seven.bf.rmit.edu.au/~s3196397/Pear...owserecipes.php

 

I've googled the problem but it seems to be ok =S

Share this post


Link to post
Share on other sites

$selectAll .= "ORDER BY ";

needed to be

 

$selectAll .= " ORDER BY ";

>.>

 

 

Thanks for the help though! ^_^

Share this post


Link to post
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
Sign in to follow this  

×