Another MySQL Search thing

drucifer

first off, the obvious warning that i am a novice....but here goes...

I have managed to pisece together a script that selects from MySQL, displays the data as I want it and includes 'prev/next' buttons etc....It works fine when the 'WHERE whatever' part of the select query is in the same script, however, when I try and tie up the script with a seperate form to post the WHERE variable (ie WHERE id is '$form_input') to the search script, i find that though it displays the first page of results ok, the 'next' link comes up with no results even though it knows there should be a following page....

Can anyone help? Or even understand what i'm trying to say?

jamfrag

Are you passing the value of $form_input to the next page??

I assume the next page redoes the mysql query with a different limit setting 0,20, 20,20 etc, so it will need to know $form_input for the where clause

James

drucifer

yeah, i'm passing on the $form_id from the form and it's as if the query remembers the value of $form_id for the first results page and then forgets it on the second....this is the bit of code that is relevant:

$numresults=mysql_query("SELECT * FROM content where id like '$form_id' ");
$numrows=mysql_num_rows($numresults);

if (empty($offset)) {
$offset=0;
}

$query = "SELECT * FROM content where id like '$form_id' order by id limit $offset,$limit";

$result = mysql_query ($query)
or die ("query 2 failed");

$num_rows = mysql_num_rows($numresults);

echo " $num_rows Matching Results \n";

while ($row = mysql_fetch_array ($result))
{
for ($i = 0; $i < mysql_num_fields ($result); $i++)
{
if ($i > 0)
print (" ");

if ($i == 0){
	print "<b>Id: </b>";	
}
else if ($i == 1){
	print "<b>Article: </b>";
}
else if ($i == 2){
	print "<b>code: <a href=\"$PHP_SELF?id=$row[id]\"><img 
    src=\"images/thumb_$row[code].jpg\"></b>";
}
else{
	print "<b>Date:</b><br>";
}


print ($row[$i]);


}

print " <center><img src='black.gif' width=590 height=1 border=0></center>";
print ("");
}
if ($offset >= 3) {
$prevoffset = $offset - $limit;
print "<a href=\"xxx.php?offset=$prevoffset\">PREV</a> \n";
}

$pages=intval($numrows/$limit);
if ($pages < ($numrows/$limit)){
$pages=($pages + 1);

}

for ($i = 1; $i <= $pages; $i++) {
$newoffset = $limit*($i-1);
if ($newoffset == $offset) {
print "$i \n";
} else {
print "<a href=\"xxx.php?offset=$newoffset\">$i</a> \n";
}
}

//show next if not last
if (! ( ($offset/$limit) == ($pages - 1) ) && ($pages != 1) ) {
$newoffset = $offset+$limit;
print "<a href=\"xxx.php?offset=$newoffset\">NEXT</a>\n";
}

That includes the prev/next buttons and the query...can you see any reson why this would happen? if not i could ut a working version online maybe, any help would be really appreciated..

jamfrag

try adding $file_id as another parameter to the

"<a href=\"xxx.php?offset=$prevoffset\">

bit, just like the offset is passed

drucifer

sounds logical but how exactly do i add it in...i've only been messing around with php with about a week and this is the first time I've dealt with links referencing the the files in which they appear...

jamfrag

you basically have it already

you have
"<a href=\"xxx.php?offset=$prevoffset\">

ok
change it to something like
"<a href=\"xxx.phpoffset=$prevoffset&fileid=$file_id\">

i think its best to avoid _ in the param name
so at the top of the page translate $fileid into $file_id

like this (for want of a better way)

if($fileid) $file_id = $fileid;

I haven't tried any of this but it should work

James

drucifer

Ok, i've done that and wer are definately gettng closer....the lniks seem to work but there seems to be alittle problem with my prev/next and page number links..ie. sometimes you only get the next button without the page numbers and sometimes not....

if you get a chance you can take a look at:

http://www.rhpinternet.com/search/new_form.php

