[Resolved] SQL Problem

furious5

As some of you will be aware I am finalising a PHP script to display paged records.

I am pretty much there, but am struggling with the last bit.

The way I have coded the page means that the SQL is built on the fly dependent upon the users search. My problem occurs when my search returns > 2 records.

Once I click on the link to view the next page of results the SQL is lost, and hence the results for the second page cannot be returned. To overcome this I placed the SQL in a session which initially seemed to work.

On closer inspection it appears the 2nd time the SQL is run it seems to return the first 2 records in the database no matter what search I carry out.

The code itself is fairly long maybe 300 lines, and it would be unfair to expect anyone to read all that code. The problem appears to be some where in this inital code.

Does anyone have any ideas how I can overcome this?


<?
// Include DB Variables
include("config.php");

// Include Functions
include("functions.php");

// Check Querystring for variables passed
$page = $_GET['page'];
$title = $HTTP_POST_VARS['title']; 
$sector = $HTTP_POST_VARS['sector']; 
$location = $HTTP_POST_VARS['location']; 

// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");

// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");

// set number of results to display per page
$pagelimit = "1";

// Check if $sql is in a session variable, if session is not null execute this $sql
// Else if $sql is null build SQL on the fly

if($_SESSION['sql']!=""){
	$sql = $_SESSION['sql'];
}	else {

/* Iterate through variables and build code on the fly */

$sql = "SELECT jobid, date, title, description, location, skills, industry, contactid FROM job WHERE";
$sql_where = "";

	if($title!= "") { 
			if($sql_where != "") { 
					$sql_where = $sql_where . "AND "; 
				} 
			$sql_where = $sql_where . " title = '" . $title . "' "; 
		} 

	if($sector!= "") { 
			if($sql_where != "") { 
					$sql_where = $sql_where . "AND "; 
				} 
			$sql_where = $sql_where . " industry = '" . $sector . "' "; 
		} 

	if($location!= "") { 
		if($sql_where != "") { 
					$sql_where = $sql_where . "AND "; 
				} 
			$sql_where = $sql_where . " location = '" . $location . "' "; 
		}

	$sql = $sql.$sql_where;

	}

// Run the SQL dynamically, by placing in the mysql_query field	

   $sql_result = mysql_query($sql) or die('SQL Error - died at SQLQuery1');

// count number of matches
	$totalrows = mysql_num_rows($sql_result);

// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
   $pagenums = ceil ($totalrows/$pagelimit);

// if no value for page, page = 1
	if ($page==''){
		$page='1';
	}
// create a start value
	$start = ($page-1) * $pagelimit;

// blank matches found
echo "<b class=general>" . $totalrows . " matches found</b><br>\n";

// Showing Results 1 to 1 (or if you're page limit were 5) 1 to 5, etc.
$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}

echo "<b class=general>Results $starting_no to $end_count shown.</b><br>";

werkkrew

I would say first of all, use a LIMIT in your SQL statement, Limiting the query to the # or results they chose, then, perhaps the best way for you to do it, would be to store the entire result in an array, and just display items 1-10 of the array on the first page, and generate the # of needed page links at the bottom using the size of that array, rather then running a new query on each page, it will probably help with consistency in the search as well...

Each page displayed, based on the # of results per page they want, would start where the previous page left off in the loop and end at a #, so and and so fourth...

I dont know if this is quite what you wanted.

If not, just be careful what variables are getting passed into the second page and make sure the query is running correctly on that page. If it runs ok on the first page, and not on the second, your SQL syntax is ok, its gotta be a problem with something getting passed into the query.

furious5

Full code code does actually use a limit.

The first query simply runs the query.
The second query does a calculation of how many pages are required based on the results and the page limit.
The third query returns all the info required from the table.

I'm loathed to post all the code as it is fairly long, but see what you make of it:

<?
	// Include DB Variables
	include("config.php");

// Include Functions
include("functions.php");

