[SOLVED] catagorising data

[rachae1]

Hi,

 

I am fairly new to php but I am trying to create a simple 1 level directory. I have figured out how to create a form to upload text and images and then display them on a page. I have also done some paging.

 

My input form has a field called type and it allows me to choose what category I want to put my data in.

 

At the moment my main page displays all the categories in the database perfectly but I want to separate them out so that I have links to the separate categories and have them paging.

 

Its probably a simple bit of code but i don't know how to do it can someone help.

 

Thanks

 

Rach

 

e.g  link1: <a href="link.php?type=category1" target="blank">category1</a>

e.g  link1: <a href="link.php?type=category2" target="blank">category2</a>

e.g  link2: <a href="link.php" target="blank">View All</a>

 

below is some code:

 

 

===============================================================

<?php

 

// If current page number, use it

// if not, set one!

 

 

 

if(!isset($_GET['page'])){

    $page = 1;

} else {

    $page = $_GET['page'];

}

 

// Define the number of results per page

$max_results = 2;

 

// Figure out the limit for the query based

// on the current page number.

$from = (($page * $max_results) - $max_results);

 

// Perform MySQL query on only the current page number's results

 

 

// Figure out the total number of results in DB:

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM link"),0);

 

// Figure out the total number of pages. Always round up using ceil()

$total_pages = ceil($total_results / $max_results);

 

// Build Page Number Hyperlinks

echo "<center><br />";

 

// Build Previous Link

if($page > 1){

    $prev = ($page - 1);

    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> ";

}

 

for($i = 1; $i <= $total_pages; $i++){

    if(($page) == $i){

        echo "$i ";

        } else {

            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";

    }

}

 

// Build Next Link

if($page < $total_pages){

    $next = ($page + 1);

    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";

}

 

 

echo "</center>";

?>

 

<a href="link.php?type=category1" target="blank">category1</a>

<a href="link.php?type=category2" target="blank">category2</a>

<a href="link.php" target="blank">View All</a>

 

<?php

$sql = mysql_query("SELECT * FROM link ORDER BY name LIMIT $from, $max_results");

