Searching a database, using %

[DaveLinger]

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.

[GingerRobot]

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'

[DaveLinger]

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...

[GingerRobot]

You might also find it easier to do it like:

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

[DaveLinger]

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)

[kenrbnsn]

Try taking out the mysql_close().

Ken

[DaveLinger]

same; blank page

edit: here's the example:

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

[GingerRobot]

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

[kenrbnsn]

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

[DaveLinger]

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]

[GingerRobot]

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

[DaveLinger]

well if it's not echoing the header, then it wont echo the variable half a page of php code down, right?

[kenrbnsn]

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

[DaveLinger]

same result, blank page.

[kenrbnsn]

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

[DaveLinger]

I put that code at the top, still nothing. All of my other php pages work! What gives?

[GingerRobot]

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

[kenrbnsn]

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

[DaveLinger]

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...

[kenrbnsn]

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

[DaveLinger]

sorry X_X

any ideas anyone?

[DaveLinger]

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...

[kenrbnsn]

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

Ken

[DaveLinger]

[code]
Array
(
    [platform] => %
    [fletter] => %
    [developer] => %
    [publisher] => %
    [sort] => score6
)[/code]

[kenrbnsn]

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