Search function issues

[cbear2021]

Hi,

 

I'm trying to implement a search function on my website.  The user is able to search for pitches (like film script pitches) through a few drop down lists and text boxes.  They're able to search by user, by genre, by resources required, the pitch name, and the body of the pitch.  I've done all the php for the forms, and they work fine and get the details of each from the database (the users, genre's, etc).  What I'm having trouble with is the actual php code to do the search.  I've created it following a texbook example, and modified it to suit my needs.  So basically it has the initial select statement, and then additionial selects are concatinated onto the original select, depending on if the user selects a genre, resource etc.  I want it so that if a user just selects a genre for example, and leaves everything else blank it'll bring up everything from that genre.

 

However, I'm getting sql syntax errors where it doesn't like my LIKE statments. I'm a little bit new to joins/likes, so i'm not sure exactly where my syntax error is. 

 

The errors I'm getting are:

 

error getting pitches from the database

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1=1 AND pitchid=pitchid AND genreid='2' AND pitchid=pitchid AND resourceid='3' A' at line 1

 

 

This is with all the fields filled in, a user, genre, resource, pitch name, and pitch text filled in.  The record for the pitch exists in the database.

 

All my databases are linked via primary/foriegn keys where neccessary, I'll post my php code/sql code below.  Any help on this would be much appreciated, as this is pretty much the last thing I have to do on this website and i'd like to understand where i'm going wrong so I can fix it.  Thanks

 

 

 

search.php (this works fine) :

 

<?php include 'dbconnect.php' ?>



<!-- Search form for pitches -->

<form name="pitchsearch" action="pitchsearch.php" method="post">

<p> Search for jokes using the criteria below: </p>

<label> By author name:

<select name="userid" size="1">

<option selected value=""> Any User </option>
<?php

while ($user = mysql_fetch_array($users)) {

$userid = $users['userid'];
$username = htmlspecialchars($user['firstname']);
echo "<option value='$userid'>$username</option>\n";

}

?>
</select></label><br />

<label> By Genre:

<select name="genre" size="1">

<option selected value=""> Any Genre </option>

<?php

// While the var gcats is being assigned the array details from the genre table
while($gcats = mysql_fetch_array($gcat)) {

//assign the var gc_id the array values of genre_id 
$gcid = $gcats['genreid'];
$gcname = htmlspecialchars($gcats['genrename']);
echo"<option value='$gcid'> $gcname </option>\n";

}
?>

</select></label><br />



<label> By Resource Required:

<select name="resource" size="1">

<option selected value=""> Any Resource </option>

<?php

// While the var gcats is being assigned the array details from the genre table
while($rcats = mysql_fetch_array($rcat)) {

//assign the var gc_id the array values of genre_id 
$rcid = $rcats['resourceid'];
$rcname = htmlspecialchars($rcats['resourcename']);
echo"<option value='$rcid'> $rcname </option>\n";

}
?>

</select></label><br />


<label> Pitch name: <input type="text" name="searchpitchname" /> </label> <br />

<label> Pitch keywords? For example, if you want stuff with zombies put that into the box 

<input type="text" name="searchpitch" />  

</label>
<br />

<input type="submit" value="search!" />

</form>

 

 

 

backend for the search

 

pitchsearch.php:

 

<?php 

include 'dbconnect.php';



//select statement

$select = 'SELECT DISTINCT pitchid, pitchname, pitch';  //get the pitch id, pitchname and pitch details
$from   = 'FROM pitch';								// from the pitch table
$where  = 'WHERE 1=1';									


$uid = $_POST['userid'];
if ($uid !='') { // a user has been selected

$where .= " AND userid='$uid' ";

}


$gid = $_POST['genre'];

if ($gid !='') {  // a genre has been selected

	$from  .= ', genrecategory';
	$where .= " AND pitchid=pitchid AND genreid='$gid'";

}


$rid = $_POST['resource'];

if ($rid !='') { // a resource has been selected

    $from  .= ', resourcecategory';
	$where .= " AND pitchid=pitchid AND resourceid='$rid'";

	}


$searchpitchname = $_POST['searchpitchname'];

if($searchpitchname !='') { //a pitch name was entered

$where .= " AND pitchname LIKE '%searchpitchname%'";

}


$searchpitch = $_POST['searchpitch'];

if($searchpitch !='') { //something was entered in the pitch 

$where .= " AND pitch LIKE '%searchpitch%'";

}

?>


<table>

<tr><th>Pitch Name</th><th>Pitch</th><th>Submitted by</th></tr>


<?php


$pitches = mysql_query($select . $from . $where);

if(!$pitches) {

echo '</table>';
echo '<p>error getting pitches from the database<br />' . 'Error'. mysql_error() . '</p>';
echo "<p> $pitches </p>";

}

while ($pitch = mysql_fetch_array ($pitches)) {

echo "<tr valign = 'top'>\n";
$id = $pitch['pitchid'];
$pitchname = htmlspecialchars($pitch['pitchname']);
$pitchtext = htmlspecialchars($pitch['pitch']);
$userid    = ($pitch['userid']);
echo "<td> $pitchname</td>\n";
echo "<td> $pitchtext</td>\n";
echo "<td> $userid</td>\n";

}

