Help please pagination and results

Paul_Jackson32 · October 20, 2010

HI,

Please forgive my inexperience I am new to php and any help is most apreciated.

I have a database called liveproj and a table named olde_history.

Within the table, Project history is stored. Each project has a unique number ie 10383 etc.

I am trying to display the results for an individual project once the project has been selected.

The structure of the table is as such:

$sql = 'CREATE TABLE IF NOT EXISTS `liveproj`.`olde_history` (

`History_Id` int(6) NOT NULL AUTO_INCREMENT,

`History_Project_Id` varchar(10) NOT NULL,

`History_Department_Id` varchar(25) NOT NULL,

`History_By` varchar(25) DEFAULT NULL,

`History_Contact` varchar(25) DEFAULT NULL,

`History_Date` varchar(10) NOT NULL,

`History_Description` varchar(500) DEFAULT NULL,

`History_Action_Owner` varchar(25) DEFAULT NULL,

`History_Status_Id` varchar(10) DEFAULT NULL,

`History_Action_Date` varchar(10) DEFAULT NULL,

`History_Action_Description` varchar(500) DEFAULT NULL,

`History_Date_Resolved` varchar(10) DEFAULT NULL,

`History_Email` varchar(150) DEFAULT NULL,

`History_Minutes` varchar(150) DEFAULT NULL,

`History_Visit` varchar(150) DEFAULT NULL,

PRIMARY KEY (`History_Id`)

)';

A snippet of the table structure

I can View all information based on all projects and this works fine althoughh I only wish to display the relevent info for a selected project.

The error code I get is as follows:

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 '-10,10' at line 1

My code is:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Live Project List</title>
<link href="../css/Style.css" rel="stylesheet" type="text/css" />
<style type="text/css"></style>
</head>
<body id="body">

<div align="center" id="Section">Project History List
  <div id="apDiv1">
    <form id="form1" name="form1" method="post" action="">
      <input type="text" name="Search" id="Search" size="20" />
      <img src="../Images/search.png" width="24" height="24" align="middle" />
    </form>
  </div>
</div>
<table class="hovertable">
  <tr>
    <th width="10%">Date:</th>
    <th width="10%">By:</th>
    <th width="30%">History Description:</th>
    <th width="5%">Action Status:</th>
    <th width="5%">Action Owner:</th>
    <th width="10%">Action Rqd Date</th>
    <th width="10%">Action Description:</th>
    <th width="10%">Date Resolved:</th>
    <th width="5%">View:</th>
    <th width="5%">Edit:</th>
  </tr>

<?php
###############################################################################
# Start of pagination script							
###############################################################################

