Page 2 of 2
Posted: Thu Aug 25, 2005 8:51 am
by sleazyfrank
I do not know what I am doing wrong - I am now using the former sql statement as suggested - and I know my table contains data that would match my query - but I am getting no results back. This is my first php query of a mysql db using a variable in the WHERE clause and it's driving me around the bend trying to figure out where I am going wrong
Is there any way of testing that data is being returned from my db?
In this code:
Code: Select all
$result_unix = mysql_query($query_unix);
$num_unix = mysql_num_rows($result_unix);
num_unix always equates to null, ie in this IF
Code: Select all
if ($num_unix==null) { echo "<span class='storybodytext'>Sorry, your search returned no matching courses, please <a href='searchcourses.php' class='storyBodyTextLink'>click here</a> to return.</span>";
}
many thanks for any guidance
frank
Posted: Thu Aug 25, 2005 8:56 am
by feyd
Have you tried running the finalized query in phpMyAdmin or mysql command line?
Code: Select all
SELECT * FROM coursesUnixLinux WHERE CourseTitle LIKE '%unix%'
Adding some debugging parts like
Code: Select all
$result_unix = mysql_query($query_unix) or die(mysql_error());
may show something too.
Posted: Thu Aug 25, 2005 10:02 am
by sleazyfrank
Hi - I added your debugging suggestion and I still get:
keywordsearch = unix
unix query = SELECT * FROM coursesUnixLinux WHERE CourseTitle LIKE '%unix%'
result_unix =
Sorry, your search returned no matching courses, please click here to return.
which I assume means there was no db error, just that really no matches are being made, so nothing is wrong here.
I've tried the query in the mysql admin interface that comes with Plus.Net, our ISP. Slightly bewildering for the noobie. But I found a popup for entering your own sql and put it in. The query result is:
Your SQL-query has been executed successfully (Query took 0.0011 sec)
SELECT *
FROM coursesUnixLinux
WHERE CourseTitle
LIKE '%unix%' LIMIT 0 , 30
No result shows or anything. So the sql is correct but does that mean no matches have been made??
Interesting; I've just done a search using the Search tab using the term %unix% to search on and got back the following result:
Search results for "%unix%" (at least one of the words):
0 match(es) inside table coursesUnixLinux
which is mad because my table has four records with Unix somewhere in the CourseTitle field!
I just ran the sql again with instead of select *, I did 'UnixLinux'.'CourseTitle' and this came back:
Error
SQL-query :
SELECT *
FROM `coursesUnixLinux`.`CourseTitle`
WHERE CourseTitle
LIKE 'unix'
LIMIT 0 , 30
MySQL said:
#1142 - select command denied to user: 'removed user details for security' for table 'CourseTitle'
help?
frank
Posted: Thu Aug 25, 2005 10:17 am
by feyd
could you post an export of the table structure? Something sounds fishy...
It's also possible that there is extra data stored in the text, like it's encoded differently than you think. To be sure, try something like this to show the exact information inside the fields
Code: Select all
$query = mysql_query('SELECT * FROM coursesUnixLinux') or die(mysql_error());
$first = true;
echo '<table>';
while($row = mysql_fetch_row($query))
{
if($first)
{
$fields = array();
$i = 0;
while($i < mysql_num_fields($query))
{
$field =& mysql_fetch_field($query,$i);
$fields[] = $field->name;
$i++;
}
echo '<tr><th>'.implode('</th><th>',array_map('htmlentities',$fields)).'</th></tr>';
$first = false;
}
echo '<tr><td><pre>'.implode('</pre></td><td><pre>',array_map('nl2br',array_map('htmlentities',$row))).'</pre></td></tr>';
}
that code is untested.
Posted: Thu Aug 25, 2005 10:31 am
by sleazyfrank
I just got a reply back from the php forum on plus net with a user confirming this:
Yep, the user you're running has not been granted select permissions.
Though looking at it is CourseTitle actually a table?
Try the following:-
Code:
my $sql = qq/SELECT * FROM coursesUnixLinux WHERE CourseTitle LIKE ( "%$keywordsearch%")/;
(The qq/ / forces quote delimiters around everything inside the / / marks avoiding the need to escape out quote marks etc...)
Table structure
Code: Select all
#
# Table structure for table `coursesUnixLinux`
#
CREATE TABLE `coursesUnixLinux` (
`RecordID` int(4) NOT NULL auto_increment,
`CourseTitle` varchar(255) binary NOT NULL default '',
`Duration` int(2) NOT NULL default '0',
`Price` int(6) NOT NULL default '0',
`January` varchar(12) binary default NULL,
`February` varchar(12) binary default NULL,
`March` varchar(12) binary default NULL,
`April` varchar(12) binary default NULL,
`May` varchar(12) binary default NULL,
`June` varchar(12) binary default NULL,
`July` varchar(12) binary default NULL,
`August` varchar(12) binary default NULL,
`September` varchar(12) binary default NULL,
`October` varchar(12) binary default NULL,
`November` varchar(12) binary default NULL,
`December` varchar(12) binary default NULL,
`Location` mediumtext NOT NULL,
PRIMARY KEY (`RecordID`),
UNIQUE KEY `RecordID` (`RecordID`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;
HTH
Frank
feyd | ..
Posted: Thu Aug 25, 2005 10:38 am
by feyd
try turning off 'binary' on the CourseTitle field.
Posted: Thu Aug 25, 2005 10:46 am
by sleazyfrank
RE your code: I tested that aginst my db and all the fields and data wrote out correctly. Therefore data can be requested from the db using my username using a simple select statement such as the one you used - SELECT * FROM coursesUnixLinux - but it seems I do not have permission to do anything more than that. I haven't tried an update or delete yet, so many be thats a course to take to really test for permissions.
frank
Posted: Thu Aug 25, 2005 11:01 am
by Stewsburntmonkey
Since you have declared the character fields as binary, text searches such as "LIKE" and "REGEXP" will be case sensitive. That might be causes the problems.

Posted: Thu Aug 25, 2005 11:24 am
by sleazyfrank
Right, now that Binary has been removed, the file just chugs. Processing away and crashes IE. If I remove the % and just do $query_unix = "SELECT * FROM coursesUnixLinux WHERE CourseTitle LIKE '" . $keywordsearch . "'";
it doesn't chug anymore but comes up with
keywordsearch = unix
unix query = SELECT * FROM coursesUnixLinux WHERE CourseTitle LIKE 'unix'
result_unix =
so again no match is made!
I must have a permission failure....!
Posted: Thu Aug 25, 2005 12:21 pm
by feyd
post the newest version please. (Remember to include the loop you are using to display)
latest code...
Posted: Fri Aug 26, 2005 3:33 am
by sleazyfrank
Hi all - here is my latest code
Code: Select all
//anti-sql injection filter function
function filterAlphanumeric($string){
preg_replace("/[^a-zA-Z0-9]/", "", $string);
}
//get keywordsearch
$keywordsearch = $_REQUEST['keywordsearch'];
echo 'keywordsearch = '.$keywordsearch . '<br />';
$keywordsearch = filterAlphanumeric($keywordsearch);
//set up queries - search on each table in db
$query_unix = "SELECT * FROM coursesUnixLinux WHERE CourseTitle LIKE '" . $keywordsearch . "'";
echo "unix query = " . $query_unix . "<br />";
//set up result variable for each return from databases
$result_unix;
//connect to database
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die("Unable to select database");
//get results
$result_unix = mysql_query($query_unix) or die(mysql_error());
//debug
$result = mysql_fetch_array($result_unix, MYSQL_ASSOC);
echo "result_unix = " . $result[CourseTitle]. "<br />";
//find out number of records in each result - IF each one. If it is null or empty, then search has
//come back empty and result can be ignored
$num_unix = mysql_num_rows($result_unix);
//if statements
//check to see if var is empty if so, no result at all!
if ($num_unix==null) {
echo "<span class='storybodytext'>Sorry, your search returned no matching courses, please <a href='searchcourses.php' class='storyBodyTextLink'>click here</a> to return.</span>";
}
else {
//results table
echo 'Matching courses: <br />';
echo '<table border="0" cellpadding="0" cellspacing="0" width="100%">';
echo '<tr><td class="storyBodyText"><b>Course Title</b></td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyText"><b>Duration</b></td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyText"><b>Price</b></td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyText"><b>Course Details</b></td>';
echo '</tr>';
//unix linux
//find out if there are unix results, if so put them into the results table
if ($num_unix != null){
//set up data loop
$i=0;
while ($i < $num_unix) {
//transfer data from result set to seperate variables
$coursetitle = mysql_result($result_unix,$i,"CourseTitle");
$duration = mysql_result($result_unix,$i,"Duration");
$price = mysql_result($result_unix,$i,"Price");
echo '<tr>';
echo '<td class="storyBodyText">'.$coursetitle.'</td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyText">'.$duration.'</td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyText">'.$price.'</td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyTextLink"><a href="training_courses/unix_systems/'.$coursetitle.'.htm" class="storyBodyTextLink">View details</a></td>';
echo '<tr>';
}
$i++;
}
//end results table
echo '</table>';
}
//close the db
mysql_close();
hth
frank
Result!
Posted: Fri Aug 26, 2005 5:41 am
by sleazyfrank
Got a result - firstly the db coursetitle field needed to be switched to Fulltext. Then I edited the code to:
Code: Select all
//unix linux
//find out if there are unix results, if so put them into the results table
if ($num_unix != null){
//set up data loop
while ($row = mysql_fetch_array($result_unix, MYSQL_ASSOC)) {
$coursetitle = $row['CourseTitle'];
$duration = $row['Duration'];
$price = $row['Price'];
echo '<tr>';
echo '<td class="storyBodyText">'.$coursetitle.'</td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyText">'.$duration.'</td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyText">'.$price.'</td>';
echo '<td width="5"><img src="images/1x1_white.gif" height="1" width="5"></td>';
echo '<td class="storyBodyTextLink"><a href="training_courses/unix_systems/'.$coursetitle.'.htm" class="storyBodyTextLink">View details</a></td>';
echo '<tr>';
}
}
Voila! Works perfectly! Woohoo! Thanks for all your help everyone!
frank