// Check Querystring for variables passed
$page = $_GET['page'];
$title = $HTTP_POST_VARS['title']; 
$sector = $HTTP_POST_VARS['sector']; 
$location = $HTTP_POST_VARS['location']; 

// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");

// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");

// set number of results to display per page
$pagelimit = "1";

// Check if $sql is in a session variable, if session is not null execute this $sql
// Else if $sql is null build SQL on the fly

if($_SESSION['sql']!=""){
	$sql = $_SESSION['sql'];
}	elseif {



/* Iterate through variables and build code on the fly */

$sql = "SELECT jobid, date, title, description, location, skills, industry, contactid FROM job WHERE";
$sql_where = "";

	if($title!= "") { 
			if($sql_where != "") { 
					$sql_where = $sql_where . "AND "; 
				} 
			$sql_where = $sql_where . " title = '" . $title . "' "; 
		} 

	if($sector!= "") { 
			if($sql_where != "") { 
					$sql_where = $sql_where . "AND "; 
				} 
			$sql_where = $sql_where . " industry = '" . $sector . "' "; 
		} 

	if($location!= "") { 
		if($sql_where != "") { 
					$sql_where = $sql_where . "AND "; 
				} 
			$sql_where = $sql_where . " location = '" . $location . "' "; 
		}

	$sql = $sql.$sql_where;

	}

// Run the SQL dynamically, by placing in the mysql_query field	

[b]	   $sql_result = mysql_query($sql) or die('SQL Error - died at SQLQuery1');[/b]

// count number of matches
	$totalrows = mysql_num_rows($sql_result);

// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
   $pagenums = ceil ($totalrows/$pagelimit);

// if no value for page, page = 1
	if ($page==''){
		$page='1';
	}
// create a start value
	$start = ($page-1) * $pagelimit;

// blank matches found
echo "<b class=general>" . $totalrows . " matches found</b><br>\n";

// Showing Results 1 to 1 (or if you're page limit were 5) 1 to 5, etc.
$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}

echo "<b class=general>Results $starting_no to $end_count shown.</b><br>";

// create dynamic next, previous, and page links

/* lets say you're set to show 5 results per page and your script comes out with 7 results.
this will allow your script to say next2 if you're on the first page and previous5 if you're on the second page. */

if ($totalrows - $end_count > $pagelimit) {
   $var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
   $var2 = $totalrows - $end_count;
}

$space = "&nbsp;";

// previous link (make sure to change yourpage.php to the name of your page)
if ($page>1) {
	echo "« <a href='result.php?page=".($page-1)."' class=general>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
	}

// dynamic page number links (make sure to change yourpage.php to the name of your page)

	for ($i=1; $i<=$pagenums; $i++) {
		if ($i!=$page) {
			echo " <a href='result.php?page=$i' class=general>$i</a>";
		}
		else {
			echo " <b class=general>$i</b>";
		}
	}


// next link (make sure to change yourpage.php to the name of your page)

	if ($page<$pagenums) {
		echo "" . $space . $space . $space . $space . " <a href='result.php?page=".($page+1)."' class=general>Next " . $var2 . "</a> »<p>";
	}

/* output your data wherever you'd like.

BUT

in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. This will limit how many results are actually displayed on the page. */

   // Add LIMIT to the end of the SQL  contained in $sql
   $limit_sql = " LIMIT ".$start.",".$pagelimit;

   // $applied_sql created to apend SQL query and LIMIT SQL 
   $applied_sql = $sql.$limit_sql;

  [b] $sql_result = mysql_query($applied_sql) or die('SQL Error - died at SQL Query2');   [/b]

// LIMIT 0,10 will start at 0 and display 10 results
// LIMIT 10,5 will start at 10 and display 5 results

/* now you can do whatever you'd like with this query. it will only output one page at a time. change the $pagelimit variable to whatever to output more than 1 result per page. */

