PHP - MYSQL - Search cannot find a word with '

Question

Search its full working but when I am trying to find a word with ' like Let's..cannot find it...the error is warning...Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in myfile on line 73. Can someone find the problem or make my code to work with this word...

Code:

$search = $_GET ['search']; 

mysql_connect("localhost","root","");
mysql_select_db("search");

$search_exploded = explode (" ", $search);

$x = "";
$construct = "";  
foreach($search_exploded as $search_each)
{
$x++;
if($x==1)
$construct .="title LIKE '%$search_each%' OR type LIKE '%$search_each%' OR year LIKE '%$search_each%'";
else
$construct .="AND title LIKE '%$search_each%' OR type LIKE '%$search_each%' OR year LIKE '%$search_each%'";

}

$constructs ="SELECT * FROM search WHERE $construct";
$run = mysql_query($constructs);

$foundnum = mysql_num_rows($run);

if ($foundnum==0)
echo "
<header class='page-header'>
<h1 class='page-title'>Nothing Found</h1>
</header>

    <div class='page-content'>

<p style='color:#999;'>Sorry, but nothing matched your search terms. Please try again with some different keywords.</p>
<aside id='search-2' class='widget widget_search'>
<form role='search' method='get' class='search-form' action='s'>
    <label>
        <input autocomplete='off' type='search' class='search-field' placeholder='Search Again …' value='' name='search' title='Search for:'>
    </label>
    <input type='submit' class='search-submit' value='Search'>
</form>
</aside>
</div></div>
";
else
{ 
echo" 
<header class='page-header'>
<h1 class='page-title'>Search results for <b>'$search'</b></h1>
</header>
";

You're missing spaces between your concatenated queries, which reads as `$constructs ="SELECT * FROM search WHERE $constructtitle LIKE '%$search_each%'";` etc. etc. etc. — Funk Forty Niner, Jan 16 '15 at 14:08
*"What spaces..??"* - Exactly, "what" spaces. Go over my comment again, as well as your code. — Funk Forty Niner, Jan 16 '15 at 14:12
You are vulnerable to [sql injection attacks](http://bobby-tables.com). — Marc B, Jan 16 '15 at 14:17
Tell you what. Put `var_dump($run);` after `$run = mysql_query($constructs);` and see how it reads, then show me how it reads. Also change your `$run = mysql_query($constructs);` to `$run = mysql_query($constructs) or die(mysql_error());` — Funk Forty Niner, Jan 16 '15 at 14:18
Without changing $run it shows bool(false) and with it shows 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 's be cops%' OR type LIKE '%let's%' OR year LIKE '%let's%'' at line 1 — Multi Subber, Jan 16 '15 at 14:25
MySQL uses single quotes to encapsulate strings. So when you directly put a single quote into a string then that single quote needs to be escaped. Please use parameterized queries instead and stop learning terrible programming practices. PHP breaks if you use `echo 'Hello there Mr. O'Reily';` so MySQL is fubarring in the same exact way. — MonkeyZeus, Jan 16 '15 at 14:26
Well there you go. The missing spaces do make a difference. Once you've fixed that and escaped your data as @MonkeyZeus stated, you will start getting some results. — Funk Forty Niner, Jan 16 '15 at 14:31
What about to detect if user its writing ' and make title without ' with srt_replace? — Multi Subber, Jan 16 '15 at 14:34
That's a whole different ball game. You shouldn't restrict your users against that. You need to escape your data using `mysql_real_escape_string()` and `stripslashes()` to accept the potential apostrophes. — Funk Forty Niner, Jan 16 '15 at 14:39
You're welcome. Try changing `$search = $_GET ['search'];` to `$search = stripslashes($_GET ['search']); $search = mysql_real_escape_string($_GET ['search']);` and `$construct .="title LIKE` to `$construct .=" title LIKE` and `$construct .="AND title LIKE` to `$construct .=" AND title LIKE` - That should work. — Funk Forty Niner, Jan 16 '15 at 14:52
Was glad to help. I reopened the question and posted an answer for you below, where you can tick the checkmark to accept it. @MultiSubber This will mark it as solved. — Funk Forty Niner, Jan 16 '15 at 15:14

score 2 · Accepted Answer · edited May 23 '17 at 10:25

You're missing spaces between your concatenated queries, which MySQL is reading it as:

$constructs ="SELECT * FROM search WHERE $constructtitle LIKE '%$search_each%'";

Notice how $construct and title are bunched up together $constructtitle, as would the other query?

Change $search = $_GET ['search']; to:

$search = stripslashes($_GET ['search']); 
$search = mysql_real_escape_string($_GET ['search']);

in order to accept apostrophes.

Also change $construct .="title LIKE to $construct .=" title LIKE

and $construct .="AND title LIKE to $construct .=" AND title LIKE

Your concatenated queries need spaces at the beginning.

Change your $run = mysql_query($constructs);
to $run = mysql_query($constructs) or die(mysql_error()); in order to spot errors.

I must note that your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

PHP - MYSQL - Search cannot find a word with '

1 Answers1