Jump to content

PHP MySQL selection and pageing issues

kiksy

By kiksy
in PHP Coding Help

 Share

Recommended Posts

kiksy Newbie

kiksy

Posted
Posted

Hello.

 

Not sure if anyones going to be able to help here, im pretty stuck with it and everythings pretty messy. Just started out learning PHP and have been making good progress.

 

Ive been stuck now for a few days with these 2 problems :

 

Firstly,

I want to make a Search from a dropdown that then brings up results based on the choice from the dropdown

 

On my first page the code is :

 

<form action="search.php" method="post"/>
<input type="text" name="search" />
<select size="1" name="dropdown">
<option value="All" selected>Search All</option>
<option value="Users">Users</option>
<option value="Video">Videos</option>
<option value="Interactive">Interactive</option> 
<option value="Radio">Radio</option> 
<option value="Script">Script</option>  
</select>

<input name="submit" type="submit" class="submitcolour" />
</form>

 

This works fine.

 

On my search.php page the code is

 

<? 
if (isset($_POST['submit'])) {
if ($_POST['search'] != "") {


	$s = mysql_real_escape_string($_POST['search']);


	// $typequery = "SELECT * FROM gallery WHERE $dropdown='$search'" or die (mysql_error());
	//$result = mysql_query($typequery) or die (mysql_error()); 


										  // % adds wildcard.... // 
	$where = $_POST['dropdown'];
	if ($where == "Users") {
		$searchSQL = "SELECT * FROM users WHERE username LIKE '%$s%' OR bio LIKE '%$s%' OR email LIKE '%$s%' ORDER BY id ASC";


	} else if ($where == "All") {
		$searchSQL = "SELECT * FROM gallery WHERE title LIKE '%$s%' OR author LIKE '%$s%' OR description LIKE '%$s%'  ";



		} else if ($where == "Video") {
		$searchSQL = "SELECT * FROM gallery WHERE title LIKE '%$s%' OR author LIKE '%$s%' OR description LIKE '%$s%' AND type = 'Video' ORDER BY id ASC";


		} else if ($where == "Interactive") {
		$searchSQL = "SELECT * FROM gallery WHERE title LIKE '%$s%' OR author LIKE '%$s%' OR description LIKE '%$s%' AND type = 'Interactive' ORDER BY id ASC";
		}

		else if ($where == "Radio") {
		$searchSQL = "SELECT * FROM gallery WHERE title LIKE '%$s%' OR author LIKE '%$s%' OR description LIKE '%$s%' AND type = 'Radio' ORDER BY id ASC";
		}

		else if ($where == "Script") {
		$searchSQL = "SELECT * FROM gallery WHERE title LIKE '%$s%' OR author LIKE '%$s%' OR description LIKE '%$s%' AND type = 'Script' ORDER BY id ASC";
		}

	$result = mysql_query($searchSQL, $connect) or die(mysql_error());

	 $num = mysql_num_rows($result);

	// mysql_close($connect);
	 // echo "<div class=\"user \";




	if ($where == "Users") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Username: "   . $row['username'] .  "</p>";
			echo "<p> Bio : " . $row['bio'] . " </p>";
			echo "<hr />";
		} 
	} else if ($where == "Video") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Video Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}

		} else if ($where == "Interactive") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Interactive Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}

		} else if ($where == "Radio") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Radio Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}

		} else if ($where == "Script") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Script Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}

		} else if ($where == "All") { 



		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}



	} else {
		echo "<p> Please type something</p>";
	}

}
}

?>

 

Whatever option you choose , all results are shown from the database with that keyword, not just the ones based on 'type'. So say I have a video called "Video 1" which Type is 'Video' , if I search with "radio" from the dropdown, Video 1 still comes up in the search results.

 

 

My second problem is with the paging.

 

Ive got this to work fine on other pages , but not the search one .

 

The code for this on search.php is :

 

<?
session_start();
require("connection.php");





// pagination 
if (!(isset($_GET['page']))) {
$page = 1;
} else {
$page = $_GET['page'];
}

//secect everything from users table

$result = mysql_query("SELECT * FROM gallery ORDER BY id ASC");

// set limit for number of results on page
$rows = mysql_num_rows($result);
$limit = 2;
$last = ceil($rows/$limit);


if($page < 1) {
$page =1;
} elseif ($page > $last) {
$page = $last;
} 

