My sql satatment is not returning any rows

PHP
1

Im doing a select with two tables but Im not having returned rows.

But If I do my select with just one table(news or pages) it works perfectly.

This is my code:

$pdo = conecting();
$read = $pdo->prepare("SELECT title,content FROM news, pages WHERE (title LIKE ? OR content LIKE ? OR title_page LIKE ? OR content_page LIKE ?)");
$read->bindValue(1,"%$search%", PDO::PARAM_STR);
$read->bindValue(2,"%$search%", PDO::PARAM_STR);
$read->bindValue(3,"%$search%", PDO::PARAM_STR);
$read->bindValue(4,"%$search%", PDO::PARAM_STR);
$read->execute();
echo '<p>Your search returned <strong>'.$read->rowCOunt().' results!</strong></p>';
if($read->rowCount() <=0){
	echo '<h2>We didnt found any result for your search.</h2>';
}

Do you see some reason for this issue?

If I use only one table, Im always returning rows, like this:

$read = $pdo->prepare("SELECT title, content FROM pages WHERE (title_page LIKE ? OR content_page LIKE ?)");

OR with just news:

$read = $pdo->prepare("SELECT title, content FROM news WHERE (title LIKE ? OR content LIKE ?)");
2

You do not appear to have either an ON clause or a condition in your WHERE clause to actually specify how the wto tables are to be joined.

3

Thanks for your answer. In my question I have one thing wrong. I have “*” and not “title,content”. I have this:
$read = $pdo->prepare(“SELECT * FROM news, pages WHERE (title LIKE ? OR content LIKE ? OR title_page LIKE ? OR content_page LIKE ?)”);

4

What fields are in each of the two tables and what field in the first table are you joining to a corresponding field having the same value in the second table?

5

Hi miOzView,

I used bindParam in searching in PDO


  function bookstitile($search){
    try{
	   
	         $con = new Connection();
		  $db = $con->dbconnection();
		  $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
		  $find = '%'.$search.'%';
		  $cmd = $db->prepare("SELECT * FROM books where books_id like ?");
		  $cmd->bindParam(1,$find);
		  $cmd->execute();
		  $title='';
		  $author='';
		  $id='';
		     while($row=$cmd->fetch(PDO::FETCH_OBJ)){
		       $bookstitle= $row->books_title;
                        $author= $row->books_author;
                        $id     = $row->books_id;			 
		      
		   }
		  $data = array(
		                                  'title'=>$bookstitle,
						  'author'=>$author,
						  'id'=>$id
		  );
		  
		  
		  return json_encode($data);
		 
	}
       catch(PDOException $ex){
	       return $ex->getMessage();
	   }
  
  }

hope this idea will help :slight_smile:

6

Felgal my two tables news and pages dont have any realtion so I think I dont need a join. Im trying with a Union but still dont works.
Im trying like this: $read = $pdo->prepare("SELECT title,content FROM news WHERE (title LIKE ? OR content LIKE ? ORDER BY date DESC) UNION title,content FROM pages WHERE (title LIKE ? OR content LIKE ? ");

Do you have any idea why its not working?

7

Execute the following command in MySQL and post the results of each.


show create table news;
show create table pages;
8

I get this:

CREATE TABLE news (
id int(11) NOT NULL AUTO_INCREMENT,
thumb varchar(255) DEFAULT NULL,
title varchar(255) NOT NULL,
content text NOT NULL,
date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
views int(11) DEFAULT ‘0’,
PRIMARY KEY (id_not)
) ENGINE=InnoDB AUTO_INCREMENT=464 DEFAULT CHARSET=latin1

CREATE TABLE pages (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(200) NOT NULL,
content text NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

9

You would not use a join here. You would actually use a union following the below format. The origin column was added to differentiate between pages and news in each result row.


SELECT
              p.id id,
              p.title title,
              p.content content,
              "pages" origin
    FROM
              pages p
 WHERE
             p.title LIKE "%foo%"
         OR
             p.content LIKE "%foo%"
UNION ALL
SELECT
              n.id,
              n.title,
              n.content,
              "news"
    FROM
              news n
  WHERE
              n.title LIKE "%foo%"
         OR
             n.content LIKE "%foo%";

Also, it looks like your building a search engine. You would have improved efficiency/scalability using MYIASM full text searching. Though INNODB does not support this feature you could create a single table that is MyISAM that continuously updated/reindexed which contains the search content and a polymorphic foreign key to the origin table for improved search functionality. Just an idea though that might be a little over your head…

Here is an example of that table:

search

  • id (primary key)
  • entity [news,pages]
  • entity_id
  • content
  • index (entity,id)

Than you would simply run a cron or add/update a row in that table for each in news and pages. Also let it be known that polymorphic concept being represented with entity "foreign keys is an anti-pattern but it makes sense here for performance and simplicity reasons. Just an idea.

10

Thanks for your help! But what is difference between your code and mine? Seems icual for me, you just use UNION ALL and I use UNION but I have same result with UNION ALL!

11

You’re using a cross join not a union. Also, no title_page or content_page columns exist in either table. So I’m not sure why those are in the original query. Union all merges the results of each table without taking into consideration duplicates. On the other hand, union by itself excludes rows when all the columns have the same value. In this case that will never happen due to the origin column but no need for MySQL to do additionally processing to pick out unique rows so union all is used. Any further explaination I recommend searching the web for union or visiting the associated MySQL manual pages.

12

Thank you very much for your help! Its working now!!

13