[SOLVED] Pagination help

[mccragge]

Obviously, I am new to the forum, and I have done multiple searches and what not to see if my problem was already addressed and I couldn't find anything.

 

I am running a Hot or Not program (except it isn't for people it is for painted miniatures) anyway, the user account would show all the miniature pictures uploaded and I needed a pagination to deal with this. Well I managed to get one to work (thanks by the way, all your tutorials and what not were invaluable) However, I do have one problem.

 

When a user access's there own account, they now see everyones miniatures that have been uploaded to the server. The images are held in a different database then the user info. What I am wondering is, how do I do the pagination so that it checks the id of the miniature image vs the id of the user so that it only displays the image that belongs to the proper user and not the whole image database.

 

I can post the script I have so far if needed.

 

McCragge

[cooldude832]

I'm assumign you have 2 tables at least (1 for users, 1 for minatures)

in the minatures table i'm sure you have 2 Key variables named MiniID (or some for of that pirmary key of table auto incremennting) and UserID (off user table) if you don't you can't do what you want.  Anyway the way to do this is simply in the user area say for your query

<?php
$q = "Select * From `minis` Where UserID = ".$_SESSION['UserID'];
?>

I'm assuming you have on your  login script a session being set = to the user's id number. 

[mccragge]

First off, let me just say thank you for the quick response. I really appreciate it. Also I am a bit nervous to ask question as I am a hopeless newbie when it comes to php. (Although I am reading several books and combing through countless tutorials) I find that it can be quite difficult for me. So thank you for bearing with me on this.

 

I'm assumign you have 2 tables at least (1 for users, 1 for minatures)in the minatures table i'm sure you have 2 Key variables named MiniID (or some for of that pirmary key of table auto incremennting) and UserID (off user table) if you don't you can't do what you want.

 

Yes I do have both of these. On the miniatures table, it is "id" and on the users table it is "name"

 

  Anyway the way to do this is simply in the user area say for your query

<?php
$q = "Select * From `minis` Where UserID = ".$_SESSION['UserID'];
?>

 

Where do I insert this code exactly? I am not sure I understand. This simple line of code will keep the images that belong to that user as the only ones he sees?

 

I'm assuming you have on your  login script a session being set = to the user's id number. 

 

Well I searched my scripts on the login.php and user.php and didn't find any code with the word session in it. If you like I can post what I have for my pagination code if you like. I don't know if that will help or not.

 

McCragge

[cooldude832]

okay i'll  answer in reverse order it will make more sense.

On your login script what does it do to make it know who is logged in (cookies might be used, if so its a slight mod).  What i'm saying is your login has to register some data (either cookies or sessions) that say hey so and so is logged in on this computer so show their user info. 

Secondly Why are you using a varchar as a linkiing between the two tables you should use the UserID.  Primary keys are by designed used for linking tables together.  The idea behind a primary key is to provide a simple piece of data associated with the row that is Unique and Minimal in the byte size (i.e an integer)  If you use a text string you are only bloating your tables.  Any way whatever you are registering in your login part you need to use to recall what you are pulling off the database in the query to retrive the data about the images.  I'd have to really see your scripts to help you out, but i'll send you a pm about this.

[mccragge]

okay i'll  answer in reverse order it will make more sense.

On your login script what does it do to make it know who is logged in (cookies might be used, if so its a slight mod).  What i'm saying is your login has to register some data (either cookies or sessions) that say hey so and so is logged in on this computer so show their user info.

 

Looking through my login.php scripts it does appear to use cookies for the login. I don't if this helps or not, but I am using PHPIX for the image rating scripts.

 

Secondly Why are you using a varchar as a linkiing between the two tables you should use the UserID.  Primary keys are by designed used for linking tables together.  The idea behind a primary key is to provide a simple piece of data associated with the row that is Unique and Minimal in the byte size (i.e an integer)  If you use a text string you are only bloating your tables.  Any way whatever you are registering in your login part you need to use to recall what you are pulling off the database in the query to retrive the data about the images.  I'd have to really see your scripts to help you out, but i'll send you a pm about this.

 

Well, it wasn't like i had a choice that I was aware of. The PHPIX scripts set up the database that way. Is there a way to fix that? Or is it too late? Also, I use the name McCragge on Yahoo messenger which I am on right now. Again, thank you for the help.

 

McCragge

[mccragge]

ATTN: cooldude832

 

I tried to write you back via the PM, however it wouldn't allow me to reply or even write a new message. I am assuming I don't have enough posts to allow that priveladge.

 

Anyway, I am on both MSN Messenger and Yahoo messenger. As [email protected] or just mccragge.

 

In any case, I haven't figured out this problem, so any help would be greatly appreciated.

 

Thank you.

 

McCragge

[teng84]

hard to read the conversation can you give me what you want in a brief explanation

[mccragge]

hard to read the conversation can you give me what you want in a brief explanation

 

Yes

 

Ok basicly what I have is this:

 

I have a hot or not script (That I use for painted miniatures and not people) where people can upload their images of miniatures. However to manage this I put in place a pagination script. Well, the script works but it shows all the image in the database instead of just the specific users.

 

So what I need is a way for the script to compare the image id vs the users name and only show those that belong to that user. Unfortunately, I am a newbie when it comes to PHP. So where I am supposed to insert the code may also be a problem as well.

 

All help is appreciated.

 

McCragge

 

 

[teng84]

ok its getting harder because havent start for anything but i guess cool dude mean is that you perform a query that will match the user account tru session variable

 

heres the code he has

$q = "Select * From `minis` Where UserID = ".$_SESSION['UserID']; this is right

 

BTW its better to condition the id rather than username and can i see the fields structure of your table

 

 

[mccragge]

ok its getting harder because havent start for anything but i guess cool dude mean is that you perform a query that will match the user account tru session variable

 

Let me know what you need and I will see if I can provide it.

 

heres the code he has

$q = "Select * From `minis` Where UserID = ".$_SESSION['UserID']; this is right

 

Problem is that it uses cookies rather then sessions. I am using the PHPIX Image Rating program. If that helps at all.

 

 

BTW its better to condition the id rather than username and can i see the fields structure of your table

 

 

Also the users and images are in two different tables. And the users don't seem to be listed by id but by names, which I too thought was odd, and I am sure if that can be fixed.

 

Here are some screenshots of both the users and images table

 

http://www.easternfringe.com/ef/mccragge/imagetable.jpg

 

http://www.easternfringe.com/ef/mccragge/usertable.jpg

 

McCragge

[teng84]

show me the query that is giving your prob?

[mccragge]

I am kinda nervous posting this after reading the warnings and FAQs on this site. I hope I am not posting to much. I don't want to offend anyone.

 

In any case here is what I got:

 

<?

include 'db.php';

if (!isset($per_page)) $per_page = 10;
$sql_text = ("select id,name,category,url,description,notifypub,average,total,status from $imagetable");

// Set page #, if no page isspecified, assume page 1
if (!$page)  $page = 1;
$prev_page = $page++;
$next_page = $page--;

$query = mysql_query($sql_text);

// Set up specified page
$page_start = ($per_page * $page) - $per_page;
$num_rows = mysql_num_rows($query);

if ($num_rows <= $per_page) $num_pages = 1;
elseif (($num_rows % $per_page) == 0) $num_pages = ($num_rows / $per_page);
else    $num_pages = ($num_rows / $per_page) + 1;

$num_pages = (int) $num_pages;

if (($page > $num_pages) || ($page < 0)) errormsg("You have specified an invalid page number");

$sql_text .= " LIMIT $page_start, $per_page";
$query = mysql_query($sql_text);

while ($result = mysql_fetch_array($query)) {

$url=mysql_result($imgresult,$count,"url");
$id=mysql_result($imgresult,$count,"id");
$category=mysql_result($imgresult,$count,"category");
$description=mysql_result($imgresult,$count,"description");
$notifypub=mysql_result($imgresult,$count,"notifypub");
$average=mysql_result($imgresult,$count,"average");
$total=mysql_result($imgresult,$count,"total");
$status=mysql_result($imgresult,$count,"status");
print "<center><table border=\"0\" width=\"100%\" bgcolor=\"#000000\">\n";
print "                  <tr>\n";
print "                    <td colspan=\"2\" align=\"center\" valign=\"top\"> \n";
print "                      <form name=\"form1\" action=\"$PHP_SELF\" method=\"POST\">\n";

print "                        <table width=\"100%\" border=\"0\" cellspacing=\"2\" cellpadding=\"2\">\n";
print "                          <tr> \n";
print "                            <td rowspan=\"6\" width=\"27%\"> \n";
print "                              <div align=\"center\"> <img src=\"$result[url]\" width=\"175\"><br>\n";
print "                              </div>\n";
print "                            </td>\n";
print "                            <td width=\"23%\" valign=\"top\"> \n";
print "                              <div align=\"right\"><font size=\"2\">".IMGID."</font></div>\n";
print "                            </td>\n";
print "                            <td width=\"50%\" valign=\"top\"><font size=\"2\">$result[id]</font></td>\n";
print "                          </tr>\n";
print "                          <tr> \n";
print "                            <td width=\"23%\"> \n";
print "                              <div align=\"right\"><font size=\"2\">".STATUS."</font></div>\n";
print "                            </td>\n";
print "                            <td width=\"50%\"><font size=\"2\"><b>$result[status]</b></font></td>\n";
print "                          </tr>\n";
print "                          <tr> \n";
print "                            <td width=\"23%\"> \n";
print "                              <div align=\"right\"><font size=\"2\">".RATING."</font></div>\n";
print "                            </td>\n";
print "                            <td width=\"50%\"><font size=\"2\"><b>average ($total ".V.")</b>  <a href=\"javascript:void(0);\" onClick=\"scrollScreen('$profilephp?id=$id&u=$cookieinfo')\">Viewer Comments</a></font></td>\n";
print "                          </tr>\n";
print "                          <tr> \n";
print "                            <td colspan=\"4\"> <font size=\"2\">".IMGURL."<br>\n";
print "                              <input type=\"text\" name=\"submiturl\" size=\"50\" value=\"$result[url]\">\n";
print "                              <br>\n";
print "                              ".DESCRIBE."<br>\n";
print "                              <input type=\"text\" name=\"submitdescription\" size=\"50\" value=\"$result[description]\" maxlength=\"60\">\n";
print "                              <br>\n";
print CATEGORY." <select name=\"updatecat\" size=\"1\">";
print " <option value=\"$category\">$result[category]</option>";
foreach ($categories as $a) print "<option value=\"$a\">$a</option>";
print "                          </select><br>";
print "                          <input type=checkbox name=\"submitnotifypub\" ";
if ($result[notifypub] == "1") print "checked ";
print " value=\"1\">".NOTIFYPUB."<br>\n";
print "                              <select name=\"action\">\n";
print "                                <option value=\"updatepic\" selected>".UPDATEIMG."</option>\n";
print "                                <option value=\"remove\">".REMOVEIMG."</option>\n";
print "                              </select>\n";
print "                              <input type=\"hidden\" name=\"submitid\" value=\"$result[id]\">\n";
print "                              <input type=\"hidden\" name=\"go\" value=\"userphp\">\n";
print "                              <input type=\"submit\" value=\"".DOIT."\">\n";
print "                              <br>\n";
print "                              <font size=\"1\">".MODNOTE."</font><br>\n";
print "                              </font></td>\n";
print "                          </tr>\n";
print "                        </table>\n";
print "                      </form>\n";
print "                    </td>\n";
print "                  </tr>\n";
print "                </table></center>\n";
print "              <hr width=\"500\">\n";
}

?> 

<center>

<?
if ($prev_page) echo "<a href=\"$PHP_SELF?go=userphp&page=$prev_page\">Prev</a>";

   for ($i = 1; $i <= $num_pages; $i++) {
   if ($i != $page) echo " <a href=\"$PHP_SELF?go=userphp&page=$i\">$i</a> ";
  else echo " $i ";
   if ($i == 15) echo "<br>";
}

// Next
if ($page != $num_pages) echo "<a href=\"$PHP_SELF?go=userphp&page=$next_page\">Next</a>";
?>

</center>

 

Is that what you are wanting to see?

[teng84]

heres how you display the record

$sql_text = ("select id,name,category,url,description,notifypub,average,total,status from $imagetable");

no filtration thats why you get all the result

try your query like

 

select id,name,category,url,description,notifypub,average,total,status from $imagetable

inner join yourothertable on yourtable.category = $imagetable.category

where yourcookie here = $imagetable.category

 

 

 

[cooldude832]

Fyi this script is very poorly designed, Don't know if you paid for it, but if you did its not a professional script at all.  First of all pagnation generally involves limiting the # of results on the physical query by using the LIMIT flag/param on the query, secondly  The table normalization/indexing/linking is so poorly done that it is using way more resources than needed.  If you are really interested in mysql/php it would behoove you to right your own better image rating script (yes I used the word behoove)

[mccragge]

Ok I'll give that a try and see what happens.

 

McCragge

[mccragge]

Fyi this script is very poorly designed, Don't know if you paid for it, but if you did its not a professional script at all.  First of all pagnation generally involves limiting the # of results on the physical query by using the LIMIT flag/param on the query, secondly  The table normalization/indexing/linking is so poorly done that it is using way more resources than needed.  If you are really interested in mysql/php it would behoove you to right your own better image rating script (yes I used the word behoove)

 

Umm, er yeah, I did pay for it actually from PHPZEN it is called PHPIX Image Rater. Anyway since I have it I might as well make the best of it for now.

 

Well I am interested in mysql/php but I don't think I am at the level that I can confidently write my own image rating script. However this is turning into quite the learning experience to say the least.

 

Also, that isn't the whole user.php script just the pagination area of it. Should I have posted the whole page?

 

 

[mccragge]

heres how you display the record

$sql_text = ("select id,name,category,url,description,notifypub,average,total,status from $imagetable");

no filtration thats why you get all the result

try your query like

 

select id,name,category,url,description,notifypub,average,total,status from $imagetable

inner join yourothertable on yourtable.category = $imagetable.category

where yourcookie here = $imagetable.category

 

Just to be clear is this what you mean?

 

$sql_text = ("select id,name,category,url,description,notifypub,average,total,status from $imagetable");

---Replace With---

$sql_text = ("select id,name,category,url,description,notifypub,average,total,status from $imagetable
inner join yourothertable on yourtable.category = $imagetable.category
where yourcookie here = $imagetable.category");

 

Is this what you mean?

[cooldude832]

teng whats the point of the join if the point is only to gain the Where Part, the inner join only slows things down for no reason

[corbin]

Hmmm... 3 more posts have been posted in the time of this posts writing, so it's mildly off topic now..... But here it is anyway:

 

To further expand on cooldude's post (behoove is an awesome word, by the way), the basic concept of pagination is simple:

 

Generally some indicator tells you where you are in standings of your record set... such as ?min=10 if you wanted to start showing at the tenth record (or it could be done with pages, like ?page=3 for the third page).  This then is used to limit sql results.... For example:

 

$page = (isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] >= 0) ? $_GET['page'] : 0; //if page is set in the get array, it's numeric, and it's greater than or equal to zero, use that value.  If it's not, then set it to the default value of 0.

$max_entries = 30; //30 entries per page

$min = $page*$max_entries;

$max = $min + $max_entries;

mysql_connect(); //blah
mysql_select_db('some_db'); //more blah

$q = mysql_query("SELECT something FROM somewhere ORDER BY some_column LIMIT {$min}, {$max}");

while($r = mysql_fetch_assoc($q)) {
//do something with the mysql record set
}

[cooldude832]

yes behoove is a great word, I have a plethora of fun words that I dish out when I feel like it, any way i think he isn't goign to modify it to a more natural paganation because he is too invested in this script

[corbin]

Ahhh plethora is right up there with cornicopia... Or maybe copious amounts.... lol ;p

 

Yeah.... I read that after I wrote that, but I figured I would post it anyway since I had already typed it out....

[mccragge]

Hmmm... 3 more posts have been posted in the time of this posts writing, so it's mildly off topic now..... But here it is anyway:

 

To further expand on cooldude's post (behoove is an awesome word, by the way), the basic concept of pagination is simple:

 

Generally some indicator tells you where you are in standings of your record set... such as ?min=10 if you wanted to start showing at the tenth record (or it could be done with pages, like ?page=3 for the third page).  This then is used to limit sql results.... For example:

 

$page = (isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] >= 0) ? $_GET['page'] : 0; //if page is set in the get array, it's numeric, and it's greater than or equal to zero, use that value.  If it's not, then set it to the default value of 0.