[b]$sql_result = mysql_query($applied_sql) or die('SQL Error - died at SQL Query3');[/b]
$num_rows = mysql_num_rows($sql_result);


if ($num_rows == 0){
echo 'Sorry your search did not return any results. Please return to the search page <a href=search.php>here</a>';
} else {

while ($row = mysql_fetch_array($sql_result)){
	$jobid = $row["jobid"];
	$date= $row["date"];
	$title = $row["title"];
	$desc = $row["description"];
	$location = $row["location"];
	$salary = $row["salary"];
	$industry = $row["industry"];

	print "<table><tr><td width=100 class=general>Job ID:</td><td class=general>$jobid</td></tr>";
	print "<tr><td width=100 class=general>Date:</td>";
	print "<td class=general>";
	print fixDate($date); 
	print "</td>"; 
	print "</td></tr>";
	print "<tr><td width=100 class=general>Title:</td><td class=general>$title</td></tr>";
	print "<tr><td width=100 class=general>Description:</td><td class=general>$desc</td></tr>";
	print "<tr><td width=100 class=general>Location:</td><td class=general>$location</td></tr>";
	print "<tr><td width=100 class=general>Salary:</td><td class=general>$salary</td></tr>";
	print "<tr><td width=100 class=general>Industry:</td><td class=general>$industry</td></tr></table>";
	print "<p class=general>To apply please click <a href=result_details.php?jobid=$jobid>here</a>";
	print "<p>";
		} 
	}	
	// Page Links at the bottom of the page aswell

	echo "<b class=general>Results $starting_no to $end_count shown.</b><br>" ;

		if ($page>1) {
				echo "« <a href='result.php?page=".($page-1)."' class=general>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
			}

		// dynamic page number links (make sure to change yourpage.php to the name of your page)

			for ($i=1; $i<=$pagenums; $i++) {
				if ($i!=$page) {
					echo " <a href='result.php?page=$i' class=general>$i</a>";
				}
				else {
					echo " <b class=general>$i</b>";
				}
			}


		// next link (make sure to change yourpage.php to the name of your page)

			if ($page<$pagenums) {
				echo "" . $space . $space . $space . $space . " <a href='result.php?page=".($page+1)."' class=general>Next " . $var2 . "</a> »";
			}

		// Put $sql in a session variable, otherwise SQL Query1 will fall over
		session_register("sql");
		$_SESSION['sql'] = $sql;

		//echo $_SESSION['sql'];



?>

furious5

Anyone able to help me out? I am stumped 😕

werkkrew

I dont have the time to sift through all of the code right now...is there a place I can see the script at work and see the errors it produces, that will make it easier to fix

furious5

I know there is a lot of code. I am loathed to normally post it but I am really stumped.

The code is being ran locally on my machine so you won't be able to see it.

There are no errors being displayed. It is the results that are being returned that are the problem.

Since adding the Session variable in to the code no matter what search I do, I always get the first 2 records returned from the database. When for instance I know there is only 1 match to the search I have just carried out.

If I take the session variable out, the results are returned correctly but if there are more than 2 results i.e on 2 pages the first SQL statement falls over and cannot return the results for the second page.

furious5

Ok I've just printed the Session Variable and it seems the session is retaining the value from the last search and not being updated with the new search SQL.

Any ideas on how best to overcome this problem?

werkkrew

First of all, your Elseif here doesnt have a condition, you should just use ELSE...you never initialize the session using Session_start() at the top of the page, I dont think it ever sees the session as being registered (as it is) without this...so every time, the query is reset....

