[SOLVED] I think im doing something wrong with my paging :( Please help.

[rachae1]

Hi, I am very new to php and not very good explaining so I will try my best to explain this :S

 

I am trying to add extra categories to my paging but I am stuck.

 

When I click on category2 (type2) and category3 (type 3) it shows all the data on the 1st page fine but if I have more than 5 entries and I click the next button it displays an empty page and my next / prev links disappear.

 

Category type1 displays fine, can someone help me out with this.

 

Thanks

 

Rachael

 

 

 

<a href="link.php?type=categoryname1">Category1</a> |
<a href="link.php?type2=categoryname2">Category2</a> | 
<a href="link.php?type3=categoryname3">Category3</a> | 
<a href="link.php">View All</a>


<?php

$type = $_GET['type'];
$type2 = $_GET['type2'];
$type3 = $_GET['type3'];
// 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 = 5;

// 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

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

if($type['type'] != '') {
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM link WHERE type='".$type."'"),0);
}

if($type2['type2'] != '') {
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM link WHERE type2='".$type2."'"),0);
}

if($type3['type3'] != '') {
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM link WHERE type3='".$type3."'"),0);
}



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

echo "<center><br />";

if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['REQUEST_URI']."page=\"> << Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "Page $i ";
        } else {echo "<a href=\"".$_SERVER['REQUEST_URI']."?page=$i&type=$type\">$i</a> ";
            
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['REQUEST_URI']."?page=$next\"> Next >></a>";
}


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

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



$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");
}

if($type2['type2'] != '') {
$query = mysql_query("SELECT * FROM link WHERE type2='$type2' ORDER BY name LIMIT $from, $max_results");
}

if($type3['type3'] != '') {
$query = mysql_query("SELECT * FROM link WHERE type3='$type3' ORDER BY name LIMIT $from, $max_results");
}


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

?>

[gerkintrigg]

this is as much of my own pagination script as I can give away without having to kill you:

$i=0;
$c=0;
$c_real=0;
$no_of_imgs_per_page=12;
$no_of_imgs_total=mysql_num_rows($shopsql);

// round up function:
function roundup ($value, $dp)
{
    // Offset to add to $value to cause round() to round up to nearest significant digit for '$dp' decimal places
    $offset = pow (10, -($dp + 1)) * 5;
    return round ($value + $offset, $dp);
}
//nop means "number of pages":
$nop= roundup(($no_of_imgs_total/$no_of_imgs_per_page),0);

if($_REQUEST['page']!=''){
$page=$_REQUEST['page'];
}
else{
$page=1;
}

// get page maximum number:
$page_min=(($page-1)*$no_of_imgs_per_page)+1;
// get page minimum number:
$page_max=($page*$no_of_imgs_per_page);

echo '<tr class="body_txt"><td colspan="4" align="center"><span class="boldText">Pages:</span> ';
// add the number of pages here.
$_SESSION['search']=$sql;

$nop_count=1;
while($nop_count<=$nop){
if ($nop_count!=$page){
	echo '<a href="'.$_SERVER['PHP_SELF'].'?page='.$nop_count.'&pop='.$_REQUEST['pop'].'&open='.$_REQUEST['open'].'&search=y" 
	class="body_txt">'.$nop_count.'</a>   ';
	$nop_count++;
}
else{
	echo '<span class="alert_txt">'.$nop_count.'</span>   ';
	$nop_count++;
}
}
echo'</td></tr>';

