[SOLVED] My form has errors - any suggestions??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Post 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 | ..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

try turning off 'binary' on the CourseTitle field.
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Post 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
Stewsburntmonkey
Forum Commoner
Posts: 44
Joined: Wed Aug 24, 2005 2:09 pm

Post 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. :)
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Post 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....!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

post the newest version please. (Remember to include the loop you are using to display)
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

latest code...

Post 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
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Result!

Post 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
Post Reply