while($row = mysql_fetch_array($sql)){

    // Build your formatted results here.

extract($row);

?>

 

[recklessgeneral]

Hi,

 

I'm still a little unsure where to go with this post, but I'll throw down a couple of things for you to mull over.

 

1. Are you wanting to generate the list of categories using a database query to replace the hand-crafted list in your original post? If so, you'd probably want to do something along the lines of:

 

$query = "SELECT DISTINCT type FROM link ORDER BY type";
$result = mysql_query ($query);
while ($row = mysql_fetch_array ($result))
{
    echo '<a href="link.php?type=' . $row['type'] . '">' . $row['type'] . '</a>';
}

 

As I don't know what your database structure is, I have had to guess a bit. If your categories are lumped in the same table as your file information, chances are that you'll get duplicate values when you query for categories. This is what the "DISTINCT" bit does. Database gurus tend to frown on this approach as it suggests poor table structure or the possibility of optimising queries (don't worry, I'm not a database guru, just know enough to be dangerous).

 

If the 'link' table is just for holding category/type information, you can get rid of the DISTINCT directive if you can already guarantee you won't have duplicates in your data.

 

2. In your link.php page you'll need to extract the type from the URL and pass that into your query. This'll be almost exactly the same format as you've posted, fitting in nicely with your paging. When you're building up the page number links, you'll have to pass the type in to those as well as the page number.

 

$type = $_GET['type'];

// Slightly different total pages query
$total_results  = mysql_result (mysql_query ("SELECT COUNT(*) AS num FROM link WHERE type='$type'"), 0);

// Page links will look like the following
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$&type=$typei\">$i</a> ";

// Finally the last query in your post would be as follows:
$sql = mysql_query("SELECT * FROM link ORDER BY name WHERE type = '$type' LIMIT $from, $max_results");

 

Does any of this make any sense, and if so is it any help?

Cheers,

Darren.

[rachae1]

Hi Darren,

 

Thank you for helping me out, I don't know php very well but I can seem to be able to work out most of the simple things but this one really stumped me.

 

I think the 2nd part of your information is what I need. I have managed to get it half working but i'm struggling to get my next / previous links to work.

 

 

This is the code so far for the categories:

 

 

<a href="links.php?type=category1">Category 1</a>

<a href="links.php?type=category2">Category 2</a>

 

<?php

 

 

$type = $_GET['type'];

 

?>

<?php

 

 

if(!isset($_GET['page'])){

    $page = 1;

} else {

    $page = $_GET['page'];

}

 

 

$max_results = 1;

$from = (($page * $max_results) - $max_results);

 

$total_results  = mysql_result (mysql_query ("SELECT COUNT(*) AS num FROM link WHERE type='$type'"), 0);

 

$total_pages = ceil($total_results / $max_results);

 

echo "<center><br />";

 

if($page > 1){

    $prev = ($page - 1);

    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"> << Previous</a> ";

}

 

for($i = 1; $i <= $total_pages; $i++){

    if(($page) == $i){

        echo "Page $i ";

        } else {

            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">Page $i</a> ";

    }

}

 

if($page < $total_pages){

    $next = ($page + 1);

    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\"> Next >></a>";

}

 

echo "<br/>";

echo "<br/>";

 

echo "</center><br />";

 

$query = mysql_query("SELECT * FROM link WHERE type='$type' ORDER BY name LIMIT $from, $max_results");

while($row = mysql_fetch_assoc($query)) {

extract($row);

 

echo $somecode;

 

}

?>

 

 

 

Also is it possible to display the last 10 added links when you first click on links.php as well as click on the link type categories?

 

I currently use this code on another page to display my last 10:

 

<?php

$sql=mysql_query('SELECT * FROM link ORDER BY ID DESC LIMIT 4');

while($row = mysql_fetch_array($sql)){

extract($row);

 

?>

 

Thanks

 

Rachael

 

[rachae1]

Hi, I have solved my next and links they work perfectly

 

If its possible I would like some help with showing the last 10 added links or all the links on links.php

 

Thanks

 

Rachael

 

 

[recklessgeneral]

Hi Rachael,

 

Glad I was able to help with my ramblings. It looks like you've got pretty much everything already in place to sort out the last 10 links problem - just slight variations on the SQL query are should be all you need.

 

1. To display all links (ordered by ID, for the sake of argument), irrespective of category type.

$query = "SELECT * FROM link ORDER BY id DESC";

 

2. We can now restrict the search by adding a WHERE clause to return only those links of a certain type:

$query = "SELECT * FROM link";
if ($type !='')
{
   $query .= " WHERE type='$type'"
}
$query .= " ORDER BY id DESC";

 

3. Limit the number of results returned by adding the LIMIT 10 directive:

// Append to code section 2 above
if ($limit)
{
   $query .= " LIMIT 10";
}

 

In section 3 I invented a variable named $limit. You would set this to true if you wish to limit the list to the last 10 links, or set it to false (or leave it unset) to display all links.

 

Once you've assembled the query string, pass it to mysql_query() and do your usual while($row = mysql_fetch_assoc($query)) thing.

 

Cheers,

Darren.

[rachae1]

Hi Dave,

 

I have tried to do the code like you said but I get a error.  :'(

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result

 

I wrote the code like this.

 

$query = "SELECT * FROM link ORDER BY name DESC";

$query = "SELECT * FROM link";

 

if ($type !=''){

$query .= "WHERE type='$type'";

}

 

$query .= "ORDER BY name DESC";

 

if ($limit)

{

$query .= "LIMIT 10";

}

 

while($row = mysql_fetch_assoc($query)) {

extract($row);

?>

[recklessgeneral]

Hi Rachael,

 

The code I supplied was more of an illustration rather than to be copied verbatim. The query string ($query) must be passed to mysql_query(), then the result set passed to mysql_fetch_assoc(), as you have done in previous code. Second, the snippets aimed to show how to build up a sql query based on information either built into the page itself or passed in through $_GET array. It's tricky giving concrete code as I don't know the structure of your database or fully understand how all these bits fit together, so I'm just kind of throwing stuff out hoping that something will crop up that you can use.

 

Maybe if you posted your link.php file as it is right now with a description of the remaining areas I might be able to decloud my brain a bit.

 

Cheers,

Darren (not Dave)

[rachae1]

I tried it again this morning fully awake and it worked like a charm.

 

Thanks for your help

 

Rachael

 

 

 

$query = mysql_query("SELECT * FROM link  ORDER BY name LIMIT $from, $max_results");

 

 

if($type['type'] != '') {

$query = mysql_query("SELECT * FROM link WHERE type='$type' ORDER BY name LIMIT $from, $max_results");

}

 

 

while($row = mysql_fetch_assoc($query)) {

extract($row);

 

?>