$max = "LIMIT ". ($page -1) * $limit . "," .$limit ; 
$result = mysql_query("SELECT * FROM gallery $max ") or die();


?>

 

and

 

 <? echo "<p>you re  on  page " .$page. " of " .$last."</p>";
?></p>
<p>  </p>
<p></p>
<?
echo "<p><a href='search.php?page=".($page-1)."'>BACK</a>";
echo " - <a href='search.php?page=".($page+1)."'>FORWARD</a>";


?>

 

What happens is that it will show all of the results on one page (even though the limit is set to 2) and then have a number of blank pages afterwards. So say my search has 6 results, all the results will be on the one page instead of 2 ,and I will be on page 1 of 5. The other 4 pages will be blank.

 

There is obviously other code on the pages , ive just posted the bits that I think are causing the problems.

 

If anyone could help in anyway at all I would be most grateful. Even if its just down to helping me lay out my code in a more readable fashion .

 

Link to comment
Share on other sites
kiksy Newbie

kiksy

Posted
  • Author
Posted

Ok, so the search from the dropdown has been sort of fixed.

 

Changing the MySQL query so that there were no ANDs in it fixed it. Although that obviously limits my results a bit, I can live with it.

 

The paging though is still an issue . It shows the results on the first page, and then all other pages are blank.

 

Code on search.php now looks like:

 

<?
session_start();
require("connection.php");


// pagination 
if (!(isset($_GET['page']))) {
$page = 1;
} else {
$page = $_GET['page'];
}

//secect everything from users table
if (isset($_POST['search'])) {
$s = $_POST['search'];
$_SESSION['searchedfor'] = $s;
} else {
$s = $_SESSION['searchedfor'];
}

if (isset($_POST['dropdown'])) {
$where = $_POST['dropdown'];
$_SESSION['searchtype'] = $where;
} else {
$where = $_SESSION['searchtype'];
}

$result = mysql_query("SELECT * FROM gallery WHERE title LIKE '%$s%' AND type = '$where' ORDER BY id ASC") or die(mysql_error());
// set limit for number of results on page
$rows = mysql_num_rows($result);
$num = mysql_num_rows($result);

if ($rows > 0) {
$limit = 15;
$last = ceil($rows/$limit);


if($page < 1) {
$page =1;
} elseif ($page > $last) {
$page = $last;
} 

$max = "LIMIT ". ($page -1) * $limit . "," .$limit ;
echo  "searching for " . $s;
echo " - in " . $where;
echo $max;

// issue here
$result = mysql_query("SELECT * FROM gallery WHERE title LIKE '%$s%' AND type = '$where' ORDER BY id ASC $max ") or die(mysql_error());

}
?>

 

 

and further down

 

 <? 
if (isset($_POST['submit'])) {
if ($_POST['search'] != "") {



	$s = mysql_real_escape_string($_POST['search']);


if ($where == "Users") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Username: "   . $row['username'] .  "</p>";
			echo "<p> Bio : " . $row['bio'] . " </p>";
			echo "<hr />";
		} 
	} else if ($where == "Video") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Video Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}

		} else if ($where == "Interactive") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Interactive Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}

		} else if ($where == "Radio") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Radio Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}

		} else if ($where == "Script") { 
		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Script Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}

		} else if ($where == "All") { 



		echo "<h3> you serched for:" . $s . "..</h3>";
		echo "<h4> there were " . $num . " results </h4>";
		while ($row = mysql_fetch_assoc($result)) {
			echo "<p> Name: "   . $row['title'] .  "</p>";
			echo "<p> Made by: " . $row['author'] . " </p>";
			echo "<hr />";
		}



	} else {
		echo "<p> Please type something</p>";
	}

}
}

?>



<!-- pagniation start -->
  <? echo "<p>you re  on  page " .$page. " of " .$last."</p>";
?></p>
<p>  </p>
<p></p>
<?
echo "<p><a href='search.php?page=".($page-1)."'>BACK</a>";
echo " - <a href='search.php?page=".($page+1)."'>FORWARD</a>";


?>


<?
mysql_close($connect);
?>

 

 

Where I have commented '//issue here ' seems to be the problem area.

 

// issue here
$result = mysql_query("SELECT * FROM gallery WHERE title LIKE '%$s%' AND type = '$where' ORDER BY id ASC $max ") or die(mysql_error());

 

 

Anyone got any ideas? Thanks.

 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.