Jump to content


Photo

Searching a database, using %


  • Please log in to reply
25 replies to this topic

#1 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 04:16 PM

so I have a database table with game reviews. The rows are for the title, first letter, review, platform, publish date, release date, hits, and scores. I'm working on a script to search through the database based on the user's input. Here's an example:

SEARCH.PHP
<form method="POST" action="gosearch.php">
	<p>&nbsp;</p>
	<table border="1" width="575" height="295">
		<tr>
			<td height="295" width="273" valign="top">Platform<p>Starts With 
			</p>
			<p>Developer</p>
			<p>Publisher</p>
			<p>Sort By </p>
			<p>&nbsp;</td>
			<td height="295" width="286" valign="top">
			<select size="1" name="platform">
<?php
include('includes/search/platforms.php');
?>
			</select><p><select size="1" name="fletter">
<option value="%">Any</option>
	<option>0-9</option>
	<option>A</option>
		<option>B</option>
			<option>C</option>
				<option>D</option>
					<option>E</option>
						<option>F</option>
							<option>G</option>
								<option>H</option>
									<option>I</option>
										<option>J</option>
											<option>K</option>
												<option>L</option>
													<option>M</option>
														<option>N</option>
															<option>O</option>
																<option>P</option>
																	<option>Q</option>
																		<option>R</option>
																			<option>S</option>
																				<option>T</option>
																					<option>U</option>
																						<option>V</option>
																							<option>W</option>
																								<option>X</option>
																									<option>Y</option>
																										<option>Z</option>
	</select></p>
			<p><select size="1" name="developer">
<?php
include('includes/search/developers.php');
?>
			</select></p>
			<p><select size="1" name="publisher">
<?php
include('includes/search/publishers.php');
?>
			</select></p>
			<p><select size="1" name="sort">
			<option value="score6">Final Score</option>
			<option value="releasedate">Release Date</option>
			<option value="publishdate">Publish Date</option>
			<option value="counter">Number of Hits</option>
			<option value="score1">Graphics Score</option>
			<option value="score2">Sound Score</option>
			<option value="score3">Gameplay Score</option>
			<option value="score4">Creativity Score</option>
			<option value="score5">Length Score</option>
			</select></td>
		</tr>
	</table>
	<p><input type="submit" value="Search"></p>
</form>
The "includes" for developer, publisher, and platform just have <option>value</option> listed in them, so that shouldnt be a problem. * The value of "any" for every field is just %. *


GOSEARCH.PHP
<?php
include('config/file.php');

$platform = $_GET['platform'];
$developer = $_GET['developer'];
$publisher = $_GET['publisher'];
$fletter = $_GET['fletter'];
$sortby = $_GET['sort'];

if (!$link = mysql_connect($sqlserver, $sqlusername, $sqlpassword)) {
   echo 'Could not connect to mysql';
   exit;
}

if (!mysql_select_db($sqldatabase, $link)) {
   echo 'Could not select database';
   exit;
}

$query="SELECT * FROM nuke_seccont WHERE secid='$platform' AND developer='$developer' AND publisher='$publisher' AND fletter='$fletter' ORDER BY $sortby DESC";
$result = mysql_query($query);

mysql_close();

$i=0;
while ($i < $row) {

$artid=mysql_result($result,$i,"artid");
$secid=mysql_result($result,$i,"secid");
$title=mysql_result($result,$i,"title");
$content=mysql_result($result,$i,"content");
$counter=mysql_result($result,$i,"counter");

echo "<a href=\"review.php?artid=$artid\"><b>$title</b></a>";

$i++;
}

include('includes/footer.php');
?>

I'm a noob as far as SQL wildcards go, but the search is yielding a blank page.
http://www.DaveLinger.com
dave at linger dot com

#2 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 11 July 2006 - 04:44 PM

Im not sure if this is the only problem, but the form action is POST, and you are trying to retrieve the variables using GET.

Also, if you are intending on using wildcard searches with %, you will need to use LIKE instead of = in your query e.g.: developer like '$developer'


#3 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 04:57 PM

gah, I feel retarded for using GET instead of POST ;D

here's my new code, same result:

<?php
include('config/file.php');
include('includes/header.php'):

$platform = $_POST['platform'];
$developer = $_POST['developer'];
$publisher = $_POST['publisher'];
$fletter = $_POST['fletter'];
$sortby = $_POST['sort'];

if (!$link = mysql_connect($sqlserver, $sqlusername, $sqlpassword)) {
   echo 'Could not connect to mysql';
   exit;
}

if (!mysql_select_db($sqldatabase, $link)) {
   echo 'Could not select database';
   exit;
}

$query="SELECT * FROM nuke_seccont WHERE secid like '$platform' AND developer like '$developer' AND publisher like '$publisher' AND fletter like '$fletter' ORDER BY $sortby DESC";
$result = mysql_query($query);

mysql_close();

$i=0;
while ($i < $row) {

$artid=mysql_result($result,$i,"artid");
$secid=mysql_result($result,$i,"secid");
$title=mysql_result($result,$i,"title");
$content=mysql_result($result,$i,"content");
$counter=mysql_result($result,$i,"counter");

echo "<a href=\"review.php?artid=$artid\"><b>$title</b></a>";

$i++;
}

include('includes/footer.php');
?>

edit: Just realized I dont have $row defined...

edit again: added "$row = mysql_num_rows($result);" and same result...
http://www.DaveLinger.com
dave at linger dot com

#4 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 11 July 2006 - 05:02 PM

You might also find it easier to do it like:

while ($row = mysql_fetch_assoc()
{
$artid = $row[artid];
etc
}

#5 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 05:09 PM

tried it. No luck... current code:

<?php
include('config/file.php');
include('includes/header.php'):

$platform = $_POST['platform'];
$developer = $_POST['developer'];
$publisher = $_POST['publisher'];
$fletter = $_POST['fletter'];
$sortby = $_POST['sort'];

if (!$link = mysql_connect($sqlserver, $sqlusername, $sqlpassword)) {
   echo 'Could not connect to mysql';
   exit;
}

if (!mysql_select_db($sqldatabase, $link)) {
   echo 'Could not select database';
   exit;
}

$query="SELECT * FROM nuke_seccont WHERE secid like '$platform' AND developer like '$developer' AND publisher like '$publisher' AND fletter like '$fletter' ORDER BY $sortby DESC";
$result = mysql_query($query);

mysql_close();

while ($row = mysql_fetch_assoc()
{
$artid = $row[artid];
$secid = $row[secid];
$title = $row[title];
$counter = $row[counter];

echo "<a href=\"review.php?artid=$artid\"><b>$title</b></a>";
}

include('includes/footer.php');
?>

(Thanks for your help so far Ginger, I must have been half asleep when I was writing this)
http://www.DaveLinger.com
dave at linger dot com

#6 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 11 July 2006 - 05:11 PM

Try taking out the mysql_close().

Ken

#7 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 05:15 PM

same; blank page

edit: here's the example:

http://gamefreaks365...te72/search.php
http://www.DaveLinger.com
dave at linger dot com

#8 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 11 July 2006 - 05:19 PM

Umm, try echoing the query and adding an or die statement?

$query="SELECT * FROM nuke_seccont WHERE secid like '$platform' AND developer like '$developer' AND publisher like '$publisher' AND fletter like '$fletter' ORDER BY $sortby DESC";
echo $query;
$result = mysql_query($query) or die(mysql_error());

#9 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 11 July 2006 - 05:24 PM

Add an "or die" clause to the mysql_query() function:
<?php $result = mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());
?>
Put in some debuging echo statement to see what is being returned to your script. You may see something that isn't working the way you think it is.

In this chuck of code:
<?php
while ($row = mysql_fetch_assoc()
{
$artid = $row[artid];
$secid = $row[secid];
$title = $row[title];
$counter = $row[counter];
?>
You're missing the closing parenthesis on the while() statement and the indices should be surrounded by quotes:
<?php
while ($row = mysql_fetch_assoc())
{
$artid = $row['artid'];
$secid = $row['secid'];
$title = $row['title'];
$counter = $row['counter'];
?>

Ken

#10 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 05:26 PM

haha, same result!

<?php
include('config/file.php');
include('includes/header.php'):

$platform = $_POST['platform'];
$developer = $_POST['developer'];
$publisher = $_POST['publisher'];
$fletter = $_POST['fletter'];
$sortby = $_POST['sort'];

if (!$link = mysql_connect($sqlserver, $sqlusername, $sqlpassword)) {
   echo 'Could not connect to mysql';
   exit;
}

if (!mysql_select_db($sqldatabase, $link)) {
   echo 'Could not select database';
   exit;
}

$query="SELECT * FROM nuke_seccont WHERE secid like '$platform' AND developer like '$developer' AND publisher like '$publisher' AND fletter like '$fletter' ORDER BY $sortby DESC";
$result = mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());

while ($row = mysql_fetch_assoc())
{
$artid = $row['artid'];
$secid = $row['secid'];
$title = $row['title'];
$counter = $row['counter'];

echo "<a href=\"review.php?artid=$artid\"><b>$title</b></a>";
}

include('includes/footer.php');
?>

http://www.DaveLinger.com
dave at linger dot com

#11 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 11 July 2006 - 05:51 PM

Can you echo the $query variable, to double check what is being passed to it?

#12 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 05:53 PM

well if it's not echoing the header, then it wont echo the variable half a page of php code down, right?
http://www.DaveLinger.com
dave at linger dot com

#13 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 11 July 2006 - 05:57 PM

Put this line at the start of your script. It sounds like there is an error that is not getting displayed on the screen:
<?php error_reporting(E_ALL); ?>

Ken

#14 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 06:01 PM

same result, blank page.
http://www.DaveLinger.com
dave at linger dot com

#15 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 11 July 2006 - 06:12 PM

This is a wild stab in the dark ....

I took a look at the HTML source of the page where the search originates. The start tag of the search form is before the start of a table, then end tag is before the table is closed. This is not allowed in the HTML spec.

Before you change your source script that generates the table, put this line at the top of your search script, if nothing is displayed, it will prove that your search script isn't being invoked.
<?php echo '<pre>' . print_r($_POST,true) . '</pre>'; ?>
Then change the script than generates the form to change where either the <form> tag is located or the </form> tag.

Ken

#16 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 06:16 PM

I put that code at the top, still nothing. All of my other php pages work! What gives?
http://www.DaveLinger.com
dave at linger dot com

#17 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 11 July 2006 - 06:18 PM

If you go straight to the header.php file, does that output something?

#18 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 11 July 2006 - 06:18 PM

Now change the script that generates the form to put the open and closing tags either both inside the table or both outside the table.

Ken

#19 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 11 July 2006 - 06:30 PM

GingerRobot: Yes. http://gamefreaks365...udes/header.php

ken: The form tags have been outside of the table since I originally posted my code...
http://www.DaveLinger.com
dave at linger dot com

#20 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 11 July 2006 - 06:39 PM

My mistake. The weird indenting of your code threw my eye off.

I just ran the source code through the w3C HTML validator <http://validator.w3.org/> and got 87 errors. Some of these errors might affect how HTML is processed.

Ken




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users