Jump to content

Searching a database, using %


DaveLinger

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.
Link to comment
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'
Link to comment
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...
Link to comment
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)
Link to comment
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());
Link to comment
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
Link to comment
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]
Link to comment
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
Link to comment
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
Link to comment
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
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.

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