Query Failure in Php and Php Myadmin (I think its in the SQL statement)

nomadsoul · November 27, 2014

Hi and thank you for reading my topic.

I'm building a search engine from a tutorial and I'm getting the famous "Warning: mysql_num_rows() expects parameter 1" boolean etc..

I know mysql_num_rows() is depricated and I promise not to use it in the future but for now I'm using it in other projects on the same server and all's ok.

I'm quite sure it's a problem with the SQL syntax because when I paste SELECT * FROM searchengine WHERE keywords LIKE 'any_keyword_from_the_column' -into Phpmyadmin it won't return results, however a simple SELECT * FROM searchengine returns results.

So, without using mysql_error() I get: Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\research2\search.php on line 42

But after I applyin mysql_error() to everything I narrowed it down to this: $query = mysql_query($query) or die (mysql_error());:

I get the error msg:

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 'OR keywords LIKE 'search' OR keywords LIKE 'term'' at line 1

I've checked multiple times to see if I've left out any columns in the db or mismatched them in while loop.

Below I have included the code which you can paste into a file called search.php along with the mysql dump file to pase into phpmyadmin query box. I'm using xampp.

In the code the WHERE clause is extended into the if statement and concatenated into the $query variable. This is done to break the words in the text box so they can be searched as an array.

As I said, I really think it is the SQL syntax and not the mysql_* functions but I could be wrong.

I'd really like to get this code working and mess with it. It looks like it could be very useful as a private website search engine.

Thanks for your time and mental energy.

<html>
<head>
	<title></title>
	<meta http-equiv="content-type" content="text/html;charset=utf-8" />
</head>
<body>

   <h2>Search Engine</h2>
	 
	 
<form  action = './search.php ' method = 'get'> 
<input type = 'text' name = 'k'  size = '50'  value = '<?php  echo $_GET['k']; ?>' />
<input type = 'submit' value = 'Search'>

</form>
<hr />
results <br>
<?php 


	   $k = $_GET['k'];
	   $terms = explode(" ", $k);
	   $query = "SELECT * FROM searchengine WHERE ";
	   
	   
	   
	   foreach ($terms as $each) { 
	   
	      $i = 0;
		  
		  if ($i == 1)
			    $query .= "keywords LIKE '$each'  ";
		  
		  else
		   $query .= "OR keywords LIKE '$each'  ";
		  
	   }
	   mysql_connect("localhost", "root", "") or die (mysql_error());
	   mysql_select_db("search2") or die (mysql_error());
	   
	   $query = mysql_query($query) or die (mysql_error());
	   $numrows = mysql_num_rows($query) ;
	   
	   if ($numrows > 0) {
	   
			while ($row = mysql_fetch_assoc($query)) {
			    $id = $row['id'];
				$title = $row['title'];
				$description = $row['description'];
				$keywords = $row['keywords'];
				$link = $row['link'];
				
				echo "<h2><a href='$link'>$title</a></h2>
				$description<br /><br />";
			
			
			}
	   
	   
	   }
	   else 
		    echo $k ;
	   
	   
	  ?>	   
</body>
</html>

