Need to search username from two mysql table sametime

Question

I am facing some problem, when trying to fetching data or username from two table in same query. the code is given below. Whats wrong with that code. I have sat UNION, UNION ALL etc. But still it is not working. Usually by putting one table, the query show result, but after putting two table in $sql is does not show any result.

$username = "";
$code = "";

$queryCondition = "";
if(!empty($_POST["search"])) {
    foreach($_POST["search"] as $k=>$v){
        if(!empty($v)) {

            $queryCases = array("username","code");
            if(in_array($k,$queryCases)) {
                if(!empty($queryCondition)) {
                    $queryCondition .= " AND ";
                } else {
                    $queryCondition .= " WHERE ";
                }
            }
            switch($k) {
                case "username":
                    $username = $v;
                    $queryCondition .= "username LIKE '%" . $v . "%'";
                    break;
                case "code":
                    $code = $v;
                    $queryCondition .= "code LIKE '%" . $v . "%'";
                    break;
            }
        }
    }
}
$orderby = " ORDER BY added desc"; 
$sql = "SELECT * FROM tableA OR SELECT * from tableB ORDER BY added" . 
$queryCondition;
$href = 'abcd.php';                 

$perPage = 5; 
$page = 1;
if(isset($_POST['page'])){
    $page = $_POST['page'];
}
$start = ($page-1)*$perPage;
if($start < 0) $start = 0;

$query =  $sql . $orderby .  " limit " . $start . "," . $perPage; 
$result = $db_handle->runQuery($query);

if(!empty($result)) {
    $result["perpage"] = showperpage($sql, $perPage, $href);
}


?>


<div id="toys-grid">      
        <form name="frmSearch" method="post" action="list.php">
        <div class="search-box">
        <p><input type="text" placeholder="username" name="search[username]" class="demoInputBox" value="<?php echo $username; ?>"  />
        <input type="text" placeholder="Code" name="search[code]" class="demoInputBox" value="<?php echo $code; ?>" />
        <input type="submit" name="go" class="btnSearch" value="Search">
        <input type="reset" class="btnSearch" value="Reset" onclick="window.location='list.php'"></p>
        </div>

<div class="not">
<?php
        foreach($result as $k=>$v) {
        if(is_numeric($k)) {
        ?>

 <div  class="" > 
  <div class="testimonial" >
  <h4><a href="<?php echo $result[$k]["link"]; ?>"><?php echo $result[$k]["username"]; ?></a></h4>
  <img src="<?php echo $result[$k]["image"]; ?>" style="width:100px;height:100px;">
  <p style=""><?php echo $result[$k]["usertype"]; ?></p>

  <div class="textads-link">
  <strong>http://btcgenuis.com</strong><br>
  <p><?php echo $result[$k]["regdate"]; ?></p>
  </div>
  </div> 
  </div>    



 <?php
        }
    }?>
 </div><div style="clear: both; height: 10px"></div>  <div style="text-align:right;margin:20px 0px 10px;">  
                <?php
                if(isset($result["perpage"])) {
                ?>

                 <?php echo $result["perpage"]; ?>

                <?php } ?>
        </div>
        </form>

Hope so get a best solution for this error.

If you explain what isn't working, that might help get you an answer. — Sloan Thrasher, May 09 '18 at 22:20
Usually by putting one table, the query show result, but after putting two table in `$sql` is does not show any result. `$sql = "SELECT * FROM tableA OR SELECT username * tableB ORDER BY added" . $queryCondition;` — SALIM MURSHED, May 09 '18 at 22:24
Print out your query and you'll see, that you have the `WHERE` clause after the `ORDER BY` clause. That's wrong. And `SELECT ... OR SELECT` won't work either. Depending on what you want to do, there are different possible operators here. Try reading a tutorial about SQL basics, instead of randomly throwing stuff together. And please read about [SQL injection](http://php.net/manual/en/security.database.sql-injection.php), your code is vulnerable. — sticky bit, May 09 '18 at 22:38
@stickybit, I need to show, name of the user from 2 table in same Mysql — SALIM MURSHED, May 09 '18 at 22:40

Barmar · Answer 1 · 2018-05-09T22:41:59.723

1

UNION is almost certainly what you want:

$sql = "SELECT * FROM tableA $queryCondition 
        UNION 
        SELECT * FROM tableB $queryCondition 
        ORDER BY added";

This assumes both tables have the same columns. If not, you need to replace SELECT * with a list of specific columns, so that you select the same columns from both tables.

edited May 09 '18 at 22:41

answered May 09 '18 at 22:32

Barmar

741,623
53
500
612

Yes, both table has same column. – SALIM MURSHED May 09 '18 at 22:39
Is the query getting an error? What's the error message? – Barmar May 09 '18 at 22:42
2 error says `mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in` in the code `$queryCondition = "";` and `Invalid argument supplied for foreach()` at the below of that code `foreach($result as $k=>$v) {` – SALIM MURSHED May 09 '18 at 22:49
You need to check whether `mysqli_query()` succeeds. If it fails you need to print `mysqli_error($conn);` to see the error message. – Barmar May 09 '18 at 22:51
That should be in your `runQuery()` method. – Barmar May 09 '18 at 22:52
Actually, when i select, one table, it shows ans, but i need to select based on two table, and i used that code to get data from 2 table. – SALIM MURSHED May 09 '18 at 22:57
What is the MySQL error you're getting when you use the code I gave? – Barmar May 09 '18 at 23:09
2 error says `mysql_fetch_assoc() expects parameter 1 to be resource, boolean` given in in the code `$queryCondition = "";` and `Invalid argument supplied for foreach()` at the below of that code `foreach($result as $k=>$v) {` – SALIM MURSHED May 09 '18 at 23:18
I told you earlier that you need to check the result from `mysqli_query()`. If it returns `FALSE` you have to `echo mysqli_error($connection);` (replace `$connection` with the variable that contains the connection object) and not try to call `mysqli_fetch_assoc()`. – Barmar May 09 '18 at 23:21
See https://stackoverflow.com/questions/2546314/php-mysql-mysqli-num-rows-expects-parameter-1-to-be-mysqli-result-boolean?noredirect=1&lq=1 – Barmar May 09 '18 at 23:22

Need to search username from two mysql table sametime

1 Answers1