?>


</table>

 

 

and the sql tables

 

users table:

 

CREATE TABLE IF NOT EXISTS `users` (
  `userid` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `password` varchar(32) NOT NULL,
  `location` varchar(50) NOT NULL,
  `age` mediumint(2) NOT NULL,
  `gender` varchar(12) NOT NULL,
  PRIMARY KEY  (`userid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='table of users' AUTO_INCREMENT=3 ;

 

pitch table

 

CREATE TABLE IF NOT EXISTS `pitch` (
  `pitchid` int(11) NOT NULL auto_increment,
  `pitchname` varchar(255) NOT NULL,
  `pitch` longtext NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY  (`pitchid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='table for pitches' AUTO_INCREMENT=2 ;

ALTER TABLE `pitch`
  ADD CONSTRAINT `pitch_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE;

 

genrecategoty table:

CREATE TABLE IF NOT EXISTS `genrecategory` (
  `pitchid` int(11) NOT NULL,
  `genreid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`pitchid`,`genreid`),
  KEY `genreid` (`genreid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='genre lookup table';

ALTER TABLE `genrecategory`
  ADD CONSTRAINT `genrecategory_ibfk_4` FOREIGN KEY (`genreid`) REFERENCES `genre` (`genreid`) ON DELETE NO ACTION,
  ADD CONSTRAINT `genrecategory_ibfk_3` FOREIGN KEY (`pitchid`) REFERENCES `pitch` (`pitchid`) ON DELETE CASCADE;

 

 

resource category table:

 

CREATE TABLE IF NOT EXISTS `resourcecategory` (
  `pitchid` int(11) NOT NULL,
  `resourceid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`pitchid`,`resourceid`),
  KEY `resourceid` (`resourceid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='resource lookup table';

ALTER TABLE `resourcecategory`
  ADD CONSTRAINT `resourcecategory_ibfk_4` FOREIGN KEY (`resourceid`) REFERENCES `resources` (`resourceid`),
  ADD CONSTRAINT `resourcecategory_ibfk_3` FOREIGN KEY (`pitchid`) REFERENCES `pitch` (`pitchid`) ON DELETE CASCADE;

 

 

genre table:

 

CREATE TABLE IF NOT EXISTS `genre` (
  `genreid` int(11) NOT NULL auto_increment,
  `genrename` varchar(30) NOT NULL,
  PRIMARY KEY  (`genreid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='genre table' AUTO_INCREMENT=13 ;

 

resources table:

 

CREATE TABLE IF NOT EXISTS `resources` (
  `resourceid` int(11) NOT NULL auto_increment,
  `resourcename` varchar(30) NOT NULL,
  PRIMARY KEY  (`resourceid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='resource table' AUTO_INCREMENT=8 ;

 

 

[alexweber15]

thats a lot to go through, would really help me if you posted a screenshot of the search form so i could understand how all the different options fit in together

 

and just by taking a glance at it this comes up kinda often:

WHERE pitchid=pitchid

you should change that to:

pitchid='$pitchid'

 

might do the trick

[cbear2021]

Yeah - I thought that too - but I don't have a variable defined as $pitchid - the genrecategory/resourcecategory tables are linked to the pitch table via the pitchid column, as primary/foriegn keys, but i'm a bit confused as to how it would to look the pitch id, so i've tried modifying it to

 

WHERE pitchid = pitch.pitchid

but it didn't like that either.  I'm gonna try and upload a screenshot of the search form. 

[cbear2021]

search form screen shot - under author name/genre/resource, when you click them it has a list of all of the available options currently stored in the tables.  so all the current registered users, the available genre's etc. 

 

[gffg4574fghsDSGDGKJYM]

Adding this in pitchsearch.php would probably help a lot :

 

...
$pitches = mysql_query($select . $from . $where);
if (!$pitches)
{
   echo '</table>';
   echo '<p>SQL : '.$select . $from . $where.'</p>';
   echo '<p>error getting pitches from the database<br />' . 'Error'. mysql_error() . '</p>';
   echo "<p> $pitches </p>";
}
...

 

So we can see the exact SQL query that raise that error. Just don't forget to comment it when you're done debugging.

[cbear2021]

This is the output of the echo statement:

 

SQL : SELECT DISTINCT *FROM pitch, genrecategory, resourcecategoryWHERE 1=1 AND pitchid=pitchid AND genreid='6' AND pitchid=pitchid AND resourceid='4' AND pitchname LIKE '%searchpitchname%' AND pitch LIKE '%searchpitch%'

 

and the mysql error:

 

error getting pitches from the database

Error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1=1 AND pitchid=pitchid AND genreid='6' AND pitchid=pitchid AND resourceid='4' ' at line 1

 

I changed the values in the SELECT part of the statement to * because I wasn't sure if it was actually getting anything from the resourcecategory and genrecategory, as it wasn't selecting anything from them in the original statement

[iarp]

Remove the "AND pitchid=pitchid " clause (both of them) and try it again.

 

Also:

<?php
$searchpitchname = $_POST['searchpitchname'];

if($searchpitchname !='') { //a pitch name was entered

$where .= " AND pitchname LIKE '%" . $searchpitchname . "%'";

}


$searchpitch = $_POST['searchpitch'];

if($searchpitch !='') { //something was entered in the pitch 

$where .= " AND pitch LIKE '%" . $searchpitch . "%'";

}
?>

[cbear2021]

and by the sql statement, I modified it so that it actually puts spaces between the conditions, which is one reason why it'd be failing (i know it's a schoolboy error, but it's 4am) and this is the latest error i'm getting

 

 

SQL : SELECT DISTINCT * FROM pitch, genrecategory, resourcecategory WHERE 1=1 AND pitchid=pitchid AND genreid='5' AND pitchid=pitchid AND resourceid='5' AND pitchname LIKE '%searchpitchname%' AND pitch LIKE '%searchpitch%'

 

error getting pitches from the database

ErrorColumn 'pitchid' in where clause is ambiguous

[cbear2021]

I removed the pitchid=pitchid - it worked, but only when selecting via user/genre/resource, not when typing in information into the pitch name, or the pitch field.

:-\

[iarp]

Look at my last reply, the code section has that fix.

[cbear2021]

Thanks, it now works! Do you mind if i pm you and ask why? I don't just want to use your fix without understanding what was going on, or i'll never learn from the mistake.

[iarp]

I don't know the technical words for it. But basically you original statement was

<?php $where .= " AND pitch LIKE '%searchpitch%'"; ?>

and that searchs for whatevers between the % signs.

 

The new one is:

<?php $where .= " AND pitch LIKE '%" . $searchpitch . "%'"; ?>

Adding the first " breaks the line and . concatenates the next item which was $searchpitch which you defined earlier another . concatenates again and then " reopens the string to finish it.

[cbear2021]

Thanks, makese sense.  I guess working on it at stupid o'clock makes you miss really obvious things.  I only have one more question - I want to modify this so I can display the users name and link that to their email (so when you click on their name you can email them) because at the moment it displays their userid under submitted by, which isn't much use.  But every time I try to include the users table in the from part of the query, it breaks and displays every user as having submitted each project.  Can anyone give me any pointers as to how I should go about that?

 

 

Thanks so much for all your help so far 

[iarp]

Try this change....

 <?php 
while ($pitch = mysql_fetch_array ($pitches)) {

echo "<tr valign = 'top'>\n";
$id = $pitch['pitchid'];
$pitchname = htmlspecialchars($pitch['pitchname']);
$pitchtext = htmlspecialchars($pitch['pitch']);
$userid    = ($pitch['userid']);
echo "<td> $pitchname</td>\n";
echo "<td> $pitchtext</td>\n";
echo "<td> $userid</td>\n";

}
?>

 

to

 

<?php

while ($pitch = mysql_fetch_array ($pitches)) {

echo "<tr valign = 'top'>\n";
$id = $pitch['pitchid'];
$pitchname = htmlspecialchars($pitch['pitchname']);
$pitchtext = htmlspecialchars($pitch['pitch']);
$userid    = ($pitch['userid']);
echo "<td> $pitchname</td>\n";
echo "<td> $pitchtext</td>\n";
echo "<td>"

$q1 = "SELECT firstname, email FROM users WHERE userid = '" . $userid . "'";
$r1 = mysql_query($q1);
$row1 = mysql_fetch_array($r1, MYSQL_NUM);
echo "<a href=\"mailto:" . $row1['1'] . "\">" . $row1['0'] . "</a>";

echo "</td>\n";

}
?>

 

Because i cant test it, there might an be error, sometimes i get confused with quotes and blackslashes and crap.

[iarp]

<?php

while ($pitch = mysql_fetch_array ($pitches)) {

echo "<tr valign = 'top'>\n";
$id = $pitch['pitchid'];
$pitchname = htmlspecialchars($pitch['pitchname']);
$pitchtext = htmlspecialchars($pitch['pitch']);
$userid = ($pitch['userid']);      // Removed &#160; &#160;
echo "<td> $pitchname</td>\n";
echo "<td> $pitchtext</td>\n";
echo "<td>"

$q1 = "SELECT firstname, email FROM users WHERE userid = '" . $userid . "'";
$r1 = mysql_query($q1);
$row1 = mysql_fetch_array($r1, MYSQL_NUM);
echo "<a href=\"mailto:" . $row1['1'] . "\">" . $row1['0'] . "</a>";

echo "</td>\n";

}
?>

[gizmola]

I just want you to know that every place you have LIKE %something% your database will not be able to use an index.  MySQL does have a solution for this -- the fulltext index.  You might want to do some reading on it. 

 

Otherwise, as time goes on, your searches will begin to crush your mysql server, and eventually (assuming your database starts to fill up with pitches) performance will become worse and worse.

[cbear2021]

Thanks for the heads up, I will look into it