CODEWITHSUNDEEP
phpmysqlmysqli
Mande Kira
Mande Kira

Reputation: 190

MySQLi select from two table with limit

I have been try to combine two tables from mysql database, the two tables are status and status_reply both have the same columns number and name, that is id, account_name, author, postdate, data Please a help will be appreciated.

            $limit = "LIMIT 0, 10";
        $query = mysqli_query($db_conx, "(SELECT * `status` as type from status WHERE data LIKE '%".$tag."%' ORDER BY postdate DESC $limit)
                                    UNION (SELECT * `status_reply` as type from status_reply WHERE data LIKE '%".$tag."%' ORDER BY postdate DESC $limit)");

            //$query = mysqli_query($db_conx, "SELECT * FROM status WHERE data LIKE '%$tag%' ORDER BY postdate DESC $limit");
            $statusnumrows = mysqli_num_rows($query);
            while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
            $statusid = $row["id"];
            $account_name = $row["account_name"];
            $author = $row["author"];
            $postdate = $row["postdate"];
            $data = $row["data"];
            $data = nl2br($data);
            $data = str_replace("&","&",$data);
            $data = stripslashes($data);
            $statuslist .= '<div id="status_'.$statusid.'" class="status_boxes"><div><b>Ivotised by <a href="home.php?u='.$author.'">'.$author.'</a> '.$postdate.':</b>
<article>'.$data.'</article>
</div></div>';
            }

Upvotes: 1

Views: 617

Answers (2)

Mande Kira
Mande Kira

Reputation: 190

I realized that I have to remove the type in both status and status_reply as references to the tables and identified each of the columns by their names. Am curious about it too! 

$query = mysqli_query($db_conx, "
(SELECT id, account_name, author, postdate, data from status WHERE data LIKE '%".$tag."%' 
     ORDER BY postdate DESC $limit)
UNION 
(SELECT id, account_name, author, postdate, data from status_reply WHERE data LIKE '%".$tag."%' 
     ORDER BY postdate DESC $limit)");

Upvotes: 0

user4035
user4035

Reputation: 23729

  1. Use backquotes ` for field names instead of straight quotes '
  2. Don't forget to quote $tag to protect from an SQL injection: mysqli_real_escape_string
  3. Remember, that is you want to search literally for LIKE wildcard characters "%", "_" as well as backslash. You need to escape them too, using: $tag = addcslashes($tag, '\%_');
$limit = "LIMIT 0, 10";
$query = mysqli_query($db_conx, "
(SELECT `status` as type from status WHERE data LIKE '%".$tag."%' 
     ORDER BY postdate DESC $limit)
UNION 
(SELECT `status_reply` as type from status_reply WHERE data LIKE '%".$tag."%' 
     ORDER BY postdate DESC $limit)");

Upvotes: 1

Related Questions