Jump to content

Archived

This topic is now archived and is closed to further replies.

DaveLinger

Searching a database, using %

Recommended Posts

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
[code]
<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>
[/code] 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
[code]<?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');
?>[/code]

I'm a noob as far as SQL wildcards go, but the search is yielding a blank page.

Share this post


Link to post
Share on other sites
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'

Share this post


Link to post
Share on other sites
gah, I feel retarded for using GET instead of POST ;D

here's my new code, same result:

[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();

$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');
?>[/code]

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

edit again: added "$row = mysql_num_rows($result);" and same result...

Share this post


Link to post
Share on other sites
You might also find it easier to do it like:

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

Share this post


Link to post
Share on other sites
tried it. No luck... current code:

[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');
?>[/code]

(Thanks for your help so far Ginger, I must have been half asleep when I was writing this)

Share this post


Link to post
Share on other sites
Try taking out the mysql_close().

Ken

Share this post


Link to post
Share on other sites
same; blank page

edit: here's the example:

http://gamefreaks365.com/template72/search.php

Share this post


Link to post
Share on other sites
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());

Share this post


Link to post
Share on other sites
Add an "or die" clause to the mysql_query() function:
[code]<?php $result = mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());
?>[/code]
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:
[code]<?php
while ($row = mysql_fetch_assoc()
{
$artid = $row[artid];
$secid = $row[secid];
$title = $row[title];
$counter = $row[counter];
?>[/code]
You're missing the closing parenthesis on the while() statement and the indices should be surrounded by quotes:
[code]<?php
while ($row = mysql_fetch_assoc())
{
$artid = $row['artid'];
$secid = $row['secid'];
$title = $row['title'];
$counter = $row['counter'];
?>[/code]

Ken

Share this post


Link to post
Share on other sites
haha, same result!

[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) 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');
?>[/code]

Share this post


Link to post
Share on other sites
well if it's not echoing the header, then it wont echo the variable half a page of php code down, right?

Share this post


Link to post
Share on other sites
Put this line at the start of your script. It sounds like there is an error that is not getting displayed on the screen:
[code]<?php error_reporting(E_ALL); ?>[/code]

Ken

Share this post


Link to post
Share on other sites
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.
[code]<?php echo '<pre>' . print_r($_POST,true) . '</pre>'; ?>[/code]
Then change the script than generates the form to change where either the <form> tag is located or the </form> tag.

Ken

Share this post


Link to post
Share on other sites
I put that code at the top, still nothing. All of my other php pages work! What gives?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
GingerRobot: Yes. http://gamefreaks365.com/template72/includes/header.php

ken: The form tags have been outside of the table since I originally posted my code...

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
I tried changing gosearch.php to:

[code]<?php
echo $_POST['platform'];
?>[/code]

and it correctly echos % if I have Any selected or the corresponding id...

Share this post


Link to post
Share on other sites
Change it to
[code]<?php echo '<pre>' . print_r($_POST,true) . '</pre>'; ?>[/code]
and post the results.

Ken

Share this post


Link to post
Share on other sites
[code]
Array
(
    [platform] => %
    [fletter] => %
    [developer] => %
    [publisher] => %
    [sort] => score6
)[/code]

Share this post


Link to post
Share on other sites
Ok, now start adding the code you had back into the script, line by line, testing after each line. If the script works fine after all the lines have been added back, then there must have been an unseen character in the file that stopped the script from running properly.

Ken

Share this post


Link to post
Share on other sites

×

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.