$max_entries = 30; //30 entries per page

$min = $page*$max_entries;

$max = $min + $max_entries;

mysql_connect(); //blah
mysql_select_db('some_db'); //more blah

$q = mysql_query("SELECT something FROM somewhere ORDER BY some_column LIMIT {$min}, {$max}");

while($r = mysql_fetch_assoc($q)) {
//do something with the mysql record set
}

 

Isn't this pretty much what I already have? Well albeit a lot prettier. But still it would show all the images in the database and not exclude the images that belong to specific users.

 

Which is kinda the problem I am having. (Although to be honest, the pagination I have used is the only oone out of countless tutorials that I actually got to work)

 

McCragge

[corbin]

$q = mysql_query("SELECT something FROM somewhere WHERE some_column = 'some_value' ORDER BY some_column LIMIT {$min}, {$max}");

 

That would limit the results to a certain thing.... such as:

 

$q = mysql_query("SELECT something FROM somewhere WHERE owner_id = '{$uid}' ORDER BY some_column LIMIT {$min}, {$max}");

[teng84]

teng whats the point of the join if the point is only to gain the Where Part, the inner join only slows things down for no reason

 

the tread starter need to get the data from another table as shown in the link he gave us any way sorry if i cant join anymore my site has an error i need to fix it right away my client should and the user should not see this

 

good luck and sorry

[mccragge]

Well it seems that it got worked out.

 

Special thanks to cooldude832 for doing all the hard stuff.

 

And thanks everyone that responded, I appreciated all the help.

 

McCragge