Another newbie question
| From: | Matt Coyne | Date: | Mon, 26 Mar 2001 11:39:15 +0000 |
| Subject: | Another newbie question | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-7807@lists.php.net to get a copy of this message | ||
Hi guys/gals
Having had my head sorted out by this list already today, I am in need of
some more help...!!
I have an input form that, through php queries a MySQL db and passes results
back and the browser displays them (at this stage just as a list).
I have been looking into the LIMIT command to split my results into sections
(ie results 1-10 then 11-20 etc). No problem in understanding how LIMIT
works and I have LIMITed the first set to the first 10 results. I understand
that to show results 11-20 I would use LIMIT 10, 10 in my SQL query.
However, how am I to update the LIMIT n, 10 dynamically depending on what
split of results they are viewing. ie How do I recall the SQL query but with
an update LIMIT value
Here is what I have been doing:
// ASSEMBLE SQL QUERY
// make the select
$select = "SELECT ref, firstname, surname ";
//add the tablename to the query
$fromtable = "FROM $table_name";
//$fromtable .= "$tablename";
//build where clause
$control=1;
$where = " WHERE $control=1 ";
//determine if field sent value, build statement if true
if ($sex != '') {
$Qsex = "AND sex = '$sex' ";
} else {
$Qsex = "";
}
if ($heightFt != '') {
$QheightFt = "AND heightFt = '$heightFt' ";
} else {
$QheightFt = "";
}
if ($heightIn != '') {
$QheightIn = "AND heightIn LIKE '$heightIn%' ";
} else {
$QheightIn = "";
}
if ($neck != '') {
$Qneck = "AND neck LIKE '$neck%' ";
} else {
$Qneck = "";
}
if ($chest != '') {
$Qchest = "AND chest LIKE '$chest%' ";
} else {
$Qchest = "";
}
if ($waist != '') {
$Qwaist = "AND waist LIKE '$waist%' ";
} else {
$Qwaist = "";
}
if ($hips != '') {
$Qhips = "AND hips LIKE '$hips%' ";
} else {
$Qhips = "";
}
if ($leg != '') {
$Qleg = "AND leg LIKE '$leg%' ";
} else {
$Qleg = "";
}
if ($shoe != '') {
$Qshoe = "AND shoe LIKE '$shoe%' ";
} else {
$Qshoe = "";
}
if ($dress != '') {
$Qdress = "AND dress LIKE '$dress%' ";
} else {
$Qdress = "";
}
if ($hair != '') {
$Qhair = "AND hair LIKE '$hair%' ";
} else {
$Qhair = "";
}
if ($eyes != '') {
$Qeyes = "AND eye LIKE '$eyes%' ";
} else {
$Qeyes = "";
}
if ($skin != '') {
$Qskin = "AND skin LIKE '$skin%' ";
} else {
$Qskin = "";
}
if ($nationality != '') {
$Qnationality = "AND nationality LIKE '$nationality%' ";
} else {
$Qnationality = "";
}
// specify how results are ordered
$orderby = "ORDER BY surname ";
// offset is a hidden value in the initial form == 0
if ($offset = '0') {
$offset = 0;
}
// specify LIMIT
$limit = "LIMIT ";
$limit .= $offset;
$limit .= ", 10";
//build concatenated query
$concatsql = $select;
$concatsql .= $fromtable;
$concatsql .= $where;
$concatsql .= $Qsex;
$concatsql .= $QheightFt;
$concatsql .= $QheightIn;
$concatsql .= $Qneck;
$concatsql .= $Qchest;
$concatsql .= $Qwaist;
$concatsql .= $Qhips;
$concatsql .= $Qleg;
$concatsql .= $Qshoe;
$concatsql .= $Qdress;
$concatsql .= $Qhair;
$concatsql .= $Qeyes;
$concatsql .= $Qskin;
$concatsql .= $Qnationality;
$concatsql .= $orderby;
$concatsql .= $limit;
// make query
$result=@mysql_query($concatsql, $connection) or die (mysql_error() .
"<BR><B>from query:</B>$concatsql");
//detect amount of rows in result for display
$num_rows = mysql_num_rows($result);
//start building set of results to be echoed in browser
$contact_list= "<UL>";
while ($row = mysql_fetch_array($result)) {
$ref = $row['ref'];
$firstname = $row['firstname'];
$surname = $row['surname'];
$contact_list .="<LI><A HREF=\"show_record.php?ref=$ref\">$surname,
$firstname</A>";
}
$contact_list .="</UL>";
// add 10 to offset for next set of results
$offset +=10;
So, that all works for the first set of results but I do not know where to
go from here. I am assuming the last sectionis the way to go to increase the
offset value but I don't know what to do with it next!! I have been scouring
phpbuilder and devshed with no luck so far.
Any help very much appreciated
cheers
matt
t h r e e z e r o : : :
the mill, millstone lane, leicester, le1 5jn
e : m@threezero.co.uk :: m : 07747 845690
w : http://www.threezero.co.uk
: : : t h r e e z e r o