and here is the SQL dump (I called my db search2 and my table searchengine

-- phpMyAdmin SQL Dump
-- version 4.2.7.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Nov 27, 2014 at 11:54 AM
-- Server version: 5.6.20
-- PHP Version: 5.5.15

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `search2`
--
CREATE DATABASE IF NOT EXISTS `search2` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `search2`;

-- --------------------------------------------------------

--
-- Table structure for table `searchengine`
--

CREATE TABLE IF NOT EXISTS `searchengine` (
`id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `description` varchar(250) NOT NULL,
  `keywords` text NOT NULL,
  `link` varchar(250) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `searchengine`
--

INSERT INTO `searchengine` (`id`, `title`, `description`, `keywords`, `link`) VALUES
(1, 'Nick Frosty''s Official Web Site', 'Welcome to Nick''s', 'Nickfrosty Website tutorials videos web design', 'http://www.nickfrosty.com'),
(2, 'Google search engine', 'The best search engine', 'google web site search ', 'http://www.google.oom'),
(3, 'The Yahoo Search Engine', 'Not as good as Google', 'Yahoo search engine website', 'http://www.google.com'),
(4, 'This is a test', 'This is a test website', 'Google', 'http://www.google.com');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `searchengine`
--
ALTER TABLE `searchengine`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `searchengine`
--
ALTER TABLE `searchengine`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

If you have any questions about any details of this code please ask me.

Thanks for your time

Barand · November 27, 2014

So what is the query you are actually executing?

$query = mysql_query($query) or die (mysql_error());
echo $query;            // what does this output?

sasa · November 27, 2014

hee

line 29 ($i = 0;) move after if statemans to line 36.

and before foreach loop insert $i = 1;

Edited November 27, 2014 by sasa

maxxd · November 27, 2014

sasa has the right idea - if you print your query, you're going to come up with something like "SELECT * FROM searchengine WHERE OR keywords LIKE 'searching'". Change your foreach loop to this:

$i = 0;
foreach ($terms as $each) {
	if ($i == 0)
		$query .= "keywords LIKE '$each' ";
	else
		$query .= "OR keywords LIKE '$each' ";
	
	$i=1;
}

Also, you'll probably want to use wildcards in your select statement.

nomadsoul · November 27, 2014

Barand: Thanks

The error out put is: 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 'OR keywords LIKE 'search' OR keywords LIKE 'term'' at line 1

And the output should return all the rows based on keywords from the keywords column that match the '$k' input. and the other columns are the id, title, description, and link fields. (I think that is what you are asking)?

sass: Thanks will try that

maxxd;Thanks

Ok will try that. I think you mean something like: $query .= "OR keywords LIKE %$each% ";?

nomadsoul · November 27, 2014

I tried the suggestions and still get the same 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 'OR keywords LIKE 'web' keywords LIKE 'yahoo'' at line 1"

yahoo is one of multiple keywords in the keywords column

When I run the query SELECT * FROM searchengine WHERE keywords LIKE 'yahoo' directly through phpmyadmin it returns an empty set:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0000 seconds.)

It will only return a results set (in phpmyadmin) only when yahoo is the only keyword in the keywords column.

But the keywords column is supposed to be able to process multiple keywords within a cell as long as they are space delimited.

I was hoping someone might paste this code into htdocs and dump the sql into phpmyadmin or the mysql shell to see if the same errors arise.

Jacques1 · November 27, 2014

You need to echo the actual query generated by your code and check it. Running some other query you made up doesn't help you with the problem.

Obviously you still haven't fixed the bug pointed out by sasa and maxxd. You also can't just drop GET parameters into your query string. Never heard of escaping?

mac_gyver · November 27, 2014

a) you need to echo the actual sql query statement, echo $query; you need to do this before the msyql_query() line since that line is die'ing due to the error.

b) you need to escape the $each value being put into your sql query statement to prevent sql injeciton and to prevent sql errors.

c) it is much easier if you just put each of the keywords like '%term%' entries into an array and implode the array with an ' OR ' to produce the combined term (this will produce the correct result even if there is just one entry.)

nomadsoul · November 28, 2014

Sassa's fix worked thanks!

Echoing the query was the first thing I did. But thanks for the great advice.

Jaques1:

You also can't just drop GET parameters into your query string. Never heard of escaping?

-Thank You. You are correct, I'm getting back to coding after a long layoff.

Sign In

Query Failure in Php and Php Myadmin (I think its in the SQL statement)

Recommended Posts

nomadsoul

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

sasa

Link to comment

Share on other sites

maxxd

Link to comment

Share on other sites

nomadsoul

Link to comment

Share on other sites

nomadsoul

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

nomadsoul

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information