while(($shop_row = mysql_fetch_array($shopsql))){ 
$c++;
if (($c>=$page_min)&&($c<=$page_max)){
	$i++;
	if($i==1){
		echo'  <tr class="body_txt">';
	}
	if (strip_tags(preg_match('/(\S*\s){20}/', strip_tags(str_replace("'",'`', 
		str_replace('"',"''",$shop_row['item_description']))), $matches))){
	    	$excerpt = strip_tags($matches[0]).'...';
		}

 

You'll need to work the rest out yourself.

[recklessgeneral]

Hi Rachael,

 

I'm not sure about these bits:

 

if($type['type'] != '') {
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM link WHERE type='".$type."'"),0);
}

 

Here, $type has been extracted from $_GET['type'] and so should be a string which in this case will contain "categoryname1", based on the links at the top of the quoted code. You should then just use a regular string comparison

 

if ($type != 'categoryname1')
{
   // get total_results...
}

 

I thought about raising the topic in one of your previous posts, but can't remember if I did or not - I'd recommend using a separate table for storing categories, as it would make the maintenance of your link table easier. For example, what if one of your link things has to have a fourth category? You'll then need 4 sets of almost identical code.

 

For this many-to-many relationship (links can have many categories, each category has mank links) you actually then use a 3rd table, say link_category, to associate links and categories. An outline of these tables would be:

 

table link:

  id int (primary key)

  name varchar

  // any other fields

 

table category:

  id int (primary key)

  name varchar

 

table link_category:

  id int (primary key)

  link_id (foreign key to link.id)

  category_id (foreign key to category.id)

 

You then only need to pass 'type' in the url instead of 'type2', 'type3' etc. Your queries would be more complex however:

 

"SELECT * FROM link l, category c, link_category lc WHERE c.name='$type' AND lc.category_id = c.id AND lc.link_id = l.id"

 

This is going off memory so may not be exactly right, but should be in the ballpark. This will reduce the amount of duplicated data and retyping of code. It's also a whole new source of debugging potential.

 

Cheers,

Darren.

[rachae1]

I think this is going right over my head now and I may need to start over, do you know any good tutorials for building a link directory with multiple categories ?

 

Thanks

Rach

[rachae1]

 

I have tried to absorb some of the information and change my tables.

 

I am not sure what foreign key is, I have tried to search for it but i'm struggling to find an answer.

 

At the moment my tables look like this:

 

CREATE TABLE `category` (
  `catagory_id` int(50) NOT NULL auto_increment,
  `catagory_name` varchar(150) NOT NULL,
  PRIMARY KEY  (`catagory_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `link` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate latin1_general_ci NOT NULL default '',
  `url` varchar(130) collate latin1_general_ci NOT NULL default '',
  `description` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=119 ;


CREATE TABLE `link_to_cat` (
  `link_catid` int(50) NOT NULL auto_increment,
  `link_id` int(50) NOT NULL,
  `catagory_id` int(50) NOT NULL,
  PRIMARY KEY  (`link_catid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

I need to modify my submit code for my add links page, can you tell me how I tell it to submit the category data as well as the current link data, and do I need to tell it to do anything with link_to_cat in the submit page?

 

This is what I tried to do but it didn't work with the insert into links:

 

$query  = "INSERT INTO category ";
$query .= "(catagory_name) ";
$query .= "VALUES ("; 
$query .= "'" . $category . "'";
$query .= ");";

 

My add links code currently looks like this

 

 

if ($submit == 'Add') {
if ($name == ""){
echo forwardScript($href);
exit;
}   
else {
$query  = "INSERT INTO link ";
$query .= "(url, name, description) ";
$query .= "VALUES ("; 
$query .= "'" . $URL . "',";
$query .= "'" . $name . "',";
$query .= "'" . $description . "'";
$query .= ");";
                
$query_result = domysql($query, 'Adding a link' . $_SERVER['PHP_SELF']); }

 

 

Sorry to ask so many questions and I appreciate the help.

 

Thanks

 

Rach

 

[rachae1]

Hi, Please don't post anything here yet, i might be a step closer to my problem, will let you know how I get on.

 

Thanks

 

Rachael

[rachae1]

ok, i didnt get very far, I tried to look up foreign key and I altered by tables so they look like this, not sure if they are correct though:

 

CREATE TABLE link_to_cat (
id int(50) NOT NULL auto_increment,
link_id varchar(255) NOT NULL,
category_id varchar(255) NOT NULL,
PRIMARY KEY  (id),
FOREIGN KEY(link_id)REFERENCES link(id),
FOREIGN KEY(category_id)REFERENCES category(id)
  
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 


CREATE TABLE `category` (
  `id` int(50) NOT NULL auto_increment,
  `type` varchar(150) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;



CREATE TABLE `link` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate latin1_general_ci NOT NULL default '',
  `url` varchar(130) collate latin1_general_ci NOT NULL default '',
  `description` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=122 ;

 

 

 

When I submit my form I get the error "Query was empty" I tried to write my submit code like this:

 

if (!isset($_POST['submit']) || $_POST['submit'] == ''){ 
if(!isset($_GET['id'])) {	
	exit;
}
else{
	$id = $_GET['id'];
	}
}
extract($_POST);

if ($submit == 'Add') {
if ($linkName == ""){
	echo forwardScript($href);
	exit;
} else {


$id = mysql_insert_id();

$sql1 = "INSERT INTO link (id, name, url, description) VALUES ('$id','$LinkName', '$linkURL', '$LinkDescription')";
mysql_query($sql1) or die (mysql_error());

$sql3 = "INSERT INTO category (id, type) VALUES ('$id', '$linkType')";
mysql_query($sql) or die (mysql_error());

$sql2="insert into link_to_cat(ID) values($id)";
  }
}

[recklessgeneral]

Hi Rachael,

 

It's been a while since I've used foreign key syntax, but the table structures look about right though.

 

A couple of things with the code you posted:

1. Don't include the id field in your insert statements. The tables have been defined to use an auto increment field for their id, so the database will take care of assigning unique numbers for the ids.

2. Use the mysql_insert_id() function after the insert statement has been run to return auto increment value that was chosen. These ids can then be used to hook up the values in the link_to_cat table.

 

Now I'll assume that when the user adds a new link, he/she will type in a category for that link. As you're likely to have multiple links in the same category (indeed, that was the point of this restructuring), you don't want to have duplicates in the category table. So first you should query the category table to see if there is an existing category with that name, and if so, return its id. If the select returns 0 rows, the category will be new so insert it into the category table and get the id using the mysql_insert_id function.

 

Next, insert the link as you are doing now, but without explicitly inserting the id field. Get the id using the mysql_insert_id function.

 

You now have both bits of information required to insert an entry into the link_to_cat table. Again, ignoring the id field, just insert the link_id and category_id values using the results from the previous two inserts.

 

Note that when the user wishes to add another category for the same link, you just need to add a row to the link_to_cat table, using the same link_id and a different category_id.

 

A limitation with getting the user to type the category for each link is that this could introduce typos, so you may run into the possibility of having 2 categories that mean the same thing but will have different entries. Depending on how many categories you're likely to have, either present a drop-down list in the link form to choose an existing category to add the link to, or if you're displaying a page listing all links in a given category, add an option to add a link to that category. Adding categories could then be done in a different form. However, these are just suggestions you can think about.

 

Cheers,

Darren.

[rachae1]

Hi Darren,

 

I noticed that the foreign key needed the InnoDB engine for it to work,

myisam was changing my foreign keys to KEY `link` ( `link_id` ) ,

 

I have made sure that InnoDB engine is installed but now when I insert the table data I get an error, I am able to change the myisam to innodb but this is no good as it has already changed the key.

 

 

SQL query:

CREATE TABLE `link_to_cat` (
`id` int( 50 ) NOT NULL AUTO_INCREMENT ,
`link` varchar( 255 ) NOT NULL ,
`category` varchar( 255 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
FOREIGN KEY `link` ( `link_id` ) ,
FOREIGN KEY `category` ( `category_id` )
) ENGINE = InnoDB

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
  Foreign KEY `category` (`category_id`)
) ENGINE=InnoDB' at line 6 

 

 

Do I need this foreign key, is there anything else that I can do? as I don't know php very well not sure if im going down the wrong path.

 

I do currently use a drop down box which allows me to currently allocate 1 category to the link, I added another drop box for type2, and type3 originally so that I could assign it to more than 1 category. I am tempted to leave the categories out as it seems to be a problem, but if I have too many pages people wont bother to go through them all.

 

I have attached a screenshot of my links page. If you have any more ideas, can you tell me what I need to research to build this?

 

Thanks for you help

 

Rachael

 

 

 

 

 

[attachment deleted by admin]

[recklessgeneral]

Hi Rachael,

 

Although you've now marked this thread as solved, I thought I would answer your last couple of questions anyway. I hadn't realised the hoops you'd have to jump through to get mysql working with foreign keys. Last time I used foreign keys properly was several years ago on Oracle, and they just worked. Not sure what this InnoDB engine thing is all about...

 

The foreign keys themselves are just constraint directives that allows the database to apply consistency checks on your data. By applying these constraints to the link_id and category_id fields, your saying that you only want ids that actually exist in the database already. You can remove the foreign key constraints and conceptually the database is the same, it just won't check that you're referencing a real link or category. To be honest, this is what I do - I use the concept of foreign keys without actually enforcing them at the database level.

 

Is your website live yet? Your screen grab looks pretty snazzy, what's the url? If its online and I get a chance I'll have a wander over and check it out.

 

Cheers,

Darren.