if you choose 'all articles' (there are only six in total' you can see what i mean.

By the way - i REALLY appreciate your help so far dude.

jamfrag

ok then

it seems to be down to how you are working out your prev and next and where you are at the mo

first off change

if ($offset >= 3) {
$prevoffset = $offset - $limit;
print "<a href=\"xxx.php?offset=$prevoffset\">PREV</a> \n";
}
to

if ($offset >= $limit) {
$prevoffset = $offset - $limit;
print "<a href=\"xxx.php?offset=$prevoffset\">PREV</a> \n";
}
that will make it easier to alter your limit variable and will make prev link appear on the page with 3&4 on

i would also on do
$numrows=mysql_num_rows($numresults);
the first time and then pass the value (like with $file_id)

if (!$numrows) $numrows=mysql_num_rows($numresults);

add &numrows=$numrows to the links

use this to work out when you comw to end of the result set, i.e when not show next link

if ($offset+$limit < $numrows) {
$newoffset = $offset+$limit;
print "<a href=\"xxx.php?offset=$newoffset\">NEXT</a>\n";
}

that should sort out the prev/next links

jamfrag

as for pages

try this in place of what you have

for ($i = 1; $i < $numrows; $i+2) {
$newoffset = $i-1;
if ($newoffset == $offset) {
print "$i \n";
} else {
print "<a href=\"xxx.php?offset=$newoffset\">$i</a> \n";
}
}

don't forget to add all the extra stuff to the url ($fileid, $numrows etc)

again I've not tested this but it should be ok

James

drucifer

ok,

the offset thing definately helped....but the next bit is provoking some strange results (as you can see by going to the previous url and trying the same thing.

i've replaced the last bit (to lose the un-needed 'next' link) but i lose the next button on page two as well as having a whole load of 'ones' at the bottom.

i'm not sure what to do with 'i would also on do with this bit
'$numrows=mysql_num_rows($numresults);
the first time and then pass the value (like with $file_id)

if (!$numrows) $numrows=mysql_num_rows($numresults); '

Is that where i'm going wrong?

jamfrag

probably yes

you need the numrows bit

replace the current line that sets $numrows with this one

if (!$numrows) $numrows=mysql_num_rows($numresults);

and add the

&numrows=$numrows bit to the urls (after the &fileid=$fileid bit)

drucifer

Ok, so this is what I have now....:

if ($id) {

$query = "SELECT * FROM content where id='$id' ";

$result = mysql_query ($query)
or die ("query 2 failed");

$row = mysql_fetch_array($result);

printf("<img src=\"images/img_%s.jpg\">\n ", $row["code"]);

}
//-----
else {
//-----

$numresults=mysql_query("SELECT * FROM content where id like '$form_id' ");
if (!$numrows) $numrows=mysql_num_rows($numresults);

if (empty($offset)) {
$offset=0;
}

if($fileid) $form_id = $fileid;

$query = "SELECT * FROM content where id like '$form_id' order by id limit $offset,$limit";

$result = mysql_query ($query)
or die ("query 2 failed");

$num_rows = mysql_num_rows($numresults);

echo " $num_rows Matching Results \n";

while ($row = mysql_fetch_array ($result))
{
for ($i = 0; $i < mysql_num_fields ($result); $i++)
{
if ($i > 0)
print (" ");

if ($i == 0){
	print "<b>Id: </b>";	
}
else if ($i == 1){
	print "<b>Article: </b>";
}
else if ($i == 2){
	print "<b>code: <a href=\"$PHP_SELF?id=$row[id]\"><img 
    src=\"images/thumb_$row[code].jpg\"></a></b>";
}
else{
	print "<b>Date:</b><br>";
}


print ($row[$i]);


}

print " <center><img src='black.gif' width=590 height=1 border=0></center>";
print ("");
}
if ($offset >= $limit) {
$prevoffset = $offset - $limit;
print "<a href=\"xxx.php?offset=$prevoffset&fileid=$form_id&numrows=$numrows\">PREV</a> \n";
}

$pages=intval($numrows/$limit);
if ($pages < ($numrows/$limit)){
$pages=($pages + 1);

}

for ($i = 1; $i <= $pages; $i++) {
$newoffset = $limit*($i-1);
if ($newoffset == $offset) {
print "$i \n";
} else {
print "<a href=\"xxx.php?offset=$newoffset&fileid=$form_id&numrows=$numrows\">$i</a> \n";
}
}

//show next if not last
for ($i = 1; $i < $numrows; $i+2) {
$newoffset = $i-1;
if ($newoffset == $offset) {
print "$i \n";
} else {
print "<a href=\"xxx.php?offset=$newoffset&fileid=$form_id&numrows=$numrows\">$i</a> \n";
}

}

with the if $numrow line near the top, but i'm still getting endless lnes of 'ones' on my results page....

(By the way, I'm really sorry to be takeing up so much of your time)

jamfrag

ok
replace this bit

for ($i = 1; $i <= $pages; $i++) {
$newoffset = $limit*($i-1);
if ($newoffset == $offset) {
print "$i \n";
} else {
print "<a href=\"xxx.php?offset=$newoffset&fileid=$form_id&numrows=$numrows\">$i</a> \n";
}
}

with this

for ($i = 1; $i < $numrows; $i+2) {
$newoffset = $i-1;
if ($newoffset == $offset) {
print "$i \n";
} else {
print "<a href=\"xxx.php?
offset=$newoffset&fileid=$form_id&numrows=$numrows\">$i</a> \n";
}
}

what does that do?

drucifer

still does the same thing...prints endless '1's with no 'next' link...

jamfrag

gotta go home mate

will have a think tonight

it will be sometihng to do with the loop and the the numrows

James

drucifer

That's what I was thinking...it's okay, i gotta split soon....thanks for your help today....are you in England by any chance (it being 'going home' time here...)

jamfrag

got it I think

it's a bit embarrassing really

make the for loop look like this

for ($i = 1; $i < $numrows; $i+=2) {

it was just looping infinitely on $i = 3 because I missed the = out of the $i increment

and yes I'm in Manchester

James

drucifer

sooo close. Ok, the recurring one thing is sorted but the next button is missing and i'm getting some duplicates of the page number links....

again, if you wanna look you can go to http://www.rhpinternet.com/search/new-form.php and choose 'all articles'....but it's definately close now...:-)

jamfrag

just to confirm, your final loop bit to do the next link looks like this

//show next if not least
if ($offset+$limit < $numrows) {
$newoffset = $offset+$limit;
print "<a href=\"xxx.php?offset=$newoffset&fileid=$form_id&numrows=$numrows\">NEXT</a>\n";
}

yes??

drucifer

i'm sorry, think I misunderstood something in the first message but have corrected it and it works, completely, all numbers and all prev/next links...

thank-you, thank-you, thank-you.

If you are ever in London, gimmes a shout so I can buy you a beer!

Thanx again for your time and patience....