if($_SESSION['sql']!=""){
		$sql = $_SESSION['sql'];
	}	elseif {



/* Iterate through variables and build code on the fly */

$sql = "SELECT jobid, date, title, description, location, skills, industry, contactid FROM job WHERE";
$sql_where = "";

Also, unless im wrong here, the point at which $sql and $sql_where get joined, there is no space in between WHERE and AND...

$sql = "SELECT jobid, date, title, description, location, skills, industry, contactid FROM job WHERE";
	$sql_where = "";

	if($title!= "") { 
			if($sql_where != "") { 
					$sql_where = $sql_where . "AND "; 
				} 
			$sql_where = $sql_where . " title = '" . $title . "' "; 
		}

Just for simplicity, why not make all of the statements like this
$sql = $sql.$sql_where;

Look like this
$sql .= $sql_where;

You initialize $page as a string or char when you use the single quotes, just use a #, especially when you perform arithmatic later...

// if no value for page, page = 1
		if ($page==''){
			$page='1';
		}
	// create a start value
		$start = ($page-1) * $pagelimit;

Instead of using a while loop here, you might consider a foreach loop to iterate through the results array

while ($row = mysql_fetch_array($sql_result)){
		$jobid = $row["jobid"];
		$date= $row["date"];
		$title = $row["title"];
		$desc = $row["description"];
		$location = $row["location"];
		$salary = $row["salary"];
		$industry = $row["industry"];

Also, and im not exactly sure on this, but you register your session, and aside from overwriting the varible right away, you also dont have a call to the function session_start() which is required for the session vars to work right.

One last thing, I see a first SQL query, and a second one (labeled as the third one) but no query labeled as the second query...

I hope one or more of these things helps you

furious5

Hey werkkrew thanks for such a comprehensive response.

I have noted all your comments and will amend the aspects of the code you mentioned this evening - the code itself is still rough code and I haven't had the oppourtunity to pour over the code and knock everything in to shape (I've had way too many problems to cope with). But that certainly helps.

With regard to your 2 last points - if you look at the code above I have highlighted the 3 SQL queries in [bold hopefully now they should jump out at you.

As for the session, it doesn't look as though it is set because it isn't in the code I have posted, but appears at the top of the page followed by HTML. There is so much of this code I didn't think it neccesarry for people reading the thread to have to trawl through even more.

ROOT OF THE PROBLEM AT THE MOMENT:

As I say the problem is with the session variable itself. For instance if I go to the search page for the first time and search. The SQL generated at that first search is then written to the session and no matter what I search therefore after it automatically looks at the session variable because it is not null.

Would something like this work?

if($SESSION['sql']!=""){
$sql = $SESSION['sql'];
} else {

 // Unregister Session Variable
 session_unregister('sql'); 


/* Iterate through variables and build code on the fly */ 

$sql = "SELECT jobid, date, title, description, location, skills, industry, contactid FROM job WHERE"; 
$sql_where = "";

furious5

Erm quickly viewing the code that wouldn't work either.

Perhaps if the search page had a hidden field - when this variable was passed to the page with the code listed above it would then wipe the session?

Any help or advice on this would be greatly appreciated.

werkkrew

at the very top of your script put

session_start();

on the line below your <?php opening tag

then have your if statement

if ( isset($_SESSION["sql"] )
{
} else {
}

Which means that it will only execute the session stored sql query after the original one has run once

furious5

I've tested out the code as per your suggestion, and it seems to work.

One problem :rolleyes: , at the moment the session is kept until you execute the session stored sql query after the original one has run once i.e it needs to be ran twice until the session is cleared.

This works fine in principle for 2 pages, but if I return just 1 result i.e one page and then return to the search page I am still encountering the same problem - the session variable is kept.

So close but yet so far.

How about something like this:

if($title==""){
session_destroy;
} elseif ($location==""){
session_destroy;
} elseif ($industry==""){
session_destroy;
}

I think this would work, but I need a more compact way of coding this.

werkkrew

Ok,

so on each subsequent page

destroy the previous session before you create the new one...

furious5

I've ended up using this code, which seems to work well:

if($page!=""){
$sql = $_SESSION['sql'];
} elseif ($page=="") {
session_destroy;

Build SQL on the fly

}

Cheers for your efforts.