IF (ISSET($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} ELSE {
   $pageno = 1;
} // if

###############################################################################
# 2. Identify how many database rows are available							
############################################################################### 

# This code will count how many rows will satisfy the current query.

session_start();
include "../scripts/connect.php";
include "../scripts/Inc/db_lp.php";
//$disc = $_SESSION['Disc'];
$quote='"';
$projno2 = $_SESSION['Project'];
$projno = "$quote$projno2$quote";

$query = "SELECT count(*) FROM `liveproj` . `olde_history` WHERE `History_Id` = '$projno'";
//$query = "SELECT count(*) FROM `liveproj` . `olde_history` WHERE `History_Id` <> ''"; //WORKING LINE

$result = mysql_query($query) or die(mysql_error());
$query_data = MYSQL_FETCH_ROW($result);
$numrows = $query_data[0];

PRINT "NUMROWS: $numrows<br>";

###############################################################################
# 3. Calculate number of $lastpage							
############################################################################### 

# This code uses the values in $rows_per_page and $numrows in order to identify the number of the last page.

$rows_per_page = 10; ######## Set the ammount of rows you wish to display per page #########
$lastpage = CEIL($numrows/$rows_per_page);

###############################################################################
# 4. Ensure that $pageno is within range							
###############################################################################  

# This code checks that the value of $pageno is an integer between 1 and $lastpage.

$pageno = (int)$pageno;
IF ($pageno < 1) {
   $pageno = 1;
} ELSEIF ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if

###############################################################################
# 5. Construct LIMIT clause							
###############################################################################   

# This code will construct the LIMIT clause for the sql SELECT statement.

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

###############################################################################
# 6. Issue the database query							
###############################################################################  

# Now we can issue the database query and process the result.

$query = "SELECT * FROM `liveproj` . `olde_history` $limit";

$result = mysql_query($query) or die(mysql_error()); 

#... process contents of $result ...

     WHILE ($row = MYSQL_FETCH_ARRAY($result)) {

echo '<tr onmouseover="this.style.backgroundColor=\'#CCCCCC\';" onmouseout="this.style.backgroundColor=\'#ffffff\';"><td width="10%">';
echo $row["History_Date"]; 
echo "</td>";
echo '<td width="10%" >';
echo $row["History_By"];
echo "</td>";
echo '<td width="30%">';
echo "" . substr($row['History_Description'],0,20) . ".....[select View To Read More]" ; 
//echo $row["History_Description"];
echo "</td>";
echo '<td width="5%">'; 
echo $row["History_Status_Id"];
echo "</td>";
echo '<td width="5%">'; 
echo $row["History_Action_Owner"];
echo "</td>";
echo '<td width="10%">'; 
echo $row["History_Action_Date"];
echo "</td>";
echo '<td width="10%">'; 
//echo $row["History_Action_Description"]; 
echo "" . substr($row['History_Action_Description'],0,20) . ".....[select View To Read More]" ; 
echo "</td>";
echo '<td width="10%">';
echo $row["History_Date_Resolved"]; 
echo "</td>";
echo '<td width="5%">'; 
echo "<div class=form>"; 
echo '<form id="form" name="form" method="post" action="Doc_History_Desc.php" onSubmit="return confirm(';
echo '">';
echo '<input type="hidden" name="histid" value="';
echo $row["History_Id"];
echo '"><input type="image"SRC="../Images/Icons/List-View.png" Height="22" Width="22" value="View" alt="submit" class="hidebutton" name="B1">';
//echo '"><input type="submit" value="View" name="B1">';
echo "</form>";
echo "</td>";
echo '<td width="5%">'; 
echo "<div class=form>"; 
echo '<form id="form" name="form" method="post" action=".php" onSubmit="return confirm(';
echo '">';
echo '<input type="hidden" name="histid" value="';
echo $row["History_Id"];
echo '"><input type="image"SRC="../Images/Icons/List-Edit.png" Height="22" Width="22" value="View" alt="submit" class="hidebutton" name="B1">';
//echo '"><input type="submit" value="View" name="B1">';
echo "</form>";
echo "</td>";
echo "</tr>";

} 

?>

</table>
<p><br />
  <br />
</p>

<?php
###############################################################################
# 7. Construct pagination hyperlinks							
###############################################################################  

# Finally we must construct the hyperlinks which will allow the user to select 
# other pages. We will start with the links for any previous pages.

echo '<div align="center" id="SectionEnd">'; 
IF ($pageno == 1) {
   echo "<font size='1' color='#FFFFFF'>FIRST | PREV</font>";

} ELSE {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a>";

   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a>";
} // if


# Next we inform the user of his current position in the sequence of available pages.

echo " <font size='1' color='#FFFFFF'>( Page $pageno of $lastpage )</font> ";

# This code will provide the links for any following pages.

IF ($pageno == $lastpage) {
   echo "<font size='1' color='#FFFFFF'>NEXT | LAST</font>";
} ELSE {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a>";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a>";
} // if
echo'<br/>'; 
echo'<br/>'; 
echo'</div>'; 

###############################################################################
# End of pagination script							
###############################################################################
?>

</body>
</html>

Thankyou in advance.

Paul_Jackson32 · October 20, 2010

I think the problem may lie in this part of the code.

$quote='"';
$projno2 = $_SESSION['Project'];
$projno = "$quote$projno2$quote";
$query = "SELECT count(*) FROM `liveproj` . `olde_history` WHERE `History_Id` = '$projno'";

This line works although I dont want to display the history info from all projects.

$query = "SELECT count(*) FROM `liveproj` . `olde_history` WHERE `History_Id` <> ''";

rwwd · October 20, 2010

$query = "SELECT count(*) FROM `liveproj` . `olde_history` WHERE `History_Id` = '".$_SESSION['Project']."' ";

or

$query = "SELECT count(*) FROM `liveproj` . `olde_history` WHERE `History_Id` = ".$_SESSION['Project']." ";

use that if the contents of $_SESSION is an int, as you don't need to quote numerical values.

Hopefully I understood the question anyway!!

Rw

Paul_Jackson32 · October 21, 2010

Thanks for the reply rwwd,

I tried as you said but still didnt seem to work.

Sorry the first line was incorrect in my origional post. It should of been.

$projno = $_SESSION['Project'];

$query = "SELECT count (*) FROM `liveproj` . `olde_history` WHERE  `History_Project_Id`  = '$Projno";

I will try to explain what I am trying to achieve as in my previous post I was a little vague in my description.

But im guessing so far you understand what im trying to do.

I am trying to display the results for an individual project using session. so if I have 200 projects

I only want to show the history data for 1 project in pagination.

I have tried for hours and looked on the internet and am now pulling my hair out

The session data is passed though as i echo'd the sessions to screen.

BlueSkyIS · October 21, 2010

in my opinion, all values should be quoted in MySQL, numbers of any kind and strings. I prefer "always or never". if you do it ONE WAY ALWAYS, you don't have to worry about what should be or shouldn't be quoted. quotes works always, leaving them out does not work always.

anyway, this is the corrected query from your last post (missing a quote):

$query = "SELECT count (*) FROM `liveproj` . `olde_history` WHERE  `History_Project_Id`  = '$Projno'";

Paul_Jackson32 · October 21, 2010

Good point about the use of quates BlueSkyIS.

Unfortunatly still not working :'(

When I added the comma the error message presented on screen was

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 '*) FROM `liveproj` . `olde_history` WHERE `History_Project_Id` = ''' at line 1

Sign In

Help please pagination and results

Recommended Posts

Paul_Jackson32

Link to comment

Share on other sites

Paul_Jackson32

Link to comment

Share on other sites

rwwd

Link to comment

Share on other sites

Paul_Jackson32

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Paul_Jackson32

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information